Jump to content
Sign in to follow this  
Zedna

_ArrayToXLS() - save 1D/2D array to XLS without installed Excel

Recommended Posts

ptrex
UEZ

Very nice work! Thanks for sharing!

Br,

UEZ


Please don't send me any personal message and ask for support! I will not reply!

Selection of finest graphical examples at Codepen.io

The own fart smells best!
Her 'sikim hıyar' diyene bir avuç tuz alıp koşma!
¯\_(ツ)_/¯  ٩(●̮̮̃•̃)۶ ٩(-̮̮̃-̃)۶ૐ

Share this post


Link to post
Share on other sites
Zedna

Thanks for positive feedback guys!

I added original Delphi sources to first post.

EDIT:

Added link to topic in examples for generating XML XLS (XLSX) without Excel installed from Jerome

Added link to topic in Examples for reading Excel data using SQL (ADO) without Excel installed from ptrex

Added link to very nice description of Excel x ADO in Delphi

Accessing and managing MS Excel sheets with Delphi

http://delphi.about.com/od/database/l/aa090903a.htm

Edited by Zedna

Share this post


Link to post
Share on other sites
skin27

Hi Zedna, I like to use this function:

1) Can you check the declarations when using Opt('MustDeclareVars', 1). I get some undeclared errors.

2) I wrote an array to the sheet, but could not open it (2003 and 2010) because it was damaged (I attached the excel)

3) Is there an option to write the array to a specific sheet (either by string name or by number)

Edited by skin27

Share this post


Link to post
Share on other sites
Zedna

Hi Zedna, I like to use this function:

1) Can you check the declarations when using Opt('MustDeclareVars', 1). I get some undeclared errors.

2) I wrote an array to the sheet, but could not open it (2003 and 2010) because it was damaged (I attached the excel)

3) Is there an option to write the array to a specific sheet (either by string name or by number)

1) I don't use Opt('MustDeclareVars', 1) in any of my projects. If you want you can simply fix it, if you don't know how I can fix it for you.

2) My XLS files are OK on my Excel 2010 and also in Open Office/Libre Office, post your code for generating XLS, your file doesn't correspond to my UDF logic.

3) No it's not possible. I have got only limited examples (in Delphi) which don't supports sheets

EDIT:

Only the one issue with XLS files generated this way is badly formated national charceters in cells when opened by Open Office/Libre Office, inside XLS and opened by Microsoft Excel it's OK.

So I think it' some bug (maybe some bad autodetection of code page?) of Open Office/Libre Office.

Share this post


Link to post
Share on other sites
skin27

1) I fixed it locally, but I thought I just let you know.

2) Strange, that it opens at your place, but I can't open it here (MS Office). Here is how I tested writing the xsl. (I attached my test excel)

$oExcel = _ExcelBookOpen("C:\input.test.xls", 0, False)
_ExcelSheetActivate($oExcel, "Wijzigingen")
$excelArray = _ExcelReadSheetToArray($oExcel)
_ArrayToXLS($excelArray, "C:\test.xls")

3) I really would like not only to create a xsl, but also want to update, overwrite, create specific sheets (feature request :mellow:)

Edited by skin27

Share this post


Link to post
Share on other sites
Zedna

1) I fixed it locally, but I thought I just let you know.

2) Strange, that it opens at your place, but I can't open it here (MS Office). Here is how I tested writing the xsl. (I attached my test excel)

$oExcel = _ExcelBookOpen("C:\input.test.xls", 0, False)
_ExcelSheetActivate($oExcel, "Wijzigingen")
$excelArray = _ExcelReadSheetToArray($oExcel)
_ArrayToXLS($excelArray, "C:\test.xls")

3) I really would like not only to create a xsl, but also want to update, overwrite, create specific sheets (feature request :mellow:)

2) your file is corrupted and I can't open it on my machine in Office too.

3) There is XLS binary file format specification available

http://msdn.microsoft.com/en-us/library/gg615407.aspx

http://msdn.microsoft.com/en-us/library/cc313154.aspx

[MS -XLS].PDF: Excel Binary File Format (.xls) Structure Specification Excel

http://download.microsoft.com/download/2/4/8/24862317-78F0-4C4B-B355-C7B2C1D997DB/%5BMS-XLS%5D.pdf

Happy reading with 1200 pages :-)

Share this post


Link to post
Share on other sites
skin27

2) your file is corrupted and I can't open it on my machine in Office too.

Do you mean the input file, because that file I can normally open (only the output file not, which is made by the au3 function)?

Share this post


Link to post
Share on other sites
Zedna

2) Strange, that it opens at your place, but I can't open it here (MS Office). Here is how I tested writing the xsl. (I attached my test excel)

$oExcel = _ExcelBookOpen("C:\input.test.xls", 0, False)
_ExcelSheetActivate($oExcel, "Wijzigingen")
$excelArray = _ExcelReadSheetToArray($oExcel)
_ArrayToXLS($excelArray, "C:\test.xls")

I tested your input file on my machine and it generate correct valid XLS file.

#Include <Array.au3>
#include <File.au3>
#include <WinAPI.au3>
#include <Excel.au3>

$oExcel = _ExcelBookOpen("C:\2\input.test.xls", 0, False)
_ExcelSheetActivate($oExcel, "Wijzigingen")
$excelArray = _ExcelReadSheetToArray($oExcel)
_ArrayDisplay($excelArray)
_ArrayToXLS($excelArray, "C:\2\test_zedna.xls")


; #FUNCTION# ====================================================================================================================
; Name...........: _ArrayToXLS
; Description ...: Places the elements of an 1D or 2D array into an Excel file (XLS).
; Syntax.........: _ArrayToXLS(Const ByRef $avArray, $sFileName[, $Transpose = False[, $iStartRow = 0[, $iEndRow = 0[, $iStartCol = 0[, $iEndCol = 0]]]]])
; Parameters ....: $avArray - Array to combine
;               $sFileName  - Full path to XLS file
;               $Transpose  - [optional] At 2D array changes rows and columns
;               $iStartRow  - [optional] Zero based index (row) of array to start saving at
;               $iEndRow    - [optional] Zero based index (row) of array to stop saving at, if zero then last row is taken
;               $iStartCol  - [optional] Zero based index (column) of array to start saving at
;               $iEndCol    - [optional] Zero based index (column) of array to stop saving at, if zero then last column is taken
; Return values .: Success - 1
;               Failure - 0, sets @error:
;               |1 - $avArray is not an array
;               |2 - $avArray is not 1D/2D array
;               |3 - $iStartRow is greater than $iEndRow
;               |4 - $iStartCol is greater than $iEndCol
;               |5 - couldn't create XLS file
; Author ........: Zedna
; Modified.......:
; Remarks .......: Function supports 1D and 2D arrays. All array's data are converted to String datatype.
;               This function doesn't depend on installed Microsoft Excel.
; Related .......: _ArrayToString, _ArrayToClip
; Link ..........;
; Example .......; Yes
; ===============================================================================================================================
Func _ArrayToXLS(Const ByRef $avArray, $FileName, $Transpose = False, $iStartRow = 0, $iEndRow = 0, $iStartCol = 0, $iEndCol = 0)
    Local $nBytes

    If Not IsArray($avArray) Then SetError(1, 0, 0)
    $iDimension = UBound($avArray, 0)
    If $iDimension > 2 Then SetError(2, 0, 0)

    $iUBound1 = UBound($avArray, 1) - 1
    If $iEndRow < 1 Or $iEndRow > $iUBound1 Then $iEndRow = $iUBound1
    If $iStartRow < 0 Then $iStartRow = 0
    If $iStartRow > $iEndRow Then Return SetError(3, 0, 0)

    If $iDimension = 2 Then
        $iUBound2 = UBound($avArray, 2) - 1
        If $iEndCol < 1 Or $iEndCol > $iUBound2 Then $iEndCol = $iUBound2
        If $iStartCol < 0 Then $iStartCol = 0
        If $iStartCol > $iEndCol Then Return SetError(4, 0, 0)
    EndIf

    $hFile = _WinAPI_CreateFile($FileName, 1)
    If @error Then Return SetError(5, 0, 0)

    $str_bof = DllStructCreate('short;short;short;short;short;short')
    DllStructSetData($str_bof, 1, 0x809)
    DllStructSetData($str_bof, 2, 0x8)
    DllStructSetData($str_bof, 3, 0x0)
    DllStructSetData($str_bof, 4, 0x10)
    DllStructSetData($str_bof, 5, 0x0)
    DllStructSetData($str_bof, 6, 0x0)
    _WinAPI_WriteFile($hFile, DLLStructGetPtr($str_bof), DllStructGetSize($str_bof), $nBytes)

    Switch $iDimension
        Case 1 ; 1D array
            For $i = $iStartRow To $iEndRow ; 0 To $iUBound1
                If $Transpose Then
                    __XLSWriteCell($hFile, 0, $i - $iStartRow, $avArray[$i])
                Else
                    __XLSWriteCell($hFile, $i - $iStartRow, 0, $avArray[$i])
                EndIf
            Next

        Case 2 ; 2D array
            For $i = $iStartRow To $iEndRow ; 0 To $iUBound1
                For $j = $iStartCol To $iEndCol ; 0 To $iUBound2
                    If $Transpose Then
                        __XLSWriteCell($hFile, $j - $iStartCol, $i - $iStartRow, $avArray[$i][$j])
                    Else
                        __XLSWriteCell($hFile, $i - $iStartRow, $j - $iStartCol, $avArray[$i][$j])
                    EndIf
                Next
            Next
    EndSwitch

    $str_eof = DllStructCreate('short;short')
    DllStructSetData($str_eof, 1, 0x0A)
    DllStructSetData($str_eof, 2, 0x0)
    _WinAPI_WriteFile($hFile, DLLStructGetPtr($str_eof), DllStructGetSize($str_eof), $nBytes)

    _WinAPI_CloseHandle($hFile)
    Return 1
EndFunc ; ==> _ArrayToXLS

; internal helper function for _ArrayToXLS()
Func __XLSWriteCell($hFile, $Row, $Col, $Value)
    Local $nBytes

    $Value = String($Value)
    $Len = StringLen($Value)

    $str_cell = DllStructCreate('short;short;short;short;short;short')
    DllStructSetData($str_cell, 1, 0x204)
    DllStructSetData($str_cell, 2, 8 + $Len)
    DllStructSetData($str_cell, 3, $Row)
    DllStructSetData($str_cell, 4, $Col)
    DllStructSetData($str_cell, 5, 0x0)
    DllStructSetData($str_cell, 6, $Len)
    _WinAPI_WriteFile($hFile, DLLStructGetPtr($str_cell), DllStructGetSize($str_cell), $nBytes)

    $tBuffer = DLLStructCreate("byte[" & $Len & "]")
    DLLStructSetData($tBuffer, 1, $Value)
    _WinAPI_WriteFile($hFile, DLLStructGetPtr($tBuffer), $Len, $nBytes)
EndFunc  ; ==> __XLSWriteCell

Here is output file:

test_zedna.xls.zip

Share this post


Link to post
Share on other sites
skin27

I did manage to write a correct test.xls now. Maybe the first time it was interrupted when writing the xls to a networkshare (see Report.html). Thanks for your help.

Report.html

Share this post


Link to post
Share on other sites
Zedna

Now I noticed there is problem with data in cells bigger than 255 chars. :mellow:

#include <String.au3>

; 250 chars in cell OK
Dim $myArray[6] = ['A','B','C','D','E','F']
$myArray[1] = _StringRepeat('1234567890',25)
_ArrayToXLS($myArray, @ScriptDir & '\testn25.xls')

; 260 chars in cell - doesn't show
Dim $myArray[6] = ['A','B','C','D','E','F']
$myArray[1] = _StringRepeat('1234567890',26)
_ArrayToXLS($myArray, @ScriptDir & '\testn26.xls')

For data > 255 chars Excel loads XLS file without errors but these cells are empty.

Length of data in cell is writen into file as number of short datatype (2 bytes). I checked with Hex editor the value in XLS file and it seems to be OK.

I even tried to swap these 2 bytes in file with Hex editor (little versus big endian?) but it was still the same (data didn't load).

I don't know if there is some Excel limitation for length of data in cell but 2 bytes number (short) can store value 65536.

If somebody have got some tip for that problem please give me help ...

Edited by Zedna

Share this post


Link to post
Share on other sites
Zedna

Now I noticed there is problem with data in cells bigger than 255 chars. :mellow:

For data > 255 chars Excel loads XLS file without errors but these cells are empty.

Now I noticed this 255 chars problem isn't in LibreOffice (version 3.4.1).

Cells containing 260 chars are shown normally.

Share this post


Link to post
Share on other sites
Zedna

Now I noticed there is problem with data in cells bigger than 255 chars. :mellow:

For data > 255 chars Excel loads XLS file without errors but these cells are empty.

Length of data in cell is writen into file as number of short datatype (2 bytes). I checked with Hex editor the value in XLS file and it seems to be OK.

I even tried to swap these 2 bytes in file with Hex editor (little versus big endian?) but it was still the same (data didn't load).

Now I tested to use ushort datatype instead of short but the result is the same (also XLS is binary identical), so this problem remains.

Share this post


Link to post
Share on other sites
FlashpointBlack

Hi Zedna! I've been using your UDF for awhile now and I've actually just come across the issue that you referenced... yipes. two years ago. I was just wondering if you (or anyone) had any luck correcting this problem? I'd be very curious to know! this is one of my most used UDFs by far!

I really appreciate your (and everyone's) effort here.

Sorry for drudging up something so old, I'm just living on a prayer that this has been silently addressed. Thanks!

Share this post


Link to post
Share on other sites
FlashpointBlack

I'm sorry to hear that. Thanks very much for the reply!

Share this post


Link to post
Share on other sites
Zedna

I have idea about analyzing source of problem.

Somebody with Delphi installed can try if this (255 char limit) problem occurs also in original Delphi version.

If not then problem is in my Delphi2AutoIt translation (more probable) or some AutoIt bug.

This is only idea how to isolate problem

I have neither installed Delphi nor time/mood to test it/do it myself ...

Edited by Zedna

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×