添加引用(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;
}
|
请发表评论