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

[Zedna]

I translated and added some parameters to this function from Delphi sources on the web.

 

Function supports 1D and 2D arrays.

All array's data are converted to String datatype.

This function doesn't depend on installed Microsoft Excel!!

 

 

_ArrayToXLS(Const ByRef $avArray, $sFileName[, $Transpose = False[, $iStartRow = 0[, $iEndRow = 0[, $iStartCol = 0[, $iEndCol = 0]]]]])

 

Here it is also with simple example:

 

expandcollapse popup

#include <File.au3>
#include <WinAPI.au3>

Dim $myArray[6] = ['A','B','C','D','E','F']
_ArrayToXLS($myArray, @ScriptDir & '\test1.xls')
_ArrayToXLS($myArray, @ScriptDir & '\test1t.xls', True) ; transpose
_ArrayToXLS($myArray, @ScriptDir & '\test1x.xls', False, 2, 4) ; C,D,E
_ArrayToXLS($myArray, @ScriptDir & '\test1xt.xls', True, 2, 4) ; C,D,E transposed

Dim $myArray[2][3] = [['A','B','C'], ['D','E','F']]
_ArrayToXLS($myArray, @ScriptDir & '\test2.xls')
_ArrayToXLS($myArray, @ScriptDir & '\test2t.xls', True) ; transpose
_ArrayToXLS($myArray, @ScriptDir & '\test2x.xls', False, 1, 1) ; second row: D,E,F
_ArrayToXLS($myArray, @ScriptDir & '\test2xt.xls', True, 1, 1) ; second row: D,E,F transposed
_ArrayToXLS($myArray, @ScriptDir & '\test2y.xls', False, 0, 0, 1, 2) ; B,C + E,F
_ArrayToXLS($myArray, @ScriptDir & '\test2yt.xls', True, 0, 0, 1, 2) ; B,C + E,F transposed

$myArray = _FileListToArray(@ScriptDir, '*', 1)
_ArrayToXLS($myArray, @ScriptDir & '\test3.xls')
_ArrayToXLS($myArray, @ScriptDir & '\test3x.xls', False, 1) ; skip first row (contains number of files)
ShellExecute(@ScriptDir & "\test3x.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 save
;               $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

 

EDIT:

Here is original Delphi source

http://programujte.com/forum/vlakno/5645-jak-na-exel/

 

expandcollapse popup

procedure XlsWriteCellLabel(XlsStream: TStream; const ACol, ARow: Word; const AValue: string);
var
  L: Word;
const
  {$J+}
  CXlsLabel: array[0..5] of Word = ($204, 0, 0, 0, 0, 0);
  {$J-}
begin
  L := Length(AValue);
  CXlsLabel[1] := 8 + L;
  CXlsLabel[2] := ARow;
  CXlsLabel[3] := ACol;
  CXlsLabel[5] := L;
  XlsStream.WriteBuffer(CXlsLabel, SizeOf(CXlsLabel));
  XlsStream.WriteBuffer(Pointer(AValue)^, L);
end;

function SaveAsExcelFile(AGrid: TStringGrid; AFileName: string): Boolean;
const
  {$J+}
  CXlsBof: array[0..5] of Word = ($809, 8, 00, $10, 0, 0);
  {$J-}
  CXlsEof: array[0..1] of Word = ($0A, 00);
var
  FStream: TFileStream;
  I, J: Integer;
begin
  Result := False;
  FStream := TFileStream.Create(PChar(AFileName), fmCreate or fmOpenWrite);
  try
    CXlsBof[4] := 0;
    FStream.WriteBuffer(CXlsBof, SizeOf(CXlsBof));
    for i := 0 to AGrid.ColCount - 1 do
    for j := 0 to AGrid.RowCount - 1 do
        XlsWriteCellLabel(FStream, I, J, AGrid.cells[i, j]);
    FStream.WriteBuffer(CXlsEof, SizeOf(CXlsEof));
    Result := True;
  finally
    FStream.Free;
  end;
end;

 

and here is link to more general Delphi code where is handled also writing integer/decimal datatypes.

http://kurapaty.blogspot.com/2008/01/creating-excel-xls-from-delphi.html

 

EDIT2:

Here is link to topic in Examples for generating XML XLS (XLSX) without Excel installed from Jerome

 

EDIT3:

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

 

EDIT4:

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

 

EDIT5:

There is only one issue with XLS files generated this way: Badly formated national characters in cells when opened by Open Office/Libre Office.

Inside binary XLS file and also when 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.

 

EDIT6:

There is problem in MS Excel with data in cells bigger than 255 chars.

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

This 255 chars problem isn't in LibreOffice (version 3.4.1) only in Microsoft Excel.

See post #14, #15 for details

Edited July 5, 2015 by Zedna
changed title of topic

[ptrex]

@Zedna,

This is realy great !

Someone else had also demonstrated the creation of the native XLSX format.

So now we can handle both.

Thanks for sharing.

Rgds

ptrex

[UEZ]

Very nice work! Thanks for sharing!

Br,

UEZ

[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 August 14, 2011 by Zedna

[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 August 17, 2012 by skin27

[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.

[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 )

Edited August 17, 2012 by skin27

[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 )

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 :-)

[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)?

[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.

expandcollapse popup

#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

[Zedna]

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)?

No, your output file.

[taietel]

skin27, I've tested your script and it opens the generated test.xls. test.zip

[EDIT] Thanks Zedna!

Edited August 16, 2011 by taietel

[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

[Zedna]

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

#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 August 17, 2011 by Zedna

[Zedna]

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

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.

[Zedna]

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

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.

[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!

[Zedna]

Sorry but there is no fix (from me) and I don't know where is problem and how it can be fixed :-(

[FlashpointBlack]

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

[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 October 23, 2013 by Zedna