C# 用NPOI导入Excel数据

最新推荐文章于 2024-07-22 21:42:23 发布

Bulusi.li

最新推荐文章于 2024-07-22 21:42:23 发布

阅读量606

点赞数

分类专栏： C# 文章标签： c# NOPI

本文链接：https://blog.csdn.net/lijun6666666666/article/details/119946555

版权

C# 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

这段代码展示了如何从Excel文件中导入数据并进行处理，特别处理了单元格未激活导致的空引用异常。通过创建缺失单元格并设置为空字符串，确保后续逻辑的正常执行。同时，对导入的数据进行了验证和存储，对错误行进行了记录。

摘要由CSDN通过智能技术生成

注意点：用NOPO有个缺点，会出现单元格未激活情况，取单元格数据就会报错，下面有解决方法。

//excel导入数据
        public List<IRow> ImportExcelData(string filePath)
        {
            var fs = System.IO.File.OpenRead(filePath);
            var wk = new XSSFWorkbook(fs);
            var errorRow = new List<IRow>();//导入失败的行
            try
            {
                //读取产品信息sheet
                var sheet = wk.GetSheetAt(0);
                for (int i = 1; i <= sheet.LastRowNum; i++)
                {
                    var row = sheet.GetRow(i);
                    if (row != null)
                    {
                    	//排除单元格未激活情况
                        for (int m = 0; m <= 11; m++)
                        {
                            if (row.GetCell(m) == null)
                            {
                                ICell cell = row.CreateCell(m);
                                cell.SetCellValue("");
                            }
                        }
                        //下面就是逻辑代码了
                        if (row.GetCell(0).ToString() == "" || row.GetCell(1).ToString() == "" || row.GetCell(2).ToString() == "" || row.GetCell(5).ToString() == "" || row.GetCell(6).ToString() == "" || row.GetCell(8).ToString() == "")
                        {
                            errorRow.Add(row);
                            continue;
                        }
                        var specificationNo = row.GetCell(1).ToString();
                        var productLocal = _productSpecificationRepository.GetAll().Where(t => t.SpecificationNo == specificationNo && t.IsDeleted == false).ToList();
                        if (productLocal.Count != 0)
                        {
                            errorRow.Add(row);
                            continue;
                        }
                        var unitFirstActive = false;
                        var unitSecondActive = false;
                        if (row.GetCell(9).ToString() != "")
                        {
                            unitFirstActive = true;
                        }
                        if (row.GetCell(10).ToString() != "")
                        {
                            unitSecondActive = true;
                        }
                        var product = new ProductSpecification
                        {
                            Type = row.GetCell(0).ToString(),
                            SpecificationNo = row.GetCell(1).ToString(),
                            SpecificationName = row.GetCell(2).ToString(),
                            PrintedLOGO = row.GetCell(3).ToString(),
                            PurchasePrice = Convert.ToSingle(row.GetCell(4).ToString()),
                            Limit = Convert.ToInt32(row.GetCell(5).ToString()),
                            Ceiling = Convert.ToInt32(row.GetCell(6).ToString()),
                            FixedLength = row.GetCell(7).ToString(),
                            UnitNo = row.GetCell(8).ToString(),
                            DeputyUnitNoFirst = row.GetCell(9).ToString(),
                            DeputyUnitNoSecond = row.GetCell(10).ToString(),
                            Remark = row.GetCell(11).ToString(),
                            DeputyUnitNoFirstIsActive = unitFirstActive,
                            DeputyUnitNoSecondIsActive = unitSecondActive,
                            CreationTime = DateTime.Now,
                            UserId = AbpSession.UserId.ToString(),
                            IsActive = true,
                            IsDeleted = false
                        };
                        _productSpecificationRepository.InsertAndGetId(product);
                    }
                }

                //读取产品属性sheet
                var sheet2 = wk.GetSheetAt(1);//工作簿
                for (int i = 1; i <= sheet2.LastRowNum; i++)
                {
                    var row = sheet2.GetRow(i);//第i行数据
                    if (row != null)
                    {
                        for (int m = 1; m <= 6; m++)
                        {
                            if (row.GetCell(m) == null)
                            {
                                ICell cell = row.CreateCell(m);
                                cell.SetCellValue("");
                            }
                        }
                        if (row.GetCell(0).ToString() == "" || row.GetCell(1).ToString() == "" || row.GetCell(2).ToString() == "")
                        {
                            errorRow.Add(row);
                            continue;
                        }
                        var detail = new ProductSpecificationDetail
                        {
                            ProductSpecificationNo = row.GetCell(0).ToString(),
                            SpecificationTypeNo = row.GetCell(1).ToString(),
                            Value = row.GetCell(2).ToString(),
                            IsMerge = row.GetCell(3).ToString() == "1" ? true : false,
                            IsAffiliation = row.GetCell(4).ToString() == "1" ? true : false,
                            IsUnit = row.GetCell(5).ToString() == "1" ? true : false,
                            IsBracket = row.GetCell(6).ToString() == "1" ? true : false,
                            IsDeleted = false,
                            IsActive = true,
                            CreationTime = DateTime.Now,
                            UserId = AbpSession.UserId.ToString(),
                        };
                        _productSpecificationDetail.InsertAndGetId(detail);
                        UpdateProductByExcel(row.GetCell(0).ToString());
                    }
                }
                return errorRow;
            }
            catch (Exception e)
            {
                return errorRow;
            }
        }