CODEWITHSUNDEEP
c#
Shubham
Shubham

Reputation: 25

Exporting Data to Excel very Slow

I am trying to export data from my C# code to MS Excel 2007, but it is taking 30 seconds to insert data in an excel file.The code is like this->

Excel.Application excelapp = new Excel.Application();

Excel.Workbook excelworkbook = excelapp.Workbooks.Open(fileTest);

Excel.Sheets excelsheets = excelworkbook.Worksheets;

Excel.Worksheet mysheets = (Excel.Worksheet)excelsheets.get_Item("Sheet1");

Excel.Range mycells = mysheets.Cells;

mycells.Item[destroyer, "A"].Value = s[2];

mycells.Item[destroyer, "B"].Value = s[1];

mycells.Item[destroyer, "C"].Value = s[3];

mycells.Item[destroyer, "D"].Value = dbl_standard.Text;

 mycells.Item[destroyer, "E"].Value = s[4];

 mycells.Item[destroyer, "F"].Value = s[7];

 mycells.Item[destroyer, "G"].Value = s[5];

 mycells.Item[destroyer, "H"].Value = s[6];

excelworkbook.Save();

 excelworkbook.Close();

 excelapp.Quit();

Marshal.ReleaseComObject(mycells);

Marshal.ReleaseComObject(mysheets);

 Marshal.ReleaseComObject(excelsheets);

Marshal.ReleaseComObject(excelworkbook);

 Marshal.ReleaseComObject(excelapp);

I am inserting hardly 25 columns.Which thing am I doing wrong?How to make it fast?

Thanks in Advance

Upvotes: 1

Views: 2337

Answers (1)

CodeMonkey
CodeMonkey

Reputation: 4738

You have two issues going on here. The first issue is that Excel interop actually opens Excel.exe and iteroperates with the process. You won't be able to remove the overhead of starting Excel, which is probably the bulk of your processing time.

The other part is that for every cell you edit you create a lot of calls "under the hood" to the interop layer. You can vectorize these calls.

For reading: https://stackoverflow.com/a/42604291/3387223

For writing (VB example): https://stackoverflow.com/a/23503305/3387223

That way you only create one interop operation for the whole range of values. This will be roughly 25 times quicker than inserting 25 values.

But as I stated above, starting Excel is probably what takes most of your time.

You can read and write Excel sheets faster with OpenXML, but maybe you'll run into some formatting issues, and you won't get instant updates of other formulas in your Excel sheet (if that's what you need).

https://msdn.microsoft.com/en-us/us-en/library/office/bb448854.aspx

Here's an example on generating Excel sheets:

https://msdn.microsoft.com/en-us/library/office/hh180830(v=office.14).aspx

And if you want an easier time dealing with OpenXml there is ClosedXml:

https://github.com/closedxml/closedxml

Which will make OpenXml about as easy as standard interop.

Upvotes: 2

Related Questions