• 设为首页
  • 点击收藏
  • 手机版
    手机扫一扫访问
    迪恩网络手机版
  • 关注官方公众号
    微信扫一扫关注
    公众号

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

原作者: [db:作者] 来自: [db:来源] 收藏 邀请

添加引用(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;
}

鲜花

握手

雷人

路过

鸡蛋
该文章已有0人参与评论

请发表评论

全部评论

专题导读
上一篇:
让JavaScript像C#一样支持Region发布时间:2022-07-13
下一篇:
关于微软C#中的CHART图表控件的简单使用【转】发布时间:2022-07-13
热门推荐
热门话题
阅读排行榜

扫描微信二维码

查看手机版网站

随时了解更新最新资讯

139-2527-9053

在线客服(服务时间 9:00~18:00)

在线QQ客服
地址:深圳市南山区西丽大学城创智工业园
电邮:jeky_zhao#qq.com
移动电话:139-2527-9053

Powered by 互联科技 X3.4© 2001-2213 极客世界.|Sitemap