做ASP.NET项目都会碰到ASP.NET报表问题,中国式报表的繁琐让我很是反感,可没法子啊,客户有需求!自然而然想用DCOM直接操作EXCEL,马马虎虎算是成了,虽说安全性不好,资源浪费严重,毕竟在局域网中使用,凑合吧。
不过总是讨厌在服务端那个没有结束的EXCEL进程……一定要把敌人杀死!google了一把,原来当今中外都有这个ASP.NET报表问题。解决方案也有,Windows XP下搞定,但我在WINDOWS 2003下搞了整整一天也没杀死,得,真想自个杀了。后来有人推荐了个控件ASPOSE解决ASP.NET报表问题,这玩意强,服务端都不用装EXCEL,效果不错!想偷懒的用这玩意,一个字——爽!
- private string subTotal(int row ,int num)
- {
- string r = (row+1).ToString();
- if( num == 0)//
- {
- return "=C"+r+"+F"+r+"-I"+r;
- }
- else
- return "=E"+r+"+H"+r+"-K"+r;
- }
- private string sumTotal(string scol,int firstrow,int num){
- string r = num.ToString();
- string fr = firstrow.ToString();
- return "=Sum("+scol+fr+":"+scol+r+")";
- }
- private void CreateExcelWorkbook(DataRow[] rows)
- {
- string strCurrentDir = Server.MapPath("..") + "\\TempReports\\";
- string licenseFile = MapPath("..") + "\\XML\\Aspose.Excel.lic";
- Excel excel = new Excel(licenseFile, this);
- ;
- string designerFile = strCurrentDir+"cangku2.xls";
- excel.Open(designerFile);
- Worksheet sheet = excel.Worksheets["Sheet1"];
- sheet.Name = "wuzi";
- Cells cells = sheet.Cells;
- int styleIndex;
- styleIndex = excel.Styles.Add();
- Aspose.Excel.Style stylecell = excel.Styles[styleIndex];
- stylecell.Borders[BorderType.LeftBorder].LineStyle=CellBorderType.Thin;
- stylecell.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
- stylecell.Borders[BorderType.RightBorder].LineStyle=CellBorderType.Thin;
- stylecell.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
- int iRow =4;
- foreach(DataRow row in rows)
- {
- cells[iRow,0].PutValue(row[0].ToString());
- cells[iRow,1].PutValue(row[1].ToString());
- cells[iRow,2].PutValue(row[2]);
- cells[iRow,3].PutValue( row[3]);
- cells[iRow,4].PutValue(row[4]);
- cells[iRow,5].PutValue(row[5]);
- cells[iRow,6].PutValue(row[3]);
- cells[iRow,7].PutValue(row[6]);
- cells[iRow,8].PutValue(row[7]);
- cells[iRow,9].PutValue(row[3]);
- cells[iRow,10].PutValue(row[8]);
- cells[iRow,11].Formula=subTotal(iRow,0);
- cells[iRow,12].PutValue(row[3]);
- cells[iRow,13].Formula=subTotal(iRow,1);
- iRow++;
- }
- string zj="总计";
- cells[iRow,3].PutValue(zj);
- cells[iRow,6].PutValue(zj);
- cells[iRow,9].PutValue(zj);
- cells[iRow,12].PutValue(zj);
- cells[iRow,4].Formula=sumTotal("E",5,iRow);
- cells[iRow,7].Formula=sumTotal("H",5,iRow);
- cells[iRow,10].Formula=sumTotal("K",5,iRow);
- cells[iRow,13].Formula=sumTotal("N",5,iRow);
- cells[1,8].PutValue(cpStartDate.SelectedDate.ToShortDateString());
- cells[1,12].PutValue(cpEndDate.SelectedDate.ToShortDateString());
- Range range = cells.CreateRange(4,0,iRow-4+1,14);
- range.Style = stylecell;
- for(int i = 0; i < excel.Worksheets.Count ; i ++)
- {
- sheet = excel.Worksheets[i];
- if(sheet.Name != "wuzi")
- {
- excel.Worksheets.RemoveAt(i);
- i --;
- }
- }
- /*Response.Clear();
- Response.Buffer= true;
- Response.Charset="GB2312";
- this.Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");*/
- excel.Save(HttpUtility.UrlEncode("物资进出库汇总.xls",Encoding.UTF8), SaveType.OpenInBrowser,FileFormatType.Default,this.Response);
- }
【编辑推荐】