Excel - Read Displayed Values of entire workbook

[BigDaddyO 63]

I'm often supplied spreadsheets from other departments that contain data to drive my scripts.  The problem that I often have is they like to apply cell formatting (Especially for Dates) which make it easy for them to use, but makes it crazy slow to open in my automation as I need to use the _Excel_RangeRead() with the Return option 3 and read one cell at a time.

For some reason, today I realized I could cheat a bit and made it so much easier and quicker.  I just opened the spreadsheet, then saved as a Tab delimited text file, then performed a _FileReadToArray() to get the actual displayed values.  works so much faster than reading cell by cell.

 

expandcollapse popup

#include <File.au3>
#include <Excel.au3>
#include <Array.au3>
Local $aRecords                                                         ;Will contain the data from the converted file

$hFileLoc = FileOpenDialog("Spreadsheet Data", @ScriptDir, "Excel (*.xls;*.xlsx)")  ;To select an Excel file
If @error Then Exit

$sDataFile = _TempFile(@TempDir, "~", ".txt")                           ;Create a temp file that will store the spreadsheet data
ConsoleWrite("CSV file = (" & $sDataFile & ")" & @CRLF)                 ;So I can view it manually while testing

;*** Spreadsheet formatted as Numbers, Currency, Dates, etc.
$oExcel = _Excel_Open(False, False, False, False, True)                 ;Open Excel a NEW excel app in Hidden mode
$oWorkbook = _Excel_BookOpen($oExcel, $hFileLoc, True, False)           ;Have Excel open the selected spreadsheet as Read Only
_Excel_BookSaveAs($oWorkbook, $sDataFile, $xlTextWindows, True)         ;Save as a Tab delimited text file; $xlCSVMSDOS for .csv
if @error Then
    MsgBox(0, "Error", "Failed to Open the Excel file for reading")
    Exit
EndIf
_Excel_BookClose($oWorkbook, False)                                     ;Close the selected spreadsheet
_Excel_Close($oExcel, False)                                            ;Close Excel
If IsObj($oExcel) Then $oExcel.Quit                                     ;Ensure the Excel actually closed

_FileReadToArray($sDataFile, $aRecords, 0, @TAB)                        ;Read the newly created Tab delimited file into an array
If @error Then
    MsgBox(0, "Error", "Failed to read the Excel data")
    Exit
EndIf

FileDelete($sDataFile)                                                  ;Remove the temp file since its in memory now

;This often brings over extra rows and columns so clean it up with this loop
For $r = 0 to UBound($aRecords) - 1
    If $aRecords[$r][0] = "" Then                                       ;If A is empty on this row then assume we have reached the end of data
        ReDim $aRecords[$r][10]                                         ;Remove extra rows, and in this case remove any columns after J
        ExitLoop
    EndIf
Next


_ArrayDisplay($aRecords, "Read Values")