一、引子
新进公司被安排处理系统的数据报表任务——对学生的考试成绩进行统计并能导出到excel。虽然以前也有弄过,但感觉不是很好,所以这次狠下心,多花点时间作个让自己满意的插件。
二、适用领域
因为需求是基于学生成绩,可能更多的是按这样的需求去考虑。如下图(请不要计较数据):
三、逻辑
一个excel文件 --> N个工作表 --> N个数据容器–>N个数据内容
四、类的组成
类名 | 描述 |
---|---|
WorkbookWrapper(抽象类) | excel容器,一个实例代表一个excel文件 |
BuildContext(数据上下文) | 在事件中获取对象的上下文 |
WorkbookExtensions(扩展类) | WorkbookWrapper的扩展,有2个方法,一个保存到本地,一个是http下载 |
XSSFWorkbookBuilder(Excel2007) | 继承WorkbookWrapper提供2007的版本的实现类 |
HSSFWorkbookBuilder(Excel2003) | 同上,版本为2003 |
ExcelModelsPropertyManage | 对生成的的数据结构的管理类 |
ISheetDetail(工作表接口) | 每一个ISheetDetail都代表一张工作表(包含一个SheetDataCollection) |
ISheetDataWrapper(内容容器接口) | 每一个ISheetDataWrapper都代表ISheetDetail里的一块内容 |
SheetDataCollection(数据集合) | 内容容器的集合 |
IExcelModelBase(内容模型的基类接口) | ISheetDataWrapper里的内容数据模型均继承此接口(包含一个IExtendedBase集合) |
IExtendedBase(扩展内容接口) | 如上图中的科目1-科目3属于不确定数量的内容均继承此接口 |
IgnoreAttribute(忽略标记) | 不想输出到excel的打上此标记即可 |
CellExtensions(列的扩展) | 格式化列的样式 |
EnumService(枚举服务类) | 输出枚举对象里的DescriptionAttribute特性的值 |
注:标题是依据模型属性的 DisplayName
特性标记来实现的。
五、主要实现类
WorkbookBuilder.cs
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.ComponentModel;
using System.Collections;
namespace ExcelHelper.Operating
{
public abstract class WorkbookBuilder
{
protected WorkbookBuilder()
{
currentWorkbook = CreateWorkbook();
buildContext = new BuildContext() { WorkbookBuilder = this, Workbook = currentWorkbook };
}
public delegate void BuildEventHandler(BuildContext context);
protected abstract IWorkbook CreateWorkbook();
public IWorkbook currentWorkbook;
private ICellStyle _centerStyle;
public ICellStyle CenterStyle
{
get
{
if (_centerStyle == null)
{
_centerStyle = currentWorkbook.CreateCellStyle();
_centerStyle.Alignment = HorizontalAlignment.Center;
_centerStyle.VerticalAlignment = VerticalAlignment.Center;
}
return _centerStyle;
}
}
private Int32 StartRow = 0;//起始行
private BuildContext buildContext;
public event BuildEventHandler OnHeadCellSetAfter;
public event BuildEventHandler OnContentCellSetAfter;
#region DataTableToExcel
public void Insert(ISheetDetail sheetDetail)
{
ISheet sheet;
if (sheetDetail.IsContinue)
{
sheet = currentWorkbook.GetSheetAt(currentWorkbook.NumberOfSheets - 1);
StartRow = sheet.LastRowNum + 1;
}
else
{
sheet = currentWorkbook.CreateSheet(sheetDetail.SheetName);
}
buildContext.Sheet = sheet;
sheet = DataToSheet(sheetDetail.SheetDetailDataWrappers, sheet);
}
/// <summary>
/// 这里添加数据,循环添加,主要应对由多个组成的
/// </summary>
/// <param name="sheetDetailDataWrappers"></param>
/// <param name="sheet"></param>
/// <returns></returns>
private ISheet DataToSheet(SheetDataCollection sheetDetailDataWrappers, ISheet sheet)
{
foreach (var sheetDetailDataWrapper in sheetDetailDataWrappers)
{
if (sheetDetailDataWrapper.Datas == null || sheetDetailDataWrapper.Datas.Count() == 0)
{
continue;
}
Type type = sheetDetailDataWrapper.Datas.GetType().GetGenericArguments()[0];
if (sheetDetailDataWrapper.HaveTitle)
{
sheet = SetTitle(sheet, sheetDetailDataWrapper, type);
}
sheet = AddValue(sheet, sheetDetailDataWrapper, type);
StartRow = StartRow + sheetDetailDataWrapper.EmptyIntervalRow;
}
return sheet;
}
#endregion
#region 设置值
private void SetCellValue(ICell cell, object obj)
{
if (obj == null)
{
cell.SetCellValue(" "); return;
}
if (obj is String)
{
cell.SetCellValue(obj.ToString()); return;
}
if (obj is Int32 || obj is Double)
{
cell.SetCellValue(Math.Round(Double.Parse(obj.ToString()), 2)); return;
}
if (obj.GetType().IsEnum)
{
cell.SetCellValue(EnumService.GetDescription((Enum)obj)); return;
}
if (obj is DateTime)
{
cell.SetCellValue(((DateTime)obj).ToString("yyyy-MM-dd HH:mm:ss")); return;
}
if (obj is Boolean)
{
cell.SetCellValue((Boolean)obj ? "√" : "×"); return;
}
}
#endregion
#region SetTitle
private ISheet SetTitle(ISheet sheet, ISheetDataWrapper sheetDetailDataWrapper, Type type)
{
IRow titleRow = null;
ICell titleCell = null;
if (!String.IsNullOrEmpty(sheetDetailDataWrapper.DataName))
{
titleRow = sheet.CreateRow(StartRow);
buildContext.Row = titleRow;
StartRow++;
titleCell = SetCell(titleRow, 0, sheetDetailDataWrapper.DataName);
if (OnHeadCellSetAfter != null)
{
OnHeadCellSetAfter(buildContext);
}
}
IRow row = sheet.CreateRow(StartRow);
buildContext.Row = row;
IList<PropertyInfo> checkPropertyInfos = ExcelModelsPropertyManage.CreatePropertyInfos(type);
int i = 0;
foreach (PropertyInfo property in checkPropertyInfos)
{
DisplayNameAttribute dn = property.GetCustomAttributes(typeof(DisplayNameAttribute), false).SingleOrDefault() as DisplayNameAttribute;
if (dn != null)
{
SetCell(row, i++, dn.DisplayName);
continue;
}
Type t = property.PropertyType;
if (t.IsGenericType)
{
if (sheetDetailDataWrapper.Titles == null || sheetDetailDataWrapper.Titles.Count() == 0)
{
continue;
}
foreach (var item in sheetDetailDataWrapper.Titles)
{
SetCell(row, i++, item.TypeName);
}
}
}
if (titleCell != null && i > 0)
{
titleCell.MergeTo(titleRow.CreateCell(i - 1));
titleCell.CellStyle = this.CenterStyle;
}
StartRow++;
return sheet;
}
#endregion
#region AddValue
private ISheet AddValue(ISheet sheet, ISheetDataWrapper sheetDetailDataWrapper, Type type)
{
IList<PropertyInfo> checkPropertyInfos = ExcelModelsPropertyManage.CreatePropertyInfos(type);
Int32 cellCount = 0;
foreach (var item in sheetDetailDataWrapper.Datas)
{
if (item == null)
{
StartRow++;
continue;
}
IRow newRow = sheet.CreateRow(StartRow);
buildContext.Row = newRow;
foreach (PropertyInfo property in checkPropertyInfos)
{
Object obj = property.GetValue(item, null);
Type t = property.PropertyType;
if (t.IsGenericType && t.GetGenericTypeDefinition() == typeof(IEnumerable<>))
{
var ssd = ((IEnumerable)obj).Cast<IExtendedBase>();
if (ssd == null)
{
continue;
}
foreach (var v in sheetDetailDataWrapper.Titles)
{
IExtendedBase sv = ssd.Where(s => s.TypeId == v.TypeId).SingleOrDefault();
SetCell(newRow, cellCount++, sv.TypeValue);
}
continue;
}
SetCell(newRow, cellCount++, obj);
}
StartRow++;
cellCount = 0;
}
return sheet;
}
#endregion
#region 设置单元格
/// <summary>
/// 设置单元格
/// </summary>
/// <param name="row"></param>
/// <param name="index"></param>
/// <param name="value"></param>
/// <returns></returns>
private ICell SetCell(IRow row, int index, object value)
{
ICell cell = row.CreateCell(index);
SetCellValue(cell, value);
buildContext.Cell = cell;
if (OnContentCellSetAfter != null)
{
OnContentCellSetAfter(buildContext);
}
return cell;
}
#endregion
#region ExcelToDataTable
/// <summary>
/// 导入
/// </summary>
/// <typeparam name="T">具体对象</typeparam>
/// <param name="fs"></param>
/// <param name="fileName"></param>
/// <param name="isFirstRowColumn"></param>
/// <returns></returns>
public static IEnumerable<T> ExcelToDataTable<T>(Stream fs, bool isFirstRowColumn = false) where T : new()
{
List<T> ts = new List<T>();
Type type = typeof(T);
IList<PropertyInfo> checkPropertyInfos = ExcelModelsPropertyManage.CreatePropertyInfos(type);
try
{
IWorkbook workbook = WorkbookFactory.Create(fs);
fs.Dispose();
ISheet sheet = workbook.GetSheetAt(0);
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
Int32 startRow = isFirstRowColumn ? 1 : 0;
int rowCount = sheet.LastRowNum; //行数
int length = checkPropertyInfos.Count;
length = length > cellCount + 1 ? cellCount + 1 : length;
Boolean haveValue = false;
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null) continue; //没有数据的行默认是null
T t = new T();
for (int f = 0; f < length; f++)
{
ICell cell = row.GetCell(f);
if (cell == null || String.IsNullOrEmpty(cell.ToString()))
{
continue;
}
object b = cell.ToString();
if (cell.CellType == CellType.Numeric)
{
//NPOI中数字和日期都是NUMERIC类型的,这里对其进行判断是否是日期类型
if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
{
b = cell.DateCellValue;
}
else
{
b = cell.NumericCellValue;
}
}
PropertyInfo pinfo = checkPropertyInfos[f];
if (pinfo.PropertyType.Name != b.GetType().Name) //类型不一样的时候,强转
{
b = System.ComponentModel.TypeDescriptor.GetConverter(pinfo.PropertyType).ConvertFrom(b.ToString());
}
type.GetProperty(pinfo.Name).SetValue(t, b, null);
if (!haveValue)
{
haveValue = true;
}
}
if (haveValue)
{
ts.Add(t); haveValue = false;
}
}
}
return ts;
}
catch (Exception ex)
{
return null;
}
}
#endregion
}
public class BuildContext
{
public WorkbookBuilder WorkbookBuilder { get; set; }
public IWorkbook Workbook { get; set; }
public ISheet Sheet { get; set; }
public IRow Row { get; set; }
public ICell Cell { get; set; }
}
}
WorkbookTest.cs
using System;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System.Collections.Generic;
using UnitTestProject.Model;
using ExcelHelper.Operating;
using System.Linq;
using System.IO;
using ExcelHelper.Operating.Model;
namespace UnitTestProject
{
[TestClass]
public class WorkbookTest
{
[TestMethod]
public void WorkbookInsert()
{
List<SumAnalysisX> sumAnalysisXs = new List<SumAnalysisX>();
for (int i = 0; i < 8; i++)
{
SumAnalysisX sumAnalysisX1 = new SumAnalysisX() { ClassName = null, MaxScore = 100 + i, Total = 1000 + i, TotalAverage = 10 + i };
List<SubjectScoreDetailX> ssd = new List<SubjectScoreDetailX>();
for (int j = 0; j < 4; j++)
{
ssd.Add(new SubjectScoreDetailX() { Score = null, TypeName = "kemu " + j.ToString(), TypeId = j });
}
sumAnalysisX1.SubjectScoreDetails = ssd;
sumAnalysisXs.Add(sumAnalysisX1);
}
WorkbookWrapper w = new WorkbookWrapper();
SheetDetail sd = new SheetDetail("sxf");
SheetDetailDataWrapper sheetDetailDataWrapper = new SheetDetailDataWrapper("No.1", sumAnalysisXs.Cast<IExcelModelBase>());
sd.SheetDetailDataWrappers.Add(sheetDetailDataWrapper);
w.AddSheetDetail(sd);
w.Save(@"C:\sxf.xls");
}
[TestMethod]
public void WorkbookInsert2()
{
String path = AppDomain.CurrentDomain.BaseDirectory + "/D.xls";
IEnumerable<PointsCoupon> pcs;
using (FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read))
{
pcs = WorkbookBuilder.ExcelToDataTable<PointsCoupon>(fs, false);
}
foreach (var item in pcs)
{
Console.WriteLine(item.ParValue);
}
}
}
}
六、总结
看似简单的逻辑在具体实施还是会碰到的许多问题,尤其是NPOI
的数据类型与想要的类型的不符的处理;通用的实现等等,不过幸运的是最后还是出一个满意的版本,这应该算自己第一个面向接口的编程的例子了。
如果你发现什么问题或者有更好的实现方式麻烦留言或者与我联系!
项目地址:https://github.com/aa317016589/ExcelHelper/
唯一不变的就是变。