public ExSheet ReadHtmlSheet(String html)
{
_sheet = new ExSheet();
var doc = GetXmlFromHtml(html);
var table = doc.FirstChild;
if (table.Name != "table")
{
throw new InteropException("Invalid element for Html2Excel");
}
var bodyRowNo = 0;
var headerRowNo = 0;
var footerRowNo = 0;
foreach (var n in table.ChildNodes)
{
var nd = n as XmlNode;
if (nd.NodeType != XmlNodeType.Element)
{
continue;
}
switch (nd.Name)
{
case "colgroup":
foreach (var col in nd.ChildNodes.OfType <XmlNode>().Where(node => node.Name == "col"))
{
AddColumn(col);
}
break;
case "tbody":
var bodyClassAttr = nd.Attributes["class"];
if (bodyClassAttr?.Value == "col-shadow")
{
continue; // skip shadows
}
foreach (var row in nd.ChildNodes.OfType <XmlNode>().Where(node => node.Name == "tr"))
{
AddRow(row, RowKind.Body, bodyRowNo++);
}
break;
case "thead":
foreach (var row in nd.ChildNodes.OfType <XmlNode>().Where(node => node.Name == "tr"))
{
AddRow(row, RowKind.Header, headerRowNo++);
}
break;
case "tfoot":
foreach (var row in nd.ChildNodes.OfType <XmlNode>().Where(node => node.Name == "tr"))
{
AddRow(row as XmlNode, RowKind.Footer, footerRowNo++);
}
break;
}
}
return(_sheet);
}
Worksheet GetDataFromSheet(ExSheet sheet)
{
var sd = new SheetData();
var cols = new Columns();
ProcessColums(sheet, cols, null);
Int32 rowNo = 0;
foreach (var row in sheet.Rows)
{
sd.Append(ProcessRow(row, rowNo++));
}
var props = new SheetFormatProperties()
{
BaseColumnWidth = 10,
DefaultRowHeight = 30,
DyDescent = 0.25
};
var ws = new Worksheet(props, cols, sd);
return(ws);
}
void ProcessColums(ExSheet sheet, Columns columns, XmlNode source)
{
for (UInt32 c = 0; c < sheet.Columns.Count; c++)
{
var col = sheet.Columns[(Int32)c];
if (col.Width != 0)
{
var w = ConvertUnit(col.Width);
columns.Append(new Column()
{
Min = c + 1, Max = c + 1, BestFit = true, CustomWidth = true, Width = w
});
}
}
}
public Stream SheetToExcel(ExSheet exsheet)
{
MemoryStream ms = null;
ms = new MemoryStream();
using (var doc = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook, true))
{
WorkbookPart wbPart = doc.AddWorkbookPart();
wbPart.Workbook = new Workbook();
WorksheetPart wsPart = wbPart.AddNewPart <WorksheetPart>();
WorkbookStylesPart workStylePart = wbPart.AddNewPart <WorkbookStylesPart>();
workStylePart.Stylesheet = AddStyles(exsheet.Styles);
workStylePart.Stylesheet.Save();
wsPart.Worksheet = GetDataFromSheet(exsheet);
if (_mergeCells.Count > 0)
{
var mc = new MergeCells();
foreach (var mergeRef in _mergeCells)
{
mc.Append(new MergeCell()
{
Reference = mergeRef
});
}
wsPart.Worksheet.Append(mc);
}
Sheets sheets = doc.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
Sheet sheet = new Sheet()
{
Id = doc.WorkbookPart.GetIdOfPart(wsPart), SheetId = 1, Name = "Sheet1"
};
sheets.Append(sheet);
wbPart.Workbook.Save();
doc.Close();
};
return(ms);
}
