使用Aspose.Cell控件实现Excel高难度报表的生成（二）
继续在上篇《
使用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);
以上报表，其实实现思路基本都差不多，相对来时，还是比较容易的，接下来设计一个比较困难的报表，需要结合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);