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