ImportHelper 导出帮助类

  public class ImportExportHelper
    {
        /// <summary>
        /// Excel to DataTable
        /// </summary>
        /// <param name="content">内容流</param>
        /// <param name="fileName">文件名</param>
        /// <param name="startRow">表头开始行（确定列和数据开始）</param>
        /// <param name="title">表头第一列（确定列和数据开始）</param>
        /// <returns></returns>
        public static DataTable ExcelToDataTable(Stream content, string fileName, int startRow = 0, string title = "")
        {
            DataTable dataTable = null;
            DataColumn column = null;
            DataRow dataRow = null;
            IWorkbook workbook = null;
            ISheet sheet = null;
            IRow row = null;
            ICell cell = null;

            try
            {
                // 2007版本  
                if (fileName.IndexOf(".xlsx") > 0)
                    workbook = new XSSFWorkbook(content);
                // 2003版本  
                else if (fileName.IndexOf(".xls") > 0)
                    workbook = new HSSFWorkbook(content);
                else
                    return null;
                if (workbook != null)
                {
                    sheet = workbook.GetSheetAt(0);//读取第一个sheet，当然也可以循环读取每个sheet  
                    dataTable = new DataTable();
                    if (sheet != null)
                    {
                        int rowCount = sheet.LastRowNum;//总行数  

                        if (startRow < 1 && !string.IsNullOrWhiteSpace(title))
                        {
                            //找到数据行title所在的行索引
                            for (int i = startRow + 1; i <= rowCount; ++i)
                            {
                                row = sheet.GetRow(i);
                                if (row != null)
                                {
                                    cell = row.GetCell(0);
                                    if (cell != null)
                                    {
                                        if (cell.StringCellValue == title)
                                        {
                                            startRow = i;
                                            break;
                                        }
                                    }
                                }
                            }
                        }
                        if (startRow == 0)
                        {
                            return null;
                        }
                        if (rowCount > 0)
                        {
                            IRow firstRow = sheet.GetRow(startRow);//数据表的第一行  
                            int cellCount = firstRow.LastCellNum;//列数  

                            //构建datatable的列  
                            for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                            {
                                cell = firstRow.GetCell(i);
                                if (cell != null)
                                {
                                    if (cell.StringCellValue != null)
                                    {
                                        column = new DataColumn(cell.StringCellValue);
                                        dataTable.Columns.Add(column);
                                    }
                                }
                            }


                            //填充行  
                            for (int i = startRow + 1; i <= rowCount; ++i)
                            {
                                row = sheet.GetRow(i);
                                if (row == null) continue;

                                dataRow = dataTable.NewRow();
                                for (int j = row.FirstCellNum; j < cellCount; ++j)
                                {
                                    cell = row.GetCell(j);
                                    if (cell == null)
                                    {
                                        dataRow[j] = "";
                                    }
                                    else
                                    {
                                        switch (cell.CellType)
                                        {
                                            case CellType.Blank:
                                                dataRow[j] = "";
                                                break;
                                            case CellType.Numeric:
                                                short format = cell.CellStyle.DataFormat;
                                                //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理  
                                                if (format == 14 || format == 31 || format == 57 || format == 58)
                                                    dataRow[j] = cell.DateCellValue;
                                                else
                                                    dataRow[j] = cell.NumericCellValue;
                                                break;
                                            case CellType.String:
                                                dataRow[j] = cell.StringCellValue;
                                                break;
                                        }
                                    }
                                }
                                List<object> listCol = dataRow.ItemArray.ToList();
                                if (listCol.Any(p => Convert.ToString(p).Trim() != ""))
                                {
                                    dataTable.Rows.Add(dataRow);
                                }
                            }
                        }
                    }
                }
                return dataTable;
            }

            catch (Exception ex)
            {
                return null;
            }
        }

        /// <summary>
        /// DataTable To Excel
        /// </summary>
        /// <param name="data"></param>
        /// <param name="sheetName"></param>
        /// <param name="isColumnWritten"></param>
        /// <returns></returns>
        public static Stream DataTableToExcel(DataTable data, string sheetName = "Sheet1", bool isColumnWritten = true)
        {
            int i = 0;
            int j = 0;
            int count = 0;
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = null;

            try
            {
                if (workbook != null)
                {
                    sheet = workbook.CreateSheet(sheetName);
                }
                if (isColumnWritten) //写入DataTable的列名  
                {
                    IRow row = sheet.CreateRow(0);
                    for (j = 0; j < data.Columns.Count; ++j)
                    {
                        row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
                    }
                    count = 1;
                }
                else
                {
                    count = 0;
                }

                for (i = 0; i < data.Rows.Count; ++i)
                {
                    IRow row = sheet.CreateRow(count);
                    for (j = 0; j < data.Columns.Count; ++j)
                    {
                        row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
                    }
                    ++count;
                }

                var ms = new NPOIMemoryStream();
                ms.AllowClose = false;
                workbook.Write(ms); //写入到stream
                ms.Flush();
                ms.Seek(0, SeekOrigin.Begin);
                ms.AllowClose = true;
                return ms;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        /// <summary>
        /// 保存DataTable为Execl文件到硬盘
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="directory"></param>
        /// <returns></returns>
        public static string SaveExcelFile(DataTable dt, string directory = null)
        {
            string excelName = DateTime.Now.ToString("yyyyMMddHHmmssfff") + "_" + Guid.NewGuid() + ".xlsx";
            string path = DateTime.Now.Year + "/" + DateTime.Now.Month + "/";

            if (string.IsNullOrEmpty(directory))
            {
                path = $"~/DownloadFile/{path}";
            }
            else
            {
                path = $"{directory}/{path}";
            }

            if (!System.IO.Directory.Exists(path))
            {
                System.IO.Directory.CreateDirectory(path);
            }

            path += excelName;

            var file = DataTableToExcel(dt);

            using (FileStream fs = new FileStream(path, FileMode.Create))//System.IO.File.Create(filefullPath)
            {
                file.CopyTo(fs);
                fs.Flush();
            }

            return path;
        }
    }

    public class NPOIMemoryStream : MemoryStream
    {
        public NPOIMemoryStream()
        {
            AllowClose = true;
        }
        public bool AllowClose { get; set; }

        public override void Close()
        {
            if (AllowClose)
                base.Close();
        }
    }