需要添加引用 Microsoft.Office.Interop.Excel
注意：使用Microsoft.Office.Interop.Excel 非常耗时。对性能有要求建议用其他。
如果要用，把数据转成字符串拷贝到剪贴板中，然后把剪贴板数据粘贴到sheet表中即可，几十万数据秒级。下面有讲到怎么转换字符串拷贝与粘贴。
代码部分
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
/*******************************************************************
* Copyright (C) 版权所有
* 文件名称:ExcelManage
* 命名空间:WindowsFormsApplication3
* 创建时间:2019/3/4 15:20:27
* 作 者: wangyonglai
* 描 述：
* 修改记录:
* 修改人:
* 版 本 号:v1.0.0
**********************************************************************/
namespace WindowsFormsApplication3
{
//替代名称
using Excel = Microsoft.Office.Interop.Excel;//替代名称
using Missing = System.Reflection.Missing;
class ExcelManage
{
public System.Data.DataSet dataSet = new System.Data.DataSet();
public void InitalTable()
{
System.Data.DataTable table = new System.Data.DataTable();
table.Columns.Add("序号", typeof(int));
table.Columns.Add("数据1", typeof(int));
table.Columns.Add("数据2", typeof(int));
Random r = new Random();
for (int i = 0; i < 200; i++)
{
if (i == 6 || i == 16) continue;
table.Rows.Add(i + 1, r.Next(50), r.Next(60));
}
dataSet.Tables.Add(table);
//ExportExcel(dataSet);
}
public void ExportExcel(System.Data.DataSet ds)
{
//新建EXCEL应用
Excel.Application excelApp = new Excel.Application();
if (excelApp == null)
return;
//设置为不可见，操作在后台执行，为 true 的话会打开 Excel
excelApp.Visible = false;
//初始化工作簿
Excel.Workbooks workbooks = excelApp.Workbooks;
//新增加一个工作簿，Add（）方法也可以直接传入参数 true
//Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
//同样是新增一个工作簿，但是会弹出保存对话框
Excel.Workbook workbook = workbooks.Add(true);
//Excel.Worksheet c_worksheet = workbook.Worksheets[1];
//int a = workbook.Worksheets.Count;
//workbook.Sheets.Add(Missing.Value, workbook.Sheets[1], ds.Tables.Count - 1, Missing.Value);
for (int index = 0; index < ds.Tables.Count; index++)
{
System.Data.DataTable dt = ds.Tables[index];
Excel.Worksheet worksheet = workbook.Worksheets.Add();
//Excel.Worksheet worksheet = workbook.Worksheets[index + 1];
//创建一个单元格
Excel.Range range;
int rowIndex = 1; //行的起始下标为 1
int colIndex = 1; //列的起始下标为 1
for (int i = 0; i < dt.Columns.Count; i++)
{
//设置第一行，即列名
worksheet.Cells[rowIndex, colIndex + i] = dt.Columns[i].ColumnName;
//获取第一行的每个单元格
range = worksheet.Cells[rowIndex, colIndex + i];
//字体加粗
range.Font.Bold = true;
//设置为黑色
range.Font.Color = 0;
//设置为宋体
range.Font.Name = "Arial";
//设置字体大小
range.Font.Size = 12;
//水平居中
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//垂直居中
range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
}
//跳过第一行，第一行写入了列名
rowIndex++;
//写入数据
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
worksheet.Cells[rowIndex + i, colIndex + j] = dt.Rows[i][j].ToString();
range = worksheet.Cells[rowIndex + i, colIndex + j];
range.Interior.Color = System.Drawing.Color.Yellow;
range.Cells.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders.Weight = Excel.XlBorderWeight.xlHairline;//边框常规粗细
}
}
//设置所有单元格列宽为自动列宽
worksheet.Cells.Columns.AutoFit();
#region 冻结行
worksheet.Select();
excelApp.ActiveWindow.SplitRow = 1;
excelApp.ActiveWindow.SplitColumn = 0;
excelApp.ActiveWindow.FreezePanes = true;
#endregion
#region 合并行
//Excel.Range mergeRange = worksheet.get_Range("A25", "B25");
//mergeRange.Merge();
/////合并单元格之后，设置其中的文本
//mergeRange.Value = "mergeRange";
#endregion
#region 绘制CHART图表
int num = dt.Rows.Count + 1;
Excel.Chart xlChart = workbook.Charts.Add(Missing.Value, Missing.Value, 1, Missing.Value);
Excel.Range ranges1 = worksheet.Cells[1, 1];
Excel.Range ranges2 = worksheet.Cells[num, dt.Columns.Count];
Excel.Range chartRage = worksheet.get_Range(ranges1, ranges2);
xlChart.ChartWizard(chartRage, Excel.XlChartType.xlLine, Missing.Value,
Excel.XlRowCol.xlColumns, 1, 1,
Missing.Value, Missing.Value, "X值", "Y值", Missing.Value);
workbook.ActiveChart.HasTitle = true;
workbook.ActiveChart.ChartTitle.Text = "图表名称";
workbook.ActiveChart.HasDataTable = false;
//给图表放在指定位置
//workbook.ActiveChart.Location(Excel.XlChartLocation.xlLocationAsObject, worksheet.Name);
xlChart.Name = "CC1";
#endregion
//workbook.Worksheets.Item(
}
//是否提示，如果想删除某个sheet页，首先要将此项设为fasle。
excelApp.DisplayAlerts = false;
//保存写入的数据，这里还没有保存到磁盘
workbook.Saved = true;
workbook.SaveCopyAs(@"C:\Users\Lenovo\Desktop\sample.xlsx");
workbook.Close();
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
workbook = null;
//worksheet = null;
//shapeSheet = null;
excelApp = null;
GC.Collect();
}
}
}
效果
最后由于此方法写入时非常耗时，我们可以采用先把数据转成字符串拷贝到剪贴板中，然后在复制到sheet表中，这样十万数据只要几秒钟
代码
public void ExportExcel()
{
System.Data.DataTable dt = new System.Data.DataTable();
dt.Columns.Add("序号", typeof(int));
dt.Columns.Add("数据1", typeof(int));
dt.Columns.Add("数据2", typeof(int));
Random r = new Random();
for (int i = 0; i < 20000; i++)
{
if (i == 6 || i == 16) continue;
dt.Rows.Add(i + 1, r.Next(50), r.Next(60));
}
StringBuilder strbu = new StringBuilder();
//写入标题
for (int i = 0; i < dt.Columns.Count; i++)
{
strbu.Append(dt.Columns[i].ColumnName.ToString() + "\t");
}
//加入换行字符串
strbu.Append(Environment.NewLine);
//写入内容
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
strbu.Append(dt.Rows[i][j].ToString() + "\t");
}
strbu.Append(Environment.NewLine);
}
System.Windows.Forms.Clipboard.SetText(strbu.ToString());
//新建EXCEL应用
Excel.Application excelApp = new Excel.Application();
if (excelApp == null)
return;
//设置为不可见，操作在后台执行，为 true 的话会打开 Excel
excelApp.Visible = false;
//初始化工作簿
Excel.Workbooks workbooks = excelApp.Workbooks;
//新增加一个工作簿，Add（）方法也可以直接传入参数 true
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
//同样是新增一个工作簿，但是会弹出保存对话框
//Excel.Workbook workbook = workbooks.Add(true);
//Excel.Worksheet worksheet = workbook.Worksheets[1];
Excel.Worksheet worksheet = workbook.Worksheets.Add();
//Excel.Range ranges1 = worksheet.Cells[1, 1];
//Excel.Range ranges2 = worksheet.Cells[dt.Rows.Count + 1, dt.Columns.Count];
//Excel.Range chartRage = worksheet.get_Range(ranges1, ranges2);
//Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[dt.Rows.Count+1, dt.Columns.Count]);
//chartRage.Copy(strbu.ToString());
//ranges1.Value = System.Windows.Forms.Clipboard.GetText();
//worksheet.Activate();
worksheet.Paste();
//worksheet.PasteSpecial(System.Windows.Forms.Clipboard.GetText(), false, false);
//新建一个 Excel 文件
string filePath = @"C:\Users\Lenovo\Desktop\" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss") + ".xlsx";
//创建文件
FileStream file = new FileStream(filePath, FileMode.CreateNew);
//关闭释放流，不然没办法写入数据
file.Close();
file.Dispose();
//保存写入的数据，这里还没有保存到磁盘
workbook.Saved = true;
//保存到指定的路径
workbook.SaveCopyAs(filePath);
}
private void WriteDataToExcel { Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application(); DataTable dataTable1 = this.GetTabel1();//获取表格2 Microsoft.Office.Interop.Excel.Application excelApp; Microsoft.Office.Interop.Excel._Workbook workBook; Microsoft.Office.Interop.Excel._Worksheet workSheet; Microsoft.Office.Interop.Excel._Worksheet workSheet1; object misValue = System.Reflection.Missing.Value; workBook = excelApp.Workbooks.Add(misValue);//加载模型 workSheet = (Microsoft.Office.Interop.Excel._Worksheet)workBook.Sheets.get_Item(1);//第一个工作薄。 workSheet1 = (Microsoft.Office.Interop.Excel._Worksheet)workBook.Sheets.get_Item(2); int rowIndex = 0; int colIndex = 0; foreach (DataRow row in dataTable.Rows) { rowIndex++; colIndex = 0; foreach (DataColumn col in dataTable.Columns) { colIndex++; workSheet.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString().Trim(); } } rowIndex = 0; colIndex = 0; foreach (DataRow row in dataTable1.Rows) { rowIndex++; colIndex = 0; foreach (DataColumn col in dataTable1.Columns) { colIndex++; workSheet1.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString().Trim(); } } workSheet.Protect("MyPassword", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing); //保护工作表 workSheet1.Protect("MyPassword", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing); /**/ excelApp.Visible = false; workBook.SaveAs(@"D:\outputFormDataBase1.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); dataTable = null; workBook.Close(true, misValue, misValue); excelApp.Quit(); PublicMethod.Kill(excelApp);//调用kill当前excel进程 }
有兴趣的可以看看 https://www.cnblogs.com/junshijie/p/5292087.html 这篇文章，里面有更详细如何操作EXCEL。
