Announcement

Collapse
No announcement yet.

About LibXL (excel library for developers)

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • About LibXL (excel library for developers)

    Dear all,

    LibXL is a library that can read and write Excel files without Microsoft Excel; I use it with PowerBASIC with great success.

    For those of you that would be interested to test it (there is a demo version available here http://www.libxl.com) I would like to mention errors in the include files that are provided for PowerBASIC (at least until version 3.7.2); I mentioned these errors to the author, I hope they will made the corrections in a future version.

    13 declarations of function with "AS STRING" are not correct; "AS STRING" should be replaced with "AS DWORD" at the end of the declaration of these functions.

    Example of an incorrect declaration:
    Code:
    Declare Function xlSheetReadStr      Lib "libxl.dll" CDecl Alias "xlSheetReadStrA"         _
                                (ByVal sheet As SheetHandle, ByVal row As Long, ByVal col As Long, ByVal format As FormatHandle Pointer) AS STRING
    Corrected declaration
    Code:
    Declare Function xlSheetReadStr      Lib "libxl.dll" CDecl Alias "xlSheetReadStrA"         _
                                (ByVal sheet As SheetHandle, ByVal row As Long, ByVal col As Long, ByVal format As FormatHandle Pointer) As Dword
    In the program to retrieve the string value the user must follow these steps:

    Ansi version:
    Code:
    LOCAL lPtr    As StringZ Ptr
    LOCAL lString AS String
    lPtr = xlSheetReadStr(lSheet, lRow, lCol, 0)
    lString = @lPtr
    Unicode version:
    Code:
    LOCAL lPtr    As wStringZ Ptr
    LOCAL lString AS wString
    lPtr = xlSheetReadStr(lSheet, lRow, lCol, 0)
    lString = @lPtr
    You will find enclosed both include files (ansi & unicode) with the 13 declarations corrected.

    I hope it helps.
    Jean-Pierre
    Attached Files
    Jean-Pierre LEROY

  • #2
    Thanks, Jean-Pierre. Interesting!
    That said ... Either I am getting old, or I am not yet used to this new forum software (or a combination of both ..), but I don't see where I can download your attached include file ...
    Eddy

    Comment


    • #3
      I have also tested LibXL and are very happy with the result. Writing of Excel files is much faster than the methods that Microsoft provides.
      /Fim W.
      Fim Wästberg

      Comment


      • #4
        Originally posted by Eddy Van Esch View Post
        Thanks, Jean-Pierre. Interesting!
        That said ... Either I am getting old, or I am not yet used to this new forum software (or a combination of both ..), but I don't see where I can download your attached include file ...
        Hi Eddy, you should be able to see the attached files at the bottom of the post.
        Jean-Pierre LEROY

        Comment


        • #5
          Originally posted by Jean-Pierre LEROY View Post

          Hi Eddy, you should be able to see the attached files at the bottom of the post.
          Indeed, after changing some settings in my profile I can now see them.
          Thanks, Jean-Pierre!

          Eddy

          Comment


          • #6
            BIFF4 - This is further development of Paul Squires old BIFF 2.1. It's poorly annotated and no neat code. But it works

            BIFF.zip

            Code:
            '========================================================================
            ' BIFF 4     // Further development from Paul Squires original BIFF 2.1
            '========================================================================
            #COMPILE EXE
            #DIM ALL
            #INCLUDE "Excel.inc"
            
            
            '%XLSFALSE = 0
            '%XLSTRUE = NOT %XLSFALSE
            
            FUNCTION PBMAIN() AS LONG
            
                DIM mSheetName(3) AS STRING
                LOCAL HeaderLength AS LONG
                LOCAL starttime, X, endtime AS LONG
                GLOBAL TypSnitt() AS sfont
                LOCAL mFileName AS STRING
            
                 LOCAL i AS LONG
            
                starttime = TIMER
                'enable the buffer. This is optional, but enabling a buffer will speed
                'up file access.
            
                bib=""
                'stat& = xlsBuffer(%XLSTRUE, (512 * 1024))  'a 512K buffer
            
                'Create the new spreadsheet
                'mFileName = "testc.xls"  'create spreadsheet in the current directory
            
               '=====================================================================
               ' ********* BOOK ***********
               '=====================================================================
            
                mSheetName$(1)="Page1"
                mSheetName$(2)="Page2"
                mSheetName$(3)="Page3"
            
                ' Create the Excelbook
                mFileName$= bib & "TESTB.xlw"
                HeaderLength = xlsCreateBook(mFileName$, mSheetName$() )+1
                'HeaderLength= sum of the header length
            
                stat& = xlsSheetFont(0,"Arial", 10, %xlsNoFormat)
                stat& = xlsSheetFont(1,"Arial", 11, %xlsBold)
                stat& = xlsSheetFont(2,"Times New Roman", 12, %xlsItalic+%xlsBold)
                stat& = xlsSheetFont(3,"Courier New", 13, %xlsNoFormat)
            
                'Gridlines, headers, show0 as empty
                'stat& = xlsInitwin(%XLStrue, %XLStrue, %XLSfalse)
            
               '=====================================================================
               ' ********* SHEET 1 ***********
               '=====================================================================
            
              ' KILL mFileName$ ' Tillfälligt när bara en test sheet
               xlsNewSheet mSheetName$(1) & ".tmp", 1
                'xlsNewSheet mSheetName$(1) & ".xls", 1
                    stat& = xlsInitwin(%XLStrue, %XLStrue, %XLSfalse)
            
                     '? mv(1)
                    stat& = xlsWriteText("AaAaA", 5, 4, %xlsFont0, %xlsCellNormal)
                    stat& = xlsWriteText("some ", 6, 4, %xlsFont3, %xlsRightAlign)
                    stat& = xlsWriteText("BaBaBBBBBb", 7, 4, %xlsFont2, %xlsCentreAlign)
                    stat& = xlsWriteText("CaCaCesar", 8, 4, %xlsFont0, %xlsLeftAlign)
            
                    stat& = xlsWriteNumber(120.5, 5, 2, %xlsFont0, %xlsCellNormal, 2)
                    stat& = xlsWriteNumber(20.5, 6, 2, %xlsFont0, %xlsCellNormal, 2)
                    stat& = xlsWriteNumber(10.75, 7, 2, %xlsFont0, %xlsCellNormal, 2)
            
                    stat& = xlsWriteFormula("=B5+B6+B7", 8, 2, %xlsFont1, %xlsRightAlign,  2)
                  '  stat& = xlsWriteFormula("SUM(B5:B7)", 8, 2, %xlsFont1, %xlsRightAlign,  2)
            
                FOR i=1 TO 100
                    xlsWriteNumber(120.5, 10+i, 2, %xlsFont0, %xlsCellNormal, 2)
                NEXT
            
            
            
                stat&= xlsCloseSheet
            
            
               '=====================================================================
               ' ********* SHEET 2 ***********
               '=====================================================================
            
                xlsNewSheet mSheetName$(2) & ".tmp", 2
            
                     ' Gridlines, headers, show0 as empty
                    stat& = xlsInitwin(%XLStrue, %XLStrue, %XLSfalse)
            
                    stat& = xlsWriteText("BbBbB", 5, 4, %xlsFont0, %xlsLeftAlign)
                    stat& = xlsWriteText("Good", 6, 4, %xlsFont0, %xlsCentreAlign)
                    stat& = xlsWriteText("Practis", 7, 4, %xlsFont0, %xlsRightAlign)
            
                    FOR x = 1 TO 500
                        stat& = xlsWriteNumber(x/100, 2+x, 2, %xlsFont0, %xlsCellNormal, 2)
                    NEXT
                   ' stat& = xlsWriteNumber(12123.456, 7, 1, %xlsFont1, %xlsrightAlign + %xlsBottomBorder + %xlsShaded, %xlsCellNormal, 0)
            
                stat&= xlsCloseSheet
            
            
               '=====================================================================
               ' ********* SHEET 3 ***********
               '=====================================================================
                xlsNewSheet mSheetName$(3) & ".tmp", 3
                     ' Gridlines, headers, show0 as empty
                    stat& = xlsInitwin(%XLStrue, %XLStrue, %XLSfalse)
            
                    stat& = xlsSheetFont(0,"Arial", 10, %xlsNoFormat)             'font0
                    stat& = xlsSheetFont(1,"Times New Roman", 10, %xlsBold)                 'font1
                    stat& = xlsSheetFont(2,"Courier New", 10, %xlsBold + %xlsUnderline) 'font2
                    stat& = xlsSheetFont(3,"Courier", 18, %xlsItalic)             'font3
            
                    stat& = xlsWriteText("CcCcC", 5, 4, %xlsFont1, %xlsCellNormal)
                   ' mDate$ = "20150831"
                   ' stat& = xlsWriteDate(mDate$, 6,4, %xlsFont0, %xlsLeftAlign)
                stat&= xlsCloseSheet
            
            
            
                '=====================================================================
                xlsCloseBook(mFileName$, mSheetName$(), HeaderLength)
                endtime = TIMER
            
                MSGBOX "Excel BIFF Spreadsheet created (" & TRIM$(FORMAT$(endtime-starttime, "###.####")) & " seconds)" & $CRLF & "Filename: " & mFileName$,, "Excel API"
            END FUNCTION

            Comment


            • #7
              BIFF File Format as used in Excel Versions 2, 3, 4, 5, 95, 97, 2000, XP, 2003 PDF format, 250 pp

              Code based on BIFF or any specific file format (eg LibXL) may not be compatible with future versions of MS-Excel.

              e.g, the Excel "xlsx" format is NOT BIFF. (Excel 2007(?) and later)

              Using the Excel API is not dependent on format in use. (True of most APIs)

              MCM

              Michael Mattias
              Tal Systems Inc.
              Racine WI USA
              mmattias@talsystems.com
              http://www.talsystems.com

              Comment


              • #8
                @Janne @Michael : thank you both for your inputs regarding BIFF (Microsoft Excel File Format); I created this particular thread only to give some useful (I hope) information for those that would like to test or use LibXL library with PowerBASIC.

                @administrator : could be a good idea to move the last two posts to a new thread about BIFF (Microsoft Excel File Format).

                JPL
                Jean-Pierre LEROY

                Comment


                • #9
                  Dear all,

                  Attached to this post you will find enclosed two zip files:

                  The first file "includes PowerBASIC.zip" contains the modified include files to use with LibXL:

                  1. I corrected enum.inc (the keyword SINGULAR was missing in order to be able to use directly the numeric equates).
                  2. I made also another modification for the xlSheetSetCol() definition in both libxl.inc and libxlw.inc; the fourth parameter was missing in the original include files.

                  The second file "Examples PowerBASIC.zip" contains LibXL examples (custom, edit, extract, format, generate and invoice) converted from C to PowerBASIC in both version Ansi and Unicode.

                  I hope it helps.
                  Jean-Pierre
                  Attached Files
                  Jean-Pierre LEROY

                  Comment


                  • #10
                    Originally posted by Jean-Pierre LEROY View Post
                    Attached to this post you will find enclosed two zip files... I hope it helps.
                    Thank you Jean-Pierre for taking the time to share your work, I for one very much appreciate your effort. I'd shied away from Excel but I think it's time to get my feet wet


                    Regards,
                    Marc

                    Comment


                    • #11
                      Hi Marc,

                      Thank you for the kind words.

                      Jean-Pierre LEROY

                      Comment


                      • #12
                        Hello Jean Pierre Mancini, thanks for your library. I had to change
                        Code:
                        ENUM Color SINGULAR
                        to
                        Code:
                        ENUM ColorCC SINGULAR
                        (rename "color") to work with PBCC, which is my main platform but so far so good.

                        Comment

                        Working...
                        X