不依赖Excel是否安装的Excel导入导出类

前端之家收集整理的这篇文章主要介绍了不依赖Excel是否安装的Excel导入导出类前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

本文利用第三方开源库NPOI实现Excel97-2003,Excel2007+的数据导入导出操作。不依赖Office是否安装。NPOI开源项目地址:http://npoi.codeplex.com/。

文件下载:http://npoi.codeplex.com/releases/view/115353


  1. using System;
  2. using System.Collections;
  3. using System.Collections.Generic;
  4. using System.Data;
  5. using System.IO;
  6. using System.Reflection;
  7. using System.Text;
  8. using System.Windows.Forms;
  9. using NPOI.HPSF;
  10. using NPOI.HSSF.UserModel;
  11. using NPOI.HSSF.Util;
  12. using NPOI.SS.UserModel;
  13. using NPOI.SS.Util;
  14. using NPOI.XSSF.UserModel;
  15. using NPOI.XSSF.Util;
  16.  
  17. namespace Youwei.Common
  18. {
  19. /// <summary>
  20. /// Excel操作类。利用第三方开源库NPOI实现Excel97-2003,Excel2007+的数据导入导出操作。不依赖Office是否安装。
  21. /// NPOI开源项目地址:http://npoi.codeplex.com/
  22. /// </summary>
  23. public class ExcelHelper
  24. {
  25. private static String FilterExcel = "Excel文件 (*.xls;*.xlsx)|*.xls;*.xlsx";
  26.  
  27. /// <summary>
  28. /// 从网格将数据导出到Excel,支持Excel97-2003(.xls)、Excel2007+(.xlsx)
  29. /// </summary>
  30. /// <param name="dgv">网格</param>
  31. /// <param name="ignoredColumns">要忽略导出的列名称集合</param>
  32. /// <param name="fileName">导出到的文件名。为空时将弹出保存对话框</param>
  33. public static void ExportToExcel(DataGridView dgv,List<string> ignoredColumns = null,string fileName = "")
  34. {
  35. if (String.IsNullOrEmpty(fileName))
  36. fileName = Dialog.SaveFileDialog(FilterExcel);
  37.  
  38. if (String.IsNullOrEmpty(fileName))
  39. return;
  40.  
  41. bool isSuccess = false;
  42. IWorkbook workBook = null;
  43. ISheet sheet = null;
  44. IRow dataRow = null;
  45. try
  46. {
  47. //不同格式实例化不同工作薄
  48. if (fileName.EndsWith(".xls"))
  49. workBook = new HSSFWorkbook();
  50. else if (fileName.EndsWith(".xlsx"))
  51. workBook = new XSSFWorkbook();
  52.  
  53. sheet = workBook.CreateSheet();
  54. dataRow = sheet.CreateRow(0);
  55.  
  56. //表头样式
  57. ICellStyle headerStyle = workBook.CreateCellStyle();
  58. headerStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
  59. headerStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
  60. headerStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
  61. headerStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
  62.  
  63. headerStyle.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
  64. headerStyle.LeftBorderColor = HSSFColor.Black.Index;
  65. headerStyle.RightBorderColor = HSSFColor.Black.Index;
  66. headerStyle.TopBorderColor = HSSFColor.Black.Index;
  67.  
  68. IFont font = workBook.CreateFont();
  69. font.FontHeightInPoints = 10;
  70. font.Boldweight = 700;
  71. headerStyle.SetFont(font);
  72. headerStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
  73.  
  74.  
  75. int i = 0,j = 0;
  76. int ignoreCnt = 0;
  77. //填充表头
  78. for (i = 0; i < dgv.Columns.Count; i++)
  79. {
  80. if ((ignoredColumns != null && ignoredColumns.Contains(dgv.Columns[i].Name)) || !dgv.Columns[i].Visible || string.IsNullOrEmpty(dgv.Columns[i].HeaderText))
  81. {
  82. ignoreCnt++;
  83. continue;
  84. }
  85. dataRow.CreateCell(i - ignoreCnt).SetCellValue(dgv.Columns[i].HeaderText);
  86. dataRow.Cells[i - ignoreCnt].CellStyle = headerStyle;
  87. }
  88.  
  89. //内容样式
  90. ICellStyle cellStyle = headerStyle;
  91. cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
  92. font = workBook.CreateFont();
  93. font.FontHeightInPoints = 10;
  94. font.Boldweight = 100;
  95. cellStyle.SetFont(font);
  96.  
  97. //填充内容
  98. DataGridViewCell cell = null;
  99. for (i = 0; i < dgv.Rows.Count; i++)
  100. {
  101. dataRow = sheet.CreateRow(i + 1);
  102. ignoreCnt = 0;
  103. for (j = 0; j < dgv.Columns.Count; j++)
  104. {
  105. if ((ignoredColumns != null && ignoredColumns.Contains(dgv.Columns[j].Name)) || !dgv.Columns[j].Visible || string.IsNullOrEmpty(dgv.Columns[j].HeaderText))
  106. {
  107. ignoreCnt++;
  108. continue;
  109. }
  110. cell = dgv[j,i];
  111. if (cell is DataGridViewComboBoxCell)
  112. dataRow.CreateCell(j - ignoreCnt).SetCellValue(ConvertHelper.ToString(cell.FormattedValue));
  113. else
  114. dataRow.CreateCell(j - ignoreCnt).SetCellValue(ConvertHelper.ToString(cell.Value));
  115. dataRow.Cells[j - ignoreCnt].CellStyle = cellStyle;
  116. }
  117. }
  118.  
  119. //写文件
  120. using (MemoryStream ms = new MemoryStream())
  121. {
  122. using (FileStream fs = new FileStream(fileName,FileMode.Create,FileAccess.Write))
  123. {
  124. workBook.Write(ms);
  125. ms.Flush();
  126. ms.Position = 0;
  127. byte[] data = ms.ToArray();
  128. fs.Write(data,data.Length);
  129. fs.Flush();
  130. data = null;
  131. isSuccess = true;
  132. }
  133. }
  134.  
  135. //打开文件
  136. if (isSuccess && Dialog.Confirm("数据已经导出到Excel成功,你要打开吗?") == DialogResult.Yes)
  137. {
  138. Util.OpenFile(fileName);
  139. }
  140. }
  141. catch (Exception ex)
  142. {
  143. Dialog.Error(ex,System.Reflection.MethodBase.GetCurrentMethod(),true);
  144. }
  145. finally
  146. {
  147. if (dataRow != null)
  148. dataRow = null;
  149.  
  150. if (sheet != null)
  151. sheet = null;
  152.  
  153. if (workBook != null)
  154. {
  155. workBook.Clear();
  156. workBook = null;
  157. }
  158. }
  159. }
  160.  
  161. /// <summary>
  162. /// 从Excel导入数据到实体类集合。支持Excel97-2003(.xls)、Excel2007+(.xlsx)
  163. /// </summary>
  164. /// <typeparam name="T">实体类</typeparam>
  165. /// <param name="fileName">要导入的Excel文件名。为空时将弹出保存对话框</param>
  166. /// <param name="propertyTextNamePair">实体类属性名称属性名对应键值对</param>
  167. /// <param name="startSheet">导入的起始Excel表单序号</param>
  168. /// <param name="startRow">导入的起始Excel行号</param>
  169. /// <param name="startColumn">导入的起始Excel列号</param>
  170. /// <returns>泛型实体类集合</returns>
  171. public static List<T> ImportFromExcel<T>(string fileName,Dictionary<string,string> propertyTextNamePair,int startSheet = 0,int startRow = 0,int startColumn = 0) where T : new()
  172. {
  173. List<T> list = new List<T>();
  174. if (String.IsNullOrEmpty(fileName) || !System.IO.File.Exists(fileName))
  175. fileName = Dialog.OpenFileDialog(FilterExcel);
  176.  
  177. if (String.IsNullOrEmpty(fileName) || !System.IO.File.Exists(fileName))
  178. return list;
  179.  
  180. IWorkbook workBook = null;
  181. ISheet sheet = null;
  182. IRow row = null;
  183.  
  184. try
  185. {
  186. //加载文档
  187. using (FileStream fileStream = new FileStream(fileName,FileMode.Open))
  188. {
  189. //不同格式实例化不同工作薄
  190. if (fileName.EndsWith(".xls"))
  191. workBook = new HSSFWorkbook(fileStream);
  192. else if (fileName.EndsWith(".xlsx"))
  193. workBook = new XSSFWorkbook(fileStream);
  194. }
  195.  
  196. //加载指定工作薄
  197. sheet = workBook.GetSheetAt(startSheet);
  198. //workBook.NumberOfSheets //工作薄的表单数
  199.  
  200. //加载表头
  201. IRow headerRow = sheet.GetRow(startRow);
  202. ICell cellHeader = null;
  203. ICell cell = null;
  204. int cellCount = headerRow.LastCellNum;
  205.  
  206. //获取实体类属性
  207. Type type = typeof(T);
  208. PropertyInfo[] ps = type.GetProperties();
  209. T t = default(T);
  210. PropertyInfo p = null;
  211.  
  212. //遍历行列,赋值到实体类,并添加到实体类集合
  213. for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
  214. {
  215. row = sheet.GetRow(i);
  216. t = new T();
  217. for (int j = 0; j < cellCount; j++)
  218. {
  219. cellHeader = headerRow.GetCell(j);
  220. cell = row.GetCell(j);
  221.  
  222. if (propertyTextNamePair != null && propertyTextNamePair.ContainsKey(cellHeader.ToString()))
  223. p = type.GetProperty(propertyTextNamePair[cellHeader.ToString()]);
  224. else
  225. p = type.GetProperty(cellHeader.ToString());
  226.  
  227. if (p != null)
  228. p.SetValue(t,Convert.ChangeType(cell.ToString(),p.PropertyType),null);
  229. }
  230. list.Add(t);
  231. }
  232.  
  233. //回收资源
  234. ps = null;
  235. cellHeader = null;
  236. headerRow = null;
  237. }
  238. catch (Exception ex)
  239. {
  240. Dialog.Error(ex,true);
  241. }
  242. finally
  243. {
  244. if (row != null)
  245. row = null;
  246.  
  247. if (sheet != null)
  248. sheet = null;
  249.  
  250. if (workBook != null)
  251. {
  252. workBook.Clear();
  253. workBook = null;
  254. }
  255. }
  256.  
  257. return list;
  258. }
  259. }
  260. }

猜你在找的设计模式相关文章