Well sorted it, it was just a mistake in my code.
Currently there seems no way to get the binary data in Excel 2003 Properties
after loading, I may be wrong but can't see how to.
So for me the following worked
1) Load the Excel Workbook as normal for processing.
*this*.filesystem = *new* POIFSFileSystem(excelInputStream);
*this*.workbook = *new* HSSFWorkbook(*this*.filesystem);
2) Register a read listener on the Root directory for an entry called XML,
this is the entry that contains XML Schema that can be used to map data
between Excel sheets and an XML source. This reader gets the XML and stores
it.
xmlsource = *new* CMA_ExcelXMLHandler();
POIFSReader reader = *new* POIFSReader();
reader.registerListener(xmlsource, *this*.filesystem.getRoot().getPath(),
"XML");
reader.read(excelInputStream);
**
// This is the event in my XML Handler class.
*public* *void* processPOIFSReaderEvent(POIFSReaderEvent event) {
DocumentInputStream istream = event.getStream();
*try* {
*int* size = istream.available();
*this*.xmlSource = *new* *byte*[size];
istream.read(*this*.*xmlSource*);
}
*catch* ( IOException e ) {
// Do Nothing, shouldn't happen.
}
}
3) Alter the Read XML to add a new data binding, from a url or file. The
XML is in the format
<MapInfo SelectionNamespaces="">
<Schema ID="Schema1">
All the imported XSD schema converted to EXCEL format.
</Schema>
<Map ID="1" Name="intermodal_Map" RootElement="intermodal" ........>
<DataBinding ....... />
</Map>
</MapInfo>
It is the DataBinding that is of interest, it may not be there so I remove
it and re-add it with my new settings.
<DataBinding FileBinding="URL/Filename or Whatever.xml"
DataBindingLoadMode="1"/>
The DataBindingLoadMode does not work.
4) Get the XML Entry from the Root directory
ByteArrayInputStream bais = *this*.xmlSource.getXMLStream();
*if* ( bais != *null* ) {
*this*.filesystem.getRoot().getEntry("XML").delete();
*this*.*filesystem*.getRoot().createDocument("XML", bais);
bais.close();
}
The resulting Excel has a macro called Auto_Open which contains
ActiveWorkbook.RefreshAll
This all works a treat for me. It would be nice to get the XML Entry
directly from the loaded workbook and also use the Custom XML parts
available in EXCEL to attach the XML directly, but I havn't worked this out
yet.
This should work for Word too.
Regards
Richard
On 12 March 2010 18:09, Richard Holmes <[email protected]> wrote:
> Thank you,
>
> I have checked the code and there seems no reason this is not reading the
> full code so it may be that it is split in EXCEL due to size. I am sure I
> will find it.
>
> Thanks for trying to help.
>
> On 12 March 2010 15:22, MSB <[email protected]> wrote:
>
>>
>> Hello Richard,
>>
>> I understand now what it is that you are trying to accomplish but can
>> still
>> offer no help or advice I am afraid. It does seem strange that the
>> POIFSReader is failing to return all of the content but, as you are
>> reading
>> the binary formatted files, I think that the only option available to you
>> is
>> to use the POIFS classes; they wil take care of locating and extracting
>> the
>> data for you. As you may be aware, the binary files consist of a series of
>> streams that contain data and pointers to thata data. Finally, as if that
>> were not enough, the file is block structured so that it is difficult to
>> locate a extract any data without the help of the POIFS infrastructure.
>>
>> Sadly, I have absolutley no experience with the POIFSReader and the
>> eventfilesystem, all of my work with the api has been with the usermodel
>> and
>> I cannot offer any help at all I afraid. It may be worthwhile starting an
>> additional thread asking for help with the POIFSReader specifically in the
>> title. That is more likely, in my opinion, to attract the attention of
>> someone who has used this part of the api.
>>
>> Yours
>>
>> Mark B
>>
>>
>> Richard Holmes-2 wrote:
>> >
>> > Hi Mark,
>> >
>> > I am definatly using the Excel 2003 binary format not the XML format.
>> > What
>> > I am referring to is a little used feature introduced in Excel 2003
>> which
>> > allows you to embed XSD (XML Schema) inside Excel and map the XML schema
>> > to
>> > different areas in the worksheet.
>> >
>> > These mappings are stored as a root entry under the name XML and the
>> byte
>> > entry is the XML schema and bindings.
>> >
>> > Once I have read the sheet into HSSFWorksheet I can get to the root
>> > element
>> > XML using getEntry("XML") but there is no way of getting to the content
>> of
>> > the Element that I can see. So I am using the POIFSReader to extract
>> the
>> > XML Schema on load, but it seems that this does not retrieve all the XML
>> > entry only a portion of it.
>> >
>> > Is there another way of getting to the contents of the Entry? or is
>> there
>> > a
>> > reason that the XML is being truncated in the POIFSReader, I can not see
>> > any
>> > reason for this.
>> >
>> > Thanks
>> > Richard
>> >
>> > On 12 March 2010 07:35, MSB <[email protected]> wrote:
>> >
>> >>
>> >> Richard, I could very well be wrong so do not take my word as being the
>> >> last
>> >> statement of fact on this point but you are using the wrong tool to
>> parse
>> >> the xml. The POIFSFileSystem and everything related to it is aimed at
>> >> parsing the binary file format not the xml based file format. I would
>> >> suggest that you are using the wrong tool to process the file here as
>> the
>> >> OPCPackage classes and eveything related to them are usually used to
>> >> process
>> >> the xml based Excel files. Just as an example, take a look at the
>> >> constructors for the HSSFWorkbook class and the XSSFWorkbook class; the
>> >> former relate to the binary file format, the latter to the xml based
>> file
>> >> format.
>> >>
>> >> Can I ask, did you identify which version of the Microsoft xml file
>> >> format
>> >> you were working with? If you are having to work with the Office 2003
>> xml
>> >> file format then I would still argue that building your own parser
>> around
>> >> something like Xerces is the way to go.
>> >>
>> >> Of course, I could very well be wrong about this, I frequently am.
>> >>
>> >> Yours
>> >>
>> >> Mark B
>> >>
>> >>
>> >> Richard Holmes-2 wrote:
>> >> >
>> >> > So fare just getting the responses has helped me look elsewhere.
>> >> >
>> >> > By using the POIFSReaderListener i found that XML schema and
>> datastores
>> >> > are
>> >> > stored in the root filesystem under the name XML. Reading this gives
>> a
>> >> > standard Microsoft XML, can't find the link just now but it is
>> defined
>> >> as
>> >> > a
>> >> > schema part and a mapping part.
>> >> >
>> >> > I have been able to delete the XML entry in the root filesystem and
>> >> > replace
>> >> > with my modified version containing a new XML location and having a
>> >> macro
>> >> > Auto_open() to refresh XML data. I'll post more later about this.
>> >> >
>> >> > So the problem I am facing now is that when reading the XML using the
>> >> > POIFS
>> >> > reader
>> >> >
>> >> > DocumentInputStream istream = event.getStream();
>> >> > try {
>> >> > int size = istream.available();
>> >> > this.xmlSource = new byte[size];
>> >> > istream.read(this.xmlSource);
>> >> > }
>> >> > catch ( IOException e ) {
>> >> > // Do Nothing, shouldn't happen.
>> >> > }
>> >> >
>> >> > it seems to cut the xml source short so at the end i just get
>> "......"
>> >> I
>> >> > was
>> >> > wondering if there was a reason or if there was another way to get to
>> >> XML
>> >> > entry in the root?
>> >> >
>> >> > Thanks
>> >> > Richard
>> >> >
>> >> >
>> >> > On 2 March 2010 17:50, MSB <[email protected]> wrote:
>> >> >
>> >> >>
>> >> >> It sounds as though you may be looking at the older xml format then.
>> >> This
>> >> >> is
>> >> >> equally easy to test; you should be able to open the file using a
>> >> simple
>> >> >> text ediot - notepad for example if you are running under Windows.
>> >> >>
>> >> >> The best bet for working directly with the xml if this is the case
>> >> would
>> >> >> be
>> >> >> something like Xerces which would offer you the choice between a
>> >> stream
>> >> >> or
>> >> >> serial parser such as SAX and the document object model or DOM.
>> >> >>
>> >> >> Yours
>> >> >>
>> >> >> Mark B
>> >> >>
>> >> >> PS Your best source of information about this file format - if it is
>> >> >> indeed
>> >> >> the earlier version - will be Microsoft. They have a gooly number of
>> >> >> articles available at MSDN.
>> >> >>
>> >> >>
>> >> >> Richard Holmes-2 wrote:
>> >> >> >
>> >> >> > Hi Mark,
>> >> >> >
>> >> >> > Thank you for your help, I had a quick try of your method but
>> could
>> >> not
>> >> >> > open
>> >> >> > the sheet at all so I think we must be using a different format.
>> >> >> >
>> >> >> > Looking at the properties using the POI filesystem I see XML but
>> it
>> >> is
>> >> >> an
>> >> >> > unsupported property.
>> >> >> >
>> >> >> > For the time limit I am going to look at the other suggestion
>> using
>> >> >> > velocity.
>> >> >> >
>> >> >> > Many thanks
>> >> >> > Richard
>> >> >> > On 1 March 2010 16:11, MSB <[email protected]> wrote:
>> >> >> >
>> >> >> >>
>> >> >> >> Richard, I just re-read your post and wanted to ask whether you
>> >> knew
>> >> >> that
>> >> >> >> there were two different 'versions' of Excel's xml file format.
>> The
>> >> >> >> earlier
>> >> >> >> format was introduced for, I think, version 2003 and so you need
>> to
>> >> be
>> >> >> >> careful to identify just which 'version' of the xml file format
>> you
>> >> >> are
>> >> >> >> using. The one I am familiar with, and which is supported by POI,
>> >> is
>> >> >> the
>> >> >> >> format that is documented in ECMA376 and, as my earlier post
>> >> >> indicates,
>> >> >> >> is
>> >> >> >> a
>> >> >> >> zipped archive consisting of several related files and folders.
>> The
>> >> >> >> earlier
>> >> >> >> version uses a much simpler format and it is possible to identify
>> >> >> which
>> >> >> >> one
>> >> >> >> you have by opening the file using a simple text editor. If the
>> >> file
>> >> >> >> conforms to the earlier format then you will be able to open the
>> >> file
>> >> >> >> with
>> >> >> >> a
>> >> >> >> text editor and view the raw xml directly; obviously this will
>> not
>> >> be
>> >> >> the
>> >> >> >> case if you are dealing with the later - version 2007 and later -
>> >> >> version
>> >> >> >> of
>> >> >> >> the file format.
>> >> >> >>
>> >> >> >> Yours
>> >> >> >>
>> >> >> >> Mark B
>> >> >> >>
>> >> >> >> PS The code I included in my last post will NOT work for the
>> >> earlier
>> >> >> file
>> >> >> >> format.
>> >> >> >>
>> >> >> >>
>> >> >> >> Richard Holmes-2 wrote:
>> >> >> >> >
>> >> >> >> > Hi All,
>> >> >> >> >
>> >> >> >> > A client of mine is looking to export a java object model to
>> >> various
>> >> >> >> EXCEL
>> >> >> >> > sheets. These sheets are not of any particular format or
>> layout
>> >> and
>> >> >> >> they
>> >> >> >> > would like to be able to use some form of configuration files
>> to
>> >> >> >> populate
>> >> >> >> > the sheets how they wish, the EXCEL version being used is 2003.
>> >> >> >> >
>> >> >> >> > The options I am looking into are:
>> >> >> >> > 1) Use the XML features in EXCEL to populate the workbook - but
>> >> in
>> >> >> this
>> >> >> >> > case
>> >> >> >> > I can not find a way of updating the XML source in Java, in
>> .net
>> >> is
>> >> >> it
>> >> >> >> > ImportXML.
>> >> >> >> > 2) Use some sort of configuration take the java or XML object
>> and
>> >> >> >> populate
>> >> >> >> > the sheets/cells in Excel, I have tried jxls but this does not
>> >> have
>> >> >> the
>> >> >> >> > flexibility other than this I have come up short. (I am
>> looking
>> >> >> into
>> >> >> >> > using
>> >> >> >> > mono but I don't believe this is possible either).
>> >> >> >> > 3) Use EXCEL macros to re-format the data once in the sheet,
>> but
>> >> >> again
>> >> >> >> > this
>> >> >> >> > requires the users to perform actions, such as allowing macros
>> to
>> >> be
>> >> >> >> run.
>> >> >> >> > Some of our users find this a complex proceedure :)
>> >> >> >> >
>> >> >> >> > I have thought of writing my own engine but the deadlines are
>> too
>> >> >> >> short.
>> >> >> >> >
>> >> >> >> > If anyone has any ideas I would be very greatful, the easiest
>> for
>> >> me
>> >> >> >> would
>> >> >> >> > be to find a way of updating XML or find an engine that uses a
>> >> >> >> > configuration
>> >> >> >> > file to populate EXCEL.
>> >> >> >> >
>> >> >> >> > Many thanks
>> >> >> >> > Richard
>> >> >> >> >
>> >> >> >> >
>> >> >> >>
>> >> >> >> --
>> >> >> >> View this message in context:
>> >> >> >>
>> >> >>
>> >>
>> http://old.nabble.com/Populating-EXCEL-Templates-using-configuration-files-tp27741955p27745536.html
>> >> >> >> Sent from the POI - User mailing list archive at Nabble.com.
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> ---------------------------------------------------------------------
>> >> >> >> To unsubscribe, e-mail: [email protected]
>> >> >> >> For additional commands, e-mail: [email protected]
>> >> >> >>
>> >> >> >>
>> >> >> >
>> >> >> >
>> >> >>
>> >> >> --
>> >> >> View this message in context:
>> >> >>
>> >>
>> http://old.nabble.com/Populating-EXCEL-Templates-using-configuration-files-tp27741955p27758922.html
>> >> >> Sent from the POI - User mailing list archive at Nabble.com.
>> >> >>
>> >> >>
>> >> >>
>> ---------------------------------------------------------------------
>> >> >> To unsubscribe, e-mail: [email protected]
>> >> >> For additional commands, e-mail: [email protected]
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >> --
>> >> View this message in context:
>> >>
>> http://old.nabble.com/Populating-EXCEL-Templates-using-configuration-files-tp27741955p27874088.html
>> >> Sent from the POI - User mailing list archive at Nabble.com.
>> >>
>> >>
>> >> ---------------------------------------------------------------------
>> >> To unsubscribe, e-mail: [email protected]
>> >> For additional commands, e-mail: [email protected]
>> >>
>> >>
>> >
>> >
>>
>> --
>> View this message in context:
>> http://old.nabble.com/Populating-EXCEL-Templates-using-configuration-files-tp27741955p27877134.html
>> Sent from the POI - User mailing list archive at Nabble.com.
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [email protected]
>> For additional commands, e-mail: [email protected]
>>
>>
>
