Exporting shedules into Excel using Microsoft.Office.Interop.Excel is slow...

[SONA-ARCHITECTURE]

Hi,

I made a plugin to batch export all shedules from multiple documents.

I've got 2 options : exporting in txt and exporting in xlsx.

Exporting in txt format via ViewSchedule Class is almost quick.

But, when I'm using schedule.GetTableData() in order to export into an Excel file using Microsoft.Office.Interop.Excel;, it is very very slow.....

Do you know why?

Does EPPlus is faster?

Thx

[jeremy_tammik]

I would suggest that you benchmark the process to determine exactly where the bottleneck lies. 

The important question may be: is the main performance hit caused by the Revit API GetTableData method, or by the subsequent communication with and processing by Microsoft.Office.Interop.Excel?

You say, exporting to txt is fast. Why don't you just export directly to CSV format?

https://en.wikipedia.org/wiki/Comma-separated_values

For your purposes, it is probably completely equivalent to native XSL or XSLX. Furthermore, it has the great advantage of being non-proprietary. Plus, you save yourself the hassle of this question.

[Kennan.Chen]

Try the NPOI library which is the .NET port of Apache POI java project.

NuGet Gallery | NPOI 2.6.0

nissl-lab/npoi: a .NET library that can read/write Office formats without Microsoft Office installed...

[SONA-ARCHITECTURE]

Hi,

I give you a feed back about the subject.

I can confirm that point : if you want to export your shedules and formating them into xlsx format, you must

export with schedule.Export method and using Microsoft.Office.Interopt.Excel library QueryTables.Add("TEXT;" + sheduleFileName. After you will be able to use the Microsoft.Office.Interop.Excel to modify your Excel file.

GetTableData() from Revit API is too slow.

Hope it will help you.

[RPTHOMAS108]

Of all the suggestions in the below link the one that seemed to have the most benefit was the one entitled: 

Read and write large blocks of data in a single operation 

This cut down a 20x200 cell write from seven seconds to about one second. You can make it quicker by reducing the range. If you don't need to expand 'UsedRange' then use that perhaps but on a new worksheet that is going to be inadequate for your purpose. This test below was on an initial blank worksheet so that needs to be noted also.

Dim XX As Object = WS.Range("A1:Z10000").Value2
    For C = 0 To 20
         For R = 0 To 200
             XX(R + 1, C + 1) = CDbl(I)
             I += 1
         Next
    Next
WS.Range("A1:Z10000").Value2 = XX

Also suggested to turn off the calculation etc. but I didn't see much benefit in that. That probably has more benefit if your worksheet has such numerous calculations.

Also if you are turning off calculations at application level then you still need there to be a workbook. Otherwise you get some COM exception that is generally unhelpful as to why:

IntXLApp.Calculation = XlCalculation.xlCalculationManual

Results in type mismatch. 

[r0893889]

hello, 

i was wondering if there is a solution to link revit scedules with excel so it would automatically change the adjustments.