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;

                 }

        }