使用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
;
}
}
///
///
Excel文件转换为DataTable.
///
///
Excel文件的全路径
///
DataTable:返回值
///
错误信息:返回错误信息,没有错误返回""
///
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内容,就非常方便,只需要如下调用方式即可完成:
private void
button1_Click(
object
sender, EventArgs e)
{
DataTable dt
=
CreateTable(
"
测试1,测试2,Test1,Test2
"
,
"
testTable
"
);
for
(
int
i
= 0
;
i
< 100
;
i
++
)
{
DataRow dr
=
dt.NewRow();
for
(
int
j
= 0
;
j
<
dt.Columns.Count;
j
++
)
{
dr[j]
=
i.ToString();
}
dt.Rows.Add(dr);
}
string
outError
= ""
;
string
fileName
= @"
C:\test.xls
"
;
AsposeExcelTools.DataTableToExcel2(dt, fileName,
out
outError);
if
(
!
string
.IsNullOrEmpty(outError))
{
MessageBox.Show(outError);
}
else
{
Process.Start(fileName);
}
}
public
DataTable CreateTable(
string
nameString,
string
tableName)
{
string
[] nameArray
=
nameString.Split(
new char
[] {
'
,
'
,
'
;
'
});
List
<
string
>
nameList
= new
List
<
string
>
();
foreach
(
string
item
in
nameArray)
{
if
(
!
string
.IsNullOrEmpty(item))
{
nameList.Add(item);
}
}
return
CreateTable(nameList, tableName);
}
另外一种是以Excel文件作为模板,然后填入必要的内容,形成比较综合性,复杂性较高的报表,这种报表一般比较专业、比较规范好看,在一些特殊的场合,必须使用这些固定格式的报表,如下所示:
文章图片
或者这样的报表格式
文章图片
这些报表,基本上就是用到了变量、函数等的概念才能处理好这些数据,如上面的出库单,里面的成本中心、部门、库房编号等,这些通过变量绑定应该就可以了,而里面的列表,则可以通过集合绑定实现,Aspose.Cell控件功能非常强大,很好支持这些操作,下面一步步介绍该控件制作这类报表的实现代码。
Aspose.Cell控件支持多种参数变量的绑定操作,如支持DataSet、Datatable、IList集合,实体类集合、类对象等。
DataSet ds
=
LoadDataSet();
//
使用DataSet对象
List
<
Customers
>
entity
=
GetCustomers();
//
使用实体类对象
DataTable dt
=
GetCustomersTable();
//
使用DataTable对象
//
创建一个workbookdesigner对象
WorkbookDesigner designer
= new
WorkbookDesigner();
//
制定报表模板
string
path
=
System.IO.Path.Combine(Application.StartupPath,
"
SmartMarkerDesigner.xls
"
);
designer.Open(path);
//
设置DataSet对象
//
designer.SetDataSource(ds);
//
设置实体类对象
//
designer.SetDataSource("Customers", entity);
//
设置Datatable对象
designer.SetDataSource(dt);
designer.SetDataSource(ds.Tables[
"
Order Details
"
]);
//
设置变量对象
designer.SetDataSource(
"
Variable
"
,
"
Single Variable
"
);
//
设置集合变量
designer.SetDataSource(
"
MultiVariable
"
,
new string
[] {
"
Variable 1
"
,
"
Variable 2
"
,
"
Variable 3
"
});
//
设置集合变量
designer.SetDataSource(
"
MultiVariable2
"
,
new string
[] {
"
Skip 1
"
,
"
Skip 2
"
,
"
Skip 3
"
});
//根据数据源处理生成报表内容
designer.Process();
//
保存Excel文件
string
fileToSave
=
System.IO.Path.Combine(Application.StartupPath,
"
SmartMarker.xls
"
);
if
(File.Exists(fileToSave))
{
File.Delete(fileToSave);
}
designer.Save(fileToSave, FileFormatType.Excel2003);
//打开Excel文件
Process.Start(fileToSave);
以上的代码说明了改控件支持的各种参数变量,我们先看看报表的模板,然后看看报表的生成内容,对比一下就更直观了。
报表1模板如下所示(其中通过引用集合的对象是通过&=来引用,对象的属性或者列名,通过如&=Customer.City方式引用,非常直观方便:
文章图片
报表1生成的效果如下所示(Customers可以使DataTable对象,也可以List
文章图片
报表2的模板如下所示,对象也可以通过&=[Order Detail]方式引用,另外模板支持一些参数,其中{r}为当行的变量,翻译到实际的报表可能就是C4*D4这样的格式了,其中两个&=表示动态公式引用,区别于普通的变量和字符,如&=&=C{r}*D{r}后者汇总函数&=&=Sum(C{r}:D{r})等等。
文章图片
报表2的生成效果如下所示
文章图片
报表3的模板如下所示,这个报表模板使用了对象变量,对象变量引用方式如&=$Variable这样格式,比集合对象或者DataTable对象多了一个$符号,其中集合支持一些遍历参数,如Skip,Horiontal等等。
文章图片
报表3的生成效果如下所示
文章图片
综上所述,模板报表的变量绑定方式有以下几种方式:
&=DataSource.FieldName
&=[Data Source].[Field Name] &=$VariableName &=$VariableArray &==DynamicFormula &=&=RepeatDynamicFormula
另外,模板报表支持一些参数进行辅助使用,如下所示:
noadd 适应数据而不添加额外的行(不知道是不是这样表达) skip:n 每行记录跳过的数量,n=1代表依次遍历N=2则跳一个遍历 ascending:n / descending:n 排序数据供. 如果n=1,那么该列就是排序的第一个关键字,例子: &=Table1.Field3(ascending:1) horizontal 默认是上下垂直方式输出,如果设置为horizontal,则是横着输出内容,见上面的例子 动态公式变量
另外,上面模板中看到&=&=C{r}*D{r}这样的动态公式,用于对列的应用,动态公式支持下面的引用变量:
{r} - 当前行变量 {2}, {-1} - 当前行的偏移位置
如果要汇总一些行列,可以使用&=&=Sum(C{r}:F{r})这样的动态变量来实现。
那如果是对同一列,不同行的字段进行汇总呢?,那样就更方便,不用这么复杂了,你只要使用普通的汇总函数如=Sum(C3:C4)这样的格式,就可以了,如果行动态增加,Excel会自动调整Sum函数里面的行列引用了,可能最后输出会变为=Sum(C3:C11)这样了。
汇总格式变量
可以通过group:normal/merge/repeat 来控制汇总合并等格式的输出,如使用两者的例子:
&=Customers.CustomerID(group:merge) &=Employees.EmployeeID(group:normal,skip:1)
出来的报表如下所示:
subtotalN函数
分别代表1=AVERAGE, 2=COUNT,3=COUNTA, 4=MAX, 5=MIN,...9=SUM等等该函数是用来执行一系列汇总计算的函数,N从
1~11subtotalN:Ref,其中Ref代表汇总的指定列
例如,&=Products.Units(subtotal9:Products.ProductID) 表示基于Units列进行数据汇总统计,统计到ProductID上来。
例如,&=Table1.ColumnD(subtotal9:Table1.ColumnA&Table1.ColumnB) ,则表示基于ColumnD列进行汇总统计,统计到ColumnA和ColumnB的分组条件上。
本篇由于篇幅原因,介绍到这里,下篇继续探讨基于模板生成的报表内容,包括利用对象动态创建行列以及公式,使用Style等方面,并结合实际复杂的报表例子,对基于Aspose.Cell报表内容进行进一步的实战分析探讨。
【使用Aspose.Cell控件实现Excel高难度报表的生成(一)】
应读者要求,放上一个操作例子:http://files.cnblogs.com/wuhuacong/TestAposeCell.rar
推荐阅读
- 由浅入深理解AOP
- 【译】20个更有效地使用谷歌搜索的技巧
- mybatisplus如何在xml的连表查询中使用queryWrapper
- MybatisPlus|MybatisPlus LambdaQueryWrapper使用int默认值的坑及解决
- MybatisPlus使用queryWrapper如何实现复杂查询
- iOS中的Block
- Linux下面如何查看tomcat已经使用多少线程
- 使用composer自动加载类文件
- android|android studio中ndk的使用
- 使用协程爬取网页,计算网页数据大小