public static System.Data.DataTable GetDataTabelFromExcelFile(string fileName, int startRowIndex) { System.Data.DataTable dataSource = new System.Data.DataTable(); dataSource.Columns.Add("Index", typeof(string)); dataSource.Columns.Add("IndexNPOI", typeof(string)); dataSource.Columns.Add("Name", typeof(string)); dataSource.Columns.Add("Description", typeof(string)); dataSource.Columns.Add("HexString", typeof(string)); // 获取Workbook。 NPOI.SS.UserModel.IWorkbook workbook = NPOIExtension.GetWorkbookFromExcelFile(fileName); if (workbook == null) { return(dataSource); } // 获取Sheet NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0); for (int i = startRowIndex; i < sheet.LastRowNum; i++) { NPOI.SS.UserModel.IRow row = sheet.GetRow(i); System.Data.DataRow dataSourceRow = dataSource.NewRow(); dataSource.Rows.Add(dataSourceRow); for (int j = 0; j < dataSource.Columns.Count; j++) { dataSourceRow[j] = row.GetCell(j).ToString(); } } return(dataSource); }
/// <summary> /// Implementació default /// </summary> /// <param name="workbook"></param> /// <param name="resumenProceso"></param> /// <returns></returns> public override bool ValidarPlantillaExcelImportacion(NPOI.SS.UserModel.IWorkbook workBookImportar, Common.Dtos.ResumenProcesoImportacionExcelDto resumenProceso) { bool puedeContinuar = true; var espacioNombresPlantilla = ""; var valorDefaultValidacionPlantilla2014 = ""; if (workBookImportar.NumberOfSheets > 0) { //2014 Validar opcionalmente que la plantilla no corresponda al espacio de nombres de la taxonomía //en la hoja 1, renglón 1, columna 2 debe encontrarse un espacio. var hoja1 = workBookImportar.GetSheetAt(0); var renglon1 = hoja1.GetRow(0); if (renglon1 != null) { var celdaValidacion = renglon1.GetCell(1); if (celdaValidacion != null) { espacioNombresPlantilla = celdaValidacion.StringCellValue; } } } if (!valorDefaultValidacionPlantilla2014.Equals(espacioNombresPlantilla)) { resumenProceso.InformeErrores.Add(new InformeErrorImportacion() { Mensaje = "La plantilla de importación para la taxonomía (" + this.documentoInstancia.Taxonomia.nombreAbax + ") " + "\n no es la esperada." }); puedeContinuar = false; } return(puedeContinuar); }
/// <summary> /// Implementació default /// </summary> /// <param name="workbook"></param> /// <param name="resumenProceso"></param> /// <returns></returns> public override bool ValidarPlantillaExcelImportacion(NPOI.SS.UserModel.IWorkbook workBookImportar, Common.Dtos.ResumenProcesoImportacionExcelDto resumenProceso) { bool puedeContinuar = true; var espacioNombresPlantilla = ""; if (workBookImportar.NumberOfSheets > 0) { //2019 Validar opcionalmente que la plantilla corresponda al espacio de nombres de la taxonomía //en la hoja 1, renglón 1, columna 2 se colocará el espacio de nombres de la taxonomía var hoja1 = workBookImportar.GetSheetAt(0); var renglon1 = hoja1.GetRow(0); if (renglon1 != null) { var celdaValidacion = renglon1.GetCell(1); if (celdaValidacion != null) { espacioNombresPlantilla = celdaValidacion.StringCellValue; } } } if (!this.documentoInstancia.EspacioNombresPrincipal.Equals(espacioNombresPlantilla)) { resumenProceso.InformeErrores.Add(new InformeErrorImportacion() { Mensaje = "El documento Excel no pertenece a la taxonomía que está utilizando (" + this.documentoInstancia.Taxonomia.nombreAbax + ")." + "\nAsegúrese que el documento corresponda a la taxonomía de la que desea importar los datos." }); puedeContinuar = false; } return(puedeContinuar); }
public static System.Data.DataTable GetDataTableFromExcelActiveSheet(string fileName, 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.GetSheetAt(workbook.ActiveSheetIndex); if (sheet == null) { return(null); } return(GetDataTableFromSheet(sheet, startRowIndex, startColumnIndex, firstRowIsColumnHead, autoAddColumn, ignoreBlankRow)); }
public static System.Data.DataSet GetDataSet(string fileName, bool firstRowIsColumnHead, bool ignoreHiddenSheet) { NPOI.SS.UserModel.IWorkbook iWorkbook = Extension.NPOIMethod.GetWorkbookFromExcelFile(fileName); if (iWorkbook == null) { return(null); } System.Data.DataSet dataSet = new System.Data.DataSet(); for (int iSheetIndex = 0; iSheetIndex < iWorkbook.NumberOfSheets; iSheetIndex++) { bool isHiddenSheet = iWorkbook.IsSheetHidden(iSheetIndex) || iWorkbook.IsSheetHidden(iSheetIndex); if (ignoreHiddenSheet && isHiddenSheet) { continue; } dataSet.Tables.Add(GetDataTable(iWorkbook.GetSheetAt(iSheetIndex), firstRowIsColumnHead, 0, 0, 0)); } return(dataSet); }
public static System.Data.DataSet GetDataSetFromExcelFile(string fileName, bool firstRowIsColumnHead = true, bool ignoreBlankRow = true, bool ignoreHiddenSheet = true) { NPOI.SS.UserModel.IWorkbook workbook = GetWorkbookFromExcelFile(fileName); if (workbook == null) { return(null); } System.Data.DataSet dataSet = new System.Data.DataSet(); for (int i = 0; i < workbook.NumberOfSheets; i++) { bool isHiddenSheet = workbook.IsSheetHidden(i) || workbook.IsSheetVeryHidden(i); if (ignoreHiddenSheet && isHiddenSheet) { continue; } dataSet.Tables.Add(GetDataTableFromSheet(workbook.GetSheetAt(i), 0, 0, true, true, true)); } return(dataSet); }
public static System.Data.DataTable GetDataTableFromExcelActiveSheet(string fileName, int startRowIndex, int startColumnIndex, bool ignoreBlankRow = true, params string[] columnHeadNames) { NPOI.SS.UserModel.IWorkbook workbook = GetWorkbookFromExcelFile(fileName); if (workbook == null) { return(null); } NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(workbook.ActiveSheetIndex); 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); }
private void button6_Click(object sender, EventArgs e) { List <FishEntity.CallRecordsEntity> list = new List <FishEntity.CallRecordsEntity>(); for (int i = 0; i < 10; i++) { FishEntity.CallRecordsEntity entity = new FishEntity.CallRecordsEntity(); entity.code = "00000" + i; entity.customer = "杭州" + i; entity.linkman = "金" + i; entity.mobile = "13757193476"; list.Add(entity); } string startDate = "2015-03-01"; string endDate = "2015-03-25"; string templatepath = Application.StartupPath + "\\template\\callrecord.xls"; FileStream fs = new FileStream(templatepath, FileMode.Open, FileAccess.Read); NPOI.SS.UserModel.IWorkbook workbook = null; workbook = NPOI.SS.UserModel.WorkbookFactory.Create(fs); NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0); int firstRow = sheet.FirstRowNum; int lastRow = sheet.LastRowNum; System.Collections.Hashtable hs = new System.Collections.Hashtable(); hs.Add("startDate", startDate); hs.Add("endDate", endDate); hs.Add("items", list); int arrStart = -1; int arrItem = -1; int arrEnd = -1; List <FishEntity.CallRecordsEntity> items = null; for (int idx = firstRow; idx <= lastRow; idx++) { NPOI.SS.UserModel.IRow row = sheet.GetRow(idx); foreach (NPOI.SS.UserModel.ICell cell in row.Cells) { string val = cell.ToString(); if (string.IsNullOrEmpty(val)) { continue; } foreach (System.Collections.DictionaryEntry entry in hs) { string key1 = "<jx:forEach items=\"${" + entry.Key.ToString() + "}\" var=\"item\"}>"; if (val.Equals(key1)) { arrStart = idx; items = entry.Value as List <FishEntity.CallRecordsEntity>; break; } string key2 = "</jx:forEach>"; if (val.Equals(key2)) { arrEnd = idx; break; } string key = "${" + entry.Key.ToString() + "}"; if (val.Contains(key)) { val = val.Replace(key, entry.Value.ToString()); } } } } if (arrStart >= 0 && arrEnd >= 2) { arrItem = arrStart + 1; NPOI.SS.UserModel.IRow rrrow = sheet.GetRow(arrItem); List <KV> columns = GetItemNames(rrrow); NPOI.SS.UserModel.IRow row = null; for (int idx = 0; idx < items.Count; idx++) { int tttidx = arrStart + idx; if (tttidx != arrItem) { row = sheet.CreateRow(tttidx); NPOI.SS.UserModel.IRow ffrow = sheet.GetRow(arrItem); //row = sheet.CopyRow(arrItem, tttidx); for (int i = 0; i < columns.Count; i++) { row.CreateCell(i); } } else { row = rrrow; } foreach (NPOI.SS.UserModel.ICell cell in row.Cells) { foreach (KV kv in columns) { if (kv.colIdx == cell.ColumnIndex) { System.Reflection.PropertyInfo prop = items[idx].GetType().GetProperty(kv.name); if (prop != null) { string temp = prop.GetValue(items[idx], null).ToString(); cell.SetCellValue(temp); } } } } } if ((arrEnd - arrStart + 1) > items.Count) { //TODO } string fpath = Application.StartupPath + "\\test.xls"; System.IO.FileStream fss = new FileStream(fpath, FileMode.Create, FileAccess.Write); workbook.Write(fss); fss.Close(); } }
public static System.Data.DataTable GetDataTabelFromExcelFile(string fileName, bool firstLineIsColumnHead) { System.Data.DataTable dataSource = new System.Data.DataTable(); NPOI.SS.UserModel.IWorkbook workbook = GetWorkbookFromExcelFile(fileName); if (workbook == null) { return(dataSource); } NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(workbook.ActiveSheetIndex); if (sheet == null) { return(dataSource); } NPOI.SS.UserModel.IRow firstRow = sheet.GetRow(0); if (firstRow != null) { for (int columnIndex = 0; columnIndex < firstRow.LastCellNum; columnIndex++) { if (firstLineIsColumnHead) { NPOI.SS.UserModel.ICell cell = firstRow.GetCell(columnIndex); if (cell == null) { dataSource.Columns.Add(); } else { dataSource.Columns.Add(cell.ToString()); } } else { dataSource.Columns.Add(); } } } int startRowIndex = firstLineIsColumnHead ? 1 : 0; for (int rowIndex = startRowIndex; rowIndex <= sheet.LastRowNum; rowIndex++) { System.Data.DataRow dataSourceRow = dataSource.NewRow(); dataSource.Rows.Add(dataSourceRow); NPOI.SS.UserModel.IRow row = sheet.GetRow(rowIndex); if (row != null) { for (int columnIndex = 0; columnIndex < dataSource.Columns.Count; columnIndex++) { NPOI.SS.UserModel.ICell cell = row.GetCell(columnIndex); if (cell != null) { switch (cell.CellType) { case NPOI.SS.UserModel.CellType.Blank: dataSourceRow[columnIndex] = ""; break; case NPOI.SS.UserModel.CellType.Boolean: dataSourceRow[columnIndex] = cell.BooleanCellValue; break; case NPOI.SS.UserModel.CellType.Error: dataSourceRow[columnIndex] = cell.ErrorCellValue; break; case NPOI.SS.UserModel.CellType.Formula: dataSourceRow[columnIndex] = cell.NumericCellValue; break; case NPOI.SS.UserModel.CellType.Numeric: if (NPOI.SS.UserModel.DateUtil.IsCellDateFormatted(cell)) { dataSourceRow[columnIndex] = cell.DateCellValue; } else { dataSourceRow[columnIndex] = cell.NumericCellValue; } break; case NPOI.SS.UserModel.CellType.String: dataSourceRow[columnIndex] = cell.StringCellValue; break; default: dataSourceRow[columnIndex] = cell.ToString(); break; } } } } } return(dataSource); }
public static System.Data.DataTable GetDataTabelFromExcelFile(string fileName, int sheetNumber, int startColumnNumber, int startRowNumber, params string[] columnNames) { System.Data.DataTable dataSource = new System.Data.DataTable(); if (columnNames.Length < 1) { return(dataSource); } foreach (string columnName in columnNames) { dataSource.Columns.Add(columnName); } NPOI.SS.UserModel.IWorkbook workbook = GetWorkbookFromExcelFile(fileName); if (workbook == null) { return(dataSource); } if (sheetNumber > workbook.NumberOfSheets) { return(dataSource); } int sheetIndex = sheetNumber > 0 ? sheetNumber - 1 : workbook.ActiveSheetIndex; NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(sheetIndex); int startRowIndex = startRowNumber > 0 ? startRowNumber - 1 : 0; for (int i = startRowIndex; i <= sheet.LastRowNum; i++) { System.Data.DataRow dataSourceRow = dataSource.NewRow(); dataSource.Rows.Add(dataSourceRow); NPOI.SS.UserModel.IRow row = sheet.GetRow(i); if (row != null) { int startColumnIndex = startColumnNumber > 0 ? startColumnNumber - 1 : 0; int columnIndex = 0; Enumerable.Range(startColumnIndex, dataSource.Columns.Count).ToList().ForEach(cellIndex => { NPOI.SS.UserModel.ICell cell = row.GetCell(cellIndex); if (cell != null) { switch (cell.CellType) { case NPOI.SS.UserModel.CellType.Blank: dataSourceRow[columnIndex] = ""; break; case NPOI.SS.UserModel.CellType.Boolean: dataSourceRow[columnIndex] = cell.BooleanCellValue; break; case NPOI.SS.UserModel.CellType.Error: dataSourceRow[columnIndex] = cell.ErrorCellValue; break; case NPOI.SS.UserModel.CellType.Formula: dataSourceRow[columnIndex] = cell.NumericCellValue; break; case NPOI.SS.UserModel.CellType.Numeric: if (NPOI.SS.UserModel.DateUtil.IsCellDateFormatted(cell)) { dataSourceRow[columnIndex] = cell.DateCellValue; } else { dataSourceRow[columnIndex] = cell.NumericCellValue; } break; case NPOI.SS.UserModel.CellType.String: dataSourceRow[columnIndex] = cell.StringCellValue; break; default: dataSourceRow[columnIndex] = cell.ToString(); break; } } columnIndex++; }); } } return(dataSource); }
public ActionResult Import(VModel.SyUserTeacher.FormImport m) { if (ModelState.IsValid) { if (m.File.FileName.ToLower().IndexOf("xls") == -1) { ModelState.AddModelError("File", string.Format("导入文件必须为Excel文件")); return(View(m)); } NPOI.SS.UserModel.IWorkbook book = NPOI.SS.UserModel.WorkbookFactory.Create(m.File.InputStream); NPOI.SS.UserModel.ISheet sheet = book.GetSheetAt(0); if (sheet != null) { List <VModel.SyUserTeacher.ImportData> list = new List <VModel.SyUserTeacher.ImportData>(); NPOI.SS.UserModel.IRow firstRow = sheet.GetRow(0); int startRow = sheet.FirstRowNum + 2; int rowCount = sheet.LastRowNum; for (int i = startRow; i <= rowCount; ++i) { NPOI.SS.UserModel.IRow row = sheet.GetRow(i); if (row == null) { continue; //没有数据的行默认是null } if (row.GetCell(0) == null || string.IsNullOrWhiteSpace(row.GetCell(0).ToString())) { ModelState.AddModelError("File", string.Format("第{0}行“用户名”不能为空", i + 1)); return(View(m)); } else if (Bll.SyTeacherBll.IsExist(row.GetCell(0).ToString(), null)) { ModelState.AddModelError("File", string.Format("第{0}行“用户名”已经存在", i + 1)); return(View(m)); } else if (row.GetCell(0).ToString().Length > 20) { ModelState.AddModelError("File", string.Format("第{0}行“用户名”最大长度不超过20个字符的内容", i + 1)); return(View(m)); } else if (row.GetCell(1) == null || string.IsNullOrWhiteSpace(row.GetCell(1).ToString())) { ModelState.AddModelError("File", string.Format("第{0}行“姓名”不能为空", i + 1)); return(View(m)); } //else if (Bll.SyTeacherBll.IsExist(null,row.GetCell(1).ToString())) //{ // ModelState.AddModelError("File", string.Format("第{0}行“姓名”已经存在", i + 1)); // return View(m); //} else if (row.GetCell(1).ToString().Length > 20) { ModelState.AddModelError("File", string.Format("第{0}行“姓名”最大长度不超过20个字符的内容", i + 1)); return(View(m)); } else if (row.GetCell(2) == null || string.IsNullOrWhiteSpace(row.GetCell(2).ToString())) { ModelState.AddModelError("File", string.Format("第{0}行“学科”不能为空", i + 1)); return(View(m)); } else if (Bll.DictSubjectBll.IsExist(row.GetCell(2).ToString()) == false) { ModelState.AddModelError("File", string.Format("第{0}行“学科”不存在", i + 1)); return(View(m)); } VModel.SyUserTeacher.ImportData data = new VModel.SyUserTeacher.ImportData(); data.Account = row.GetCell(0).ToString(); data.Name = row.GetCell(1).ToString(); data.SubjectName = row.GetCell(2).ToString(); list.Add(data); } int r = Bll.SyUserBll.ImportTeacher(list); switch (r) { case 200: return(RedirectToAction("Index")); } } } return(View(m)); }
public void Save(string FileName, Model model) { try { int idx = FileName.LastIndexOf('.'); string ext = FileName.Substring(idx + 1); NPOI.SS.UserModel.IWorkbook workbook = null; if (File.Exists(FileName)) { using (FileStream fs = new FileStream(FileName, FileMode.Open, FileAccess.Read)) { workbook = NPOI.SS.UserModel.WorkbookFactory.Create(fs); int nSheets = workbook.NumberOfSheets; if (nSheets > 0) { var sheet = workbook.GetSheetAt(0); int nRows = sheet.LastRowNum + 1; var row = sheet.CreateRow(nRows); row.CreateCell(0).SetCellValue(model.Id); row.CreateCell(1).SetCellValue(model.Time); row.CreateCell(2).SetCellValue(model.QRCode); row.CreateCell(3).SetCellValue(model.Flow); row.CreateCell(4).SetCellValue(model.APressure); row.CreateCell(5).SetCellValue(model.BPressure); } fs.Close(); } } else { if (ext == "xls") { workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(); } else if (ext == "xlsx") { workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); } else { return; } var sheet = workbook.CreateSheet(); var row0 = sheet.CreateRow(0); row0.CreateCell(0).SetCellValue("序号"); row0.CreateCell(1).SetCellValue("时间"); row0.CreateCell(2).SetCellValue("条码"); row0.CreateCell(3).SetCellValue("流量/ml"); row0.CreateCell(4).SetCellValue("A胶压力/psi"); row0.CreateCell(5).SetCellValue("B胶压力/psi"); var row = sheet.CreateRow(1); row.CreateCell(0).SetCellValue(model.Id); row.CreateCell(1).SetCellValue(model.Time); row.CreateCell(2).SetCellValue(model.QRCode); row.CreateCell(3).SetCellValue(model.Flow); row.CreateCell(4).SetCellValue(model.APressure); row.CreateCell(5).SetCellValue(model.BPressure); } using (FileStream fs_write = new FileStream(FileName, FileMode.Create)) { workbook.Write(fs_write); fs_write.Close(); } } catch (Exception ex) { MessageBox.Show(ex.Message); } }
/// <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); } }