Skip to content

Instantly share code, notes, and snippets.

@PNergard
Last active November 22, 2018 15:40
Show Gist options
  • Save PNergard/867df42299275e358648 to your computer and use it in GitHub Desktop.
Save PNergard/867df42299275e358648 to your computer and use it in GitHub Desktop.
A method that uses EPPlus to parse a excel-file and generate a html table. Link to EPPlus nuget page: https://www.nuget.org/packages/EPPlus/. I don't take any credit for the code since I found it on the web and have only slightly modified it. But it works and I think it's worth spreading.
private string ParseExcelFileAndGenerateHtmlTable(ExcelPackage xlPackage)
{
string html = "";
int workBooks = 0;
using (xlPackage)
{
var workbook = xlPackage.Workbook;
if (workbook != null)
{
if (CurrentBlock.OnlyFirstWorkSheet && workbook.Worksheets.Count > 1)
{
workBooks = 1;
}
else
workBooks = workbook.Worksheets.Count;
for (int j = 1; j <= workBooks; j++)
{
html += "<table class='table' style='border-collapse: collapse;font-family:arial;'>";
var worksheet = workbook.Worksheets[j];
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++;
if (rowCount == 1) html += String.Format("<thead>");
if (rowCount == 2) html += String.Format("<tbody>");
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();
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
if (rowCount == 1)
html += String.Format("<th colspan={0} rowspan={1}>{2}</th>", colSpan, rowSpan, currentCell.Value);
else
html += String.Format("<td colspan={0} rowspan={1}>{2}</td>", colSpan, rowSpan, currentCell.Value);
}
html += String.Format("</tr>");
if (rowCount == 1) html += String.Format("</thead>");
});
html += String.Format("</tbody></table>");
}//worksheet loop
}
}
return html;
}
@pandeharsh
Copy link

Hi

I want to create html table in same style as excel file.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment