Converting an XSSF model to an HSSF model

[Greg Inns <gi...@extol.com>]

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?


-- 
View this message in context: http://www.nabble.com/Converting-an-XSSF-model-to-an-HSSF-model-tp24113483p24113483.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: Converting an XSSF model to an HSSF model

[MSB <ma...@tiscali.co.uk>]

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


Lingjiao.Chen wrote:
> 
> 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.
> 
> 
> MSB wrote:
>> 
>> 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
>> 
>> 
>> Greg Inns wrote:
>>> 
>>> 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?
>>> 
>>> 
>>> 
>> 
>> 
> 
> 

-- 
View this message in context: http://www.nabble.com/Converting-an-XSSF-model-to-an-HSSF-model-tp24113483p25491993.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: Converting an XSSF model to an HSSF model

["Lingjiao.Chen" <li...@cloudnovations.com>]

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.


MSB wrote:
> 
> 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
> 
> 
> Greg Inns wrote:
>> 
>> 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?
>> 
>> 
>> 
> 
> 

-- 
View this message in context: http://www.nabble.com/Converting-an-XSSF-model-to-an-HSSF-model-tp24113483p25491916.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: Converting an XSSF model to an HSSF model

[MSB <ma...@tiscali.co.uk>]

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


Greg Inns wrote:
> 
> 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?
> 
> 
> 

-- 
View this message in context: http://www.nabble.com/Converting-an-XSSF-model-to-an-HSSF-model-tp24113483p24122575.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