查看: 480|回复: 0

[.NET源码] asp.net 利用NPOI导出Excel通用类的方法

发表于 2018-1-1 08:00:00

解决中文文件名保存Excel乱码问题,主要是判断火狐或者IE浏览器,然后做对应的判断处理,核心代码如下:

  1. System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
  2. //设置下载的Excel文件名\
  3. if (System.Web.HttpContext.Current.Request.ServerVariables["http_user_agent"].ToString().IndexOf("Firefox") != -1)
  4. {
  5. //火狐浏览器
  6. System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "=?UTF-8?B?" + Convert.ToBase64String(System.Text.Encoding.UTF8.GetBytes(fileName)) + "?="));
  7. }
  8. else
  9. {
  10. //IE等浏览器
  11. System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8)));
  12. }
复制代码

废话不多说,直接上类库代码,ATNPOIHelper.cs:

  1. using System;
  2. using System.Linq;
  3. using System.Web;
  4. using System.IO;
  5. using NPOI;
  6. using NPOI.SS.Util;
  7. using NPOI.HSSF.Util;
  8. using NPOI.SS.UserModel;
  9. using NPOI.HSSF.UserModel;
  10. using System.Data;
  11. using System.Collections.Generic;
  12. using System.Text;
  13. namespace AT.Utility.DotNetFile
  14. {
  15. /*
  16. 导出Excel包含的功能:
  17. 1.多表头导出最多支持到三行,表头格式说明
  18. 相邻父列头之间用'#'分隔,父列头与子列头用空格(' ‘)分隔,相邻子列头用逗号分隔(‘,')
  19. 两行:序号#分公司#组别#本日成功签约单数 预警,续约,流失,合计#累计成功签约单数 预警,续约,流失,合计#任务数#完成比例#排名
  20. 三行:等级#级别#上期结存 件数,重量,比例#本期调入 收购调入 件数,重量,比例#本期发出 车间投料 件数,重量,比例#本期发出 产品外销百分比 件数,重量,比例#平均值
  21. 三行时请注意:列头要重复
  22. 2.添加表头标题功能
  23. 3.添加序号功能
  24. 4.根据数据设置列宽
  25. 缺陷:
  26. 数据内容不能合并列合并行
  27. 改进思路:
  28. 添加一属性:设置要合并的列,为了实现多列合并可以这样设置{“列1,列2”,”列4”}
  29. */
  30. /// <summary>
  31. /// 利用NPOI实现导出Excel
  32. /// </summary>
  33. public class ATNPOIHelper
  34. {
  35. #region 初始化
  36. /// <summary>
  37. /// 声明 HSSFWorkbook 对象
  38. /// </summary>
  39. private static HSSFWorkbook _workbook;
  40. /// <summary>
  41. /// 声明 HSSFSheet 对象
  42. /// </summary>
  43. private static HSSFSheet _sheet;
  44. #endregion
  45. #region Excel导出
  46. /// <summary>
  47. /// Excel导出
  48. /// </summary>
  49. /// <param name="fileName">文件名称 如果为空或NULL,则默认“新建Excel.xls”</param>
  50. /// <param name="list"></param>
  51. /// <param name="ColMergeNum">合计:末行合计时,合并的列数</param>
  52. /// <param name="method">导出方式 1:WEB导出(默认)2:按文件路径导出</param>
  53. /// <param name="filePath">文件路径 如果WEB导出,则可以为空;如果按文件路径导出,则默认桌面路径</param>
  54. public static void Export(string fileName, IList<NPOIModel> list, int ColMergeNum, int method = 1, string filePath = null)
  55. {
  56. // 文件名称
  57. if (!string.IsNullOrEmpty(fileName))
  58. {
  59. if (fileName.IndexOf('.') == -1)
  60. {
  61. fileName += ".xls";
  62. }
  63. else
  64. {
  65. fileName = fileName.Substring(1, fileName.IndexOf('.')) + ".xls";
  66. }
  67. }
  68. else
  69. {
  70. fileName = "新建Excel.xls";
  71. }
  72. // 文件路径
  73. if (2 == method && string.IsNullOrEmpty(filePath))
  74. {
  75. filePath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
  76. }
  77. // 调用导出处理程序
  78. Export(list, ColMergeNum);
  79. // WEB导出
  80. if (1 == method)
  81. {
  82. System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
  83. //设置下载的Excel文件名\
  84. if (System.Web.HttpContext.Current.Request.ServerVariables["http_user_agent"].ToString().IndexOf("Firefox") != -1)
  85. {
  86. //火狐浏览器
  87. System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "=?UTF-8?B?" + Convert.ToBase64String(System.Text.Encoding.UTF8.GetBytes(fileName)) + "?="));
  88. }
  89. else
  90. {
  91. //IE等浏览器
  92. System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8)));
  93. }
  94. using (MemoryStream ms = new MemoryStream())
  95. {
  96. //将工作簿的内容放到内存流中
  97. _workbook.Write(ms);
  98. //将内存流转换成字节数组发送到客户端
  99. System.Web.HttpContext.Current.Response.BinaryWrite(ms.GetBuffer());
  100. System.Web.HttpContext.Current.Response.End();
  101. _sheet = null;
  102. _workbook = null;
  103. }
  104. }
  105. else if (2 == method)
  106. {
  107. using (FileStream fs = File.Open(filePath, FileMode.Append))
  108. {
  109. _workbook.Write(fs);
  110. _sheet = null;
  111. _workbook = null;
  112. }
  113. }
  114. }
  115. /// <summary>
  116. /// 导出方法实现
  117. /// </summary>
  118. /// <param name="list"></param>
  119. private static void Export(IList<NPOIModel> list, int ColMergeNum)
  120. {
  121. #region 变量声明
  122. // 初始化
  123. _workbook = new HSSFWorkbook();
  124. // 声明 Row 对象
  125. IRow _row;
  126. // 声明 Cell 对象
  127. ICell _cell;
  128. // 总列数
  129. int cols = 0;
  130. // 总行数
  131. int rows = 0;
  132. // 行数计数器
  133. int rowIndex = 0;
  134. // 单元格值
  135. string drValue = null;
  136. #endregion
  137. foreach (NPOIModel model in list)
  138. {
  139. // 工作薄命名
  140. if (model.sheetName != null)
  141. _sheet = (HSSFSheet)_workbook.CreateSheet(model.sheetName);
  142. else
  143. _sheet = (HSSFSheet)_workbook.CreateSheet();
  144. // 获取数据源
  145. DataTable dt = model.dataSource;
  146. // 初始化
  147. rowIndex = 0;
  148. // 获取总行数
  149. rows = GetRowCount(model.headerName);
  150. // 获取总列数
  151. cols = GetColCount(model.headerName);
  152. //合计:合并表格末行N列,rows为表头行数,dt.Rows.Count为数据行数
  153. if (ColMergeNum > 1)
  154. {
  155. CellRangeAddress region_Merge = new CellRangeAddress(rows + dt.Rows.Count, rows + dt.Rows.Count, 0, ColMergeNum - 1);
  156. _sheet.AddMergedRegion(region_Merge);
  157. }
  158. ICellStyle myBodyStyle = bodyStyle;
  159. ICellStyle myTitleStyle = titleStyle;
  160. ICellStyle myDateStyle = dateStyle;
  161. ICellStyle myBodyRightStyle = bodyRightStyle;
  162. // 循环行数
  163. foreach (DataRow row in dt.Rows)
  164. {
  165. #region 新建表,填充表头,填充列头,样式
  166. if (rowIndex == 65535 || rowIndex == 0)
  167. {
  168. if (rowIndex != 0)
  169. _sheet = (HSSFSheet)_workbook.CreateSheet();
  170. // 构建行
  171. for (int i = 0; i < rows + model.isTitle; i++)
  172. {
  173. _row = _sheet.GetRow(i);
  174. // 创建行
  175. if (_row == null)
  176. _row = _sheet.CreateRow(i);
  177. for (int j = 0; j < cols; j++)
  178. _row.CreateCell(j).CellStyle = myBodyStyle;
  179. }
  180. // 如果存在表标题
  181. if (model.isTitle > 0)
  182. {
  183. // 获取行
  184. _row = _sheet.GetRow(0);
  185. // 合并单元格
  186. CellRangeAddress region = new CellRangeAddress(0, 0, 0, (cols - 1));
  187. _sheet.AddMergedRegion(region);
  188. // 填充值
  189. _row.CreateCell(0).SetCellValue(model.tableTitle);
  190. // 设置样式
  191. _row.GetCell(0).CellStyle = myTitleStyle;
  192. // 设置行高
  193. _row.HeightInPoints = 20;
  194. }
  195. // 取得上一个实体
  196. NPOIHeader lastRow = null;
  197. IList<NPOIHeader> hList = GetHeaders(model.headerName, rows, model.isTitle);
  198. // 创建表头
  199. foreach (NPOIHeader m in hList)
  200. {
  201. var data = hList.Where(c => c.firstRow == m.firstRow && c.lastCol == m.firstCol - 1);
  202. if (data.Count() > 0)
  203. {
  204. lastRow = data.First();
  205. if (m.headerName == lastRow.headerName)
  206. m.firstCol = lastRow.firstCol;
  207. }
  208. // 获取行
  209. _row = _sheet.GetRow(m.firstRow);
  210. // 合并单元格
  211. CellRangeAddress region = new CellRangeAddress(m.firstRow, m.lastRow, m.firstCol, m.lastCol);
  212. _sheet.AddMergedRegion(region);
  213. // 填充值
  214. _row.CreateCell(m.firstCol).SetCellValue(m.headerName);
  215. }
  216. // 填充表头样式
  217. for (int i = 0; i < rows + model.isTitle; i++)
  218. {
  219. _row = _sheet.GetRow(i);
  220. for (int j = 0; j < cols; j++)
  221. {
  222. _row.GetCell(j).CellStyle = myBodyStyle;
  223. //设置列宽
  224. _sheet.SetColumnWidth(j, (model.colWidths[j] + 1) * 450);
  225. }
  226. }
  227. rowIndex = (rows + model.isTitle);
  228. }
  229. #endregion
  230. #region 填充内容
  231. // 构建列
  232. _row = _sheet.CreateRow(rowIndex);
  233. foreach (DataColumn column in dt.Columns)
  234. {
  235. // 添加序号列
  236. if (1 == model.isOrderby && column.Ordinal == 0)
  237. {
  238. _cell = _row.CreateCell(0);
  239. _cell.SetCellValue(rowIndex - rows);
  240. _cell.CellStyle = myBodyStyle;
  241. }
  242. // 创建列
  243. _cell = _row.CreateCell(column.Ordinal + model.isOrderby);
  244. // 获取值
  245. drValue = row[column].ToString();
  246. switch (column.DataType.ToString())
  247. {
  248. case "System.String"://字符串类型
  249. _cell.SetCellValue(drValue);
  250. _cell.CellStyle = myBodyStyle;
  251. break;
  252. case "System.DateTime"://日期类型
  253. DateTime dateV;
  254. DateTime.TryParse(drValue, out dateV);
  255. _cell.SetCellValue(dateV);
  256. _cell.CellStyle = myDateStyle;//格式化显示
  257. break;
  258. case "System.Boolean"://布尔型
  259. bool boolV = false;
  260. bool.TryParse(drValue, out boolV);
  261. _cell.SetCellValue(boolV);
  262. _cell.CellStyle = myBodyStyle;
  263. break;
  264. case "System.Int16"://整型
  265. case "System.Int32":
  266. case "System.Int64":
  267. case "System.Byte":
  268. int intV = 0;
  269. int.TryParse(drValue, out intV);
  270. _cell.SetCellValue(intV);
  271. _cell.CellStyle = myBodyRightStyle;
  272. break;
  273. case "System.Decimal"://浮点型
  274. case "System.Double":
  275. double doubV = 0;
  276. double.TryParse(drValue, out doubV);
  277. _cell.SetCellValue(doubV.ToString("f2"));
  278. _cell.CellStyle = myBodyRightStyle;
  279. break;
  280. case "System.DBNull"://空值处理
  281. _cell.SetCellValue("");
  282. break;
  283. default:
  284. _cell.SetCellValue("");
  285. break;
  286. }
  287. }
  288. #endregion
  289. rowIndex++;
  290. }
  291. }
  292. }
  293. #region 辅助方法
  294. /// <summary>
  295. /// 表头解析
  296. /// </summary>
  297. /// <remarks>
  298. /// </remarks>
  299. /// <param name="header">表头</param>
  300. /// <param name="rows">总行数</param>
  301. /// <param name="addRows">外加行</param>
  302. /// <param name="addCols">外加列</param>
  303. /// <returns></returns>
  304. private static IList<NPOIHeader> GetHeaders(string header, int rows, int addRows)
  305. {
  306. // 临时表头数组
  307. string[] tempHeader;
  308. string[] tempHeader2;
  309. // 所跨列数
  310. int colSpan = 0;
  311. // 所跨行数
  312. int rowSpan = 0;
  313. // 单元格对象
  314. NPOIHeader model = null;
  315. // 行数计数器
  316. int rowIndex = 0;
  317. // 列数计数器
  318. int colIndex = 0;
  319. //
  320. IList<NPOIHeader> list = new List<NPOIHeader>();
  321. // 初步解析
  322. string[] headers = header.Split(new string[] { "#" }, StringSplitOptions.RemoveEmptyEntries);
  323. // 表头遍历
  324. for (int i = 0; i < headers.Length; i++)
  325. {
  326. // 行数计数器清零
  327. rowIndex = 0;
  328. // 列数计数器清零
  329. colIndex = 0;
  330. // 获取所跨行数
  331. rowSpan = GetRowSpan(headers[i], rows);
  332. // 获取所跨列数
  333. colSpan = GetColSpan(headers[i]);
  334. // 如果所跨行数与总行数相等,则不考虑是否合并单元格问题
  335. if (rows == rowSpan)
  336. {
  337. colIndex = GetMaxCol(list);
  338. model = new NPOIHeader(headers[i],
  339. addRows,
  340. (rowSpan - 1 + addRows),
  341. colIndex,
  342. (colSpan - 1 + colIndex),
  343. addRows);
  344. list.Add(model);
  345. rowIndex += (rowSpan - 1) + addRows;
  346. }
  347. else
  348. {
  349. // 列索引
  350. colIndex = GetMaxCol(list);
  351. // 如果所跨行数不相等,则考虑是否包含多行
  352. tempHeader = headers[i].Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries);
  353. for (int j = 0; j < tempHeader.Length; j++)
  354. {
  355. // 如果总行数=数组长度
  356. if (1 == GetColSpan(tempHeader[j]))
  357. {
  358. if (j == tempHeader.Length - 1 && tempHeader.Length < rows)
  359. {
  360. model = new NPOIHeader(tempHeader[j],
  361. (j + addRows),
  362. (j + addRows) + (rows - tempHeader.Length),
  363. colIndex,
  364. (colIndex + colSpan - 1),
  365. addRows);
  366. list.Add(model);
  367. }
  368. else
  369. {
  370. model = new NPOIHeader(tempHeader[j],
  371. (j + addRows),
  372. (j + addRows),
  373. colIndex,
  374. (colIndex + colSpan - 1),
  375. addRows);
  376. list.Add(model);
  377. }
  378. }
  379. else
  380. {
  381. // 如果所跨列数不相等,则考虑是否包含多列
  382. tempHeader2 = tempHeader[j].Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
  383. for (int m = 0; m < tempHeader2.Length; m++)
  384. {
  385. // 列索引
  386. colIndex = GetMaxCol(list) - colSpan + m;
  387. if (j == tempHeader.Length - 1 && tempHeader.Length < rows)
  388. {
  389. model = new NPOIHeader(tempHeader2[m],
  390. (j + addRows),
  391. (j + addRows) + (rows - tempHeader.Length),
  392. colIndex,
  393. colIndex,
  394. addRows);
  395. list.Add(model);
  396. }
  397. else
  398. {
  399. model = new NPOIHeader(tempHeader2[m],
  400. (j + addRows),
  401. (j + addRows),
  402. colIndex,
  403. colIndex,
  404. addRows);
  405. list.Add(model);
  406. }
  407. }
  408. }
  409. rowIndex += j + addRows;
  410. }
  411. }
  412. }
  413. return list;
  414. }
  415. /// <summary>
  416. /// 获取最大列
  417. /// </summary>
  418. /// <param name="list"></param>
  419. /// <returns></returns>
  420. private static int GetMaxCol(IList<NPOIHeader> list)
  421. {
  422. int maxCol = 0;
  423. if (list.Count > 0)
  424. {
  425. foreach (NPOIHeader model in list)
  426. {
  427. if (maxCol < model.lastCol)
  428. maxCol = model.lastCol;
  429. }
  430. maxCol += 1;
  431. }
  432. return maxCol;
  433. }
  434. /// <summary>
  435. /// 获取表头行数
  436. /// </summary>
  437. /// <param name="newHeaders">表头文字</param>
  438. /// <returns></returns>
  439. private static int GetRowCount(string newHeaders)
  440. {
  441. string[] ColumnNames = newHeaders.Split(new char[] { '@' });
  442. int Count = 0;
  443. if (ColumnNames.Length <= 1)
  444. ColumnNames = newHeaders.Split(new char[] { '#' });
  445. foreach (string name in ColumnNames)
  446. {
  447. int TempCount = name.Split(new char[] { ' ' }).Length;
  448. if (TempCount > Count)
  449. Count = TempCount;
  450. }
  451. return Count;
  452. }
  453. /// <summary>
  454. /// 获取表头列数
  455. /// </summary>
  456. /// <param name="newHeaders">表头文字</param>
  457. /// <returns></returns>
  458. private static int GetColCount(string newHeaders)
  459. {
  460. string[] ColumnNames = newHeaders.Split(new char[] { '@' });
  461. int Count = 0;
  462. if (ColumnNames.Length <= 1)
  463. ColumnNames = newHeaders.Split(new char[] { '#' });
  464. Count = ColumnNames.Length;
  465. foreach (string name in ColumnNames)
  466. {
  467. int TempCount = name.Split(new char[] { ',' }).Length;
  468. if (TempCount > 1)
  469. Count += TempCount - 1;
  470. }
  471. return Count;
  472. }
  473. /// <summary>
  474. /// 列头跨列数
  475. /// </summary>
  476. /// <remarks>
  477. /// </remarks>
  478. /// <param name="newHeaders">表头文字</param>
  479. /// <returns></returns>
  480. private static int GetColSpan(string newHeaders)
  481. {
  482. return newHeaders.Split(',').Count();
  483. }
  484. /// <summary>
  485. /// 列头跨行数
  486. /// </summary>
  487. /// <remarks>
  488. /// </remarks>
  489. /// <param name="newHeaders">列头文本</param>
  490. /// <param name="rows">表头总行数</param>
  491. /// <returns></returns>
  492. private static int GetRowSpan(string newHeaders, int rows)
  493. {
  494. int Count = newHeaders.Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries).Length;
  495. // 如果总行数与当前表头所拥有行数相等
  496. if (rows == Count)
  497. Count = 1;
  498. else if (Count < rows)
  499. Count = 1 + (rows - Count);
  500. else
  501. throw new Exception("表头格式不正确!");
  502. return Count;
  503. }
  504. #endregion
  505. #region 单元格样式
  506. /// <summary>
  507. /// 数据单元格样式
  508. /// </summary>
  509. private static ICellStyle bodyStyle
  510. {
  511. get
  512. {
  513. ICellStyle style = _workbook.CreateCellStyle();
  514. style.Alignment = HorizontalAlignment.CENTER; //居中
  515. style.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中
  516. style.WrapText = true;//自动换行
  517. // 边框
  518. style.BorderBottom = BorderStyle.THIN;
  519. style.BorderLeft = BorderStyle.THIN;
  520. style.BorderRight = BorderStyle.THIN;
  521. style.BorderTop = BorderStyle.THIN;
  522. // 字体
  523. //IFont font = _workbook.CreateFont();
  524. //font.FontHeightInPoints = 10;
  525. //font.FontName = "宋体";
  526. //style.SetFont(font);
  527. return style;
  528. }
  529. }
  530. /// <summary>
  531. /// 数据单元格样式
  532. /// </summary>
  533. private static ICellStyle bodyRightStyle
  534. {
  535. get
  536. {
  537. ICellStyle style = _workbook.CreateCellStyle();
  538. style.Alignment = HorizontalAlignment.RIGHT; //居中
  539. style.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中
  540. style.WrapText = true;//自动换行
  541. // 边框
  542. style.BorderBottom = BorderStyle.THIN;
  543. style.BorderLeft = BorderStyle.THIN;
  544. style.BorderRight = BorderStyle.THIN;
  545. style.BorderTop = BorderStyle.THIN;
  546. // 字体
  547. //IFont font = _workbook.CreateFont();
  548. //font.FontHeightInPoints = 10;
  549. //font.FontName = "宋体";
  550. //style.SetFont(font);
  551. return style;
  552. }
  553. }
  554. /// <summary>
  555. /// 标题单元格样式
  556. /// </summary>
  557. private static ICellStyle titleStyle
  558. {
  559. get
  560. {
  561. ICellStyle style = _workbook.CreateCellStyle();
  562. style.Alignment = HorizontalAlignment.CENTER; //居中
  563. style.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中
  564. style.WrapText = true;//自动换行
  565. //IFont font = _workbook.CreateFont();
  566. //font.FontHeightInPoints = 14;
  567. //font.FontName = "宋体";
  568. //font.Boldweight = (short)FontBoldWeight.BOLD;
  569. //style.SetFont(font);
  570. return style;
  571. }
  572. }
  573. /// <summary>
  574. /// 日期单元格样式
  575. /// </summary>
  576. private static ICellStyle dateStyle
  577. {
  578. get
  579. {
  580. ICellStyle style = _workbook.CreateCellStyle();
  581. style.Alignment = HorizontalAlignment.CENTER; //居中
  582. style.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中
  583. style.WrapText = true;//自动换行
  584. // 边框
  585. style.BorderBottom = BorderStyle.THIN;
  586. style.BorderLeft = BorderStyle.THIN;
  587. style.BorderRight = BorderStyle.THIN;
  588. style.BorderTop = BorderStyle.THIN;
  589. // 字体
  590. //IFont font = _workbook.CreateFont();
  591. //font.FontHeightInPoints = 10;
  592. //font.FontName = "宋体";
  593. //style.SetFont(font);
  594. IDataFormat format = _workbook.CreateDataFormat();
  595. style.DataFormat = format.GetFormat("yyyy-MM-dd");
  596. return style;
  597. }
  598. }
  599. #endregion
  600. #endregion
  601. }
  602. /// <summary>
  603. /// 实体类
  604. /// </summary>
  605. public class NPOIModel
  606. {
  607. /// <summary>
  608. /// 数据源
  609. /// </summary>
  610. public DataTable dataSource { get; private set; }
  611. /// <summary>
  612. /// 要导出的数据列数组
  613. /// </summary>
  614. public string[] fileds { get; private set; }
  615. /// <summary>
  616. /// 工作薄名称数组
  617. /// </summary>
  618. public string sheetName { get; private set; }
  619. /// <summary>
  620. /// 表标题
  621. /// </summary>
  622. public string tableTitle { get; private set; }
  623. /// <summary>
  624. /// 表标题是否存在 1:存在 0:不存在
  625. /// </summary>
  626. public int isTitle { get; private set; }
  627. /// <summary>
  628. /// 是否添加序号
  629. /// </summary>
  630. public int isOrderby { get; private set; }
  631. /// <summary>
  632. /// 表头
  633. /// </summary>
  634. public string headerName { get; private set; }
  635. /// <summary>
  636. /// 取得列宽
  637. /// </summary>
  638. public int[] colWidths { get; private set; }
  639. /// <summary>
  640. /// 构造函数
  641. /// </summary>
  642. /// <remarks>
  643. /// </remarks>
  644. /// <param name="dataSource">数据来源 DataTable</param>
  645. /// <param name="filed">要导出的字段,如果为空或NULL,则默认全部</param>
  646. /// <param name="sheetName">工作薄名称</param>
  647. /// <param name="headerName">表头名称 如果为空或NULL,则默认数据列字段
  648. /// 相邻父列头之间用'#'分隔,父列头与子列头用空格(' ')分隔,相邻子列头用逗号分隔(',')
  649. /// 两行:序号#分公司#组别#本日成功签约单数 预警,续约,流失,合计#累计成功签约单数 预警,续约,流失,合计#任务数#完成比例#排名
  650. /// 三行:等级#级别#上期结存 件数,重量,比例#本期调入 收购调入 件数,重量,比例#本期发出 车间投料 件数,重量,比例#本期发出 产品外销百分比 件数,重量,比例#平均值
  651. /// 三行时请注意:列头要重复
  652. /// </param>
  653. /// <param name="tableTitle">表标题</param>
  654. /// <param name="isOrderby">是否添加序号 0:不添加 1:添加</param>
  655. public NPOIModel(DataTable dataSource, string filed, string sheetName, string headerName, string tableTitle = null, int isOrderby = 0)
  656. {
  657. if (!string.IsNullOrEmpty(filed))
  658. {
  659. this.fileds = filed.ToUpper().Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries);
  660. // 移除多余数据列
  661. for (int i = dataSource.Columns.Count - 1; i >= 0; i--)
  662. {
  663. DataColumn dc = dataSource.Columns[i];
  664. if (!this.fileds.Contains(dataSource.Columns[i].Caption.ToUpper()))
  665. {
  666. dataSource.Columns.Remove(dataSource.Columns[i]);
  667. }
  668. }
  669. // 列索引
  670. int colIndex = 0;
  671. // 循环排序
  672. for (int i = 0; i < dataSource.Columns.Count; i++)
  673. {
  674. // 获取索引
  675. colIndex = GetColIndex(dataSource.Columns[i].Caption.ToUpper());
  676. // 设置下标
  677. dataSource.Columns[i].SetOrdinal(colIndex);
  678. }
  679. }
  680. else
  681. {
  682. this.fileds = new string[dataSource.Columns.Count];
  683. for (int i = 0; i < dataSource.Columns.Count; i++)
  684. {
  685. this.fileds[i] = dataSource.Columns[i].ColumnName;
  686. }
  687. }
  688. this.dataSource = dataSource;
  689. if (!string.IsNullOrEmpty(sheetName))
  690. {
  691. this.sheetName = sheetName;
  692. }
  693. if (!string.IsNullOrEmpty(headerName))
  694. {
  695. this.headerName = headerName;
  696. }
  697. else
  698. {
  699. this.headerName = string.Join("#", this.fileds);
  700. }
  701. if (!string.IsNullOrEmpty(tableTitle))
  702. {
  703. this.tableTitle = tableTitle;
  704. this.isTitle = 1;
  705. }
  706. // 取得数据列宽 数据列宽可以和表头列宽比较,采取最长宽度
  707. colWidths = new int[this.dataSource.Columns.Count];
  708. foreach (DataColumn item in this.dataSource.Columns)
  709. {
  710. colWidths[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
  711. }
  712. // 循环比较最大宽度
  713. for (int i = 0; i < this.dataSource.Rows.Count; i++)
  714. {
  715. for (int j = 0; j < this.dataSource.Columns.Count; j++)
  716. {
  717. int intTemp = Encoding.GetEncoding(936).GetBytes(this.dataSource.Rows[i][j].ToString()).Length;
  718. if (intTemp > colWidths[j])
  719. {
  720. colWidths[j] = intTemp;
  721. }
  722. }
  723. }
  724. if (isOrderby > 0)
  725. {
  726. this.isOrderby = isOrderby;
  727. this.headerName = "序号#" + this.headerName;
  728. }
  729. }
  730. /// <summary>
  731. /// 获取列名下标
  732. /// </summary>
  733. /// <param name="colName">列名称</param>
  734. /// <returns></returns>
  735. private int GetColIndex(string colName)
  736. {
  737. for (int i = 0; i < this.fileds.Length; i++)
  738. {
  739. if (colName == this.fileds[i])
  740. return i;
  741. }
  742. return 0;
  743. }
  744. }
  745. /// <summary>
  746. /// 表头构建类
  747. /// </summary>
  748. public class NPOIHeader
  749. {
  750. /// <summary>
  751. /// 表头
  752. /// </summary>
  753. public string headerName { get; set; }
  754. /// <summary>
  755. /// 起始行
  756. /// </summary>
  757. public int firstRow { get; set; }
  758. /// <summary>
  759. /// 结束行
  760. /// </summary>
  761. public int lastRow { get; set; }
  762. /// <summary>
  763. /// 起始列
  764. /// </summary>
  765. public int firstCol { get; set; }
  766. /// <summary>
  767. /// 结束列
  768. /// </summary>
  769. public int lastCol { get; set; }
  770. /// <summary>
  771. /// 是否跨行
  772. /// </summary>
  773. public int isRowSpan { get; private set; }
  774. /// <summary>
  775. /// 是否跨列
  776. /// </summary>
  777. public int isColSpan { get; private set; }
  778. /// <summary>
  779. /// 外加行
  780. /// </summary>
  781. public int rows { get; set; }
  782. public NPOIHeader() { }
  783. /// <summary>
  784. /// 构造函数
  785. /// </summary>
  786. /// <param name="headerName">表头</param>
  787. /// <param name="firstRow">起始行</param>
  788. /// <param name="lastRow">结束行</param>
  789. /// <param name="firstCol">起始列</param>
  790. /// <param name="lastCol">结束列</param>
  791. /// <param name="rows">外加行</param>
  792. /// <param name="cols">外加列</param>
  793. public NPOIHeader(string headerName, int firstRow, int lastRow, int firstCol, int lastCol, int rows = 0)
  794. {
  795. this.headerName = headerName;
  796. this.firstRow = firstRow;
  797. this.lastRow = lastRow;
  798. this.firstCol = firstCol;
  799. this.lastCol = lastCol;
  800. // 是否跨行判断
  801. if (firstRow != lastRow)
  802. isRowSpan = 1;
  803. if (firstCol != lastCol)
  804. isColSpan = 1;
  805. this.rows = rows;
  806. }
  807. }
  808. }
复制代码

3、导出代码示例如下:

  1. /// <summary>
  2. /// 导出测点列表表格
  3. /// </summary>
  4. [HttpGet]
  5. [AllowAnonymous]
  6. public void ExportMeasurePointData(string TreeID, string TreeType)
  7. {
  8. DataTable dtResult = new DataTable();
  9. DataTable dtExcel = new DataTable();
  10. try
  11. {
  12. string sql = string.Format("EXEC P_GET_ZXJG_TagList '{0}','小贝'", TreeID, TreeType);
  13. dtResult = QuerySQL.GetDataTable(sql);
  14. dtExcel = dtResult.Copy();
  15. dtExcel.Columns.Add("xuhao", typeof(string));
  16. dtExcel.Columns.Add("StrValueTime", typeof(string));
  17. dtExcel.Columns["xuhao"].SetOrdinal(0);
  18. dtExcel.Columns["StrValueTime"].SetOrdinal(2);
  19. for (int i = 0; i < dtResult.Rows.Count; i++)
  20. {
  21. dtExcel.Rows[i]["xuhao"] = (i + 1).ToString();
  22. dtExcel.Rows[i]["StrValueTime"] = Convert.ToDateTime(dtResult.Rows[i]["F_ValueTime"]).ToString("yyyy-MM-dd HH:mm:ss");
  23. }
  24. List<NPOIModel> list = new List<NPOIModel>();
  25. list.Add(new NPOIModel(dtExcel, "xuhao;F_Description;StrValueTime;F_Value;F_Unit;F_AlmLow;F_AlmUp", "sheet", "序号#监测点#采集时间#当前数值#工程单位#报警下限#报警上限"));
  26. ATNPOIHelper.Export("测点列表", list, 0);
  27. }
  28. catch (Exception ex)
  29. {
  30. }
  31. }
复制代码

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持程序员之家。



回复

使用道具 举报