使用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
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