/// <summary>
/// insert the data into an Xml Node. This is useful for later converting the Xml to meaningful CSV with the same columns for Excel or Calc
/// </summary>
/// <param name="AJsonData"></param>
/// <param name="ANode"></param>
/// <param name="ADoc"></param>
/// <param name="AOverwrite"></param>
public static void DataToXml(string AJsonData, ref XmlNode ANode, XmlDocument ADoc, bool AOverwrite)
{
if (AJsonData.Length == 0)
{
return;
}
try
{
string RequiredCulture = CultureInfo.CurrentCulture.Name;
AJsonData = RemoveContainerControls(AJsonData, ref RequiredCulture);
JsonObject list = (JsonObject)JsonConvert.Import(AJsonData);
foreach (string key in list.Names)
{
if (AOverwrite || !TXMLParser.HasAttribute(ANode, key))
{
XmlAttribute attr = ADoc.CreateAttribute(StringHelper.UpperCamelCase(key));
string text = list[key].ToString().Replace("<br/>", "_");
attr.Value = text;
ANode.Attributes.Append(attr);
}
}
}
catch (Exception)
{
TLogging.Log("Problem parsing: " + AJsonData);
throw;
}
}
/// <summary>
/// find a node somewhere in the xml document by its tag name and attribute name
/// </summary>
/// <param name="AParentNode"></param>
/// <param name="ANodeNameToSearch"></param>
/// <param name="ANameAttribute"></param>
/// <returns></returns>
public static XmlNode FindNodeRecursive(XmlNode AParentNode, string ANodeNameToSearch, string ANameAttribute)
{
for (Int32 counter = 0; counter < AParentNode.ChildNodes.Count; counter++)
{
XmlNode ChildNode = AParentNode.ChildNodes[counter];
if (ChildNode.Name == ANodeNameToSearch)
{
if (ANameAttribute.Length > 0)
{
if (TXMLParser.HasAttribute(ChildNode, "name") && (TXMLParser.GetAttribute(ChildNode, "name") == ANameAttribute))
{
return(ChildNode);
}
}
else
{
return(ChildNode);
}
}
}
XmlNode ResultNode = GetChild(AParentNode, ANodeNameToSearch);
if (ResultNode == null)
{
foreach (XmlNode childNode in AParentNode.ChildNodes)
{
ResultNode = FindNodeRecursive(childNode, ANodeNameToSearch, ANameAttribute);
if (ResultNode != null)
{
return(ResultNode);
}
}
}
if (ANameAttribute.Length > 0)
{
return(null);
}
return(ResultNode);
}
/// <summary>
/// Load an OpenDocument .ods file into a datatable
/// </summary>
public static DataTable ParseODSStream2DataTable(MemoryStream AStream,
bool AHasHeader = false,
int AWorksheetID = 0,
List <string> AColumnsToImport = null)
{
XmlDocument doc = GetContent(AStream);
XmlNode OfficeBody = TXMLParser.GetChild(doc.DocumentElement, "office:body");
XmlNode OfficeDocument = TXMLParser.GetChild(OfficeBody, "office:spreadsheet");
int countWorksheets = 0;
XmlNode worksheet = null;
foreach (XmlNode worksheetLoop in OfficeDocument.ChildNodes)
{
if (worksheetLoop.Name != "table:table")
{
continue;
}
if (countWorksheets == AWorksheetID)
{
worksheet = worksheetLoop;
}
countWorksheets++;
}
DataTable result = new DataTable();
if (worksheet == null)
{
return(result);
}
List <string> ColumnNames = new List <string>();
bool firstRow = true;
foreach (XmlNode rowNode in worksheet.ChildNodes)
{
if (rowNode.Name != "table:table-row")
{
continue;
}
// create columns
if (firstRow)
{
int columnCounter = 0;
foreach (XmlNode cellNode in rowNode.ChildNodes)
{
if (cellNode.Name != "table:table-cell")
{
continue;
}
if (TXMLParser.HasAttribute(cellNode, "table:number-columns-repeated"))
{
// just ignore duplicate columns in the header line, the values must be unique anyway
continue;
}
string ColumnName = ((AHasHeader && cellNode.FirstChild != null) ? cellNode.FirstChild.InnerText : string.Format("Column {0}", columnCounter));
ColumnNames.Add(ColumnName);
columnCounter++;
if ((AColumnsToImport != null) && !AColumnsToImport.Contains(ColumnName))
{
continue;
}
result.Columns.Add(ColumnName);
}
}
// import row
if (!firstRow || !AHasHeader)
{
DataRow NewRow = result.NewRow();
int columnCounter = 0;
foreach (XmlNode cellNode in rowNode.ChildNodes)
{
if (cellNode.Name != "table:table-cell")
{
continue;
}
Int32 NumberColumnsRepeated = 1;
// handle columns with same value
if (TXMLParser.HasAttribute(cellNode, "table:number-columns-repeated"))
{
NumberColumnsRepeated = Convert.ToInt32(TXMLParser.GetAttribute(cellNode, "table:number-columns-repeated"));
if (!TXMLParser.HasAttribute(cellNode, "office:value-type"))
{
// skip empty columns
columnCounter += NumberColumnsRepeated;
continue;
}
}
while (NumberColumnsRepeated > 0)
{
string CellType = TXMLParser.GetAttribute(cellNode, "office:value-type");
if ((AColumnsToImport != null) && !AColumnsToImport.Contains(ColumnNames[columnCounter]))
{
// skip this column
}
else if (CellType == "float")
{
TVariant variant = new TVariant(TXMLParser.GetAttribute(cellNode, "office:value"));
NewRow[ColumnNames[columnCounter]] = variant.ToObject();
}
else if (CellType == "date")
{
NewRow[ColumnNames[columnCounter]] =
new TVariant(TXMLParser.GetAttribute(cellNode, "office:date-value")).EncodeToString();
}
else if (CellType == "boolean")
{
NewRow[ColumnNames[columnCounter]] =
(TXMLParser.GetAttribute(cellNode, "office:boolean-value") == "true");
}
else if (CellType == "string")
{
NewRow[ColumnNames[columnCounter]] = cellNode.FirstChild.InnerText;
}
columnCounter++;
NumberColumnsRepeated--;
}
}
result.Rows.Add(NewRow);
}
firstRow = false;
}
return(result);
}