The last time I wrote a file stream method to export a DataTable to Excel, this method has limitations, such as the inability to add to excel some simple operation of the column color, and now, to introduce another way to use Microsoft's Excel class. Since you need to use the class, it must be your machine to load Excel.
public void Datatabletoexcel (system.data.datatable[] tmpdatatable,string date1,string date2)
{
String savefilename = "";
SaveFileDialog Savedialog = new SaveFileDialog ();
Savedialog.defaultext = "xls";
Savedialog.filter = "Excel file |*.xls";
Savedialog.showdialog ();
Savefilename = Savedialog.filename;
if (Savefilename.indexof (":") < 0)
Return It was canceled.
for (int k = 0; k < tmpdatatable.length; k++)
{
if (k% 50 = = 0)
//{
Process[] processes = process.getprocesses ();
foreach (process process in processes)
{
if (process. ProcessName = = "EXCEL")
{
if (string. IsNullOrEmpty (process. MainWindowTitle))
{
Process. Kill ();
}
}
}
//}
Microsoft.Office.Interop.Excel.Application xlapp = new Microsoft.Office.Interop.Excel.ApplicationClass ();
Try
{
#region
if (tmpdatatable[k] = = null)
Return
String name=string. Empty;
if (grid_result1.getgridview.columns["str_1"]. Visible = = False && grid_result1.getgridview.columns["Str_8"]. Visible = = False)
Name = Tmpdatatable[k]. Rows[0][0]. ToString ();
if (grid_result1.getgridview.columns["str_1"]. Visible = = False && grid_result1.getgridview.columns["Str_8"]. Visible = = True)
Name = Tmpdatatable[k]. ROWS[0][1]. ToString ();
if (grid_result1.getgridview.columns["str_1"]. Visible = = True && grid_result1.getgridview.columns["Str_8"]. Visible = = True)
Name = Tmpdatatable[k]. ROWS[0][2]. ToString ();
if (grid_result1.getgridview.columns["str_1"]. Visible = = True && grid_result1.getgridview.columns["Str_8"]. Visible = = False)
Name = Tmpdatatable[k]. ROWS[0][1]. ToString ();
if (name. IndexOf ("/") >-1)
{
Name = name. Replace ("/", "");
}
Name = name + Date1 + "~" + date2;
Savefilename = savefilename.substring (0, savefilename.lastindexof ("\ \")) + "\ \" + name + ". xls";
int rowNum = Tmpdatatable[k]. Rows.Count;
int columnnum = Tmpdatatable[k]. Columns.count;
int rowIndex = 1;
int columnindex = 0;
Xlapp.defaultfilepath = "";
Xlapp.displayalerts = true;
Xlapp.sheetsinnewworkbook = 1;
Workbook xlbook = XlApp.Workbooks.Add (true);
Import the column name of a DataTable into the first row of an Excel table
foreach (DataColumn dc in tmpdatatable[k]. Columns)
{
columnindex++;
Xlapp.cells[rowindex, ColumnIndex] = DC. ColumnName;
}
Import data from a DataTable into Excel
for (int i = 0; i < rowNum; i++)
{
rowindex++;
columnindex = 0;
for (int j = 0; J < Columnnum; J + +)
{
columnindex++;
Xlapp.cells[rowindex, columnindex] = tmpdatatable[k]. ROWS[I][J]. ToString ();
}
}
if (Savefilename.indexof ("/") >-1)
{
Savefilename = Savefilename.replace ("/", "-");
}
System.IO.File.Delete (Savefilename);
Xlbook.savecopyas (Savefilename);
process[] processes1 = process.getprocesses ();
foreach (Process process in processes1)
{
if (process. ProcessName = = "EXCEL")
{
if (string. IsNullOrEmpty (process. MainWindowTitle))
{
Process. Kill ();
}
}
}
System.Threading.Thread.Sleep (1000);
#endregion
}
catch (Exception ex)
{
Xtramessagebox.show ("Error exporting Excel, Reason:" + ex.) Message);
Return
}
}
C # Exports DataTable data in memory to Excel (method two, create Excel object Export) "reprint"