C#中使用NPOI进行DataTable和Excel互转

        /// <summary>
        /// 读取excel文件数据到DataTable
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="deleteFile"></param>
        /// <returns></returns>
        public static DataTable ReadExcelToTable(string filePath, bool deleteFile = false)
        {
            using (var tempFile = new FileStream(filePath, FileMode.Open))
            {
                var workbook = new HSSFWorkbook(tempFile);
                var sheet = workbook.GetSheetAt(0);
                var dataTable = new DataTable();
                var tableHeadRow = sheet.GetRow(0);
                for (int i = 0; i < tableHeadRow.PhysicalNumberOfCells; i++)
                {
                    var headCell = tableHeadRow.Cells[i];
                    dataTable.Columns.Add(new DataColumn(headCell.StringCellValue));
                }
                for (int i = 1; i < sheet.PhysicalNumberOfRows; i++)
                {
                    var row = sheet.GetRow(i);
                    var newRow = dataTable.NewRow();
                    for (int j = 0; j < row.PhysicalNumberOfCells; j++)
                    {
                        var cell = row.Cells[j];
                        newRow[j] = cell.StringCellValue;
                    }
                    dataTable.Rows.Add(newRow);
                }
                workbook.Clear();
                workbook.Close();
                if (deleteFile)
                {
                    File.Delete(filePath);
                }
                return dataTable;
            }
        }
      
        /// <summary>
        /// 将datatable转化为Excel
        /// </summary>
        /// <param name="dataTable"></param>
        /// <returns></returns>
        public static string DataTableToExcel(DataTable dataTable)
        {
            var dt = dataTable;
            var filePath = HttpContext.Current.Server.MapPath("/ExportTemplete/" + Guid.NewGuid() + ".xls");
            using (var fileStream = new FileStream(filePath, FileMode.OpenOrCreate))
            {
                var workBook = new HSSFWorkbook();
                var sheet = workBook.CreateSheet();

                IRow headRow = sheet.CreateRow(0);
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    ICell cell = headRow.CreateCell(i);
                    cell.SetCellValue(dt.Columns[i] == null ? "" : dt.Columns[i].ToString());
                }
                if (dt != null && dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        IRow newRow = sheet.CreateRow(i + 1);
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            ICell cell = newRow.CreateCell(j);
                            cell.SetCellValue(dt.Rows[i][j] == null ? "" : dt.Rows[i][j].ToString());
                        }
                    }
                }
                workBook.Write(fileStream);
                workBook.Clear();
                workBook.Close();
            }
            return filePath;
        }

 

 /// <summary>        /// 读取excel文件数据到DataTable        /// </summary>        /// <param name="filePath"></param>        /// <param name="deleteFile"></param>        /// <returns></returns>        public static DataTable ReadExcelToTable(string filePath, bool deleteFile = false)        {            using (var tempFile = new FileStream(filePath, FileMode.Open))            {                var workbook = new HSSFWorkbook(tempFile);                var sheet = workbook.GetSheetAt(0);                var dataTable = new DataTable();                var tableHeadRow = sheet.GetRow(0);                for (int i = 0; i < tableHeadRow.PhysicalNumberOfCells; i++)                {                    var headCell = tableHeadRow.Cells[i];                    dataTable.Columns.Add(new DataColumn(headCell.StringCellValue));                }                for (int i = 1; i < sheet.PhysicalNumberOfRows; i++)                {                    var row = sheet.GetRow(i);                    var newRow = dataTable.NewRow();                    for (int j = 0; j < row.PhysicalNumberOfCells; j++)                    {                        var cell = row.Cells[j];                        newRow[j] = cell.StringCellValue;                    }                    dataTable.Rows.Add(newRow);                }                workbook.Clear();                workbook.Close();                if (deleteFile)                {                    File.Delete(filePath);                }                return dataTable;            }        }              /// <summary>        /// 将datatable转化为Excel        /// </summary>        /// <param name="dataTable"></param>        /// <returns></returns>        public static string DataTableToExcel(DataTable dataTable)        {            var dt = dataTable;            var filePath = HttpContext.Current.Server.MapPath("/ExportTemplete/" + Guid.NewGuid() + ".xls");            using (var fileStream = new FileStream(filePath, FileMode.OpenOrCreate))            {                var workBook = new HSSFWorkbook();                var sheet = workBook.CreateSheet();
                IRow headRow = sheet.CreateRow(0);                for (int i = 0; i < dt.Columns.Count; i++)                {                    ICell cell = headRow.CreateCell(i);                    cell.SetCellValue(dt.Columns[i] == null ? "" : dt.Columns[i].ToString());                }                if (dt != null && dt.Rows.Count > 0)                {                    for (int i = 0; i < dt.Rows.Count; i++)                    {                        IRow newRow = sheet.CreateRow(i + 1);                        for (int j = 0; j < dt.Columns.Count; j++)                        {                            ICell cell = newRow.CreateCell(j);                            cell.SetCellValue(dt.Rows[i][j] == null ? "" : dt.Rows[i][j].ToString());                        }                    }                }                workBook.Write(fileStream);                workBook.Clear();                workBook.Close();            }            return filePath;        }