0

I have a program to write the cells of a big excel file.(About 40 Mega Bytes).

I found it's very slow even just a move that writing a cell only. This is what I do for my project.

Excel.Workbook apoWorkBook = _Excel.Workbooks.Open(apo);
Excel.Worksheet apoWorkSheet = apoWorkBook.Worksheets[1];
Excel.Range apoRange = apoWorkSheet.UsedRange;

Then when I am going to write a cell like this:

apoRange.Cells[target_row, 23].Value2 = "Value"; // ( Repeats in different rows and columns )

It spends about 20 or more seconds to finish this line of source codes.

But I have many many same codes to write like this. This would spend so much time to finish it.

I made an experiment to write to a small empty excel file, it only used about < 0.1 seconds to do that.

I think it's very slow to write to a big excel file because it's like the program saves the file after writing to a cell every time.

What's the fastest way to write in an BIG excel file ? Can it be just like puting all values on the cells then just save at once.

Please help, thanks !

Jarkid
  • 171
  • 1
  • 4
  • 13
  • 2
    Related https://stackoverflow.com/a/536699/5265292 – grek40 Dec 05 '17 at 14:18
  • 2
    Optionally, don't use Excel Interop at all. A few hints are found here https://stackoverflow.com/a/2603625/5265292 – grek40 Dec 05 '17 at 14:21
  • You can write an array of values to an Excel.Range - as per @grek40's first comment - what you do need to be aware of though is the C# array is 0-offset, whereas the Excel.Range - although accessed in an C# array like manner - is actually 1-offset - so in your above example you would need to assign the value to arr[target_row-1,22] before writing to the Range. – PaulF Dec 05 '17 at 14:27

1 Answers1

3

you can try turning off auto recalcuation

set Application.Calculation = xlCalculationManual

may help, but interop is slow so doubt difference will be much.

Have you considered writing a csv file then opening that at the end?

Could even write the csv, then use interop to do the formatting after if that matters.

Rob
  • 444
  • 3
  • 10