Asp.net生成Excel报表

  测试环境:windows2003,MSSQL2000,IIS6.0,Asp.net2.0

 需求:根据用户查询条件将满足要求的信息查询出来生成xls文件供用户下载.
       1.使用Excel组件生成xls文件,不采用CSV文件(逗号分割文件).
       2.下载操作时采用ajax方式进行,需要提示界面.
       3.需要将下载后的文件删除.

  设计的过程:
       1.使用Excel软件设计一个空的模版文件(temp.xls)被用.
       2.每次生成时,将temp.xls拷贝一份到临时目录,文件名称随机生成(ran.xls).
       3.根据用户提交的条件将数据填充到ran.xls文件中.
       4.下载ran.xls文件.
       5.将临时目录中的ran.xls文件删除.

分析:
A.过期文件删除处理:
     针对第5点,一般可以使用定时清理程序根据文件生成时间进行清理(比方一天前的文件则删除掉),不过考虑到生成的xls文件一般在100KB以内,而且这个导出操作不很频繁(只对VIP用户提供),那么这里采用下载后马上删除的方式来实现,开始尝试了如下下载代码:
//=======注意***以下代码不能得到预期效果==============
        Response.TransmitFile(filename);
        //重载WriteFile(filename,true)是把整个文件读入内存再输出,跟下面的BineryWrite(byte[])表现是一样的
        //Response.WriteFile(filename);//部分缓存文件
        //即使加了下面两句,程序的表现也是不稳定的
        //Response.Flush();
        //Response.Close();
        System.IO.File.Delete(filename);

//=========End==========

参考(http://forums.asp.net/t/1031479.aspx,)提到的在每次使用TransmitFile发送文件时,调用一次过期文件删除进程(按文件生成时间).考虑到TransmitFile能稳定的发送大文件,实际应用时建议按这种方式实施.

针对当前项目,这里采用一次性读入内存再逐步输出的方式进行
//===============目前采用的方式============

Response.ContentType = "application/ms-excel";
Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(savefilename, Encoding.GetEncoding("utf-8")));
Response.BinaryWrite(File.ReadAllBytes(filename));//直接将输出写入
Response.Flush();
System.IO.File.Delete(filename);

 B.使用ajax方式下载问题:
     用户点下载按钮后将下载按钮设置为不可用(防止多次点击),并且显示一个处理中的gif动画.
首先碰到的问题是使用ajax方式无法弹出文件下载对话框,后来找到使用iframe的形式来实现,相关的jquery代码大概是这样的,$("#frmDownload").attr("src","download.ashx?条件");.

    起初是将生成Excel表格,发送下载文件做在一个过程里面的,使用iframe方式时,从点了下载按钮后到弹出文件下载对话框为止,对应服务器的处理时间,这段时间内客户端需要将下载按钮butOK禁用,然后显示一个gif,当弹出下载对话框时,再将按钮恢复并且隐藏gif动画,尝试了在ifrmae.onload里捕获下载框弹出事件,结果发现iframe里如果加载的是html页面时会触发,但是当是文件下载时就没有触发,估计是文件下载时IE解释器不生成DOM模型,当然也就没有JS事件了.

  目前解决方法:
   把Excel生成跟服务器发出下信号(指上面下载代码那一部分)分开,即首先客户端发出请求,服务器收到后生成excel文件然后返回一段json格式的信息(成功与否,以及excel文件地址),客户端收到成功信息后隐藏gif动画,恢复按钮可用,然后设置iframe.src="下载地址",这样一来就解决了上面的问题.
  --另外可以考虑,照样使用iframe.onload事件来处理excel生成结束事件(隐藏动画,恢复按钮),不过服务器应该返回是一个包含的页面,即iframe里再套个iframe, 这个没测试过想来应该可性,这样就不需要将excel处理跟下载分成两步了.

 参考代码如下:
///****************ajax下载过程*************************

 

       $("#butOK").click(function(){
         $("#butOK").attr("disabled",true);
         $("#tip").html("正在处理请求...");
         $.getJSON("download.ashx?m=1",function(data){
           //这里是演示,实际应用应该根据data里的信息来决定下一步操作
           $("#frmDownload").attr("src","download.ashx?m=2");//附加到iframe下载
           $("#butOK").attr("disabled","");
           $("#tip").html("");
         });

         
       });
//********************代码结束******************

 C.Excel报表生成问题:

   首先这个报表包括表头(第一行是神鹰医药招商网--代理信息目录,第二行是http://www.gyzs.net/ 导出时间:******),第三行是字段名称,而且表格使用了样式以便看上去更加美观,这样一来使用OLEDB方式的Insert语句(按数据库方式处理)就会出错,Insert好象将第一行是字段,第二行就是数据行了,也不知道有没有办法解决.

  另外朋友提供了一个服务端导出xml格式的数据,然后在客户端使用VBA(Js操作Excel组件)生成表格的方式,可以避免在服务器上装Excel程序,配置权限,Excel注销(回收内参)等一系列问题,不过需要对Excel进行格式化(能否下在服务器xls模版再进行操作?),用户当前电脑Excel版本是否会对Js编写有要求,在Js中使用Excel组件是否有权限以及安全方面的问题等, 多需要逐一测试.

 

  目前解决方法:

   使用Miscrosoft Excel 10.0 Object Library (Office 2002)组件,(需要在服务器上安装并配置权限)
//*****************写excel文件参考代码**********************

            System.Data.DataTable dt = LoadData();//加载符合条件的数据

            Object miss = System.Reflection.Missing.Value;
            Excel.Application ExcelApp = new Excel.ApplicationClass();
            Excel.Workbooks wbooks = (Excel.Workbooks)ExcelApp.Workbooks;
            Excel.Workbook wbook = wbooks.Open(Path, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss);
            //获得第一个工作薄
            Excel.Worksheet wsheet = (Excel.Worksheet)wbook.Worksheets.get_Item(1);
            ExcelApp.Visible = false;
            ExcelApp.DisplayAlerts = false;
            wsheet.Cells[2, 1] = string.Format("信息来源:神鹰医药网〖http://www.gyzs.net/〗 导出时间:{0}", DateTime.Now.ToString("yyyy-MM-dd"));

            //填充具体的单元格
            //逐个填充
            //for (int i = 0; i < dt.Rows.Count; i++)
            //{
            //    for (int j = 1; j <= 6; j++)
            //    {
            //        wsheet.Cells[i + 4, j] = dt.Rows[i][j - 1];
            //    }
            //}
            //使用Rang填充
            object[,] arr = new object[dt.Rows.Count, dt.Columns.Count];
            for (int iRow = 0; iRow < dt.Rows.Count; iRow++)
            {
                for (int iCol = 0; iCol < dt.Columns.Count; iCol++)
                {
                    arr[iRow, iCol] = dt.Rows[iRow][iCol];
                }
            }
            Excel.Range range = wsheet.get_Range(wsheet.Cells[4, 1], wsheet.Cells[dt.Rows.Count + 4 - 1, dt.Columns.Count]);
            range.set_Value(miss, arr);
            ReleaseObject(range);
            //填充结束
            wbook.Close(true, miss, miss);
            ExcelApp.Quit();

            ReleaseObject(wsheet);
            ReleaseObject(wbook);
            ReleaseObject(wbooks);
            ReleaseObject(ExcelApp);

            GC.Collect();
            GC.WaitForPendingFinalizers();

 

//*******************结束***********************
  说明:Excel 首先是Applicate对象,代表了Excel应用程序,一个Applicate对应多个WorkBook(集合WorkBooks),一个WorkBook包含多张WorkSheet(工作表),每张表包括多个Cell(单元格式), 上面代码注释了逐个单元格填充数据的方式,目前使用的是按Range按区域(如A1-C3有9个单元格A1表示左上角的哪个,
上面使用Get_Range(Cell[LRow,LCol],Cell[RRow,RCol])获取需要填充的区域,而Object[,] arr是一个二纬数组,这些数据将被填充到这个区域(Range)里. 使用区域填充比逐个填充速度要快,3-4倍(我这里测试的信息)

附加部分:在客户端使用ActiveX方式将Table内容导入到Excel表格里

//*******需要权限,并且用户安装了合适版本的office

// 指定页面区域“单元格”内容导入Excel
function CellAreaExcel(objid) {
 var oXL = null;
 var oWB = null;
 var oSheet = null;
 try {
  oXL = new ActiveXObject("Excel.Application");
 } catch (e) {
  alert("Excel对象无法创建");
  return;
 }
 try {
  oWB = oXL.Workbooks.Add();
  oSheet = oWB.ActiveSheet;
  oSheet.Cells.NumberFormatLocal = "@";
  var Lenr = gridlist.rows.length;
  for (var i = 0; i < Lenr; i++) {
   var Lenc = $id(objid).rows(i).cells.length;
   for (var j = 0; j < Lenc; j++) {
    if (i == 0) {
     oSheet.Cells(i + 1, j + 1).Font.Bold = true;
     oSheet.Cells(i + 1, j + 1).ColumnWidth = $id(objid).rows(i).cells(j).clientWidth / 7;
    }
    oSheet.Cells(i + 1, j + 1).value = $id(objid).rows(i).cells(j).innerText;
   }
  }
  oXL.Visible = true;
 } catch (e) {
  alert("导出到excel出错");
 }

 oXL.Quit();
 oXL = null;
 oWB = null;
 oSheet = null;
 idTmr = window.setInterval("Cleanup();", 1);
}