Thanks for Amol's BiffViews, I was able to show the active cell in
window's display pane. Here's what I did to do just that.
1) Added two methods to org.apache.poi.hssf.model.Sheet.java to set
TopRow and LeftCol properties, respectively:
/**
* Sets the top row to show in desktop window pane.
* @param the top row to show in desktop window pane
*/
public void setTopRow(short topRow){
windowTwo.setTopRow(topRow);
}
/**
* Sets the left column to show in desktop window pane.
* @param the left column to show in desktop window pane
*/
public void setLeftCol(short leftCol){
windowTwo.setLeftCol(leftCol);
}
2) Added one method to the org.apache.poi.hssf.usermodel.HSSFSheet to
set display in pane:
/**
* Sets desktop window pane display area.
* @param the top row to show in desktop window pane
* @param the left column to show in desktop window pane
*/
public void showInPane(int row, int col){
this.sheet.setTopRow((short)row);
this.sheet.setLeftCol((short)col);
}
After that, make a call to sheet.showInPane(20,0); will show the A20
cell in window pane:
POIFSFileSystem fs = new POIFSFileSystem(
new FileInputStream("c:/sample.xls"));
HSSFWorkbook workbook = new HSSFWorkbook(fs,true); HSSFSheet sheet =
workbook.getSheetAt(0); HSSFRow row = sheet.getRow(20); HSSFCell cell =
row.getCell((short)0); cell.setAsActiveCell(); sheet.showInPane(20,0);
// Show A20 in display pane POIUtils.dumpWorkbookToFile(workbook,
"c:/new_sample.xls");
Thanks all for the help,
Jimmy
-----Original Message-----
From: Amol Deshmukh [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 03, 2005 6:42 PM
To: 'POI Users List'
Subject: RE: How to mandate showing the active cell when the Excel file
is first opened?
Shawn,
the problem as I tracked it using BiffViewer is that
setAsActiveCell() does not actually put the cell into
view, but there is another parameter "[WINDOW].toprow"
that controls this. Apparently POI HSSF does not have
an API call to set this.
BiffView results snippets:
In the file generated by saving from within MsExcel
(with active cell = D100 & toprow in the view = 58):
<snip>
[WINDOW2]
.options = 6b6
.dispformulas= false
.dispgridlins= true
.disprcheadin= true
.freezepanes = false
.displayzeros= true
.defaultheadr= true
.arabic = false
.displayguts = true
.frzpnsnosplt= false
.selected = true
.paged = true
.svdinpgbrkpv= false
.toprow = 39
.leftcol = 0
.headercolor = 40
.pagebreakzoom = 0
.normalzoom = 0
.reserved = 0
[/WINDOW2]
Offset 0x710 (1808)
recordid = 0x1d, size = 15
[SELECTION]
.pane = 3
.activecellrow = 63
.activecellcol = 3
.activecellref = 0
.numrefs = 1
[/SELECTION]
</snip>
Whereas calling cell.setAsActiveCell and BiffViewing the generated file
gives:
<snip>
[WINDOW2]
.options = 6b6
.dispformulas= false
.dispgridlins= true
.disprcheadin= true
.freezepanes = false
.displayzeros= true
.defaultheadr= true
.arabic = false
.displayguts = true
.frzpnsnosplt= false
.selected = true
.paged = true
.svdinpgbrkpv= false
.toprow = 0
.leftcol = 0
.headercolor = 40
.pagebreakzoom = 0
.normalzoom = 0
.reserved = 0
[/WINDOW2]
Offset 0x71e (1822)
recordid = 0x1d, size = 15
[SELECTION]
.pane = 3
.activecellrow = 63
.activecellcol = 3
.activecellref = 0
.numrefs = 1
[/SELECTION]
</snip>
Which means that although BiffViewer shows that
POI generated excel sets the value for the
"[SELECTION].activecellrow" and
"[SELECTION].activecellcol" match those from MsExcel
generated file, the value for "[WINDOW2].toprow"
is not set correctly by POI (Probably POI ignores
this altogether).
So probably it would help to have a sheet.setTopRow()
or some such method that sets the top row in focus.
HTH,
~ amol
> -----Original Message-----
> From: Shawn Laubach [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, May 03, 2005 4:39 PM
> To: 'Abhi'; 'POI Users List'
> Subject: RE: How to mandate showing the active cell when the
> Excel file
> is first opened?
>
>
> From what I remember, it should show up on the screen if that
> is also the
> active sheet. I'd just run a quick test and create a sheet
> with some data
> in the first cell and one at the bottom or something and make
> one of the
> bottom ones active.
>
>
>
> Shawn
>
>
>
> _____
>
> From: Abhi [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, May 03, 2005 8:56 AM
> To: Shawn Laubach
> Subject: Re: How to mandate showing the active cell when the
> Excel file is
> first opened?
>
>
>
> hey, setAsActiveCell sets a given cell as the active cell.
> But does it also
> ensure that when the file is opened in excel, this is the
> cell shown on the
> screen? I mean the cell would be the active cell but might
> still be outside
> the displayed screen area (on the display unit) of the excel sheet.
>
>
>
> plz help if u have any idea
>
>
>
> On 5/3/05, Shawn Laubach <[EMAIL PROTECTED]> wrote:
>
> http://jakarta.apache.org/poi/apidocs/org/apache/poi/hssf/user
> model/HSSFCell
>
> .html#setAsActiveCell()
>
> Shawn
>
> -----Original Message-----
> From: Abhi [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, May 03, 2005 7:42 AM
> To: POI Users List
> Subject: Re: How to mandate showing the active cell when the
> Excel file is
> first opened?
>
> did u find a solution? please do let me know the solution(s).
>
> On 4/30/05, Li, Jianming non Unisys < <mailto:[EMAIL PROTECTED]>
> [EMAIL PROTECTED]> wrote:
> >
> > Hi there,
> >
> > We are using POI to generate a large Excel file (the work
> sheet contains
> > around 50,000 rows of data) for our client. Our client
> mandates that
> > when the file is first opened with Excel, it shows the last
> row data. I
> > can set the first cell of the last row as active cell with
> > hSSFCell.setAsActiveCell(). But how can I programmatically
> dictate that
> > the active cell be shown before I write HSSFWorkbook to the actually
> > file?
> >
> > Any clue is grealy appreciated,
> >
> > Jimmy Lee
> >
> >
>
> --
> Abhi
> trin-trin: 9810760474
> ************************************************
> Love is the desire to do good to others
>
> GREATEST is the one who LOVES all creations of GOD
>
>
>
>
> --
> Abhi
> trin-trin: 9810760474
> ************************************************
> Love is the desire to do good to others
>
> GREATEST is the one who LOVES all creations of GOD
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
