/// <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; } }