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;
                    #endregion
                    HSSFRow 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;
        }