Update the Excel UDF that comes with AutoIt

[Myicq]

I am about to add functions to import text files into Excel.

Does anyone need a function to import fixed width files?

@water.. I know the Import CSV wizard is a part of Excel. But it COULD be argued that if the UDF offers possibility to insert data (by cell or by row), it's actually redundant to have this wizard. It may be just as good to work with CSV data as arrays in AutoIT and insert directly.

But perhaps for non-programmers, it would be good to have. And fixed-width data actually is still being used. At least my customers (in the printing industry) often use fixed-width typically for mailing actions. Yes, old habits never die.

Will there be an export function as well (_Excel_SaveAs_CSV($options...) ) ?

When you develop/test this, make sure to test in different system locale combinations, as I know Excel sometimes play games with you if you f.ex don't have the same Excel locale as your Windows system. You may f.ex experience that suddenly "," is preferred over ";" as separator etc.

Also try to test for unicode compatability and requirements on source encoding ? (yea I know you're busy. Just in case you get 3 rain days )

Will be happy to assist any way I can...

[water]

Hi Myicq,

the function to insert data lets you insert a string, an 1D or 2D array. The goal is to let the user insert data he created in his script etc.

The main purpose of the CSV import function is to read, parse and insert a CSV file. The difficult part is to parse the CSV file (different text qualifiers, number separators, column delimiters etc.). Inserting the data into Excel is the easy part.

So I think the functions aren't very redundant.

To create a function to import fixed width text files is more complex. I will have a look but can't promise anything.

To export an Excel sheet as CSV you use function _Excel_BookSaveAs and specify $xlCSVWindows as fileformat. A PDF export function will be available too.

Unfortunately I have only two PC's available to test. Windows XP with Office XP and Windows 7 with office 2010 - both german locale. So the testing with other locales needs to be done by the users of this forum.

The same is true for unicode tests. I can only test with German Umlauts.

I hope to soon release the Alpha 3 where you then can play with the _Excel_BookOpenText (CSV import) function (and all other functions as well)

[Myicq]

To create a function to import fixed width text files is more complex. I will have a look but can't promise anything.

Water,

did you come by these examples ?

Some C code to import CSV files programmatically

Some VBA code do import fixed-width CSV

Some more examples of the same

Seems like they set some 20 parameters + the array of widths. What is the best way (in terms of coding style), or preferred way, to transfer that many parameters to an AutoIT function ?

Eagerly await your next alpha

[water]

I used Excel's macro recorder to see what goes on under the covers when importing a fixed width file. I got the same "QueryTables.Add" result.

I'm sure some of the parameters are not needed or can be derived from a single parameter.

But I will first add the CSV import feature plus export to PDF before I release the next Alpha (hopefully this weekend).

[water]

Alpha 3 of the UDF has been released.

Please tell me what you think!

For download please see post #1.

[water]

Another question:

The current Excel UDF allows up to 4 parameters when a range needs to be passed as parameter (_ExcelFontSetproperties, _ExcelHorizontalAlignSet, _ExcelNumberFormat etc.). This makes a function call lengthy and quite unreadable.

I tend to reduce this to a single parameter: This could be either a range object, an A1 range ("A1:B17") or a string with two or four values (e.g. "1,3,3,5" = range from row one, column 3 to row 3 column 5). This would be converted to a range object so all functions work with ranges internally.

What do you think?

[THECARDNEXUS]

So, been testing out your V3.0 Its definetely faster at the tasks it currently has. That being said, its missing some pretty huge/basic functions I think. Like the ability to delete rows/columns. It also doesnt play nice with the current excel.au3 so I cant run both with #include and mix and match functions "as needed". From what i can tell its a Const issue, I wonder since so many are similar, if you could comment out the overlapping ones and #include the current excel to make the systems play nicely together? Also, just to make sure this is what it takes to open a file.

Global $sPriceFile = @DesktopDir & "nova systemtrade dataTCN3.csv"

Global $in = _Excel_Open()

$in = _Excel_BookOpen($oExcel, $sPriceFile)

The #in = _excel_open() thing was kind of confusing as I am not sure what that is, how it works or why its needed

Thanks for your assistance.

[water]

That being said, its missing some pretty huge/basic functions I think. Like the ability to delete rows/columns.

Correct. The version you downloaded is a very early alpha. The functional range hasn't been defined and will be enhanced upon sensible user request. Functions to insert/delete/move rows/columns will definitely be in the first beta version.

It also doesnt play nice with the current excel.au3 so I cant run both with #include and mix and match functions "as needed".

Correct. I had run a poll and people didn't have much problems with a complete rewrite of the UDF. The new UDF has a lot of design changes that lead to script breaking code.

This means: You can't mix the current Excel UDF and the new one!

Also, just to make sure this is what it takes to open a file.

The $in = _excel_open() thing was kind of confusing as I am not sure what that is, how it works or why its needed

One of the design changes was that every function does exactly what the name tells you it does. The current UDF does a lot of things under the covers.

To open an Excel workbook you now need to call this functions:

_Excel_Open - Connect to a running instance of Excel or create a new application object. Do all application related stuff (set visibility, screen updating, re-calculation setting ...)

_Excel_BookOpen - Open an existing Excel workbook and do some book related stuff

_Excel_BookClose - Close the book

_Excel_Close - Close the application

All clear?

[THECARDNEXUS]

Correct. The version you downloaded is a very early alpha. The functional range hasn't been defined and will be enhanced upon sensible user request. Functions to insert/delete/move rows/columns will definitely be in the first beta version.

Correct. I had run a poll and people didn't have much problems with a complete rewrite of the UDF. The new UDF has a lot of design changes that lead to script breaking code.

This means: You can't mix the current Excel UDF and the new one!

One of the design changes was that every function does exactly what the name tells you it does. The current UDF does a lot of things under the covers.

To open an Excel workbook you now need to call this functions:

_Excel_Open - Connect to a running instance of Excel or create a new application object. Do all application related stuff (set visibility, screen updating, re-calculation setting ...)

_Excel_BookOpen - Open an existing Excel workbook and do some book related stuff

_Excel_BookClose - Close the book

_Excel_Close - Close the application

All clear?

Gotcha, for some reason I thought I had a earlier copy that had a lot of the old functionality mixed with way higher read times on readtoarray and the like. Maybe you did and decided to completely scrap the old UDF post poll?

Yeah, I tried a few ways to #include both. Unfortunately all the scripts I would love to update use functions currently unavailable. Unfortunate for me because your new functions are WAY faster on read/write to array.

Gotcha on the excel calls. Makes a lot of sense, I am a novice coder(1 yr~ on AU3, nothing else rly). Thanks for the information.

Edited December 17, 2012 by THECARDNEXUS

[water]

The Excel poll thread can be found

As soon as I have released the Word UDF to the AutoIt source I hope to find some spare time to work on the Excel UDF again.

[Spiff59]

Probably four years ago I needed to pass through 14,000 spreadsheets and make the same format modification to each file.

I threw together a script using BookOpen and BookClose and fired off a test run. Ten minutes later I killed it when it had barely processed 1% of the files. I scrapped the Excel UDF and went with custom code that processed all 14K files in just over an hour. The UDF was truly unusable in a batch environment when opening or closing a workbook also meant starting and stopping the Excel application. I pretty much ignored the Excel UDF after that. I griped about it on the forum back then, a few times since, and often have recommended that others steer clear of it.

So, this is a long-overdue improvement, that will allow someone to loop through and process mass quantities of workbooks while only loading excel.exe once.

Looking good, Water

[nitekram]

small question - is there something different with 2007 vs 2010 versions for this UDF - the reason I mention it is - I used to be able to put the same file into an array and it would take on average 3.5 mins, now it is taking over 15 mins.

[water]

Nitekram,

I only have Excel 2010 available but would like to test performanace of such a large file. Can you provide more information (number of columns/rows etc.) or even the file itself?

Processing shouldn't take minutes.

[nitekram]

Using _ExcelReadSheetToArray()

File size is about 1000 - 1500 KB

About 700+ rows

55 columns

Sorry, unable to provide file.

[water]

I will do some tests with a workbook of this size and post the results.

[water]

Reading/writing a worksheet with 750 rows, 55 columns into/from an array takes about 1/4 second.

expandcollapse popup

#AutoIt3Wrapper_AU3Check_Stop_OnWarning=Y
#AutoIt3Wrapper_AU3Check_Parameters=-d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6

#include <Excel Rewrite.au3>
#include <Array.au3>

; Create application object
Global $oExcel = _Excel_Open()
If @error <> 0 Then Exit MsgBox(16, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_Excel_ErrorNotify(2)

Example1($oExcel)
Example2($oExcel)

_Excel_Close($oExcel, False)
Exit

; *****************************************************************************
; Example 1
; Write an array with 750 rows and 55 columns to a worksheet
; *****************************************************************************
Func Example1($oExcel)
    Local $aTab[751][55] = [[750, 55]]
    For $iIndex1 = 1 To UBound($aTab, 1) - 1
        For $iIndex2 = 0 To UBound($aTab, 2) - 1
            $aTab[$iIndex1][$iIndex2] = Random(1000000000,2000000000)
        Next
    Next
    _ArrayDisplay($aTab)
    Local $oWorkbook = _Excel_Booknew($oExcel)
    Local $iTime = TimerInit()
    _Excel_RangeWrite($oExcel, $oWorkbook, Default, $aTab, 1, 1, 1)
    MsgBox(0, "", "Writing an array with 750 rows and 55 columns to the worksheet took " & TimerDiff($iTime) & " milliseconds.")
    _Excel_BookSaveAs($oExcel, Default, @ScriptDir & "BigArray", $xlExcel8, True)
    _Excel_BookClose($oExcel, $oWorkbook)
EndFunc   ;==>Example1

; *****************************************************************************
; Example 2
; Read the active worksheet into an array
; *****************************************************************************
Func Example2($oExcel)
    Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "BigArray.xls")
    Local $iTime = TimerInit()
    Local $aTab = _Excel_RangeRead($oExcel, $oWorkbook, Default, "A1:BC750")
    MsgBox(0, "", "Reading the workbook with 750 rows and 55 columns to an array took " & TimerDiff($iTime) & " milliseconds.")
    _ArrayDisplay($aTab)
EndFunc   ;==>Example2

[water]

Processing 32760 rows with 225 columns takes about 32 seconds to write to the worksheet. To read this worksheet and create an array takes 90 milliseconds.

BTW: The script has to be run with AutoIt beta 3.3.9.2 or later.

[nitekram]

So this function _Excel_RangeRead is only in the BETA? Does this replace _ExcelReadSheetToArray()?

As I am unable to install anything on this work computer, is there a install to USB for BETA?

[water]

So this function _Excel_RangeRead is only in the BETA? Does this replace _ExcelReadSheetToArray()?

Correct. Correct.

As I am unable to install anything on this work computer, is there a install to USB for BETA?

Not that I know of. But there is a about to create a portable version of the production version. Maybe its possible to make the beta portable too?

[BrewManNH]

If you download the zipped beta version, you can expand it to a flash drive and start it the same way with the portable wrapper program. I have the beta and the production version on a thumb drive in different folders that I can run without ever installing anything.