这是一个在.net中Excel基本的操作,代码有参考价值. 但是缺点是写入速度太慢.
1. 首先要导入Com文件Microsoft Excel 11.0 Object Library. 或者昌我提供的下载资源的Excel.dll,建议使用我的那一个Excel.dll,是Office2000 的,兼容性比较好一些.
2. 代码如下
- /// <summary>
- /// 导出Excel
- /// </summary>
- /// <param name="dt">要导出的DataTable</param>
- public void ExportToExcel(System.Data.DataTable dt)
- {
- if (dt == null) return;
- Excel.Application xlApp = new Excel.Application();
- if (xlApp == null)
- {
- lblMsg.Text = "无法创建Excel对象,可能您的机子未安装Excel";
- return;
- }
- Excel.Workbooks workbooks = xlApp.Workbooks;
- Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
- Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
- Excel.Range range = null;
- long totalCount = dt.Rows.Count;
- long rowRead = 0;
- float percent = 0;
- //写入标题
- for (int i = 0; i < dt.Columns.Count; i++)
- {
- worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
- range = (Excel.Range)worksheet.Cells[1, i + 1];
- //range.Interior.ColorIndex = 15;//背景颜色
- range.Font.Bold = true;//粗体
- range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//居中
- //加边框
- range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
- //range.ColumnWidth = 4.63;//设置列宽
- //range.EntireColumn.AutoFit();//自动调整列宽
- //r1.EntireRow.AutoFit();//自动调整行高
- }
- //写入内容
- for (int r = 0; r < dt.Rows.Count; r++)
- {
- for (int i = 0; i < dt.Columns.Count; i++)
- {
- worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i];
- range = (Excel.Range)worksheet.Cells[r + 2, i + 1];
- range.Font.Size = 9;//字体大小
- //加边框
- range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
- range.EntireColumn.AutoFit();//自动调整列宽
- }
- rowRead++;
- percent = ((float)(100 * rowRead)) / totalCount;
- System.Windows.Forms.Application.DoEvents();
- }
- range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
- if (dt.Columns.Count > 1)
- {
- range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
- }
- try
- {
- workbook.Saved = true;
- workbook.SaveCopyAs(System.Web.HttpRuntime.AppDomainAppPath + "XMLFiles//EduceWordFiles//" + this.Context.User.Identity.Name + ".xls");
- }
- catch (Exception ex)
- {
- lblMsg.Text = "导出文件时出错,文件可能正被打开!/n" + ex.Message;
- }
- xlApp.Quit();
- GC.Collect();//强行销毁
3.如果是放在IIS中,现在是不能到出的,还要配置一下
方案一:在Web.config中添加
<system.web>
<identity impersonate="true" userName="管理员名" password="密码" />
<system.web>
方案二:
(1)在运行->dcomcnfg打开组件服务
(2) 在控制台根目录->组件服务->计算机->我的电脑->DCOM配置->MicrosoftExcel应用程序->属性->安全
(3)启动和激活权限->使用自定义->添加一个ASPNET用户,还有打开本地启动和本地激活
访问权限->使用自定义->添加一个ASPNET用户,还有打开本地访问和远程访问