一、引子
新進公司被安排處理系統的數據報表任務——對學生的考試成績進行統計并能導出到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 特性標記來實現的。
五、主要實現類
1 using NPOI.HSSF.UserModel; 2 using NPOI.SS.UserModel; 3 using System; 4 using System.Collections.Generic; 5 using System.IO; 6 using System.Linq; 7 using System.Reflection; 8 using System.ComponentModel; 9 using System.Collections; 10 11 12 namespace ExcelHelper.Operating 13 { 14 public abstract class WorkbookBuilder 15 { 16 protected WorkbookBuilder() 17 { 18 currentWorkbook = CreateWorkbook(); 19 20 buildContext = new BuildContext() { WorkbookBuilder = this, Workbook = currentWorkbook }; 21 } 22 23 public delegate void BuildEventHandler(BuildContext context); 24 25 protected abstract IWorkbook CreateWorkbook(); 26 27 public IWorkbook currentWorkbook; 28 29 private ICellStyle _centerStyle; 30 31 public ICellStyle CenterStyle 32 { 33 get 34 { 35 if (_centerStyle == null) 36 { 37 _centerStyle = currentWorkbook.CreateCellStyle(); 38 39 _centerStyle.Alignment = HorizontalAlignment.Center; 40 41 _centerStyle.VerticalAlignment = VerticalAlignment.Center; 42 } 43 44 return _centerStyle; 45 } 46 } 47 48 private Int32 StartRow = 0;//起始行 49 50 51 private BuildContext buildContext; 52 53 public event BuildEventHandler OnHeadCellSetAfter; 54 55 public event BuildEventHandler OnContentCellSetAfter; 56 57 58 #region DataTableToExcel 59 60 public void Insert(ISheetDetail sheetDetail) 61 { 62 ISheet sheet; 63 64 if (sheetDetail.IsContinue) 65 { 66 sheet = currentWorkbook.GetSheetAt(currentWorkbook.NumberOfSheets - 1); 67 68 StartRow = sheet.LastRowNum + 1; 69 } 70 else 71 { 72 sheet = currentWorkbook.CreateSheet(sheetDetail.SheetName); 73 } 74 75 buildContext.Sheet = sheet; 76 77 sheet = DataToSheet(sheetDetail.SheetDetailDataWrappers, sheet); 78 79 } 80 /// <summary> 81 /// 這里添加數據，循環添加，主要應對由多個組成的 82 /// </summary> 83 /// <param name="sheetDetailDataWrappers"></param> 84 /// <param name="sheet"></param> 85 /// <returns></returns> 86 private ISheet DataToSheet(SheetDataCollection sheetDetailDataWrappers, ISheet sheet) 87 { 88 foreach (var sheetDetailDataWrapper in sheetDetailDataWrappers) 89 { 90 if (sheetDetailDataWrapper.Datas == null || sheetDetailDataWrapper.Datas.Count() == 0) 91 { 92 continue; 93 } 94 95 Type type = sheetDetailDataWrapper.Datas.GetType().GetGenericArguments()[0]; 96 97 if (sheetDetailDataWrapper.HaveTitle) 98 { 99 sheet = SetTitle(sheet, sheetDetailDataWrapper, type);100 }101 102 sheet = AddValue(sheet, sheetDetailDataWrapper, type);103 104 StartRow = StartRow + sheetDetailDataWrapper.EmptyIntervalRow;105 }106 107 return sheet;108 }109 110 #endregion111 112 #region 設置值113 114 private void SetCellValue(ICell cell, object obj)115 {116 if (obj == null)117 {118 cell.SetCellValue(" "); return;119 }120 121 if (obj is String)122 {123 cell.SetCellValue(obj.ToString()); return;124 }125 126 if (obj is Int32 || obj is Double)127 {128 cell.SetCellValue(Math.Round(Double.Parse(obj.ToString()), 2)); return;129 }130 131 if (obj.GetType().IsEnum)132 {133 cell.SetCellValue(EnumService.GetDescription((Enum)obj)); return;134 }135 136 if (obj is DateTime)137 {138 cell.SetCellValue(((DateTime)obj).ToString("yyyy-MM-dd HH:mm:ss")); return;139 }140 141 if (obj is Boolean)142 {143 cell.SetCellValue((Boolean)obj ? "√" : "×"); return;144 } 145 }146 147 #endregion148 149 #region SetTitle150 private ISheet SetTitle(ISheet sheet, ISheetDataWrapper sheetDetailDataWrapper, Type type)151 {152 IRow titleRow = null;153 154 ICell titleCell = null;155 156 if (!String.IsNullOrEmpty(sheetDetailDataWrapper.DataName))157 {158 titleRow = sheet.CreateRow(StartRow);159 160 buildContext.Row = titleRow;161 162 StartRow++;163 164 titleCell = SetCell(titleRow, 0, sheetDetailDataWrapper.DataName);165 166 if (OnHeadCellSetAfter != null)167 {168 OnHeadCellSetAfter(buildContext);169 }170 }171 172 IRow row = sheet.CreateRow(StartRow);173 174 buildContext.Row = row;175 176 IList<PropertyInfo> checkPropertyInfos = ExcelModelsPropertyManage.CreatePropertyInfos(type);177 178 int i = 0;179 180 foreach (PropertyInfo property in checkPropertyInfos)181 {182 DisplayNameAttribute dn = property.GetCustomAttributes(typeof(DisplayNameAttribute), false).SingleOrDefault() as DisplayNameAttribute;183 184 if (dn != null)185 {186 SetCell(row, i++, dn.DisplayName);187 continue;188 }189 190 Type t = property.PropertyType;191 192 if (t.IsGenericType)193 {194 if (sheetDetailDataWrapper.Titles == null || sheetDetailDataWrapper.Titles.Count() == 0)195 {196 continue;197 }198 199 foreach (var item in sheetDetailDataWrapper.Titles)200 {201 SetCell(row, i++, item.TypeName);202 }203 }204 }205 206 if (titleCell != null && i > 0)207 {208 titleCell.MergeTo(titleRow.CreateCell(i - 1));209 210 titleCell.CellStyle = this.CenterStyle;211 }212 213 StartRow++;214 215 return sheet;216 }217 #endregion218 219 #region AddValue220 private ISheet AddValue(ISheet sheet, ISheetDataWrapper sheetDetailDataWrapper, Type type)221 {222 IList<PropertyInfo> checkPropertyInfos = ExcelModelsPropertyManage.CreatePropertyInfos(type);223 224 Int32 cellCount = 0;225 226 foreach (var item in sheetDetailDataWrapper.Datas)227 {228 if (item == null)229 {230 StartRow++;231 continue;232 }233 234 IRow newRow = sheet.CreateRow(StartRow);235 236 buildContext.Row = newRow;237 238 foreach (PropertyInfo property in checkPropertyInfos)239 {240 Object obj = property.GetValue(item, null);241 242 Type t = property.PropertyType;243 244 if (t.IsGenericType && t.GetGenericTypeDefinition() == typeof(IEnumerable<>))245 {246 var ssd = ((IEnumerable)obj).Cast<IExtendedBase>();247 248 if (ssd == null)249 {250 continue;251 }252 253 foreach (var v in sheetDetailDataWrapper.Titles)254 {255 IExtendedBase sv = ssd.Where(s => s.TypeId == v.TypeId).SingleOrDefault();256 257 SetCell(newRow, cellCount++, sv.TypeValue);258 }259 260 continue;261 }262 263 SetCell(newRow, cellCount++, obj);264 }265 266 StartRow++;267 cellCount = 0;268 }269 270 return sheet;271 }272 273 #endregion274 275 #region 設置單元格276 /// <summary>277 /// 設置單元格278 /// </summary>279 /// <param name="row"></param>280 /// <param name="index"></param>281 /// <param name="value"></param>282 /// <returns></returns>283 private ICell SetCell(IRow row, int index, object value)284 {285 ICell cell = row.CreateCell(index);286 287 SetCellValue(cell, value);288 289 buildContext.Cell = cell;290 291 if (OnContentCellSetAfter != null)292 {293 OnContentCellSetAfter(buildContext);294 }295 296 return cell;297 } 298 #endregion299 300 #region ExcelToDataTable301 302 /// <summary>303 /// 導入304 /// </summary>305 /// <typeparam name="T">具體對象</typeparam>306 /// <param name="fs"></param>307 /// <param name="fileName"></param>308 /// <param name="isFirstRowColumn"></param>309 /// <returns></returns>310 public static IEnumerable<T> ExcelToDataTable<T>(Stream fs, bool isFirstRowColumn = false) where T : new()311 {312 List<T> ts = new List<T>();313 314 Type type = typeof(T);315 316 IList<PropertyInfo> checkPropertyInfos = ExcelModelsPropertyManage.CreatePropertyInfos(type);317 318 try319 {320 IWorkbook workbook = WorkbookFactory.Create(fs);321 322 fs.Dispose();323 324 ISheet sheet = workbook.GetSheetAt(0);325 326 if (sheet != null)327 {328 IRow firstRow = sheet.GetRow(0);329 330 int cellCount = firstRow.LastCellNum; //一行最后一個cell的編號 即總的列數331 332 Int32 startRow = isFirstRowColumn ? 1 : 0;333 334 int rowCount = sheet.LastRowNum; //行數335 336 int length = checkPropertyInfos.Count;337 338 length = length > cellCount + 1 ? cellCount + 1 : length;339 340 Boolean haveValue = false;341 342 for (int i = startRow; i <= rowCount; ++i)343 {344 IRow row = sheet.GetRow(i);345 346 if (row == null) continue; //沒有數據的行默認是null 347 348 T t = new T();349 350 for (int f = 0; f < length; f++)351 {352 ICell cell = row.GetCell(f);353 354 if (cell == null || String.IsNullOrEmpty(cell.ToString()))355 {356 continue;357 }358 359 object b = cell.ToString();360 361 if (cell.CellType == CellType.Numeric)362 {363 //NPOI中數字和日期都是NUMERIC類型的，這里對其進行判斷是否是日期類型364 if (HSSFDateUtil.IsCellDateFormatted(cell))//日期類型365 {366 b = cell.DateCellValue;367 }368 else369 {370 b = cell.NumericCellValue;371 }372 }373 374 PropertyInfo pinfo = checkPropertyInfos[f];375 376 if (pinfo.PropertyType.Name != b.GetType().Name) //類型不一樣的時候，強轉377 {378 b = System.ComponentModel.TypeDescriptor.GetConverter(pinfo.PropertyType).ConvertFrom(b.ToString());379 }380 381 type.GetProperty(pinfo.Name).SetValue(t, b, null);382 383 if (!haveValue)384 {385 haveValue = true;386 }387 }388 if (haveValue)389 {390 ts.Add(t); haveValue = false;391 }392 }393 }394 395 return ts;396 }397 catch (Exception ex)398 {399 return null;400 }401 }402 403 #endregion404 }405 406 public class BuildContext407 {408 public WorkbookBuilder WorkbookBuilder { get; set; }409 410 public IWorkbook Workbook { get; set; }411 412 public ISheet Sheet { get; set; }413 414 public IRow Row { get; set; }415 416 public ICell Cell { get; set; }417 418 }419 }
六、總結
看似簡單的邏輯在具體實施還是會碰到的許多問題，尤其是NPOI的數據類型與想要的類型的不符的處理；通用的實現等等，不過幸運的是最后還是出一個滿意的版本，這應該算自己第一個面向接口的編程的例子了。
如果你發現什么問題或者有更好的實現方式麻煩留言或者與我聯系！
項目地址：https://github.com/aa317016589/ExcelHelper/
新聞熱點
疑難解答
