You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by jasenj1 <ja...@mitre.org> on 2012/03/23 21:26:02 UTC
Trouble with hiding rows in Excel.
I'm using poi-3.8-beta5. I've created a spreadsheet that writes out fine and
now I'm trying to add some styling/formatting. I would like to hide the
first two rows. Based on the docs it seems like the following should work:
CellStyle hidden = out_wb.createCellStyle(); // Create a new style in the
workbook
hidden.setHidden(true); // make it hidden
out_sheet.getRow(0).setRowStyle(hidden); // Set the style on the row.
But the above does not work. I did a little Googling and found a
stackoverflow answer recommending the above, but it is not working for me.
It would be nice if the HOWTO for POI showed some examples of hiding rows,
columns, & cells - assuming it works by some method other than what I'm
doing.
- Jasen.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Trouble-with-hiding-rows-in-Excel-tp5590626p5590626.html
Sent from the POI - User mailing list archive at Nabble.com.
Re: Trouble with hiding rows in Excel.
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Thanks for that Jasen. I thought it had worked successfully but did not know
how to unhide the rows once I had the workbook open in OpenOffice - could
not select them of course and did not know what the keyboard short-cut was.
Must admit that I agree with the naming of the method and updating the How
To on the site. I cannot take care of this tonight but I will write up
something that includes a bit of simple code similar to the one I sent to
you and forward it to Yegor for possible inclusion. With regards to
re-naming the method, I do not think we should as it might break some
existing code out there. Instead, I would argue for updating the javadoc to
reflect the methods purpose and will again suggest this to Yegor - that's
assuming he is not following this discussion of course.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Trouble-with-hiding-rows-in-Excel-tp5590626p5595805.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: Trouble with hiding rows in Excel.
Posted by jasenj1 <ja...@mitre.org>.
Excellent! "Unhide" works for both the XLS and XLSX file.
So it seems to be a case of the method being poorly named. In the XLSX
format, it is definitely setting the "hidden" attribute, not setting the
height to zero.
I would strongly recommend the HOWTO be updated to show this is the "proper"
way to hide a row. And possibly the JavaDocs be updated to reflect what is
really being done.
Thanks for your help.
- Jasen.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Trouble-with-hiding-rows-in-Excel-tp5590626p5595720.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: Trouble with hiding rows in Excel.
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Well, at least with reference to the xml based file format, calling the
setZeroHeight() method does indeed hide the row.
Can I ask you to compile and run this piece of code for me please?
import java.io.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
*
* @author Mark Beardsley
*/
public class HidingTest {
public HidingTest(String filename) {
File file = null;
FileOutputStream fos = null;
Workbook workbook = null;
Sheet sheet = null;
Row row = null;
Cell cell = null;
CellStyle cellStyle = null;
try {
//
// Does the uwser want to create a binary or OpenXML based
workbook?
//
if(filename.endsWith("xlsx")) {
workbook = new XSSFWorkbook();
}
else {
workbook = new HSSFWorkbook();
}
// Create the cell style for the hidden row. I am going to try
and
// hide row 5.
cellStyle = workbook.createCellStyle();
cellStyle.setHidden(true);
// Insert a new sheet into the workbook
sheet = workbook.createSheet("Hiding Test");
for(int i = 0; i < 10; i++) {
row = sheet.createRow(i);
cell = row.createCell(i);
cell.setCellValue(i);
if(i == 4) {
row.setZeroHeight(true);
}
}
// Save the file away to disc.
file = new File(filename);
fos = new FileOutputStream(file);
workbook.write(fos);
}
catch(IOException ioEx) {
HidingTest.printException(ioEx);
}
finally {
if(fos != null) {
try {
fos.close();
fos = null;
}
catch(IOException ioEx) {
HidingTest.printException(ioEx);
}
}
}
}
private static void printException(Throwable th) {
System.out.println("Thrown: " + th.getClass().getName());
System.out.println("Message: " + th.getMessage());
System.out.println("Stacktrace follows:.....");
th.printStackTrace(System.out);
}
}
If you run it twice, something like this;
new HidingTest("C:/temp/Hidden.xlsx");
new HidingTest("C:/temp/Hidden.xls");
Then it should produce workbooks both of which have row 5 hidden. I have
checked the xml produced by the new HidingTest("C:/temp/Hidden.xlsx") call
as that is a simple matter of unzipping the archive and using a text ediotr
to take a look, and I found this fragment in the sheet1.xml file;
<row r="5" hidden="true"><c r="E5" t="n"><v>4.0</v></c></row>
which does indeed suggest that the setZeroHeight() method hides the row.
Will you still check that for me please and make sure that users can
successfully unhide the rows (the acid test so to speak)?
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Trouble-with-hiding-rows-in-Excel-tp5590626p5595608.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: Trouble with hiding rows in Excel.
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Also, and I should have said this, if you do log it as a bug, can you make
sure to upload a test spreadsheet(s) and the code to illustrate the problem
please?
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Trouble-with-hiding-rows-in-Excel-tp5590626p5595445.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: Trouble with hiding rows in Excel.
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
I think that yes, it would be worth logging this through bugzilla. So far, I
have not been able to find any other obviously available method that does
hide the row and I have tried the simple things such as making sure all of
the cells on the row are also hidden before trying to hide the row.
Can I ask a question though before you do log this as a bug; are you
targetting the older binary format and the newer xml based one OR just the
latter? If you are only targetting the xml based file format exclusively
then it should (might) be possible to get at the bean in the OpenXML layer
that controls the row and call the method on that to hide the row. That is a
might however as I have not yet investigated the possibility. Also, I am
going to try using the setZeroHeight() method just to see if this does in
fact hide the row rather than simply set it's height to zero. The does sound
odd I know but you never do know what might happen.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Trouble-with-hiding-rows-in-Excel-tp5590626p5595435.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: Trouble with hiding rows in Excel.
Posted by jasenj1 <ja...@mitre.org>.
Thank you for investigating and confirming that POI doesn't allow rows to be
hidden the same way Excel does. And that is the heart of the problem. Yes,
setting the row height to zero works, but then the user of the Excel sheet
cannot use the "Unhide" command to reveal the rows. Just as setting the font
and background colors the same works to hide the contents of a cell, it's
not really the right way.
Should I file a feature request somewhere?
- Jasen.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Trouble-with-hiding-rows-in-Excel-tp5590626p5595000.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: Trouble with hiding rows in Excel.
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
With the F1 race suspended owing to rain in Malaysia, I had the chance to
play around with some code and can confirm your findings. There is a
difference between the xml created using the setRowStyle() method and hiding
the row using Excel. The latter actually sets a hidden attribute on the row
record whereas the former creates a style in the styles.xml file and links
the row record to that. I do wonder - and that is wonder - if the
setRowStyle() method creates a record that applies the style to the cells
the row contains rather than the row itself; I do not know this and have not
tested it but it might be the case. For now, I suggest you take tcole6's
suggestion and set the row height to 0; there are two ways to accomplish
this, either setRowHeight((short)0) or setZerHeight(true).
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Trouble-with-hiding-rows-in-Excel-tp5590626p5592989.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: Trouble with hiding rows in Excel.
Posted by tcole6 <tc...@gmail.com>.
jasenj1 wrote
>
>
> CellStyle hidden = out_wb.createCellStyle(); // Create a new style in the
> workbook
> hidden.setHidden(true); // make it hidden
> out_sheet.getRow(0).setRowStyle(hidden); // Set the style on the row.
>
>
Have you tried setting the row height to 0?
out_sheet.getRow(0).setHeight((short) 0);
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Trouble-with-hiding-rows-in-Excel-tp5590626p5590721.html
Sent from the POI - User mailing list archive at Nabble.com.