当前位置:主页 > 幸运飞艇冠亚和互刷正文

幸运飞艇冠亚和互刷:使用Aspose.Cell控件实现Excel高难度报表的生成(一)

05月07日作者:黑曼巴


本文章主要先容报表的天生,基于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; idatatable.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; idatatable.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; idatatable.Columns.Count; i++)

{

wb.Worksheets[0].Cells[rowIndex, i].PutValue(row[i].ToString());

}

rowIndex++;

}

for (int k = 0; kdatatable.Columns.Count; k++)

{

wb.Worksheets[0].AutoFitColumn(k, 0, 150);

}

wb.Worksheets[0].FreezePanes(1, 0, 1, datatable.Columns.Count);

wb.Save(filepath);

re幸运飞艇冠亚和互刷turn true;

}

catch (Exception e)

{

error = error + " DataTableToExcel: " + e.Message;

return false;

}

}

///

/// Excel文件转换为DataTable.

///

///

Excel文件的全路径

///

DataTable:返回值

///

差错信息:返回差错信息,没有差错返回""

/// true:函数精确履行 false:函数履行差错

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; idatatable.Columns.Count - 1; i++)

{

lists[nRow].Add(row[i]);

}

nRow++;

}

//图片

for (int i = 0; ipictures.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.UpperLeftRowdatatable.Rows.Count && picture.UpperLeftColumndatatable.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; ilist.Count - 1; i++)

{

try

{

System.Console.WriteLine(i.ToString() + "" + list[i].GetType());

if (list[i].GetType().ToString() == "System.Drawing.Bitmap")

{

//插入图片数据

System.Drawing.Image image = (Syste幸运飞艇冠亚和互刷m.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; i100; i++)

{

DataRow dr = dt.NewRow();

for (int j = 0; jdt.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[] { ',', ';' });

Liststring> nameList = new Liststring>();

foreach (st幸运飞艇冠亚和互刷ring item in nameArray)

{

if (!string.IsNullOrEmpty(item))

{

nameList.Add(item);

}

}

return CreateTable(nameList, tableName);

}

DataSet ds = LoadDataSet();//应用DataSet工具

ListCustomers> 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工具

以上的代码阐清楚明了改控件支持的各类参数变量,我们先看看报表的模板,然后看看报表的天生内容,比较一下就更直不雅了。

报表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等等。

最近关注

热点内容

更多