Handle merged cells in Epplus Excel conversion to HTML

Question

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;
}

Answer 1

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);