1

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

Deee
  • 331
  • 2
  • 9
  • 27
Shubham
  • 25
  • 9
  • 2
    Possible duplicate of [Microsoft.Office.Interop.Excel really slow](https://stackoverflow.com/questions/3989122/microsoft-office-interop-excel-really-slow) – grek40 Sep 01 '17 at 07:02
  • @grek40 this one is kind of duplicate-ish. Shugham's problem is different because of the number of operations he does on the Excel sheet. Most of the time is spent opening up Excel.exe, in the example above it's mostly taken up by the number of values changed. – CodeMonkey Sep 01 '17 at 07:08
  • 1
    @CodeMonkey fair enough, you are probably right, considering the low amount of data. – grek40 Sep 01 '17 at 07:09
  • There's probably a duplicate somewhere though ;-). – CodeMonkey Sep 01 '17 at 07:09

1 Answers1

2

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.

CodeMonkey
  • 4,067
  • 1
  • 31
  • 43