-1

Hi I'm currently having a datagridview with thousand (about 3000+) rows of record and 9 columns. I was using this method I found to export the datagridview to excel. There was no problem exporting hundred of rows but when it comes to thousand of rows, it hangs and it wouldn't respond to anything.

What problem could it be? and if there's any other way that is faster/better than this?

Thank you!

Community
  • 1
  • 1
WLeo
  • 203
  • 2
  • 16
  • It's not absolutely clear which method you're referring to, but if it's the first answer on the linked page then that's the least-efficient way to transfer a large amount of data to Excel. Load your data into a 2d array and place it on the sheet in a single step. https://www.add-in-express.com/creating-addins-blog/2013/11/29/populate-excel-workbooks-ranges-with-arrays/ – Tim Williams Apr 06 '16 at 06:19
  • @TimWilliams sorry for not stating it clear, yes I am using the method in the link. How do I actually do that? can you give me a simple example or what? thank you! – WLeo Apr 06 '16 at 06:26
  • It's in the link I posted – Tim Williams Apr 06 '16 at 14:33
  • @TimWilliams Thanks! – WLeo Apr 07 '16 at 07:15

1 Answers1

0

The code is being executed on the UI thread so of course your application would freeze while it's executing. If you want the UI to remain responsive then you'd have to execute the code on a secondary thread. The issue there is that the data is coming from a control, so that part at least must be executed on the UI thread.

I would suggest that one possibility is to use a BackgroundWorker and do the work in the DoWork event handler. You can set up a loop that calls ReportProgress, which raises the ProgressChanged event on the UI thread and allows you to get the data in pages, then write it out to your spreadsheet on the background thread. I'll follow up with an example.

jmcilhinney
  • 50,448
  • 5
  • 26
  • 46
  • Yes I know it would freeze but it actually hangs and it wouldn't complete the exporting, I've left my pc running for more than 12 hours already.. is there anyway rather than using a background worker? or a faster way to export it? – WLeo Apr 06 '16 at 03:43
  • Have you bothered to try to find out why/where it hangs? If something hangs it is because of either an infinite loop or a method call that doesn't return. Which is it in your case? Find out. You can add tracing code for that, i.e. output at milestones to let you know how far it's got. – jmcilhinney Apr 06 '16 at 04:21
  • Also, I just determined that the `ReportProgress` method is asynchronous so cannot be reliably used to get data from the `ProgressChanged` event handler. That means that you would have to `Invoke` a method yourself. If you don't mind your UI freezing then it's not an issue but if you do then you can use a `BackgroundWorker` or some other means of multi-threading and get the data on the UI thread as I suggested. – jmcilhinney Apr 06 '16 at 04:23