ExcelHelper:
    1. using NPOI.HSSF.UserModel;
    2. using NPOI.SS.UserModel;
    3. using System;
    4. using System.Collections.Generic;
    5. using System.Data;
    6. using System.IO;
    7. using System.Linq;
    8. using System.Text;
    9. using System.Threading.Tasks;
    10. namespace CMS.Common
    11. {
    12. public class ExcelHelper
    13. {
    14. /// <summary>
    15. /// 将Excel表导入DataTable
    16. /// </summary>
    17. /// <param name="filePath">目标Excel文件的物理路径(xxx.xls)</param>
    18. /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
    19. /// <returns></returns>
    20. public static DataTable ImportExcelFile(string filePath, bool isFirstRowColumn)
    21. {
    22. HSSFWorkbook hssfworkbook;
    23. try
    24. {
    25. using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
    26. {
    27. hssfworkbook = new HSSFWorkbook(file);
    28. }
    29. }
    30. catch (Exception e)
    31. {
    32. throw e;
    33. }
    34. NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
    35. System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
    36. DataTable dt = new DataTable();
    37. #region 添加列
    38. if (isFirstRowColumn)
    39. {
    40. for (int j = sheet.GetRow(0).FirstCellNum; j < (sheet.GetRow(0).LastCellNum); ++j)
    41. {
    42. ICell cell = sheet.GetRow(0).GetCell(j);
    43. if (cell != null)
    44. {
    45. string cellValue = cell.StringCellValue;//将列的值设为列名称
    46. if (cellValue != null)
    47. {
    48. DataColumn column = new DataColumn(cellValue);
    49. dt.Columns.Add(column);
    50. }
    51. }
    52. }
    53. rows.MoveNext();//如果Excel表的第一行是表名称,则往下推进一行(数据的遍历从第二行开始)
    54. }
    55. #endregion
    56. while (rows.MoveNext())
    57. {
    58. HSSFRow row = (HSSFRow)rows.Current;
    59. DataRow dr = dt.NewRow();
    60. for (int i = 0; i < row.LastCellNum; i++)
    61. {
    62. NPOI.SS.UserModel.ICell cell = row.GetCell(i);
    63. if (cell == null)
    64. {
    65. dr[i] = null;
    66. }
    67. else
    68. {
    69. if (cell.CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(cell)) //如果单元格是日期格式
    70. {
    71. dr[i] = cell.DateCellValue.ToString("yyyy-MM-dd");
    72. }
    73. else
    74. {
    75. dr[i] = cell.ToString();
    76. }
    77. }
    78. }
    79. dt.Rows.Add(dr);
    80. }
    81. RemoveEmpty(dt);//删除DataTable中的空白行
    82. return dt;
    83. }
    84. /// <summary>
    85. /// 将DataTable导出到Excel表:
    86. /// </summary>
    87. /// <param name="dt">数据源</param>
    88. /// <param name="filePath">写入到目标Excel文件的路径(xxx.xls)</param>
    89. public static void DataTableToExcel(DataTable dt, string filePath)
    90. {
    91. if (dt.TableName == null || dt.TableName == "")
    92. {
    93. dt.TableName = DateTime.Now.ToString();
    94. }
    95. if (!string.IsNullOrEmpty(filePath) && null != dt && dt.Rows.Count > 0)
    96. {
    97. NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
    98. NPOI.SS.UserModel.ISheet sheet = book.CreateSheet();
    99. NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
    100. for (int i = 0; i < dt.Columns.Count; i++)
    101. {
    102. row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
    103. }
    104. for (int i = 0; i < dt.Rows.Count; i++)
    105. {
    106. NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
    107. for (int j = 0; j < dt.Columns.Count; j++)
    108. {
    109. row2.CreateCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j]));
    110. }
    111. }
    112. sheet.CreateFreezePane(0, 1, 0, 1);//冻结第一行(第一行不能拖动)
    113. // 写入到客户端
    114. using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
    115. {
    116. book.Write(ms);
    117. using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
    118. {
    119. byte[] data = ms.ToArray();
    120. fs.Write(data, 0, data.Length);
    121. fs.Flush();
    122. }
    123. book = null;
    124. }
    125. }
    126. }
    127. /// <summary>
    128. /// 删除DataTable中的空白行
    129. /// </summary>
    130. /// <param name="dt"></param>
    131. protected static void RemoveEmpty(DataTable dt)
    132. {
    133. List<DataRow> removelist = new List<DataRow>();
    134. for (int i = 0; i < dt.Rows.Count; i++)
    135. {
    136. bool IsNull = true;
    137. for (int j = 0; j < dt.Columns.Count; j++)
    138. {
    139. if (!string.IsNullOrEmpty(dt.Rows[i][j].ToString().Trim()))
    140. {
    141. IsNull = false;
    142. }
    143. }
    144. if (IsNull)
    145. {
    146. removelist.Add(dt.Rows[i]);
    147. }
    148. }
    149. for (int i = 0; i < removelist.Count; i++)
    150. {
    151. dt.Rows.Remove(removelist[i]);
    152. }
    153. }
    154. }
    155. }
    文件下载:
    1. public class HomeController : Controller
    2. {
    3. public ActionResult Index()
    4. {
    5. //SqlHelper.SqlBulkCopy();
    6. DataTable table = SqlHelper.ExecuteDataTable("select * from Table_1", null);
    7. var filePath = Server.MapPath("Excel/abc.xls");//设定要文件要保存的路径
    8. ExcelHelper.WriteExcel(table,filePath); //将DataTable表的数据写入到Excel文件中
    9. FileInfo info = new FileInfo(filePath); //读取刚刚保存的Excel文件
    10. long fileSize = info.Length;
    11. Response.Clear();
    12. Response.ContentType = "application/octet-stream";
    13. Response.AddHeader("Content-Disposition", "attachement;filename=" + "abc.xls");
    14. //指定文件大小
    15. Response.AddHeader("Content-Length", fileSize.ToString());
    16. Response.WriteFile(filePath, 0, fileSize); //将文件响应到浏览器
    17. Response.Flush();
    18. Response.Close();
    19. return View();
    20. }
    21. }