• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1783
  • Last Modified:

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

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
      MyDataAdapter.Fill(MyDataTable)
      MyDataTable.TableName = "test"
      Dim MyFileStream As New FileStream(sTempXMLFilename, FileMode.Create)
      MyDataTable.WriteXml(MyFileStream)
      MyFileStream.Close()
      MyFileStream.Dispose()
      MyDataTable.Clear()
      MyDataTable.Dispose()
      MyDataAdapter.Dispose()

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?
0
koossa
Asked:
koossa
  • 6
  • 4
1 Solution
 
Pratima PharandeCommented:
refer the below linke

http://www.codeproject.com/Articles/371203/Creating-basic-Excel-workbook-with-Open-XML

proc=vides the details of frmatting in excel.
Hopw it helps you
0
 
koossaAuthor Commented:
Thank you, but they're doing it in a different way, I'm using a DataTable and cannot change that?
0
 
CodeCruiserCommented:
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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
koossaAuthor Commented:
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?
0
 
CodeCruiserCommented:
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.
0
 
koossaAuthor Commented:
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:


'00000021
'0000003
'0000004
'0000005
'0000006
'0000007
'0000008
'0000009
'0000010
'0000011

So the single quote is visible in excel? (See attachment)
singlequote.PNG
0
 
CodeCruiserCommented:
It would be. What happens if you use = instead of '?
0
 
koossaAuthor Commented:
No, not working.
MyFile
0
 
CodeCruiserCommented:
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.
0
 
koossaAuthor Commented:
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?
0
 
koossaAuthor Commented:
Thank you!
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now