You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Richard Holmes <ri...@shedconsulting.co.uk> on 2010/03/01 11:38:26 UTC
Populating EXCEL Templates using configuration files
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
Re: Populating EXCEL Templates using configuration files
Posted by MSB <ma...@tiscali.co.uk>.
Not too sure I would advise this as I do not know enough about the format and
contents of the various files, but you can work on the xml directly. The
.xslx files are simply zipped archives that you can unzip and rezip (if that
is the term) with the toolset provided by the core java API. Just recently,
I wanted to play around with a particular setting for one of the style
objects, did not know how to gain access to that setting using Excel and so
bogded togehther a calss that would allow me to modify the xml directly.
Only a few lines of code are required to either open or resave the archive;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Enumeration;
import java.util.zip.ZipFile;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
/**
*
* @author win user
*/
public class HyperlinkImages {
private String sourceFilename = null;
private String destinationFolder = null;
private BufferedOutputStream destination = null;
private BufferedInputStream source = null;
private ZipEntry zipEntry = null;
private ZipFile zipFile = null;
private Enumeration<? extends ZipEntry> zipEntries = null;
private byte[] data = null;
private int count = 0;
private static final int BUF_SIZE = 2048;
public HyperlinkImages(String sourceFilename, String destinationFolder)
{
this.sourceFilename = sourceFilename.replaceAll("\\\\", "/");
this.destinationFolder = destinationFolder.replaceAll("\\\\", "/");
}
public void unzipFile() throws IOException {
File file = null;
String fileName = null;
int to = 0;
boolean result = false;
try {
zipFile = new ZipFile(this.sourceFilename);
zipEntries = zipFile.entries();
while(zipEntries.hasMoreElements()) {
zipEntry = (ZipEntry)zipEntries.nextElement();
source = new
BufferedInputStream(zipFile.getInputStream(zipEntry));
fileName = this.destinationFolder + "/" +
zipEntry.getName();
// If the file should be unzipped into a folder then.....
if(fileName.contains("/")) {
to = fileName.lastIndexOf("/");
// Get the name of the folder
file = new File(fileName.substring(0, to));
// If the folder DOES NOT already exit then.....
if(!file.exists()) {
// Try to create the folder.
result = file.mkdirs();
if(!result) {
// Throw an exception if the folder cannot be
created
throw new IOException("It was not possible to
create the following folder to unzip the archive into: " +
fileName.substring(0, to));
}
}
}
file = new File(fileName);
destination = new BufferedOutputStream(new
FileOutputStream(file), BUF_SIZE);
data = new byte[BUF_SIZE];
while((count = source.read(data, 0, BUF_SIZE)) != -1) {
destination.write(data, 0, count);
}
destination.flush();
destination.close();
destination = null;
source.close();
source = null;
}
}
finally {
try {
if(destination != null) {
destination.flush();
destination.close();
destination = null;
}
}
catch(Exception ex) {
// I G N O R E //
}
try {
if(source != null) {
source.close();
source = null;
}
}
catch(Exception ex) {
// I G N O R E //
}
}
}
public void zipFile(String zipFilename, String sourceFilename) throws
FileNotFoundException, IOException {
File outputFile = null;
File inputFile = null;
FileOutputStream fileOutputStream = null;
ZipOutputStream zipOutputStream = null;
try {
outputFile = new File(zipFilename);
inputFile = new File(sourceFilename);
fileOutputStream = new FileOutputStream(outputFile);
zipOutputStream = new ZipOutputStream(new
BufferedOutputStream(fileOutputStream, BUF_SIZE));
this.saveToFile(zipOutputStream, inputFile, sourceFilename);
}
finally {
if(zipOutputStream != null) {
try {
zipOutputStream.flush();
zipOutputStream.close();
}
catch(Exception ex) {
// I G N O R E //
}
}
if(outputFile != null) {
outputFile = null;
}
if(inputFile != null) {
inputFile = null;
}
}
}
public void editXML(String filename) throws Exception {
File file = null;
FileInputStream fis = null;
FileOutputStream fos = null;
StringBuffer buffer = null;
String contents = null;
int readFromFile = 0;
byte[] byteBuf = new byte[1024];
byte[] outputByteBuf = null;
try {
file = new File(filename);
fis = new FileInputStream(file);
buffer = new StringBuffer();
readFromFile = fis.read(byteBuf, 0, byteBuf.length);
while(readFromFile > -1) {
buffer.append(new String(byteBuf, 0, readFromFile));
readFromFile = fis.read(byteBuf, 0, byteBuf.length);
}
fis.close();
fis = null;
contents = buffer.toString().trim();
if(contents.contains("quotePrefix=\"1\"")) {
System.out.println("Found it.");
contents = contents.replace("quotePrefix=\"1\"",
"quotePrefix=\"1\"");
}
outputByteBuf = contents.getBytes();
fos = new FileOutputStream(file);
fos.write(outputByteBuf, 0, outputByteBuf.length);
}
catch(IOException ioEx) {
}
finally {
if(fos != null) {
try {
fos.flush();
fos.close();
fos = null;
}
catch(IOException ioEx) {
}
}
}
}
private void saveToFile(ZipOutputStream zipOutputStream, File
sourceFile, String sourceFilename) throws FileNotFoundException, IOException
{
BufferedInputStream bufferedInputStream = null;
FileInputStream fileInputStream = null;
File[] files = null;
String entryName = null;
try {
if(sourceFile.isDirectory()) {
files = sourceFile.listFiles();
for(File file : files) {
this.saveToFile(zipOutputStream, file, sourceFilename);
}
}
else {
this.data = new byte[BUF_SIZE];
this.count = 0;
entryName =
sourceFile.getAbsolutePath().substring(sourceFilename.length() + 1);
fileInputStream = new FileInputStream(sourceFile);
bufferedInputStream = new
BufferedInputStream(fileInputStream, BUF_SIZE);
this.zipEntry = new ZipEntry(entryName);
zipOutputStream.putNextEntry(zipEntry);
while((count = bufferedInputStream.read(data, 0, BUF_SIZE))
!= -1) {
zipOutputStream.write(data, 0, count);
}
}
}
finally {
if(bufferedInputStream != null) {
try {
bufferedInputStream.close();
}
catch(Exception ex) {
// I G N O R E //
}
}
}
}
}
I would not say that this is the best approach to adopt - just as an
example, strings are shared and how you would deal with the situation where
you wanted to change the value of one of the cells that shared a particular
value, I do not know - but it is possible to get at the raw xml.
As to the other two options you presented, I am sorry to say that I cannot
offer an opinion.
Yours
Mark B
PS Please ignore the editXML() method, that was me simply substituting
different values for one of a cell styles attributes.
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-tp27741955p27745456.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Populating EXCEL Templates using configuration files
Posted by Richard Holmes <ri...@shedconsulting.co.uk>.
Hi Mark,
Thank you for your help I will investigate this and see if the client thinks
it is suitable.
Regards
Richard
On 1 March 2010 15:56, Mark Fortner <ph...@gmail.com> wrote:
> Hi Richard,
> I usually just use Excel XML as a Velocity template and insert
> Velocity macro statements directly into the template using a text
> editor. It's pretty easy to do and Velocity is pretty well
> documented. This should help you get started:
> http://velocity.apache.org/
>
> Hope this helps,
>
> Mark
>
> On Monday, March 1, 2010, Richard Holmes <ri...@shedconsulting.co.uk>
> 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
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: Populating EXCEL Templates using configuration files
Posted by Mark Fortner <ph...@gmail.com>.
Hi Richard,
I usually just use Excel XML as a Velocity template and insert
Velocity macro statements directly into the template using a text
editor. It's pretty easy to do and Velocity is pretty well
documented. This should help you get started:
http://velocity.apache.org/
Hope this helps,
Mark
On Monday, March 1, 2010, Richard Holmes <ri...@shedconsulting.co.uk> 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
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Populating EXCEL Templates using configuration files
Posted by Richard Holmes <ri...@shedconsulting.co.uk>.
Thanks works perfectly.
On 16 March 2010 15:49, Nick Burch <ni...@alfresco.com> wrote:
> On Tue, 16 Mar 2010, Richard Holmes wrote:
>
>> Thank you this is what I was looking for, I am pretty new to this only
>> started using it a couple of weeks ago so don't know everything available.
>>
>
> http://poi.apache.org/poifs/index.html is a good starting point when
> working with POIFS - both the direct and event based stuff should be fairly
> well documented there to help :)
>
>
> Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: Populating EXCEL Templates using configuration files
Posted by Nick Burch <ni...@alfresco.com>.
On Tue, 16 Mar 2010, Richard Holmes wrote:
> Thank you this is what I was looking for, I am pretty new to this only
> started using it a couple of weeks ago so don't know everything available.
http://poi.apache.org/poifs/index.html is a good starting point when
working with POIFS - both the direct and event based stuff should be
fairly well documented there to help :)
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Populating EXCEL Templates using configuration files
Posted by Richard Holmes <ri...@shedconsulting.co.uk>.
Ahh perfect,
Thank you this is what I was looking for, I am pretty new to this only
started using it a couple of weeks ago so don't know everything available.
Many thanks
Richard
On 16 March 2010 15:43, Nick Burch <ni...@alfresco.com> wrote:
> On Tue, 16 Mar 2010, Richard Holmes wrote:
>
>> 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,
>>
>
> Why do you need to use the listener interface? Why not just get it all
> directly?
>
> eg
>
> POIFSFilesystem fs = new POIFSFileSystem(inp);
> HSSFWorkbook wb = new HSSFWorkbook(fs)
> InputStream xml = fs.createDocumentInputStream("XML");
> // SAX/Dom parsing of the xml follows
>
> Nick
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: Populating EXCEL Templates using configuration files
Posted by Nick Burch <ni...@alfresco.com>.
On Tue, 16 Mar 2010, Richard Holmes wrote:
> 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,
Why do you need to use the listener interface? Why not just get it all
directly?
eg
POIFSFilesystem fs = new POIFSFileSystem(inp);
HSSFWorkbook wb = new HSSFWorkbook(fs)
InputStream xml = fs.createDocumentInputStream("XML");
// SAX/Dom parsing of the xml follows
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Populating EXCEL Templates using configuration files
Posted by Richard Holmes <ri...@shedconsulting.co.uk>.
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 <ri...@shedconsulting.co.uk> 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 <ma...@tiscali.co.uk> 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 <ma...@tiscali.co.uk> 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 <ma...@tiscali.co.uk> 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 <ma...@tiscali.co.uk> 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: user-unsubscribe@poi.apache.org
>> >> >> >> For additional commands, e-mail: user-help@poi.apache.org
>> >> >> >>
>> >> >> >>
>> >> >> >
>> >> >> >
>> >> >>
>> >> >> --
>> >> >> 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: user-unsubscribe@poi.apache.org
>> >> >> For additional commands, e-mail: user-help@poi.apache.org
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >> --
>> >> 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: user-unsubscribe@poi.apache.org
>> >> For additional commands, e-mail: user-help@poi.apache.org
>> >>
>> >>
>> >
>> >
>>
>> --
>> 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: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>
Re: Populating EXCEL Templates using configuration files
Posted by Richard Holmes <ri...@shedconsulting.co.uk>.
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 <ma...@tiscali.co.uk> 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 <ma...@tiscali.co.uk> 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 <ma...@tiscali.co.uk> 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 <ma...@tiscali.co.uk> 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: user-unsubscribe@poi.apache.org
> >> >> >> For additional commands, e-mail: user-help@poi.apache.org
> >> >> >>
> >> >> >>
> >> >> >
> >> >> >
> >> >>
> >> >> --
> >> >> 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: user-unsubscribe@poi.apache.org
> >> >> For additional commands, e-mail: user-help@poi.apache.org
> >> >>
> >> >>
> >> >
> >> >
> >>
> >> --
> >> 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: user-unsubscribe@poi.apache.org
> >> For additional commands, e-mail: user-help@poi.apache.org
> >>
> >>
> >
> >
>
> --
> 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: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: Populating EXCEL Templates using configuration files
Posted by MSB <ma...@tiscali.co.uk>.
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 <ma...@tiscali.co.uk> 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 <ma...@tiscali.co.uk> 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 <ma...@tiscali.co.uk> 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: user-unsubscribe@poi.apache.org
>> >> >> For additional commands, e-mail: user-help@poi.apache.org
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >> --
>> >> 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: user-unsubscribe@poi.apache.org
>> >> For additional commands, e-mail: user-help@poi.apache.org
>> >>
>> >>
>> >
>> >
>>
>> --
>> 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: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>
>
--
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Populating EXCEL Templates using configuration files
Posted by Richard Holmes <ri...@shedconsulting.co.uk>.
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 <ma...@tiscali.co.uk> 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 <ma...@tiscali.co.uk> 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 <ma...@tiscali.co.uk> 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: user-unsubscribe@poi.apache.org
> >> >> For additional commands, e-mail: user-help@poi.apache.org
> >> >>
> >> >>
> >> >
> >> >
> >>
> >> --
> >> 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: user-unsubscribe@poi.apache.org
> >> For additional commands, e-mail: user-help@poi.apache.org
> >>
> >>
> >
> >
>
> --
> 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: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: Populating EXCEL Templates using configuration files
Posted by MSB <ma...@tiscali.co.uk>.
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 <ma...@tiscali.co.uk> 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 <ma...@tiscali.co.uk> 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: user-unsubscribe@poi.apache.org
>> >> For additional commands, e-mail: user-help@poi.apache.org
>> >>
>> >>
>> >
>> >
>>
>> --
>> 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: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>
>
--
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Populating EXCEL Templates using configuration files
Posted by Richard Holmes <ri...@shedconsulting.co.uk>.
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 <ma...@tiscali.co.uk> 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 <ma...@tiscali.co.uk> 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: user-unsubscribe@poi.apache.org
> >> For additional commands, e-mail: user-help@poi.apache.org
> >>
> >>
> >
> >
>
> --
> 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: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: Populating EXCEL Templates using configuration files
Posted by MSB <ma...@tiscali.co.uk>.
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 <ma...@tiscali.co.uk> 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: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>
>
--
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Populating EXCEL Templates using configuration files
Posted by Richard Holmes <ri...@shedconsulting.co.uk>.
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 <ma...@tiscali.co.uk> 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: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: Populating EXCEL Templates using configuration files
Posted by MSB <ma...@tiscali.co.uk>.
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org