ASP.NET操作EXCEL的总结篇

公元19XX年前，关于EXCEL的操作就如滔滔江水，连绵不绝，真正操作EXCEL我也是从去年下半年开始的，有些比较复杂的年度报表之类的，做起来也有点费力，不过还是都能画出来了，关于EXCEL的报表导出，考虑到导出耗时的问题我主要采用AJAX来做的，分别捕捉几个起止状态，给客户端提示3个状态：正在检索数据。。。---》准备导出数据。。。(只是从数据库成功取出，还没有读写excel文件)--》正在读写文件--》导出数据成功，当然如果哪一过程出错，都有对应的提示，只所以想到写这篇文章，主要是因为今年有个系统的部分EXCEL的操作也让我做，顺便结合之前操作EXCEL的经验作一下总结，可能也算不上什么，对于绝大多数来说也没什么技术含量，网上一搜一大把，但我想还是有必要总结一下，至少能给园子里的新手些许帮助，OK,Let's Go...

   一. 程序操作EXCEL的应用主要还是在统计报表方面，您可能会考虑读EXCEL模板，也可能会考虑没必要读模板，其实读不读模板都能达到一样的效果，看实际情况而用了。
       1. 读模板的话，首先模板存放在某个路径下，根据模板把从数据库里取出的数据写回EXCEL然后生成一个新的EXCEL存放都另一个路径以供下载，模板不变。
          我这里的EXCEL操作主要是在VS2005里的，VS2003也可以的，不过没怎么研究03里的操作(文章最后我会把05的示例下载地址贴上 那个demo里之前打包忘了放了一个生成数据的文件，刚放进去了，不加也是可以运行的，还有模板文件的数据稍微过滤了下重新放了部分对照看下）vs05中操作EXCEL直接引用.NET自带的COM组件，添加后项目的bin目录下会自动出现

Interop.Excel.dll这个DLL（需安装office2003 excel，下面的说明及示例都是基于office2003的，版本不同调用可能会不一样）
页面的命名空间引用 using Excel;
下面是调用模板的一段代码
代码如下:

<br>#region 使用模板导出Excel表 <br>case "ReportByTemp": <br>{ <br><br>DataView dv = Cache["ReportByTemp"] as DataView; <br>//建立一个Excel.Application的新进程 <br>Excel.Application app = new Excel.Application(); <br>if (app == null) <br>{ <br>return; <br>} <br>app.Visible = false; <br>app.UserControl = true; <br>Workbooks workbooks = app.Workbooks; <br>_Workbook workbook = workbooks.Add(template_path + "\\EXCEL测试模板.xls");//这里的Add方法里的参数就是模板的路径 <br>Sheets sheets = workbook.Worksheets; <br>_Worksheet worksheet = (_Worksheet)sheets.get_Item(1);//模板只有一个sheet表 <br>if (worksheet == null) <br>{ <br>return; <br>} <br><br>int rowNum = 0; <br>for (int i = 0; i < dv.Count; i++) <br>{ <br>rowNum = i + 1; <br>worksheet.Cells[3 + i, 1] = rowNum; <br>worksheet.Cells[3 + i, 2] = dv[i].Row[0].ToString(); <br>worksheet.Cells[3 + i, 3] = dv[i].Row[1].ToString(); <br><br>excelOperate.SetBold(worksheet, worksheet.Cells[3 + i, 1], worksheet.Cells[3 + i, 1]); //黑体 <br>excelOperate.SetHAlignCenter(worksheet, worksheet.Cells[3 + i, 1], worksheet.Cells[3 + i, 3]);//居中 <br>worksheet.get_Range(worksheet.Cells[3 + i, 1], worksheet.Cells[3 + i, 3]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); <br><br>} <br><br>tick = DateTime.Now.Ticks.ToString(); <br>save_path = temp_path + "\\" + tick + ".xls"; <br>workbook.SaveAs(save_path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); <br>excelOperate.Dispose(worksheet, workbook, app);//关闭Excel进程 <br><br>} <br>break; <br>#endregion <br><br>效果如下：<br><img alt="" src="https://img.gxlcms.com//Uploads-s/new/2019-09-19-201919/20110215005000398.jpg" border="0"><br><br>       2. 不读模板的话，调用的时候其实会继承一个空白模板，然后写入数据，程序画表头，最终达到一样的效果，程序如下：<br><span><u></u></span> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>#region 不使用模板生成Excel表 <br>case "ReportByNone": <br>{ <br><br>DataView dv = Cache["ReportByNone"] as DataView; <br>//建立一个Excel.Application的新进程 <br>Excel.Application app = new Excel.Application(); <br>if (app == null) <br>{ <br>return; <br>} <br>app.Visible = false; <br>app.UserControl = true; <br>Workbooks workbooks = app.Workbooks; <br>_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);//这里的Add方法里的参数就相当于继承了一个空模板(暂这样理解吧) <br>Sheets sheets = workbook.Worksheets; <br>_Worksheet worksheet = (_Worksheet)sheets.get_Item(1); <br>if (worksheet == null) <br>{ <br>return; <br>} <br><br>worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 3]).Merge(Missing.Value); //横向合并 <br>worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 1]).Value2 = "导出EXCEL测试一"; <br>excelOperate.SetBold(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1]); //黑体 <br>excelOperate.SetHAlignCenter(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1]);//居中 <br>excelOperate.SetBgColor(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1], System.Drawing.Color.Red);//背景色 <br>excelOperate.SetFontSize(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1], 16);//字体大小 <br>excelOperate.SetRowHeight(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1], 32.25);//行高 <br>worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 1]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);//黑色连续边框 <br><br>worksheet.Cells[2, 1] = "序号"; <br>worksheet.Cells[2, 2] = "公司"; <br>worksheet.Cells[2, 3] = "部门"; <br>excelOperate.SetBold(worksheet, worksheet.Cells[2, 1], worksheet.Cells[2, 3]); //黑体 <br>worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[2, 3]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); <br>excelOperate.SetHAlignRight(worksheet, worksheet.Cells[2, 1], worksheet.Cells[2, 3]); <br>excelOperate.SetBgColor(worksheet, worksheet.Cells[2, 1], worksheet.Cells[2, 3], System.Drawing.Color.Silver);//背景色 <br>int rowNum = 0; <br>for (int i = 0; i < dv.Count; i++) <br>{ <br>rowNum = i + 1; <br>worksheet.Cells[3 + i, 1] = rowNum; <br>worksheet.Cells[3 + i, 2] = dv[i].Row[0].ToString(); <br>worksheet.Cells[3 + i, 3] = dv[i].Row[1].ToString(); <br><br>excelOperate.SetBold(worksheet, worksheet.Cells[3 + i, 1], worksheet.Cells[3 + i, 1]); //黑体 <br>excelOperate.SetHAlignCenter(worksheet, worksheet.Cells[3 + i, 1], worksheet.Cells[3 + i, 3]);//居中 <br>worksheet.get_Range(worksheet.Cells[3 + i, 1], worksheet.Cells[3 + i, 3]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);//设置边框颜色，不然打印预览，会非常不雅观 <br><br>} <br>excelOperate.SetColumnWidth(worksheet, "A", 10); <br>excelOperate.SetColumnWidth(worksheet, "B", 20); <br>excelOperate.SetColumnWidth(worksheet, "C", 20); <br>worksheet.Name = "导出EXCEL测试一"; <br><br>tick = DateTime.Now.Ticks.ToString(); <br>save_path = temp_path + "\\"+ tick + ".xls"; <br>workbook.SaveAs(save_path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); <br>excelOperate.Dispose(worksheet, workbook, app);//关闭Excel进程 <br><br>} <br>break; <br><br>#endregion <br><br>效果如下：<br><img height="640" alt="" src="https://img.gxlcms.com//Uploads-s/new/2019-09-19-201919/20110215005054630.jpg" width="437" border="0"><br><br>以上我给了两个最简单的操作说明，下面详细说一下对于一些稍微复杂的报表的生成处理<br><br>      二. 对于复杂的EXCEL报表的生成处理，无非是纵向合并相同的数据行及嵌套纵向合并等一些操作，下面就几个具有针对性的报表作下说明.<br>            1.要生成相对复杂的EXCEL表，在从数据库取数据时，要注意先按照合理的要求排好序，有时候可能order by后面要跟好几个字段，而且这几个字段谁先谁后也要注意，因为这些会直接影响报表呈现的效果，比如你的EXCEL表要按月份统计国内外的项目，显示出来的时候要多个项目相同的人连续，那么排序就可能要这样order by 月份，项目类别，用户ID，项目ID（这是写好的视图，基于视图来检索的），这个排序的字段顺序就不能变了，变了的话就不太好生成想要的形式了，如下图：<br><img alt="" src="https://img.gxlcms.com//Uploads-s/new/2019-09-19-201919/20110215005054946.jpg" border="0"><br>这个也是动态画的，用了个简单的模板，模板就一个表头，没多大意义，除非表头很复杂而且在列表中不需要重画，考虑模板就比较好，向上面那个一月份国际的和其它月份的都是需要重画表头的。至于合并，如果不是嵌套的合并，我们可以在向模板循环写数据的时候直接控制，比如下面一个简单的写法：<br><span><u></u></span> 代码如下:<br>for (i = 0; i < table.Rows.Count; i++) <br>{ <br>bidName = table.Rows[index]["BIDNAME"].ToString(); <br>if (table.Rows[i]["BIDNAME"].ToString() == bidName) <br>{ <br>projNum++; <br>worksheet.Cells[5 + i, 2] = table.Rows[i]["PROJNO"]; <br>worksheet.Cells[5 + i, 3] = table.Rows[i]["PROJNAME"]; <br>worksheet.Cells[5 + i, 4] = table.Rows[i]["STAT_DATE"]; <br>worksheet.Cells[5 + i, 5] = table.Rows[i]["PROJTYPE"]; <br>worksheet.Cells[5 + i, 6] = table.Rows[i]["CONTENT"]; <br>worksheet.Cells[5 + i, 7] = table.Rows[i]["OPENDT"]; <br>worksheet.Cells[5 + i, 8] = table.Rows[i]["OPENADDRESS"]; <br>worksheet.Cells[5 + i, 9] = table.Rows[i]["REV_DATE"]; <br>worksheet.Cells[5 + i, 10] = table.Rows[i]["BID_UNIT"]; <br>worksheet.Cells[5 + i, 11] = table.Rows[i]["AGT_AMOUNT"]; <br>worksheet.Cells[5 + i, 12] = table.Rows[i]["CURRENCY"] + ":" + table.Rows[i]["BIDSER_AMOUNT"]; <br>worksheet.Cells[5 + i, 13] = table.Rows[i]["SENDDATE"]; <br>worksheet.Cells[5 + i, 14] = table.Rows[i]["CURRENCY"] + ":" + table.Rows[i]["BIDPRICE"]; <br>worksheet.Cells[5 + i, 15] = table.Rows[i]["BOOKAMOUNT"]; <br>worksheet.Cells[5 + i, 16] = table.Rows[i]["CURRENCY"] + ":" + table.Rows[i]["BAIL_AMOUNT"]; <br>worksheet.Cells[5 + i, 17] = table.Rows[i]["USERNAME"]; <br>worksheet.Cells[5 + i, 18] = table.Rows[i]["SECOND_USER"]; <br>worksheet.Cells[5 + i, 19] = ""; <br>worksheet.get_Range(worksheet.Cells[5 + i, 1], worksheet.Cells[5 + i, 19]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); <br>continue; <br>} <br><br>worksheet.get_Range(worksheet.Cells[5 + rowid, 1], worksheet.Cells[5 + i - 1, 1]).Merge(Missing.Value); //将第一列按投标单位合并 <br>worksheet.get_Range(worksheet.Cells[5 + rowid, 1], worksheet.Cells[5 + rowid, 1]).Value2 = bidName + "(" + projNum.ToString() + "个项目)";//合并后的单元格内容<br><br>合并单元格的时候也要注意一个问题，就是合并的单元格必须是为空的，不然在执行合并时，会提示“合并后的单元格的值将丢失”，具体不这样提示的，大致是这个意思，一般我们合并都单元格相同的内容，在合并前我们先保存那个值，再清空后合并，上面的代码中把worksheet.Cell[5+rowid,1]这里系列的单元格的值空出来了，没写数据，而且最后合并了再写值，避免了去循环清空。<br>     2.嵌套的合并向上面那样做可能控制比较麻烦，而且思路可能很混乱，我们可以考虑先循环填充所有的数据，在循环出来要合并的列，比如像下面的这张表<br><img height="652" alt="" src="https://img.gxlcms.com//Uploads-s/new/2019-09-19-201919/20110215005150472.jpg" width="1277" border="0"><br>先循环填充数据，如下：<br><span><u></u></span> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>int index = 0, rownum = 0; <br>string ProjNo = ""; <br>for (i = 0; i < table.Rows.Count; i++) <br>{ <br>ProjNo = table.Rows[index]["PROJNO"].ToString(); <br>if (table.Rows[i]["PROJNO"].ToString() == ProjNo) <br>{ <br>wksheet.Cells[3 + i, 1] = rownum + 1; <br>wksheet.Cells[3 + i, 2] = "'" + table.Rows[i]["PROJNO"]; //加上单引号保证以0开头的字符原样</li></ol></pre>输出 <br>wksheet.Cells[3 + i, 3] = "'" + table.Rows[i]["PROJNAME"]; <br>wksheet.Cells[3 + i, 4] = "'" + table.Rows[i]["PA_NAME"]; <br>wksheet.Cells[3 + i, 5] = "'" + table.Rows[i]["BIDER_NAME"]; <br>wksheet.Cells[3 + i, 6] = table.Rows[i]["BAIL_AMOUNT"]; <br>wksheet.Cells[3 + i, 7] = table.Rows[i]["NOT_BACK"]; <br>wksheet.get_Range(wksheet.Cells[3 + i, 1], wksheet.Cells[3 + i, 7]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); <br>continue; <br>} <br>index = i; <br>rownum++; <br>i--; <br>} <br> <br>下面合并前三列相同内容的单元： <br><span><u></u></span> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>//合并前三列操作 <br>int m = 1, rowid = 3, k; <br>string projName = ""; <br>for (k = 3; k <= i + 2; k++) <br>{ <br>if (Convert.ToInt32(wksheet.get_Range(wksheet.Cells[k, 1], wksheet.Cells[k, 1]).Value2) == m) <br>{ <br>ProjNo = wksheet.get_Range(wksheet.Cells[k, 2], wksheet.Cells[k, 2]).Value2.ToString(); <br>projName = wksheet.get_Range(wksheet.Cells[k, 3], wksheet.Cells[k, 3]).Value2.ToString(); <br>wksheet.get_Range(wksheet.Cells[k, 1], wksheet.Cells[k, 1]).Value2 = ""; <br>wksheet.get_Range(wksheet.Cells[k, 2], wksheet.Cells[k, 2]).Value2 = ""; <br>wksheet.get_Range(wksheet.Cells[k, 3], wksheet.Cells[k, 3]).Value2 = ""; <br>continue; <br>} <br>wksheet.get_Range(wksheet.Cells[rowid, 1], wksheet.Cells[k - 1, 1]).Merge(Missing.Value); <br>wksheet.get_Range(wksheet.Cells[rowid, 1], wksheet.Cells[rowid, 1]).Value2 = m; <br>wksheet.get_Range(wksheet.Cells[rowid, 2], wksheet.Cells[k - 1, 2]).Merge(Missing.Value); <br>wksheet.get_Range(wksheet.Cells[rowid, 2], wksheet.Cells[rowid, 2]).Value2 = "'" + ProjNo; <br>wksheet.get_Range(wksheet.Cells[rowid, 3], wksheet.Cells[k - 1, 3]).Merge(Missing.Value); <br>wksheet.get_Range(wksheet.Cells[rowid, 3], wksheet.Cells[rowid, 3]).Value2 = "'" + projName; <br>m++; <br>rowid = k; <br>k--; <br>} <br>//跳出循环后合并最后一个招标项目 <br>wksheet.get_Range(wksheet.Cells[rowid, 1], wksheet.Cells[k - 1, 1]).Merge(Missing.Value); <br>wksheet.get_Range(wksheet.Cells[rowid, 1], wksheet.Cells[rowid, 1]).Value2 = m; <br>wksheet.get_Range(wksheet.Cells[rowid, 2], wksheet.Cells[k - 1, 2]).Merge(Missing.Value); <br>wksheet.get_Range(wksheet.Cells[rowid, 2], wksheet.Cells[rowid, 2]).Value2 = "'" + ProjNo; <br>wksheet.get_Range(wksheet.Cells[rowid, 3], wksheet.Cells[k - 1, 3]).Merge(Missing.Value); <br>wksheet.get_Range(wksheet.Cells[rowid, 3], wksheet.Cells[rowid, 3]).Value2 = "'" + projName; <br> <br>下面合并标段列 <br><span><u></u></span> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>//合并标段列 <br>index = 0; rowid = 3; //重置变量 <br>string pa_name = string.Empty; //标段名称 <br>for (k = 3; k <= i + 2; k++) <br>{ <br>pa_name = table.Rows[index]["PA_NAME"].ToString(); <br>if (wksheet.get_Range(wksheet.Cells[k, 4], wksheet.Cells[k, 4]).Value2.ToString() == pa_name) <br>{ <br>wksheet.get_Range(wksheet.Cells[k, 4], wksheet.Cells[k, 4]).Value2 = ""; <br>continue; <br>} <br>wksheet.get_Range(wksheet.Cells[rowid, 4], wksheet.Cells[k - 1, 4]).Merge(Missing.Value); <br>wksheet.get_Range(wksheet.Cells[rowid, 4], wksheet.Cells[rowid, 4]).Value2 = "'" + pa_name; <br>index = k - 3; <br>rowid = k; <br>k--; <br>} <br>//退出循环时合并最后一个项目的标段 <br>wksheet.get_Range(wksheet.Cells[rowid, 4], wksheet.Cells[k - 1, 4]).Merge(Missing.Value); <br>wksheet.get_Range(wksheet.Cells[rowid, 4], wksheet.Cells[rowid, 4]).Value2 = "'" + pa_name; <br>tick = DateTime.Now.ToString("yyyyMMddhhmmss"); <br>save_path = temp_path + "\\" + tick + "保证金收退情况表.xls"; <br>Session["BailBackID"] = tick + "保证金收退情况表.xls"; <br>Session["_BailBack"] = "true"; <br>workbook.SaveAs(save_path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); <br>excelOperate.Dispose(worksheet, workbook, app);//关闭Excel进程 <br>//DownLoad(save_path); <br>//Page_Close(); <br> <br>当然，上面的操作中会进行好几次循环，在性能方面不太可取，园子里的兄弟也许会有更好的方法，小弟不吝赐教了<br>下面我们看下几个效果图：<br><img alt="" src="https://img.gxlcms.com//Uploads-s/new/2019-09-19-201919/20110215005801780.jpg" border="0"><br><br><img alt="" src="https://img.gxlcms.com//Uploads-s/new/2019-09-19-201919/20110215005801519.jpg" border="0"><br><br><img alt="" src="https://img.gxlcms.com//Uploads-s/new/2019-09-19-201919/20110215005801520.jpg" border="0"><br>（注意：这里提示的导出数据是指从数据库成功取出数据，还没有操作EXCEL对象，刚开始已经说过了，当然这个提示文字换成其它的也可以）<br><img alt="" src="https://img.gxlcms.com//Uploads-s/new/2019-09-19-201919/20110215005802828.jpg" border="0"><br><br><img height="472" alt="" src="https://img.gxlcms.com//Uploads-s/new/2019-09-19-201919/20110215005802590.jpg" width="871" border="0"><br><br>整个过程采用AJAX提示的，一来不刷新，二来导出时间比较长的话，可以给客户一个良好的体验效果，否可，用户一点导出按钮，半天没反应也没提示，客户就觉得怎么这么慢的，是不是你们程序有问题，指责一大堆，有了这么些交互提示信息，让客户多等几分钟也能承受。<br><br>     3.生成的表格包含多个sheet的操作，比如下面一种情况<br><img alt="" src="https://img.gxlcms.com//Uploads-s/new/2019-09-19-201919/20110215005802664.jpg" border="0"><br><br>绘制这张表的要求是根据选择某年的几月到几月，生成这个几个月的一个综合情况的sheet，然后分别生成这几个月的单独的sheet表，生成上面表的模板，包含两个sheet ，一个综合月份的sheet和一个单独月份的sheet，因为单独月份的sheet表现形式都是一样的，我们可以根据选择的月份个数Copy几个sheet就可以了<br><span><u></u></span> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>Workbooks workbooks = app.Workbooks; <br>_Workbook workbook = workbooks.Add(template_path + "\\招标单位年度招标情况逐月统计表.xls"); <br>Sheets sheets = workbook.Worksheets; <br>_Worksheet Yearsheet = (_Worksheet)sheets.get_Item(1); <br>_Worksheet worksheet = (_Worksheet)sheets.get_Item(2); <br>if (worksheet == null) <br>{ <br>return; <br>} <br>for (int i = 1; i < monthCount; i++) <br>worksheet.Copy(Missing.Value, workbook.Worksheets[2]);//月统计工作薄 <br> <br>Yearsheet的操作就不说了，和前面几个一样操作，关键是月份的sheet的生成，其实就是循环操作get_Item(i)，代码如下 <br><span><u></u></span> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>//////////////////////////////////////每月详细统计//////////////////////////////////// <br>int item_id = 2; <br>rowNum = 0; book_Amount = 0; index = 0; <br>bid_Amount = ""; bidser_Amount = ""; agent_Amount = 0;//清空变量 <br>_Worksheet ws = null; <br>for (int i = 0; i < tableMM.Rows.Count; i++) <br>{ <br>rowNum++; <br>Month = tableMM.Rows[index]["DATE_MONTH"].ToString(); <br>if (tableMM.Rows[i]["DATE_MONTH"].ToString() == Month) <br>{ <br>ws = (_Worksheet)sheets.get_Item(item_id); <br>ws.Cells[3 + rowNum - 1, 1] = rowNum; <br>ws.Cells[3 + rowNum - 1, 2] = tableMM.Rows[i]["PROJNO"]; <br>ws.Cells[3 + rowNum - 1, 3] = tableMM.Rows[i]["PROJNAME"]; <br>ws.Cells[3 + rowNum - 1, 4] = tableMM.Rows[i]["BID_TYPE"]; <br>ws.Cells[3 + rowNum - 1, 5] = tableMM.Rows[i]["BID_MODE"]; <br>ws.Cells[3 + rowNum - 1, 6] = tableMM.Rows[i]["OPENDT"]; <br>ws.Cells[3 + rowNum - 1, 7] = tableMM.Rows[i]["OPENADDRESS"]; <br>ws.Cells[3 + rowNum - 1, 8] = tableMM.Rows[i]["BID_UNIT"]; <br>ws.Cells[3 + rowNum - 1, 9] = tableMM.Rows[i]["NOTICE_NO"].ToString().Replace("神华国贸", ""); <br>ws.Cells[3 + rowNum - 1, 10] = tableMM.Rows[i]["BOOKAMOUNT"]; <br>ws.Cells[3 + rowNum - 1, 11] = tableMM.Rows[i]["BIDPRICE"] + "(万" + tableMM.Rows[i]["CURRENCY"] + ")"; <br>ws.Cells[3 + rowNum - 1, 12] = tableMM.Rows[i]["BIDSER_AMOUNT"] + "(万" + tableMM.Rows[i]["CURRENCY"]+")"; <br>ws.Cells[3 + rowNum - 1, 13] = tableMM.Rows[i]["AGT_AMOUNT"]; <br>ws.Cells[3 + rowNum - 1, 14] = ""; <br>ws.get_Range(ws.Cells[3 + rowNum - 1, 1], ws.Cells[3 + rowNum - 1, 14]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); <br>continue; <br>} <br>ws.Cells[1, 1] = year + "年" + bidName + GetMonth(Month) + "月份招标项目情况一览表"; <br>//每月合计 <br>sql = " SELECT CURRENCY, NVL(SUM(BIDPRICE),0) AS BIDPRICE,NVL(SUM(BOOKAMOUNT),0) AS BOOKAMOUNT,NVL(SUM(BIDSER_AMOUNT),0) AS BIDSER_AMOUNT,NVL(SUM(AGT_AMOUNT),0) AS AGT_AMOUNT FROM IBS_V_BID_MONTHLY_STAT" + SqlFilter + <br>" AND DATE_YEAR ='" + year + "' AND COMPANY_ID=" + biderID + " AND DATE_MONTH ='" + Month + "'" + <br>" GROUP BY CURRENCY"; <br>System.Data.DataTable dt1 = OracleHelper.RetDataTable(sql); <br>for (int m = 0; m < dt1.Rows.Count; m++) <br>{ <br>bid_Amount += dt1.Rows[m]["BIDPRICE"] + "(万"+dt1.Rows[m]["CURRENCY"] + ")\r\t"; <br>book_Amount += float.Parse(dt1.Rows[m]["BOOKAMOUNT"].ToString()); <br>bidser_Amount += dt1.Rows[m]["BIDSER_AMOUNT"] + "(万" + dt1.Rows[m]["CURRENCY"] + ")\r\t"; <br>agent_Amount += float.Parse(dt1.Rows[m]["AGT_AMOUNT"].ToString()); <br>} <br>ws.Cells[3 + rowNum - 1, 3] = "合 计"; <br>ws.Cells[3 + rowNum - 1, 10] = book_Amount; <br>ws.Cells[3 + rowNum - 1, 11] = bid_Amount; <br>ws.Cells[3 + rowNum - 1, 12] = bidser_Amount; <br>ws.Cells[3 + rowNum - 1, 13] = agent_Amount; <br>ws.get_Range(ws.Cells[3 + rowNum - 1, 1], ws.Cells[3 + rowNum - 1, 14]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); <br>ws.Name = GetMM(Month); <br>item_id++; <br>index = i; //汇总下一个月份的招标项目 <br>i--; <br>rowNum = 0; book_Amount = 0; <br>bid_Amount = ""; bidser_Amount = ""; agent_Amount = 0;//清空变量 <br>} <br>//跳出循环时进行最后一个月份的项目汇总 <br> <br>用的是oracle数据库，所以上面那个sql语句。。。 呵呵 <br>============================================================================================ <br>上面大致说得就差不多了，因为是不断循环的什么的，可能对于大的数据量读写来说，比较好性能，如果大家有什么更好的方法，可以指点下，为了弥补等待时间过长，所以才结合了AJAX来处理。 <br>最后我把做的一个小demo的链接帖出来给大家，还有一些空模板和对应生成的数据表给大家对照看下，尤其相对复杂一些的表画应该是能画出来的，主要看大家采用什么样的方法，能少循环一次就尽量少循环，呵呵~~~ <br>EXCEL模板读写说明 <br>http://www.justlike.com.cn/upfiles/template_xls.rar <br>http://www.justlike.com.cn/upfiles/ExcelFiles.rar <br>http://www.justlike.com.cn/upfiles/ExcelReportDemo.rar <br>（说明：最后弹出下载文件的一个页面一直想让其自动关掉，但是不行，如果不关掉，再点导出，不会弹出下载框，实际的处理中我们可以在导出旁边放个下载按钮，就像上面的效果图里那样，当然可以点导出的时候让其在网页中直接打开，点下载的时候再弹出下载框，但是直接打开的话，文件需要生成在虚拟目录下，不太安全，呵呵~~，看实际情况处理了） <br>========================================================================================== <br>今天补充说明下，关于那个调用ajax回调的效果，有个地方用到了所谓的“ajax嵌套调用”，如下 <br><br><span><u></u></span> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>function ExcelReportCallback(resp) <br>{ <br>if(resp.value == "OK") <br>{ <br>$('tipMsg').innerHTML = "<img border=\"0\" src=\"images/s_progressbar.gif\"><font color=#FF0000 style=font-weight:bold>准备导出数据,请稍等</font>"; <br>setTimeout("RedirectUrl()",1000);//延时体验 <br>} <br>else <br>if(resp.value == "NO") <br>{ <br>$('tipMsg').innerHTML = "<font color=#FF0000 style=font-weight:bold>没有找到符合该查询条件的数据</font>"; <br>$('btnExcel').disabled = false; <br>} <br>else <br>{ <br>$('tipMsg').innerHTML = "<font color=#FF0000 style=font-weight:bold>警告:导出数据出错</font>"; <br>$('btnExcel').disabled = false; <br>} <br><br>} <br> <br><span><u></u></span> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>function RedirectUrl() <br>{ <br>$('tipMsg').innerHTML = "<img border=\"0\" src=\"images/ajaxloading.gif\"><font color=#7fffd4 style=font-weight:bold>正在读写报表文件,请稍后</font>"; <br>var ajax = new ajax_request("ExcelReport.aspx?flag=ReportByTemp&"+Math.random(), "", "", ReportCallback); <br>function ReportCallback(resp) <br>{ <br>if(resp.value != "Error" && resp.value !="") <br>{ <br>$('btnExcel').disabled = false; <br>$('tipMsg').innerHTML = "<font color=#FF0000 style=font-weight:bold>数据导出成功!</font>"; <br>Open("XLS_DownLoad.aspx?path="+resp.value);//window.location.href = resp.value;// <br>} <br>else <br>{ <br>$('btnExcel').disabled = false; <br>$('tipMsg').innerHTML = "<font color=#FF0000 style=font-weight:bold>文件读写出错,请检查文件模板是否存在或对文件是否有读写权限!</font>"; <br>} <br>} <br>} <br>function Open(url) <br>{ <br>window.open(url,'newwindow','height=1,width=1,top=1500,left=1500,toolbar=no,menubar=no,scrollbars=yes,location=no,status=no') <br>} <br> <br>ExcelReportCallback(resp)原本是一个回调函数，但是里面调用了一个RedirectUrl()方法，这个方法又包含了一个回调函数，这样就形成了回调的嵌套，之所以这么做，是因为，第一个回调是处理从数据库取出数据成功与否，如果成功了跳转到画EXCEL的页面，这样的话会出现一个空白页等生成好后出现下载框，后来觉得是否可以嵌套一个回调来继续一次异步操作，这样就不会出现长时间等待的空白页面了，而是生成好EXCEL后返回地址，或者可以返回一个文件名到XLS_DownLoad.aspx页面直接下载，但是XLS_DownLoad.aspx也是要出现的，我尝试过让下载后这个页面自动关闭，无赖做不到，所以把Open()方法里的数据值调得让页面不显示，但是状态栏还是有显示的。 <br>到这里算是写完了，决定奢侈下，放到首页下：），总觉得首页的文章只有高手才能放，而且放到首页也是一种奢侈，希望对园子里的某些人有一定的帮助吧~~ <br>http://xiazai.jb51.net/201102/yuanma/ExcelReport.rar</li><li>            </li><li>                                </li></ol></pre></li></ol></pre></li></ol></pre></li></ol></pre></li></ol></pre></li></ol></pre></li></ol></pre>