It is time for me to retire I think. Ignore last message and simply try this;
public static void main(String[] args) {
File file = null;
FileOutputStream fos = null;
HSSFWorkbook workbook = null;
HSSFSheet sheet = null;
HSSFRow row = null;
HSSFCell cell = null;
HSSFCellStyle wrapStyle = null;
try {
file = new File("C:/temp/Wrap Test.xls");
fos = new FileOutputStream(file);
workbook = new HSSFWorkbook();
wrapStyle = workbook.createCellStyle();
wrapStyle.setWrapText(true);
sheet = workbook.createSheet("Wrap Demo.");
row = sheet.createRow(0);
cell = row.createCell(0);
cell.setCellValue("This is a slightly longer String.");
cell.setCellStyle(wrapStyle);
workbook.write(fos);
}
catch(IOException ioEx) {
System.out.println("Caught: " + ioEx.getClass().getName());
System.out.println("Message: " + ioEx.getMessage());
System.out.println("Stacktrace follows:............");
ioEx.printStackTrace(System.out);
}
finally {
if(fos != null) {
try {
fos.close();
}
catch(IOException ioEx) {
// I G N O R E
}
}
}
}
The key is the cell style object, it ensures that the long String will be
wrapped in the cell, that the 'breaks' occur at the word boundaries and
Excel itself ensures that the row is shown expanded to the correct height to
accomodate the cells contents.
Yours
Mark B
Michael L-3 wrote:
>
> Is there a way to format the string put in a cell so that it will attempt
> to fit
> in the visible dimensions of the cell? Currently, if I put a string whose
> length
> is longer than that of the cell's width, the string is cut off and can
> only be
> read if the reader manually extends the width of the cell. I guess what I
> really
> want is for the string to go to a new line in the cell when it reaches the
> end
> of the cell's width. Is this possible using POI?
>
>
> ---------------------------------------------------------------------
> 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/String-formatting-in-cells--tp26649280p26654385.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
I am not certain just what your requirements/constraints are so this suggestion may be useless; can you not simply increase the width of the cell so that it is able to accomodate the String? Obvisouly, this means that the width of the column will be set to accomodate the longest String but there is already a method in the API to accomplish just this task. Take a look at the autoSizeColumn() method that is defined on both of the HSSFSheet and XSSFSheet classes. Typically, you use this by iterating through all of the columns on a sheet immediately prior to saving the workbook away. To answer your original question, yes it is possible to recover the width of the column, find out how long the String is, insert a carriage return/carriage returns at the appropriate place(s) in the String, write the String way to the cell and set the cells format so that it's contents wrap but this could present you with other problems; what if the row was not high enough? Then the users would have to scroll the contents of the cell up and down within the cell to view them fully. Of course you can increase the rows' height but it may be that setting the columns width is the easiest, neatest answer if this fits your requirement. Yours Mark B Michael L-3 wrote: > > Is there a way to format the string put in a cell so that it will attempt > to fit > in the visible dimensions of the cell? Currently, if I put a string whose > length > is longer than that of the cell's width, the string is cut off and can > only be > read if the reader manually extends the width of the cell. I guess what I > really > want is for the string to go to a new line in the cell when it reaches the > end > of the cell's width. Is this possible using POI? > > > --------------------------------------------------------------------- > 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/String-formatting-in-cells--tp26649280p26653493.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
Is there a way to format the string put in a cell so that it will attempt to fit in the visible dimensions of the cell? Currently, if I put a string whose length is longer than that of the cell's width, the string is cut off and can only be read if the reader manually extends the width of the cell. I guess what I really want is for the string to go to a new line in the cell when it reaches the end of the cell's width. Is this possible using POI? --------------------------------------------------------------------- To unsubscribe, e-mail: user-unsubscribe@poi.apache.org For additional commands, e-mail: user-help@poi.apache.org
It looks like Petr Udalau has done a bit of work that should get committed soon. This is the first related bugzilla entry that Petr has submitted. https://issues.apache.org/bugzilla/show_bug.cgi?id=48292 If you are able to help in any way (by offering new code, design work, junit test code or even QA) that would be appreciated. --------------------------------------------------------------------- To unsubscribe, e-mail: user-unsubscribe@poi.apache.org For additional commands, e-mail: user-help@poi.apache.org
Hi all,
Sorry if this is already an old subject, i really tried to search solutions
about it, but always saying to increase my JVM memory allocation, convert my
XLS files to CSV or even limit the size of my documents. None of them are
doable in my current project.
Really simple code:
HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(new
FileInputStream("c:/plan1.xls")));
Where this "plan1.xls" has more then 30MB (actually my goal is something
like 200MB).
And i want to import the content to some database.
Can it be done?
I also tried the XLS2CSVmra.class, but got another Java heap space
excpetion.
Thank you in advance.
--
Leandro Carvalho
Hi, How can i get background color for part of .doc-document? (need it for doc2html) Thnks -- View this message in context: http://old.nabble.com/Background-color-for-symbol-textpart-in-HWPFDocument-tp26635772p26635772.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
Hi Josh, thanks for that thorough explanation. My formulation was rather ambiguous. The need for migrating to int is clear. My issue is that for Rows it makes sense in the HSSF world to convert to "unsigned short" by AND'ing with 65535, as that is the maximum no. of rows, but for columns the max is 256, so it is really quite misleading, because the signed range of the short is sufficient. The HSSFCell.getCell(short) code... int ushortCellNum = cellnum & 0x0000FFFF; // avoid sign extension return getCell(ushortCellNum); ...raises an issue with the beholder that is not relevant. Maybe I can illustrate that with an example: I actually noticed this while migrating code from the deprecated method to the int method. My first shot was to AND my short with 65535 before calling the int method, so you see, I was forced to deal with an issue that was irrelevant. My code would of course have worked with the AND, but its not necessary. I would replace the 2 lines with... return getCell((int)cellnum); In any case, I would think its better to give an out-of-range error that mentions the value supplied & not its unsigned equivalent. Regards, DaveLaw ----- original message -------- Subject: Re: HSSFCell short/int Sent: Wed, 02 Dec 2009 From: Josh Micich<josh.micich@gmail.com> > Hello Dave, > > It's not clear if you are questioning the merit of creating the newer > method "getCell(int)", or whether the old method "getCell(short)" > should have been deprecated at all, so I'll attempt to answer both > concerns. > > > The most visible reason why POI is moving away from bytes and shorts > is the need for typecasts when using literal integer constants, even > when the value is quite obviously within range. For example without > the new method "row.getCell(5)" would not compile. We would have to > write "row.getCell((short)5)". > > There have been several POI bugs involving representation of unsigned > (ushort, ubyte) quantities with java datatypes (signed) of the same > width (short, byte). When shorts and bytes are used, the corrected > code generally needs more typecasts and bit mask expressions. If > those datatypes are changed to int, the code is usually simpler and > easier to read. > > The new method "getCell(int) method may in the first place have been > introduced as a result of the XSSF common interface work (a separate > reason, as suggested by Mark B). > > > That sort-of explains the rationale for creating the new method > "getCell(int)". The reason for deprecating the old is to eliminate > overloading (of "getCell"). Method overloading is sometimes very > handy but has quite a few pitfalls, and for that reason POI tries to > use overloading sparingly. > > As far as the exact meaning of the '@deprecated' tag, in POI (unlike > in the java runtime library) it means "This method will be removed in > a future POI version". An alternative is always given. A deprecation > date has been provided in most places, to help POI users prioritize > clean-up of their own code. > > > You mentioned the comment from the the first line of the deprecated method: > int ushortCellNum = cellnum & 0x0000FFFF; // avoid sign extension > The bit-mask is correct for 16-bit unsigned conversion. Masking with > 0xFF would be wrong because it would silently convert every possible > input to a valid column index. For example: is it sensible for > "getCell((short)300)" to succeed? Maybe it looks pointless to > convert values in the range (-32768...-1) to (32768...65535) is > because the maximum column index is 255. This conversion to unsigned > 16-bit has been done because it's more likely that the caller intends > an unsigned quantity (column indexes are never negative). So if a > negative value *does* get into this method, it's probably better to > format the error message in terms of the unsigned 16 bit quantity. > Similar results could have been achieved with bounds checking the > argument, but would involve duplicating all the MissingCellPolicy > logic too. > So - that line of code is there for making better error messages, and > has little to do with the reasons for the deprecation. > > regards, > Josh > > --------------------------------------------------------------------- > To unsubscribe, e-mail: user-unsubscribe@poi.apache.org > For additional commands, e-mail: user-help@poi.apache.org > > --- original message end ---- --------------------------------------------------------------------- To unsubscribe, e-mail: user-unsubscribe@poi.apache.org For additional commands, e-mail: user-help@poi.apache.org
Hello Dave,
It's not clear if you are questioning the merit of creating the newer
method "getCell(int)", or whether the old method "getCell(short)"
should have been deprecated at all, so I'll attempt to answer both
concerns.
The most visible reason why POI is moving away from bytes and shorts
is the need for typecasts when using literal integer constants, even
when the value is quite obviously within range. For example without
the new method "row.getCell(5)" would not compile. We would have to
write "row.getCell((short)5)".
There have been several POI bugs involving representation of unsigned
(ushort, ubyte) quantities with java datatypes (signed) of the same
width (short, byte). When shorts and bytes are used, the corrected
code generally needs more typecasts and bit mask expressions. If
those datatypes are changed to int, the code is usually simpler and
easier to read.
The new method "getCell(int) method may in the first place have been
introduced as a result of the XSSF common interface work (a separate
reason, as suggested by Mark B).
That sort-of explains the rationale for creating the new method
"getCell(int)". The reason for deprecating the old is to eliminate
overloading (of "getCell"). Method overloading is sometimes very
handy but has quite a few pitfalls, and for that reason POI tries to
use overloading sparingly.
As far as the exact meaning of the '@deprecated' tag, in POI (unlike
in the java runtime library) it means "This method will be removed in
a future POI version". An alternative is always given. A deprecation
date has been provided in most places, to help POI users prioritize
clean-up of their own code.
You mentioned the comment from the the first line of the deprecated method:
int ushortCellNum = cellnum & 0x0000FFFF; // avoid sign extension
The bit-mask is correct for 16-bit unsigned conversion. Masking with
0xFF would be wrong because it would silently convert every possible
input to a valid column index. For example: is it sensible for
"getCell((short)300)" to succeed? Maybe it looks pointless to
convert values in the range (-32768...-1) to (32768...65535) is
because the maximum column index is 255. This conversion to unsigned
16-bit has been done because it's more likely that the caller intends
an unsigned quantity (column indexes are never negative). So if a
negative value *does* get into this method, it's probably better to
format the error message in terms of the unsigned 16 bit quantity.
Similar results could have been achieved with bounds checking the
argument, but would involve duplicating all the MissingCellPolicy
logic too.
So - that line of code is there for making better error messages, and
has little to do with the reasons for the deprecation.
regards,
Josh
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
I am only guessing David but the change my be the result of introducing the SS model to accomodate both the binary and OpenXML based file formats 'invisibly'. If you look at the org.apache.poi.ss.usermodel package, you will find there are classes called Workbook, Sheet, Cell, etc and these are used by those who may have to work with both types of file format and do not want to maintain two different code bases. Well, both HSSFCell and XSSFCell both implement the Cell interface and I would guess that the decision was taken to include just the single method getCell(int) in the interface and - as a result - both concrete classes because the OpenXML file format does not impose the 256 row limit on the user. As I said, this is only a guess. Yours Mark B David Law-2 wrote: > > Could it be, the HSSFCell getCell(short cellnum) > method has been rather confusingly deprecated? > > Arguably, its laudable to "avoid sign extension", > but as there can only be 256 columns anyway, > I find it really rather misleading. > > Regards, > DaveLaw > > --------------------------------------------------------------------- > 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/HSSFCell-short-int-tp26607785p26612088.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
Hello again, If you code to the 'SS' model then you will not need to check the type returned by the WorkbookFactory at all. By this. I mean that you will use objects from the org.apache.poi.ss.usermodel package such as Workbook, Sheet and Cell in your program. That way, you need have no concerns at all about the actual type - and therefore the file format - that you are dealing with. Before deciding to move your code in this direction however, it would be wise to look closely at the methods defined on the various interfaces just to make sure that you can accomplish everything you require. Just as am example, on HSSFSheet you can add a data validation whuilst this methgod has not yet been declared within the Sheet interface. Should you need to extended functionality then of course you can just test the type returned to you by the WorkbookFactory and then 'direct' program flow accordingly. Yours Mark B PS Sorry again for that 'just open the file and look at the header' bit this morning. I was over-excited about the work we had on today and seriously deprived of tea at that moment. Hope you did not waste any time pursuing that fruitless/pointless direction. Sparecreative wrote: > > Thanks Mark, > > That’s exactly what I was after. And, just so I’m clear, with the Workbook > factory, I just check if the Workbook is an instance of either HSSF or > XSSF > and process accordingly. > > On a related topic it would be great if the specifics for the two event > based readers (for HSSF and XSSF) could be abstracted away so the we could > use one set of methods regardless of the document type. Much like the user > model. > > Z. >> >> So, to be clear, all you want to do is identify which files use the >> OpenXML >> file format and which are binary? >> >> If so, then take a look at the >> org.apache.poi.ss.usermodel.WorkbookFactory >> (http://poi.eu.apache.org/apidocs/org/apache/poi/ss/usermodel/WorkbookFactory. >> html) >> class. All you need to do is call the static create method pasing an >> InputStream and you will receive back an instance of either the HSSF or >> XSSFWorkbook class depending upon the type of the file. Myself, I have >> never >> tried using it with files that lack extensions but it ought to work and I >> would certainly suggest giving it a try. Alternatively, you can simply >> catch >> exceptions; i.e. try to open the file as an HSSFWorkbook, catch the >> exception if the format is not correct and try to open it as an >> XSSFWorkbook >> then catch and handle the exception thrown if the format is again >> invalid. >> Finally, you could open an InputStream onto the file and examine the >> first >> few bytes - I think it is safe to assume that the xml header would be the >> first thing you read from an OpenXML based file. >> >> Yours >> >> Mark B >> >> >> Sparecreative wrote: >>> > >>> > We¹re currently successfully using poi in a webapp to read from >>> uploaded >>> > excel files. >>> > >>> > At present we do a check on the filename extension. If xls we use HSSF >>> and >>> > if xslx we use XSSF and this is working reasonably well. >>> > >>> > The problem we have is that some of our users are uploading files with >>> no >>> > extension. Is there a way to determine the type of excel file that we >>> are >>> > dealing with programmatically? >>> > >>> > I¹d appreciate any pointers as I haven¹t had a lot of luck finding >>> > anything >>> > on the web. >>> > >>> > Z. >>> > >>> > > > > -- View this message in context: http://old.nabble.com/Determine-version-of-excel-file-tp26603831p26611959.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
Thanks for that Chris. I realised what I had said just as we pulled onto site
this morning and have spent hours today calling myself all of the names
under the sun and bewailing (good word) the fact that I could not get to a
PC!! Of course you are correct, the xml is zipped so there will be no xml
header at the start of the file. That will teach me to show off before I
have had two cups of tea in the morning; my apologies to all.
Yours
Mark B
ChrisLott wrote:
>
> MSB wrote:
>> ..
>> Finally, you could open an InputStream onto the file and examine the
>> first
>> few bytes - I think it is safe to assume that the xml header would be the
>> first thing you read from an OpenXML based file.
>
> Goodness, no! :-) An xlsx file (like a docx file and I suppose a pptx
> file) is actually a zip archive. Try opening it with winzip or your
> favorite zip-file reader and you'll see ("zip -T sheet.xlsx"). Inside
> you'll see XML files, each of which should have a nice XML header. I
> suppose you could reimplement the magic-number check for a zip file done
> by a unix/linux machine's "file" program, maybe that's what POI's
> WorkbookFactory does under the covers.
>
> chris...
>
> ---------------------------------------------------------------------
> 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/Determine-version-of-excel-file-tp26603831p26611823.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
Could it be, the HSSFCell getCell(short cellnum) method has been rather confusingly deprecated? Arguably, its laudable to "avoid sign extension", but as there can only be 256 columns anyway, I find it really rather misleading. Regards, DaveLaw --------------------------------------------------------------------- To unsubscribe, e-mail: user-unsubscribe@poi.apache.org For additional commands, e-mail: user-help@poi.apache.org
I'm very new too. I found a lot of good information on the POI website ( http://poi.eu.apache.org/) and in the examples and unit tests that you get in the source distribution. Regards Mark 2009/12/2 Emeka <emekamicro@gmail.com> > Hello All, > > I am pretty new here, and would like to know where I can get good tutorials > and other relevant materials to get started. > > Regards, > Janus >
Hello All, I am pretty new here, and would like to know where I can get good tutorials and other relevant materials to get started. Regards, Janus
MSB wrote:
> ..
> Finally, you could open an InputStream onto the file and examine the first
> few bytes - I think it is safe to assume that the xml header would be the
> first thing you read from an OpenXML based file.
Goodness, no! :-) An xlsx file (like a docx file and I suppose a pptx
file) is actually a zip archive. Try opening it with winzip or your
favorite zip-file reader and you'll see ("zip -T sheet.xlsx"). Inside
you'll see XML files, each of which should have a nice XML header. I
suppose you could reimplement the magic-number check for a zip file done
by a unix/linux machine's "file" program, maybe that's what POI's
WorkbookFactory does under the covers.
chris...
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Content-type: text/plain; charset="EUC-KR" Content-transfer-encoding: quoted-printable Thanks Mark, That=A1=AFs exactly what I was after. And, just so I=A1=AFm clear, with the Workboo= k factory, I just check if the Workbook is an instance of either HSSF or XSSF and process accordingly. On a related topic it would be great if the specifics for the two event based readers (for HSSF and XSSF) could be abstracted away so the we could use one set of methods regardless of the document type. Much like the user model. Z. >=20 > So, to be clear, all you want to do is identify which files use the OpenX= ML > file format and which are binary? >=20 > If so, then take a look at the org.apache.poi.ss.usermodel.WorkbookFactor= y > (http://poi.eu.apache.org/apidocs/org/apache/poi/ss/usermodel/WorkbookFac= tory. > html) > class. All you need to do is call the static create method pasing an > InputStream and you will receive back an instance of either the HSSF or > XSSFWorkbook class depending upon the type of the file. Myself, I have ne= ver > tried using it with files that lack extensions but it ought to work and I > would certainly suggest giving it a try. Alternatively, you can simply ca= tch > exceptions; i.e. try to open the file as an HSSFWorkbook, catch the > exception if the format is not correct and try to open it as an XSSFWorkb= ook > then catch and handle the exception thrown if the format is again invalid= . > Finally, you could open an InputStream onto the file and examine the firs= t > few bytes - I think it is safe to assume that the xml header would be the > first thing you read from an OpenXML based file. >=20 > Yours >=20 > Mark B >=20 >=20 > Sparecreative wrote: >> >=20 >> > We=A9=F6re currently successfully using poi in a webapp to read from uploa= ded >> > excel files. >> >=20 >> > At present we do a check on the filename extension. If xls we use HSSF= and >> > if xslx we use XSSF and this is working reasonably well. >> >=20 >> > The problem we have is that some of our users are uploading files with= no >> > extension. Is there a way to determine the type of excel file that we = are >> > dealing with programmatically? >> >=20 >> > I=A9=F6d appreciate any pointers as I haven=A9=F6t had a lot of luck finding >> > anything >> > on the web. >> >=20 >> > Z. >> >=20 >> >=20
So, to be clear, all you want to do is identify which files use the OpenXML file format and which are binary? If so, then take a look at the org.apache.poi.ss.usermodel.WorkbookFactory (http://poi.eu.apache.org/apidocs/org/apache/poi/ss/usermodel/WorkbookFactory.html) class. All you need to do is call the static create method pasing an InputStream and you will receive back an instance of either the HSSF or XSSFWorkbook class depending upon the type of the file. Myself, I have never tried using it with files that lack extensions but it ought to work and I would certainly suggest giving it a try. Alternatively, you can simply catch exceptions; i.e. try to open the file as an HSSFWorkbook, catch the exception if the format is not correct and try to open it as an XSSFWorkbook then catch and handle the exception thrown if the format is again invalid. Finally, you could open an InputStream onto the file and examine the first few bytes - I think it is safe to assume that the xml header would be the first thing you read from an OpenXML based file. Yours Mark B Sparecreative wrote: > > We¹re currently successfully using poi in a webapp to read from uploaded > excel files. > > At present we do a check on the filename extension. If xls we use HSSF and > if xslx we use XSSF and this is working reasonably well. > > The problem we have is that some of our users are uploading files with no > extension. Is there a way to determine the type of excel file that we are > dealing with programmatically? > > I¹d appreciate any pointers as I haven¹t had a lot of luck finding > anything > on the web. > > Z. > > -- View this message in context: http://old.nabble.com/Determine-version-of-excel-file-tp26603831p26604556.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
Content-type: text/plain; charset="ISO-8859-1" Content-transfer-encoding: quoted-printable We=B9re currently successfully using poi in a webapp to read from uploaded excel files. At present we do a check on the filename extension. If xls we use HSSF and if xslx we use XSSF and this is working reasonably well. The problem we have is that some of our users are uploading files with no extension. Is there a way to determine the type of excel file that we are dealing with programmatically? I=B9d appreciate any pointers as I haven=B9t had a lot of luck finding anything on the web. Z.
Hi all, It appears that setting the rotation on an XSSFCellStyle works fine with positive short values, but doesn't work with negative values (mainly testing -45 and -90). I ran the same tests to write a 2003 document using an HSSFCellStyle and the negative rotations appear accurately in the resulting spreadsheet. So my guess is there is a bug with this setting in XSSF. I am using the latest nightly build from today (20091201) for this test: poi-3.6-beta1-20091201.jar poi-ooxml-3.6-beta1-20091201.jar Can anyone else confirm this behavior as a bug? Thanks, Erick Lichtas <http://www.linomasoftware.com/> Linoma-Software-Logo 1409 Silver Street, Ashland, NE 68003 1-800-949-4696 x714
Thanks everybody, During all my tests I had an old poi in the jre directory of the jdk I am actually using. I am programming now with Netbeans and I thought my settings there will be enough, but it looks the old settings in jre were overwriting the new ones. Thanks again to everybody! Hannes Erven schreef: >> Strange enough this is the output: >> file:/C:/Program%20Files/Java/jdk1.6.0_03/jre/lib/ext/poi-3.5-beta4-20081128/ >> but this path is not existing! >> > > In any case, it seems you have some older POI version somewhere in your > classpath. Have you double-checked your classpath settings? How do you > invoke the program in question? > > -hannes > > --------------------------------------------------------------------- > To unsubscribe, e-mail: user-unsubscribe@poi.apache.org > For additional commands, e-mail: user-help@poi.apache.org > > > --------------------------------------------------------------------- To unsubscribe, e-mail: user-unsubscribe@poi.apache.org For additional commands, e-mail: user-help@poi.apache.org
> Strange enough this is the output: > file:/C:/Program%20Files/Java/jdk1.6.0_03/jre/lib/ext/poi-3.5-beta4-20081128/ > but this path is not existing! In any case, it seems you have some older POI version somewhere in your classpath. Have you double-checked your classpath settings? How do you invoke the program in question? -hannes --------------------------------------------------------------------- To unsubscribe, e-mail: user-unsubscribe@poi.apache.org For additional commands, e-mail: user-help@poi.apache.org
Strange enough this is the output:
file:/C:/Program%20Files/Java/jdk1.6.0_03/jre/lib/ext/poi-3.5-beta4-20081128/
but this path is not existing!
Yegor Kozlov schreef:
> Execute the following code:
>
> URL url =
> HSSFWorkbook.class.getProtectionDomain().getCodeSource().getLocation();
> System.out.println(url);
>
> it will print the path to the POI jar.
>
> nightly builds can be downloaded from
> http://encore.torchbox.com/poi-svn-build/
>
> Yegor
>
>> Your prediction is good.
>>
>> This was the output:
>> Wrong result type - got 5errCode=-30
>> But how can I get rhe newest poi jar file?
>> I downloaded the one I am using
>>
>> POI-3.5-FINAL-20090928.jar
>>
>> from POI website.
>>
>>
>>
>>
>> Josh Micich schreef:
>>>> I am using POI-3.5-FINAL-20090928.jar.
>>>>
>>> There's a high chance you're not.
>>>
>>> You can also dump the error code to help diagnose the problem.
>>> Change this line:
>>> System.err.println("Wrong result type - got " + cv.getCellType() + "
>>> errCode=" + cv.getErrorValue());
>>>
>>> I predict the error code will be -30. Which will mean you are using a
>>> version of POI prior to 3.5-beta4
>>>
>>> If I am mistaken, another thing you can try is to put a breakpoint in
>>> Indirect.java:76, to confirm whether execution gets there. You can
>>> also look around the variables in the stack frame which might help
>>> explain what is going wrong.
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Execute the following code:
URL url = HSSFWorkbook.class.getProtectionDomain().getCodeSource().getLocation();
System.out.println(url);
it will print the path to the POI jar.
nightly builds can be downloaded from http://encore.torchbox.com/poi-svn-build/
Yegor
> Your prediction is good.
>
> This was the output:
> Wrong result type - got 5errCode=-30
> But how can I get rhe newest poi jar file?
> I downloaded the one I am using
>
> POI-3.5-FINAL-20090928.jar
>
> from POI website.
>
>
>
>
> Josh Micich schreef:
>>> I am using POI-3.5-FINAL-20090928.jar.
>>>
>> There's a high chance you're not.
>>
>> You can also dump the error code to help diagnose the problem.
>> Change this line:
>> System.err.println("Wrong result type - got " + cv.getCellType() + "
>> errCode=" + cv.getErrorValue());
>>
>> I predict the error code will be -30. Which will mean you are using a
>> version of POI prior to 3.5-beta4
>>
>> If I am mistaken, another thing you can try is to put a breakpoint in
>> Indirect.java:76, to confirm whether execution gets there. You can
>> also look around the variables in the stack frame which might help
>> explain what is going wrong.
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Your prediction is good.
This was the output:
Wrong result type - got 5errCode=-30
But how can I get rhe newest poi jar file?
I downloaded the one I am using
POI-3.5-FINAL-20090928.jar
from POI website.
Josh Micich schreef:
>> I am using POI-3.5-FINAL-20090928.jar.
>>
> There's a high chance you're not.
>
> You can also dump the error code to help diagnose the problem.
> Change this line:
> System.err.println("Wrong result type - got " + cv.getCellType() + "
> errCode=" + cv.getErrorValue());
>
> I predict the error code will be -30. Which will mean you are using a
> version of POI prior to 3.5-beta4
>
> If I am mistaken, another thing you can try is to put a breakpoint in
> Indirect.java:76, to confirm whether execution gets there. You can
> also look around the variables in the stack frame which might help
> explain what is going wrong.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
> I am using POI-3.5-FINAL-20090928.jar.
There's a high chance you're not.
You can also dump the error code to help diagnose the problem.
Change this line:
System.err.println("Wrong result type - got " + cv.getCellType() + "
errCode=" + cv.getErrorValue());
I predict the error code will be -30. Which will mean you are using a
version of POI prior to 3.5-beta4
If I am mistaken, another thing you can try is to put a breakpoint in
Indirect.java:76, to confirm whether execution gets there. You can
also look around the variables in the stack frame which might help
explain what is going wrong.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Hi,
I am using POI-3.5-FINAL-20090928.jar.
I changed my code as follows:
public static void main(String[] args) throws IOException {
HSSFWorkbook wbAA = new HSSFWorkbook(new
FileInputStream("D:/Book1.xls"));
wbAA.getSheetAt(0).getRow(1).getCell(0).setCellValue("Sheet2");
HSSFFormulaEvaluator evalAA = new HSSFFormulaEvaluator(wbAA);
CellValue cv =
evalAA.evaluate(wbAA.getSheetAt(0).getRow(3).getCell(0));
evalAA.clearAllCachedResultValues();
if (cv.getCellType() != Cell.CELL_TYPE_NUMERIC) {
System.err.println("Wrong result type - got " + cv.getCellType());
return;
}
System.err.println("Cell A4=" +cv.getNumberValue());
}
and I get:
Wrong result type - got 5
This means formula error. So INDIRECT is not evaluated corectly. Why?
I really need to make this working. What should I do?
Thanks,
Adrian
Josh Micich schreef:
> You are probably still running a very old version of POI. In those
> versions it was best practice to check the cell type of the
> evaluation result (rather than just assume CELL_TYPE_NUMERIC). You
> are probably getting CELL_TYPE_ERROR due to INDIRECT not being
> implemented in your version of POI. Make a small change to your test
> code to see:
>
> ----------------
> public static void main(String[] args) throws IOException {
> HSSFWorkbook wbA = new HSSFWorkbook(new
> FileInputStream("c:/josh/temp/Book1.xls"));
> wbA.getSheetAt(0).getRow(1).getCell(0).setCellValue("Sheet2");
> HSSFFormulaEvaluator evalA = new HSSFFormulaEvaluator(wbA);
> CellValue cv = evalA.evaluate(wbA.getSheetAt(0).getRow(3).getCell(0));
> if (cv.getCellType() != Cell.CELL_TYPE_NUMERIC) {
> System.err.println("Wrong result type - got " + cv.getCellType());
> return;
> }
> System.err.println("Cell A4=" + cv.getNumberValue());
> }
> --------------
>
> This change is not required if you are running anything newer than
> version 3.5-beta4. Since then unimplemented functions cause
> NotImplementedException to be thrown
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
You are probably still running a very old version of POI. In those
versions it was best practice to check the cell type of the
evaluation result (rather than just assume CELL_TYPE_NUMERIC). You
are probably getting CELL_TYPE_ERROR due to INDIRECT not being
implemented in your version of POI. Make a small change to your test
code to see:
----------------
public static void main(String[] args) throws IOException {
HSSFWorkbook wbA = new HSSFWorkbook(new
FileInputStream("c:/josh/temp/Book1.xls"));
wbA.getSheetAt(0).getRow(1).getCell(0).setCellValue("Sheet2");
HSSFFormulaEvaluator evalA = new HSSFFormulaEvaluator(wbA);
CellValue cv = evalA.evaluate(wbA.getSheetAt(0).getRow(3).getCell(0));
if (cv.getCellType() != Cell.CELL_TYPE_NUMERIC) {
System.err.println("Wrong result type - got " + cv.getCellType());
return;
}
System.err.println("Cell A4=" + cv.getNumberValue());
}
--------------
This change is not required if you are running anything newer than
version 3.5-beta4. Since then unimplemented functions cause
NotImplementedException to be thrown
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
On my computer does not work!
I restricted the code to:
public static void main(String[] args) throws IOException {
HSSFWorkbook wbAA = new HSSFWorkbook(new
FileInputStream("D:/Book1.xls"));
wbAA.getSheetAt(0).getRow(1).getCell(0).setCellValue("Sheet2");
HSSFFormulaEvaluator evalAA = new HSSFFormulaEvaluator(wbAA);
System.err.println("Cell A4="
+evalAA.evaluate(wbAA.getSheetAt(0).getRow(3).getCell(0)).getNumberValue());
}
and the output is:
run:
Cell A4=0.0
BUILD SUCCESSFUL (total time: 1 second)
Regards,
Adrian
Josh Micich schreef:
> I modified your code just slightly to get it to compile and it seems
> to run OK, producing your expected result (100.0).
> The output is: Cell A4=100.0
>
>
> Here is the modified code - please check it to make sure I didn't do
> anything crazy
> ---------------
> public static void main(String[] args) throws IOException {
> HSSFWorkbook wbA = new HSSFWorkbook(new FileInputStream("D:/Book1.xls"));
> wbA.getSheetAt(0).getRow(1).getCell(0).setCellValue("Sheet2");
> HSSFFormulaEvaluator evalA = (HSSFFormulaEvaluator)
> wbA.getCreationHelper().createFormulaEvaluator();
> evalA.evaluateFormulaCell(wbA.getSheetAt(0).getRow(3).getCell(0));
> printCell(wbA, evalA, 0, "A4");
> }
>
> private static void printCell(HSSFWorkbook wbA, HSSFFormulaEvaluator
> evalA, int sheet, String str) {
> CellReference cellReference = new CellReference(str);
> Row row = wbA.getSheetAt(sheet).getRow(cellReference.getRow());
> Cell cell = row.getCell(cellReference.getCol());
> evalA.clearAllCachedResultValues();
> evalA.evaluate((HSSFCell) cell);
> System.err.println("Cell " + str + "=" +
> evalA.evaluate(cell).getNumberValue());
> }
> ------------------
>
> >From what I can tell, your sample code could be as simple as this:
>
> HSSFWorkbook wbA = new HSSFWorkbook(new
> FileInputStream("c:/josh/temp/Book1.xls"));
> wbA.getSheetAt(0).getRow(1).getCell(0).setCellValue("Sheet2");
> HSSFFormulaEvaluator evalA = new HSSFFormulaEvaluator(wbA);
> System.err.println("Cell A4=" +
> evalA.evaluate(wbA.getSheetAt(0).getRow(3).getCell(0)).getNumberValue());
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
I modified your code just slightly to get it to compile and it seems
to run OK, producing your expected result (100.0).
The output is: Cell A4=100.0
Here is the modified code - please check it to make sure I didn't do
anything crazy
---------------
public static void main(String[] args) throws IOException {
HSSFWorkbook wbA = new HSSFWorkbook(new FileInputStream("D:/Book1.xls"));
wbA.getSheetAt(0).getRow(1).getCell(0).setCellValue("Sheet2");
HSSFFormulaEvaluator evalA = (HSSFFormulaEvaluator)
wbA.getCreationHelper().createFormulaEvaluator();
evalA.evaluateFormulaCell(wbA.getSheetAt(0).getRow(3).getCell(0));
printCell(wbA, evalA, 0, "A4");
}
private static void printCell(HSSFWorkbook wbA, HSSFFormulaEvaluator
evalA, int sheet, String str) {
CellReference cellReference = new CellReference(str);
Row row = wbA.getSheetAt(sheet).getRow(cellReference.getRow());
Cell cell = row.getCell(cellReference.getCol());
evalA.clearAllCachedResultValues();
evalA.evaluate((HSSFCell) cell);
System.err.println("Cell " + str + "=" +
evalA.evaluate(cell).getNumberValue());
}
------------------
>From what I can tell, your sample code could be as simple as this:
HSSFWorkbook wbA = new HSSFWorkbook(new
FileInputStream("c:/josh/temp/Book1.xls"));
wbA.getSheetAt(0).getRow(1).getCell(0).setCellValue("Sheet2");
HSSFFormulaEvaluator evalA = new HSSFFormulaEvaluator(wbA);
System.err.println("Cell A4=" +
evalA.evaluate(wbA.getSheetAt(0).getRow(3).getCell(0)).getNumberValue());
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
This is the a simple code:
public static void main(String[] args) throws IOException {
wbA = new HSSFWorkbook(new FileInputStream("D:/Book1.xls"));
wbA.getSheetAt(0).getRow(1).getCell(0).setCellValue("Sheet2");
evalA = (HSSFFormulaEvaluator)
wbA.getCreationHelper().createFormulaEvaluator();
evalA.evaluateFormulaCell(wbA.getSheetAt(0).getRow(3).getCell(0));
printCell(0,"A4");
}
public static void printCell(int sheet, String str)
{
CellReference cellReference = new CellReference(str);
Row row = wbA.getSheetAt(sheet).getRow(cellReference.getRow());
Cell cell = row.getCell(cellReference.getCol());
evalA.clearAllCachedResultValues();
evalA.evaluate((HSSFCell) cell);
System.err.println("Cell
"+str+"="+evalA.evaluate(cell).getNumberValue());
}
And attached is the file.
The output is: Cell A4=0.0
and I expected 100.0
Regards,
Adrian
Josh Micich schreef:
> Evaluation support for INDIRECT() was added recently:
> https://issues.apache.org/bugzilla/show_bug.cgi?id=47721
>
> This happened before 3.5-final.
>
> Please reply with a your sample code, but most importantly please
> also include the stack trace of your error.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
Evaluation support for INDIRECT() was added recently: https://issues.apache.org/bugzilla/show_bug.cgi?id=47721 This happened before 3.5-final. Please reply with a your sample code, but most importantly please also include the stack trace of your error. --------------------------------------------------------------------- To unsubscribe, e-mail: user-unsubscribe@poi.apache.org For additional commands, e-mail: user-help@poi.apache.org
Hi, I am trying to read a cell of an Excel sheet containing an INDIRECT function. The result is always error. Is INDIRECT function supported in POI 3.5-final? Regards, Adrian --------------------------------------------------------------------- To unsubscribe, e-mail: user-unsubscribe@poi.apache.org For additional commands, e-mail: user-help@poi.apache.org
Hi, This bug was fixed about 4 weeks ago. See https://issues.apache.org/bugzilla/show_bug.cgi?id=48070 You will need to get a nightly build or build from trunk yourself. Regards, Dave On Nov 30, 2009, at 9:03 AM, Erick Lichtas wrote: > Hi all, > > > > I am currently using POI 3.5 FINAL to write both XLS and XLSX > documents. I am noticing that when writing string data a XSSF cell, > the data is always trimmed. The same process when writing to an > HSSFCell preserves the leading and trailing spaces. Is this a bug > or the intended behavior? Is there a way in the current release to > preserve these leading and trailing spaces for string data? > > > > Thanks! > > > > Erick Lichtas > > > > <image003.jpg> > > 1409 Silver Street, Ashland, NE 68003 > > 1-800-949-4696 x714 > > > > > > __________ Information from ESET NOD32 Antivirus, version of virus > signature database 4649 (20091130) __________ > > The message was checked by ESET NOD32 Antivirus. > > http://www.eset.com
Hi all, I am currently using POI 3.5 FINAL to write both XLS and XLSX documents. I am noticing that when writing string data a XSSF cell, the data is always trimmed. The same process when writing to an HSSFCell preserves the leading and trailing spaces. Is this a bug or the intended behavior? Is there a way in the current release to preserve these leading and trailing spaces for string data? Thanks! Erick Lichtas <http://www.linomasoftware.com/> Linoma-Software-Logo 1409 Silver Street, Ashland, NE 68003 1-800-949-4696 x714
Hi Mark, Thank you for your reply. After writing my initial post I started looking at the use of template files, and it turns out this was the way to go. Every feature that was requested succeeded and the final report file is great :jumping:. Thanks again, Jochen MSB wrote: > > I must admit that I would use a template file to do this because I am not > aware of any technique that will allow you to insert pictures into the > footer using POI. Simply create a new Workbook using Excel, set the > margins as you want them and save it away. All you need to do then is open > the workbook using POI and populate the sheet(s). > > Just a word of warning - possibly - pictures in headers and footers were > not supported in versions prior to 2002 - I think. Before that time, you > have to use a trick to fool Excel into placing the image into a > hearder/footer and so you risk creating files that may not - and I > emphasise the may bit - be completely compatible with older versions (e.g. > Excel 97/2000) of Excel. > > Yours > > Mark B > > > JochenP wrote: >> >> Hello, >> >> I currently trying to figure out if it is possible to set the margins of >> a workbook or excel document. >> I know there are two methods to set the footer margin and header margin >> on the PrintSetup class, on every sheet, but these don't seem to help. >> >> What I need to do is reduce the top, right, bottom and left margin to >> it's minimum, so more content can be printed on one page. >> >> I also tried the the autobreaks and fit-width 1, but then I don't have >> control over the paging which I need. >> >> One thing I also would like to know: Is it possible to add pictures to >> the footers? >> >> Or is there an other way, to start from a template excel file and dump my >> data in a new copy of the template? Has anyone had experience with this? >> >> Kind regards, >> >> Jochen Punie >> > > -- View this message in context: http://old.nabble.com/HSSF-workbook-print-setup-margins-tp26513632p26525831.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
MSB schrieb: > Simply create a new Workbook using Excel, set the margins > as you want them and save it away. All you need to do then is open the > workbook using POI and populate the sheet(s). And make sure that your master sheet contains at least one cell of data -- it seems Excel 2003 does not save margins on totally empty sheets. (YMMV.) -hannes --------------------------------------------------------------------- To unsubscribe, e-mail: user-unsubscribe@poi.apache.org For additional commands, e-mail: user-help@poi.apache.org
I must admit that I would use a template file to do this because I am not aware of any technique that will allow you to insert pictures into the footer using POI. Simply create a new Workbook using Excel, set the margins as you want them and save it away. All you need to do then is open the workbook using POI and populate the sheet(s). Just a word of warning - possibly - pictures in headers and footers were not supported in versions prior to 2002 - I think. Before that time, you have to use a trick to fool Excel into placing the image into a hearder/footer and so you risk creating files that may not - and I emphasise the may bit - be completely compatible with older versions (e.g. Excel 97/2000) of Excel. Yours Mark B JochenP wrote: > > Hello, > > I currently trying to figure out if it is possible to set the margins of a > workbook or excel document. > I know there are two methods to set the footer margin and header margin on > the PrintSetup class, on every sheet, but these don't seem to help. > > What I need to do is reduce the top, right, bottom and left margin to it's > minimum, so more content can be printed on one page. > > I also tried the the autobreaks and fit-width 1, but then I don't have > control over the paging which I need. > > One thing I also would like to know: Is it possible to add pictures to the > footers? > > Or is there an other way, to start from a template excel file and dump my > data in a new copy of the template? Has anyone had experience with this? > > Kind regards, > > Jochen Punie > -- View this message in context: http://old.nabble.com/HSSF-workbook-print-setup-margins-tp26513632p26525253.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
I must admit that I would use a template file to do this because I am not aware of any technique that will allow you to insert pictures into the footer using POI. Simply create a new Workbook using Excel, set the margins as you want them and save it away. All you need to do then is open the workbook using POI and populate the sheet(s). Just a word of warning - possibly - pictures in headers and footers were not supported in versions prior to 2002 - I think. Before that time, you have to use a trick to fool Excel into placing the image into a hearder/footer and so you risk creating files that may not - and I emphasise the may bit - be completely compatible with older versions (e.g. Excel 97/2000) of Excel. Yours Mark B JochenP wrote: > > Hello, > > I currently trying to figure out if it is possible to set the margins of a > workbook or excel document. > I know there are two methods to set the footer margin and header margin on > the PrintSetup class, on every sheet, but these don't seem to help. > > What I need to do is reduce the top, right, bottom and left margin to it's > minimum, so more content can be printed on one page. > > I also tried the the autobreaks and fit-width 1, but then I don't have > control over the paging which I need. > > One thing I also would like to know: Is it possible to add pictures to the > footers? > > Or is there an other way, to start from a template excel file and dump my > data in a new copy of the template? Has anyone had experience with this? > > Kind regards, > > Jochen Punie > -- View this message in context: http://old.nabble.com/HSSF-workbook-print-setup-margins-tp26513632p26525252.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
Folks, sorry to re-activite this thread... I've run into the same issue, but I could track it down to using workbook.setRepeatingRowsAndColumns() on the workbook: if used, Excel complains when opening about the same things as David Hoffer described: > Errors were detected in file 'C:\filename.xls' > The following is a list of repairs: > > Removed one or more invalid names. Unfortunately, my Excel is in German, but there is no more info available anyways. If I omit that call, the file opens fine. OpenOffice opens the file without issues and also the repeating rows are set the way I'd like them to be. Has anyone any idea what might be going on? If helpful, I can make up some test files to inspect. Thanks, best regards -hannes PS: I was surprised that I have to use workbook.setRepeatingRowsAndColumns() at all since I'm wb.cloneSheet() a sheet that already has repeating rows/columns set... is this by design? --------------------------------------------------------------------- To unsubscribe, e-mail: user-unsubscribe@poi.apache.org For additional commands, e-mail: user-help@poi.apache.org
Thanks for that, I have seen people asking for just this sort of information
before on the list. Can I assume you have been able to get something to
work?
Yours
Mark B
Fabián Avilés Martínez wrote:
>
> Hi Mark, version 3.2-FINAL is accesible in public maven repositories,
> these are the dependencies:
>
> <dependency>
> <groupId>org.apache.poi</groupId>
> <artifactId>poi</artifactId>
> <version>3.2-FINAL</version>
> </dependency>
> <dependency>
> <groupId>org.apache.poi</groupId>
> <artifactId>poi-scratchpad</artifactId>
> <version>3.2-FINAL</version>
> </dependency>
>
>
> Thanks, Fabi.
>
> -----Mensaje original-----
> De: MSB [mailto:markbrdsly@tiscali.co.uk]
> Enviado el: martes, 24 de noviembre de 2009 17:27
> Para: user@poi.apache.org
> Asunto: RE: Modify word document
>
>
> You are welcome.
>
> If you do not have access to 3.2 FINAL of the API, it is possible to
> download older releases from here -
> http://archive.apache.org/dist/poi/release/bin/. Must admit that I do not
> know what changes were made to HWPF between 3.2 and 3.5 so cannot say why
> the formatting information is being lost and can only hope that you will
> ne
> able to revert to using 3.2 FINAL for this project.
>
> All that you will need to do is to ensure that both the scratchpad and POI
> archives are in your classpath and you should be able to successfully
> compile and run the code. Any problems, just let me know.
>
> Yours
>
> Mark B
>
>
>
> Fabián Avilés Martínez wrote:
>>
>> Wow, thats great. At least I have new direction to work with. I have been
>> struggling myself for at least three days. I can not try it today, but
>> tomorrow wil be the first thing I am going to do. I will told you the
>> results.
>>
>> Thank you so nuch.
>>
>> -----Mensaje original-----
>> De: MSB [mailto:markbrdsly@tiscali.co.uk]
>> Enviado el: martes, 24 de noviembre de 2009 16:51
>> Para: user@poi.apache.org
>> Asunto: RE: Modify word document
>>
>>
>> I have had the chance to play around with some code and I have to admit
>> that
>> I was wrong, on two counts.
>>
>> Firstly, if you do drill down to the level of the CharacterRun and
>> perform
>> a
>> replacement operation there, you will not retain the formatting applied
>> to
>> the text, further more, it seems to fail completely; no replacements will
>> be
>> made in the document at all. To have the search term be successfully
>> replaced, you DO need to operate at the Pargraph level.
>>
>> Secondly, if the search term is shorter than the replacement term, then
>> HWPF
>> will throw an exception. It seems quite happy to work if the replacement
>> term is equal to or longer - in terms of the number of characters - than
>> the
>> search term.
>>
>> Please see the code I have attached below;
>>
>> /* ====================================================================
>> Licensed to the Apache Software Foundation (ASF) under one or more
>> contributor license agreements. See the NOTICE file distributed with
>> this work for additional information regarding copyright ownership.
>> The ASF licenses this file to You under the Apache License, Version
>> 2.0
>> (the "License"); you may not use this file except in compliance with
>> the License. You may obtain a copy of the License at
>>
>> http://www.apache.org/licenses/LICENSE-2.0
>>
>> Unless required by applicable law or agreed to in writing, software
>> distributed under the License is distributed on an "AS IS" BASIS,
>> WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or
>> implied.
>> See the License for the specific language governing permissions and
>> limitations under the License.
>> ==================================================================== */
>>
>> package newsearchreplace;
>>
>> import java.io.File;
>> import java.io.FileInputStream;
>> import java.io.FileOutputStream;
>> import java.io.FileNotFoundException;
>> import java.io.IOException;
>> import java.util.HashMap;
>> import java.util.Set;
>>
>> import org.apache.poi.hwpf.HWPFDocument;
>> import org.apache.poi.hwpf.usermodel.Range;
>> import org.apache.poi.hwpf.usermodel.Paragraph;
>> import org.apache.poi.hwpf.usermodel.CharacterRun;
>>
>>
>> /**
>> *
>> * @author win Mark Beardsley [msb at apache.org]
>> * @version 1.00
>> */
>> public class SearchReplace {
>>
>> private HashMap<String, String> searchTerms = null;
>> private Set<String> searchKeys = null;
>> private HWPFDocument wordDocument = null;
>>
>> public SearchReplace() {
>> searchTerms = new HashMap<String, String>();
>> // The first String is the text that will be searched for, the
>> second is what will be used to
>> // replace it. Of course, it is possible to create more than one
>> search term, replacement text
>> // pairing.
>> searchTerms.put("replace", "tester");
>> searchKeys = searchTerms.keySet();
>> }
>>
>> public void openTemplate(String filename) throws
>> FileNotFoundException,
>> IOException {
>> File file = null;
>> FileInputStream fis = null;
>> try {
>> file = new File(filename);
>> fis = new FileInputStream(file);
>> this.wordDocument = new HWPFDocument(fis);
>> }
>> finally {
>> if(fis != null) {
>> try {
>> fis.close();
>> fis = null;
>> }
>> catch(Exception ex) {
>> // I G N O R E
>> }
>> }
>> }
>> }
>>
>> public void searchAndReplace() {
>> Range docRange = this.wordDocument.getRange();
>> int numParas = docRange.numParagraphs();
>> for(int i = 0; i < numParas; i++) {
>> Paragraph para = docRange.getParagraph(i);
>> int numCharRuns = para.numCharacterRuns();
>> for(int j = 0; j < numCharRuns; j++) {
>> CharacterRun charRun = para.getCharacterRun(j);
>> String text = charRun.text();
>> for(String key : this.searchKeys) {
>> if(text.contains(key)) {
>> String replacementTerm =
>> this.searchTerms.get(key);
>> charRun.replaceText(replacementTerm, key);
>> System.out.println("Found: " + key + " in " +
>> text
>> +
>> ". Will replace with: " + replacementTerm);
>> }
>> }
>> }
>> }
>>
>> }
>>
>> public void searchReplace() {
>> Range docRange = this.wordDocument.getRange();
>> int numParas = docRange.numParagraphs();
>> for(int i = 0; i < numParas; i++) {
>> Paragraph para = docRange.getParagraph(i);
>> String text = para.text();
>> for(String key : this.searchKeys) {
>> if(text.contains(key)) {
>> String replacementTerm = this.searchTerms.get(key);
>> para.replaceText(key, replacementTerm);
>> }
>> }
>> }
>> }
>>
>> public void saveResults(String filename) throws
>> FileNotFoundException,
>> IOException {
>> File file = null;
>> FileOutputStream fos = null;
>> try {
>> file = new File(filename);
>> fos = new FileOutputStream(file);
>> this.wordDocument.write(fos);
>> }
>> finally {
>> if(fos != null) {
>> try {
>> fos.close();
>> fos = null;
>> }
>> catch(Exception ex) {
>> // I G N O R E
>> }
>> }
>> }
>> }
>>
>> /**
>> * @param args the command line arguments
>> */
>> public static void main(String[] args) {
>> try {
>> SearchReplace sr = new SearchReplace();
>> sr.openTemplate("C:/temp/Test Document.doc");
>> sr.searchAndReplace();
>> //sr.searchReplace();
>> sr.saveResults("C:/temp/New Updated Document.doc");
>> }
>> catch(Exception ex) {
>> System.out.println("Caught an: " + ex.getClass().getName());
>> System.out.println("Message: " + ex.getMessage());
>> System.out.println("Stacktrace follows............");
>> ex.printStackTrace(System.out);
>> }
>> }
>> }
>>
>> More particularly, look at the main method. If you comment out the
>> sr.searchAndReplace() and un-comment the sr.searchReplace() line, then
>> the
>> code will work successfully. But, and this is a BIG but, it will only
>> work
>> if you compile and run it against 3.2 FINAL of the API. I have found that
>> later versions seem to 'drop' or lose the formatting information
>> completely;
>> to convince yourself of this, just modify the main method so that it
>> contains only these lines of code;
>>
>> SearchReplace sr = new SearchReplace();
>> sr.openTemplate("C:/temp/Test Document.doc");
>> sr.saveResults("C:/temp/New Updated Document.doc");
>>
>> If you run that against versions later than 3.2 FINAL, you should see
>> that
>> the copy of the original document that this produces loses all of it's
>> formatting.
>>
>> Yours
>>
>> Mark B
>>
>> PS. I guess that it should go without saying, you will need to replace
>> the
>> paths to and document names passed to the openTemplate() and
>> saveResults()
>> methods to point to locations and files that exist on your machine.
>>
>> PPS Forgive the lack of comments please. I hope that the it is apparant
>> just
>> what the methods do.
>>
>>
>> Fabián Avilés Martínez wrote:
>>>
>>> Hi, as I told you, I have tried it, but with the same result, the
>>> resulting file is corrupted, that is what MSWord says. My next approach
>>> is
>>> to create a copy file, and do modifications within this file. My problem
>>> is that I do not know how to save modifications done in the charRuns of
>>> the paragraphs, what I mean is to persist modifications done in the
>>> resulting file, without have to coopy it, calling
>>> document.write(outputStream)
>>>
>>> My code is:
>>>
>>> public File processFile(final InputStream is, final Map<String, String>
>>> replacementText) throws IOException {
>>> Set<String> keys = replacementText.keySet();
>>> try {
>>> // Makes a copy of the file.
>>> File res = copyfile(is);
>>> InputStream auxIs = new FileInputStream(res);
>>> POIFSFileSystem poifs = new POIFSFileSystem(auxIs);
>>> HWPFDocument document = new HWPFDocument(poifs);
>>> Range range = document.getRange();
>>>
>>> for (int i = 0; i < range.numParagraphs(); i++) {
>>> Paragraph paragraph = range.getParagraph(i);
>>> int numCharRuns = paragraph.numCharacterRuns();
>>> for (int j = 0; j < numCharRuns; j++) {
>>> CharacterRun charRun = paragraph.getCharacterRun(j);
>>> for (Iterator<String> it = keys.iterator();
>>> it.hasNext();) {
>>> String key = it.next();
>>> if (charRun.text().contains(key)) {
>>> String value = replacementText.get(key);
>>> charRun.replaceText(key, value);
>>> range = document.getRange();
>>> paragraph = range.getParagraph(i);
>>> charRun = paragraph.getCharacterRun(j);
>>> }
>>> }
>>> }
>>> }
>>> is.close();
>>> return res;
>>> } catch (IOException e) {
>>> logger.error("Error procesando el fichero WORD: " + e);
>>> throw new IOException("Error procesando el fichero WORD");
>>> } finally {
>>> if (is != null) {
>>> is.close();
>>> }
>>> }
>>> }
>>>
>>>
>>> Thanks in advance, Fabi.
>>>
>>> -----Mensaje original-----
>>> De: MSB [mailto:markbrdsly@tiscali.co.uk]
>>> Enviado el: martes, 24 de noviembre de 2009 8:43
>>> Para: user@poi.apache.org
>>> Asunto: Re: Modify word document
>>>
>>>
>>> You have not dug down far enough into the structure of the document yet
>>> I
>>> am
>>> afraid - all of the formatting information is stopred (encapsulated)
>>> within
>>> the CharacterRun class and you need to perform the repllacements at that
>>> level.
>>>
>>> I do not have any suitable code at hand as I type this so what follows
>>> will
>>> need to be converted into Java and tested;
>>>
>>> Open the Word document.
>>> Get the overall Range for the document.
>>> Get the number of Paragraph objects the Range contains.
>>> Iterate through the Pargraphs and for each Pargraph
>>> Get the CharacterRun(s) the Paragraph contains.
>>> Call the method to replace the search term with the replacement text
>>> on
>>> the CharacterRun
>>> Save the modified document away again.
>>>
>>> You do however face a couple of problems with this. It has been a long
>>> time
>>> since I tried to write a search and replace routine using HWPF and I
>>> could
>>> not get it to work if the replacement text was longer that the search
>>> term.
>>> In that case, HWPF threw an exception and would not allow me to complete
>>> the
>>> process; but that problem could well have been addressed by now as it
>>> was
>>> well known and caused by faulty bounds checking within the Range class.
>>> Only
>>> testing will prove or disprove this for you I am afraid.
>>>
>>> Secondly, the CharacterRun class encapsulates a piece of text with
>>> common
>>> properties. So, imagine that we are searching for the phrase 'search
>>> term'
>>> and that the word 'search' has been emboldened whilst the word 'term'
>>> has
>>> been left as normal text, then my suggested approach will not work. That
>>> is
>>> because the words search and term will be held in different
>>> CharacterRun(s).
>>> If you do hit this problem, then I am afraid you will have to write code
>>> that searches for the term at the Paragraph level and that identifies
>>> where
>>> the search terms can be found and recovers the CharacterRun(s) that
>>> encapsulate them. Once you have these, you can modify the runs or create
>>> and
>>> substitute new ones but I have to admit that I have never tried to do
>>> this
>>> myself. Instead I chose to automate Word using OLE and to explore the
>>> possibilities offered by OpenOffices UNO interface. Both options did
>>> work
>>> but threw up other problems that proved more limiting (in terms of
>>> architecture and platform). If you can get it to work, HWPF offers the
>>> better solution IMO.
>>>
>>> Yours
>>>
>>> Mark B
>>>
>>>
>>> Fabián Avilés Martínez wrote:
>>>>
>>>> Hi all,
>>>> I have a Word document, as a template: In this template there are
>>>> some
>>>> tokenized words, which have to be modified and the result has to be
>>>> saved
>>>> into another file. The original file has some properties, like header
>>>> and
>>>> footer, images, etc. The resulting file has to be the same, but with
>>>> the
>>>> modified words. I am trying it with the code below, but it does not
>>>> work.
>>>>
>>>> public ByteArrayOutputStream processFile(final InputStream is, final
>>>> Map<String, String> replacementText)
>>>> throws IOException {
>>>> Set<String> keys = replacementText.keySet();
>>>> try {
>>>> POIFSFileSystem poifs = new POIFSFileSystem(is);
>>>> HWPFDocument document = new HWPFDocument(poifs);
>>>> Range range = document.getRange();
>>>>
>>>> for (int i = 0; i < range.numParagraphs(); i++) {
>>>> String newTxt = range.getParagraph(i).text();
>>>> String oldTxt = range.getParagraph(i).text();
>>>> for (Iterator<String> it = keys.iterator();
>>>> it.hasNext();)
>>>> {
>>>> String key = it.next();
>>>> if (newTxt.contains(key)) {
>>>> newTxt = replacePlaceholders(key,
>>>> replacementText.get(key), newTxt);
>>>> }
>>>> }
>>>> if (!oldTxt.equals(newTxt)) {
>>>> range.getParagraph(i).replaceText(oldTxt, newTxt);
>>>> }
>>>> }
>>>>
>>>> // Save the document away.
>>>> ByteArrayOutputStream bos = new ByteArrayOutputStream();
>>>> document.write(bos);
>>>> bos.flush();
>>>> bos.close();
>>>> return bos;
>>>> } catch (IOException e) {
>>>> logger.error("Error procesando el fichero WORD: " + e);
>>>> throw new IOException("Error procesando el fichero WORD");
>>>> } finally {
>>>> if (is != null) {
>>>> is.close();
>>>> }
>>>> }
>>>> }
>>>>
>>>> Any help, please?
>>>>
>>>> Thanks in advance, Fabi.
>>>>
>>>>
>>>>
>>>> ______________________
>>>> This message including any attachments may contain confidential
>>>> information, according to our Information Security Management System,
>>>> and intended solely for a specific individual to whom they are
>>>> addressed.
>>>> Any unauthorised copy, disclosure or distribution of this message
>>>> is strictly forbidden. If you have received this transmission in
>>>> error,
>>>> please notify the sender immediately and delete it.
>>>>
>>>> ______________________
>>>> Este mensaje, y en su caso, cualquier fichero anexo al mismo,
>>>> puede contener informacion clasificada por su emisor como confidencial
>>>> en el marco de su Sistema de Gestion de Seguridad de la
>>>> Informacion siendo para uso exclusivo del destinatario, quedando
>>>> prohibida su divulgacion copia o distribucion a terceros sin la
>>>> autorizacion expresa del remitente. Si Vd. ha recibido este mensaje
>>>> erroneamente, se ruega lo notifique al remitente y proceda a su
>>>> borrado.
>>>> Gracias por su colaboracion.
>>>>
>>>> ______________________
>>>>
>>>>
>>>> ---------------------------------------------------------------------
>>>> 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/Modify-word-document-tp26480450p26491636.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
>>>
>>>
>>> ______________________
>>> This message including any attachments may contain confidential
>>> information, according to our Information Security Management System,
>>> and intended solely for a specific individual to whom they are
>>> addressed.
>>> Any unauthorised copy, disclosure or distribution of this message
>>> is strictly forbidden. If you have received this transmission in error,
>>> please notify the sender immediately and delete it.
>>>
>>> ______________________
>>> Este mensaje, y en su caso, cualquier fichero anexo al mismo,
>>> puede contener informacion clasificada por su emisor como confidencial
>>> en el marco de su Sistema de Gestion de Seguridad de la
>>> Informacion siendo para uso exclusivo del destinatario, quedando
>>> prohibida su divulgacion copia o distribucion a terceros sin la
>>> autorizacion expresa del remitente. Si Vd. ha recibido este mensaje
>>> erroneamente, se ruega lo notifique al remitente y proceda a su
>>> borrado.
>>> Gracias por su colaboracion.
>>>
>>> ______________________
>>>
>>>
>>>
>>
>> --
>> View this message in context:
>> http://old.nabble.com/Modify-word-document-tp26480450p26498333.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
>>
>>
>> ______________________
>> This message including any attachments may contain confidential
>> information, according to our Information Security Management System,
>> and intended solely for a specific individual to whom they are
>> addressed.
>> Any unauthorised copy, disclosure or distribution of this message
>> is strictly forbidden. If you have received this transmission in error,
>> please notify the sender immediately and delete it.
>>
>> ______________________
>> Este mensaje, y en su caso, cualquier fichero anexo al mismo,
>> puede contener informacion clasificada por su emisor como confidencial
>> en el marco de su Sistema de Gestion de Seguridad de la
>> Informacion siendo para uso exclusivo del destinatario, quedando
>> prohibida su divulgacion copia o distribucion a terceros sin la
>> autorizacion expresa del remitente. Si Vd. ha recibido este mensaje
>> erroneamente, se ruega lo notifique al remitente y proceda a su borrado.
>> Gracias por su colaboracion.
>>
>> ______________________
>>
>>
>>
>
> --
> View this message in context:
> http://old.nabble.com/Modify-word-document-tp26480450p26498547.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
>
>
> ______________________
> This message including any attachments may contain confidential
> information, according to our Information Security Management System,
> and intended solely for a specific individual to whom they are addressed.
> Any unauthorised copy, disclosure or distribution of this message
> is strictly forbidden. If you have received this transmission in error,
> please notify the sender immediately and delete it.
>
> ______________________
> Este mensaje, y en su caso, cualquier fichero anexo al mismo,
> puede contener informacion clasificada por su emisor como confidencial
> en el marco de su Sistema de Gestion de Seguridad de la
> Informacion siendo para uso exclusivo del destinatario, quedando
> prohibida su divulgacion copia o distribucion a terceros sin la
> autorizacion expresa del remitente. Si Vd. ha recibido este mensaje
> erroneamente, se ruega lo notifique al remitente y proceda a su borrado.
> Gracias por su colaboracion.
>
> ______________________
>
>
>
--
View this message in context: http://old.nabble.com/Modify-word-document-tp26480450p26514349.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