公元19XX年前,关于EXCEL的操作就如滔滔江水,连绵不绝,真正操作EXCEL我也是从去年下半年开始的,有些比较复杂的年度报表之类的,做起来也有点费力,不过还是都能画出来了,关于EXCEL的报表导出,考虑到导出耗时的问题我主要采用AJAX来做的,分别捕捉几个起止状态,给客户端提示3个状态:正在检索数据。。。---》准备导出数据。。。(只是从数据库成功取出,还没有读写excel文件)--》正在读写文件--》导出数据成功,当然如果哪一过程出错,都有对应的提示,只所以想到写这篇文章,主要是因为今年有个系统的部分EXCEL的操作也让我做,顺便结合之前操作EXCEL的经验作一下总结,可能也算不上什么,对于绝大多数来说也没什么技术含量,网上一搜一大把,但我想还是有必要总结一下,至少能给园子里的新手些许帮助,OK,Let's Go...
1
#region
使用模板导出Excel表
2 case " ReportByTemp " : 3 { 4 5 DataView dv = Cache[ " ReportByTemp " ] as DataView; 6 // 建立一个Excel.Application的新进程 7 Excel.Application app = new Excel.Application(); 8 if (app == null ) 9 { 10 return ; 11 } 12 app.Visible = false ; 13 app.UserControl = true ; 14 Workbooks workbooks = app.Workbooks; 15 _Workbook workbook = workbooks.Add(template_path + " //EXCEL测试模板.xls " ); // 这里的Add方法里的参数就是模板的路径 16 Sheets sheets = workbook.Worksheets; 17 _Worksheet worksheet = (_Worksheet)sheets.get_Item( 1 ); // 模板只有一个sheet表 18 if (worksheet == null ) 19 { 20 return ; 21 } 22 23 int rowNum = 0 ; 24 for ( int i = 0 ; i < dv.Count; i ++ ) 25 { 26 rowNum = i + 1 ; 27 worksheet.Cells[ 3 + i, 1 ] = rowNum; 28 worksheet.Cells[ 3 + i, 2 ] = dv[i].Row[ 0 ].ToString(); 29 worksheet.Cells[ 3 + i, 3 ] = dv[i].Row[ 1 ].ToString(); 30 31 excelOperate.SetBold(worksheet, worksheet.Cells[ 3 + i, 1 ], worksheet.Cells[ 3 + i, 1 ]); // 黑体 32 excelOperate.SetHAlignCenter(worksheet, worksheet.Cells[ 3 + i, 1 ], worksheet.Cells[ 3 + i, 3 ]); // 居中 33 worksheet.get_Range(worksheet.Cells[ 3 + i, 1 ], worksheet.Cells[ 3 + i, 3 ]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); 34 35 } 36 37 tick = DateTime.Now.Ticks.ToString(); 38 save_path = temp_path + " // " + tick + " .xls " ; 39 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); 40 excelOperate.Dispose(worksheet, workbook, app); // 关闭Excel进程 41 42 } 43 break ; 44 #endregion 效果如下:
1
#region
不使用模板生成Excel表
2 case " ReportByNone " : 3 { 4 5 DataView dv = Cache[ " ReportByNone " ] as DataView; 6 // 建立一个Excel.Application的新进程 7 Excel.Application app = new Excel.Application(); 8 if (app == null ) 9 { 10 return ; 11 } 12 app.Visible = false ; 13 app.UserControl = true ; 14 Workbooks workbooks = app.Workbooks; 15 _Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet); // 这里的Add方法里的参数就相当于继承了一个空模板(暂这样理解吧) 16 Sheets sheets = workbook.Worksheets; 17 _Worksheet worksheet = (_Worksheet)sheets.get_Item( 1 ); 18 if (worksheet == null ) 19 { 20 return ; 21 } 22 23 worksheet.get_Range(worksheet.Cells[ 1 , 1 ], worksheet.Cells[ 1 , 3 ]).Merge(Missing.Value); // 横向合并 24 worksheet.get_Range(worksheet.Cells[ 1 , 1 ], worksheet.Cells[ 1 , 1 ]).Value2 = " 导出EXCEL测试一 " ; 25 excelOperate.SetBold(worksheet, worksheet.Cells[ 1 , 1 ], worksheet.Cells[ 1 , 1 ]); // 黑体 26 excelOperate.SetHAlignCenter(worksheet, worksheet.Cells[ 1 , 1 ], worksheet.Cells[ 1 , 1 ]); // 居中 27 excelOperate.SetBgColor(worksheet, worksheet.Cells[ 1 , 1 ], worksheet.Cells[ 1 , 1 ], System.Drawing.Color.Red); // 背景色 28 excelOperate.SetFontSize(worksheet, worksheet.Cells[ 1 , 1 ], worksheet.Cells[ 1 , 1 ], 16 ); // 字体大小 29 excelOperate.SetRowHeight(worksheet, worksheet.Cells[ 1 , 1 ], worksheet.Cells[ 1 , 1 ], 32.25 ); // 行高 30 worksheet.get_Range(worksheet.Cells[ 1 , 1 ], worksheet.Cells[ 1 , 1 ]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); // 黑色连续边框 31 32 worksheet.Cells[ 2 , 1 ] = " 序号 " ; 33 worksheet.Cells[ 2 , 2 ] = " 公司 " ; 34 worksheet.Cells[ 2 , 3 ] = " 部门 " ; 35 excelOperate.SetBold(worksheet, worksheet.Cells[ 2 , 1 ], worksheet.Cells[ 2 , 3 ]); // 黑体 36 worksheet.get_Range(worksheet.Cells[ 2 , 1 ], worksheet.Cells[ 2 , 3 ]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); 37 excelOperate.SetHAlignRight(worksheet, worksheet.Cells[ 2 , 1 ], worksheet.Cells[ 2 , 3 ]); 38 excelOperate.SetBgColor(worksheet, worksheet.Cells[ 2 , 1 ], worksheet.Cells[ 2 , 3 ], System.Drawing.Color.Silver); // 背景色 39 int rowNum = 0 ; 40 for ( int i = 0 ; i < dv.Count; i ++ ) 41 { 42 rowNum = i + 1 ; 43 worksheet.Cells[ 3 + i, 1 ] = rowNum; 44 worksheet.Cells[ 3 + i, 2 ] = dv[i].Row[ 0 ].ToString(); 45 worksheet.Cells[ 3 + i, 3 ] = dv[i].Row[ 1 ].ToString(); 46 47 excelOperate.SetBold(worksheet, worksheet.Cells[ 3 + i, 1 ], worksheet.Cells[ 3 + i, 1 ]); // 黑体 48 excelOperate.SetHAlignCenter(worksheet, worksheet.Cells[ 3 + i, 1 ], worksheet.Cells[ 3 + i, 3 ]); // 居中 49 worksheet.get_Range(worksheet.Cells[ 3 + i, 1 ], worksheet.Cells[ 3 + i, 3 ]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); // 设置边框颜色,不然打印预览,会非常不雅观 50 51 } 52 excelOperate.SetColumnWidth(worksheet, " A " , 10 ); 53 excelOperate.SetColumnWidth(worksheet, " B " , 20 ); 54 excelOperate.SetColumnWidth(worksheet, " C " , 20 ); 55 worksheet.Name = " 导出EXCEL测试一 " ; 56 57 tick = DateTime.Now.Ticks.ToString(); 58 save_path = temp_path + " // " + tick + " .xls " ; 59 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); 60 excelOperate.Dispose(worksheet, workbook, app); // 关闭Excel进程 61 62 } 63 break ; 64 65 #endregion 效果如下:
1
for
(i
=
0
; i
<
table.Rows.Count; i
++
)
2 { 3 bidName = table.Rows[index][ " BIDNAME " ].ToString(); 4 if (table.Rows[i][ " BIDNAME " ].ToString() == bidName) 5 { 6 projNum ++ ; 7 worksheet.Cells[ 5 + i, 2 ] = table.Rows[i][ " PROJNO " ]; 8 worksheet.Cells[ 5 + i, 3 ] = table.Rows[i][ " PROJNAME " ]; 9 worksheet.Cells[ 5 + i, 4 ] = table.Rows[i][ " STAT_DATE " ]; 10 worksheet.Cells[ 5 + i, 5 ] = table.Rows[i][ " PROJTYPE " ]; 11 worksheet.Cells[ 5 + i, 6 ] = table.Rows[i][ " CONTENT " ]; 12 worksheet.Cells[ 5 + i, 7 ] = table.Rows[i][ " OPENDT " ]; 13 worksheet.Cells[ 5 + i, 8 ] = table.Rows[i][ " OPENADDRESS " ]; 14 worksheet.Cells[ 5 + i, 9 ] = table.Rows[i][ " REV_DATE " ]; 15 worksheet.Cells[ 5 + i, 10 ] = table.Rows[i][ " BID_UNIT " ]; 16 worksheet.Cells[ 5 + i, 11 ] = table.Rows[i][ " AGT_AMOUNT " ]; 17 worksheet.Cells[ 5 + i, 12 ] = table.Rows[i][ " CURRENCY " ] + " : " + table.Rows[i][ " BIDSER_AMOUNT " ]; 18 worksheet.Cells[ 5 + i, 13 ] = table.Rows[i][ " SENDDATE " ]; 19 worksheet.Cells[ 5 + i, 14 ] = table.Rows[i][ " CURRENCY " ] + " : " + table.Rows[i][ " BIDPRICE " ]; 20 worksheet.Cells[ 5 + i, 15 ] = table.Rows[i][ " BOOKAMOUNT " ]; 21 worksheet.Cells[ 5 + i, 16 ] = table.Rows[i][ " CURRENCY " ] + " : " + table.Rows[i][ " BAIL_AMOUNT " ]; 22 worksheet.Cells[ 5 + i, 17 ] = table.Rows[i][ " USERNAME " ]; 23 worksheet.Cells[ 5 + i, 18 ] = table.Rows[i][ " SECOND_USER " ]; 24 worksheet.Cells[ 5 + i, 19 ] = "" ; 25 worksheet.get_Range(worksheet.Cells[ 5 + i, 1 ], worksheet.Cells[ 5 + i, 19 ]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); 26 continue ; 27 } 28 29 worksheet.get_Range(worksheet.Cells[ 5 + rowid, 1 ], worksheet.Cells[ 5 + i - 1 , 1 ]).Merge(Missing.Value); // 将第一列按投标单位合并 30 worksheet.get_Range(worksheet.Cells[ 5 + rowid, 1 ], worksheet.Cells[ 5 + rowid, 1 ]).Value2 = bidName + " ( " + projNum.ToString() + " 个项目) " ; // 合并后的单元格内容 合并单元格的时候也要注意一个问题,就是合并的单元格必须是为空的,不然在执行合并时,会提示“合并后的单元格的值将丢失”,具体不这样提示的,大致是这个意思,一般我们合并都单元格相同的内容,在合并前我们先保存那个值,再清空后合并,上面的代码中把worksheet.Cell[5+rowid,1]这里系列的单元格的值空出来了,没写数据,而且最后合并了再写值,避免了去循环清空。
1
int
index
=
0
, rownum
=
0
;
2 string ProjNo = "" ; 3 for (i = 0 ; i < table.Rows.Count; i ++ ) 4 { 5 ProjNo = table.Rows[index][ " PROJNO " ].ToString(); 6 if (table.Rows[i][ " PROJNO " ].ToString() == ProjNo) 7 { 8 wksheet.Cells[ 3 + i, 1 ] = rownum + 1 ; 9 wksheet.Cells[ 3 + i, 2 ] = " ' " + table.Rows[i][ " PROJNO " ]; // 加上单引号保证以0开头的字符原样输出 10 wksheet.Cells[ 3 + i, 3 ] = " ' " + table.Rows[i][ " PROJNAME " ]; 11 wksheet.Cells[ 3 + i, 4 ] = " ' " + table.Rows[i][ " PA_NAME " ]; 12 wksheet.Cells[ 3 + i, 5 ] = " ' " + table.Rows[i][ " BIDER_NAME " ]; 13 wksheet.Cells[ 3 + i, 6 ] = table.Rows[i][ " BAIL_AMOUNT " ]; 14 wksheet.Cells[ 3 + i, 7 ] = table.Rows[i][ " NOT_BACK " ]; 15 wksheet.get_Range(wksheet.Cells[ 3 + i, 1 ], wksheet.Cells[ 3 + i, 7 ]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); 16 continue ; 17 } 18 19 index = i; 20 rownum ++ ; 21 i -- ; 22 23 } 下面合并前三列相同内容的单元:
1
//
合并前三列操作
2 int m = 1 , rowid = 3 , k; 3 string projName = "" ; 4 for (k = 3 ; k <= i + 2 ; k ++ ) 5 { 6 if (Convert.ToInt32(wksheet.get_Range(wksheet.Cells[k, 1 ], wksheet.Cells[k, 1 ]).Value2) == m) 7 { 8 ProjNo = wksheet.get_Range(wksheet.Cells[k, 2 ], wksheet.Cells[k, 2 ]).Value2.ToString(); 9 projName = wksheet.get_Range(wksheet.Cells[k, 3 ], wksheet.Cells[k, 3 ]).Value2.ToString(); 10 wksheet.get_Range(wksheet.Cells[k, 1 ], wksheet.Cells[k, 1 ]).Value2 = "" ; 11 wksheet.get_Range(wksheet.Cells[k, 2 ], wksheet.Cells[k, 2 ]).Value2 = "" ; 12 wksheet.get_Range(wksheet.Cells[k, 3 ], wksheet.Cells[k, 3 ]).Value2 = "" ; 13 continue ; 14 } 15 wksheet.get_Range(wksheet.Cells[rowid, 1 ], wksheet.Cells[k - 1 , 1 ]).Merge(Missing.Value); 16 wksheet.get_Range(wksheet.Cells[rowid, 1 ], wksheet.Cells[rowid, 1 ]).Value2 = m; 17 18 wksheet.get_Range(wksheet.Cells[rowid, 2 ], wksheet.Cells[k - 1 , 2 ]).Merge(Missing.Value); 19 wksheet.get_Range(wksheet.Cells[rowid, 2 ], wksheet.Cells[rowid, 2 ]).Value2 = " ' " + ProjNo; 20 21 wksheet.get_Range(wksheet.Cells[rowid, 3 ], wksheet.Cells[k - 1 , 3 ]).Merge(Missing.Value); 22 wksheet.get_Range(wksheet.Cells[rowid, 3 ], wksheet.Cells[rowid, 3 ]).Value2 = " ' " + projName; 23 24 m ++ ; 25 rowid = k; 26 k -- ; 27 } 28 // 跳出循环后合并最后一个招标项目 29 30 wksheet.get_Range(wksheet.Cells[rowid, 1 ], wksheet.Cells[k - 1 , 1 ]).Merge(Missing.Value); 31 wksheet.get_Range(wksheet.Cells[rowid, 1 ], wksheet.Cells[rowid, 1 ]).Value2 = m; 32 33 wksheet.get_Range(wksheet.Cells[rowid, 2 ], wksheet.Cells[k - 1 , 2 ]).Merge(Missing.Value); 34 wksheet.get_Range(wksheet.Cells[rowid, 2 ], wksheet.Cells[rowid, 2 ]).Value2 = " ' " + ProjNo; 35 36 wksheet.get_Range(wksheet.Cells[rowid, 3 ], wksheet.Cells[k - 1 , 3 ]).Merge(Missing.Value); 37 wksheet.get_Range(wksheet.Cells[rowid, 3 ], wksheet.Cells[rowid, 3 ]).Value2 = " ' " + projName; 下面合并标段列
1
//
合并标段列
2 3 index = 0 ; rowid = 3 ; // 重置变量 4 string pa_name = string .Empty; // 标段名称 5 for (k = 3 ; k <= i + 2 ; k ++ ) 6 { 7 pa_name = table.Rows[index][ " PA_NAME " ].ToString(); 8 if (wksheet.get_Range(wksheet.Cells[k, 4 ], wksheet.Cells[k, 4 ]).Value2.ToString() == pa_name) 9 { 10 wksheet.get_Range(wksheet.Cells[k, 4 ], wksheet.Cells[k, 4 ]).Value2 = "" ; 11 continue ; 12 } 13 wksheet.get_Range(wksheet.Cells[rowid, 4 ], wksheet.Cells[k - 1 , 4 ]).Merge(Missing.Value); 14 wksheet.get_Range(wksheet.Cells[rowid, 4 ], wksheet.Cells[rowid, 4 ]).Value2 = " ' " + pa_name; 15 index = k - 3 ; 16 rowid = k; 17 k -- ; 18 19 } 20 // 退出循环时合并最后一个项目的标段 21 wksheet.get_Range(wksheet.Cells[rowid, 4 ], wksheet.Cells[k - 1 , 4 ]).Merge(Missing.Value); 22 wksheet.get_Range(wksheet.Cells[rowid, 4 ], wksheet.Cells[rowid, 4 ]).Value2 = " ' " + pa_name; 23 tick = DateTime.Now.ToString( " yyyyMMddhhmmss " ); 24 save_path = temp_path + " // " + tick + " 保证金收退情况表.xls " ; 25 Session[ " BailBackID " ] = tick + " 保证金收退情况表.xls " ; 26 Session[ " _BailBack " ] = " true " ; 27 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); 28 excelOperate.Dispose(worksheet, workbook, app); // 关闭Excel进程 29 // DownLoad(save_path); 30 // Page_Close(); 当然,上面的操作中会进行好几次循环,在性能方面不太可取,园子里的兄弟也许会有更好的方法,小弟不吝赐教了
1
Workbooks workbooks
=
app.Workbooks;
2 3 _Workbook workbook = workbooks.Add(template_path + " //招标单位年度招标情况逐月统计表.xls " ); 4 Sheets sheets = workbook.Worksheets; 5 _Worksheet Yearsheet = (_Worksheet)sheets.get_Item( 1 ); 6 _Worksheet worksheet = (_Worksheet)sheets.get_Item( 2 ); 7 if (worksheet == null ) 8 { 9 return ; 10 } 11 for ( int i = 1 ; i < monthCount; i ++ ) 12 worksheet.Copy(Missing.Value, workbook.Worksheets[ 2 ]); // 月统计工作薄
1
////////////////////////////////////
//每月详细统计
////////////////////////////////////
2 3 int item_id = 2 ; 4 rowNum = 0 ; book_Amount = 0 ; index = 0 ; 5 bid_Amount = "" ; bidser_Amount = "" ; agent_Amount = 0 ; // 清空变量 6 _Worksheet ws = null ; 7 for ( int i = 0 ; i < tableMM.Rows.Count; i ++ ) 8 { 9 rowNum ++ ; 10 Month = tableMM.Rows[index][ " DATE_MONTH " ].ToString(); 11 if (tableMM.Rows[i][ " DATE_MONTH " ].ToString() == Month) 12 { 13 ws = (_Worksheet)sheets.get_Item(item_id); 14 ws.Cells[ 3 + rowNum - 1 , 1 ] = rowNum; 15 ws.Cells[ 3 + rowNum - 1 , 2 ] = tableMM.Rows[i][ " PROJNO " ]; 16 ws.Cells[ 3 + rowNum - 1 , 3 ] = tableMM.Rows[i][ " PROJNAME " ]; 17 ws.Cells[ 3 + rowNum - 1 , 4 ] = tableMM.Rows[i][ " BID_TYPE " ]; 18 ws.Cells[ 3 + rowNum - 1 , 5 ] = tableMM.Rows[i][ " BID_MODE " ]; 19 ws.Cells[ 3 + rowNum - 1 , 6 ] = tableMM.Rows[i][ " OPENDT " ]; 20 ws.Cells[ 3 + rowNum - 1 , 7 ] = tableMM.Rows[i][ " OPENADDRESS " ]; 21 ws.Cells[ 3 + rowNum - 1 , 8 ] = tableMM.Rows[i][ " BID_UNIT " ]; 22 ws.Cells[ 3 + rowNum - 1 , 9 ] = tableMM.Rows[i][ " NOTICE_NO " ].ToString().Replace( " 神华国贸 " , "" ); 23 ws.Cells[ 3 + rowNum - 1 , 10 ] = tableMM.Rows[i][ " BOOKAMOUNT " ]; 24 ws.Cells[ 3 + rowNum - 1 , 11 ] = tableMM.Rows[i][ " BIDPRICE " ] + " (万 " + tableMM.Rows[i][ " CURRENCY " ] + " ) " ; 25 ws.Cells[ 3 + rowNum - 1 , 12 ] = tableMM.Rows[i][ " BIDSER_AMOUNT " ] + " (万 " + tableMM.Rows[i][ " CURRENCY " ] + " ) " ; 26 ws.Cells[ 3 + rowNum - 1 , 13 ] = tableMM.Rows[i][ " AGT_AMOUNT " ]; 27 ws.Cells[ 3 + rowNum - 1 , 14 ] = "" ; 28 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); 29 continue ; 30 } 31 32 ws.Cells[ 1 , 1 ] = year + " 年 " + bidName + GetMonth(Month) + " 月份招标项目情况一览表 " ; 33 34 // 每月合计 35 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 + 36 " AND DATE_YEAR =' " + year + " ' AND COMPANY_ID= " + biderID + " AND DATE_MONTH =' " + Month + " ' " + 37 " GROUP BY CURRENCY " ; 38 System.Data.DataTable dt1 = OracleHelper.RetDataTable(sql); 39 for ( int m = 0 ; m < dt1.Rows.Count; m ++ ) 40 { 41 bid_Amount += dt1.Rows[m][ " BIDPRICE " ] + " (万 " + dt1.Rows[m][ " CURRENCY " ] + " )/r/t " ; 42 book_Amount += float .Parse(dt1.Rows[m][ " BOOKAMOUNT " ].ToString()); 43 bidser_Amount += dt1.Rows[m][ " BIDSER_AMOUNT " ] + " (万 " + dt1.Rows[m][ " CURRENCY " ] + " )/r/t " ; 44 agent_Amount += float .Parse(dt1.Rows[m][ " AGT_AMOUNT " ].ToString()); 45 } 46 47 ws.Cells[ 3 + rowNum - 1 , 3 ] = " 合 计 " ; 48 ws.Cells[ 3 + rowNum - 1 , 10 ] = book_Amount; 49 ws.Cells[ 3 + rowNum - 1 , 11 ] = bid_Amount; 50 ws.Cells[ 3 + rowNum - 1 , 12 ] = bidser_Amount; 51 ws.Cells[ 3 + rowNum - 1 , 13 ] = agent_Amount; 52 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); 53 ws.Name = GetMM(Month); 54 55 item_id ++ ; 56 index = i; // 汇总下一个月份的招标项目 57 i -- ; 58 rowNum = 0 ; book_Amount = 0 ; 59 bid_Amount = "" ; bidser_Amount = "" ; agent_Amount = 0 ; // 清空变量 60 } 61 62 // 跳出循环时进行最后一个月份的项目汇总 用的是oracle数据库,所以上面那个sql语句。。。 呵呵
1
function
ExcelReportCallback(resp)
2 { 3 if (resp.value == " OK " ) 4 { 5 6 $( ' tipMsg').innerHTML = "<img border=/"0/" src=/"images/s_progressbar.gif/"><font color=#FF0000 style=font-weight:bold>准备导出数据,请稍等 ![]() ![]() 7 setTimeout( " RedirectUrl() " , 1000 ); // 延时体验 8 } 9 else 10 if (resp.value == " NO " ) 11 { 12 $( ' tipMsg').innerHTML = "<font color=#FF0000 style=font-weight:bold>没有找到符合该查询条件的数据</font>"; 13 $( ' btnExcel').disabled = false; 14 } 15 else 16 { 17 $( ' tipMsg').innerHTML = "<font color=#FF0000 style=font-weight:bold>警告:导出数据出错</font>"; 18 $( ' btnExcel').disabled = false; 19 } 20 21 22 }
1
function
RedirectUrl()
2 { 3 $( ' tipMsg').innerHTML = "<img border=/"0/" src=/"images/ajaxloading.gif/"><font color=#7fffd4 style=font-weight:bold>正在读写报表文件,请稍后 ![]() ![]() 4 var ajax = new ajax_request( " ExcelReport.aspx?flag=ReportByTemp& " + Math.random(), "" , "" , ReportCallback); 5 function ReportCallback(resp) 6 { 7 if (resp.value ! = " Error " && resp.value ! = "" ) 8 { 9 $( ' btnExcel').disabled = false; 10 $( ' tipMsg').innerHTML = "<font color=#FF0000 style=font-weight:bold>数据导出成功!</font>"; 11 Open( " XLS_DownLoad.aspx?path= " + resp.value); // window.location.href = resp.value; // 12 } 13 else 14 { 15 $( ' btnExcel').disabled = false; 16 $( ' tipMsg').innerHTML = "<font color=#FF0000 style=font-weight:bold>文件读写出错,请检查文件模板是否存在或对文件是否有读写权限!</font>"; 17 } 18 } 19 20 } 21 22 function Open(url) 23 { 24 window.open(url, ' newwindow','height=1,width=1,top=1500,left=1500,toolbar=no,menubar=no,scrollbars=yes,location=no,status=no') 25 } ExcelReportCallback(resp)原本是一个回调函数,但是里面调用了一个RedirectUrl()方法,这个方法又包含了一个回调函数,这样就形成了回调的嵌套,之所以这么做,是因为,第一个回调是处理从数据库取出数据成功与否,如果成功了跳转到画EXCEL的页面,这样的话会出现一个空白页等生成好后出现下载框,后来觉得是否可以嵌套一个回调来继续一次异步操作,这样就不会出现长时间等待的空白页面了,而是生成好EXCEL后返回地址,或者可以返回一个文件名到XLS_DownLoad.aspx页面直接下载,但是XLS_DownLoad.aspx也是要出现的,我尝试过让下载后这个页面自动关闭,无赖做不到,所以把Open()方法里的数据值调得让页面不显示,但是状态栏还是有显示的。
注意两个地方:
2、在页面下载用:
private void DownLoad(string path) Response.Clear(); //将下载保存对话框指定默认的文件名添加到HTTP头中 Response.AddHeader("Content-Disposition", "attachment; filename=" + System.Web.HttpUtility.UrlEncode(file.Name, System.Text.Encoding.UTF8));//避免中文出现乱码现象 //在header中指定文件的大小,使浏览器能显示下载过程 //设置输出流的 HTTP MIME 类型 // 发送文件流到客户端 Response.End(); }
3、类文件
using System; /// <summary> public ExcelOperate() /// <summary> /// <summary> }
} /// <summary> } /// <summary>
/// <summary> /// <summary> /// <summary> /// <summary> } /// <summary> /// <summary>
/// <summary>
/// <summary> /// <summary> /// <summary>
CurExcel.Quit(); }
|