C#操作Excel

来源:互联网 发布:社交软件排名 编辑:程序博客网 时间:2024/06/06 05:45

刚好用到C#操作Excel,goole了一些资料,总结了一下,主要流程如下所示:

 

一、添加引用
    添加com组件 Microsoft Office 11.0 Object Library 或者添加Excel.exe,引用默认路径为C:/Program Files/Microsoft Office/OFFICE11/Excel.exe

 

二、代码

 public static void ExportToExcel(DataSet dataSet, string outputPath)
  {
   // Create the Excel Application object
   ApplicationClass excelApp = new ApplicationClass();

   // Create a new Excel Workbook
   Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing);

   int sheetIndex = 0;

   // Copy each DataTable as a new Sheet
   foreach (System.Data.DataTable dt in dataSet.Tables)
   {

    // Create a new Sheet
    Worksheet excelSheet = (Worksheet) excelWorkbook.Sheets.Add(
     excelWorkbook.Sheets.get_Item(++sheetIndex),
     Type.Missing, 1, XlSheetType.xlWorksheet);

    excelSheet.Name = dt.TableName;

    // Copy the column names (cell-by-cell)
    for (int col = 0; col < dt.Columns.Count; col++) {
     excelSheet.Cells[1, col + 1] = dt.Columns[col].ColumnName;
    }

    ((Range) excelSheet.Rows[1, Type.Missing]).Font.Bold = true;


    // Copy the values (cell-by-cell)
    for (int col = 0; col < dt.Columns.Count; col++)
    {
     for (int row = 0; row < dt.Rows.Count; row++)
     {
      excelSheet.Cells[row + 2, col + 1] = dt.Rows[row].ItemArray[col];
     }
    }

   }

   // Save and Close the Workbook
   excelWorkbook.SaveAs(outputPath, XlFileFormat.xlWorkbookNormal, Type.Missing,
    Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive,
    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
   excelWorkbook.Close(true, Type.Missing, Type.Missing);
   excelWorkbook = null;

   // Release the Application object
   excelApp.Quit();
   excelApp = null;

   // Collect the unreferenced objects
   GC.Collect();
   GC.WaitForPendingFinalizers();

  }

原创粉丝点击