Tech Off Thread

4 posts

Excel 2003 and save as XML Spreadsheet

Back to Forum: Tech Off
  • User profile image
    quantass0

    We have excel 2003 and are trying to save out a "xml spreadsheet".  The mess of a file is created (sorry but why is it so diffiuclt for MS to save out clean proper tags like everyone else) but i notice rows which are completely blank or contain blank fields will have the particular XML tag completely removed.  The problem with this is Im expecting 7 columns when sometimes the column can be 5 or 3 or 7.  Is there any way to force excel to NOT remove blank fields from the XML file this way everyone contains the 7 tags to correspond with the 7 column fields in the excel file.

    Thanks

  • User profile image
    odujosh

    quantass0 wrote:
    We have excel 2003 and are trying to save out a "xml spreadsheet".  The mess of a file is created (sorry but why is it so diffiuclt for MS to save out clean proper tags like everyone else) but i notice rows which are completely blank or contain blank fields will have the particular XML tag completely removed.  The problem with this is Im expecting 7 columns when sometimes the column can be 5 or 3 or 7.  Is there any way to force excel to NOT remove blank fields from the XML file this way everyone contains the 7 tags to correspond with the 7 column fields in the excel file.

    Thanks


    Not that I am aware of.

    I would go off some heading naming semantic. This way you can see if the mapping you need for what you need in order to do some data exporting.

  • User profile image
    erik_

    A different direction, but you could create a small application to read out the excel file and generate the xml file in your own format. Not that hard to do with ado.net. 

    But I don't know your requirements, this would make no sence if the end user needs to use this application a few times a day, etc.

    ADO.NET read excel
    http://davidhayden.com/blog/dave/archive/2006/05/26/2973.aspx

    Fill it to a datatable and use DataTable.WriteXml, maby that already gives an acceptable result.

  • User profile image
    JChung2006

    I suspect the reason that you are not seeing blank rows and columns in the XML is that it would be inefficient to store those in memory.  Read more about it in Wikipedia's sparse array entry.

Comments closed

Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums, or Contact Us and let us know.