/// <summary>This parses a string into an excel-sheets-instance</summary>
/// <param name="input"></param>
/// <returns></returns>
public static Sheets ParseExcelXML(string input)
{
Sheets output = new Sheets();
int sheetCounter = 0;
// Load string into XML object
XmlDocument doc = new XmlDocument();
doc.LoadXml(input);
// Get sheets that might exist
XmlNodeList possibleSheets = doc["Workbook"].ChildNodes;
foreach (XmlNode aPossibleSheet in possibleSheets)
{
// Only look at so called "work sheets" = sheets
if (aPossibleSheet.Name.ToLower().Equals("worksheet"))
{
// This sheet has a name
string sheetName = sheetCounter.ToString();
if (aPossibleSheet.Attributes["ss:Name"] != null)
{
sheetName = aPossibleSheet.Attributes["ss:Name"].Value;
}
sheetCounter++;
// Create a table
Table <string> aTable = new Table <string>();
// Go through the table
XmlNodeList rows = aPossibleSheet["Table"].ChildNodes;
List <string> columnTitles = new List <string>();
bool firstRow = true;
foreach (XmlNode aRow in rows)
{
// Only look at real rows
if (aRow.Name.ToLower().Equals("row"))
{
//Debugger.addMessage(Debugger.LEVEL_LOWEST, "New row");
int columnCounter = 0;
bool firstColumn = true;
string rowName = "";
// and go through all the columns
foreach (XmlNode aCell in aRow.ChildNodes)
{
// If this is the first element, check, if everything is correct or we might need to skip this row
if (firstColumn && !firstRow && aCell.Name.ToLower().Equals("cell"))
{
// Find out, if we need to skip this row and skip it...
if (aCell["Data"] == null || aCell["Data"].InnerText.Trim().Equals("") || ExistsInArray(aTable.RowTitles, aCell["Data"].InnerText))
{
break;
}
// Usually, set title
rowName = aCell["Data"].InnerText;
}
// Only look at real cells:
if (aCell.Name.ToLower().Equals("cell"))
{
//Debugger.addMessage(Debugger.LEVEL_LOWEST, "Content: " + rowName + ": '" + aCell.InnerText + "'\nCurrent column: " + columnCounter.ToString() + "\t" + "firstColumn: " + firstColumn.ToString() + "\t" + "firstRow: " + firstRow.ToString() + "\t" + "columnTitles.Count: " + columnTitles.Count.ToString());
// Get current content, if possible
XmlNode currentContent = aCell["Data"];
// If there is no valid content in the "Data"-Tag, perhaps there is an "ss:Data"-tag?
if (currentContent == null)
{
currentContent = aCell["ss:Data"];
}
// in first column...
if (!firstColumn)
{
// Only save content that have a heading
if (firstRow || columnCounter < columnTitles.Count)
{
// For all other columns
// In first row...
if (firstRow)
{
// Add this one to our column list, but only if this is a valid content
if (currentContent != null)
{
columnTitles.Add(currentContent.InnerText);
}
}
else
{
if (aCell.Attributes["ss:Index"] != null)
{
int desiredPos = int.Parse(aCell.Attributes["ss:Index"].Value.Trim()) - 2;
if (desiredPos < columnTitles.Count)
{
columnCounter = desiredPos;
}
}
// Don't save a cell that has no content
if (currentContent != null)
{
//Debugger.addMessage(Debugger.LEVEL_LOWEST, "Saving: " + rowName + "/" + columnTitles.ToArray()[columnCounter] + ": '" + currentContent.InnerText + "'");
// In all usual rows...
// We know the row name (rowName) and the column name is from the columnTitles-array the position at which we are now
aTable.SetValue(rowName, columnTitles.ToArray()[columnCounter], currentContent.InnerText);
}
}
columnCounter++;
}
}
firstColumn = false;
}
// Set first column end, if we were in it at the top
if (firstColumn && !firstRow && aCell.Name.ToLower().Equals("cell"))
{
firstColumn = false;
}
}
// Now, the first row must be finished
firstRow = false;
}
}
// Now, we have the table for this sheet. Add it.
if (!output.ContainsKey(sheetName))
{
output.Add(sheetName, aTable);
}
}
}
return(output);
}
