Announcement

Collapse
No announcement yet.

Anyone have a SIMPLE example of how to READ cell values from EXCEL? XLSX...

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

  • Anyone have a SIMPLE example of how to READ cell values from EXCEL? XLSX...

    Anyone have a SIMPLE example of how to READ cell values from EXCEL?

    There are lots of complex examples of writing but I can't find a simple example of reading.

    Code:
    #COMPILE EXE
    #DIM ALL
    
    #INCLUDE ONCE "Excel.inc"
    
    FUNCTION PBMAIN () AS LONG
    
       LOCAL oExcelApp       AS Excel_Application
       LOCAL sFileName          AS STRING
       LOCAL vInFile            AS WSTRING
    
    
       sFileName = CURDIR$ & "\task.xlsx" '
    
       LET vInFile = sFileName ' do not forget drive/directory
    
       oExcelApp = ANYCOM $PROGID_Excel_Application
    
    
      IF ISTRUE ISOBJECT(oExcelApp) THEN
         MSGBOX "It is an object."
       END IF
    
    
    Terminate:
    
          ' Close the current document and then close EXCEL completely
    
          OBJECT CALL oExcelApp.ActiveWindow.Close
          OBJECT CALL oExcelApp.Quit
          SET oExcelApp = NOTHING
    
    END FUNCTION

  • #2
    David,

    This is an example I built up from one of my programs. I tested it with two variations of the Excel.inc, as yours seems to be older than mine.

    The Excel example file is attached for your convenience, and to demonstrate that the sheet to process does not need to be the first one and does not need to be the active one.

    I hope this helps.

    Code:
    #COMPILE EXE "C:\Temp\Excel-Demo-Read.exe"
    
    '#INCLUDE  "Excel14.inc"
    #INCLUDE  "C:\Files\Apps\Pb\PBWin10\samples\Objects\Excel\Excel.inc"
    
    FUNCTION PBMAIN AS LONG
      CALL Read_Excel("C:\Temp\Example.xlsx","Test")
    END FUNCTION
    
    
    FUNCTION Read_Excel(pXlsFile$,pXlsShet$) AS LONG
    '  DIM oExcelApp         AS Int__Application
      DIM oExcelApp         AS Excel_Application
      DIM vExcelWorkbook    AS VARIANT
    '  DIM oExcelWorkbook    AS Int__Workbook
      DIM oExcelWorkbook    AS Excel_Workbook
      DIM vExcelWorkSheet   AS VARIANT
    '  DIM oExcelWorkSheet   AS Int__WorkSheet
      DIM oExcelWorkSheet   AS Excel_WorkSheet
      DIM vInFile           AS VARIANT
      DIM vInSheet          AS VARIANT
      DIM vFalse            AS VARIANT
      DIM vRange            AS VARIANT
      DIM vExcelCellContent AS VARIANT
    
      ' Trick to create a true boolean variant
      LET vFalse=0 AS LONG
      POKE$ VARPTR(vFalse),MKWRD$(%VT_BOOL)
    
      IF DIR$(pXlsFile$)="" THEN
        MSGBOX "File not found."
        EXIT FUNCTION
      END IF
    
      ERRCLEAR
      oExcelApp = ANYCOM $PROGID_Excel_Application
      IF ISFALSE ISOBJECT(oExcelApp) OR ERR THEN
        MSGBOX "Can't open Excel application"
        EXIT FUNCTION
      END IF
    
      ' Open the Excel file:
      LET vInFile=pXlsFile$
      OBJECT CALL oExcelApp.WorkBooks.Open(Filename=vInFile,UpdateLinks=vFalse) TO vExcelWorkbook
      SET oExcelWorkbook = vExcelWorkbook
      IF ISNOTHING(oExcelWorkbook) THEN
        MSGBOX "Can't open the file. Excel is not responding."
        SET oExcelApp       = NOTHING
        EXIT FUNCTION
      END IF
    
      ' Check that the sheet exists:
      LET vInSheet=pXlsShet$
      OBJECT CALL oExcelApp.Sheets(vInSheet).Select
      IF ERR THEN
        MSGBOX "Sheet '" & pXlsShet$ & "' does not exist or is hidden."
        SET oExcelWorkbook  = NOTHING
        SET oExcelApp       = NOTHING
        EXIT FUNCTION
      END IF
    
      ' Activate the sheet:
      OBJECT GET oExcelApp.ActiveSheet TO vExcelWorkSheet
      SET oExcelWorkSheet = vExcelWorkSheet
      IF ISNOTHING(oExcelWorkbook) THEN
        MSGBOX "Sheet '" & pXlsShet$ & "' failed to be activated."
        SET oExcelWorkbook  = NOTHING
        SET oExcelApp       = NOTHING
        EXIT FUNCTION
      END IF
    
      ' Read data in a cell that contains a string:
      LET vRange="A1"
      OBJECT GET oExcelWorkSheet.Range(vRange).Value TO vExcelCellContent
      GOSUB Get_Cell_Data
    
      MSGBOX "String that was read:" & $CR & $CR & CellData$
    
      ' Read data in a cell that contains a number:
      LET vRange="C3"
      OBJECT GET oExcelWorkSheet.Range(vRange).Value TO vExcelCellContent
      GOSUB Get_Cell_Data
    
      MSGBOX "Number that was read:" & $CR & $CR & CellData$
    
      ' Close Excel and release the objects
      OBJECT CALL oExcelWorkbook.Close(SaveChanges=vFalse)
      SET oExcelWorkSheet = NOTHING
      SET oExcelWorkbook  = NOTHING
      SET oExcelApp       = NOTHING
    
      EXIT FUNCTION
    
      Get_Cell_Data:
        lvType&=VARIANTVT(vExcelCellContent)
        SELECT CASE AS LONG lvType&
        CASE 0,1
          ' Nothing or Null
          CellData$=""
        CASE 2 TO 7
          ' Number or date
          CellData$=FORMAT$(VARIANT#(vExcelCellContent))
        CASE 8
          ' String
          CellData$=VARIANT$(vExcelCellContent)
        CASE ELSE
          ' Other
          CellData$=""
        END SELECT
      RETURN
    
    END FUNCTION
    Attached Files
    Julien Tosoni - Goodyear France

    Comment


    • #3
      Julien Tosoni solution above looks great.

      Did you try Paul Squiires SLL?
      Post #26 http://www.planetsquires.com/files/xmlExcel.zip
      https://forum.powerbasic.com/forum/u...eadsheetml-xml

      Paul mentions in Post#11 GetCell method iCell = ws.GetCell(8,4)

      https://forum.powerbasic.com/forum/u...g-for-csv-file





      https://duckduckgo.com instead of google

      Comment


      • #4
        I got a copy of LIBXL and it is amazing!

        Comment


        • #5
          Thanks so much Julien, can your program work without Excel being installed in the machine?

          Comment


          • #6
            Hi Dave
            Regarding LibXL , it only caters for C , C++, C# and Delphi
            does it work with Powerbasic ?

            Comment


            • #7
              Anne,
              Unfortunately no, it can't run without Excel's COM interface installed. You'd get the "Can't open Excel application" error message trying to run the program.
              Julien Tosoni - Goodyear France

              Comment


              • #8
                That's good to know, thanks Julien

                Comment


                • #9
                  You can also use Jose's XMLLite Reader to read an XML file
                  please supply your own Test Excel.xml or excel file

                  https://forum.powerbasic.com/forum/j.../50982-xmllite

                  Code:
                  '
                  
                   ' It reads only the program attributes of the file and does not convert to anything else
                   ' Modified to print out to a text file Read dat.txt
                  
                  
                  ' ########################################################################################
                  ' Microsoft Windows
                  ' File: EX_XmlLiteReader.bas
                  ' Contents: XmlLite example
                  ' This example provides details of how to read an XML document by using XmlLite.
                  ' Compilers: PBWIN 10+, PBCC 6+
                  ' Headers: Windows API headers III+
                  ' Copyright (c) 2012 José Roca. Freeware. Use at your own risk.
                  ' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
                  ' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
                  ' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
                  ' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
                  ' ########################################################################################
                  
                  #COMPILE EXE
                  #DIM ALL
                  #INCLUDE ONCE "shlwapi.inc"
                  #INCLUDE ONCE "ocidl.inc"
                  #INCLUDE ONCE "xmllite.inc"
                  
                  ' ========================================================================================
                  ' Display the attributes
                  ' ========================================================================================
                  FUNCTION WriteAttributes (BYVAL pReader AS IXmlReader) AS LONG
                  
                     LOCAL hr AS LONG
                     LOCAL pwszPrefix AS WSTRINGZ PTR
                     LOCAL pwszLocalName AS WSTRINGZ PTR
                     LOCAL pwszValue AS WSTRINGZ PTR
                  
                     hr = pReader.MoveToFirstAttribute
                     IF hr = %S_FALSE THEN
                        FUNCTION = hr
                        EXIT FUNCTION
                     END IF
                  
                     IF hr <> %S_OK THEN
                        ? "Error moving to first attribute &H" & HEX$(hr)
                        FUNCTION = -1
                     END IF
                  
                     DO
                        IF ISFALSE pReader.IsDefault THEN
                           hr = pReader.GetPrefix(pwszPrefix)
                           IF FAILED(hr) THEN
                              ? "Error getting prefix &H" & HEX$(hr)
                              hr = -1
                              EXIT DO
                           END IF
                           hr = pReader.GetLocalName(pwszLocalName)
                           IF FAILED(hr) THEN
                              ? "Error getting local name &H" & HEX$(hr)
                              hr = -1
                              EXIT DO
                           END IF
                           hr = pReader.GetValue(pwszValue)
                           IF FAILED(hr) THEN
                              ? "Error getting value &H" & HEX$(hr)
                              hr = -1
                              EXIT DO
                           END IF
                  
                            ' Not needed as these are not data
                         '  IF pwszPrefix THEN
                          '    ? "Attr: " & @pwszPrefix & " " & @pwszLocalName
                         '  ELSE
                         '     ? "Attr: " & @pwszLocalName & " " & @pwszValue
                         '  END IF
                  
                        END IF
                        hr = pReader.MoveToNextAttribute
                        IF hr <> %S_OK THEN EXIT DO
                     LOOP
                  
                     FUNCTION = hr
                  
                  END FUNCTION
                  ' ========================================================================================
                  
                  
                  
                  ' ========================================================================================
                  ' Main
                  ' ========================================================================================
                  FUNCTION PBMAIN () AS LONG
                  
                     LOCAL hr AS LONG
                     LOCAL pFileStream AS IStream
                     LOCAL nodeType AS LONG
                     LOCAL pwszPrefix AS WSTRINGZ PTR
                     LOCAL pwszLocalName AS WSTRINGZ PTR
                     LOCAL pwszValue AS WSTRINGZ PTR
                     LOCAL nCount AS LONG
                  
                     ' // Open read-only input stream
                     ' Supply your own Test Excel.xml file here
                     hr = SHCreateStreamOnFile("Test Excel.xml", %STGM_READ, pFileStream)
                     IF FAILED(hr) THEN
                        ? "  File not found  SHCreateStreamOnFile error &H" & HEX$(hr)
                        EXIT FUNCTION
                     END IF
                  
                     ' // Create the reader
                     LOCAL pReader AS IXmlReader
                     hr = CreateXmlReader($IID_IXmlReader, pReader, NOTHING)
                     IF FAILED(hr) THEN
                        ? "CreateXmlReader error &H" & HEX$(hr)
                        EXIT FUNCTION
                     END IF
                  
                     ' // Set the DtdProcessing_Prohibit property
                     hr = pReader.SetProperty(%XmlReaderProperty_DtdProcessing, %DtdProcessing_Prohibit)
                     IF FAILED(hr) THEN
                        ? "Error setting XmlReaderProperty_DtdProcessing &H" & HEX$(hr)
                        EXIT FUNCTION
                     END IF
                  
                     ' // Set the input source of the XML document to be parsed
                     hr = pReader.SetInput(pFileStream)
                     IF FAILED(hr) THEN
                        ? "Error setting input for reader &H" & HEX$(hr)
                        EXIT FUNCTION
                     END IF
                  
                  
                  ' Print its contents to a text file
                    LOCAL jj AS LONG
                    jj = FREEFILE
                    OPEN "Read dat.txt" FOR OUTPUT AS #jj
                  
                  
                     ' // Parse the xml file
                     DO
                        hr = pReader.Read(nodeType)
                        IF hr <> %S_OK THEN EXIT DO
                        SELECT CASE AS LONG nodeType
                  
                           CASE %XmlNodeType_XmlDeclaration
                            '  ? "XmlDeclaration"
                              hr = WriteAttributes(pReader)
                              IF FAILED(hr) THEN
                                 ? "Error writing attributes &H" & HEX$(hr)
                                 EXIT DO
                              END IF
                  
                           CASE %XmlNodeType_Element
                              hr = pReader.GetPrefix(pwszPrefix)
                              IF FAILED(hr) THEN
                                 ? "Error getting prefix &H" & HEX$(hr)
                                 EXIT DO
                              END IF
                              hr = pReader.GetLocalName(pwszLocalName)
                              IF FAILED(hr) THEN
                                 ? "Error getting local name &H" & HEX$(hr)
                                 EXIT DO
                              END IF
                  
                              ' Not needed as these are not data
                            '  IF pwszPrefix THEN
                            '     ? "Element: " & @pwszPrefix & @pwszLocalName
                            '  ELSE
                            '     ? "Element: " & @pwszLocalName & @pwszValue
                            '  END IF
                  
                              hr = WriteAttributes(pReader)
                              IF FAILED(hr) THEN
                                 ? "Error writing attributes &H" & HEX$(hr)
                                 EXIT DO
                              END IF
                  
                               ' Not needed as these are not data
                             ' IF pReader.IsEmptyElement THEN
                             '    ? "Empty"
                             ' END IF
                  
                           CASE %XmlNodeType_EndElement
                              hr = pReader.GetPrefix(pwszPrefix)
                              IF FAILED(hr) THEN
                                 ? "Error getting prefix &H" & HEX$(hr)
                                 EXIT DO
                              END IF
                              hr = pReader.GetLocalName(pwszLocalName)
                              IF FAILED(hr) THEN
                                 ? "Error getting local name &H" & HEX$(hr)
                                 EXIT DO
                              END IF
                  
                             ' Not needed as these are not data
                            '  IF pwszPrefix THEN
                            '     ? "End element: " & @pwszPrefix & " " & @pwszLocalName
                            '  ELSE
                             '    ? "End element: " & @pwszLocalName & " " & @pwszValue
                            '  END IF
                              hr = WriteAttributes(pReader)
                              IF FAILED(hr) THEN
                                 ? "Error writing attributes &H" & HEX$(hr)
                                 EXIT DO
                              END IF
                             ' Not needed as these are not data
                            '  IF pReader.IsEmptyElement THEN
                            '     ? "Empty"
                            '  END IF
                  
                           CASE %XmlNodeType_Text, %XmlNodeType_Whitespace
                              hr = pReader.GetValue(pwszValue)
                              IF FAILED(hr) THEN
                                 ? "Error getting value &H" & HEX$(hr)
                                 EXIT DO
                              END IF
                              IF pwszValue THEN
                               '   ? "Text: " & @pwszValue
                                 PRINT #jj ,"Text: " & @pwszValue
                              END IF
                  
                           CASE %XmlNodeType_CDATA
                              hr = pReader.GetValue(pwszValue)
                              IF FAILED(hr) THEN
                                 ? "Error getting value &H" & HEX$(hr)
                                 EXIT DO
                              END IF
                              IF pwszValue THEN
                                  '? "CDATA: " & @pwszValue
                                    PRINT #jj , "CDATA: " & @pwszValue
                              END IF
                  
                           CASE %XmlNodeType_ProcessingInstruction
                              hr = pReader.GetLocalName(pwszLocalName)
                              IF FAILED(hr) THEN
                                 ? "Error getting local name &H" & HEX$(hr)
                                 EXIT DO
                              END IF
                              hr = pReader.GetValue(pwszValue)
                              IF FAILED(hr) THEN
                                 ? "Error getting value &H" & HEX$(hr)
                                 EXIT DO
                              END IF
                             ' Not needed as these are not data
                             ' IF pwszValue THEN ? "Processing Instruction name: " & @pwszLocalName & " value " & @pwszValue
                  
                           CASE %XmlNodeType_Comment
                              hr = pReader.GetValue(pwszValue)
                              IF FAILED(hr) THEN
                                 ? "Error getting value &H" & HEX$(hr)
                                 EXIT DO
                              END IF
                             ' Not needed as not data
                             ' IF pwszValue THEN ? "Comment: " & @pwszValue
                  
                           CASE %XmlNodeType_DocumentType
                              ? "DOCTYPE is not printed"
                  
                        END SELECT
                     LOOP
                  
                    CLOSE #jj
                  
                    ? " completed "
                  
                  END FUNCTION

                  Comment


                  • #10
                    Originally posted by Anne Wilson View Post
                    Hi Dave
                    Regarding LibXL , it only caters for C , C++, C# and Delphi
                    does it work with Powerbasic ?
                    Yes it Does....
                    Download Demo, Extract, and in Examples. You will find the Includes and some very simple samples.

                    Comment


                    • #11
                      Hi Anne:

                      Example:

                      Code:
                      #COMPILE EXE
                      #DIM ALL
                      
                      #INCLUDE "libxl.inc"
                      
                      FUNCTION PBMAIN
                      
                      
                          OPEN "out.txt" FOR OUTPUT AS #1
                      
                          LOCAL book AS BookHandle
                          LOCAL sheet AS SheetHandle
                      
                          LOCAL s_row AS LONG
                          LOCAL s_col AS LONG
                      
                          LOCAL s AS STRINGZ PTR
                      
                          book = xlCreateXMLBook()
                      
                       '  xlBookSetKey - only if you paid $$$ and have a key
                      
                          IF ISFALSE book THEN MSGBOX "book error"
                      
                              IF ISFALSE xlBookLoad(book, "task.xlsx") THEN MSGBOX "load book error"
                      
                                  sheet = xlBookGetSheet(book, 0)
                                  IF ISFALSE sheet THEN MSGBOX "sheet error"
                      
                      
                                  MSGBOX FORMAT$(xlSheetFirstRow(sheet))
                                  MSGBOX FORMAT$(xlSheetLastRow(sheet))
                                  MSGBOX FORMAT$(xlSheetFirstCol(sheet))
                                  MSGBOX FORMAT$(xlSheetLastCol(sheet))
                      
                      
                                 FOR s_row = xlSheetFirstRow(sheet) TO  xlSheetLastRow(sheet)
                      
                      
                                    FOR s_col = xlSheetFirstCol(sheet) TO xlSheetLastCol(sheet)
                      
                      
                                    s = xlSheetReadStr(sheet, s_row, s_col, 0)
                      
                                    PRINT#1, @s;
                      
                                    NEXT s_col
                                     PRINT#1, ""
                      
                                 NEXT s_row
                      
                              xlBookRelease(book)
                      
                           CLOSE #1
                      
                      END FUNCTION

                      Comment


                      • #12
                        Thanks so much Rod and Dave, the code in post #11 works like a charm
                        There is no need to convert xlsx file into an xml file.

                        Comment


                        • #13
                          Anytime Anne!

                          Comment

                          Working...
                          X