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

来源：互联网发布：北京好的体检医院知乎编辑：程序博客网时间：2024/05/22 14:16

    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);