?
Solved

Slow Excel 2013 Interop performance

Posted on 2014-08-14
10
Medium Priority
?
1,789 Views
Last Modified: 2014-08-19
Hello,

I'm creating an Excel worksheet in C# using Excel Interop 15. Everything is running fast on a PC that have Office 2010 on it but it gets really slow on a PC with Office 2013.

If I remove Office 2013 and install Office 2010 on the same PC, it runs fast.

Any idea? Searched the web, someone tells to use CSV instead but I can't because I'm formating my worksheet with some colours and stuff. Others said to use ADO.net, will look into that. But what bugs me is it works fine with Office 2010 ...

Thank you
0
Comment
Question by:GIP
  • 4
  • 4
9 Comments
 
LVL 36

Expert Comment

by:Miguel Oz
ID: 40262968
Hard to say without doing a performance test to identify the Interop C# lines  that are causing the slow execution in Office 2013.
This issue could be a configuration issue or worse a Excel for an specific Interop call or the way you use it.
0
 

Author Comment

by:GIP
ID: 40268000
Hi Miguel,
Is slow because I use transfert Data Cell By Cell and I have 1500 lines. I have many color to configure and different style.
The problem I have, if I install Office 2010 on my computer is take 30sec do build my Workbooks, if I install Office 2013 on my computer is take 4m30s.
Why office 2013 is slower that 2010 ?

I can change my methode to field my data with Array of Data and build many text variable to field my color and style.  But I don't want to change my methode I just want to know Why Office 2013 is more slow ?

Francois
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40268024
Try saving in xlsb format
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:GIP
ID: 40268278
No is during create my workbook is slow
Exemple.
Office 2010 this code take 0.3 Second to build
Office 2013 take 2 second.
 Outils.DebugElapse("Début entête")

            ' Geler les ligne 1à3
            .Range("A" & LigneEC + 1).Select()
            ' Faut absolument que l'affichage soit en mode Normal pour FreezePanes
            oExcel.ActiveWindow.View = Excel.XlWindowView.xlNormalView
            oExcel.ActiveWindow.FreezePanes = True


            ' ============================
            ' === La ligne de l'entête ===
            ' ============================
            .Range("A" & LigneEP, FinColonne & LigneEP).MergeCells = True
            .Range("A" & LigneEP, FinColonne & LigneEP).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
            .Range("A" & LigneEP, FinColonne & LigneEP).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
            .Range("A" & LigneEP, FinColonne & LigneEP).RowHeight = 35
            .Range("A" & LigneEP).Value = NomEntete
            .Range("A" & LigneEP).Font.Size = 26
            .Range("A" & LigneEP).Font.Name = "Time New Roman"
            .Range("A" & LigneEP).Font.Bold = True
            .Range("A" & LigneEP).Font.Color = clrEnteteTitre

            ' =========================
            ' === La ligne du TITRE ===
            ' =========================
            .Range("A" & LigneT, FinColonne & LigneT).MergeCells = True
            .Range("A" & LigneT, FinColonne & LigneT).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
            .Range("A" & LigneT, FinColonne & LigneT).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
            .Range("A" & LigneT, FinColonne & LigneT).RowHeight = 35
            Select Case typeBC
                Case Enumeration.TypeDeBonCommande.Commande
                    .Range("A" & LigneT).Value = "Bon commande pour " & NomAfficher
                    NomFichier = Outils.CleanFileName("Bon commande " & NomAfficher & " " & DatesSemaine(DateDuJour))
                Case Enumeration.TypeDeBonCommande.Reservation
                    .Range("A" & LigneT).Value = "Bon réservation pour " & NomAfficher
                    NomFichier = Outils.CleanFileName("Bon reservation " & NomAfficher & " " & DatesSemaine(DateDuJour))
                Case Enumeration.TypeDeBonCommande.ListeDePrix
                    .Range("A" & LigneT).Value = "Bon réservation " & Annee.Nom & " pour " & NomAfficher
                    NomFichier = Outils.CleanFileName("Liste de prix " & NomAfficher & " " & Annee.Nom)
            End Select

            .Range("A" & LigneT).Font.Size = 18
            .Range("A" & LigneT).Font.Italic = True
            .Range("A" & LigneT).Font.Name = "Time New Roman"

            ' ==============================
            ' === La ligne de la semaine ===
            ' ==============================
            .Range("A" & LigneS, "B" & LigneS).Merge()

            Select Case typeBC
                Case Enumeration.TypeDeBonCommande.Commande
                    .Range("A" & LigneS).Value = "Semaine " & DatesSemaine(DateDuJour)
                    .Range("A" & LigneS).Font.Size = 22
                Case Enumeration.TypeDeBonCommande.Reservation
                    .Range("A" & LigneS).Value = "Semaine " & DatesSemaine(DateDuJour)
                    .Range("A" & LigneS).Font.Size = 22
                Case Enumeration.TypeDeBonCommande.ListeDePrix
                    .Range("A" & LigneS).Value = "LISTE DE PRIX " & Annee.Nom
                    .Range("A" & LigneS).Font.Color = Color.Red
                    .Range("A" & LigneS).Font.Size = 32
            End Select
            .Range("A" & LigneS).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
            .Range("A" & LigneS).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
            .Range("A" & LigneS, FinColonne & LigneS).RowHeight = 35
            .Range("C" & LigneS).Value = "PO : "
            .Range("C" & LigneS).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
            .Range("D" & LigneS).Locked = False
            .Range("D" & LigneS).Interior.Color = clrBleuFonce
            .Range("F" & LigneS).Value = "No. client : "
            .Range("F" & LigneS).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
            If oSheetParametre.Range(CellCleClient).Value Is Nothing Then
                .Range("G" & LigneS).Locked = False
                .Range("G" & LigneS).Interior.Color = clrBleuFonce
            Else
                .Range("G" & LigneS).Value = Client.NumClient
            End If
            .Range("G" & LigneS).Validation.Add(Excel.XlDVType.xlValidateWholeNumber, Excel.XlDVAlertStyle.xlValidAlertStop, Excel.XlFormatConditionOperator.xlGreater, 0)
            .Range("I" & LigneS).Value = "Nbre plantes : "
            .Range("I" & LigneS).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
            .Range("J" & LigneS, "K" & LigneS).MergeCells = True
            .Range("J" & LigneS).Interior.Color = clrBrunOrange
            .Range("J" & LigneS).Font.Color = clrBlanc
            .Range("J" & LigneS).Font.Size = 20
            .Range("J" & LigneS).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
            .Range("J" & LigneS).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
            .Range("L" & LigneS).Value = "Montant : "
            .Range("L" & LigneS).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
            .Range("M" & LigneS, "N" & LigneS).MergeCells = True
            .Range("M" & LigneS).Interior.Color = clrBrunOrange
            .Range("M" & LigneS).Font.Color = clrBlanc
            .Range("M" & LigneS).Font.Size = 20
            .Range("M" & LigneS).NumberFormat = CurrencyMode
            .Range("M" & LigneS).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
            .Range("M" & LigneS).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
            .Range("O" & LigneS).Value = "Palette : "
            .Range("O" & LigneS).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
            .Range("P" & LigneS, "Q" & LigneS).MergeCells = True
            .Range("P" & LigneS).Interior.Color = clrBrunOrange
            .Range("P" & LigneS).Font.Color = clrBlanc
            .Range("P" & LigneS).Font.Size = 20
            .Range("P" & LigneS).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
            .Range("P" & LigneS).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter


            ' ============================
            ' === La ligne des entêtes ===
            ' ============================
            .Range("A" & LigneEC, FinColonne & LigneEC).Interior.Color = clrEntete
            .Range("A" & LigneEC, FinColonne & LigneEC).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
            .Range("A" & LigneEC, FinColonne & LigneEC).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
            .Range("A" & LigneEC, FinColonne & LigneEC).Borders.Weight = Excel.XlBorderWeight.xlThin
            .Range("A" & LigneEC).RowHeight = 45
            .Range("A" & LigneEC).Value = "Code PLF"
            .Range("A" & LigneEC).ColumnWidth = 11
            .Range("B" & LigneEC).Value = "Nom Latin"
            .Range("B" & LigneEC).ColumnWidth = 54
            .Range("C" & LigneEC, "E" & LigneEC).MergeCells = True
            Select Case typeBC
                Case Enumeration.TypeDeBonCommande.Commande
                    .Range("C" & LigneEC).Value = "État"
                Case Enumeration.TypeDeBonCommande.Reservation
                    .Range("C" & LigneEC).Value = "État"
                Case Enumeration.TypeDeBonCommande.ListeDePrix
            End Select
            .Range("C" & LigneEC).ColumnWidth = 11
            .Range("D" & LigneEC).ColumnWidth = 11
            .Range("E" & LigneEC).ColumnWidth = 11
            .Range("F" & LigneEC).Value = "Pot"
            .Range("F" & LigneEC).ColumnWidth = 4
            .Range("G" & LigneEC).Value = "Hauteur" & Chr(10) & "cm" & Chr(10)
            .Range("G" & LigneEC).ColumnWidth = 9
            .Range("H" & LigneEC).Value = "Largeur" & Chr(10) & "cm" & Chr(10)
            .Range("H" & LigneEC).ColumnWidth = 9
            .Range("I" & LigneEC).Value = "Diamètre" & Chr(10) & "mm" & Chr(10)
            .Range("I" & LigneEC).ColumnWidth = 9
            .Range("J" & LigneEC).Value = "Zone"
            .Range("J" & LigneEC).ColumnWidth = 5
            Select Case typeBC
                Case Enumeration.TypeDeBonCommande.Commande
                    .Range("K" & LigneEC).Value = "Quantité" & Chr(10) & "Commandé" & Chr(10)
                Case Enumeration.TypeDeBonCommande.Reservation
                    .Range("K" & LigneEC).Value = "Quantité" & Chr(10) & "Réservé" & Chr(10)
                Case Enumeration.TypeDeBonCommande.ListeDePrix
                    .Range("K" & LigneEC).Value = "Quantité" & Chr(10) & "Réservé" & Chr(10)
            End Select

            .Range("K" & LigneEC).ColumnWidth = 11
            .Range("L" & LigneEC).Value = "Prix" & Chr(10) & "1-" & MesParametres.QtePrix2 - 1 & Chr(10)
            .Range("L" & LigneEC).ColumnWidth = 10
            .Range("M" & LigneEC).Value = "Prix" & Chr(10) & MesParametres.QtePrix2 & "-" & MesParametres.QtePrix3 - 1 & Chr(10)
            .Range("M" & LigneEC).ColumnWidth = 10
            .Range("N" & LigneEC).Value = "Prix" & Chr(10) & MesParametres.QtePrix3 & " et +" & Chr(10)
            .Range("N" & LigneEC).ColumnWidth = 10
            .Range("O" & LigneEC).Value = "Quantité" & Chr(10) & "Minimum" & Chr(10) & "Spécial   ."
            .Range("O" & LigneEC).ColumnWidth = 10
            .Range("O" & LigneEC).Interior.Color = clrRouge
            .Range("O" & LigneEC).Font.Color = clrBlanc
            .Range("P" & LigneEC).Value = "Prix" & Chr(10) & "Spécial" & Chr(10)
            .Range("P" & LigneEC).ColumnWidth = 10
            .Range("P" & LigneEC).Interior.Color = clrRouge
            .Range("P" & LigneEC).Font.Color = clrBlanc
            .Range("Q" & LigneEC).Value = "Prix" & Chr(10) & "Étiquette" & Chr(10) & "Client"
            .Range("Q" & LigneEC).ColumnWidth = 10
            .Range("R" & LigneEC).Value = Chr(10) & "SKU 1" & Chr(10) & "Client"
            .Range("R" & LigneEC).ColumnWidth = 10
            .Range("S" & LigneEC).Value = Chr(10) & "UPC" & Chr(10) & "Client"
            .Range("S" & LigneEC).ColumnWidth = 14
            .Range("T" & LigneEC).Value = Chr(10) & "UPC" & Chr(10) & "PLF"
            .Range("T" & LigneEC).ColumnWidth = 14
        End With

        Dim FamillePrecedent As String = ""
        Dim FamillePresente As String = ""
        Dim InterligneDeCouleur As Integer = 0
        Dim DebutFamillePresenteindex As Integer = 0
        Dim SelectionCelluleQuantite As String = ""
        Dim RangeConditionQuantite As Excel.Range = Nothing
        If upb IsNot Nothing Then upb.Maximum = ds.Tables(0).Rows.Count + ds.Tables(1).Rows.Count

        Outils.DebugElapse("Fin entête")

Open in new window

0
 
LVL 46

Accepted Solution

by:
aikimark earned 2000 total points
ID: 40268298
1. Transfer the values in bulk, if possible.  Read my
Fast Data Push to Excel article:  http:A_2253.html

2. Some of these formatting changes are made for entire columns.  Do these on the columnar level, not inside any loop.

3. If you must loop to do your data population, suspend screen updating
Application.Screenupdating = False
'your current code goes here
Application.Screenupdating = True

Open in new window

0
 

Author Comment

by:GIP
ID: 40268390
Hi aikimark,
Application.Screenupdating = False is  work :)
Thank you aikimark.

For data I know I can use Array for more performance.

When I have same property I use text variable
exemple
Dim RangeSelection as string
For Each dr As DataRow In ds.Tables(0).Rows
    rowIndex = rowIndex + 1
    If dr.item("Price") > 0 then
       RangeSelection += RangeSelection & "A" & RowIndex
    END IF
Next
Osheet1.Range(RangeSelection).Interior.Color = ClrBlue


Do you have other way for that ?

Francois.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40268431
Application = oExcel in your case

Do you have other way for that ?
Play an efficiency game with yourself.  How few references can you make to Excel objects in your code?

Populating an array in memory is fast.
Pushing that array to a row of cells is one-operation fast.
Formatting an entire column or partial column of cells only needs to reference the range once for each format.
0
 

Author Comment

by:GIP
ID: 40270241
Hi aikimark,
I have other problem.
I have Exception de HRESULT : 0x800A03EC when I use .validation.add

If I add both validation I have Exception de HRESULT : 0x800A03EC
But if I have only one i dont have exception.

SelectionCelluleQuantiteCondition = "29:1630"


SelectionCelluleQuantite = "K6:K7,K9:K27,K29:K101,K103:K127,K129:K191,K193:K645,K647:K750,K752:K764,K766:K820,K822:K826,K828:K860,K862:K927,K929:K935,K937:K974,K976:K980,K982:K991,K993:K1064,K1066:K1126,K1128:K1630"

.Range(SelectionCelluleQuantiteCondition).Validation.Add(Excel.XlDVType.xlValidateWholeNumber, Excel.XlDVAlertStyle.xlValidAlertStop, Excel.XlFormatConditionOperator.xlBetween, nbMinPermisExcel, nbMaxPermisExcel)
            .Range(SelectionCelluleQuantiteCondition).Validation.ErrorMessage = "Vous devez entrer un nombre compris entre " & nbMinPermisExcel & " et " & nbMaxPermisExcel
            .Range(SelectionCelluleQuantiteCondition).Validation.ErrorTitle = "Condition quantite"


.Range(SelectionCelluleQuantite).Validation.Add(Excel.XlDVType.xlValidateDecimal, Excel.XlDVAlertStyle.xlValidAlertStop, Excel.XlFormatConditionOperator.xlGreater, 0)
            .Range(SelectionCelluleQuantite.Replace("K", "Q")).Validation.ErrorMessage = "Vous devez entrer un nombre plus grand que 0"
            .Range(SelectionCelluleQuantite.Replace("K", "Q")).Validation.ErrorTitle = "Condition prix étiquette"



Francois.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40270257
If this is a new problem, please open a new question.  This question thread is about performance.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses
Course of the Month14 days, 6 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question