Tech Off Thread

7 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Spreadsheetml and something or other...

Back to Forum: Tech Off
  • User profile image
    t0rn

    Hello,

    I have a project, where I need to take an xls spreadsheet from a server and parse the data (a sea of badge numbers and stats data) so that I can then turn around and run conditional statements and filter out only the data that I want then parse that into human readable form. 

    I started by trying to import it into Access so that I can run SQL queries on the data but the problem with that is that there are multiple headings with the same name and I didn't want to have to edit them by hand (this is for a non technical management team).  My next thought was to save as an XML file and parse the data using Java...either DOM or SAX.  that didn't work either b/c the java implementation of DOM doesn't work with the namespaces that microsoft put into spreadsheetml.  

    Now, I am fairly new to C# but not to programming in general and I know the basics of C#...my question is are there any classes available that will parse data directly from a spreadsheetml format specifically into a format that I can run queries on (looped conditional statements and the like)?  I figured the best way to go would be to go to the source...since microsoft created the schema for spreadsheetml, i figured there would be at least a couple of classes able to parse the data and manipulate it using the .net framework. 

    Any suggestions?  Thank you in advance.  Also, if anyone thinks I am going about this the wrong way and knows of a better way please tell me...i've researched several solutions as you can see and am sort of at a wall at the moment...

    thank you,

    paul

  • User profile image
    t0rn

    Well, I can't say that I blame anyone for not responding.  This is turning out to be a major pain in the butt.  it may be my inexperience with xml but wow...i've hit a wall of sorts.  i have all of this data spread out in a spreadsheet and i want to run search routines on it and parse it into a readable format...  i tried xslt but found it lacking as the iterative processes of it are not very strong...i would end up just rewriting by hand which is very error prone...  the DOM implementation doesn't seem to like the namespaces that are used by the spreadsheetml schema (or again, maybe it is that i am not experienced enough)...i think i'm going to try again using xmlreader and use the forward-only approach and run standard if statements on the information...  if anyone has any suggestions to give it would be most appreciative.  i'm doin my homework but wanna make sure i'm going in the right direction Smiley

    later

    paul

  • User profile image
    Harlequin

    If you already have the SpreadsheetML, then you already have nicely formed Xml. Just load it up into an XmlDocument object and do your thing.

  • User profile image
    Harlequin

    Or if you need to work with the spreadsheet directly, then search google for:
    OWC11 spreadsheet

    Should get lots of sites to show you how to use the Office Web Components.

  • User profile image
    AndyC

    At the risk of sounding like a Luddite, if the data is static, have you considered just using a CSV file?

  • User profile image
    edefazio

    Good morning,

    If you are still open to the idea of using Java here are some open source alternatives I can see for you:

    If you are using SpreadsheetML:

    Check out the XELEM project (at sourceforge).  It's Java, Open Source and can read an write SpreadsheetML. 

    Binary:

    The POI HSSF project will handle Excel Binaries (and it has read capabilities and an event driven API.


    JExcel is similar:

    Cheers,

    Eric
    eric@workbeans.com


  • User profile image
    edefazio

    Follow Up... Forgot one:

    the XLSQL tool treats an Excel File as a Database table (I believe it uses POI HSSF (for excel binary files).  You can query the excel document as if it were a database and extract data that way.

    Hope one of these solutions works for you. (IMO XELEM looks like it would be the best fit with an event API and filters).

    Cheers,
    Eric
    eric@workbeans.com

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.