Virgilio1

use DllCall

12 posts in this topic

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

Share this post


Link to post
Share on other sites



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

 

1 person likes this

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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 by Virgilio1

Share this post


Link to post
Share on other sites

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

 

Saludos

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

 

Saludos

Share this post


Link to post
Share on other sites

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 ?

 

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#10 ·  Posted

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

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


Can you give me a hand ?

Thanks so much

Share this post


Link to post
Share on other sites

#11 ·  Posted

You can do something like this.

 

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

Share this post


Link to post
Share on other sites

#12 ·  Posted

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

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