Solved Open XML File (DataTable.WriteXml) in Excel with correct format.

Posted on 2012-09-17
Last Modified: 2012-09-20
Good day

I have a DataTable, and I'm using the WriteXml method to write it as a XML file.
      Dim MyDataAdapter As New SqlDataAdapter(m_sExportQry, m_db)
      Dim MyDataTable As New DataTable
      MyDataAdapter.SelectCommand.CommandTimeout = 0
      MyDataTable.TableName = "test"
      Dim MyFileStream As New FileStream(sTempXMLFilename, FileMode.Create)

Open in new window

Then I open the file with excel as follows
      oApp = CreateObject("Excel.Application")
      oApp.UserControl = True
      oApp.Visible = True
      oApp.DisplayAlerts = False
      oBook = oApp.Workbooks.OpenXML(sTempXMLFilename, , 2)

Open in new window

My first field is a Serial number, for example 0000101, but in excel it shows as 101, how do I keep the format as text?
Question by:koossa
    LVL 39

    Expert Comment

    by:Pratima Pharande
    refer the below linke

    proc=vides the details of frmatting in excel.
    Hopw it helps you

    Author Comment

    Thank you, but they're doing it in a different way, I'm using a DataTable and cannot change that?
    LVL 83

    Expert Comment

    What is type of that column in datatable? String? You may have to append either ' or = at the start of all values in that column before exporting to force Excel to treat it as string.

    Author Comment

    Yes, it is a string.
    I know about the single quote to add before a number, but in this case it is not that easy.
    I'm using a query to setup the DataTable and write the datatable to XML.
    I cannot find a way to add a single quote before the field using the query?
    LVL 83

    Accepted Solution

    You have two options.

    1) Modify your query

     Select ''' + columnname As columnname, ...

    2) Loop through datatable and update value of this column before writing to xml.

    Author Comment

    Hi CodeCruiser

    Thank you, option 2 is not an option because I need speed!
    I have tried option 1 before, but it said :
    "Unclosed quotation mark after the character string ..."

    I think I found a solution to put a single quote in front of the column:
    "Select CHAR(39) + Column as columname from ......"

    There is then a single quote in front of this column, but when I open the XML in Excel, then it looks like this:


    So the single quote is visible in excel? (See attachment)
    LVL 83

    Expert Comment

    It would be. What happens if you use = instead of '?

    Author Comment

    No, not working.
    LVL 83

    Expert Comment

    I was hoping that = will be evaluated like a formula and will not be displayed but it is. I think the character will be displayed.

    Author Comment

    Isn't there a way that I can include the field data type in the XML that Excel will know what data type it is?

    Author Closing Comment

    Thank you!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
    Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now