Converting an XSSF model to an HSSF model

[Greg Inns]

Use Case: read in an XLSX file, convert it, and write out an XLS file.

It looks like both the HSSFWorkbook and the XSSFWorkbook both implement the
SS Workbook interface, but that's where it ends. In my mind, it make sense
to be able to construct a HSSFWorkbook from an XSSFWorkbook and visa versa,
although I would expect there might be some lost information in former case.

Am I missing something in the API documentation? Or something else?

[MSB]

Morning Greg,

Whilst I have had a very good look through the javadocs, I am by no means an
expert so treat what I have to say with caution please. Having said that, I
do not think there is an existing method within the POI API that you can use
to transcode (convert) between an OpenXML file and a binary (BIFF8) file.
You could use POI to create such a method however by stepping through the
input workbook - an XSSFWorkbook in your case I would guess - and creating
analogous objects within an HSSFWorkbook for everything you find there; if
your workbooks are fairly simple then this could be the way to go but if
they are more complex then that could involve some work.

You do have other options however and the best one - at least to my mind -
would be OLE/COM. If you are working on a stand alone PC and have Office
2007 installed on that machine, then you can use OLE to control an instance
of the Excel application and 'ask' it to convert the files for you. There
are plenty of libraries you could use to accomplish this task but the only
one I have any experience with is the ole32 package of the Standard Widget
Toolkit. The SWT is part of the Eclispe IDE, can be downloaded separately
and I have found it quite easy to work with. In fact, if you choose to go
this way, let me know and I will forward a couple of classes that will allow
you to use Excel to convert between different file types. You do need to be
aware however that this approach does have problems; it is hard to trap and
handle errors for example. Still, I think this is the beast approach as you
are using the application created by the authors of the file formats
involved.

A slightly modified version of this would be to use Excel macros to perform
the conversions for you. It is possible to create a VBA macro that would
open a file and save it in a different format. If you have lots of files to
process, you could enter their names into the cells of a worksheet and have
the macro iterate through the cells opening each file and then re-saving it
in the appropriate format. Of course, this also depeneds upon your having
access to Office 2007. I have never done this but it would be a simple task
to use the macro recorder feature to prototype the code and then simply
tweak it to do what you require.

The third option is OpenOffice. It uses filters to handle different file
types and by specifying the filter the application should use, it is
possible to convert between different file types. Again, it is possible to
control OpenOffice from Java code through a clearly defined - though complex
- interface. Using this technique again depened upon having OpenOffice and
it's associated SDK installed. Furthermore, I would advise you to use either
Eclipse or Netbeans as you IDE as both have a plugin that make developing
UNO client applications far easier. As with OLE, I have code that I can let
you have to accomplish this task. In my experience, OpenOffice is fine with
all but the more complex spreadsheet files; for example, it has problems
handling OpenXML spreadsheets that contain embedded objects and I am certain
there will be other limitations. My only concern with this technique is that
I have not yet been able to find the specific filter for opening and openXML
based workbook even thgouhg I know that Calc can read such files.

Finally, there are on-line conversion services available. This would be, to
my mind, a last resort as you would have to send your files across the
internet to a third party; the security implications are obvious.

Hope this helps.

Yours

Mark B

Post by Greg Inns
Use Case: read in an XLSX file, convert it, and write out an XLS file.
It looks like both the HSSFWorkbook and the XSSFWorkbook both implement
the SS Workbook interface, but that's where it ends. In my mind, it make
sense to be able to construct a HSSFWorkbook from an XSSFWorkbook and visa
versa, although I would expect there might be some lost information in
former case.
Am I missing something in the API documentation? Or something else?

[Lingjiao.Chen]

Hi Mark,

We are somewhat similar in situation. The second approach you purposed
sounds promising. Could you please forward the classes you've mentioned?

Thanks.

Post by MSB
Morning Greg,
Whilst I have had a very good look through the javadocs, I am by no means
an expert so treat what I have to say with caution please. Having said
that, I do not think there is an existing method within the POI API that
you can use to transcode (convert) between an OpenXML file and a binary
(BIFF8) file. You could use POI to create such a method however by
stepping through the input workbook - an XSSFWorkbook in your case I would
guess - and creating analogous objects within an HSSFWorkbook for
everything you find there; if your workbooks are fairly simple then this
could be the way to go but if they are more complex then that could
involve some work.
You do have other options however and the best one - at least to my mind -
would be OLE/COM. If you are working on a stand alone PC and have Office
2007 installed on that machine, then you can use OLE to control an
instance of the Excel application and 'ask' it to convert the files for
you. There are plenty of libraries you could use to accomplish this task
but the only one I have any experience with is the ole32 package of the
Standard Widget Toolkit. The SWT is part of the Eclispe IDE, can be
downloaded separately and I have found it quite easy to work with. In
fact, if you choose to go this way, let me know and I will forward a
couple of classes that will allow you to use Excel to convert between
different file types. You do need to be aware however that this approach
does have problems; it is hard to trap and handle errors for example.
Still, I think this is the beast approach as you are using the application
created by the authors of the file formats involved.
A slightly modified version of this would be to use Excel macros to
perform the conversions for you. It is possible to create a VBA macro that
would open a file and save it in a different format. If you have lots of
files to process, you could enter their names into the cells of a
worksheet and have the macro iterate through the cells opening each file
and then re-saving it in the appropriate format. Of course, this also
depeneds upon your having access to Office 2007. I have never done this
but it would be a simple task to use the macro recorder feature to
prototype the code and then simply tweak it to do what you require.
The third option is OpenOffice. It uses filters to handle different file
types and by specifying the filter the application should use, it is
possible to convert between different file types. Again, it is possible to
control OpenOffice from Java code through a clearly defined - though
complex - interface. Using this technique again depened upon having
OpenOffice and it's associated SDK installed. Furthermore, I would advise
you to use either Eclipse or Netbeans as you IDE as both have a plugin
that make developing UNO client applications far easier. As with OLE, I
have code that I can let you have to accomplish this task. In my
experience, OpenOffice is fine with all but the more complex spreadsheet
files; for example, it has problems handling OpenXML spreadsheets that
contain embedded objects and I am certain there will be other limitations.
My only concern with this technique is that I have not yet been able to
find the specific filter for opening and openXML based workbook even
thgouhg I know that Calc can read such files.
Finally, there are on-line conversion services available. This would be,
to my mind, a last resort as you would have to send your files across the
internet to a third party; the security implications are obvious.
Hope this helps.
Yours
Mark B

Post by Greg Inns
Use Case: read in an XLSX file, convert it, and write out an XLS file.
It looks like both the HSSFWorkbook and the XSSFWorkbook both implement
the SS Workbook interface, but that's where it ends. In my mind, it make
sense to be able to construct a HSSFWorkbook from an XSSFWorkbook and
visa versa, although I would expect there might be some lost information
in former case.
Am I missing something in the API documentation? Or something else?

[MSB]

Hello,

Can I ask, do you mean the OpenOffice UNO approach or the OLE/COM based one?
The reason I ask is that the two different methods have themselves different
requirements.

For example, to use 'my' OLE\COM classes, you will need to get your hands
the the SWT - not a problem becuase, if you use Eclipse on a Windows
platform then it is likely that the necessary classes are already included
in your distribution and if not then I can easilly send you the archive and
the necessary dlls. As SWT makes use of dlls it is limited to executing on
Windows platforms and is best run on a stand alone PC upon which Excel
itself is also installed. To date, I have not had any success trying to run
this code in a distributed environment but remain convinced that it has to
be possible.

The OpenOffice approach has similar requirements - you will need to have
OpenOffice installed and also to download and install the OpenOffice SDK.
Once you have the SDK, it is also worthwhile downloading one of the plugins
that are available for NetBeans and Eclipse. These make it far easier to
build UNO applications and I would certainly advise you to try and get one.

Anyway, for now, if you can just let me know which code you want -
OpenOffice UNO or OLE\COM and I can take it from there.

Yours

Mark B

Post by Lingjiao.Chen
Hi Mark,
We are somewhat similar in situation. The second approach you purposed
sounds promising. Could you please forward the classes you've mentioned?
Thanks.

Post by MSB
Morning Greg,
Whilst I have had a very good look through the javadocs, I am by no means
an expert so treat what I have to say with caution please. Having said
that, I do not think there is an existing method within the POI API that
you can use to transcode (convert) between an OpenXML file and a binary
(BIFF8) file. You could use POI to create such a method however by
stepping through the input workbook - an XSSFWorkbook in your case I
would guess - and creating analogous objects within an HSSFWorkbook for
everything you find there; if your workbooks are fairly simple then this
could be the way to go but if they are more complex then that could
involve some work.
You do have other options however and the best one - at least to my mind
- would be OLE/COM. If you are working on a stand alone PC and have
Office 2007 installed on that machine, then you can use OLE to control an
instance of the Excel application and 'ask' it to convert the files for
you. There are plenty of libraries you could use to accomplish this task
but the only one I have any experience with is the ole32 package of the
Standard Widget Toolkit. The SWT is part of the Eclispe IDE, can be
downloaded separately and I have found it quite easy to work with. In
fact, if you choose to go this way, let me know and I will forward a
couple of classes that will allow you to use Excel to convert between
different file types. You do need to be aware however that this approach
does have problems; it is hard to trap and handle errors for example.
Still, I think this is the beast approach as you are using the
application created by the authors of the file formats involved.
A slightly modified version of this would be to use Excel macros to
perform the conversions for you. It is possible to create a VBA macro
that would open a file and save it in a different format. If you have
lots of files to process, you could enter their names into the cells of a
worksheet and have the macro iterate through the cells opening each file
and then re-saving it in the appropriate format. Of course, this also
depeneds upon your having access to Office 2007. I have never done this
but it would be a simple task to use the macro recorder feature to
prototype the code and then simply tweak it to do what you require.
The third option is OpenOffice. It uses filters to handle different file
types and by specifying the filter the application should use, it is
possible to convert between different file types. Again, it is possible
to control OpenOffice from Java code through a clearly defined - though
complex - interface. Using this technique again depened upon having
OpenOffice and it's associated SDK installed. Furthermore, I would advise
you to use either Eclipse or Netbeans as you IDE as both have a plugin
that make developing UNO client applications far easier. As with OLE, I
have code that I can let you have to accomplish this task. In my
experience, OpenOffice is fine with all but the more complex spreadsheet
files; for example, it has problems handling OpenXML spreadsheets that
contain embedded objects and I am certain there will be other
limitations. My only concern with this technique is that I have not yet
been able to find the specific filter for opening and openXML based
workbook even thgouhg I know that Calc can read such files.
Finally, there are on-line conversion services available. This would be,
to my mind, a last resort as you would have to send your files across the
internet to a third party; the security implications are obvious.
Hope this helps.
Yours
Mark B

Post by Greg Inns
Use Case: read in an XLSX file, convert it, and write out an XLS file.
It looks like both the HSSFWorkbook and the XSSFWorkbook both implement
the SS Workbook interface, but that's where it ends. In my mind, it
make sense to be able to construct a HSSFWorkbook from an XSSFWorkbook
and visa versa, although I would expect there might be some lost
information in former case.
Am I missing something in the API documentation? Or something else?