继续在上篇《使用Aspose.Cell控件实现Excel高难度报表的生成（一）》随笔基础上，研究探讨基于模板的Aspose.cell报表实现，其中提到了下面两种报表的界面，如下所示：
或者这样的报表格式
首先来分析第一种报表，这个其实还是比较固定的二维表，我们只要绑定相关的信息即可，设计模板如下所示：
实际生成的报表如下所示：
实现的代码其实不复杂，如下所示：
private DataTable GetTable(string sql)
{
Database db = DatabaseFactory.CreateDatabase();
DbCommand command = db.GetSqlStringCommand(sql);
return db.ExecuteDataSet(command).Tables[ 0 ];
}
private void btnAllMonthReport_Click( object sender, EventArgs e)
{
string sql = @" Select [LastCount] as LC, [LastMoney] as LM, [CurrentInCount] as CIC, [CurrentInMoney] as CIM,
[CurrentOutCount] as COC, [CurrentOutMoney] as COM, [CurrentCount] as CC, [CurrentMoney] as CM,
YearMonth,ItemName
from TB_ReportMonthCheckOut " ;
DataTable dtBigType = GetTable(sql + " where ReportType =3 " );
dtBigType.TableName = " BigType " ;
if (dtBigType.Rows.Count == 0 )
return ;
DataTable dtItemType = GetTable(sql + " where ReportType =3 " );
dtItemType.TableName = " ItemType " ;
WorkbookDesigner designer = new WorkbookDesigner();
string path = System.IO.Path.Combine(Application.StartupPath, " Report2-1.xls " );
designer.Open(path);
designer.SetDataSource(dtBigType);
designer.SetDataSource(dtItemType);
designer.SetDataSource( " YearMonth " , dtBigType.Rows[ 0 ][ " YearMonth " ].ToString());
designer.Process();
// Save the excel file
string fileToSave = FileDialogHelper.SaveExcel();
if (File.Exists(fileToSave))
{
File.Delete(fileToSave);
}
designer.Save(fileToSave, FileFormatType.Excel2003);
Process.Start(fileToSave);
}
Database db = DatabaseFactory.CreateDatabase();
DbCommand command = db.GetSqlStringCommand(sql);
return db.ExecuteDataSet(command).Tables[ 0 ];
}
private void btnAllMonthReport_Click( object sender, EventArgs e)
{
string sql = @" Select [LastCount] as LC, [LastMoney] as LM, [CurrentInCount] as CIC, [CurrentInMoney] as CIM,
[CurrentOutCount] as COC, [CurrentOutMoney] as COM, [CurrentCount] as CC, [CurrentMoney] as CM,
YearMonth,ItemName
from TB_ReportMonthCheckOut " ;
DataTable dtBigType = GetTable(sql + " where ReportType =3 " );
dtBigType.TableName = " BigType " ;
if (dtBigType.Rows.Count == 0 )
return ;
DataTable dtItemType = GetTable(sql + " where ReportType =3 " );
dtItemType.TableName = " ItemType " ;
WorkbookDesigner designer = new WorkbookDesigner();
string path = System.IO.Path.Combine(Application.StartupPath, " Report2-1.xls " );
designer.Open(path);
designer.SetDataSource(dtBigType);
designer.SetDataSource(dtItemType);
designer.SetDataSource( " YearMonth " , dtBigType.Rows[ 0 ][ " YearMonth " ].ToString());
designer.Process();
// Save the excel file
string fileToSave = FileDialogHelper.SaveExcel();
if (File.Exists(fileToSave))
{
File.Delete(fileToSave);
}
designer.Save(fileToSave, FileFormatType.Excel2003);
Process.Start(fileToSave);
}
通过绑定两个不同的DataTable对象，然后引用他的属性即可，行会自动增加以适应实际的数据，并且对象变量&=$YearMonth也正常显示了，注意一点就是，所有使用变量的地方，都必须在一个独立的Excel单元格中，否则不能解析出来。另外上图的红色圆圈里面表示，汇总的函数，会自动根据行列的增加，自动调整引用，这真是我们需要的。
出库单的实现也差不多，实现代码如下所示：
string
TakeOutBill
=
Path.Combine(Application.StartupPath,
"
TakeOutBill.xls
"
);
WorkbookDesigner designer = new WorkbookDesigner();
designer.Open(TakeOutBill);
designer.SetDataSource( " TakeOutDate " , DateTime.Now.ToString( " yyyy-MM-dd " ));
designer.SetDataSource( " WareHouse " , this .txtWareHouse.Text);
designer.SetDataSource( " Manager " , this .txtCreator.Text);
designer.SetDataSource( " CostCenter " , this .txtCostCenter.Text);
designer.SetDataSource( " Dept " , this .txtDept.Text);
string columns = " Start|int,ItemNo,ItemName,Specification,Unit,Price|decimal,Count|int " ;
DataTable dt = DataTableHelper.CreateTable(columns);
dt.TableName = " Detail " ;
DataRow row = null ;
for ( int i = 0 ; i < this .lvwDetail.Items.Count; i ++ )
{
PurchaseDetailInfo info = this .lvwDetail.Items[i].Tag as PurchaseDetailInfo;
if (info != null )
{
row = dt.NewRow();
row[ " Start " ] = (i + 1 );
row[ " ItemNo " ] = info.ItemNo;
row[ " ItemName " ] = info.ItemName;
row[ " Specification " ] = info.Specification;
row[ " Unit " ] = info.Unit;
row[ " Price " ] = info.Price;
row[ " Count " ] = Math.Abs(info.Quantity);
dt.Rows.Add(row);
}
}
designer.SetDataSource(dt);
designer.Process();
string fileToSave = FileDialogHelper.SaveExcel();
if (File.Exists(fileToSave))
{
File.Delete(fileToSave);
}
designer.Save(fileToSave, FileFormatType.Excel2003);
Process.Start(fileToSave);
WorkbookDesigner designer = new WorkbookDesigner();
designer.Open(TakeOutBill);
designer.SetDataSource( " TakeOutDate " , DateTime.Now.ToString( " yyyy-MM-dd " ));
designer.SetDataSource( " WareHouse " , this .txtWareHouse.Text);
designer.SetDataSource( " Manager " , this .txtCreator.Text);
designer.SetDataSource( " CostCenter " , this .txtCostCenter.Text);
designer.SetDataSource( " Dept " , this .txtDept.Text);
string columns = " Start|int,ItemNo,ItemName,Specification,Unit,Price|decimal,Count|int " ;
DataTable dt = DataTableHelper.CreateTable(columns);
dt.TableName = " Detail " ;
DataRow row = null ;
for ( int i = 0 ; i < this .lvwDetail.Items.Count; i ++ )
{
PurchaseDetailInfo info = this .lvwDetail.Items[i].Tag as PurchaseDetailInfo;
if (info != null )
{
row = dt.NewRow();
row[ " Start " ] = (i + 1 );
row[ " ItemNo " ] = info.ItemNo;
row[ " ItemName " ] = info.ItemName;
row[ " Specification " ] = info.Specification;
row[ " Unit " ] = info.Unit;
row[ " Price " ] = info.Price;
row[ " Count " ] = Math.Abs(info.Quantity);
dt.Rows.Add(row);
}
}
designer.SetDataSource(dt);
designer.Process();
string fileToSave = FileDialogHelper.SaveExcel();
if (File.Exists(fileToSave))
{
File.Delete(fileToSave);
}
designer.Save(fileToSave, FileFormatType.Excel2003);
Process.Start(fileToSave);
以上报表，其实实现思路基本都差不多，相对来时，还是比较容易的，接下来设计一个比较困难的报表，需要结合Aspose.Cell一些对象来动态创建行列，并设置单元格的变量，然后填入相应的对象构造报表，另外还需要注意单元格格式的变化，如下所示的这种报表
这个报表初看没有太多特别的地方，难点就是他的第一行列也是变化的，因此不能通过普通的方式构建二维表，然后绑定数据源的方式，要先加载模板文件，然后操作Excel对象，把第一行的各列头部补齐，然后给下一行各单元格填入对象公式，如&=BigType.DeptName 和&=BigType.TotalMoney等内容，实现的代码如下所示：
string
sql
=
@"
Select [YearMonth], [DeptName], [ItemType], [TotalMoney]
from TB_ReportDeptCost " ;
DataTable dt = GetTable(sql);
if (dt.Rows.Count == 0 )
return ;
List < string > itemTypeList = new List < string > ();
List < string > partList = new List < string > ();
foreach (DataRow row in dt.Rows)
{
string itemType = row[ " ItemType " ].ToString();
if ( ! itemTypeList.Contains(itemType))
{
itemTypeList.Add(itemType);
}
string part = row[ " DeptName " ].ToString();
if ( ! partList.Contains(part))
{
partList.Add(part);
}
}
string columnString = " DeptName " ;
for ( int i = 0 ; i < itemTypeList.Count; i ++ )
{
columnString += string .Format( " ,TotalMoney{0}|decimal " , i);
}
DataTable dtBigType = DataTableHelper.CreateTable(columnString);
dtBigType.TableName = " BigType " ;
foreach ( string part in partList)
{
DataRow row = dtBigType.NewRow();
row[ " DeptName " ] = part;
for ( int i = 0 ; i < itemTypeList.Count; i ++ )
{
string itemType = itemTypeList[i];
DataRow[] rowSelect = dt.Select( string .Format( " DeptName='{0}' AND ItemType='{1}' " , part, itemType));
if (rowSelect.Length > 0 )
{
row[ " TotalMoney " + i.ToString()] = rowSelect[ 0 ][ " TotalMoney " ];
}
}
dtBigType.Rows.Add(row);
}
WorkbookDesigner designer = new WorkbookDesigner();
string path = System.IO.Path.Combine(Application.StartupPath, " Report1.xls " );
designer.Open(path);
Aspose.Cells.Worksheet w = designer.Workbook.Worksheets[ 0 ];
// 先设置标题项目：如大修件，日常备件等
int rowIndex = 2 ; // 第三行为标题
Aspose.Cells.Style style = w.Cells[rowIndex + 1 , 1 ].Style; // 继承数字栏目的样式
style.Number = 4 ; // 对应格式是#,##0.00
Aspose.Cells.Style boldStyle = w.Cells[rowIndex, 0 ].Style; // 继承开始栏目的样式
for ( int i = 0 ; i < itemTypeList.Count; i ++ )
{
w.Cells[rowIndex, i + 1 ].PutValue(itemTypeList[i]);
w.Cells[rowIndex, i + 1 ].Style = boldStyle;
w.Cells[rowIndex + 1 , i + 1 ].PutValue( " &=BigType.TotalMoney " + i.ToString());
w.Cells[rowIndex + 1 , i + 1 ].Style = style;
}
// 添加合计行
w.Cells[rowIndex, itemTypeList.Count + 1 ].PutValue( " 合计 " );
w.Cells[rowIndex, itemTypeList.Count + 1 ].Style = boldStyle;
w.Cells[rowIndex + 1 , itemTypeList.Count + 1 ].PutValue( string .Format( " &=&=SUM(B{{r}}:{0}{{r}}) " , GetChar(itemTypeList.Count + 1 )));
w.Cells[rowIndex + 1 , itemTypeList.Count + 1 ].Style = style;
designer.SetDataSource(dtBigType);
designer.SetDataSource( " YearMonth " , dt.Rows[ 0 ][ " YearMonth " ].ToString());
designer.Process();
// Save the excel file
string fileToSave = FileDialogHelper.SaveExcel();
if (File.Exists(fileToSave))
{
File.Delete(fileToSave);
}
designer.Save(fileToSave, FileFormatType.Excel2003);
Process.Start(fileToSave);
from TB_ReportDeptCost " ;
DataTable dt = GetTable(sql);
if (dt.Rows.Count == 0 )
return ;
List < string > itemTypeList = new List < string > ();
List < string > partList = new List < string > ();
foreach (DataRow row in dt.Rows)
{
string itemType = row[ " ItemType " ].ToString();
if ( ! itemTypeList.Contains(itemType))
{
itemTypeList.Add(itemType);
}
string part = row[ " DeptName " ].ToString();
if ( ! partList.Contains(part))
{
partList.Add(part);
}
}
string columnString = " DeptName " ;
for ( int i = 0 ; i < itemTypeList.Count; i ++ )
{
columnString += string .Format( " ,TotalMoney{0}|decimal " , i);
}
DataTable dtBigType = DataTableHelper.CreateTable(columnString);
dtBigType.TableName = " BigType " ;
foreach ( string part in partList)
{
DataRow row = dtBigType.NewRow();
row[ " DeptName " ] = part;
for ( int i = 0 ; i < itemTypeList.Count; i ++ )
{
string itemType = itemTypeList[i];
DataRow[] rowSelect = dt.Select( string .Format( " DeptName='{0}' AND ItemType='{1}' " , part, itemType));
if (rowSelect.Length > 0 )
{
row[ " TotalMoney " + i.ToString()] = rowSelect[ 0 ][ " TotalMoney " ];
}
}
dtBigType.Rows.Add(row);
}
WorkbookDesigner designer = new WorkbookDesigner();
string path = System.IO.Path.Combine(Application.StartupPath, " Report1.xls " );
designer.Open(path);
Aspose.Cells.Worksheet w = designer.Workbook.Worksheets[ 0 ];
// 先设置标题项目：如大修件，日常备件等
int rowIndex = 2 ; // 第三行为标题
Aspose.Cells.Style style = w.Cells[rowIndex + 1 , 1 ].Style; // 继承数字栏目的样式
style.Number = 4 ; // 对应格式是#,##0.00
Aspose.Cells.Style boldStyle = w.Cells[rowIndex, 0 ].Style; // 继承开始栏目的样式
for ( int i = 0 ; i < itemTypeList.Count; i ++ )
{
w.Cells[rowIndex, i + 1 ].PutValue(itemTypeList[i]);
w.Cells[rowIndex, i + 1 ].Style = boldStyle;
w.Cells[rowIndex + 1 , i + 1 ].PutValue( " &=BigType.TotalMoney " + i.ToString());
w.Cells[rowIndex + 1 , i + 1 ].Style = style;
}
// 添加合计行
w.Cells[rowIndex, itemTypeList.Count + 1 ].PutValue( " 合计 " );
w.Cells[rowIndex, itemTypeList.Count + 1 ].Style = boldStyle;
w.Cells[rowIndex + 1 , itemTypeList.Count + 1 ].PutValue( string .Format( " &=&=SUM(B{{r}}:{0}{{r}}) " , GetChar(itemTypeList.Count + 1 )));
w.Cells[rowIndex + 1 , itemTypeList.Count + 1 ].Style = style;
designer.SetDataSource(dtBigType);
designer.SetDataSource( " YearMonth " , dt.Rows[ 0 ][ " YearMonth " ].ToString());
designer.Process();
// Save the excel file
string fileToSave = FileDialogHelper.SaveExcel();
if (File.Exists(fileToSave))
{
File.Delete(fileToSave);
}
designer.Save(fileToSave, FileFormatType.Excel2003);
Process.Start(fileToSave);
本文转自博客园伍华聪的博客，原文链接：使用Aspose.Cell控件实现Excel高难度报表的生成（二），如需转载请自行联系原博主。
