/// <summary> /// 将excel中的数据导入到DataTable中 /// </summary> /// <param>excel工作薄sheet的名称</param> /// <param>第一行是否是DataTable的列名</param> /// <param>文件路径</param> /// <returns>返回的DataTable</returns> public static DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn, string fileName) { if (string.IsNullOrEmpty(sheetName)) { throw new ArgumentNullException(sheetName); } if (string.IsNullOrEmpty(fileName)) { throw new ArgumentNullException(fileName); } var data = new DataTable(); IWorkbook workbook = null; FileStream fs = null; try { fs = new FileStream(fileName, FileMode.Open, FileAccess.Read); if (fileName.IndexOf(".xlsx", StringComparison.Ordinal) > 0) { workbook = new XSSFWorkbook(fs); } else if (fileName.IndexOf(".xls", StringComparison.Ordinal) > 0) { workbook = new HSSFWorkbook(fs); } ISheet sheet = null; if (workbook != null) { //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet sheet = workbook.GetSheet(sheetName) ?? workbook.GetSheetAt(0); } if (sheet == null) return data; var firstRow = sheet.GetRow(0); //一行最后一个cell的编号 即总的列数 int cellCount = firstRow.LastCellNum; int startRow; if (isFirstRowColumn) { for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { var cell = firstRow.GetCell(i); var cellValue = cell.StringCellValue; if (cellValue == null) continue; var column = new DataColumn(cellValue); data.Columns.Add(column); } startRow = sheet.FirstRowNum + 1; } else { startRow = sheet.FirstRowNum; } //最后一列的标号 var rowCount = sheet.LastRowNum; for (var i = startRow; i <= rowCount; ++i) { var row = sheet.GetRow(i); //没有数据的行默认是null if (row == null) continue; var dataRow = data.NewRow(); for (int j = row.FirstCellNum; j < cellCount; ++j) { //同理，没有数据的单元格都默认是null if (row.GetCell(j) != null) dataRow[j] = row.GetCell(j).ToString(); } data.Rows.Add(dataRow); } return data; } catch (IOException ioex) { throw new IOException(ioex.Message); } catch (Exception ex) { throw new Exception(ex.Message); } finally { if (fs != null) { fs.Close(); } } }
4.读取Excel文件内容转换为DataSet：
/// <summary> /// 读取Excel文件内容转换为DataSet,列名依次为 "c0"……c[columnlength-1] /// </summary> /// <param>文件绝对路径</param> /// <param>数据开始行数(1为第一行)</param> /// <param>每列的数据类型</param> /// <returns></returns> public static DataSet ReadExcel(string fileName, int startRow, params NpoiDataType[] columnDataType) { var ds = new DataSet("ds"); var dt = new DataTable("dt"); var sb = new StringBuilder(); using (var stream = new FileStream(fileName, FileMode.Open, FileAccess.Read)) { //使用接口，自动识别excel2003/2007格式 var workbook = WorkbookFactory.Create(stream); //得到里面第一个sheet var sheet = workbook.GetSheetAt(0); int j; IRow row; //ColumnDataType赋值 if (columnDataType.Length <= 0) { //得到第i行 row = sheet.GetRow(startRow - 1); columnDataType = new NpoiDataType[row.LastCellNum]; for (var i = 0; i < row.LastCellNum; i++) { var hs = row.GetCell(i); columnDataType[i] = GetCellDataType(hs); } } for (j = 0; j < columnDataType.Length; j++) { var tp = GetDataTableType(columnDataType[j]); dt.Columns.Add("c" + j, tp); } for (var i = startRow - 1; i <= sheet.PhysicalNumberOfRows; i++) { //得到第i行 row = sheet.GetRow(i); if (row == null) continue; try { var dr = dt.NewRow(); for (j = 0; j < columnDataType.Length; j++) { dr["c" + j] = GetCellData(columnDataType[j], row, j); } dt.Rows.Add(dr); } catch (Exception er) { sb.Append(string.Format("第{0}行出错：{1}\r\n", i + 1, er.Message)); } } ds.Tables.Add(dt); } if (ds.Tables[0].Rows.Count == 0 && sb.ToString() != "") throw new Exception(sb.ToString()); return ds; }
5.从DataSet导出到2003：