7 Replies - 1928 Views - Last Post: 19 October 2012 - 05:08 AM Rate Topic: -----

#1 nighttrain  Icon User is offline

  • D.I.C Regular

Reputation: 6
  • View blog
  • Posts: 327
  • Joined: 22-September 10

Populate excel by values in 'one shoot'

Posted 15 October 2012 - 10:47 PM

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

Is This A Good Question/Topic? 0
  • +

Replies To: Populate excel by values in 'one shoot'

#2 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Populate excel by values in 'one shoot'

Posted 16 October 2012 - 09:47 AM

Have a look at this. It's supposed to be faster than using the InterOp
Was This Post Helpful? 1
  • +
  • -

#3 lar3ry  Icon User is offline

  • Coding Geezer
  • member icon

Reputation: 310
  • View blog
  • Posts: 1,290
  • Joined: 12-September 12

Re: Populate excel by values in 'one shoot'

Posted 16 October 2012 - 10:22 AM

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

Was This Post Helpful? 0
  • +
  • -

#4 AdamSpeight2008  Icon User is offline

  • MrCupOfT
  • member icon


Reputation: 2262
  • View blog
  • Posts: 9,464
  • Joined: 29-May 08

Re: Populate excel by values in 'one shoot'

Posted 16 October 2012 - 10:41 AM

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
Was This Post Helpful? 0
  • +
  • -

#5 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Populate excel by values in 'one shoot'

Posted 16 October 2012 - 10:44 AM

View Postlar3ry, 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 :(
Was This Post Helpful? 0
  • +
  • -

#6 lar3ry  Icon User is offline

  • Coding Geezer
  • member icon

Reputation: 310
  • View blog
  • Posts: 1,290
  • Joined: 12-September 12

Re: Populate excel by values in 'one shoot'

Posted 16 October 2012 - 11:18 AM

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.
Was This Post Helpful? 0
  • +
  • -

#7 nighttrain  Icon User is offline

  • D.I.C Regular

Reputation: 6
  • View blog
  • Posts: 327
  • Joined: 22-September 10

Re: Populate excel by values in 'one shoot'

Posted 18 October 2012 - 11:50 PM

Could someone show me how could i populate an array from the dataset?
Was This Post Helpful? 0
  • +
  • -

#8 nighttrain  Icon User is offline

  • D.I.C Regular

Reputation: 6
  • View blog
  • Posts: 327
  • Joined: 22-September 10

Re: Populate excel by values in 'one shoot'

Posted 19 October 2012 - 05:08 AM

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


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1