Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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;
  }
share|improve this question
4  
Does "don't do that" count as an answer? It might not sound very helpful on the surface, but I really don't think this is a good idea. –  Marc Gravell Aug 13 '13 at 10:01
1  
Is there any particular reason for using Excel and cells? If we knew why... –  Grant Thomas Aug 13 '13 at 10:04
    
Why don't you just save the xml file to disk? –  JP Hellemons Aug 13 '13 at 10:08
    
I can't save the xml on the disk. The xml is generated from the same excel data in other sheets. so I thought it would be better to keep the xml along with the excel so that I don't have to search for xml for a particular excel. –  user2470489 Aug 13 '13 at 11:35

1 Answer 1

Makes me wonder why you want to store an xml file in a cell... seems an odd thing to do... the issue you have about splitting them is it is no longer a valid xml scheme...

I know it's an old link but take a look here for a way to import xml data into a workbook (using xsl)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.