利用COM组件导出Excel

最新推荐文章于 2023-02-23 10:02:11 发布

weixin_33727510

最新推荐文章于 2023-02-23 10:02:11 发布

阅读量149

点赞数

文章标签： runtime

原文链接：http://www.cnblogs.com/linbaoji/archive/2009/10/10/1580121.html

版权

2009-07-29 16:59

1,首先要导入Com文件Microsoft Excel 11.0 Object Library.

2,要添加Interop.Excel.dll文件

http://files.cnblogs.com/ghostljj/Interop.Excel.rar

DataTable dt = (DataTable)Session["CodeS"];

string[] arrHead = new string[] { "生产批号", "防伪码" };

ExportToExcel(dt.DefaultView, arrHead, excelname);

public static void ExportToExcel(DataView dv, string[] strArrHead, string filename)

{

HttpServerUtility Server = System.Web.HttpContext.Current.Server;

HttpResponse Response = System.Web.HttpContext.Current.Response;

DataTable dt = dv.Table;

if (dt == null) return;

Excel.Application xlApp = new Excel.Application();

if (xlApp == null)

{

// lblMsg.Text = "无法创建Excel对象，可能您的机子未安装Excel";

//lblMsg.Text = GetLocalResourceObject("noexcel").ToString();

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;

//写入Title

worksheet.Cells[1, 1] = filename;

range = (Excel.Range)worksheet.Cells[1, 1];

range.Font.Bold = true;

range.Font.Size = 18;

range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//居中

range = (Excel.Range)worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, strArrHead.Length]);

range.Select();

range.Merge(false);

//写入table header

for (int i = 0; i < strArrHead.Length; i++)

{

worksheet.Cells[2, i + 1] = strArrHead[i];

range = (Excel.Range)worksheet.Cells[2, i + 1];

//range.Interior.ColorIndex = 15;//背景颜色

range.Font.Bold = true;//粗体

range.Font.Size = 10;

range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//居中

//加边框

range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);

range.EntireColumn.AutoFit();//自动调整列宽

range.EntireRow.AutoFit();//自动调整行高

}

//写入table body

for (int r = 0; r < dt.Rows.Count; r++)

{

for (int i = 0; i < dt.Columns.Count; i++)

{

//if (i == 0)

//{

worksheet.Cells[r + 3, i + 1] = "'" + dt.Rows[r][i];

range = (Excel.Range)worksheet.Cells[r + 3, i + 1];

range.Font.Size = 10;//字体大小

range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

//加边框

range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);

range.EntireColumn.AutoFit();//自动调整列宽

//}

//else

//{

// worksheet.Cells[r + 3, i + 1] = dt.Rows[r][i];

// range = (Excel.Range)worksheet.Cells[r + 3, i + 1];

// range.Font.Size = 10;//字体大小

// range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

// //加边框

// range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);

// range.EntireColumn.AutoFit();//自动调整列宽

//}

}

rowRead++;

percent = ((float)(100 * rowRead)) / totalCount;

//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;

}

//string path = "/xls/" + filename + ".xls";

string path = filename + ".xls";

try

{

workbook.Saved = true;

workbook.SaveCopyAs(Server.MapPath(path));

}

catch (Exception ex)

{

//lblMsg.Text = "导出文件时出错,文件可能正被打开！n" + ex.Message;

//lblMsg.Text = GetLocalResourceObject("error").ToString() + "n" + ex.Message;

throw (ex);

}

workbooks.Close();

if (xlApp != null)

{

xlApp.Workbooks.Close();

xlApp.Quit();

int generation = System.GC.GetGeneration(xlApp);

System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);

xlApp = null;

System.GC.Collect(generation);

}

GC.Collect();//强行销毁

#region 强行杀死最近打开的Excel进程

System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL");

System.DateTime startTime = new DateTime();

int m, killId = 0;

for (m = 0; m < excelProc.Length; m++)

{

if (startTime < excelProc[m].StartTime)

{

startTime = excelProc[m].StartTime;

killId = m;

}

if (excelProc[killId].HasExited == false)

{

excelProc[killId].Kill();

}

#endregion

//提供下载

System.IO.FileInfo file = new System.IO.FileInfo(Server.MapPath(path));

Response.Clear();

Response.Charset = "UTF-8";

Response.ContentEncoding = System.Text.Encoding.UTF8;

// 添加头信息，为"文件下载/另存为"对话框指定默认文件名

Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));

// 添加头信息，指定文件大小，让浏览器能够显示下载进度

Response.AddHeader("Content-Length", file.Length.ToString());

// 指定返回的是一个不能被客户端读取的流，必须被下载

Response.ContentType = "Application/ms-excel";

// 把文件流发送到客户端

Response.WriteFile(file.FullName);

// 停止页面的执行

Response.End();

//BIClass.BusinessLogic.Util.ResponseFile(Page.Request, Page.Response, "ReportToExcel.xls"

// , System.Web.HttpRuntime.AppDomainAppPath + "XMLFilesEduceWordFiles" + this.Context.User.Identity.Name + ".xls", 1024000);

}

4.如果是放在IIS中,现在是不能导出的,还要配置一下

方案一:在Web.config中添加

<system.web>

<system.web>

方案二:

(1)在运行->dcomcnfg打开组件服务

(2) 在控制台根目录->组件服务->计算机->我的电脑->DCOM配置->Microsoft Excel应用程序->属性->安全

(3)启动和激活权限->使用自定义->添加一个ASPNET用户,还有打开本地启动和本地激活

访问权限->使用自定义->添加一个ASPNET用户,还有打开本地访问和远程访问

编辑时可能出现如下错误：检索 COM 类工厂中 CLSID 为{00024500-0000-0000-C000-000000000046} 的组件时失败，原因是出现以下错误: 80070005。

具体解决方法如下:

1:在服务器上安装office的Excel软件.

2:在"开始"->"运行"中输入dcomcnfg.exe启动"组件服务"

3:依次双击"组件服务"->"计算机"->"我的电脑"->"DCOM配置"

4:在"DCOM配置"中找到"Microsoft Excel 应用程序",在它上面点击右键,然后点击"属性",弹出"Microsoft Excel 应用程序属性"对话框

5:点击"标识"标签,选择"交互式用户"

6:点击"安全"标签,在"启动和激活权限"上点击"自定义",然后点击对应的"编辑"按钮,在弹出的"安全性"对话框中填加一个"NETWORK SERVICE"用户(注意要选择本计算机名),并给它赋予"本地启动"和"本地激活"权限.

7:依然是"安全"标签,在"访问权限"上点击"自定义",然后点击"编辑",在弹出的"安全性"对话框中也填加一个"NETWORK SERVICE"用户,然后赋予"本地访问"权限. 这样,我们便配置好了相应的Excel的DCOM权限. 注意:这是在WIN2003上配置的,在2000上,可能是配置ASPNET用户由于EXCEL是在服务器上打开的，所以应该写一个把导出数据保存在服务器上,然后再传递给客户端的方法,最后每次调用这个功能的时候再删除以前在服务器上所生成的所有Excel

转载于:https://www.cnblogs.com/linbaoji/archive/2009/10/10/1580121.html

weixin_33727510

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
利用COM组件导出Excel

2009-07-29 16:591,首先要导入Com文件Microsoft Excel 11.0 Object Library.2,要添加Interop.Excel.dll文件http://files.cnblogs.com/ghostljj/Interop.Excel.rar DataTable dt = (DataTable)Session["Code...
复制链接

扫一扫