/// <summary>
/// Recurses through the XML hiearchy, writing the contents to Excel.
/// </summary>
//"details" could be passed by value but "ref" saves memory.
private static RecurseDetails travelXML(ref RecurseDetails details, XmlNode cat, int level = 0)
{
int currApttuscount = details.apttusRow + 1;
int currExcelRow;
//string ID, name;
int left = currApttuscount;
int i = 1;
currExcelRow = ++details.excelRow;
currApttuscount = ++details.apttusRow;
foreach (XmlNode node in cat["values"].ChildNodes)
{
details.ws.Cells[currExcelRow, i++].Value = node.InnerText;
}
details.ws.Cells[currExcelRow, i++].Value = currApttuscount; // left
if (cat["nest"] != null)
{
foreach (XmlNode nestedCat in cat["nest"].ChildNodes)
{
details = travelXML(ref details, nestedCat, level + 1);
}
}
details.ws.Cells[currExcelRow, i++].Value = ++details.apttusRow; // right
details.ws.Cells[currExcelRow, i++].Value = level.ToString(); // level
return(details);
}
/// <summary>
/// Reads the sourceXML and transforms it to Excel.
/// </summary>
public static bool transformXMLtoExcel(string sourceXML, string outName)
{
bool runResult = false;
XmlDocument xmlFile = new XmlDocument();
XmlNode doc = new XmlDocument();
List <headerCell> header;
xmlFile.Load(sourceXML);
doc = xmlFile.DocumentElement;
header = getHeader(doc);
//Creates a blank workbook. Use the using statment, so the package is disposed when we are done.
using (ExcelPackage p = new ExcelPackage())
{
//A workbook must have at least on sheet, so lets add one...
ExcelWorksheet ws = p.Workbook.Worksheets.Add("Sheet1");
//Create the header
foreach (headerCell cell in header)
{
ws.Cells[1, cell.col].Value = cell.text;
}
RecurseDetails details = new RecurseDetails(ws, header);
//recurse code here
details = travelXML(ref details, doc["cat"]);
//Save the new workbook. We haven't specified the filename so use the Save as method.
p.SaveAs(new FileInfo(outName));
}
return(runResult);
}