asp.net报表解决方法(第一次使用cnblogs的blog)

    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);
}
   做ASP.NET项目时,都会碰到报表问题,中国式报表的繁琐让我很是反感,可没法子啊,客户有需求!自然而然想用DCOM直接操作EXCEL,马马虎虎算是成了,虽说安全性不好,资源浪费严重,毕竟在局域网中使用,凑合吧,但我讨厌在服务端那个没有结束的EXCEL进程,杀死,一定要把敌人杀死! google了一把,当今中外都有这个问题,解决方案也有,WINXP下杀了,但我在WINDOWS 2003下搞了整整一天也没杀死,得,真想自个杀了。后来有人推荐了个控件ASPOSE,这玩意强,服务端都不用装EXCEL,效果不错!想偷懒的用这玩意,一个字爽!

posted on 2005-11-10 15:36  蔚然之家  阅读(...)  评论(...编辑  收藏

导航