在上一篇文章[关于大数据的查询与导出]中,提到了使用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 > )
{
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 <= )
{
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(
, , //- 上左 到 上右 的位置，是基于下面的行列位置
, , //- 下左 到 下右 的位置，是基于下面的行列位置
t.CellPoint.Y, t.CellPoint.X,
t.CellPoint.Y + , t.CellPoint.X + ); //- 插入图片到 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 <= )
{
return;
}
var bgRowIndex = formater.DetailRowBeginIndex;
sheet.InsertRow(bgRowIndex, source.Count, (HSSFRow)sheet.GetRow(bgRowIndex)); //填充数据
for (int i = ; i < source.Count; i++)
{
var tempRow = sheet.GetRow(bgRowIndex + i);
for (int j = ; 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(); //加载第一个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),至于接口中约定的其他几个处理逻辑类似,本处不再赘述.
- 这个文章不是什么高大上的东东,各位看官仅当做一个工具即可,园子里面有很多写类似的东东,仅想说的是这个不具备互比性,写在这里仅仅是为了给自己一个备忘,以完善自己的代码库.