www.webdeveloper.com
Results 1 to 4 of 4

Thread: Excel export to XML

  1. #1
    Join Date
    Feb 2005
    Posts
    3

    Excel export to XML

    Hi

    Im having problems with dates when exporting an excel spreadsheet as an XML file. I have applied an XML map to the spreadsheet in which the users enter data. They then save the spreadsheet as an xml document. Everything exports fine apart from the dates which are appearing as the XLS serial date format (a number). If I format all date fields as text then the data exports fine. The prob Im having is that the users are copying these dates from another spreadsheet where the fields are formatted as dates. When they copy into my spreadsheet it is defaulting my text field back to a date field and thus the dates are saving again as the serial date format. Is there a way tp get the XML file to save whats in the field as when I save as a CSV that date appears fine even though it is formatted as a date field.

  2. #2
    Join Date
    Nov 2002
    Location
    Baltimore, Maryland
    Posts
    12,279
    Welcome to the wonder that is Excel. I think that the only way to solve this is to use a different method of exporting the data. I would run a little JScript or VB script using the Windows Scripting Host. Or if you or your users are freaked out by the command line then I would write a little HTA that reads the Excel file and generates the XML.
    “The power of the Web is in its universality. Access by everyone regardless of disability is an essential aspect.”
    —Tim Berners-Lee, W3C Director and inventor of the World Wide Web

  3. #3
    Join Date
    Jan 2005
    Location
    Los Angeles, CA
    Posts
    4,887
    Excel probably has some settings somewhere for this type of thing which can control the data formats of the export. I don't play around with Excel exports much. Here are some workarounds: (Note that I haven't tested any of these)


    Perhaps:
    Export it as a CSV initially so the export contains the right data (since you said exporting as a CSV doesn't corrupt the format of the cells).
    Then import it and export it again but this time change the file type to XML.

    Another method:
    Export the orginal as an XML and a CSV.
    Reopen both in seperate sheets, and pick and choose to merge the two as you see fit to a 3rd sheet and finally export as XML.

    Or:
    On the original, manually change the fields from a date format to a text format before exporting.

    Or:
    Edit the exported XML file manually and change the fields to your liking from notepad and resave.

    Or:
    Don't trust the user's input, and have them copy-paste to a different sheet where they can paste in date format.
    Then have another row reference it but this time convert it to the text format.

    Or:
    Write a note on the excel page adjacent to the cells telling the user to make sure the cells are kept in the text format "@".

    Or:
    Write a new VBA module using the OnEntry event handler:
    http://support.microsoft.com/kb/125810
    http://www.mrexcel.com/articles/macr...ll-changes.php
    Code:
    Sub Auto_Open()
              Worksheets("Sheet1").OnEntry = "Test"
    End Sub
    
    Sub Test()
              MsgBox "A key was pressed."
    End Sub
    That way the subroutine "Test" would be called any time a field in the entire sheet changes.
    Then instead of showing a dialog, for the subroutine, apply a function to make sure a cell range is the proper type:
    Untested:
    Code:
    Sub Auto_Open()
              Worksheets("Sheet1").OnEntry = "EntryCallBack"
    End Sub
    
    Sub EntryCallBack()
    	Worksheets("Sheet1").Range("B5:B12").Select
    	Selection.NumberFormat = "@"
    	'Sets the format to Text for the given range.
    End Sub

    A few links that might come in handy:
    http://www.accessmonster.com/Uwe/For...xport-to-Excel
    http://www.excelforum.com/excel-prog...rt-to-csv.html
    http://jira.atlassian.com/browse/JRA-11360


    You could also go with Charles' idea and setup a .WSH, .VBS, .JS or .HTA file to open the excel file
    by creating an "Excel.Application" object.
    http://msdn.microsoft.com/en-US/libr...=VS.80%29.aspx
    Make some edits to the page programmatically then SaveAsXMLData().
    Last edited by Ultimater; 01-13-2011 at 05:26 AM.

  4. #4
    Join Date
    Nov 2002
    Location
    Baltimore, Maryland
    Posts
    12,279
    Quote Originally Posted by Ultimater View Post
    Make some edits to the page programmatically then SaveAsXMLData().
    Actually, my idea is to have the script pull the information from Excel, build the XML tree with the MSXML parser and then save. The idea is to get Excel out of the equation as much as possible.

    Just one caveat. You can read an Excel file with ActiveX and with the Jet engine. The Active X method is way powerful, you can make SQL calls to your sheet, but with Excel it drops data in certain situations. For this use the ActiveX method.
    Code:
    excel = new ActiveXObject ('Excel.Application');
    book = excel.workbooks.open (fileName);
    sheet = book.worksheets (1);
    headings = [];
    for (i = 1; i <= sheet.usedRange.columns.count; i++) headings.push (sheet.cells (1, i));
    data = array[];
    for (j = 2; j <= sheet.usedRange.rows.count; j++) {
        a = [];
        for (i = 1; i <= sheet.usedRange.columns.count; i++) headings.push (sheet.cells (j, i));
        data.push (a);
    }
    “The power of the Web is in its universality. Access by everyone regardless of disability is an essential aspect.”
    —Tim Berners-Lee, W3C Director and inventor of the World Wide Web

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles