8 Replies - 3154 Views - Last Post: 11 May 2016 - 04:59 AM

#1 andrewsw  Icon User is offline

  • blow up my boots
  • member icon

Reputation: 6447
  • View blog
  • Posts: 26,076
  • Joined: 12-December 12

DataTable to Excel via XML

Posted 05 May 2016 - 01:59 PM

To get the data from a DataTable to Excel it is common to iterate all the rows and columns (of the DT) writing to individual Excel cells. A simpler alternative is to write the DataTable-data to an XML file then open this file in Excel, particularly when most of the time this is only to provide a rough-and-ready snapshot that the user can explore at their leisure.

(Other alternatives are to automate Excel to read the original source data, or to just have a linked workbook that reads from the source and open this file. Writing a CSV file is another route, as Excel can open such files.)

A snippet, by implication, should be reasonably short so, to that end, it boils down to this code:
        _dt.WriteXml(_tempFile)

        Dim xl As New Excel.Application
        Dim wb = xl.Workbooks.OpenXML(Filename:=_tempFile, LoadOption:=Excel.XlXmlLoadOption.xlXmlLoadImportToList)
        xl.Visible = True

Knowing how to write a temporary file (to the user's temporary folder) is also a good idea and, of course, we want to release the Excel resources, but leave the file open. So snippet version two is:
    Private Sub btnTransfer_Click(sender As Object, e As EventArgs) Handles btnTransfer.Click
        _tempFile = Path.Combine(Path.GetTempPath, String.Concat("tempxml ", DateTime.Now.ToLongDateString, ".xml"))
        _dt.WriteXml(_tempFile)

        Dim xl As New Excel.Application
        Dim wb = xl.Workbooks.OpenXML(Filename:=_tempFile, LoadOption:=Excel.XlXmlLoadOption.xlXmlLoadImportToList)
        xl.Visible = True

        ReleaseObject(wb)
        ReleaseObject(xl)
    End Sub

(The ReleaseObject code is presented further down.)

If we have a temporary file we may want to delete it later. I'm using the FormClosing event to do this, but if the user might want to keep the file open, then you could just not bother to delete it; it's in the temporary folder anyway, so should disappear at some future point.
    Private Sub frmTableToXML_FormClosing(sender As Object, e As FormClosingEventArgs) Handles MyBase.FormClosing
        If Not String.IsNullOrEmpty(_tempFile) Then
            File.Delete(_tempFile)
        End If
    End Sub



Here's a full example if you want to explore. One thing in particular to point out is this line:
        _dt.Columns("DOB").DateTimeMode = DataSetDateTime.Unspecified 'ignore time

Without this the dates include a timezone/offset element ("1980-04-20T00:00:00+01:00") and are treated as text by Excel.

Attached Image

Spoiler

Attached Image

Is This A Good Question/Topic? 1
  • +

Replies To: DataTable to Excel via XML

#2 maceysoftware  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 350
  • View blog
  • Posts: 1,508
  • Joined: 07-September 13

Re: DataTable to Excel via XML

Posted 10 May 2016 - 05:31 AM

Interesting way of looking at it, it should also cut down with issues that can occur.
Was This Post Helpful? 0
  • +
  • -

#3 andrewsw  Icon User is offline

  • blow up my boots
  • member icon

Reputation: 6447
  • View blog
  • Posts: 26,076
  • Joined: 12-December 12

Re: DataTable to Excel via XML

Posted 10 May 2016 - 09:36 AM

Thank you ;)

Mmm, I'm not sure that I would suggest that it will cut down on issues ;). It is the sort of option that will either work and achieve what you need, or you might not be quite happy with the (formatted) output. In which case, if you start to tweak the XML, then you might revert instead to a CSV output or, at a push, go back to iterating the rows and columns in Excel. (That is, of course, unless you actually desire XML output.)

I hope that the previous paragraph makes some sense? Basically, suck it and see. If you don't like the XML generated then you might consider other options.
Was This Post Helpful? 0
  • +
  • -

#4 maceysoftware  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 350
  • View blog
  • Posts: 1,508
  • Joined: 07-September 13

Re: DataTable to Excel via XML

Posted 10 May 2016 - 01:48 PM

Yes that does make sense.

The issues i was referring too are different to the issues than you are thinking of (at least i think).

For example

I use to do the export as you described cell by cell (iterate all the rows and columns), literally moving each cell across one at a time, however this could be slow, this was down to the office API, small data was fine but when moving around 100k rows or more it was painful.

This has been re-written to use the .range method, whereby yes I still iterate all the rows and columns but I shove the data into the office api less often, which deceased the slow down. The issue with the .Range was when you are exporting to office 2003 string we are capped at something like 1024, this would just error and you would therefore have to keep note of which cells these were and go back and re-populate them afterwards.

That's the one big issue i remember from the office API however i know i done several fixes around our exporting a year or so ago. I believe it will cut down on this issue as i wouldn't have to remember the 2003 API issues when looking at export code or any of the other fixes i have done.


Also going for the range method means you have to extract the data from the datatable and store it in an array first, this basically doubles the amount of data which i never liked as well, for example if you have 100 rows in your datatable with three text columns, that is 3 string variables that are created for each row, you would then store them same 3 values in three new string variables in the array. (unless i am missing something)

Therefore writing the datatable to xml and then opening up the xml seems like it bypasses the issues with the API and saves you resources.

But you are also correct if the desired output from the xml is not what is wanted then back to the old automation method.

This post has been edited by maceysoftware: 10 May 2016 - 01:49 PM

Was This Post Helpful? 0
  • +
  • -

#5 andrewsw  Icon User is offline

  • blow up my boots
  • member icon

Reputation: 6447
  • View blog
  • Posts: 26,076
  • Joined: 12-December 12

Re: DataTable to Excel via XML

Posted 10 May 2016 - 11:07 PM

Quote

Also going for the range method means you have to extract the data from the datatable and store it in an array first, this basically doubles the amount of data which i never liked as well, for example if you have 100 rows in your datatable with three text columns, that is 3 string variables that are created for each row, you would then store them same 3 values in three new string variables in the array. (unless i am missing something)

Do you mean that you put the entire dataset into an array? I would consider, and compare, doing this row by row using an Object array:
        xl.Visible = True
        Dim wb As Excel.Workbook = xl.Workbooks.Add
        Dim ws As Excel.Worksheet = xl.Worksheets(1)
        Dim rng As Excel.Range = ws.Range("A2:D2")
        With dgvStaff.Rows(0)   'just a single row to test
            rng.Value = New Object() {.Cells(0).Value, .Cells(1).Value, .Cells(2).Value, .Cells(3).Value}
        End With

The data could be iterated from the DataTable.

I would turn off automatic calculation mode, leave Excel not visible until needed (and after testing). Some Excel columns could be formatted before starting to put the data in, and/or afterwards.

It is early in the morning for me, so this is just a rough assessment.



An Object array involves boxing/unboxing, but this is unavoidable and happens anyway I believe, because, as we know, Excel and .NET are incompatible data types.
Was This Post Helpful? 0
  • +
  • -

#6 andrewsw  Icon User is offline

  • blow up my boots
  • member icon

Reputation: 6447
  • View blog
  • Posts: 26,076
  • Joined: 12-December 12

Re: DataTable to Excel via XML

Posted 10 May 2016 - 11:17 PM

Quote

literally moving each cell across one at a time..

I hope that you don't mean that you Activated each cell in turn :whistling: rather than just referencing the next cell .Cells(x, the_next_one).
Was This Post Helpful? 0
  • +
  • -

#7 maceysoftware  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 350
  • View blog
  • Posts: 1,508
  • Joined: 07-September 13

Re: DataTable to Excel via XML

Posted 11 May 2016 - 12:31 AM

Sadly I do mean Activating each cell in turn. However I don't feel to bad about it as I was only just learning to code when I originally wrote that snippet and it's only now that I have gone back to it that I have ended up scrapping it all and re-written it.

You are correct, you can do it row by row however when your talking about a big sets of data this is still slow, however at the same time we don't plunk all the data into array and export but rather do it in chunks.

So we loop around the first 1000 rows and store that information then push it over to excel, then gets the next lot etc etc. Ideally this needs to be improved again so that it takes the number of columns into consideration, for example moving 1000 rows with 10 columns isn't the same as moving 1000 rows with 40 columns.

I am also doing this:

Quote

I would turn off automatic calculation mode, leave Excel not visible until needed (and after testing). Some Excel columns could be formatted before starting to put the data in, and/or afterwards.

This post has been edited by maceysoftware: 11 May 2016 - 12:32 AM

Was This Post Helpful? 0
  • +
  • -

#8 andrewsw  Icon User is offline

  • blow up my boots
  • member icon

Reputation: 6447
  • View blog
  • Posts: 26,076
  • Joined: 12-December 12

Re: DataTable to Excel via XML

Posted 11 May 2016 - 04:15 AM

What's the data store? One last idea is to use ADO in Excel (and CopyFromRecordset) to retrieve the data.

You've probably already considered this :)
Was This Post Helpful? 0
  • +
  • -

#9 maceysoftware  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 350
  • View blog
  • Posts: 1,508
  • Joined: 07-September 13

Re: DataTable to Excel via XML

Posted 11 May 2016 - 04:59 AM

I have, however in the application anything can be exported, literally if there is a list there is a export button. The data store differs really, most of the time it is stored within a object structure, occasionally a datatable, we also have crystal reports.

Which is why we convert to a datatable first to keep the export as generic as possible.

This post has been edited by maceysoftware: 11 May 2016 - 05:10 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1