C#利用Microsoft.Office.Interop.Excel导出数据到Excel

拼搏现实的明天。 2022-03-08 05:24 292阅读 0赞

### 添加引用(VS2013位于`C:\Program Files (x86)\Microsoft Visual Studio 12.0\Visual Studio Tools for Office\PIA\Office15`文件夹下) ###

本案例是直接将DataTable里面的数据导入到Excel内，所以需将数据转换成DataTable数据类型才可以

using Microsoft.Office.Interop.Excel;
    using System.Reflection;

public void DataTableToExcel(System.Data.DataTable dataTable, string strTitle, string[] strHead, int[] columnWidth)
    {
    	int nMax = dataTable.Rows.Count;
    	int nMin = 0;
    	int rowCount = nMax - nMin + 1;//总行数
    	const int columnCount = 7;//总列数
    
    	//创建Excel对象
    	Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
    	//新建工作簿
    	Workbook workBook = excelApp.Workbooks.Add(true);
    	//新建工作表
    	Worksheet worksheet = workBook.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
    
    	设置标题
    	Microsoft.Office.Interop.Excel.Range titleRange = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1, columnCount]];//选取单元格
    
    	titleRange.Merge(true);                                         //合并单元格
    	titleRange.Value2 = strTitle;                                   //设置单元格内文本
    	titleRange.Font.Name = "微软雅黑";                              //设置字体
    	titleRange.Font.Size = 18;                                      //字体大小
    	titleRange.Font.Bold = true;                                    //加粗显示
    	titleRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;       //水平居中
    	titleRange.VerticalAlignment = XlVAlign.xlVAlignCenter;         //垂直居中
    	titleRange.Borders.LineStyle = XlLineStyle.xlContinuous;        //设置边框
    	titleRange.Borders.Weight = XlBorderWeight.xlThin;              //边框常规粗细
    
    	//设置表头
    	//string[] strHead = new string[columnCount] { "id", "productUid", "productBarcode", "checkData", "checkResult", "creater", "checkDate" };
    	//int[] columnWidth = new int[columnCount] { 8, 24, 24, 22, 10, 14, 20 };
    	for (int i = 0; i < columnCount; i++)
    	{
    		Microsoft.Office.Interop.Excel.Range headRange = worksheet.Cells[2, i + 1] as Microsoft.Office.Interop.Excel.Range;//获取表头单元格,不用标题则从1开始
    		headRange.Value2 = strHead[i];                              //设置单元格文本
    		headRange.Font.Name = "Consolas";                           //设置字体
    		headRange.Font.Size = 13;                                   //字体大小
    		headRange.Font.Bold = true;                                 //加粗显示
    		headRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;    //水平居中
    		headRange.VerticalAlignment = XlVAlign.xlVAlignCenter;      //垂直居中
    		headRange.ColumnWidth = columnWidth[i];                     //设置列宽
    		headRange.Borders.LineStyle = XlLineStyle.xlContinuous;     //设置边框
    		headRange.Borders.Weight = XlBorderWeight.xlThin;           //边框常规粗细
    	}
    
    	//设置每列格式
    	for (int i = 0; i < columnCount; i++)
    	{
    		Microsoft.Office.Interop.Excel.Range contentRange = worksheet.Range[worksheet.Cells[2, i + 1], worksheet.Cells[rowCount - 1 + 3, i + 1]];//不用标题则从第二行开始
    		contentRange.Font.Name = "Consolas";
    		contentRange.Font.Size = 10;
    		contentRange.HorizontalAlignment = XlHAlign.xlHAlignCenter; //水平居中
    		contentRange.VerticalAlignment = XlVAlign.xlVAlignCenter;   //垂直居中
    		contentRange.Borders.LineStyle = XlLineStyle.xlContinuous;  //设置边框
    		contentRange.Borders.Weight = XlBorderWeight.xlThin;        //边框常规粗细
    		contentRange.WrapText = true;                               //自动换行
    		contentRange.NumberFormatLocal = "@";                       //文本格式
    	}
    
    	//填充数据
    	for (int i = 0; i < dataTable.Rows.Count; i++)
    	{
    		DataRow row = dataTable.Rows[i];
    		for (int j = 0; j < dataTable.Columns.Count; j++)
    		{
    			excelApp.Cells[i + 3, j + 1] = row[j].ToString();
    		}
    	}
    	//设置Excel可见
    	excelApp.Visible = true;
    }