在上一篇文章[关于大数据的查询与导出]中,提到了使用NPOI组件导出Excel,本想上次一起分享给大家,无奈最近比较忙,今天抽空整理了下,分享出来.
- 预置填充模板,并且需要支持公式计算;
- 可导入图片;
- 可以追加数据形式填充表格.
- 简单分一下这个功能.
- 需要处理模板的读取,并根据模板中指定特定适配符替换模板中的数据,需要处理三种类型的单元格的格式化:散列单元格,图片单元格,数据明细区单元格;
- 散列单元格: 需要定义具体的模板字符串,及期望格式化后的数据,姑且将这里处理散列单元格的对象 命名为 DispersedCellFormater;
- 图片的导出,需要指定需要导出Excel 中的具体位置及期望填充的图片资源,姑且将这里才处理图片单元格的对象命名为 ImageCellFormater;
- 明细区单元格格式化,需要制定当前的列索引及每个列的这个取值函数.姑且这里将处理对象命名为DetailCellValueFormater<T>,因为了便于使用,我们引入了泛型类型;
- 外部调用,统一从一个入口处理,这里暂且命名为 ExportFormater;
- 至于散列单元格的位置,我们采用系统内置的 Point 类型来描述,明细区的取值函数使用系统定义的Func<T,object> 来描述,这里的输入类型T即为当前行数据对象;
- 根据以上分析,我们简单画一下这个功能的UML图,以方便理解.
- 有了以上分析,实现似乎就是水到渠成的事情了.
- 我们拟定一个接口,用于约束导出操作.
/// <summary> /// 导出接口 /// </summary> public interface IExport { /// <summary> /// 导出数据,基于模板文件处理替换后,保存为一个新文件 /// </summary> /// <param name="templateFile">模板文件</param> /// <param name="targetFile">目标文件</param> /// <param name="fromater">模板格式化处理规则</param> void Export(string templateFile, string targetFile, ExportFormater fromater); /// <summary> /// 导出数据,基于模板文件处理替换后,保存为一个新文件 /// </summary> /// <typeparam name="T">数据源类型</typeparam> /// <param name="templateFile">模板文件</param> /// <param name="targetFile">目标文件</param> /// <param name="formater">模板格式化处理规则</param> /// <param name="source">数据源</param> void Export<T>(string templateFile, string targetFile, ExportFormater<T> formater, IList<T> source) where T : class; /// <summary> /// 以追加的形式,将数据添加到已存在的文件中 /// </summary> /// <typeparam name="T">数据源类型</typeparam> /// <param name="targetFile">目标文件</param> /// <param name="formater">模板格式化处理规则</param> /// <param name="source">数据源</param> void ExportByAppend<T>(string targetFile, ExportFormater<T> formater, IList<T> source) where T : class; }
- 散列单元格格式处理器
/// <summary> /// 散列单元格数据格式器 /// </summary> public class DispersedCellFormater { /// <summary> /// 单元格坐标 /// </summary> public Point CellPoint { get; set; } /// <summary> /// 格式化字符串 /// </summary> public string FormaterString { get; set; } /// <summary> /// 格式化数据 /// </summary> public object CellValue { get; set; } /// <summary> /// 实例化 /// </summary> public DispersedCellFormater() { } /// <summary> /// 实例化 /// </summary> /// <param name="x">cell 横坐标</param> /// <param name="y">cell 纵坐标</param> /// <param name="formatStr">格式化字符串</param> /// <param name="val">替换值</param> public DispersedCellFormater(int x, int y, string formatStr, object val) { this.CellPoint = new Point(x, y); this.FormaterString = formatStr; this.CellValue = val; } }
- 图片单元格格式处理器
/// <summary> /// 图片Cell格式化器 /// </summary> public class ImageCellFormater { /// <summary> /// 单元格位置 /// </summary> public Point CellPoint { get; set; } /// <summary> /// 显示图片 /// </summary> public Image Img { get; set; } /// <summary> /// 实例化 /// </summary> public ImageCellFormater() { } /// <summary> /// 实例化 /// </summary> /// <param name="x">cell横坐标</param> /// <param name="y">cell纵坐标</param> /// <param name="img">图片</param> public ImageCellFormater(int x, int y, Image img) { this.CellPoint = new Point(x, y); this.Img = img; } }
- 明细区单元格格式处理器
/// <summary> /// 明细单元格取值规则 /// <typeparam name="T">数据类型</typeparam> /// </summary> public class DetailCellValueFormater<T> where T : class { /// <summary> /// 列索引 /// </summary> public int Index { get; set; } /// <summary> /// 取值函数 /// </summary> public Func<T, object> Value { get; set; } /// <summary> /// 实例化 /// </summary> public DetailCellValueFormater() { } /// <summary> /// 实例化 /// </summary> /// <param name="index">列索引</param> /// <param name="val">数据</param> public DetailCellValueFormater(int index, Func<T, object> val) { this.Index = index; this.Value = val; } }
- 接下来一起看下导出格式处理集合的定义.
/// <summary> /// 用于描述导出格式化数据 /// </summary> public class ExportFormater { /// <summary> /// 散列单元格替换规格 /// </summary> public List<DispersedCellFormater> DispersedCellFormaters { get; private set; } /// <summary> /// 图片单元格格式化规则 /// </summary> public List<ImageCellFormater> ImageCellFormaters { get; private set; } /// <summary> /// 明细数据起始行索引 /// </summary> public int DetailRowBeginIndex { get; set; } /// <summary> /// 实例化 /// </summary> public ExportFormater() { DispersedCellFormaters = new List<DispersedCellFormater>(); ImageCellFormaters = new List<ImageCellFormater>(); } } /// <summary> /// 用于描述导出格式化数据,带有明细区数据取值规则 /// </summary> public class ExportFormater<T> : ExportFormater where T : class { /// <summary> /// 明细区取值函数 /// </summary> public List<DetailCellValueFormater<T>> DetailCellValueFormaters { get; private set; } /// <summary> /// 实例化 /// </summary> public ExportFormater() { DetailCellValueFormaters = new List<DetailCellValueFormater<T>>(); } }
- 接下来我们简单看下导出的具体实现.
- 散列单元格的处理
/// <summary> /// 应用散列单元格格式 /// </summary> /// <param name="formater">格式化规则</param> /// <param name="sheet">当前sheet</param> private static void ApplyDispersedCell(ExportFormater formater, HSSFSheet sheet) { if (formater.DispersedCellFormaters != null && formater.DispersedCellFormaters.Count > 0) { formater.DispersedCellFormaters.ForEach(r => { var tempRow = sheet.GetRow(r.CellPoint.X); var tempCell = tempRow.GetCell(r.CellPoint.Y); var txt = tempCell.StringCellValue; if (string.IsNullOrWhiteSpace(txt)) { tempCell.SetCellValue(Convert.ToString(r.CellValue)); } else { //替换模板 tempCell.SetCellValue(txt.Replace(r.FormaterString, Convert.ToString(r.CellValue))); } }); } }
- 图片单元格的处理
/// <summary> /// 应用图片单元格 /// </summary> /// <param name="formater">格式化处理器</param> /// <param name="workbook"></param> /// <param name="sheet"></param> private static void ApplyImgCell(ExportFormater formater, HSSFWorkbook workbook, HSSFSheet sheet) { if (formater.ImageCellFormaters.Count <= 0) { return; } var patriarch = sheet.CreateDrawingPatriarch(); formater.ImageCellFormaters.ForEach(t => { if (t.Img != null) { var imgData = t.Img.ToByte(); //- 图片输出的位置这么计算的： //- 假设我们要将图片放置于第 5(E) 列的第 2 行 //- 对应索引为是 4 : 1 （默认位置） //- 放置的位置就等于（默认位置）到（默认位置各自加上一行、一列） var imgPath = new HSSFClientAnchor( 1, 1, //- 上左 到 上右 的位置，是基于下面的行列位置 1, 1, //- 下左 到 下右 的位置，是基于下面的行列位置 t.CellPoint.Y, t.CellPoint.X, t.CellPoint.Y + 3, t.CellPoint.X + 8); //- 插入图片到 Excel，并返回一个图片的标识 var pictureIdx = workbook.AddPicture(imgData, NPOI.SS.UserModel.PictureType.JPEG); patriarch.CreatePicture(imgPath, pictureIdx);//- 使用绘画器绘画图片 } }); }
- 明细单元格处理
/// <summary> /// 使用格式应用明细单元格 /// </summary> /// <typeparam name="T">数据类型</typeparam> /// <param name="formater">数据格式化器</param> /// <param name="source">数据源</param> /// <param name="sheet">当前单元格</param> private void ApplyDetailCell<T>(ExportFormater<T> formater, IList<T> source, HSSFSheet sheet) where T : class { if (source == null || source.Count <= 0) { return; } var bgRowIndex = formater.DetailRowBeginIndex; sheet.InsertRow(bgRowIndex, source.Count, (HSSFRow)sheet.GetRow(bgRowIndex)); //填充数据 for (int i = 0; i < source.Count; i++) { var tempRow = sheet.GetRow(bgRowIndex + i); for (int j = 0; j < formater.DetailCellValueFormaters.Count; j++) { var tempFormarter = formater.DetailCellValueFormaters[j]; if (tempFormarter.Value != null) { var tempCell = tempRow.GetCell(tempFormarter.Index); SetCellValue(tempCell, tempFormarter.Value, source[i]); } } } } /// <summary>
/// 设置单元格值 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="cell"></param> /// <param name="func"></param> /// <param name="data"></param> private void SetCellValue<T>(ICell cell, Func<T, object> func, T data) { if (cell == null || func == null) { return; } var val = func(data); if (val != null) { var valType = val.GetType(); switch (valType.ToString()) { case "System.String"://字符串类型 cell.SetCellValue(Convert.ToString(val)); break; case "System.DateTime"://日期类型 cell.SetCellValue((DateTime)val); break; case "System.Boolean"://布尔型 cell.SetCellValue((bool)val); break; case "System.Int16"://整型 case "System.Int32": case "System.Int64": case "System.Byte": case "System.Decimal"://浮点型 case "System.Double": cell.SetCellValue(Convert.ToDouble(val)); break; case "System.DBNull"://空值处理 cell.SetCellValue(""); break; default: cell.SetCellValue(""); break; } } } - 最后,有了上面的支持,我们的调用接口就变得很简单了
/// <summary> /// 导出数据,基于模板文件处理替换后,保存为一个新文件 /// </summary> /// <typeparam name="T">数据源类型</typeparam> /// <param name="templateFile">模板文件</param> /// <param name="targetFile">目标文件</param> /// <param name="fromater">模板格式化处理规则</param> /// <param name="source">数据源</param> public void Export<T>(string templateFile, string targetFile, ExportFormater<T> formater, IList<T> source) where T : class { #region 参数验证 if (string.IsNullOrWhiteSpace(templateFile)) { throw new ArgumentNullException("templateFile"); } if (string.IsNullOrWhiteSpace(targetFile)) { throw new ArgumentNullException("targetFile"); } if (!File.Exists(templateFile)) { throw new FileNotFoundException(templateFile + " 文件不存在!"); } if (formater == null) { throw new ArgumentNullException("formater"); } if (source == null) { throw new ArgumentNullException("source"); } #endregion var fileStream = new FileStream(templateFile, FileMode.Open, FileAccess.Read);//读入excel模板 var workbook = new HSSFWorkbook(fileStream); var sheet = (HSSFSheet)workbook.GetSheetAt(0); //加载第一个sheet //设置文件属性 SetExcelProperty(workbook); //离散单元格 ApplyDispersedCell(formater, sheet); //明细行 ApplyDetailCell<T>(formater, source, sheet); //应用图片 ApplyImgCell(formater, workbook, sheet); // 格式化当前sheet，用于数据total计算 sheet.ForceFormulaRecalculation = true; using (FileStream fs = new FileStream(targetFile, FileMode.Create, FileAccess.Write)) { workbook.Write(fs); fs.Flush(); } sheet = null; workbook = null; }
- 散列单元格的处理
- 我们拟定一个接口,用于约束导出操作.
结语:
- 这个日志本来应该是和上一篇”关于大数据的查询与导出 ”一起出来的,但最近确实太忙了,零零碎碎的今天才整理完毕.
- 这里仅给出了一个导出的基本方法,Export<T>(string templateFile, string targetFile, ExportFormater<T> formater, IList<T> source),至于接口中约定的其他几个处理逻辑类似,本处不再赘述.
- 这个文章不是什么高大上的东东,各位看官仅当做一个工具即可,园子里面有很多写类似的东东,仅想说的是这个不具备互比性,写在这里仅仅是为了给自己一个备忘,以完善自己的代码库.