Formating Excel-Cells --> Very slow

[Basement]

Hey,

last question this day.

I've got a Excel Sheet with 8000 rows.

No i want to go through every row and if cell A contains ".1" format the whole row with grey background color.

Here is what i've done and what works...but very very damn slow:

Local $oAppl = _Excel_Open()
Local $sWorkbook = @ScriptDir & "\Template.xlsm"
Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook)

;Copy the 8000 lines from Array to Excel
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet,$arr_final, "A1")

;Autofit
$oWorkbook.Activesheet.Columns.Autofit

;Start to format
For $i=1 to UBound($arr_final) Step +1
    local $col_val=_Excel_RangeRead($oWorkbook, $oWorkbook.Activesheet, "A"&$i)
    if $col_val=".1" Then
        $oWorkbook.ActiveSheet.Range("A"&$i&":H"&$i).Interior.ColorIndex = 15
    EndIf
Next

You can watch excel walking through every line of the sheet...and drink tons of coffee ;-)

Is there a possibillity to fasten this process?

Best regards

Daniel

[Oscis]

What is the maximum number of columns that is in any row? Do you have any blank cells in a row, or does the row end at a blank cell? The answer to these questions may help limit how much of the document has to be read, which will speed up the program. I'll type up some code with some made up ranges as an example.

Edit: I took a closer look at your code, and I think I have the answers to my own questions.

Edited June 20, 2014 by Oscis

[water]

You write the array to Excel and then read each cell in column A again. That slows down your script.

Loop through the array and then color the corresponding row in Excel.

To enhance speed set the screen update parameter in _Excel_Open or _Excel_BookOpen (I'm not sure which one) to false and then set the property to true after processing has finished. Check the remarks in the Udf docu how to do.

[Oscis]

Here's my attempt at it. Is this script any faster than what you have?

expandcollapse popup

#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_Run_Au3Stripper=y
#Au3Stripper_Parameters=/RM /SF = 1 /SV = 1 /PE
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****
Opt("MustDeclareVars",1)
Opt("GUIOnEventMode", 1);Turn on GUIOnEventMode
#include <Excel.au3>
#include <Array.au3>
#include <StaticConstants.au3>
#include <ProgressConstants.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
Local $oExcelFilePath = @ScriptDir & "\Template.xlsx"
_CreateExcelWorkbook($oExcelFilePath);You already have your workbook.
Local $Array = _CreateArray();You already have an array of course.
_UpdateExcelWorkbook($Array,$oExcelFilePath)
Func _CreateExcelWorkbook($oExcelFilePath)
    Local $oExcel = ObjCreate("Excel.Application")
    $oExcel.Visible = True
    $oExcel.WorkBooks.Add()
    $oExcel.ActiveWorkbook.Sheets(1).Select()
    $oExcel.ActiveWorkBook.SaveAs($oExcelFilePath,51, "", "", Default, Default, 1, 2);(FilePath,VType,ReadPassword,$WritePassword,$AccessMode,$ConflictResolution)
    ;51 is xlsx
    $oExcel.Application.Quit()
EndFunc
Func _CreateArray()
    Local $Array[8001][9]
    For $C = 1 to 8000;8,000 rows.
        For $C2 = 1 to 8;8 collumns A to H, right?
            $Array[$C][$C2] = Random(1,9,1)/10
        Next
    Next
    Return $Array
EndFunc
Func _UpdateExcelWorkbook(ByRef $Array, $oExcelFilePath)
    Local $Timer = TimerInit()
    Global $oExcel = ObjCreate("Excel.Application")
    $oExcel.Visible = False;Hide the workbook. It won't update anyway.
    $oExcel.WorkBooks.Open($oExcelFilePath, Default, False, Default, "", "");($FilePath, Unknown, $ReadOnly, Unknown, $ReadPassword, $WritePassword)
    $oExcel.ActiveWorkbook.Sheets(1).Select()
    $oExcel.Application.ScreenUpdating = False;Turn off screen updating to speed things up
    Global Const $CMax = UBound($Array,1) - 1, $MaxCollumns = _AddCommas($CMax)
    Global $Percent, $C
    Local Const $C2Max = UBound($Array,2) - 1
    AdlibRegister("_Progress",2500);Update every 2.5 seconds. This can be shortened down to 1/4 of a second, but calling a function pauses the rest of the script until that function is done.
    Local $GUI = GUICreate("Grey Out Excel Rows",400,100,-1,-1,$WS_CAPTION)
    Global $ProgressControl = GUICtrlCreateProgress(10,10,380,20,$PBS_SMOOTH), $ProgressLabel = GUICtrlCreateLabel("0%",10,40,380,20,$SS_CENTER), _
    $StatusLabel = GUICtrlCreateLabel("Write Data to Excel - Row 0 of " & $MaxCollumns,10,70,380,20,$SS_CENTER)
    GUISetState()
    GUISetOnEvent($GUI_EVENT_CLOSE, "_GUIClose")
    ;Write data to Excel
    Do
        $C += 1
        For $C2 = 1 to $C2Max;Collumns
            $oExcel.Sheets(1).Cells($C,$C2).Value = $Array[$C][$C2]
        Next
    Until $C = $CMax
    AdlibUnRegister("_Progress")
    GUICtrlSetData($ProgressControl,0)
    GUICtrlSetData($ProgressLabel,"0%")
    GUICtrlSetData($StatusLabel,"Grey out cells - Row 0 of " & $MaxCollumns)
    AdlibRegister("_Progress2",2500)
    ;Grey out rows
    $C = 0
    Do
        $C += 1
        If $oExcel.Sheets(1).Cells($C,1).Value = 0.1 Then $oExcel.ActiveSheet.Range("A" & $C & ":H" & $C).Interior.ColorIndex = 15
    Until $C = $CMax
    AdlibUnRegister("_Progress2")
    GUIDelete($GUI)
    GUISetOnEvent($GUI_EVENT_CLOSE,"");Unregister this event
    ProgressOff()
    ;$oExcel.DisplayAlerts = False;Without this, Excel asks to save your work.
    $oExcel.Visible = True;Show the workbook so that you can close it if it doesn't close on its own.
    $oExcel.Application.ScreenUpdating = True;Turn Screen Updating back on
    $oExcel.ActiveWorkBook.Save()
    $oExcel.Application.Quit()
    $oExcel = 0;Free memory
    $Timer = Round(TimerDiff($Timer)/1000,2)
    ;$oExcel.Quit();Does the same thing 0.o
    ShellExecute($oExcelFilePath);Close Excel, then re-launch it to make sure it saved properly
    MsgBox(0,"Done","I've finished updating your spreadsheet, and opened the file for you to view." & @CRLF & @CRLF & "The process took " & _FormatMiliseconds($Timer) & ".")
EndFunc
Func _Progress()
    $Percent = Round($C/$CMax*100,2)
    GUICtrlSetData($ProgressControl,$Percent)
    GUICtrlSetData($ProgressLabel,$Percent & "%")
    GUICtrlSetData($StatusLabel,"Write Data to Excel - Row " & $C & " of " & $MaxCollumns)
    ProgressSet($Percent,$Percent & "%","Row " & _AddCommas($C) & " of " & $MaxCollumns)
EndFunc
Func _Progress2()
    $Percent = Round($C/$CMax*100,2)
    GUICtrlSetData($ProgressControl,$Percent)
    GUICtrlSetData($ProgressLabel,$Percent & "%")
    GUICtrlSetData($StatusLabel,"Grey out cells - Row " & $C & " of " & $MaxCollumns)
    ProgressSet($Percent,$Percent & "%","Row " & _AddCommas($C) & " of " & $MaxCollumns)
EndFunc
Func _GUIClose()
    If MsgBox(262180,"Question","Are you sure you want to exit the program?") = 6 Then;4 + 32 + 262144 = 262180 Yes and No buttons, question mark and topmost attribute.
        $oExcel.DisplayAlerts = False;Without this, Excel asks to save your work.
        $oExcel.Application.ScreenUpdating = True;Turn Screen Updating back on
        $oExcel.Application.Quit()
        $oExcel = 0;Free memory
        Exit
    EndIf
EndFunc
Func _AddCommas($N)
    Local $D = StringInStr($N,".",2), $E, $S, $T
    If $D Then
        $E = $D - 4
    Else
        $E = StringLen($N) - 3
    EndIf
    If $E > 0 Then
        $S = Mod($E,3)
        If $S Then
            $T = StringLeft($N,$S) & ","
        Else
            $T = ""
        EndIf
        $S += 1
        For $C = $S to $E Step 3
            $T &= StringMid($N,$C,3) & ","
        Next
        $T &= StringMid($N,$E + 1)
        Return $T
    Else
        Return $N
    EndIf
EndFunc
Func _FormatMiliseconds($N,$P = 2)
    Local $D, $H, $M, $S, $T;Declaring every variable at the beginning proved to be faster than declaring the variables only when they're needed.
    If $N > 86400000 Then;24*60*60*1000 = 86400000 1 Day
        $D = Floor($N/86400000)
        $N = Mod($N,86400000)
        If $N > 3600000 Then;60 * 60 * 1000 = 3600000 1 Hour
            $H = Floor($N/3600000)
            $N = Mod($N,3600000)
            If $N > 60000 Then ;60 * 1000 = 60000 1 Minute
                $M = Floor($N/60000)
                $N = Mod($N,60000)
                If $N > 0 Then ;1 Second
                    $S = Round($N/1000,$P)
                    If $D > 1 Then
                        $T = $D & " days, "
                    Else
                        $T = "1 day, "
                    EndIf
                    If $H > 1 Then
                        $T &= $H & " hours, "
                    Else
                        $T &= "1 hour, "
                    EndIf
                    If $M > 1 Then
                        $T &= $M & " minutes, and "
                    Else
                        $T &= "1 minute, and "
                    EndIf
                    If $S = 1 Then
                        $T &= "1 second"
                    Else
                        $T &= $S & " seconds"
                    EndIf
                    Return $T;1111
                Else
                    If $D > 1 Then
                        $T = $D & " days, "
                    Else
                        $T = "1 day, "
                    EndIf
                    If $H > 1 Then
                        $T &= $H & " hours, and "
                    Else
                        $T &= "1 hour, and "
                    EndIf
                    If $M > 1 Then
                        $T &= $M & " minutes"
                    Else
                        $T &= "1 minute"
                    EndIf
                    Return $T;1110
                EndIf
            Else
                If $N > 0 Then ;1 Second
                    $S = Round($N/1000,$P)
                    If $D > 1 Then
                        $T = $D & " days, "
                    Else
                        $T = "1 day, "
                    EndIf
                    If $H > 1 Then
                        $T &= $H & " hours, and "
                    Else
                        $T &= "1 hour, and "
                    EndIf
                    If $S = 1 Then
                        $T &= "1 second"
                    Else
                        $T &= $S & " seconds"
                    EndIf
                    Return $T;1101
                Else
                    If $D > 1 Then
                        $T = $D & " days, and "
                    Else
                        $T = "1 day, and "
                    EndIf
                    If $H > 1 Then
                        $T &= $H & " hours"
                    Else
                        $T &= "1 hour"
                    EndIf
                    Return $T;1100
                EndIf
            EndIf
        Else
            If $N > 60000 Then ;60 * 1000 = 60000 1 Minute
                $M = Floor($N/60000)
                $N = Mod($N,60000)
                If $N > 0 Then ;1 Second
                    $S = Round($N/1000,$P)
                    If $D > 1 Then
                        $T = $D & " days, "
                    Else
                        $T = "1 day, "
                    EndIf
                    If $M > 1 Then
                        $T &= $M & " minutes, and "
                    Else
                        $T &= "1 minute, and "
                    EndIf
                    If $S = 1 Then
                        $T &= "1 second"
                    Else
                        $T &= $S & " seconds"
                    EndIf
                    Return $T;1011
                Else
                    If $D > 1 Then
                        $T = $D & " days and "
                    Else
                        $T = "1 day and "
                    EndIf
                    If $M > 1 Then
                        $T &= $M & " minutes"
                    Else
                        $T &= "1 minute"
                    EndIf
                    Return $T;1010
                EndIf
            Else
                If $N > 0 Then ;1 Second
                    $S = Round($N/1000,$P)
                    If $D > 1 Then
                        $T = $D & " days and "
                    Else
                        $T = "1 day and "
                    EndIf
                    If $S = 1 Then
                        $T &= "1 second"
                    Else
                        $T &= $S & " seconds"
                    EndIf
                    Return $T;1001
                Else
                    If $D > 1 Then
                        Return $D & " days"
                    Else
                        Return "1 day"
                    EndIf
                    ;1000
                EndIf
            EndIf
        EndIf
    Else
        If $N > 3600000 Then;60 * 60 * 1000 = 3600000 1 Hour
            $H = Floor($N/3600000)
            $N = Mod($N,3600000)
            If $N > 60000 Then ;60 * 1000 = 60000 1 Minute
                $M = Floor($N/60000)
                $N = Mod($N,60000)
                If $N > 0 Then ;1 Second
                    $S = Round($N/1000,$P)
                    If $H > 1 Then
                        $T = $H & " hours, "
                    Else
                        $T = "1 hour, "
                    EndIf
                    If $M > 1 Then
                        $T &= $M & " minutes, and "
                    Else
                        $T &= "1 minute, and "
                    EndIf
                    If $S = 1 Then
                        $T &= "1 second"
                    Else
                        $T &= $S & " seconds"
                    EndIf
                    Return $T;0111
                Else
                    If $H > 1 Then
                        $T = $H & " hours and "
                    Else
                        $T = "1 hour and "
                    EndIf
                    If $M > 1 Then
                        $T &= $M & " minutes"
                    Else
                        $T &= "1 minute"
                    EndIf
                    Return $T;0110
                EndIf
            Else
                If $N > 0 Then ;1 Second
                    $S = Round($N/1000,$P)
                    If $H > 1 Then
                        $T = $H & " hours and "
                    Else
                        $T = "1 hour and "
                    EndIf
                    If $S = 1 Then
                        $T &= "1 second"
                    Else
                        $T &= $S & " seconds"
                    EndIf
                    Return $T;0101
                Else
                    If $H > 1 Then
                        Return $H & " hours"
                    Else
                        Return "1 hour"
                    EndIf
                    ;0100
                EndIf
            EndIf
        Else
            If $N > 60000 Then ;60 * 1000 = 60000 1 Minute
                $M = Floor($N/60000)
                $N = Mod($N,60000)
                If $N > 0 Then ;1 Second
                    $S = Round($N/1000,$P)
                    If $M > 1 Then
                        $T = $M & " minutes and "
                    Else
                        $T = "1 minute and "
                    EndIf
                    If $S = 1 Then
                        $T &= "1 second"
                    Else
                        $T &= $S & " seconds"
                    EndIf
                    Return $T;0011
                Else
                    If $M > 1 Then
                        Return $M & " minutes"
                    Else
                        Return "1 minute"
                    EndIf
                    ;0010
                EndIf
            Else
                If $N > 0 Then ;1 Second
                    $S = Round($N/1000,$P)
                    If $S = 1 Then
                        Return "1 second"
                    Else
                        Return $S & " seconds"
                    EndIf
                    ;0001
                Else
                    Return "0 seconds"
                    ;0000
                EndIf
            EndIf
        EndIf
    EndIf
EndFunc

I have some functions thrown in there for adding commas to numbers and milliseconds to days, hours, minutes, and seconds.

If you compile this script it may run faster because of the way the AU3Stripper is set up. The translator will have shorter variable names to work with, which generally speeds up a program, but sometimes the change isn't drastic.

Edited June 20, 2014 by Oscis

[Basement]

Hi,

thanx for your answers.

@Oscis: I will try out your script tommorow (i'm not at home today)

@water: I already tried out, checking the conditions directly in the array and then formatting the Excel-rows - no significant change of performance.

But i will try out the screen updating thing.

Stay tuned ;-)

Now i have to prepare my sons third birthday, 

Best regards

Daniel

[RichardL]

Comment out the colour setting line to show how much time is taken by the value read, and the colour setting.

Declare $col_val once at the top (every little helps), put the ".1" in another pre-declared var.

[junkew]

First determine all rownumbers you want to color and make it one long string below underlined just lines 1,4 and 10

worksheets(1).range("a1,a4,a10").entirerow.Interior.ColorIndex = 15

you can create dynamically first the whole string a1,a4,a10, a7500, a7999 and then in one call color all rows at once

I was never able to find out the excel range logic to be "1,4,10,7500,7999" so prefixed a in front of it

if there is a string length limit you should apply some logic to do it in a few steps

I assume the colorsetting itself is slow.

If the reading of the cells is then you should read it in blocks directly in an AutoIT array.

The excel calls are allways cross process boundary and as with any activex object thats relatively slow.

Edited June 22, 2014 by junkew