C# 将内存中的datatable数据导出为Excel（方法二，创建Excel对象导出）

来源：互联网发布：淘宝上卖宠物用品货源编辑：程序博客网时间：2024/04/29 19:18

上次写了一个用文件流方式将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;

}

0 0