RE: How to mandate showing the active cell when the Excel file is first opened?

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

RE: How to mandate showing the active cell when the Excel file is first opened?

Li, Jianming non Unisys

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:[hidden email]]
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:[hidden email]]
> 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:[hidden email]]
> 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 <[hidden email]> wrote:
>
> http://jakarta.apache.org/poi/apidocs/org/apache/poi/hssf/user
> model/HSSFCell
>
> .html#setAsActiveCell()
>
> Shawn
>
> -----Original Message-----
> From: Abhi [mailto:[hidden email]]
> 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:[hidden email]>
> [hidden email]> 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: [hidden email]
Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/