NPOIHelper.cs 15 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Data;
  4. using System.IO;
  5. using System.Linq;
  6. using System.Reflection;
  7. using System.Text;
  8. using NPOI.HSSF.UserModel;
  9. using NPOI.SS.UserModel;
  10. using NPOI.SS.Util;
  11. using NPOI.XSSF.UserModel;
  12. using SAGA.DotNetUtils.NPOI;
  13. using SAGA.DotNetUtils.Extend;
  14. namespace SAGA.DotNetUtils.NPOI
  15. {
  16. /// <summary>
  17. /// NPOI操作帮助类
  18. /// </summary>
  19. public class NPOIHelper
  20. {
  21. /// <summary>
  22. /// DataTable导出到Excel文件
  23. /// </summary>
  24. /// <param name="dtSource">源DataTable</param>
  25. /// <param name="strHeaderText">表头文本</param>
  26. /// <param name="strFileName">保存位置</param>
  27. public static void Export(DataTable dtSource, string strHeaderText, string strFileName)
  28. {
  29. using (MemoryStream ms = Export(dtSource, strHeaderText))
  30. {
  31. using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
  32. {
  33. byte[] data = ms.ToArray();
  34. fs.Write(data, 0, data.Length);
  35. fs.Flush();
  36. }
  37. }
  38. }
  39. /// <summary>
  40. /// DataTable导出到Excel的MemoryStream
  41. /// </summary>
  42. /// <param name="dtSource">源DataTable</param>
  43. /// <param name="strHeaderText">表头文本</param>
  44. public static MemoryStream Export(DataTable dtSource, string strHeaderText)
  45. {
  46. XSSFWorkbook workbook = new XSSFWorkbook();
  47. ISheet sheet = workbook.CreateSheet();
  48. ICellStyle dateStyle = workbook.CreateCellStyle();
  49. IDataFormat format = workbook.CreateDataFormat();
  50. dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
  51. #region 取得每列的列宽(最大宽度)
  52. int[] arrColWidth = new int[dtSource.Columns.Count];
  53. foreach (DataColumn item in dtSource.Columns)
  54. {
  55. //GBK对应的code page是CP936
  56. arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
  57. }
  58. for (int i = 0; i < dtSource.Rows.Count; i++)
  59. {
  60. for (int j = 0; j < dtSource.Columns.Count; j++)
  61. {
  62. int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
  63. if (intTemp > arrColWidth[j])
  64. {
  65. arrColWidth[j] = intTemp;
  66. }
  67. }
  68. }
  69. #endregion
  70. int rowIndex = 0;
  71. foreach (DataRow row in dtSource.Rows)
  72. {
  73. #region 新建表,填充表头,填充列头,样式
  74. if (rowIndex == 65535 || rowIndex == 0)
  75. {
  76. if (rowIndex != 0)
  77. {
  78. sheet = workbook.CreateSheet();
  79. }
  80. #region 表头及样式
  81. {
  82. IRow headerRow = sheet.CreateRow(0);
  83. headerRow.HeightInPoints = 25;
  84. headerRow.CreateCell(0).SetCellValue(strHeaderText);
  85. ICellStyle headStyle = workbook.CreateCellStyle();
  86. headStyle.Alignment = HorizontalAlignment.Center;
  87. IFont font = workbook.CreateFont();
  88. font.FontHeightInPoints = 20;
  89. font.Boldweight = 700;
  90. headStyle.SetFont(font);
  91. headerRow.GetCell(0).CellStyle = headStyle;
  92. sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
  93. }
  94. #endregion
  95. #region 列头及样式
  96. {
  97. IRow headerRow = sheet.CreateRow(1);
  98. ICellStyle headStyle = workbook.CreateCellStyle();
  99. headStyle.Alignment = HorizontalAlignment.Center;
  100. IFont font = workbook.CreateFont();
  101. font.FontHeightInPoints = 10;
  102. font.Boldweight = 700;
  103. headStyle.SetFont(font);
  104. foreach (DataColumn column in dtSource.Columns)
  105. {
  106. headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
  107. headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
  108. //设置列宽
  109. sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
  110. }
  111. }
  112. #endregion
  113. rowIndex = 2;
  114. }
  115. #endregion
  116. #region 填充内容
  117. ICellStyle contentStyle = workbook.CreateCellStyle();
  118. contentStyle.Alignment = HorizontalAlignment.Left;
  119. IRow dataRow = sheet.CreateRow(rowIndex);
  120. foreach (DataColumn column in dtSource.Columns)
  121. {
  122. ICell newCell = dataRow.CreateCell(column.Ordinal);
  123. newCell.CellStyle = contentStyle;
  124. string drValue = row[column].ToString();
  125. switch (column.DataType.ToString())
  126. {
  127. case "System.String"://字符串类型
  128. newCell.SetCellValue(drValue);
  129. break;
  130. case "System.DateTime"://日期类型
  131. DateTime dateV;
  132. DateTime.TryParse(drValue, out dateV);
  133. newCell.SetCellValue(dateV);
  134. newCell.CellStyle = dateStyle;//格式化显示
  135. break;
  136. case "System.Boolean"://布尔型
  137. bool boolV = false;
  138. bool.TryParse(drValue, out boolV);
  139. newCell.SetCellValue(boolV);
  140. break;
  141. case "System.Int16"://整型
  142. case "System.Int32":
  143. case "System.Int64":
  144. case "System.Byte":
  145. int intV = 0;
  146. int.TryParse(drValue, out intV);
  147. newCell.SetCellValue(intV);
  148. break;
  149. case "System.Decimal"://浮点型
  150. case "System.Double":
  151. double doubV = 0;
  152. double.TryParse(drValue, out doubV);
  153. newCell.SetCellValue(doubV);
  154. break;
  155. case "System.DBNull"://空值处理
  156. newCell.SetCellValue("");
  157. break;
  158. default:
  159. newCell.SetCellValue("");
  160. break;
  161. }
  162. }
  163. #endregion
  164. rowIndex++;
  165. }
  166. using (MemoryStream ms = new MemoryStream())
  167. {
  168. workbook.Write(ms);
  169. ms.Flush();
  170. ms.Position = 0;
  171. //sheet.Dispose();
  172. //workbook.Dispose();//一般只用写这一个就OK了,他会遍历并释放所有资源,但当前版本有问题所以只释放sheet
  173. return ms;
  174. }
  175. }
  176. /// <summary>
  177. /// 用于Web导出
  178. /// </summary>
  179. /// <param name="dtSource">源DataTable</param>
  180. /// <param name="strHeaderText">表头文本</param>
  181. /// <param name="strFileName">文件名</param>
  182. //public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName)
  183. //{
  184. // HttpContext curContext = HttpContext.Current;
  185. // // 设置编码和附件格式
  186. // curContext.Response.ContentType = "application/vnd.ms-excel";
  187. // curContext.Response.ContentEncoding = Encoding.UTF8;
  188. // curContext.Response.Charset = "";
  189. // curContext.Response.AppendHeader("Content-Disposition",
  190. // "attachment;filename=" + HttpUtility.UrlEncode(strFileName + ".xls", Encoding.UTF8));
  191. // curContext.Response.BinaryWrite(Export(dtSource, strHeaderText).GetBuffer());
  192. // curContext.Response.End();
  193. //}
  194. /// <summary>读取excel
  195. /// 默认第一行为标头
  196. /// 导出某一张表
  197. /// </summary>
  198. /// <param name="strFileName">excel文档路径</param>
  199. /// <returns></returns>
  200. public static DataTable ImportAppointSheet(XSSFWorkbook hssfworkbook, int index)
  201. {
  202. ISheet sheet = hssfworkbook.GetSheetAt(index);
  203. if (sheet == null) return null;
  204. DataTable dt = new DataTable(sheet.SheetName);
  205. IRow headerRow = sheet.GetRow(0);
  206. if (headerRow == null) return dt;
  207. if (headerRow.Cells.Count==0)
  208. {
  209. headerRow = sheet.GetRow(1);
  210. }
  211. int cellCount = headerRow.LastCellNum;
  212. for (int j = 0; j < cellCount; j++)
  213. {
  214. ICell cell = headerRow.GetCell(j);
  215. dt.Columns.Add(cell.ToStr());
  216. }
  217. for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
  218. {
  219. IRow row = sheet.GetRow(i);
  220. if(row==null)continue;
  221. DataRow dataRow = dt.NewRow();
  222. for (int j = row.FirstCellNum; j < cellCount; j++)
  223. {
  224. if (row.GetCell(j) != null)
  225. dataRow[j] = row.GetCell(j).ToString();
  226. }
  227. dt.Rows.Add(dataRow);
  228. }
  229. return dt;
  230. }
  231. /// <summary>
  232. /// 导出整个Excel
  233. /// </summary>
  234. /// <param name="strFileName"></param>
  235. /// <returns></returns>
  236. public static DataSet Import(string strFileName)
  237. {
  238. DataSet ds = new DataSet();
  239. XSSFWorkbook hssfworkbook;
  240. using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
  241. {
  242. hssfworkbook = new XSSFWorkbook(file);
  243. }
  244. var sheetCount = hssfworkbook.Count;
  245. for (int i = 0; i < sheetCount; i++)
  246. {
  247. DataTable dt = ImportAppointSheet(hssfworkbook, i);
  248. if (dt != null)
  249. ds.Tables.Add(dt);
  250. }
  251. return ds;
  252. }
  253. /// <summary>
  254. /// 将Excel数据转换为实体列表
  255. /// </summary>
  256. /// <typeparam name="T"></typeparam>
  257. /// <returns></returns>
  258. public static List<T> ConvertExcelSheetToModel<T>(string filePath)
  259. {
  260. var datas = new List<T>();
  261. var type = typeof(T);
  262. var sheetInfo = type.GetAttribute<SheetInfoAttribute>();
  263. if (sheetInfo == null) return datas;
  264. var fileName = filePath;
  265. if (!File.Exists(fileName)) return datas;
  266. using (var fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read))
  267. {
  268. IWorkbook workbook = null; //新建IWorkbook对象
  269. var fileExtension = Path.GetExtension(fileName);
  270. if (fileExtension == ".xlsx") // 2007版本
  271. {
  272. workbook = new XSSFWorkbook(fileStream); //xlsx数据读入workbook
  273. }
  274. else if (fileExtension == ".xls") // 2003版本
  275. {
  276. workbook = new HSSFWorkbook(fileStream); //xls数据读入workbook
  277. }
  278. ISheet sheet = sheetInfo.SheetName == "" ? workbook.GetSheetAt(0) : workbook.GetSheet(sheetInfo.SheetName);
  279. IRow row;
  280. // List<int> showIndex = new List<int> { 1, 2, 3, 4 };
  281. for (int i = sheetInfo.RowStartIndex; i < sheet.LastRowNum; i++)
  282. {
  283. row = sheet.GetRow(i);
  284. if (row != null)
  285. {
  286. datas.Add(ForeachClassProperties<T>(row));
  287. }
  288. }
  289. workbook.Close();
  290. }
  291. return datas;
  292. }
  293. /// <summary>
  294. /// C#反射遍历对象属性
  295. /// </summary>
  296. /// <typeparam name="T">对象类型</typeparam>
  297. /// <param name="model">对象</param>
  298. public static T ForeachClassProperties<T>(IRow row)
  299. {
  300. Type t = typeof(T);
  301. var model = Activator.CreateInstance<T>();
  302. PropertyInfo[] PropertyList = t.GetProperties();
  303. foreach (PropertyInfo item in PropertyList)
  304. {
  305. var index = item.GetCustomAttribute<CellIndexAttribute>()?.Index;
  306. ICell cell = row.GetCell(index ?? 0);
  307. if (cell == null)
  308. {
  309. item.SetValue(model, "");
  310. continue;
  311. }
  312. if (cell.IsMergedCell)
  313. {
  314. //row.Sheet.
  315. var sheet = row.Sheet;
  316. for (int i = 0; i < sheet.NumMergedRegions; i++)
  317. {
  318. var range = sheet.GetMergedRegion(i);
  319. if (range.IsInRange(cell.RowIndex, cell.ColumnIndex))
  320. {
  321. var useRow = row.Sheet.GetRow(range.FirstRow);
  322. item.SetValue(model, useRow.GetCell(range.FirstColumn) + "");
  323. break;
  324. }
  325. }
  326. }
  327. else
  328. {
  329. item.SetValue(model, row.GetCell(index ?? 0) + "");
  330. }
  331. }
  332. return model;
  333. }
  334. }
  335. public class SheetInfoAttribute : Attribute
  336. {
  337. public string FilePath { get; set; }
  338. public string SheetName { get; set; }
  339. public int RowStartIndex { get; set; }
  340. }
  341. public class CellIndexAttribute : Attribute
  342. {
  343. public CellIndexAttribute(int index)
  344. {
  345. Index = index;
  346. }
  347. public int Index { get; set; }
  348. }
  349. public static class RefletcExtend
  350. {
  351. public static T GetAttribute<T>(this Type type) where T : Attribute
  352. {
  353. return (T)type.GetCustomAttributes(typeof(T), false).FirstOrDefault();
  354. }
  355. }
  356. }