Discussion:
showInPane does not work
Bengt Rodehav
9 years ago
Permalink
We are generating Excel templates for our reporting customers. We would
like the top-left cell of every tab to be selected and visible when the
Excel template is opened.

I use the Cell.setAsActiveCell to select the cell and this works. I then
use Shtt.showInPane(0, 0) to set the top row and the left column to 0.
However this does not work. I've verified this both visually via Excel and
by calling the Sheet.getTopRow() and the Sheet.getLeftCol() methods. I've
read somewhere that there should also be setters for the top row and left
column properties but I haven't seen them but I assume that the
showInPanel() method is supposed to do that.

I've tried this in POI versions 3.11, 3.12 and 3.13-beta1 but it doesn't
work in any of them.

Is this a known bug or have I misunderstood the functionality.

Best regards,

Bengt Rodehav
Mark Beardsley
9 years ago
Permalink
Just a thought from off of the top of my head - and I have not yet tried this
out - but what happens if yoi call the setAsActiveCell() method after you
create each sheet of the workbook?

The other alternative would be to create a workbook macro that gives cell A1
focus whenever the user selects a new tab but this could well have
undesirable consequences.

If I have the chance tonight, I will try this out for myself.



--
View this message in context: http://apache-poi.1045710.n5.nabble.com/showInPane-does-not-work-tp5720321p5720322.html
Sent from the POI - User mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe, e-mail: user-***@poi.apache.org
For additional commands, e-mail: user-***@poi.apache.org
Bengt Rodehav
9 years ago
Permalink
Hello Mark and thank you for your reply,

I'm not exactly sure what you mean. Perhaps my case is a bit special
though. I use a base template that contains too many sheets. I then remove
sheets and modify them a bit. I also add a couple of sheets. However, most
sheets are not created by me (POI) but already exist.

Would be nice if you could confirm if the showInPane() method works. If it
works for you then maybe there is something in my scenario that messes
things up.

Thanks,

/Bengt
...
Bengt Rodehav
9 years ago
Permalink
Did you have a chance to see if showInPane() works Mark?

/Bengt
...
Mark Beardsley
9 years ago
Permalink
If I am correct, showInPane() is not at all what you are after.

With Excel, it is possible to lock one or more rows/columns so that they
remain in view if the user scrolls through the contents of the worksheet.
One obvious use is when you place labels at the top of each column to show
what figures they contain. If you do not lock the first row then those
labels will disappear as the user scrolls down the sheet. The word pane is
used to refer to the area of the sheet that does scroll as I understand it
so if you want a specific part of the sheet to show within the scrollable
pane you call the show in pane method.

Now that I understand more what you are after, I would suggest a simple
test. Once you have created the workbook and just before you save it away to
file, iterate through each of the sheets the workbook and call the
setActiveCell() method for each sheet passing as a parameter the reference
of the cell you wish to see as active when the sheet is selected by your
user. I do not know if this will work but it should be a very quick, easy
test to run; sorry to say I have not yet had the chance to test the theory
but it is sound enough I think..



--
View this message in context: http://apache-poi.1045710.n5.nabble.com/showInPane-does-not-work-tp5720321p5720340.html
Sent from the POI - User mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe, e-mail: user-***@poi.apache.org
For additional commands, e-mail: user-***@poi.apache.org
Mark Beardsley
9 years ago
Permalink
Seems to work. I wrote and ran this to test the idea

import java.io.*;
import org.apache.poi.xssf.usermodel.*;

public class ActiveCellTest {

public static void main(String[] args) {

File file = null;
FileOutputStream fos = null;
BufferedOutputStream bos = null;

XSSFWorkbook workbook = null;
XSSFSheet sheet = null;
XSSFRow row = null;
XSSFCell cell = null;

// Build a workbook with five sheets for testing
workbook = new XSSFWorkbook();
for(int i = 0; i < 5; i++) {
sheet = workbook.createSheet("Sheet Number " + i);
for(int j = 0; j < 30; j++) {
row = sheet.createRow(j);
for(int k = 0; k < 20; k++) {
cell = row.createCell(k);
cell.setCellValue("Row " + j + " Column " + k);
}
}
}

// Iterate through the sheets and make cell active
for(int i = 0; i < workbook.getNumberOfSheets(); i++) {
sheet = workbook.getSheetAt(i);
// Select diffeent cell in column A to show selection is working
sheet.setActiveCell("A" + (i + 1));
}

// Save book away - change path to suit on your system
try {
file = new
File("/home/markb/Public/java/testdocs/ActiveCellTest.xlsx");
fos = new FileOutputStream(file);
bos = new BufferedOutputStream(fos);
workbook.write(bos);
}
catch(IOException ioEx) {
System.out.println("Catch of saving book away and caught an " +
ioEx.getClass().getName());
System.out.println("Message " + ioEx.getMessage());
System.out.println("Stacktrace");
ioEx.printStackTrace(System.out);
}
finally {
try {
if(bos != null) {
bos.flush();
bos.close();
}
}
catch(IOException ioEx) {
System.out.println("Catch of finally clause and caught an " +
ioEx.getClass().getName());
System.out.println("Message " + ioEx.getMessage());
System.out.println("Stacktrace");
ioEx.printStackTrace(System.out);
}
}
}
}

and all you need to do is set the active cell for each sheet in the
workbook.

Hope this helps.



--
View this message in context: http://apache-poi.1045710.n5.nabble.com/showInPane-does-not-work-tp5720321p5720353.html
Sent from the POI - User mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe, e-mail: user-***@poi.apache.org
For additional commands, e-mail: user-***@poi.apache.org
Bengt Rodehav
9 years ago
Permalink
Hello Mark,

I am already using Sheet.setActiveCell() but it doesn't help. The cell
becomes selected but it is not in view.

My use case is a bit different. I read an existing Excel document which is
a template for the Excel document I create. The source Excel sometimes has
sheets that are scrolled down a bit and I try to "tidy" that up up. I
therefore call the Sheet.setActiveCell() for every sheet and I then save
the Excel document to another file then the original file. Could this be a
problem? Everything else seems to work except the that the sheets are not
scrolled up to make the first cell visible.

I think the basic difference is that I do not create the sheets with POI -
I read existing sheets.

/Bengt
...
Mark Beardsley
9 years ago
Permalink
No answer off of the top of my head but let me play with a bit more code to
see what I can find out.



--
View this message in context: http://apache-poi.1045710.n5.nabble.com/showInPane-does-not-work-tp5720321p5720386.html
Sent from the POI - User mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe, e-mail: user-***@poi.apache.org
For additional commands, e-mail: user-***@poi.apache.org
Bengt Rodehav
9 years ago
Permalink
Thanks - I appreciate it.

/Bengt
Post by Mark Beardsley
No answer off of the top of my head but let me play with a bit more code to
see what I can find out.
--
http://apache-poi.1045710.n5.nabble.com/showInPane-does-not-work-tp5720321p5720386.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
Mark Beardsley
9 years ago
Permalink
No time yet to experiment with code - some people do not seem to understand
what I mean when I say that I have retired - but what I did intend to test
was combiningg a call to setActiveCell() with showInPane() something like
this;

showInPane()
setActiveCell()

that really did ought to work in my opinion. If not, it may be the case that
we need to ding into the OOXML layer underneath the api. I will certainly
have the time to do this over the weekend but most likely not before. Sorry
again but real life - well my old job - intrude in a very unwelcome manner.



--
View this message in context: http://apache-poi.1045710.n5.nabble.com/showInPane-does-not-work-tp5720321p5720416.html
Sent from the POI - User mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe, e-mail: user-***@poi.apache.org
For additional commands, e-mail: user-***@poi.apache.org
Bengt Rodehav
9 years ago
Permalink
No worries Mark - I'm happy with your help.

I do use both showInPane() and setActiveCell() but it doesn't work for me.
I'm doing it in the other order though - maybe that makes a difference?

/Bengt
...
Mark Beardsley
9 years ago
Permalink
Now for the bad news; currently, the api does not provide the necessary
interface to set the value within the xml markup for the workbook to control
the view in the way you require.

During today, I have had the opportunity to dig around and experiment with
some code. By looking at the raw xml markup, I have found that it is the
sheetView element and more specifically the topLeftCell attribute that
influences the workbooks behaviour in the way you require. To test this, I
created a workbook with a few sheets that had lots of rows and cells
populated. Using LibreOffice Calc, I opened the workbook and scrolled away
down the sheets selecting a cell at random before saving the workbook away
again. Unzipping the xml, I saw a few settings that could be changed and did
so in sequence, rezipping the xml markup and opening it again with Calc. If
I set the value of the topLeftCell attribute to A!, then the sheet would be
opened with that cell in the top left hand corner of the display, just as
you wanted. This did not make the cell active, nor give it focus, only made
sure it was displayed at the top left hand corner of the sheet.

It is possible to get at the address of this cell using the api. Two methods
are defined on the XSSFSheet class - getTopRow() and getLeftCol() - but
there are no corresponding setters for these values. In addition, the sheet
view object is declared as private within the class and I cannot see a quick
and easy way to get at it yet. I may well be able to do so but until then, I
am afraid your quest will have to be placed on hold.

Just for the sake of completeness, setActiveCell() will work but it does not
cause the cell to be scrolled into view. In like manner, showInPane() will
work but only if employed with the locking I described earlier. Called
either separately or together they will not have the effect you desire,
sorry. I suspect that only by setting the value of the topLeftCell()
attribute of the sheetView element will we accomplish this. I have the time
to dig around more this weekend and will let you know if I can make any
progress. Hopefully, I will be able to do something without needing to patch
the api.



--
View this message in context: http://apache-poi.1045710.n5.nabble.com/showInPane-does-not-work-tp5720321p5720431.html
Sent from the POI - User mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe, e-mail: user-***@poi.apache.org
For additional commands, e-mail: user-***@poi.apache.org
Bengt Rodehav
9 years ago
Permalink
Thanks a lot for your effort!

Yes, what you've found out does agree with the testing I've done. I've also
wondered why there is no setter for the topLeftCell which I also thought
would be what is required in this case.

Thanks for your help,

/Bengt
...
Mark Beardsley
9 years ago
Permalink
Three days of rain and it is too wet to work in the reed beds. So, I have
been able to play and make some real progress. The code below will make sure
that cell A1 is both active and appears at the top leaft hand corner of the
worksheet 'window'.

import java.io.*;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetViews;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetView;

public class ActiveCellTest {

public static void setCell(String inputFilename, String outputFilename) {

File file = null;
FileOutputStream fos = null;
BufferedOutputStream bos = null;
FileInputStream fis = null;
BufferedInputStream bis = null;
XSSFWorkbook workbook = null;
XSSFSheet sheet = null;
CTWorksheet ctWorksheet = null;
CTSheetViews ctSheetViews = null;
CTSheetView ctSheetView = null;

try {
file = new File(inputFilename);
fis = new FileInputStream(file);
bis = new BufferedInputStream(fis);

workbook = new XSSFWorkbook(bis);

bis.close();

// Iterate through the sheets and make cell active
for(int i = 0; i < workbook.getNumberOfSheets(); i++) {
sheet = workbook.getSheetAt(i);

// First step is to get at the CTWorksheet bean underlying the
worksheet.
ctWorksheet = sheet.getCTWorksheet();
// From the CTWorksheet, get at the sheet views.
ctSheetViews = ctWorksheet.getSheetViews();
// Grab a single sheet view from that array
ctSheetView =
ctSheetViews.getSheetViewArray(ctSheetViews.sizeOfSheetViewArray() - 1);
// Se the address of the top left hand cell.
ctSheetView.setTopLeftCell("A1");

// Also, make sure that cell A1 is the active cell
sheet.setActiveCell("A1");
}

file = new File(outputFilename);
fos = new FileOutputStream(file);
bos = new BufferedOutputStream(fos);
workbook.write(bos);

}
catch(IOException ioEx) {
System.out.println("Catch of setCell() and caught an " +
ioEx.getClass().getName());
System.out.println("Message " + ioEx.getMessage());
System.out.println("Stacktrace");
ioEx.printStackTrace(System.out);
}
finally {
try {
if(bos != null) {
bos.flush();
bos.close();
}
}
catch(IOException ioEx) {
System.out.println("Catch of finally clause in setCell() and caught
an " + ioEx.getClass().getName());
System.out.println("Message " + ioEx.getMessage());
System.out.println("Stacktrace");
ioEx.printStackTrace(System.out);
}
}
}
}

It really goes without saying, but to run the code do something like this;


ActiveCellTest.setCell("/home/markb/Public/java/testdocs/ActiveCellTest.xlsx",

"/home/markb/Public/java/testdocs/NewSetCellTest.xlsx");

where the first parameter is the path to and name of the Excel file you wish
to open and the second the path to and name under which it should be saved.

There is one big problem with the code as it stands - what happens if there
is no sheet view in the xml markup? Well, the answer is that we would have
to create a new one and I will look into that. For now, try the code out and
see if it accomplishes what you are after. Use it with care as I would never
make the claim that this is production ready code.




--
View this message in context: http://apache-poi.1045710.n5.nabble.com/showInPane-does-not-work-tp5720321p5720509.html
Sent from the POI - User mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe, e-mail: user-***@poi.apache.org
For additional commands, e-mail: user-***@poi.apache.org
Bengt Rodehav
9 years ago
Permalink
Thanks a lot - will try ASAP.

/Bengt
...
Bengt Rodehav
9 years ago
Permalink
I've tried your code and it works perfectly for me - thanks.

/Bengt
...
Mark Beardsley
9 years ago
Permalink
Thanks for letting me know and sorry again that it too so long. Might well
propose this as an enhance,ent to the api - maybe setTopLeftCell(Sttring
cellAddress).



--
View this message in context: http://apache-poi.1045710.n5.nabble.com/showInPane-does-not-work-tp5720321p5720524.html
Sent from the POI - User mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe, e-mail: user-***@poi.apache.org
For additional commands, e-mail: user-***@poi.apache.org
Bengt Rodehav
9 years ago
Permalink
I think that's an excellent idea. I did this:

private void scrollTopLeft(XSSFSheet theSheet) {
CTWorksheet ctWorksheet = theSheet.getCTWorksheet();
CTSheetViews ctSheetViews = ctWorksheet.getSheetViews();
CTSheetView ctSheetView =
ctSheetViews.getSheetViewArray(ctSheetViews.sizeOfSheetViewArray() - 1);
ctSheetView.setTopLeftCell("A1");
}

As you mentioned, some error handling should be added since the above could
theoretically throw NPE's. In my case, it doesn't. I'm also thinking that
there might be several sheet views (since there is an array) and perhaps
"setTopLeftCell" should be called for all of them...

/Bengt
Post by Mark Beardsley
Thanks for letting me know and sorry again that it too so long. Might well
propose this as an enhance,ent to the api - maybe setTopLeftCell(Sttring
cellAddress).
--
http://apache-poi.1045710.n5.nabble.com/showInPane-does-not-work-tp5720321p5720524.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
Mark Beardsley
9 years ago
Permalink
To find out how to get at the sheet view, I dug around in the source for the
XSSFSheet class. That class already included code to get at the array of
sheet views from the worksheet and it simply assumed that the first element
in the array should be used. The only check on size was simply to make sure
that at least one sheet view had been recovered ; if not then a new one was
created using the appropriate factory. On the one hand, this would make
adding support for the topLeftCell(String) method we talked about a very
simple task - all the support is there in the XSSFSheet class - but it does
duck the question of how to deal with the case of multiple sheet views -
assuming that can exist.

If I have the time this weekend, I will dig into the spec for the
appropriate file format and see if it comtains a definitive answer. Will
post if I find anything out.



--
View this message in context: http://apache-poi.1045710.n5.nabble.com/showInPane-does-not-work-tp5720321p5720531.html
Sent from the POI - User mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe, e-mail: user-***@poi.apache.org
For additional commands, e-mail: user-***@poi.apache.org
Bengt Rodehav
9 years ago
Permalink
Thanks a lot Mark,

/Bengt
...
arun.ka
7 years ago
Permalink
Dear Mark,

Please help me to resolve the below

I have the same scenario but now I am dealing with HSSF Workbook and HSSF
Sheet.
Do we have any workaround for HSSF instead of XSSF.
<<<<
// First step is to get at the CTWorksheet bean underlying the worksheet.
ctWorksheet = sheet.getCTWorksheet();
// From the CTWorksheet, get at the sheet views.
ctSheetViews = ctWorksheet.getSheetViews();
// Grab a single sheet view from that array
ctSheetView =
ctSheetViews.getSheetViewArray(ctSheetViews.sizeOfSheetViewArray() - 1);
// Se the address of the top left hand cell.
ctSheetView.setTopLeftCell("A1");
Please advice.



--
Sent from: http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html

---------------------------------------------------------------------
To unsubscribe, e-mail: user-***@poi.apache.org
For additional commands, e-mail: user-***@poi.apache.org
arun.ka
7 years ago
Permalink
Dear POI team and Mark,

Please help me out of that issue.
showInPane not working for HSSF.

BR,
ARUN



--
Sent from: http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html

---------------------------------------------------------------------
To unsubscribe, e-mail: user-***@poi.apache.org
For additional commands, e-mail: user-***@poi.apache.org
Murphy, Mark
7 years ago
Permalink
Not working is a very broad statement, what is happening, what did you expect? What does your code look like?

-----Original Message-----
From: arun.ka [mailto:***@gmail.com]
Sent: Wednesday, February 07, 2018 2:44 AM
To: ***@poi.apache.org
Subject: Re: showInPane does not work

Dear POI team and Mark,

Please help me out of that issue.
showInPane not working for HSSF.

BR,
ARUN



--
Sent from: http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html

---------------------------------------------------------------------
To unsubscribe, e-mail: user-***@poi.apache.org For additional commands, e-mail: user-***@poi.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: user-***@poi.apache.org
For additional commands, e-mail: user-***@poi.apache.org
Markus Sandmann
7 years ago
Permalink
Hi,

hm? I already described what is not working. Using JDK 9 some Imports
cannot be resolved using the same Libary and Code as with JDK 7 (where the
imports do work).
Screenshots and Codeparts can be seen in the linked StackOverFlow Post.

I don't know what else i could say about the problem. Because of the
failing imports I can't use the Classes and Functions in the code like -
for example- :

[...]

private HashMap<XSSFColor, XSSFCellStyle> xssfStyles;

public XLSXFileGeneratorWorker(JLabel statusLabel, File file) {
this.statusLabel = statusLabel;
this.xlsxFile = file;
this.xssfStyles = new HashMap<XSSFColor, XSSFCellStyle>();
}

[...]

Where the XSSFColor can not be resolved due to the import issue, etc.

Or another example as simple as:

XSSFWorkbook workbook = new XSSFWorkbook();

doesn't work either because of the import problem.

And as already stated, as soon as i switch back to JDK7 the exact same code
works.
Hope it is clearer now.

Greetings,
Markus
...
Markus Sandmann
7 years ago
Permalink
Sorry messed up, post went to the wrong list.
...
arun.ka
7 years ago
Permalink
Dear POI Team,
I am very sorry for unclear post.
Let me explain the situation.
I have the template.xls, that consists of macro with freezed pane.
Steps,
1. Reading the xls file (template.xls) from Input directory path
2. Writing the contents to that xls file[value pasting process based on
biz.]
3. Finally write it as new xls file [Target.xls] to output directory path

To do this, I am using the HSSFWorkbook, HSSFSheet etc.. stuffs.
So for everything is fine.

But, when I open the target file, view is showing at that bottom of the last
line like line number 350.
I have to manually move the vertical scrollbar to reach the next line of
freeze pane.

My requirement is it should be focus next line to freeze line when I open
the xls file. Meantime, xls file should not be corrupted.
Note: First 6 line are freezed and locked.

Please help me.

If still it is unclear let me post my code, java and poi.jar version
details.

BR,
Arun




--
Sent from: http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html

---------------------------------------------------------------------
To unsubscribe, e-mail: user-***@poi.apache.org
For additional commands, e-mail: user-***@poi.apache.org
arun.ka
7 years ago
Permalink
Dear POI Team,

Sorry for asking again.
Any updates.

BR,
Arun



--
Sent from: http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html

---------------------------------------------------------------------
To unsubscribe, e-mail: user-***@poi.apache.org
For additional commands, e-mail: user-***@poi.apache.org

Loading...