ExcelHelper 帮助类-可以直接保存到共享文件夹

备注：使用这种生成文件的方式，可以直接把文件放在\\xxx\xxx\xx 这样的共享盘

FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write)

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

 

namespace VWNMSCNDSFA.Common

{

   using NPOI.HSSF.UserModel;

   using NPOI.SS.UserModel;

   using NPOI.XSSF.UserModel;

   using System.Data;

   using System.IO;

 

   public class ExcelHelper

    {

       /// <summary>

       /// Datable导出成Excel

       /// </summary>

       /// <param name="dt"></param>

       /// <param name="file">导出路径(包括文件名与扩展名)</param>

       public static void TableToExcel(DataTable dt, string file)

        {

            IWorkbook workbook;

           string fileExt = Path.GetExtension(file).ToLower();

           if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }

           if (workbook == null) { return; }

            ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);

 

           //表头 

            IRow row = sheet.CreateRow(0);

           for (int i = 0; i < dt.Columns.Count; i++)

            {

                ICell cell = row.CreateCell(i);

                cell.SetCellValue(dt.Columns[i].ColumnName);

            }

 

           //数据 

            for (int i = 0; i < dt.Rows.Count; i++)

            {

                IRow row1 = sheet.CreateRow(i + 1);

               for (int j = 0; j < dt.Columns.Count; j++)

                {

                    ICell cell = row1.CreateCell(j);

                    cell.SetCellValue(dt.Rows[i][j].ToString());

                }

            }

 

           //转为字节数组 

            MemoryStream stream = new MemoryStream();

            workbook.Write(stream);

           var buf = stream.ToArray();

 

            //保存为Excel文件 

            using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))

            {

                fs.Write(buf, 0, buf.Length);

                fs.Flush();

            }

        }

       /// <summary>

       /// Excel导入成Datable

       /// </summary>

       /// <param name="file">导入路径(包含文件名与扩展名)</param>

       /// <returns></returns>

       public static DataTable ExcelToTable(string file)

        {

            DataTable dt = new DataTable();

            IWorkbook workbook;

           string fileExt = Path.GetExtension(file).ToLower();

           using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))

            {

               //XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式

               if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(fs); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(fs); } else { workbook = null; }

               if (workbook == null) { return null; }

                ISheet sheet = workbook.GetSheetAt(0);

 

               //表头 

                IRow header = sheet.GetRow(sheet.FirstRowNum);

                List<int> columns = new List<int>();

               for (int i = 0; i < header.LastCellNum; i++)

                {

                   object obj = GetValueType(header.GetCell(i));

                   if (obj == null || obj.ToString() == string.Empty)

                    {

                        dt.Columns.Add(new DataColumn("Columns" + i.ToString()));

                    }

                   else

                        dt.Columns.Add(new DataColumn(obj.ToString()));

                    columns.Add(i);

                }

               //数据 

                for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)

                {

                    DataRow dr = dt.NewRow();

                   bool hasValue = false;

                   foreach (int j in columns)

                    {

                        dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));

                       if (dr[j] != null && dr[j].ToString() != string.Empty)

                        {

                            hasValue = true;

                        }

                    }

                   if (hasValue)

                    {

                        dt.Rows.Add(dr);

                    }

                }

            }

           return dt;

        }

       /// <summary>

       /// 将Excel转换为字典

       /// </summary>

       /// <param name="file"></param>

       /// <returns>string:sheet名称,datatable:sheet页数据</returns>

       public static Dictionary<string, DataTable> ExcelToTables(string file)

        {

            Dictionary<string, DataTable> pairs = new Dictionary<string, DataTable>();

            IWorkbook workbook;

            string fileExt = Path.GetExtension(file).ToLower();

           using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))

            {

               //XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式

               if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(fs); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(fs); } else { workbook = null; }

               if (workbook == null) { return null; }

 

               for (int m = 0; m < workbook.NumberOfSheets; m++)

                {

                   try

                    {

                        DataTable dt = new DataTable();

                        ISheet sheet = workbook.GetSheetAt(m);

                       string sheetName = sheet.SheetName.ToLower();

 

                       if (!sheetName.Contains("contractinfo") && !sheetName.Contains("paymentschedule") && !sheetName.Contains("incomeschedule") && !sheetName.Contains("estdsf") && !sheetName.Contains("actdsf") && !sheetName.Contains("1stchange") && !sheetName.Contains("2ndchange"))

                        {

                           continue;

                        }

 

                       //表头 

                        IRow header = sheet.GetRow(sheet.FirstRowNum);

                        List<int> columns = new List<int>();

                       for (int i = 0; i < header.LastCellNum; i++)

                        {

                           object obj = GetValueType(header.GetCell(i));

                           if (obj == null || obj.ToString() == string.Empty)

                            {

                                dt.Columns.Add(new DataColumn("Columns" + i.ToString()));

                            }

                           else

                                dt.Columns.Add(new DataColumn(obj.ToString()));

                            columns.Add(i);

                        }

                       //数据 

                        for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)

                        {

                            DataRow dr = dt.NewRow();

                           bool hasValue = false;

                           if (sheet.GetRow(i) != null)

                            {

                               foreach (int j in columns)

                                {

 

                                   if (sheet.GetRow(i).GetCell(j) != null)

                                    {

                                        dr[j] = GetValueType(sheet.GetRow(i).GetCell(j)); if (dr[j] != null && dr[j].ToString() != string.Empty)

                                        {

                                            hasValue = true;

                                        }

                                    }

 

                                }

                                if (hasValue)

                                {

                                    dt.Rows.Add(dr);

                                }

                            }

 

                        }

 

                        pairs.Add(sheetName, dt);

                    }

                   catch (Exception e)

                    {

                      

                    }

                }

            }

           return pairs;

        }

 

 

        /// <summary>

       /// 获取单元格类型

       /// </summary>

       /// <param name="cell"></param>

       /// <returns></returns>

       private static object GetValueType(ICell cell)

        {

           if (cell == null)

               return null;

           switch (cell.CellType)

            {

               case CellType.Blank: //BLANK: 

                    return null;

               case CellType.Boolean: //BOOLEAN:

                    return cell.BooleanCellValue;

               case CellType.Numeric: //NUMERIC:

                   if (DateUtil.IsValidExcelDate(cell.NumericCellValue) && DateUtil.IsCellDateFormatted(cell))

                    {

                       return cell.DateCellValue;

                    }

                   else

                    {

                       return cell.NumericCellValue;

                    }

               //return cell.NumericCellValue;

               case CellType.String: //STRING: 

                    return cell.StringCellValue;

                case CellType.Error: //ERROR:

                    return cell.ErrorCellValue;

               case CellType.Formula: //FORMULA:

                    cell.SetCellType(CellType.String);

                   return cell.StringCellValue;

                default:

                   return "=" + cell.CellFormula;

            }

        }

    }

}