I have an Excel workbook with multiple sheets. Each sheet holds a table, the different tables have different formats. I need to read the entire workbook into my Java program. The most convenient method IMHO is to export the entire data into a single XML and parse it (using simpleXML or some other compatible parser). I have found no method for applying a schema to multiple sheets of a workbook, only to a single sheet. Is it possible? If so, how?
0
votes
1 Answers
1
votes
When it comes to convenience, there are many factors that influence or define it. For example, it depends if this is an ongoing thing, or if it needs to be integrated into a process, etc.
Before recommending a solution as suggested, I would try to convince you to take a look at Apache's POI (the Java API for Microsoft Documents), specifically the Excel API. It gives you a Java API for your Java program that should allow you to read what you need pretty easily. It would be a one stop shop kind of thing.
Another approach might be to use Jdbc to Odbc and access the Excel via JDBC API (JDBC to ODBC provider). I can't tell from details in your question if your deployment model would allow for this (e.g. if you run on a platform that doesn't have an ODBC provider for Excel files), but on Windows for sure is an option; also, many places on internet detailing this approach.
If you insist on going down the XML export way, QTAssistant (I am associated with it) has a comprehensive solution (XML Builder) for generating XML from any supported relational data source. It provides a GUI and a command line. In your case it would need the XLS, an XSD which describes the XML you want to get out and a mapping file (basically another XML file) to create the XML you need. In general this feature is largely used to convert test data into XML for Web service calls, so it is geared towards a certain interaction pattern between the user, the tool, and the XML generation activities. If you're interested in more details, let me know.
