In my C# code, I have a list with data that I want to write into an Excel file
using Interop.Excel.
How could I do it?
Add a comment
|
2 Answers
try
{
xlsApp = new Excel.Application();
xlsWorkbook = xlsApp.Workbooks.Add(misValue);
xlsWorksheet = (Excel.Worksheet)xlsWorkbook.Sheets[1];
// Create the header for Excel file
xlsWorksheet.Cells[1, 1] = "The Export Excels Function";
Excel.Range range = xlsWorksheet.get_Range("A1", "E1");
range.Merge(1);
range.Borders.Color = Color.Black.ToArgb();
range.Interior.Color = Color.Yellow.ToArgb();
#region get connection string from config file
int selectedIndex = DbCbx.SelectedIndex;
string[] listConnection = ConfigurationManager.AppSettings["listConnection"].Split(',');
connectionString = listConnection[selectedIndex];
#endregion
int i = 3;
if(IsOraDB.Checked)
{
OracleConnection con = new OracleConnection(connectionString);
OracleCommand cmdORA = new OracleCommand();
cmdORA.CommandText = sqlselect;
cmdORA.Connection = con;
con.Open();
OracleDataReader dr = cmdORA.ExecuteReader();
#region set the max of progress
// Set Maximum to the total number of files to copy.
progress.Maximum = (int)dr.FieldCount;
#endregion
if (dr.HasRows)
{
for (int j = 0; j < dr.FieldCount; ++j)
{
xlsWorksheet.Cells[i, j + 1] = dr.GetName(j);
}
++i;
}
while (dr.Read())
{
for (int j = 1; j <= dr.FieldCount; ++j)
xlsWorksheet.Cells[i, j] = dr.GetValue(j - 1);
++i;
progress.PerformStep();
}
}
else
{
using SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
using SqlCommand cmd = new SqlCommand(sqlselect, conn);
using SqlDataReader dr = cmd.ExecuteReader();
#region set the max of progress
// Set Maximum to the total number of files to copy.
progress.Maximum = (int)dr.FieldCount;
#endregion
if (dr.HasRows)
{
for (int j = 0; j < dr.FieldCount; ++j)
{
xlsWorksheet.Cells[i, j + 1] = dr.GetName(j);
}
++i;
}
while (dr.Read())
{
for (int j = 1; j <= dr.FieldCount; ++j)
xlsWorksheet.Cells[i, j] = dr.GetValue(j - 1);
++i;
progress.PerformStep();
}
}
range = xlsWorksheet.get_Range("A2", "I" + (i + 2).ToString());
range.Columns.AutoFit();
xlsWorkbook.SaveAs(fileName, Excel.XlFileFormat.xlWorkbookDefault, misValue, misValue, misValue, misValue,
Excel.XlSaveAsAccessMode.xlExclusive, Excel.XlSaveConflictResolution.xlLocalSessionChanges, misValue, misValue, misValue, misValue);
xlsWorkbook.Close(true, misValue, misValue);
xlsApp.Quit();
ReleaseObject(xlsWorksheet);
ReleaseObject(xlsWorkbook);
ReleaseObject(xlsApp);
if (MessageBox.Show("Excel report has been created on your desktop\nWould you like to open it?", "Created Excel report",
MessageBoxButtons.YesNo, MessageBoxIcon.Information, MessageBoxDefaultButton.Button2) == DialogResult.Yes)
{
Process.Start(@"cmd.exe ", @"/c "+fileName);
}
}
catch (Exception ex)
{
MessageBox.Show("Error creating Excel report: " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Stop);
}
Comments
add reference and using
Microsoft.Office.Interop.Excel
code for one sheat:
List<string> list = new List<string>();
object missing = Type.Missing;
Excel.Application oXL = new Excel.Application();
oXL.Visible = false;
Excel.Workbook oWB = oXL.Workbooks.Add(missing);
string fileName = string.Empty;
Excel.Worksheet oSheet = oWB.ActiveSheet as Excel.Worksheet;
var oSheetItems = oSheet;
if (oSheetItems != null)
{
oSheetItems.Name = "sheatName";
int i = 1;
foreach (var item in list)
{
//insert your object
oSheetItems.Cells[i, 1] = item;
oSheetItems.Cells[i, 2] = item;
i++;
}
}
fileName = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)
+ "\\ExcelName.xlsx";
oWB.SaveAs(fileName, Excel.XlFileFormat.xlOpenXMLWorkbook,
missing, missing, missing, missing,
Excel.XlSaveAsAccessMode.xlNoChange,
missing, missing, missing, missing, missing);
oWB.Close(missing, missing, missing);
oXL.UserControl = true;
oXL.Quit();
if need more sheat: inter all code in loop an for every sheat use this code and change sheat with:
Excel.Worksheet oSheet2 = oWB.Sheets.Add(missing, missing, 1, missing);
oSheetItems = oSheet2;
