use DllCall

[Virgilio1]

Hello everyone,
I'm trying to use the libxlsxwriter.dl that lets you create an Excel file without having Excel installed on the machine.
http://libxlsxwriter.github.io/index.html the website is available in the documentation dll and   (This dll was found from http://www.rapideuphoria.com/libxlsxwriter.zip , it may not the latest version).
in https://autohotkey.com/boards/viewtopic.php?p=128209#p128209 forum I found an example for authokey and I'm trying to fit it all for autoit.
I wrote this short example:

Global $LibXslDll = 0

_XslStart("libxlsxwriter.dll")

$p = _OpenWorkBook("prova.xlsx")

_CloseWorkBook($p)


Func _XslStart($dll)
   $LibXslDll = DllOpen($dll)
   If $LibXslDll = -1 Then Return SetError(1, 0, 0)
    Return 1
 EndFunc

 Func _OpenWorkBook($filename)
    $result = DllCall($LibXslDll,"ptr:cdecl","new_workbook", "str",$filename)
    if @error Then
       ConsoleWrite(@error)
       return -1
    Else
     return $result[0]
   EndIf
 EndFunc

 Func _CloseWorkBook($Work)
    $result =  DllCall($LibXslDll,"int:cdecl","workbook_close", "ptr", $Work)
    if @error Then
       ConsoleWrite(@error)
    EndIf
EndFunc

I have no errors, but the file that creates does not have the correct name and is corrupt.
Can you help me?
thank you

[Danyfirex]

Hello. You need to do this:

#include <WinAPI.au3>
Global Const $CP_UTF8 = 65001
Global $LibXslDll = 0

Global $LibXslDll = 0

_XslStart("libxlsxwriter.dll")
Local $p = _OpenWorkBook("prova.xlsx")
_CloseWorkBook($p)


Func _XslStart($dll)
    $LibXslDll = DllOpen($dll)
    If $LibXslDll = -1 Then Return SetError(1, 0, 0)
    Return 1
EndFunc   ;==>_XslStart

Func _OpenWorkBook($filename)
    Local $tStringUTF8 = _WinAPI_WideCharToMultiByte($filename, $CP_UTF8, False)
    Local $pString = DllStructGetPtr($tStringUTF8)
    $result = DllCall($LibXslDll, "ptr:cdecl", "new_workbook", "ptr", $pString)
    If @error Then
        ConsoleWrite(@error)
        Return -1
    Else
        Return $result[0]
    EndIf
EndFunc   ;==>_OpenWorkBook

Func _CloseWorkBook($Work)
    $result = DllCall($LibXslDll, "int:cdecl", "workbook_close", "ptr", $Work)
    If @error Then
        ConsoleWrite(@error)
    EndIf
EndFunc   ;==>_CloseWorkBook

 

Saludos

 

[Virgilio1]

thank you very much for your availability.

Now the file is saved, but it does not have the correct structure

I added this function:

Func _AddWorkSheet($Work, $nome)
   Local $tStringUTF8 = _WinAPI_WideCharToMultiByte($nome, $CP_UTF8, False)
   Local $pString = DllStructGetPtr($tStringUTF8)
   $result = DllCall($LibXslDll,"ptr:cdecl","workbook_add_worksheet", "ptr", $Work, "ptr", $pString)
       If @error Then
        ConsoleWrite(@error)
        Return -1
    Else
        Return $result[0]
    EndIf
EndFunc

but I get an error that I do not understand.

Quote

[ERROR][packager.c:62]: Error opening zip file for xlsx
[ERROR][workbook.c:1108]: Memory allocation failed.

Can you tell me something?

Thanks again

Edited January 29, 2017 by Virgilio1

[Danyfirex]

Hello. Could you confirm that the AutoHotkey Script works? I think probably is a internal issue ( libxlsxwriter.dl issue) 

 

Saludos

[Virgilio1]

the library works, is safe.
I think it's a problem of how to step Adjustment parameters to functions.

this is the function that I found in the forum of Autohotkey

workbook_add_worksheet(workbook, sheetname) {
    StrPutVar(sheetname, _sheetname, "utf-8")
    return DllCall("libxlsxwriter\workbook_add_worksheet"
        , "ptr", workbook
        , "ptr", (sheetname="") ? 0 : &_sheetname
        , "cdecl ptr")
}

This is the definition in the language c :

lxw_worksheet* workbook_add_worksheet   (   lxw_workbook *  workbook,
const char *    sheetname 
)

I do not understand why it does not work ???

[Danyfirex]

I've checked AutoHotScript It  does not work. I think there is some issue in the library.

 

Saludos

[Virgilio1]

Thanks for your help.
It is a big problem.
I have to write a script that creates an excel file without Office package.
On the web I found several libraries An example is as follows: http://spreadsheetlight.com/download/

But the dll is compiled in c # and I do not think that you can use with autoit.
Do you have any idea ?

 

 

[Danyfirex]

I'm no sure if you can use that library. (maybe I'll check later). you could compile this version of  libxlsxwriter  https://github.com/jmcnamara/MSVCLibXlsxWriter proably It has the issue avobe fixed.

 

Saludos

[Virgilio1]

Thank you,
but by studying the site I found this:

Quote

The section outlines some answers to some frequently asked questions. 

Q. Can Libxlsxwriter use an existing Excel file as a template?

No. Libxlsxwriter is designed only as a file writer. It cannot read or modify an existing Excel file.

but I have some .xlsx files to compile, so this dll is not my case.

I am several days that I try a dll that particular case mine, but all of the dll written in C # and are not able to integrate with autoit !!!

it obnoxious that in PHP, JavaScript, vb.net, etc is very simple

[Virgilio1]

Hello,
after searching the net I decided to buy the site Libxl.dll (http://www.libxl.com/).
Now I would first try to write a wraper in autoit.
Here there is the documentation:  (http://www.libxl.com/documentation.html)
I tried to ascribe this short script

expandcollapse popup

Global $DllHandle

LoadDll()
$a = CreateBook()
SaveBook($a, @ScriptDir & "\Prova.xlsx")




Func LoadDll($dll ="")
   if $dll ="" Then
      $DllHandle = DllOpen("libxl.dll")
   Else
      $DllHandle = DllOpen($dll)
   EndIf
if $DllHandle = -1 Then
ConsoleWrite("Error Load Dll")
Exit
EndIf
EndFunc

Func CreateBook()
   Dim $rest = DllCall($DllHandle,"ptr*","xlCreateXMLBook")
   if @error Then
      ErrorTrace("Create")
   Else
      Return $rest[0]
   EndIf
EndFunc

Func SaveBook($book, $nome)
   Dim $rest = DllCall($DllHandle,"int","xlBookSave","ptr",$book,"char",$nome)
   if @error Then
      ErrorTrace("Save")
   EndIf
EndFunc

Func ErrorTrace($nome = "")
   ConsoleWrite("Nome " & $nome & " Error Number " & @error & @crlf)
   Exit
EndFunc

but it does not work.

calls in c are:

Quote

• BookHandle xlCreateXMLBook()
• int xlBookSave(BookHandle handle, const wchar_t* filename)

Can you give me a hand ?

Thanks so much

[Danyfirex]

You can do something like this.

 

expandcollapse popup

Global $__hLibXL = 0
Global Const $__ghLibXLName = "libxl.dll"


If _LibXL_Load() Then
    Local $sSaveFilePath=@ScriptDir & "\Prova.xlsx"
    Local $hBook = _LibXL_CreateBook()
    Local $hSheet = _LibXL_BookAddSheet($hBook, "Sheet1")
    _LibXL_SheetWriteStr($hSheet, 1, 0, "AutoIt Rocks")
    _LibXL_SheetWriteStr($hSheet, 2, 0, "Danyfirex Rocks")
    _LibXL_SheetWriteStr($hSheet, 3, 0, "Virgilio1 Rocks")
    _LibXL_BookSave($hBook,$sSaveFilePath)
    _LibXL_BookRelease($hBook)
EndIf




Func _LibXL_SheetWriteStr($hSheet, $iRow, $iCol, $sString, $hFormat = Null)
    Local $aRet = DllCall($__hLibXL, "int:cdecl", "xlSheetWriteStrW", "handle", $hSheet, "int", $iRow, "int", $iCol, "wstr", $sString, "handle", $hFormat)
    If @error Or Not $aRet[0] Then
        Return SetError(@error, @extended, 0)
    Else
        Return $aRet[0]
    EndIf
EndFunc   ;==>_LibXL_SheetWriteStr

Func _LibXL_CreateBook()
    Local $aRet = DllCall($__hLibXL, "handle:cdecl", "xlCreateXMLBookW")
    If @error Or Not $aRet[0] Then
        Return SetError(@error, @extended, 0)
    Else
        Return $aRet[0]
    EndIf
EndFunc   ;==>_LibXL_CreateBook


Func _LibXL_BookRelease($hBook)
    DllCall($__hLibXL, "none:cdecl", "xlBookReleaseW", "ptr", $hBook)
EndFunc   ;==>_LibXL_BookRelease


;~

Func _LibXL_BookAddSheet($hBook, $sSheetName, $hInitSheet = 0)
    Local $aRet = DllCall($__hLibXL, "handle:cdecl", "xlBookAddSheetW", "ptr", $hBook, "wstr", $sSheetName, "handle", $hInitSheet)
    If @error Or Not $aRet[0] Then
        Return SetError(@error, @extended, 0)
    Else
        Return $aRet[0]
    EndIf
EndFunc   ;==>_LibXL_BookAddSheet


Func _LibXL_BookSave($hBook, $sFileName)
    Local $aRet = DllCall($__hLibXL, "int:cdecl", "xlBookSaveW", "ptr", $hBook, "wstr", $sFileName)
    If @error Or Not $aRet[0] Then
        Return SetError(@error, @extended, 0)
    Else
        Return $aRet[0]
    EndIf
EndFunc   ;==>_LibXL_BookSave



Func _LibXL_Load()
    $__hLibXL = DllOpen($__ghLibXLName)
    Return $__hLibXL
EndFunc   ;==>_LibXL_Load

 

Saludos

[Virgilio1]

Thanks a lot, great job works well.

A job well deserves to be included among the UDF !!!

I also wrote these other functions that go well:

Func _LibXL_SheetWriteNum($hSheet, $iRow, $iCol, $Num, $hFormat = Null)
   Local $aRet = DllCall($__hlibXL, "int:cdecl","xlSheetWriteNumW","handle",$hSheet, "int", $iRow, "int", $iCol,"double",$Num,"handle", $hFormat)
   If @error Or Not $aRet[0] Then
        Return SetError(@error, @extended, 0)
    Else
        Return $aRet[0]
    EndIf
EndFunc

Func _LibXL_BookLoad($hBook, $sFileName)
   Local $aRet = DllCall($__hLibXL,"int:cdecl","xlBookLoadW","ptr",$hBook,"wstr",$sFileName)
    If @error Or Not $aRet[0] Then
        Return SetError(@error, @extended, 0)
    Else
        Return $aRet[0]
    EndIf
EndFunc

Func _LibXL_BookInsertSheet($hBook,$pos,$sSheetName, $hInitSheet = 0)
   local $aRet = DllCall($__hLibXL, "handle:cdecl","xlBookInsertSheetW","ptr",$hBook,"int",$pos,"wstr",$sSheetName, "handle", $hInitSheet)
    If @error Or Not $aRet[0] Then
        Return SetError(@error, @extended, 0)
    Else
        Return $aRet[0]
    EndIf
EndFunc

Func _LibXL_BookGetSheet($hBook, $pos)
   local $aRet = DllCall($__hlibXL,"handle:cdecl","xlBookGetSheet","ptr", $hBook, "int", $pos)
    If @error Or Not $aRet[0] Then
        Return SetError(@error, @extended, 0)
    Else
        Return $aRet[0]
    EndIf
EndFunc

While this feature is not to be:

Func _LibXL_SheetSetMerge($hSheet, $rowFirst, $colFirst, $rowLast, $colLast)
   Local $aRet = DllCall($__hLibXL, "int:cdecl","xlSheetSetMergeW","handle", $hSheet, "int", $rowFirst, "int", $colFirst, "int", $rowLast, "int", $colLast)
   If @error Or Not $aRet[0] Then
        Return SetError(@error, @extended, 0)
    Else
        Return $aRet[0]
    EndIf
EndFunc

Thanks again