You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Ramon F Herrera <ra...@patriot.net> on 2010/04/23 17:50:30 UTC
Can we edit spreadsheets using XSSF?
In my experience, we can:
(1) Create and edit spreadsheets using HSSF.
(2) Create spreadsheets using XSSF.
All the examples I have found fail to edit a spreadsheet under XSSF.
Can somebody please provide a link to a working program that edits
XML-based spreadsheets?
TIA,
-Ramon
ps: See my thread "Trying to edit a spreadsheet using XSSF"
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Can we edit spreadsheets using XSSF?
Posted by MSB <ma...@tiscali.co.uk>.
Right, I did some digging and came across another thread where someone else
faced a problem with a class that was missing from the smaller version of
the ooxml archive and;
David wrote this reply;
"Hi Amedeo,
It is explained poorly on the site, but here is the deal.
There are two jar files for ooxml. One is the "lite" version that we hope
has full coverage of the parts that POI uses, and the other is the full, or
"heavy" version. The heavy version is much, much larger - around 10 MB.
Hopefully if you replace the poi-ooxml-schemas jar file with the
ooxml-schemas jar file you will be good. If this works and you can share a
version of your document then please submit a bugzilla and we can make sure
that later version of the "lite" jar included these parts of the schema.
We started this change at Apachecon last fall in response to a request of
the Tika project.
Regards,
Dave"
..and then this
"Hi Amedeo,
It is available in two ways. Either directly from a maven repository under
the tag 'ooxml-schemas' (http://poi.apache.org/overview.html), or you can
fetch it from maven using the build from the POI source distribution
(http://poi.apache.org/howtobuild.html) and find it in
./ooxml-lib/ooxml-schemas-1.0.jar
If anyone wants to suggest better documentation for this for the website,
please let us know.
Regards,
Dave"
So, it seems as though you will have to check out a copy of the larger ooxml
archive from the Maven repository. The bad news is that I have absolutly no
idea of how you would go about this sorry.
Yours
Mark B
Ramon F Herrera-2 wrote:
>
>
> In my experience, we can:
>
> (1) Create and edit spreadsheets using HSSF.
>
> (2) Create spreadsheets using XSSF.
>
> All the examples I have found fail to edit a spreadsheet under XSSF.
>
> Can somebody please provide a link to a working program that edits
> XML-based spreadsheets?
>
> TIA,
>
> -Ramon
>
> ps: See my thread "Trying to edit a spreadsheet using XSSF"
>
>
>
>
> ---------------------------------------------------------------------
> 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/Can-we-edit-spreadsheets-using-XSSF--tp28343163p28348572.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: Can we edit spreadsheets using XSSF?
Posted by MSB <ma...@tiscali.co.uk>.
Thanks Nick, will have a look later tonight.
Yours
Mark B
Nick Burch-11 wrote:
>
> On Mon, 26 Apr 2010, MSB wrote:
>> Sadly, I cannot build a 'complete' ooxml-schemas archive on the source
>> that
>> I checked out of SVN - ant reports that a file is missing - so do not
>> have
>> that archive to dig into, but it seems as though this is the cause of the
>> problem.
>
> Someone seems to have removed a couple of the depends lines from the ant
> build file for this stuff. I've sorted it out, it should now be able to
> download and compile everything for you
>
>> Do you have a copy of that larger ooxml-schemas archive on hand that you
>> could look into?
>
> Try the one from maven?
>
> Nick
>
> ---------------------------------------------------------------------
> 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/Can-we-edit-spreadsheets-using-XSSF--tp28343163p28373651.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: Can we edit spreadsheets using XSSF?
Posted by Nick Burch <ni...@alfresco.com>.
On Mon, 26 Apr 2010, MSB wrote:
> Sadly, I cannot build a 'complete' ooxml-schemas archive on the source that
> I checked out of SVN - ant reports that a file is missing - so do not have
> that archive to dig into, but it seems as though this is the cause of the
> problem.
Someone seems to have removed a couple of the depends lines from the ant
build file for this stuff. I've sorted it out, it should now be able to
download and compile everything for you
> Do you have a copy of that larger ooxml-schemas archive on hand that you
> could look into?
Try the one from maven?
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Can we edit spreadsheets using XSSF?
Posted by MSB <ma...@tiscali.co.uk>.
Hello Nick,
I have just had a look into the ooxml-schemas-1.0.jar archive that I had on
hand and it does contain a class called CTDfxs but I cannot find any
reference to the inner CTDfxs$1 class that Ramon reports POI is requesting.
Sadly, I cannot build a 'complete' ooxml-schemas archive on the source that
I checked out of SVN - ant reports that a file is missing - so do not have
that archive to dig into, but it seems as though this is the cause of the
problem. Do you have a copy of that larger ooxml-schemas archive on hand
that you could look into?
Yours
Mark B
Nick Burch-11 wrote:
>
> On Mon, 26 Apr 2010, Ramon F Herrera wrote:
>> Have you tried to edit an XSSF spreadsheet? That is what triggered the
>> problem.
>
> Yes, but generally only simpler spreadsheets
>
> Nick
>
> ---------------------------------------------------------------------
> 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/Can-we-edit-spreadsheets-using-XSSF--tp28343163p28367174.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: Can we edit spreadsheets using XSSF?
Posted by Nick Burch <ni...@alfresco.com>.
On Mon, 26 Apr 2010, Ramon F Herrera wrote:
> Have you tried to edit an XSSF spreadsheet? That is what triggered the
> problem.
Yes, but generally only simpler spreadsheets
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Can we edit spreadsheets using XSSF?
Posted by Ramon F Herrera <ra...@patriot.net>.
On 4/26/2010 3:54 AM, Nick Burch wrote:
> FWIW, I've not yet had a case of needing the full jar since we've had
> the small one, so it isn't very common a need
>
> Nick
Hi Nick,
Have you tried to edit an XSSF spreadsheet? That is what triggered the
problem.
See the simplest case below. It crashes at the end, creating an empty file.
Thx,
-Ramon
----------------------
package org.apache.poi.hssf.usermodel.examples;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
/**
* This example demonstrates opening a workbook, modifying
* it and writing the results back out.
* @author Glen Stampoultzis (glens at apache.org)
*/
public class ReadWriteWorkbook {
public static void main(String[] args) throws IOException {
FileInputStream fileIn = null;
FileOutputStream fileOut = null;
XSSFWorkbook wb;
try {
fileIn = new FileInputStream("1-Tab.xlsx");
wb = new XSSFWorkbook(fileIn);
Sheet sheet = wb.getSheetAt(0);
Row row = sheet.getRow(2);
if (row == null)
row = sheet.createRow(2);
Cell cell = row.getCell(3);
if (cell == null)
cell = row.createCell(3);
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cell.setCellValue("a test");
// Write the output to a file
fileOut = new FileOutputStream("workbookout.xlsx");
wb.write(fileOut); // Crashes here, creating an empty file
} finally {
if (fileOut != null)
fileOut.close();
if (fileIn != null)
fileIn.close();
}
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Can we edit spreadsheets using XSSF?
Posted by Nick Burch <ni...@alfresco.com>.
On Sun, 25 Apr 2010, MSB wrote:
> It might be worth posting a question to the list with this sort of title
> - 'How do I extract the complete OOXML archive from Maven?' Or 'How to
> extract individual archives from the Maven repository?' Something like
> thit should elicit a more cific response I would hope.
If you download the source version, or do an svn checkout, then ant has a
task (compile-ooxml-xsds) which will build the full jar for you
FWIW, I've not yet had a case of needing the full jar since we've had the
small one, so it isn't very common a need
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Can we edit spreadsheets using XSSF?
Posted by MSB <ma...@tiscali.co.uk>.
Hello Ramon,
I was worried that was still the problem and I cannot see how to do it but
am convinced it is possible - David would not have suggested it otherwise.
It might be worth posting a question to the list with this sort of title -
'How do I extract the complete OOXML archive from Maven?' Or 'How to extract
individual archives from the Maven repository?' Something like thit should
elicit a more cific response I would hope.
Yours
Mark B
Ramon F Herrera-2 wrote:
>
> On 4/24/2010 12:53 AM, MSB wrote:
>>
>> Morning Ramon,
>
> Afternoon :-)
>
>>
>> What problems are you having when you try to edit the Excel worksheet? I
>> have just had a look at you previous thread and it ended with a problem
>> concerning a class that may not have found it's way yet into the lighter
>> version of the OOXML archive. Are you still facing this problem?
>
> Yes. That is my one and only problem.
>
> I guess the question remains open.
>
> Thanks!
>
> -Ramon
>
>
>
> ---------------------------------------------------------------------
> 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/Can-we-edit-spreadsheets-using-XSSF--tp28343163p28361354.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: Can we edit spreadsheets using XSSF?
Posted by "Ramon F. Herrera" <ra...@patriot.net>.
On 4/24/2010 12:53 AM, MSB wrote:
>
> Morning Ramon,
Afternoon :-)
>
> What problems are you having when you try to edit the Excel worksheet? I
> have just had a look at you previous thread and it ended with a problem
> concerning a class that may not have found it's way yet into the lighter
> version of the OOXML archive. Are you still facing this problem?
Yes. That is my one and only problem.
I guess the question remains open.
Thanks!
-Ramon
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Can we edit spreadsheets using XSSF?
Posted by "Ramon F. Herrera" <ra...@patriot.net>.
On 4/24/2010 12:53 AM, MSB wrote:
>
> Morning Ramon,
Afternoon :-)
>
> What problems are you having when you try to edit the Excel worksheet? I
> have just had a look at you previous thread and it ended with a problem
> concerning a class that may not have found it's way yet into the lighter
> version of the OOXML archive. Are you still facing this problem?
Yes. That is my one and only problem.
I guess the question remains open.
Thanks!
-Ramon
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Can we edit spreadsheets using XSSF?
Posted by Ramon F Herrera <ra...@patriot.net>.
On 4/26/2010 9:56 AM, David Fisher wrote:
> Hi,
>
>> David does mention how to obtain the complete archive somewhere and once I
>> find it, I will post the link.
>
> All the details of loading build requirements including the "heavy" and full ooxml-schemas is in the build.xml available with the source download.
>
> Here is the direct link to the jar in the repository:
>
> http://repo1.maven.org/maven2/org/apache/poi/ooxml-schemas/1.0/ooxml-schemas-1.0.jar
>
> Regards,
> Dave
Eureka!
My program works great now...
Thanks to everybody!!!!
(specially Mark + Dave)
-Ramon
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Can we edit spreadsheets using XSSF?
Posted by David Fisher <df...@jmlafferty.com>.
Hi,
> David does mention how to obtain the complete archive somewhere and once I
> find it, I will post the link.
All the details of loading build requirements including the "heavy" and full ooxml-schemas is in the build.xml available with the source download.
Here is the direct link to the jar in the repository:
http://repo1.maven.org/maven2/org/apache/poi/ooxml-schemas/1.0/ooxml-schemas-1.0.jar
Regards,
Dave
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Can we edit spreadsheets using XSSF?
Posted by MSB <ma...@tiscali.co.uk>.
Morning Ramon,
What problems are you having when you try to edit the Excel worksheet? I
have just had a look at you previous thread and it ended with a problem
concerning a class that may not have found it's way yet into the lighter
version of the OOXML archive. Are you still facing this problem? If so, I
will have a search back through some older posts because I am certain that
David does mention how to obtain the complete archive somewhere and once I
find it, I will post the link.
If that is not the problem, can you please let us know just what problems
you are facing? The process should be as simple as this;
Create and InputStream attached to the Excel workbook.
Pass that reference to the constructor of the XSSFWorkbook class.
Close the InputStream - and null it out so that it is eligable for garbage
collection.
Use that object to retrieve referenbces to XSSFSHeet objects, from those get
XSSFRow objects and from those get XSSFCell objects.
Once you get down to that level, it is possible to get and set cell values,
get and set cell styles etc.
After you have finished editing the workbook, all you need to do is, create
an OutputStream that will allow you to 'write' the file back to storage and
then call the workbooks write method, pasing the OutputStream to it as the
single argument.
As you can see, the process mirrors exactly the one you would adopt if using
the HSSF stream and this is because many of the calsses in both stream share
common ancestors; the so called SS classes such as Workbook, Sheet, Cell,
CellStyle and so on.
Yours
Mark B
Ramon F Herrera-2 wrote:
>
>
> In my experience, we can:
>
> (1) Create and edit spreadsheets using HSSF.
>
> (2) Create spreadsheets using XSSF.
>
> All the examples I have found fail to edit a spreadsheet under XSSF.
>
> Can somebody please provide a link to a working program that edits
> XML-based spreadsheets?
>
> TIA,
>
> -Ramon
>
> ps: See my thread "Trying to edit a spreadsheet using XSSF"
>
>
>
>
> ---------------------------------------------------------------------
> 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/Can-we-edit-spreadsheets-using-XSSF--tp28343163p28348516.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