xls不规则表合并单元格读取

最新推荐文章于 2023-12-25 11:25:12 发布

威威当爸了

最新推荐文章于 2023-12-25 11:25:12 发布

阅读量211

点赞数

CC 4.0 BY-SA版权

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

本文链接：https://blog.csdn.net/lw8014/article/details/120455292

C# 专栏收录该内容

22 篇文章

订阅专栏

本文介绍了一种使用OfficeOpenXml库在C#中解析Excel文件的方法，包括定位特定工作表、查找特定列并读取内容，同时处理了单元格合并的情况，并实现了数据去重。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

            ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;
            using (ExcelPackage package = new ExcelPackage(new FileInfo(xlsPath)))
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets["Result"];//读固定的表名字
                if (worksheet == null)
                {
                    err = ("档案中没有Result的工作本(Sheet),请检查.");
                    return false;
                }
                //遍历
                var start = worksheet.Dimension.Start;
                var end = worksheet.Dimension.End;
                //确定列
                for (int row = 3; row <= 5; row++)
                {
                    for (int col = 1; col <= end.Column; col++)
                    {
                        string cellValue = worksheet.Cells[row, col].Text;
                        if (cellValue.Contains("Finished Good "))  //自定义要读取的列
                        {
                            ExcelColumnIndex["FG"] = col;
                        }
                        if (cellValue.Contains("Project Name"))
                        {
                            ExcelColumnIndex["MT"] = col;
                        }
                        if (cellValue.Contains("Test Program DOC"))
                        {
                            ExcelColumnIndex["SOFT"] = col;
                        }
                        if (cellValue.Contains("Vendor MAC"))
                        {
                            ExcelColumnIndex["MACSTART"] = col;
                        }
                        if (cellValue.Contains("使用MAC个数"))
                        {
                            ExcelColumnIndex["MACUSECOUNT"] = col;
                        }
                    }
                }

                List<string> DistinctPnMt = new List<string>();
                for (int row = 7; row < end.Row; row++)
                {//这里都是读取内容
                    string cellValue = worksheet.Cells[row, ExcelColumnIndex["FG"]].Text;
                    if (!string.IsNullOrEmpty(cellValue))
                    {
                        string macu = GetMegerValue(worksheet, row, ExcelColumnIndex["MACUSECOUNT"]).Trim();
                        if (!int.TryParse(macu, out int macUse))
                        {
                            macUse = 0;
                        }
                        string tempMt = worksheet.Cells[row, ExcelColumnIndex["MT"]].Text.Trim();
                        if (!Regex.IsMatch(tempMt, @" MT:[0-9]{1,10}"))
                        { continue; }
                        //有内容
                        MatchCollection tt = Regex.Matches(tempMt, @" MT:[0-9]{1,10}");
                        List<string> strList = new List<string>();
                        foreach (Match item in tt)
                        {
                            strList.Add(item.Value.Replace(":", ""));
                        }
                        strList = strList.Distinct().ToList();


                        if (strList.Count > 1)
                        {
                            continue;
                        }
                        Devolo_Pn_Maintain pn = new Devolo_Pn_Maintain()
                        {
                            Pn = cellValue,
                            MT = strList[0],
                            MacOui = GetMegerValue(worksheet, row, ExcelColumnIndex["MACSTART"]).Replace("-", "").Replace(":", "").Trim(),
                            MacUseQuantity = macUse,
                            ProgramDOC = GetMegerValue(worksheet, row, ExcelColumnIndex["SOFT"]).Trim(),
                        };

                        Devolo_Pn_Maintain tempDis = pn_List.FirstOrDefault(o => o.Pn.Equals(pn.Pn) && o.MT.Equals(pn.MT));

                        if (tempDis == null)
                        {
                            pn_List.Add(pn);
                        }
                        else
                        {
                            pn_List.Remove(tempDis);
                            DistinctPnMt.Add(tempDis.Pn + "," + tempDis.MT);
                        }
                    }
                }
                if (DistinctPnMt.Count > 0)
                {
                    err = "xls中有重复数据" + string.Join("\r\n", DistinctPnMt);
                    return false;
                }
            }

        /// <summary>
        /// 读取有合并单元格的内容,普通的单元格也可以读
        /// </summary>
        /// <param name="wSheet"></param>
        /// <param name="row"></param>
        /// <param name="column"></param>
        /// <returns></returns>
        public static string GetMegerValue(ExcelWorksheet wSheet, int row, int column)
        {
            string range = wSheet.MergedCells[row, column];
            if (range == null)
                if (wSheet.Cells[row, column].Value != null)
                    return wSheet.Cells[row, column].Value.ToString();
                else
                    return "";
            object value =
                wSheet.Cells[(new ExcelAddress(range)).Start.Row, (new ExcelAddress(range)).Start.Column].Value;
            if (value != null)
                return value.ToString();
            else
                return "";
        }

        Dictionary<string, int> ExcelColumnIndex = new Dictionary<string, int>()
        {
            {"FG",0 },
            {"MT",0 },
            {"SOFT",0 },
            {"MACSTART",0 },
            {"MACUSECOUNT",0 },
        };