使用 epplus 导出时动态合并行

Question

我想使用 epplus 合并导出的 excel 中的行。下面的代码仅在我在一列中只有一个相同的值时才有效。

例如（我在 Col1 行中合并相同的值）：

但是，如果我有另一个这样的表，则代码在合并时出错（我在 Col1 和 Col2 行中合并相同的值）

请帮我修复代码。

 void mergeCells(DataTable dt, int startIndex, int totalColumns, ExcelWorksheet ws)

    {

            if (totalColumns == 0) return;

            int i, count = 1;

            ArrayList lst = new ArrayList();

            lst.Add(ws.Cells[2, 1]);

            var ctrl = ws.Cells[startIndex + 1, 1];

            for (i = 1; i <= dt.Rows.Count; i++)

            {

                ExcelRange nextMerge = ws.Cells[i + totalColumns + 1, 1];

                if (ctrl.Text == nextMerge.Text)

                {

                    count++;

                    lst.Add(ws.Cells[i, 1]);

                }

                else

                {

                    if (count > 1)

                    {

                        ws.Cells[i + 1, 1, i + count, 1].Merge = true;

                        mergeCells(new DataTable(lst.ToString()), startIndex + count, totalColumns, ws);

                    }

                    count = 1;

                    lst.Clear();

                    ctrl = ws.Cells[i + 2, startIndex];

                    lst.Add(ws.Cells[i, 1]);

                }

            }

            if (count > 1)

            {

                ws.Cells[startIndex + 1, 1, startIndex + count, 1].Merge = true;

                mergeCells(new DataTable(lst.ToString()), startIndex + count, totalColumns - 1, ws);

            }

            count = 1;

            lst.Clear();

        }

Answer 1

我已经这样做了：public void WriteDataToSheet(DataTable data){&nbsp; &nbsp; using (ExcelPackage excel = new ExcelPackage())&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; ExcelWorksheet ws = excel.Workbook.Worksheets.Add("Test");&nbsp; &nbsp; &nbsp; &nbsp; ws.Cells["A1"].LoadFromDataTable(data, true);&nbsp; &nbsp; &nbsp; &nbsp; ws.Cells[ws.Dimension.Address].AutoFitColumns();&nbsp; &nbsp; &nbsp; &nbsp; var listObject = data.AsEnumerable()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .Select(x => new&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Col1 = x.Field<string>("Col1"),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Col2 = x.Field<string>("Col2"),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Col3 = x.Field<string>("Col3")&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }).ToList();&nbsp; &nbsp; &nbsp; &nbsp; var lisa = listObject.GroupBy(x => x.Col1).&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Select(x => new&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Id = x.Key,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Quantity = x.Count(),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; secondGroup = x.GroupBy(y => y.Col2)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.Select(y => new&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;{&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ID = y.Key,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;secondGroup = y.Count()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;})&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; });&nbsp; &nbsp; &nbsp; &nbsp; int A = 1, B = 0, C = 1, D = 0;&nbsp; &nbsp; &nbsp; &nbsp; foreach (var item in lisa)&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B = A + 1;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A += item.Quantity;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ws.Cells["A" + B + ":A" + A + ""].Merge = true;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ws.Cells["B" + B + ":B" + A + ""].Merge = true;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; foreach (var item2 in item.secondGroup)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; D = C + 1;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; C += item2.secondGroup;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ws.Cells["C" + D + ":C" + C + ""].Merge = true;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; // Save merged and modified file to the location&nbsp; &nbsp; }}