When working on ASP.NET projects, you often encounter issues with ASP.NET reporting. The complexity of Chinese-style reports really annoys me, but alas, the client has demands! Naturally, I thought about using DCOM to directly manipulate Excel, and it worked decently enough. Although there are security concerns and significant resource wastage, since it's being used within a local network, it’s acceptable under these circumstances.
However, I still dislike those lingering Excel processes on the server... They must be terminated! After some Googling, I found that both domestic and international developers face this ASP.NET reporting issue. There are solutions available; they work fine on Windows XP, but despite spending an entire day trying on Windows 2003, I couldn't kill the process. Frustrating! Later, someone recommended a control called **ASPOSE** for solving ASP.NET report problems. It's powerful—no need to install Excel on the server, and the results are great! If you're looking to save time, use this—it's simply awesome!
```csharp
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 = "总计"; // Total
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);
}
```
Original article: 【Eden Network】http://www.edenw.com/tech/devdeloper/net/2010-07-16/4754.html