/// <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); }