Announcement

Collapse
No announcement yet.

Parsing Excel .XLS files without using COM

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

  • Parsing Excel .XLS files without using COM

    I have been asked to provide support for data in .XLS files.
    I need to do this nativly as some people do not have Excel(!)

    Has anyone done this without using COM?



    [This message has been edited by Mike Trader (edited January 17, 2007).]

  • #2
    I believe that all Office documents (that are not using the new XML format) are OLE compound storage documents. Unless you're up for digging around in the Open Office code, COM would be your best bet here I think.

    ------------------
    Mike Stefanik
    www.catalyst.com
    Catalyst Development Corporation
    Mike Stefanik
    sockettools.com

    Comment


    • #3
      What is the "new" Excel format?

      Where do I find the documentation on the "OLE compound storage"?

      Im up for exploring the possability...
      I dont need to handle complicate worksheets with formulae, just
      a bunch of text/numbers/dates in cells.

      ------------------
      Kind Regards
      Mike

      Comment


      • #4
        Here's a link where Microsoft writes about structured storage and compound documents. In essence, it gives you the ability to write multiple "streams" of a data into a single file.
        http://msdn2.microsoft.com/en-us/library/aa380369.aspx

        And here's a link that discusses the Office Open XML formats:
        http://msdn2.microsoft.com/en-us/library/aa338205.aspx

        Expect some heavy reading there.

        Edit: Just to reiterate, unless you want to spend a signifiant chunk of your life digging around in there, using Excel's automation interface is really the best way to go. It does require that the user have Excel installed, but that's pretty much par for the course these days.

        ------------------
        Mike Stefanik
        www.catalyst.com
        Catalyst Development Corporation

        [This message has been edited by Mike Stefanik (edited January 15, 2007).]
        Mike Stefanik
        sockettools.com

        Comment


        • #5
           Also...

          Wotsit's Format at http://www.wotsit.org/search.asp
          Try "XLS" in search box

          Pierre

          Comment


          • #6
            Does it have to be .XLS, or can it be the Excel 2003 XML format?
            I did a project that had to create Word documents, and found
            that creating the WordML format was far faster and easier to manage
            than going the COM route. Note that Word loads the XML files as
            if they were .DOC files, I expect Excel would do the same for its
            XML format files.

            I've also used Jakarta POI to directly interact with Word docs, but
            it's a Java lib, which probably isn't what you're looking for:
            http://jakarta.apache.org/poi/




            ------------------

            --pdf
            --pdf

            Comment


            • #7
              look at this thread: http://www.powerbasic.com/support/pb...ad.php?t=28014

              it includes a short example that shows how to open an .xls file using
              the storage_openstorage function and how to get a pointer to the workbook's
              stream using the wrapper function istorage_openstream. then, is a metter of
              reading bytes with istream_read at the correct locations if you have the
              needed information.

              istorage and istream are standard low-level com interfaces available in
              all windows versions. so, you can read .xls files without excel, but
              using com.


              ------------------
              website: http://com.it-berater.org
              sed editor, typelib browser, com wrappers.
              forum: http://www.forum.it-berater.org
              Forum: http://www.jose.it-berater.org/smfforum/index.php

              Comment


              • #8
                If not <U>all</U> users have Excel installed, have the one user who does export it to delimited or fixed-field and let the other users operate on that.

                I'm no COM guy, but even I have a PB/COM routine here to 'ExportSheetToDelimited' (OK, so I cut and pasted it from here, I didn't write it myself. Big deal.)

                MCM


                Michael Mattias
                Tal Systems (retired)
                Port Washington WI USA
                [email protected]
                http://www.talsystems.com

                Comment


                • #9
                  Password incorrect... i just lost all my typing... aaagrh i hate
                  when that happens.

                  Basically
                  Pierre,
                  122 files..

                  Paul,
                  er.. not sure where your going with the XML... I have a .XLS file...

                  Michael
                  They dont communicate

                  Jose
                  as usual.. wow wow wow

                  The question is what is the file format??
                  Is it BIFF 8?


                  ------------------
                  Kind Regards
                  Mike

                  Comment


                  • #10
                    Mike,

                    If you had been using EZ-Post, you would not have lost any of your post.

                    Regards,
                    ~Bob
                    ----------------------
                    Composed with EZ-POST!


                    ------------------

                    Comment


                    • #11
                      Click on "preferences" (top-right) and check "Save user name and password for 1 year" (cookies must be enabled)

                      ------------------
                      kgpsoftware.com - Downloads
                      kgpsoftware.com - Development and Consulting
                      kgpsoftware.com | Slam DBMS | PrpT Control | Other Downloads | Contact Me

                      Comment


                      • #12
                        Paul,
                        Sorry, I was not aware of the new EXCEL formats...
                        http://blogs.msdn.com/excel/archive/...20/671995.aspx

                        To clarify, i am looking to work with the long standing
                        Excel 97 format

                        Added:

                        Jose,
                        on my machine "ExcelCom05.bas" results in EXCEL left running.
                        a Ctrl-Alt-Del shows a new instance each time I run this app taking
                        about 20,400k of memory.
                        I checked and the "Object CALL oExcel.Quit" is firing...
                        Could it just be my version is corrupted. It sometimes does not
                        open a workbook by Dbl Clicking on it. If you drag and drop it on
                        Excel it opens ok tho.

                        Added:
                        EXCEL 97 is BIFF8 file format it seems. The description of which
                        is this tome:
                        Microsoft Excel 97 developer's kit
                        (Goggle it)

                        I guess I'll continue this when it gets here...

                        [This message has been edited by Mike Trader (edited January 15, 2007).]

                        Comment


                        • #13
                          Can't you work with Excel via ODBC? I know I have a driver for it.
                          (I have Excel XP on here, but I believe the driver is installed by
                          default.)

                          Check out: http://support.microsoft.com/kb/178717

                          If you don't mind, I'd be really interested to know if it works for
                          you or not (if the driver is in fact installed on all computers).

                          Hope this helps,

                          John



                          ------------------
                          LOCAL MyEMail AS STRING , MySkype AS STRING
                          MyEmail = STRREVERSE$("letnitj") & CHR$(64) & STRREVERSE$("liamg") & CHR$(46) & STRREVERSE$("moc")
                          MySkype = STRREVERSE$("adirolftj")
                          LOCAL MyEMail AS STRING
                          MyEmail = STRREVERSE$("53pmohtj") & CHR$(64) & STRREVERSE$("liamg") & CHR$(46) & STRREVERSE$("moc")

                          Comment


                          • #14
                            Originally posted by Mike Trader:
                            I have been asked to provide support for data in .XLS files.
                            I need to do this nativly as some people do not have Excel(!)

                            (Does windows come without Excel anymore?)

                            Anyway, I guess the file format is OLE?

                            Is there any information on the format of these files anywhere?
                            Has anyone done this without using COM?

                            If the user doesn't have Excel, why do they want provision for the Excel format?

                            Could you save the data in CSV(Comma separated values). Excel could load it?

                            Or is the client receiving the data Excel, and you need to import the data into your application, with out using Excel?

                            There are many versions of Excel and the format has changed. What versions do you need to work with?
                            Would files be coming from the Mac? I think the format is slightly different from there as well. I could be wrong, it has been a long time since I used a Mac.




                            ------------------
                            Hope this helps.

                            Brian Chirgwin
                            brian at chirgwin dot net

                            Comment


                            • #15
                              I am receiveing a .XLS file over the internet.
                              The file happens to be Excel97 format.
                              (Assume the user does NOT have Excel installed)
                              It could be any format of course, but given the extended period
                              that this BIFF8 format has been deployed it will probably be all
                              I need.

                              The new XML format is probably not needed at this point.

                              I would like to do the conversion to a text file (.csv)
                              without having to write to disk.


                              I am surprised no one in the Basic world has written to this format
                              thus far.



                              ------------------
                              Kind Regards
                              Mike

                              Comment


                              • #16
                                Jose,
                                on my machine "ExcelCom05.bas" results in EXCEL left running.
                                a Ctrl-Alt-Del shows a new instance each time I run this app taking
                                about 20,400k of memory.
                                I checked and the "Object CALL oExcel.Quit" is firing...
                                Which program is it? I don't remember having written a program
                                called ExcelCom05.bas.

                                ------------------
                                Website: http://com.it-berater.org
                                SED Editor, TypeLib Browser, COM Wrappers.
                                Forum: http://www.forum.it-berater.org
                                Forum: http://www.jose.it-berater.org/smfforum/index.php

                                Comment


                                • #17
                                  Looks like you're going with BIFF, but FYI (or anyone else curious
                                  about the Excel + ODBC stuff)...

                                  "The Excel application does not need to be installed on the federated server. The Excel ODBC driver is automatically installed 4 with Microsoft Windows." http://publib.boulder.ibm.com/infoce...t/tlsodb13.htm

                                  ------------------
                                  LOCAL MyEMail AS STRING , MySkype AS STRING
                                  MyEmail = STRREVERSE$("letnitj") & CHR$(64) & STRREVERSE$("liamg") & CHR$(46) & STRREVERSE$("moc")
                                  MySkype = STRREVERSE$("adirolftj")
                                  LOCAL MyEMail AS STRING
                                  MyEmail = STRREVERSE$("53pmohtj") & CHR$(64) & STRREVERSE$("liamg") & CHR$(46) & STRREVERSE$("moc")

                                  Comment


                                  • #18
                                    > am receiveing a .XLS file over the internet.
                                    > ..(Assume the user does NOT have Excel installed)

                                    Silly plan; poor management decision.

                                    >I would like to do the conversion to a text file (.csv)
                                    >without having to write to disk.

                                    Pray how does one get a 'file' without writing to disk? (I won't even get into "why not?" Disk is your friend; use it).

                                    > I am surprised no one in the Basic world has written to this format thus far.

                                    Which format? Reading XLS files? That's only necessary if you have some kind of (silly) plan to accept proprietary 'XLS' format files without installing the application which manages them (Microsoft Excel)... so I'm not surprised at all.

                                    Besides, programmer-types being programmer-types, I am sure someone has done an application to read XLS-format files sans Excel; you just haven't found it yet.


                                    Michael Mattias
                                    Tal Systems (retired)
                                    Port Washington WI USA
                                    [email protected]
                                    http://www.talsystems.com

                                    Comment


                                    • #19
                                      I have used ODBC and Perfect Sync SQL Tools to read .XLS files.
                                      It does work without COM. Essentially an SQL statement
                                      can read the data. With some care I believe I was even able
                                      to update the file.

                                      It creates a nice abstraction layer something like:

                                      SELECT A1, A2 FROM WORKBOOK ....

                                      It did involved some care in creating the spreadsheet.

                                      One of many ways to solve the problem --- pick what you are
                                      comfortable using.


                                      ------------------
                                      Mark Strickland, CISSP
                                      SimplyBASICsecurity.com
                                      Mark Strickland, CISSP, CEH
                                      SimplyBASICsecurity.com

                                      Comment


                                      • #20
                                        looks like it can be done fairly easily without excel, using ado and
                                        the ole db provider as described by m$ here:
                                        http://support.microsoft.com/kb/257819

                                        luckily, we already have a pb example of using the ole db provider
                                        (for csv files) in this thread:
                                        http://www.powerbasic.com/support/pb...ad.php?t=13916

                                        shouldn't be terribly difficult to change it to work for .xls files...


                                        ------------------

                                        --pdf
                                        --pdf

                                        Comment

                                        Working...
                                        X