本文主要是介绍NPOI.HSSF 导入excel数据(记录)，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
https://download.csdn.net/download/ttbat/15435334 NPOI.HSSF.Dll 下载地址。
在项目引用 NPOI.HSSF.Dll 后 using NPOI.HSSF.UserModel;
PS：excel文件只能读取1997-2003版本的，2007以上的excel版本无法读取。
/// <summary>/// /// </summary>/// <param name="path">文件地址</param>/// <param name="_syptktService"></param>/// <param name="success">成功数量</param>/// <param name="failure">错误数量</param>/// <returns></returns>
public static string ImprotSXSC(string path, ISYPTKTService _syptktService, out int success, out int failure){string errorMsg = string.Empty;success = failure = 0;try{HSSFWorkbook hssfworkbook;using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read)){#region 检查excel数据hssfworkbook = new HSSFWorkbook(file);HSSFSheet sheet = hssfworkbook.GetSheetAt(0);if (sheet == null || sheet.LastRowNum < 0){errorMsg = "excel格式异常，请检查";return errorMsg;}Dictionary<string, int> headCellDic = GetCellDictNum(sheet, 0); //表头键与索引集合errorMsg = CheckExcel(sheet);if (!string.IsNullOrEmpty(errorMsg))return errorMsg;#endregionHSSFRow row = null;HSSFCell cell;string rowMsg;int startRowIndex = 1;for (int i = startRowIndex; i <= sheet.LastRowNum; i++){ row = sheet.GetRow(i);cell = row.GetCell(headCellDic["A"]);string name = getCellStringValue(row, cell, "A", out rowMsg);var model = _syptktService.GetSxScxts(s => s.NAME.Contains(name)).FirstOrDefault();bool isnew = false;int failure1 = 0;if (model == null){model = new SXSCXTEntiry();isnew = true;}model.NAME = name;cell = row.GetCell(headCellDic["B"]);string X = getCellStringValue(row, cell, "B", out rowMsg);cell = row.GetCell(headCellDic["C"]);string Y = getCellStringValue(row, cell, "C", out rowMsg);float x = CommonMethodNew.FromDFM(X);float y = CommonMethodNew.FromDFM(Y);model.X = X;model.Y = Y;if (X != "" && Y != "" && x >= 3 && y >= 2){model.SHAPE = "POINT ( " + x + " " + y + ")";}if (string.IsNullOrEmpty(name)){failure1++;errorMsg += string.Format("第{0}行水下生产系统名称未录入$", i);}if (string.IsNullOrEmpty(model.SHAPE)){failure1++;errorMsg += string.Format("{0}水下生产系统经纬度数据不正确$", name); }if (failure1 > 0){ failure++; continue; }}}}catch (Exception ex){failure++;errorMsg = ex.ToString();}return errorMsg;}/// <summary>/// 获取excel指定行中，列键值（键/索引）集合/// </summary>/// <param name="sheet"></param>/// <param name="rowIndex"></param>/// <returns></returns>public static Dictionary<string, int> GetCellDictNum(HSSFSheet sheet,int rowIndex){Dictionary<string, int> celldict = new Dictionary<string, int>();HSSFRow frow = sheet.GetRow(rowIndex);int colNum = frow.PhysicalNumberOfCells;char A = 'A';int Acode = (int)A;int firA = 0;String sfir = "";String stow = "";for (int i = 0; i < colNum; i++){if (i < 26){celldict.Add(((char)(Acode + i)).ToString(), i);}else if (i >= 26 && i <= (26 * 26)){firA = i / 26;sfir = ((char)(Acode + firA - 1)).ToString();firA = i % 26;stow = ((char)(Acode + firA)).ToString();celldict.Add(sfir + stow, i);}}return celldict;}/// <summary>/// 获取文本格式/// </summary>/// <param name="row"></param>/// <param name="cell"></param>/// <param name="cellKey"></param>/// <param name="errorMsg"></param>/// <returns></returns>public static string getCellStringValue(HSSFRow row, HSSFCell cell, string cellKey, out string errorMsg){errorMsg = string.Empty;string value = null;if (cell == null){return null;}if (cell.CellType == HSSFCell.CELL_TYPE_NUMERIC)value= cell.NumericCellValue.ToString();else if (cell.CellType == HSSFCell.CELL_TYPE_STRING)value= cell.StringCellValue;else{try{value = cell.StringCellValue;}catch (Exception ex){errorMsg = string.Format("文档第" + (row.RowNum + 1) + "行," + cellKey + "列格式不正确");}}if(!string.IsNullOrEmpty(value))value = value.Trim().Replace("\n", "").Replace(" ", "").Replace("\t", "").Replace("\r", ""); ;return value;}
这篇关于NPOI.HSSF 导入excel数据(记录)的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
