ExcelHandler.cs 5.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125
  1. using System.IO;
  2. using System.Data;
  3. using System;
  4. using NPOI.HSSF.UserModel;
  5. using NPOI.SS.UserModel;
  6. using NPOI.HSSF.Util;
  7. using UAS_LabelMachine.Entity;
  8. namespace UAS_LabelMachine
  9. {
  10. class ExcelHandler
  11. {
  12. DataHelper dh = SystemInf.dh;
  13. /// <summary>
  14. /// 导出Excel,返回文件在客户端的路径
  15. /// </summary>
  16. public string ExportExcel(DataTable dt, string FolderPath, string FileName)
  17. {
  18. //创建一个内存流,用来接收转换成Excel的内容
  19. MemoryStream ms;
  20. ms = DataTableToExcel(dt);
  21. //以系统当前时间命名文件,FileMode.Create表示创建文件,FileAccess.Write表示拥有写的权限
  22. string filePath = @FolderPath + "\\" + FileName + ".xls";
  23. FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write);
  24. byte[] data = ms.ToArray();
  25. fs.Write(data, 0, data.Length);
  26. fs.Flush();
  27. //释放当前Excel文件,否则打开文件的时候会显示文件被占用
  28. ms.Dispose();
  29. fs.Dispose();
  30. return filePath;
  31. }
  32. /// <summary>
  33. /// 导入Excel
  34. /// </summary>
  35. public void ImportExcel(DataTable DataTable, string TableName)
  36. {
  37. int columnNum = DataTable.Columns.Count;
  38. int rowNum = DataTable.Columns.Count;
  39. string[] field = new string[columnNum];
  40. for (int i = 0; i < columnNum; i++)
  41. {
  42. field[i] = DataTable.Rows[0][i].ToString();
  43. }
  44. }
  45. /// <summary>
  46. /// 将DataTable形式的数据转成Excel格式的,然后用字节流的形式写入文件
  47. /// </summary>
  48. /// <param name="DataTable"></param>
  49. /// <returns></returns>
  50. public MemoryStream DataTableToExcel(DataTable DataTable)
  51. {
  52. //创建内存流
  53. MemoryStream ms = new MemoryStream();
  54. //创建一个Book,相当于一个Excel文件
  55. HSSFWorkbook book = new HSSFWorkbook();
  56. //Excel中的Sheet
  57. ISheet sheet = book.CreateSheet("sheet1");
  58. //获取行数量和列数量
  59. int rowNum = DataTable.Rows.Count;
  60. int columnNum = DataTable.Columns.Count;
  61. //设置列的宽度,根据首行的列的内容的长度来设置
  62. for (int i = 0; i < columnNum; i++)
  63. {
  64. int dataLength;
  65. //如果内容比标题短则取标题长度
  66. if (DataTable.Rows[0][i].ToString().Length < DataTable.Columns[i].ColumnName.Length)
  67. {
  68. dataLength = DataTable.Columns[i].ColumnName.Length;
  69. dataLength = dataLength * 600;
  70. }
  71. else
  72. {
  73. dataLength = DataTable.Rows[0][i].ToString().Length;
  74. dataLength = dataLength * 500;
  75. }
  76. sheet.SetColumnWidth(i, dataLength);
  77. }
  78. //首先画好第一行带颜色的,单独写出来,避免写在循环里面
  79. IRow row = sheet.CreateRow(0);
  80. //冻结第一行
  81. sheet.CreateFreezePane(0, 1, 0, 1);
  82. ICellStyle style = book.CreateCellStyle();
  83. style.FillForegroundColor = HSSFColor.PALE_BLUE.index;
  84. style.FillPattern = FillPatternType.BIG_SPOTS;
  85. style.FillBackgroundColor = HSSFColor.LIGHT_GREEN.index;
  86. //设置边框
  87. style.BorderBottom = BorderStyle.THICK;
  88. style.BorderLeft = BorderStyle.THICK;
  89. style.BorderRight = BorderStyle.THICK;
  90. style.BorderTop = BorderStyle.THICK;
  91. row.HeightInPoints = 20;
  92. //固定第一行
  93. //row.RowStyle.IsLocked=true;
  94. //给第一行的标签赋值样式和值
  95. for (int j = 0; j < columnNum; j++)
  96. {
  97. row.CreateCell(j);
  98. row.Cells[j].CellStyle = style;
  99. row.Cells[j].CellStyle.VerticalAlignment = VerticalAlignment.CENTER;
  100. row.Cells[j].CellStyle.Alignment = HorizontalAlignment.CENTER;
  101. row.Cells[j].SetCellValue(DataTable.Columns[j].ColumnName);
  102. }
  103. //将DataTable的值循环赋值给book,Aligment设置居中
  104. //之前已经画了带颜色的第一行,所以从i=1开始画
  105. for (int i = 0; i < rowNum; i++)
  106. {
  107. IRow row1 = sheet.CreateRow(i + 1);
  108. row1.HeightInPoints = 20;
  109. for (int j = 0; j < columnNum; j++)
  110. {
  111. row1.CreateCell(j);
  112. row1.Cells[j].SetCellValue(DataTable.Rows[i][j].ToString());
  113. row1.GetCell(j).CellStyle.VerticalAlignment = VerticalAlignment.CENTER;
  114. }
  115. }
  116. //将book的内容写入内存流中返回
  117. book.Write(ms);
  118. return ms;
  119. }
  120. }
  121. }