使用Aspose.Cell控件实现Excel高难度报表的生成（一）

时光飞逝，生活、工作、业余研究总是在不停忙碌着，转眼快到月底，该月的博客文章任务未完，停顿回忆一下，总结一些经验以及好的东西出来，大家一起分享一下。本文章主要介绍报表的生成，基于Aspose.Cell控件的报表生成。谈到报表，估计大家都有所领悟以及个人的理解，总的来说，一般的报表生成，基本上是基于以下几种方式：一种是基于微软Excel内置的引擎来实现；一种是构造HTML格式的Excle报表；一种是基于控件的方式来处理，基于控件有很多种方式，个人认为比较有名的是Aspose.Cell（收费破解）和NPOI（开源）。

而报表的表现方式大致可以分为两种：

一种是通用的二维表导出的Excel格式，这种方式通过封装一个操作类，传递一个DataTable参数，把数据导出就可以了。这种报表特点是操作方便，通用，能应付一般常用的数据报表，如下所示；

由于这种报表，一般是在一个数据表格中显示，通常的做法是把这个东西做成控件，一个可以解决分页问题，一个可以解决导出、打印问题等，如我的随笔文章《
WinForm界面开发之“分页控件”
》 介绍的解决办法。

当然，也可以把导入导出Excel的操作封装成一个公用的辅助来调用，如我封装的Aspose.Cell的导入导出处理函数如下所示：

代码

public

class
AsposeExcelTools
{


public

static

bool
DataTableToExcel(DataTable datatable,
string
filepath,
out

string
error)
{
error
=

""
;

try

{

if
(datatable
==

null
)
{
error
=

"
DataTableToExcel:datatable 为空
"
;

return

false
;
}

Aspose.Cells.Workbook workbook
=

new
Aspose.Cells.Workbook();
Aspose.Cells.Worksheet sheet
=
workbook.Worksheets[
0
];
Aspose.Cells.Cells cells
=
sheet.Cells;


int
nRow
=

0
;

foreach
(DataRow row
in
datatable.Rows)
{
nRow
++
;

try

{

for
(
int
i
=

0
; i
<
datatable.Columns.Count; i
++
)
{

if
(row[i].GetType().ToString()
==

"
System.Drawing.Bitmap
"
)
{

//
------插入图片数据-------


System.Drawing.Image image
=
(System.Drawing.Image)row[i];
MemoryStream mstream
=

new
MemoryStream();
image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg);
sheet.Pictures.Add(nRow, i, mstream);
}

else

{
cells[nRow, i].PutValue(row[i]);
}
}
}

catch
(System.Exception e)
{
error
=
error
+

"
DataTableToExcel:
"

+
e.Message;
}
}

workbook.Save(filepath);

return

true
;
}

catch
(System.Exception e)
{
error
=
error
+

"
DataTableToExcel:
"

+
e.Message;

return

false
;
}
}


public

static

bool
DataTableToExcel2(DataTable datatable,
string
filepath,
out

string
error)
{
error
=

""
;
Aspose.Cells.Workbook wb
=

new
Aspose.Cells.Workbook();


try

{

if
(datatable
==

null
)
{
error
=

"
DataTableToExcel:datatable 为空
"
;

return

false
;
}


//
为单元格添加样式


Aspose.Cells.Style style
=
wb.Styles[wb.Styles.Add()];

//
设置居中


style.HorizontalAlignment
=
Aspose.Cells.TextAlignmentType.Center;

//
设置背景颜色


style.ForegroundColor
=
System.Drawing.Color.FromArgb(
153
,
204
,
0
);
style.Pattern
=
BackgroundType.Solid;
style.Font.IsBold
=

true
;


int
rowIndex
=

0
;

for
(
int
i
=

0
; i
<
datatable.Columns.Count; i
++
)
{
DataColumn col
=
datatable.Columns[i];

string
columnName
=
col.Caption
??
col.ColumnName;
wb.Worksheets[
0
].Cells[rowIndex, i].PutValue(columnName);
wb.Worksheets[
0
].Cells[rowIndex, i].Style
=
style;
}
rowIndex
++
;


foreach
(DataRow row
in
datatable.Rows)
{

for
(
int
i
=

0
; i
<
datatable.Columns.Count; i
++
)
{
wb.Worksheets[
0
].Cells[rowIndex, i].PutValue(row[i].ToString());
}
rowIndex
++
;
}


for
(
int
k
=

0
; k
<
datatable.Columns.Count; k
++
)
{
wb.Worksheets[
0
].AutoFitColumn(k,
0
,
150
);
}
wb.Worksheets[
0
].FreezePanes(
1
,
0
,
1
, datatable.Columns.Count);
wb.Save(filepath);

return

true
;
}

catch
(Exception e)
{
error
=
error
+

"
DataTableToExcel:
"

+
e.Message;

return

false
;
}

}


///

<summary>


///
Excel文件转换为DataTable.

///

</summary>


///

<param name="filepath">
Excel文件的全路径
</param>


///

<param name="datatable">
DataTable:返回值
</param>


///

<param name="error">
错误信息:返回错误信息，没有错误返回""
</param>


///

<returns>
true:函数正确执行 false:函数执行错误
</returns>



public

static

bool
ExcelFileToDataTable(
string
filepath,
out
DataTable datatable,
out

string
error)
{
error
=

""
;
datatable
=

null
;

try

{

if
(File.Exists(filepath)
==

false
)
{
error
=

"
文件不存在
"
;
datatable
=

null
;

return

false
;
}
Aspose.Cells.Workbook workbook
=

new
Aspose.Cells.Workbook();
workbook.Open(filepath);
Aspose.Cells.Worksheet worksheet
=
workbook.Worksheets[
0
];
datatable
=
worksheet.Cells.ExportDataTable(
0
,
0
, worksheet.Cells.MaxRow
+

1
, worksheet.Cells.MaxColumn
+

1
);

//
-------------图片处理-------------


Aspose.Cells.Pictures pictures
=
worksheet.Pictures;

if
(pictures.Count
>

0
)
{

string
error2
=

""
;

if
(InsertPicturesIntoDataTable(pictures, datatable,
out
datatable,
out
error2)
==

false
)
{
error
=
error
+
error2;
}
}

return

true
;
}

catch
(System.Exception e)
{
error
=
e.Message;

return

false
;
}

}




public

static

bool
ExcelFileToLists(
string
filepath,
out
IList[] lists,
out

string
error)
{
error
=

""
;
lists
=

null
;
DataTable datatable
=

new
DataTable();
IList list
=

new
ArrayList();
Pictures[] pictures;

if
(ExcelFileToDataTable(filepath,
out
datatable,
out
error)
&&
GetPicturesFromExcelFile(filepath,
out
pictures,
out
error))
{
lists
=

new
ArrayList[datatable.Rows.Count];

//
------------DataTable转换成IList[]--------------

//
数据



int
nRow
=

0
;

foreach
(DataRow row
in
datatable.Rows)
{
lists[nRow]
=

new
ArrayList(datatable.Columns.Count);

for
(
int
i
=

0
; i
<=
datatable.Columns.Count
-

1
; i
++
)
{
lists[nRow].Add(row[i]);
}
nRow
++
;
}

//
图片



for
(
int
i
=

0
; i
<
pictures.Length; i
++
)
{

foreach
(Picture picture
in
pictures[i])
{

try

{

//
----把图片转换成System.Drawing.Image----

//
MemoryStream mstream = new MemoryStream();

//
mstream.Write(picture.Data, 0, picture.Data.Length);

//
System.Drawing.Image image = System.Drawing.Image.FromStream(mstream);

//
----Image放入IList------

//
图片有可能越界



if
(picture.UpperLeftRow
<=
datatable.Rows.Count
&&
picture.UpperLeftColumn
<=
datatable.Columns.Count)
{
lists[picture.UpperLeftRow][picture.UpperLeftColumn]
=
picture.Data;
}

}

catch
(System.Exception e)
{
error
=
error
+
e.Message;
}

}
}

}

else

{


return

false
;
}

return

true
;
}


public

static

bool
ListsToExcelFile(
string
filepath, IList[] lists,
out

string
error)
{
error
=

""
;

//
----------Aspose变量初始化----------------


Aspose.Cells.Workbook workbook
=

new
Aspose.Cells.Workbook();
Aspose.Cells.Worksheet sheet
=
workbook.Worksheets[
0
];
Aspose.Cells.Cells cells
=
sheet.Cells;

//
-------------输入数据-------------



int
nRow
=

0
;
sheet.Pictures.Clear();
cells.Clear();

foreach
(IList list
in
lists)
{


for
(
int
i
=

0
; i
<=
list.Count
-

1
; i
++
)
{

try

{
System.Console.WriteLine(i.ToString()
+

"

"

+
list[i].GetType());

if
(list[i].GetType().ToString()
==

"
System.Drawing.Bitmap
"
)
{

//
插入图片数据


System.Drawing.Image image
=
(System.Drawing.Image)list[i];

MemoryStream mstream
=

new
MemoryStream();

image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg);

sheet.Pictures.Add(nRow, i, mstream);
}

else

{
cells[nRow, i].PutValue(list[i]);
}
}

catch
(System.Exception e)
{
error
=
error
+
e.Message;
}

}

nRow
++
;
}

//
-------------保存-------------


workbook.Save(filepath);


return

true
;
}

这样封装了Aspose.Cell的操作，每次生成Excel文件或者导入Excel内容，就非常方便，只需要如下调用方式即可完成：

另外一种是以Excel文件作为模板，然后填入必要的内容，形成比较综合性，复杂性较高的报表，这种报表一般比较专业、比较规范好看，在一些特殊的场合，必须使用这些固定格式的报表，如下所示：

或者这样的报表格式

这些报表，基本上就是用到了变量、函数等的概念才能处理好这些数据，如上面的出库单，里面的成本中心、部门、库房编号等，这些通过变量绑定应该就可以了，而里面的列表，则可以通过集合绑定实现，Aspose.Cell控件功能非常强大，很好支持这些操作，下面一步步介绍该控件制作这类报表的实现代码。

Aspose.Cell控件支持多种参数变量的绑定操作，如支持DataSet、Datatable、IList集合，实体类集合、类对象等。

以上的代码说明了改控件支持的各种参数变量，我们先看看报表的模板，然后看看报表的生成内容，对比一下就更直观了。

报表1模板如下所示（其中通过引用集合的对象是通过&=来引用，对象的属性或者列名，通过如&=Customer.City方式引用，非常直观方便：

报表1生成的效果如下所示（Customers可以使DataTable对象，也可以List<Customer>实体对象集合。

报表2的模板如下所示，对象也可以通过&=[Order Detail]方式引用，另外模板支持一些参数，其中{r}为当行的变量，翻译到实际的报表可能就是C4*D4这样的格式了，其中两个&=表示动态公式引用，区别于普通的变量和字符，如&=&=C{r}*D{r}后者汇总函数&=&=Sum(C{r}:D{r})等等。

报表2的生成效果如下所示

报表3的生成效果如下所示

综上所述，模板报表的变量绑定方式有以下几种方式：

&=DataSource.FieldName

另外，模板报表支持一些参数进行辅助使用，如下所示：

动态公式变量

另外，上面模板中看到&=&=C{r}*D{r}这样的动态公式，用于对列的应用，动态公式支持下面的引用变量:

{2}, {-1}
- 当前行的偏移位置

如果要汇总一些行列，可以使用&=&=Sum(C{r}:F{r})这样的动态变量来实现。

那如果是对同一列，不同行的字段进行汇总呢？，那样就更方便，不用这么复杂了，你只要使用普通的汇总函数如=Sum(C3:C4)这样的格式，就可以了，如果行动态增加，Excel会自动调整Sum函数里面的行列引用了，可能最后输出会变为=Sum(C3:C11)这样了。

汇总格式变量

可以通过
group:normal/merge/repeat
来控制汇总合并等格式的输出，如使用两者的例子：

&=Employees.EmployeeID(group:normal,skip:1)

出来的报表如下所示：

该函数是用来执行一系列汇总计算的函数，N从
1~11
subtotalN:Ref，其中Ref代表汇总的指定列

例如，&=Products.Units(subtotal9:Products.ProductID) 表示基于Units列进行数据汇总统计，统计到ProductID上来。

例如，&=Table1.ColumnD(subtotal9:Table1.ColumnA&Table1.ColumnB) ，则表示基于ColumnD列进行汇总统计，统计到ColumnA和ColumnB的分组条件上。

本篇由于篇幅原因，介绍到这里，下篇继续探讨基于模板生成的报表内容，包括利用对象动态创建行列以及公式，使用Style等方面，并结合实际复杂的报表例子，对基于Aspose.Cell报表内容进行进一步的实战分析探讨。

应读者要求，放上一个操作例子：
https://files.cnblogs.com/wuhuacong/TestAposeCell.rar