public static System.Data.DataTable GetDataTable(string fileName, string sheetName, bool firstRowIsColumnHead, int maxColumnCount, int startColumnIndex, int startRowIndex) { NPOI.SS.UserModel.IWorkbook iWorkbook = Extension.NPOIMethod.GetWorkbookFromExcelFile(fileName); if (iWorkbook == null) { return(null); } NPOI.SS.UserModel.ISheet iSheet = iWorkbook.GetSheet(sheetName); return(GetDataTable(iSheet, firstRowIsColumnHead, maxColumnCount, startColumnIndex, startRowIndex)); }
public static System.Data.DataTable GetDataTableFromExcelFile(string fileName, string sheetName, int startRowIndex, int startColumnIndex, bool firstRowIsColumnHead = true, bool autoAddColumn = false, bool ignoreBlankRow = true) { NPOI.SS.UserModel.IWorkbook workbook = GetWorkbookFromExcelFile(fileName); if (workbook == null) { return(null); } NPOI.SS.UserModel.ISheet sheet = workbook.GetSheet(sheetName); if (sheet == null) { return(null); } return(GetDataTableFromSheet(sheet, startRowIndex, startColumnIndex, firstRowIsColumnHead, autoAddColumn, ignoreBlankRow)); }
public static System.Data.DataTable GetDataTableFromExcelFile(string fileName, string sheetName, int startRowIndex, int startColumnIndex, bool ignoreBlankRow, params string[] columnHeadNames) { NPOI.SS.UserModel.IWorkbook workbook = GetWorkbookFromExcelFile(fileName); if (workbook == null) { return(null); } NPOI.SS.UserModel.ISheet sheet = workbook.GetSheet(sheetName); if (sheet == null) { return(null); } System.Data.DataTable dataTable = new System.Data.DataTable(sheet.SheetName); for (int i = 0; i < columnHeadNames.Length; i++) { dataTable.Columns.Add(columnHeadNames[i]); } if (columnHeadNames.Length > 0) { for (int rowIndex = startRowIndex; rowIndex < sheet.PhysicalNumberOfRows; rowIndex++) { NPOI.SS.UserModel.IRow row = sheet.GetRow(rowIndex); if (row != null) { System.Data.DataRow dataRow = dataTable.NewRow(); dataTable.Rows.Add(dataRow); int dataTableColumnIndex = 0; for (int columnIndex = startColumnIndex; columnIndex < row.PhysicalNumberOfCells; columnIndex++) { if (dataTableColumnIndex >= dataTable.Columns.Count) { break; } dataRow[dataTableColumnIndex] = GetCellValue(row.GetCell(columnIndex)); dataTableColumnIndex++; } } } } return(dataTable); }
/// <summary> /// 将excel文件内容读取到DataTable数据表中 /// </summary> /// <param name="fileName">文件完整路径名</param> /// <param name="sheetName">指定读取excel工作薄sheet的名称</param> /// <param name="isFirstRowColumn">第一行是否是DataTable的列名:true=是,false=否</param> /// <returns>DataTable数据表</returns> public static DataTable ReadExcelToDataTable(string fileName, string sheetName = null, bool isFirstRowColumn = true) { //定义要返回的datatable对象 DataTable data = new DataTable(); //excel工作表 NPOI.SS.UserModel.ISheet sheet = null; //数据开始行(排除标题行) int startRow = 0; try { if (!File.Exists(fileName)) { return(null); } //根据指定路径读取文件 FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read); //根据文件流创建excel数据结构 NPOI.SS.UserModel.IWorkbook workbook = NPOI.SS.UserModel.WorkbookFactory.Create(fs); //IWorkbook workbook = new HSSFWorkbook(fs); //如果有指定工作表名称 if (!string.IsNullOrEmpty(sheetName)) { sheet = workbook.GetSheet(sheetName); //如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet if (sheet == null) { sheet = workbook.GetSheetAt(0); } } else { //如果没有指定的sheetName,则尝试获取第一个sheet sheet = workbook.GetSheetAt(0); } if (sheet != null) { NPOI.SS.UserModel.IRow firstRow = sheet.GetRow(0); //一行最后一个cell的编号 即总的列数 int cellCount = firstRow.LastCellNum; //如果第一行是标题列名 if (isFirstRowColumn) { for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { NPOI.SS.UserModel.ICell cell = firstRow.GetCell(i); cell.SetCellType(NPOI.SS.UserModel.CellType.String); if (cell != null) { string cellValue = cell.StringCellValue; if (cellValue != null) { DataColumn column = new DataColumn(cellValue); data.Columns.Add(column); } } } startRow = sheet.FirstRowNum + 1; } else { startRow = sheet.FirstRowNum; } //最后一列的标号 int rowCount = sheet.LastRowNum; for (int i = startRow; i <= rowCount; ++i) { NPOI.SS.UserModel.IRow row = sheet.GetRow(i); if (row == null) { continue; //没有数据的行默认是null } DataRow dataRow = data.NewRow(); for (int j = row.FirstCellNum; j < cellCount; ++j) { if (row.GetCell(j) != null) //同理,没有数据的单元格都默认是null { dataRow[j] = row.GetCell(j).ToString(); } } data.Rows.Add(dataRow); } } return(data); } catch (Exception ex) { throw ex; } }
/// <summary> /// 将excel中的数据导入到DataTable中 /// </summary> /// <param name="sheetName">excel工作薄sheet的名称</param> /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param> /// <returns>返回的DataTable</returns> /// <author>范永坚 2017-08-09</author> public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn) { NPOI.SS.UserModel.ISheet sheet = null; var data = new DataTable(); data.TableName = sheetName; int startRow = 0; try { sheet = sheetName != null?_workbook.GetSheet(sheetName) : _workbook.GetSheetAt(0); if (sheet != null) { var firstRow = sheet.GetRow(0); if (firstRow == null) { return(data); } int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数 startRow = isFirstRowColumn ? sheet.FirstRowNum + 1 : sheet.FirstRowNum; for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { //.StringCellValue; var column = new DataColumn(Convert.ToChar(((int)'A') + i).ToString()); if (isFirstRowColumn) { var columnName = firstRow.GetCell(i).StringCellValue; column = new DataColumn(columnName); } data.Columns.Add(column); } //最后一列的标号 int rowCount = sheet.LastRowNum; for (int i = startRow; i <= rowCount; ++i) { NPOI.SS.UserModel.IRow row = sheet.GetRow(i); if (row == null) { continue; //没有数据的行默认是null } DataRow dataRow = data.NewRow(); for (int j = row.FirstCellNum; j < cellCount; ++j) { if (row.GetCell(j) != null) //同理,没有数据的单元格都默认是null { dataRow[j] = row.GetCell(j, NPOI.SS.UserModel.MissingCellPolicy.RETURN_NULL_AND_BLANK).ToString(); } } data.Rows.Add(dataRow); } } else { throw new Exception("Don not have This Sheet"); } return(data); } catch (Exception ex) { Console.WriteLine("Exception: " + ex.Message); return(null); } }