Leaderboard

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: #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/ 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

Just a quick look...try $i = 1 to $sections[0] -1 8)

Hello jfish, Congratulations on a job done extremely well. I first came across AutoIt about 3 days after I bought this computer - the only previous experience I had with programming was with a mighty monster called a "VIC-20". I would have loved to have something like this when I started with AutoIt. I agree completely with your position that something more is needed for those who are just starting out. I can also add the simple fact that it doesn't hurt to have a straight forward reference around for people like myself who don't always remember the finer points. I'm just starting a new project with AutoIt and I'm looking forward to having your work handy. Once again jfish - Well Done! JAPP

Just open the "SciTE Director interface" page in the SciTE4AutoIt3 helpfile to start reading. (Search: Director) Jos

Yes, else a double UTF8 encoding occurs, which is getting messy to decode correctly.

Return $oHTTP.ResponseBody from the function. That's raw binary data. That way your original code should work with no other modifications.