C# 保存表格数据到Excel

方法1

这种方法需要在本机安装了Office，然后在程序中调用Windows Office的组件。
首先需要包含的空间：

using Excel = Microsoft.Office.Interop.Excel;

下面是导出到Excel表格的响应函数，（m_table是界面上的DataGridView控件）

//导出到excel文件
        private void button_ToExcel_Click(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            dt = m_table.Copy();
            int mun = dt.Rows.Count;
            if (dt == null) return;

            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlApp == null)
            {
                MessageBox.Show("无法创建Excel对象，可能您的电脑未安装Excel");
                return;
            }
            System.Windows.Forms.SaveFileDialog saveDia = new SaveFileDialog();
            saveDia.Filter = "Excel|*.xls";
            saveDia.Title = "导出为Excel文件";
            if (saveDia.ShowDialog() == System.Windows.Forms.DialogResult.OK
             && !string.Empty.Equals(saveDia.FileName))
            {
                Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
                Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
                Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
                Microsoft.Office.Interop.Excel.Range range = null;
                long totalCount = dt.Rows.Count;
                long rowRead = 0;
                float percent = 0;
                string fileName = saveDia.FileName;
                //写入标题
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
                    range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
                    //range.Interior.ColorIndex = 15;//背景颜色
                    range.Font.Bold = true;//粗体
                    range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;//居中
                    //加边框
                    range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
                    //range.ColumnWidth = 4.63;//设置列宽
                    //range.EntireColumn.AutoFit();//自动调整列宽
                    //r1.EntireRow.AutoFit();//自动调整行高
                }
                //写入内容
                for (int r = 0; r < dt.DefaultView.Count; r++)
                {
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        worksheet.Cells[r + 2, i + 1] = dt.DefaultView[r][i];
                        range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + 2, i + 1];
                        range.Font.Size = 9;//字体大小
                        //加边框
                        range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
                        range.EntireColumn.AutoFit();//自动调整列宽
                    }
                    rowRead++;
                    percent = ((float)(100 * rowRead)) / totalCount;
                    System.Windows.Forms.Application.DoEvents();
                }
                range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
                if (dt.Columns.Count > 1)
                {
                    range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
                }
                try
                {
                    workbook.Saved = true;
                    workbook.SaveCopyAs(fileName);
                }
                catch (Exception ex)
                {
                    MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
                    return;
                }

                workbooks.Close();
                if (xlApp != null)
                {
                    xlApp.Workbooks.Close();
                    xlApp.Quit();
                    int generation = System.GC.GetGeneration(xlApp);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                    xlApp = null;
                    System.GC.Collect(generation);
                }
                GC.Collect();//强行销毁
                #region 强行杀死最近打开的Excel进程
                System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL");
                System.DateTime startTime = new DateTime();
                int m, killId = 0;
                for (m = 0; m < excelProc.Length; m++)
                {
                    if (startTime < excelProc[m].StartTime)
                    {
                        startTime = excelProc[m].StartTime;
                        killId = m;
                    }
                }
                if (excelProc[killId].HasExited == false)
                {
                    excelProc[killId].Kill();
                }
                #endregion
                MessageBox.Show("导出成功!", "提示", MessageBoxButtons.OK);
            }
        }

方法二

前面的方法虽然能够成功导出Excel到本机，但是它需要Office的组件，在有些没有Office组件的机器上就不能使用了。因而使用最多的是第三方的组件，这里使用到的组件是NPOI。
首先添加空间

using NPOI.HSSF.UserModel; 
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

DataTable dt = new DataTable(); 
if (this.dgv_allItems.DataSource == null || ((DataTable)this.dgv_allItems.DataSource).Rows.Count == 0)
{
    MessageBox.Show("当前没有数据！");
    return;
}   //dgv_allItems是界面上的DataGridView
if (this.dgv_allItems.SelectedRows.Count > 0)
{
    dt = (this.dgv_allItems.DataSource as DataTable).Clone();
    foreach (DataGridViewRow r in this.dgv_allItems.SelectedRows)
    {
        dt.Rows.Add((r.DataBoundItem as DataRowView).Row.ItemArray);
    }
}
else
{
    dt = (this.dgv_allItems.DataSource as DataTable).Clone();
    foreach (DataGridViewRow r in this.dgv_allItems.Rows)
    {
        dt.Rows.Add((r.DataBoundItem as DataRowView).Row.ItemArray);
    }
}
SaveFileDialog dlg = new SaveFileDialog();
dlg.Filter = "97-2003文档（*.xls）|*.xls|2007文档(*.xlsx)|*.xlsx";
if (DialogResult.OK == dlg.ShowDialog())
{
     string suffix = dlg.FileName.Substring(dlg.FileName.LastIndexOf(".") + 1, dlg.FileName.Length - dlg.FileName.LastIndexOf(".") - 1);
     if (suffix == "xls")
     {
        GetExcelClass.RenderToExcel(dt, dlg.FileName);
     }
     else
     GetExcelClass.TableToExcelForXLSX(dt, dlg.FileName);
}

/// <summary>
/// 导出数据到excel2003中
/// </summary>
/// <param name="table"></param>
/// <param name="filename"></param>
/// <returns></returns>
public bool RenderToExcel(DataTable table,string filename)
{
    MemoryStream ms = new MemoryStream();
    using (table)
    {
         NPOI.HSSF.UserModel.HSSFWorkbook workbook = new HSSFWorkbook();

         ISheet sheet = workbook.CreateSheet();

         IRow headerRow = sheet.CreateRow(0);
         // handling header. 
         foreach (DataColumn column in table.Columns)
              headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);//If Caption not set, returns the ColumnName value 

         // handling value. 
         int rowIndex = 1;

         foreach (DataRow row in table.Rows)
         {
              IRow dataRow = sheet.CreateRow(rowIndex);

              foreach (DataColumn column in table.Columns)
              {
                   dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
              }
              //  proBar.progressBar1.Value = proBar.progressBar1.Value+1;

              rowIndex++;
          }
          workbook.Write(ms);
          ms.Flush();
          ms.Position = 0;
          try
          {
               SaveToFile(ms, filename);
               MessageBox.Show("数据导出成功！");
               return true;
           }
           catch (System.Exception ex)
           {
               MessageBox.Show(ex.ToString());
               return false;
            }
     } 
}


//////////////////////////////////////////////////////////////////////////
public void SaveToFile(MemoryStream ms, string fileName)
{
     using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
     {
         byte[] data = ms.ToArray();

         fs.Write(data, 0, data.Length);
         fs.Flush();

         data = null;
     }
}

/// <summary>
/// 导出数据到excel2007中
/// </summary>
/// <param name="dt"></param>
/// <param name="file"></param>
/// <returns></returns>
public bool TableToExcelForXLSX(DataTable dt, string file)
{
     XSSFWorkbook xssfworkbook = new XSSFWorkbook();
     ISheet sheet = xssfworkbook.CreateSheet("Test");

     //表头   
     IRow row = sheet.CreateRow(0);
     for (int i = 0; i < dt.Columns.Count; i++)
     {
          ICell cell = row.CreateCell(i);
          cell.SetCellValue(dt.Columns[i].ColumnName);
     }

     //数据   
     for (int i = 0; i < dt.Rows.Count; i++)
     {
          IRow row1 = sheet.CreateRow(i + 1);
          for (int j = 0; j < dt.Columns.Count; j++)
          {
               ICell cell = row1.CreateCell(j);
               cell.SetCellValue(dt.Rows[i][j].ToString());
          }
     }

     //转为字节数组   
     MemoryStream stream = new MemoryStream();
     xssfworkbook.Write(stream);
     var buf = stream.ToArray();

     //保存为Excel文件  
     try
    {
          using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
          {
               fs.Write(buf, 0, buf.Length);
               fs.Flush();
           }
           MessageBox.Show("数据导出成功！");
           return true;
     }

      catch(SystemException ex)
     {
         MessageBox.Show(ex.ToString());
         return false;
     }
}