c# - office - Interoperabilidad de Excel-Eficiencia y rendimiento

microsoft excel interop c# example

Question

Me preguntaba qué podría hacer para mejorar el rendimiento de la automatización de Excel, ya que puede ser bastante lento si tiene mucho en juego en la hoja de trabajo ...

Aquí hay algunos que encontré a mí mismo:

• ExcelApp.ScreenUpdating = false - desactiva el redibujado de la pantalla

• ExcelApp.Calculation = Excel.XlCalculation.xlCalculationManual : desactivar el motor de cálculo para que Excel no vuelva a calcular automáticamente cuando cambie el valor de una celda (vuelva a encenderlo cuando haya terminado)

• Reduce las llamadas a Worksheet.Cells.Item(row, col) y Worksheet.Range - Tuve que sondear cientos de celdas para encontrar la celda que necesitaba. La implementación de almacenamiento en caché de las ubicaciones de las celdas redujo el tiempo de ejecución de ~ 40 a ~ 5 segundos.

¿Qué tipo de llamadas interoperativas tienen un alto costo en el rendimiento y deben evitarse? ¿Qué más puedes hacer para evitar que se realice un procesamiento innecesario?

Answer 1

Al usar C # o VB.Net para obtener o establecer un rango, averigüe cuál es el tamaño total del rango y luego obtenga un gran conjunto de objetos bidimensionales ...

//get values
object[,] objectArray = shtName.get_Range("A1:Z100").Value2;
iFace = Convert.ToInt32(objectArray[1,1]);

//set values
object[,] objectArray = new object[3,1] {{"A"}{"B"}{"C"}};
rngName.Value2 = objectArray;

Tenga en cuenta que es importante saber qué tipo de datos está almacenando Excel (texto o números), ya que no lo hará automáticamente cuando esté volviendo a convertir el tipo de la matriz de objetos. Agregue pruebas si es necesario para validar los datos si no puede estar seguro de antemano del tipo de datos.

Answer 2

Como dice el tipo anónimo: leer / escribir bloques de gran alcance es muy importante para el rendimiento.

En los casos en que la sobrecarga de COM-Interop aún es demasiado grande, puede cambiar a usar la interfaz XLL, que es la interfaz más rápida de Excel.

Aunque la interfaz XLL está pensada principalmente para usuarios de C ++, tanto XL DNA como Addin Express proporcionan la capacidad de puente de .NET a XLL, que es significativamente más rápida que COM-Interop.

Answer 3

El rendimiento también depende mucho de cómo automatizar Excel. VBA es más rápido que la automatización COM es más rápido que la automatización .NET. Y típicamente el enlace temprano (tiempo de compilación) es más rápido que el enlace tardío, también.

Si tiene problemas graves de rendimiento, podría pensar en mover las partes críticas del código a un módulo VBA y llamar a ese código desde su código de automatización COM / .NET.

Si usa .NET, también debe usar los ensamblados de interoperabilidad primarios optimizados disponibles de Microsoft y no usar conjuntos de interoperabilidad personalizados.

Answer 4

El uso sobresale la funcionalidad incorporada siempre que sea posible, por ejemplo: en lugar de buscar una columna completa para una cadena dada, use el comando find disponible en la GUI mediante Ctrl-F:

Set Found = Cells.Find(What:=SearchString, LookIn:=xlValues, _
    SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)

If Not Found Is Nothing Then
    Found.Activate
    (...)
EndIf

Si desea ordenar algunas listas, use el comando excel sort , no lo haga manualmente en VBA:

Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal

Answer 5

Esto es para cualquiera que se pregunte cuál es la mejor manera de rellenar una hoja de Excel desde un conjunto de resultados db. Esto no pretende ser una lista completa de ninguna manera, pero enumera algunas opciones.

Algunos números de rendimiento al intentar rellenar una hoja de Excel con 155 columnas y 4200 registros en un antiguo cuadro Pentium 4 3GHz, incluido el tiempo de recuperación de datos que nunca fue más de 10 segundos en orden de más lento a más rápido, es el siguiente ...

1. Una célula a la vez - Poco menos de 11 minutos

2. Poblando un conjunto de datos convirtiendo a html + Guardando html en el disco + Cargando html en excel y guardando la hoja de trabajo como xls / xlsx - 5 minutos

3. Una columna a la vez - 4 minutos

4. Uso del procedimiento sp_makewebtask obsoleto en SQL 2005 para crear un archivo HTML - 9 segundos + Seguido cargando el archivo html en Excel y guardándolo como XLS / XLSX - Aproximadamente 2 minutos.

5. Convierta el conjunto de datos .Net a ADO RecordSet y utilice la función WorkSheet.Range []. CopyFromRecordset para completar Excel: ¡ 45 segundos!

Terminé usando la opción 5. Espero que esto ayude.

Answer 6

Otra gran cosa que puede hacer en VBA es utilizar Option Explicit y evitar variantes siempre que sea posible. Las variantes no son 100% evitables en VBA, pero hacen que el intérprete trabaje más en tiempo de ejecución y desperdicie memoria.

Encontré este artículo muy útil cuando comencé con VBA en Excel.
http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm

Y este libro

http://www.amazon.com/VB-VBA-Nutshell-Language-OReilly/dp/1565923588

Similar a

 app.ScreenUpdates = false //and
 app.Calculation = xlCalculationManual

también puedes establecer

 app.EnableEvents = false //Prevent Excel events
 app.Interactive = false  //Prevent user clicks and keystrokes

aunque no parecen marcar una gran diferencia como los primeros dos.

Similar a establecer valores de rango en matrices, si trabaja con datos que son principalmente tablas con la misma fórmula en cada fila de una columna, puede usar la notación de fórmulas R1C1 para su fórmula y establecer una columna completa igual a la cadena de fórmulas para establecer todo en una sola llamada.

app.ReferenceStyle = xlR1C1
app.ActiveSheet.Columns(2) = "=SUBSTITUTE(C[-1],"foo","bar")"

Además, la creación de complementos XLL con ExcelDNA & .NET (o por el camino difícil en C) es también la única forma en que puede obtener las UDF para ejecutarse en varios subprocesos. (Consulte la propiedad IsThreadSafe del atributo ExcelFunction de Excel DNA).

Antes de hacer la transición completa a Excel DNA, también experimenté con la creación de bibliotecas COM visibles en .NET para hacer referencia en proyectos de VBA. El procesamiento de texto pesado es un poco más rápido que VBA de esa manera, al igual que el uso de clases de listas .NET envueltas en lugar de la colección de VBA, pero Excel DNA es mejor.

Answer 7

Si está sondeando valores de muchas celdas, puede obtener todos los valores de celda en un rango almacenado en una matriz variante de una sola vez:

Dim CellVals() as Variant
CellVals = Range("A1:B1000").Value

Aquí hay una compensación, en términos del tamaño del rango para el que obtiene los valores. Supongo que si necesita un millar de valores de celda o más, probablemente sea más rápido que solo pasar por diferentes celdas y sondear los valores.