C# 将内存中的datatable数据导出为Excel（方法二，创建Excel对象导出）
上次写了一个用文件流方式将Datatable导出Excel的方法，这个方法有局限性，比如没法对Excel进行一些增加列颜色等简单的操作，现在，给大家介绍另外一种方法，用微软的Excel类。既然要用到类，那必须是你的机子要装上Excel才行呢。
public void DataTabletoExcel(System.Data.DataTable[] tmpDataTable,string date1,string date2)
{
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel 文件|*.xls";
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
return; //被点了取消
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);
//将DataTable的列名导入Excel表第一行
foreach (DataColumn dc in tmpDataTable[k].Columns)
{
columnIndex++;
xlApp.Cells[rowIndex, columnIndex] = dc.ColumnName;
}
//将DataTable中的数据导入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("导出Excel出错，原因:" + ex.Message);
return;
}
}
