前一段做企业信息系统中的报表时,遇到一些问题:报表格式不规则,数据来源于多个库,原定的是将报表数据生成EXCEL文件,然后下载就可以了,但是客户要求能够在网页上对生成的报表进行预览。苦思解决方法,最后使用Excel的发布(PublishObjects)功能将Excel文件生成为Html文件。
以下是示例代码:
//建立Excel应用对象
string strExcelXTPath = Server.MapPath("模板.xlt");
Excel.Application xlapp = new Excel.ApplicationClass();
Excel.Workbook xBook = xlapp.Workbooks.Open(strExcelXTPath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
Excel.Worksheet xSheet = (Excel.Worksheet)xBook.Sheets[1];
Excel.Range Rang = null;
xlapp.Visible = false;
//.......充填数据代码
//保存
strFileNo = GetNoString();
strSaveName = strFileNo + "报表.xls";
strPath = Server.MapPath("Temp/" + strSaveName);
//发布
string strPathAs = Server.MapPath("Files/" + strFileNo + "预览.htm");
xlapp.ActiveWorkbook.PublishObjects.Add(Excel.XlSourceType.xlSourceSheet, strPathAs, "Sheet1", "", Excel.XlHtmlType.xlHtmlStatic, "", Missing.Value).Publish(Missing.Value);
//保存
xBook.Saved = true;
xBook.SaveAs(strPath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
//关闭应用程序
Rang = null;
xSheet = null;
xBook.Close(Missing.Value, Missing.Value, Missing.Value);
xlapp.Quit();
xBook = null;
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlapp);
xlapp = null;
GC.Collect();
//删除临时文件
if (File.Exists(strPath))
{
File.Delete(strPath);
}
//打开/下载该文件
Response.Write("<script language='JavaScript'>window.open('" + "Files/" + strFileNo + "预览.htm" + "')</script>");