I want to store xml file in excel. For that I converted xml to string and stored it in cell. Since excel cell can't support more than 32767 characters so I splitted the xml on the basis of character count and stored the splitted xml in different cells. but the problem is that at the time of reading xml, an error occurs because the xml is getting corrupted. Here is the code. Any help would be great. I would like to know if any new way can be suggested to do so.
private void SaveXml(XmlDocument xmlDoc)
{
Excel.Workbook WB = Globals.ThisAddIn.Application.ActiveWorkbook;
string strXml = string.Empty;
if (xmlDoc != null)
strXml = xmlDoc.OuterXml;
else
return;
Excel.Worksheet ws = null;
try
{
ws = WB.Sheets["XML"];
}
catch
{
ws = (Excel.Worksheet)WB.Sheets.Add(After: WB.Sheets[WB.Sheets.Count]);
ws.Name = "XML";
WB.Save();
}
int MergeCount = 1;
if (strXml.Length > 32700)
{
while (strXml.Length > 32700)
{
ws.Cells[MergeCount, 1] = strXml.Substring(0, 32699);
strXml = strXml.Substring(32700);
MergeCount++;
}
ws.Cells[MergeCount, 1] = strXml;
}
else
ws.Cells[1, 1] = strXml;
}
private XmlDocument GetXml()
{
Excel.Workbook WB = Globals.ThisAddIn.Application.ActiveWorkbook;
XmlDocument xmlDoc = new XmlDocument();
string strXml = string.Empty;
int XmlColumn = 1;
try
{
Excel.Worksheet ws = WB.Sheets["XML"];
while (ws.Cells[XmlColumn, 1].Value != null)
{
strXml = strXml + ws.Cells[XmlColumn, 1].Value.ToString();
XmlColumn++;
}
xmlDoc.LoadXml(strXml);
}
catch
{
MessageBox.Show("Xml not found.");
}
return xmlDoc;
}