扩展NPOI，一行代码导出二进制Excel(下)
2009-12-30 13:52 Zork 阅读(890) 评论(0) 编辑 收藏 举报话接上一回。扩展NPOI，一行代码导出二进制Excel
在上一篇Blog中，我们已经讲解了生成Excel所需要的接口和类库。然而真正实现生成Excel代码的逻辑还没有给出，这也是本文的目的。
回顾上一篇文章，生成Excel的方法的实现代码如下：
ReportExcel
1 /// <summary>
2 /// 导出Excel
3 /// </summary>
4 /// <typeparam name="T"></typeparam>
5 /// <param name="dataSource"></param>
6 /// <param name="sheetName"></param>
7 /// <param name="writer"></param>
8 /// <param name="columns"></param>
9 public static void ReportExcel<T>(IEnumerable<T> dataSource, String sheetName, Stream writer, Action<IRootExcelColumnBuilder<T>> columns)
10 where T : class
11 {
12 ReportExcel(dataSource, sheetName, writer, columns, null, null, null, null);
13 }
14
15 /// <summary>
16 /// 导出Excel
17 /// </summary>
18 /// <typeparam name="T"></typeparam>
19 /// <param name="dataSource"></param>
20 /// <param name="sheetName"></param>
21 /// <param name="writer"></param>
22 /// <param name="columns"></param>
23 /// <param name="documentProperty"></param>
24 /// <param name="workbookProperty"></param>
25 public static void ReportExcel<T>(IEnumerable<T> dataSource, String sheetName, Stream writer, Action<IRootExcelColumnBuilder<T>> columns,
26 Action<DocumentSummaryInformation> documentProperty, Action<SummaryInformation> summaryProperty,
27 Action<HSSFCellStyle> defaultHeaderStyle, Action<HSSFCellStyle> defaultBodyStyle)
28 where T : class
29 {
30 var exporter = new ExcelExporter(writer);
31 exporter.DocumentProperty = documentProperty;
32 exporter.SummaryProperty = summaryProperty;
33 exporter.DefaultHeaderStyle = defaultHeaderStyle;
34 exporter.DefaultBodyStyle = defaultBodyStyle;
35
36 var columnBuilder = CreateColumnBuilder(columns);
37 //生成Excel
38 exporter.GenerateSheet(sheetName, dataSource, columnBuilder);
39
40 exporter.Save();
41 }
2 /// 导出Excel
3 /// </summary>
4 /// <typeparam name="T"></typeparam>
5 /// <param name="dataSource"></param>
6 /// <param name="sheetName"></param>
7 /// <param name="writer"></param>
8 /// <param name="columns"></param>
9 public static void ReportExcel<T>(IEnumerable<T> dataSource, String sheetName, Stream writer, Action<IRootExcelColumnBuilder<T>> columns)
10 where T : class
11 {
12 ReportExcel(dataSource, sheetName, writer, columns, null, null, null, null);
13 }
14
15 /// <summary>
16 /// 导出Excel
17 /// </summary>
18 /// <typeparam name="T"></typeparam>
19 /// <param name="dataSource"></param>
20 /// <param name="sheetName"></param>
21 /// <param name="writer"></param>
22 /// <param name="columns"></param>
23 /// <param name="documentProperty"></param>
24 /// <param name="workbookProperty"></param>
25 public static void ReportExcel<T>(IEnumerable<T> dataSource, String sheetName, Stream writer, Action<IRootExcelColumnBuilder<T>> columns,
26 Action<DocumentSummaryInformation> documentProperty, Action<SummaryInformation> summaryProperty,
27 Action<HSSFCellStyle> defaultHeaderStyle, Action<HSSFCellStyle> defaultBodyStyle)
28 where T : class
29 {
30 var exporter = new ExcelExporter(writer);
31 exporter.DocumentProperty = documentProperty;
32 exporter.SummaryProperty = summaryProperty;
33 exporter.DefaultHeaderStyle = defaultHeaderStyle;
34 exporter.DefaultBodyStyle = defaultBodyStyle;
35
36 var columnBuilder = CreateColumnBuilder(columns);
37 //生成Excel
38 exporter.GenerateSheet(sheetName, dataSource, columnBuilder);
39
40 exporter.Save();
41 }
抛开其它杂项，请看方法exporter.GenerateSheet(sheetName, dataSource, columnBuilder);的实现：
GenerateSheet /// <summary>
/// 生成工作表
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sheetName">工作表名称</param>
/// <param name="items">数据源</param>
/// <param name="columns">数据列构建器</param>
public void GenerateSheet<T>(String sheetName, IEnumerable<T> items, ExcelColumnBuilder<T> columns)
where T : class
{
//设置Table样式
HSSFSheet sheet = String.IsNullOrEmpty(sheetName) ? this.Workbook.CreateSheet() : this.Workbook.CreateSheet(sheetName);
this.CurrentSheet = sheet;
//生成样式
this.GenerateHeader(columns, sheet);
//生成Body
this.GenerateItems(items, columns, sheet);
}
/// 生成工作表
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sheetName">工作表名称</param>
/// <param name="items">数据源</param>
/// <param name="columns">数据列构建器</param>
public void GenerateSheet<T>(String sheetName, IEnumerable<T> items, ExcelColumnBuilder<T> columns)
where T : class
{
//设置Table样式
HSSFSheet sheet = String.IsNullOrEmpty(sheetName) ? this.Workbook.CreateSheet() : this.Workbook.CreateSheet(sheetName);
this.CurrentSheet = sheet;
//生成样式
this.GenerateHeader(columns, sheet);
//生成Body
this.GenerateItems(items, columns, sheet);
}
从该段代码中可以看出生成Excel的顺序是：
1. 生成工作表Sheet；
2. 生成数据列头样式；
3. 生成Excel数据单元格。
GenerateItems 1 /// <summary>
2 /// 生成数据行
3 /// </summary>
4 protected virtual void GenerateItems<T>(IEnumerable<T> dataSource, ExcelColumnBuilder<T> columns, HSSFSheet sheet)
5 where T : class
6 {
7 if (!dataSource.Any())
8 {
9 return;
10 }
11
12 //保存单元格样式信息
13 List<HSSFCellStyle> bodyStyles = new List<HSSFCellStyle>(columns.ColumnCount);
14 //生成样式信息
15 foreach (var column in columns)
16 {
17 #region 设置样式
18 HSSFCellStyle bodyStyle = this.Workbook.CreateCellStyle();
19 this.SetDefaultBodyStyle(bodyStyle);
20 if (column.BodyStyle != null)
21 {
22 column.BodyStyle(bodyStyle);
23 }
24 bodyStyles.Add(bodyStyle);
25 #endregion
26 }
27
28 //第一行用户数据列头
29 Int32 rowIndex = 1;
30 foreach (var item in dataSource)
31 {
32 HSSFRow row = sheet.CreateRow(rowIndex);
33 Int32 columnIndex = 0;
34 rowIndex++;
35
36 foreach (var column in columns)
37 {
38 HSSFCell cell = row.CreateCell(columnIndex);
39 cell.CellStyle = bodyStyles[columnIndex];
40 if (column.CustomRenderer == null)
41 {
42 #region 获取数据 并 格式化单元格数据
43 Object value = null;
44 if (column.ColumnDelegate != null)
45 {
46 value = column.ColumnDelegate(item);
47 }
48 else
49 {
50 var property = item.GetType().GetProperty(column.Name);
51 if (property != null)
52 {
53 value = property.GetValue(item, null);
54 }
55 }
56
57 String formattedValue = null;
58 if (value != null)
59 {
60 if (column.Format != null)
61 {
62 formattedValue = String.Format(column.Format, value);
63 }
64 else
65 {
66 formattedValue = value.ToString();
67 }
68 }
69 #endregion
70
71 cell.SetCellValue(formattedValue);
72 }
73 else
74 {
75 //自定义呈现单元格
76 column.CustomRenderer(item, cell);
77 }
78
79 #region 设置链接
80 if (null != column.HrefDelegate)
81 {
82 var href = column.HrefDelegate(item);
83 if (null != href)
84 {
85 var link = new HSSFHyperlink(HSSFHyperlink.LINK_URL)
86 {
87 Address = href.ToString()
88 };
89 cell.Hyperlink = link;
90 }
91 }
92 #endregion
93
94 columnIndex++;
95 }
96 }
97 }
2 /// 生成数据行
3 /// </summary>
4 protected virtual void GenerateItems<T>(IEnumerable<T> dataSource, ExcelColumnBuilder<T> columns, HSSFSheet sheet)
5 where T : class
6 {
7 if (!dataSource.Any())
8 {
9 return;
10 }
11
12 //保存单元格样式信息
13 List<HSSFCellStyle> bodyStyles = new List<HSSFCellStyle>(columns.ColumnCount);
14 //生成样式信息
15 foreach (var column in columns)
16 {
17 #region 设置样式
18 HSSFCellStyle bodyStyle = this.Workbook.CreateCellStyle();
19 this.SetDefaultBodyStyle(bodyStyle);
20 if (column.BodyStyle != null)
21 {
22 column.BodyStyle(bodyStyle);
23 }
24 bodyStyles.Add(bodyStyle);
25 #endregion
26 }
27
28 //第一行用户数据列头
29 Int32 rowIndex = 1;
30 foreach (var item in dataSource)
31 {
32 HSSFRow row = sheet.CreateRow(rowIndex);
33 Int32 columnIndex = 0;
34 rowIndex++;
35
36 foreach (var column in columns)
37 {
38 HSSFCell cell = row.CreateCell(columnIndex);
39 cell.CellStyle = bodyStyles[columnIndex];
40 if (column.CustomRenderer == null)
41 {
42 #region 获取数据 并 格式化单元格数据
43 Object value = null;
44 if (column.ColumnDelegate != null)
45 {
46 value = column.ColumnDelegate(item);
47 }
48 else
49 {
50 var property = item.GetType().GetProperty(column.Name);
51 if (property != null)
52 {
53 value = property.GetValue(item, null);
54 }
55 }
56
57 String formattedValue = null;
58 if (value != null)
59 {
60 if (column.Format != null)
61 {
62 formattedValue = String.Format(column.Format, value);
63 }
64 else
65 {
66 formattedValue = value.ToString();
67 }
68 }
69 #endregion
70
71 cell.SetCellValue(formattedValue);
72 }
73 else
74 {
75 //自定义呈现单元格
76 column.CustomRenderer(item, cell);
77 }
78
79 #region 设置链接
80 if (null != column.HrefDelegate)
81 {
82 var href = column.HrefDelegate(item);
83 if (null != href)
84 {
85 var link = new HSSFHyperlink(HSSFHyperlink.LINK_URL)
86 {
87 Address = href.ToString()
88 };
89 cell.Hyperlink = link;
90 }
91 }
92 #endregion
93
94 columnIndex++;
95 }
96 }
97 }
首先，创建单元格样式，如果有自定义样式委托，则调用委托方法，生成单元格样式。否者使用默认样式。然后， 迭代数据源，生成Excel工作表的数据行。
在创建单元格的过程中，
1. 首先查看单元格是否为自定义创建NPOI单元格。
2. 查看单元格是否有获取数据的委托Func<T, Object> ColumnDelegate，如果存在，从该委托获取值.如果不存在，从数据实体T的属性中获取值。然后提供格式化功能，格式化数据输出。
3. 如果单元格需要超链接，则设置单元格的超链接。
到此，用一行代码导出Excel的目标就实现了。
以下是相关类库和实例下载：
Coding change lives
