Slow Excel 2013 Interop performance

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
GIPAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Miguel OzSoftware EngineerCommented:
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
GIPAuthor Commented:
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
aikimarkCommented:
Try saving in xlsb format
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

GIPAuthor Commented:
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
aikimarkCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GIPAuthor Commented:
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
aikimarkCommented:
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
GIPAuthor Commented:
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
aikimarkCommented:
If this is a new problem, please open a new question.  This question thread is about performance.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.