NPOI组件操作Excel导入、导出

        /// <summary>
        /// 将xls格式的Excel读取到DataTable中
        /// </summary>
        /// <param name="fileName">文件路径</param>
        /// <returns></returns>
        public DataTable HSSFReadExcel(string fileName)
        {
            try
            {
                DataTable data = new DataTable();
                #region 读excel
                using (FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Read))
                {
                    HSSFWorkbook hssf = new HSSFWorkbook(fs);
                    ISheet sheet = hssf.GetSheetAt(0);  //只读去第一个sheet工作表内容
                    IRow row = sheet.GetRow(0); //获取第一行
                    //遍历第一行表头
                    for (int i = 0; i < row.LastCellNum; i++)
                    {
                        ICell cell = row.GetCell(i);    //获取到当前单元格
                        data.Columns.Add(row.GetCell(i).StringCellValue);   //
                    }
                    //遍历第二行到sheet的最后一行的每一行
                    for (int i = 1; i < sheet.LastRowNum; i++)
                    {
                        IRow ThisRow = sheet.GetRow(i); //当前行
                        if (ThisRow == null)
                        {
                            continue;
                        }
                        DataRow dataRow = data.NewRow();  //设置datatable表新的一行来接受数据
                        //遍历当前行的每一列
                        for (int j = 0; j < ThisRow.LastCellNum; j++)
                        {
                            ICell cell = ThisRow.GetCell(j);
                            if (cell != null)
                            {

                                if (cell.CellType == CellType.Numeric)
                                {
                                    //NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
                                    if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
                                    {
                                        dataRow[j] = cell.DateCellValue.ToShortDateString();
                                    }
                                    else//其他数字类型
                                    {
                                        dataRow[j] = cell.NumericCellValue.ToString();
                                    }
                                }
                                else if (cell.CellType == CellType.Blank)//空数据类型
                                {
                                    dataRow[j] = "";
                                }
                                else if (cell.CellType == CellType.Formula)//公式类型
                                {
                                    HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(hssf);
                                    dataRow[j] = eva.Evaluate(cell).NumberValue;    //输出公式计算的结果
                                }
                                else //其他类型都按字符串类型来处理
                                {
                                    dataRow[j] = cell.StringCellValue;
                                }
                            }
                        }
                        data.Rows.Add(dataRow);
                    }
                }
                #endregion
                return data;
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 将xlsx格式的Excel读取到Datatable中
        /// </summary>
        /// <param name="fileName">文件路径</param>
        /// <returns></returns>
        public DataTable XSSFReadExcel(string fileName)
        {
            try
            {
                DataTable data = new DataTable();
                #region 读excel
                using (FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Read))
                {
                    XSSFWorkbook xssf = new XSSFWorkbook(fileName);
                    ISheet sheet = xssf.GetSheetAt(0);  //只读去第一个sheet工作表内容
                    IRow row = sheet.GetRow(0); //获取第一行
                    //遍历第一行表头
                    for (int i = 0; i < row.LastCellNum; i++)
                    {
                        ICell cell = row.GetCell(i);    //获取到当前单元格
                        data.Columns.Add(row.GetCell(i).StringCellValue);   //
                    }
                    //遍历第二行到sheet的最后一行的每一行
                    for (int i = 1; i < sheet.LastRowNum; i++)
                    {
                        IRow ThisRow = sheet.GetRow(i); //当前行
                        if (ThisRow == null)
                        {
                            continue;
                        }
                        DataRow dataRow = data.NewRow();  //设置datatable表新的一行来接受数据
                        //遍历当前行的每一列
                        for (int j = 0; j < ThisRow.LastCellNum; j++)
                        {
                            ICell cell = ThisRow.GetCell(j);
                            if (cell != null)
                            {

                                if (cell.CellType == CellType.Numeric)
                                {
                                    //NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
                                    if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
                                    {
                                        dataRow[j] = cell.DateCellValue.ToShortDateString();
                                    }
                                    else//其他数字类型
                                    {
                                        dataRow[j] = cell.NumericCellValue.ToString();
                                    }
                                }
                                else if (cell.CellType == CellType.Blank)//空数据类型
                                {
                                    dataRow[j] = "";
                                }
                                else if (cell.CellType == CellType.Formula)//公式类型
                                {
                                    XSSFFormulaEvaluator eva = new XSSFFormulaEvaluator(xssf);
                                    dataRow[j] = eva.Evaluate(cell).NumberValue;    //输出公式计算的结果
                                }
                                else //其他类型都按字符串类型来处理
                                {
                                    dataRow[j] = cell.StringCellValue;
                                }
                            }
                        }
                        data.Rows.Add(dataRow);
                    }
                }
                #endregion
                return data;
            }
            catch (Exception)
            {

                throw;
            }
        }


        /// <summary>
        /// 将DataGridView数据输出Excel
        /// </summary>
        /// <param name="datatable">DataGridView中的DataTable类型数据</param>
        /// <param name="fileName">文件路径</param>
        public void DataTableOutTOExcel(DataTable datatable, string fileName)
        {
            try
            {
                IWorkbook workbook = null;
                string endName = Path.GetExtension(fileName);
                if (endName.ToUpper() == ".XLS")
                {
                    workbook = new HSSFWorkbook();
                }
                else if (endName.ToUpper() == ".XLSX")
                {
                    workbook = new XSSFWorkbook();
                }
                using (FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Write))
                {
                    if (workbook != null)
                    {
                        ISheet sheet = workbook.CreateSheet("sheet1"); ;  //创建第一个sheet
                        #region 遍历表的第一行标题行
                        IRow row = sheet.CreateRow(0);     //创建第一行
                        for (int i = 0; i < datatable.Columns.Count; i++)
                        {
                            ICell cell = row.CreateCell(i);
                            cell.SetCellValue(datatable.Columns[i].ColumnName);
                        }
                        #endregion
                        #region 遍历表中的数据行
                        //遍历第二行开始的每一行
                        for (int i = 1; i < datatable.Rows.Count; i++)
                        {
                            row = sheet.CreateRow(i);
                            //遍历每个单元格
                            for (int j = 0; j < datatable.Columns.Count; j++)
                            {
                                ICell cell = row.CreateCell(j);
                                cell.SetCellValue(datatable.Rows[i][j].ToString());
                            }
                        }
                        #endregion
                    }
                    workbook.Write(fs);
                }

            }
            catch (Exception)
            {
                throw;
            }

        }