[nighttrain]

Hi guys, i heard that best way to populate an excel by values e.g from dataset is to fill the array and then populate the excel sheet. I got below code:

.........
DA.Fill(DS, "sheet")

Dim iColumn, iRow As Integer
                        Dim iColumnMax, iRowMax As Integer
                        iRowMax = DS.Tables(0).Rows.Count - 1
                        iColumnMax = DS.Tables(0).Columns.Count - 1
                        For iRow = 0 To iRowMax
                            For iColumn = 0 To iColumnMax
                                .Cells(iRow + 2, iColumn + 1).Value = DS.Tables(0).Rows(iRow).Item(iColumn)
                            Next
                        Next

that's the way i am filling the excel sheet, but sometimes it takes almost 1 hour with big wuerie, so i decided to try something else like put firstly the values from dataset to array and then fill the sheet in one shoot. Could someone show me how i could do that on my above example?

thanks & regards

[CharlieMay]

Have a look at this. It's supposed to be faster than using the InterOp

[lar3ry]

Good find, CharlieMay! I was converting some old VB6 source, but was a little handicapped by having only OpenOffice.org.

oops. I see it's a bit pricey.

This post has been edited by lar3ry: 16 October 2012 - 10:25 AM

[AdamSpeight2008]

What InterOp is essentially doing is working Excel remotely.

Do you consider a different approach, like XML? As Excel and OpenOffice both support a XML Document file format.

Video

[CharlieMay]

lar3ry, on 16 October 2012 - 01:22 PM, said:

Good find, CharlieMay! I was converting some old VB6 source, but was a little handicapped by having only OpenOffice.org.

oops. I see it's a bit pricey.

Yea, I should have researched it more, the free version is limited to 150 rows so that's pretty much worthless

[lar3ry]

Quote

Do you consider a different approach, like XML? As Excel and OpenOffice both support a XML Document file format.

Actually I have, personally, not much use for Excel programming. Only an occasional problem to solve that is best done on a spreadsheet, and was only translating some code (to help answer here), I found on PlanetSourceCode that is a complete project for comparing times for a few ways to fill an Excel sheet, including a fast one filling from an array.

It's done with an earlier version of VB.Net, and it seems the conversion wizard in VS2010 doesn't want to handle a .vbp file.

I sorta got stuck when I realized it required Excel to load. I'll be looking at it later, though, because it's an interesting concept.

[nighttrain]

Could someone show me how could i populate an array from the dataset?

[nighttrain]

After all i found out whats correct way to do that:

 Dim minutes As Double                ' <=========================== NEED TO BE DOUBLE
                Dim p As Integer = 2

                Do While .Cells(p, 1).Value <> Nothing
                    minutes = .Cells(p, 13).Value
                    Dim ts = TimeSpan.FromMinutes(minutes)
                    .Cells(p, 13).value = ts.ToString
                    p += 1
                Loop