处理Epplus Excel转换为HTML中的合并单元格 [英] Handle merged cells in Epplus Excel conversion to HTML

问题描述

我正在使用Epplus将Excel电子表格呈现为HTML。到目前为止，进展非常非常顺利，除了一件事...跨越合并的单元格。我似乎无法理解正确的逻辑。我以为我会把它扔在那里，看看社区将如何处理它。到目前为止，这是我的代码。

I'm using Epplus to render an Excel spreadsheet into HTML. So far it's going very, very well, except for one thing... spanning merged cells. I just can't seem to get the logic right. I thought I would throw it out there to see how the community would deal with it. Here is my code so far.

public String ParseExcelStamps(String FileName)
{
    FileInfo theFile = new FileInfo(FileName);
    String html = "";
    using (ExcelPackage xlPackage = new ExcelPackage(theFile))
    {
        var workbook = xlPackage.Workbook;
        if (workbook != null)
        {
            for (int j = 1; j <= workbook.Worksheets.Count; j++)
            {
                Tab tab = new Tab();
                html+= "<table style='border-collapse: collapse;font-family:arial;'><tbody>";
                var worksheet = workbook.Worksheets[j];
                tab.Title = worksheet.Name;
                if (worksheet.Dimension == null) { continue; }
                int rowCount = 0;
                int maxColumnNumber = worksheet.Dimension.End.Column;
                var convertedRecords = new List<List<string>>(worksheet.Dimension.End.Row);
                var excelRows = worksheet.Cells.GroupBy(c => c.Start.Row).ToList();
                excelRows.ForEach(r =>
                {
                    rowCount++;
                    html += String.Format("<tr>");
                    var currentRecord = new List<string>(maxColumnNumber);
                    var cells = r.OrderBy(cell => cell.Start.Column).ToList();
                    Double rowHeight = worksheet.Row(rowCount).Height;
                    for (int i = 1; i <= maxColumnNumber; i++)
                    {
                        var currentCell = cells.Where(c => c.Start.Column == i).FirstOrDefault();

                        //look aheads for colspan and rowspan
                        ExcelRangeBase previousCellAbove = null;
                        ExcelRangeBase previousCell = null;
                        ExcelRangeBase nextCell = null;
                        ExcelRangeBase nextCellBelow = null;
                        try { previousCellAbove = worksheet.Cells[rowCount-1, i]; }catch (Exception) { }
                        try { previousCell = worksheet.Cells[rowCount, (i - 1)]; }catch (Exception) { }
                        try { nextCell = worksheet.Cells[rowCount, (i + 1)]; }catch (Exception) { }
                        try { nextCellBelow = worksheet.Cells[rowCount+1, i]; }catch (Exception) { }

                        if ((previousCell != null) && (previousCell.Merge) && (currentCell != null) && (currentCell.Merge)){continue;}
                        if ((previousCellAbove != null) && (previousCellAbove.Merge) && (currentCell != null)) {continue; }

                        if (currentCell == null)
                        {
                            html += String.Format("<td>{0}</td>", String.Empty);
                        }
                        else
                        {
                            int colSpan = 1;
                            int rowSpan = 1;
                            if ((nextCell != null) && (nextCell.Merge) && (currentCell.Merge)) {
                                colSpan = 2;
                               // Console.WriteLine(String.Format("{0} - {1}", currentCell.Address, nextCell.Address));
                            }

                            if ((nextCellBelow != null) && (nextCellBelow.Merge) && (currentCell.Merge)) {
                                Console.WriteLine(String.Format("{0} - {1}", currentCell.Address, nextCellBelow.Address));
                            }

                            html += String.Format("<td colspan={0} rowspan={1}>{2}</td>", colSpan, rowSpan, currentCell.Value);
                        }
                    }
                    html += String.Format("</tr>");
                });
                html += "</tbody></table>";
            }//worksheet loop
        }
    }
    return html;
}

推荐答案

据我所知这正是您所需要的。您所缺少的是工作表上的 MergedCells 属性，该属性列出了工作表中所有合并的单元格。

As far as I can tell this is exactly what you need. What you were missing was the MergedCells property on the worksheet which lists all merged cells in the sheet.

我的代码同时处理行跨度，列跨度和两者。我对包含行，列和行/列跨度的电子表格进行了一些测试。在所有情况下，它们都能正常工作。

My code handles row spans, column spans, and both at the same time. I did some testing with a spreadsheet that included both row, column and row/column spanning. In all cases they worked perfectly.

代码

Code

int colSpan = 1;
int rowSpan = 1;

//check if this is the start of a merged cell
ExcelAddress cellAddress = new ExcelAddress(currentCell.Address);

var mCellsResult = (from c in worksheet.MergedCells 
                let addr = new ExcelAddress(c)
                    where cellAddress.Start.Row >= addr.Start.Row &&
                    cellAddress.End.Row <= addr.End.Row &&
                    cellAddress.Start.Column >= addr.Start.Column &&
                    cellAddress.End.Column <= addr.End.Column 
                select addr);

if (mCellsResult.Count() >0)
{
    var mCells = mCellsResult.First();

    //if the cell and the merged cell do not share a common start address then skip this cell as it's already been covered by a previous item
    if (mCells.Start.Address != cellAddress.Start.Address)
        continue;

    if(mCells.Start.Column != mCells.End.Column) {
        colSpan += mCells.End.Column - mCells.Start.Column;
    }

    if (mCells.Start.Row != mCells.End.Row)
    {
        rowSpan += mCells.End.Row - mCells.Start.Row;
    }
}

//load up data
html += String.Format("<td colspan={0} rowspan={1}>{2}</td>", colSpan, rowSpan, currentCell.Value);

这篇关于处理Epplus Excel转换为HTML中的合并单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！