Handling merged cells in Excel to Excel conversion

Question

I am using Epplus to render an Excel spreadsheet in HTML. So far this is happening very, very well, with the exception of one thing ... covering the merged cells. I just can't understand the logic. I thought I would drop him there to see how the community handles this. 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 sheet, which lists all the merged cells on the sheet.

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

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