You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by "Haggerty, Michael" <mi...@sap.com> on 2007/04/27 16:27:38 UTC
how to find style in existing workbook for cell position with no cell
When a cell position (some row/column position such as 0,0) in an
existing workbook contains a cell, then its style can be found with
HSSFCell.getCellStyle(). When a cell position does not contain a cell
(HSSFRow.getCell() returns null), how can I find the style that would be
applied by Excel when a value is set into that cell position?
For example, a column can be formatted to have a specified fill color.
When working in Excel manually, a value typed into a cell in that column
will retain that fill color. I want to use HSSF to find that column's
style, create a new style based on it, and make some changes to it. I
cannot simply create a new style, because the default style does not
represent the column formatting, and the fill color is lost.
This question was asked by bchalla...@sqli.com on 22 Feb 2006 in this
mailing list, with the subject of "[Q] Excel -> HSFFCell, loosing style"
but no response was made.
Possible lead: Will the org.apache.poi.hssf.record.BlankRecord class
help in this case? An extended format record can be retrieved from
this, but how can a cell position be mapped to a BlankRecord?
Thank you.
--Michael
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
Re: how to find style in existing workbook for cell position with
Posted by "Andrew C. Oliver" <ac...@buni.org>.
An excellent question. Discovery is partly covered here:
http://jakarta.apache.org/poi/getinvolved/index.html
Otherwise one can use a combination of
org.apache.poi.hssf.dev.BiffViewer foo.xls > myfile and possibly
engineer files with the low level APIs/stuff to see what is visible when.
The Excel 97 dev kit explains this somewhat but I do not recall
precedence being directly covered in the way you are looking for in that
level of detail.
Haggerty, Michael wrote:
> The main question I would have in implementing Sheet.findStyle is that
> if a style (an extended format record) is found in a ColumnInfoRecord, a
> RowRecord, a merged region record, etc., that all cover the same cell
> position, then which one takes precedence? The last one created? If
> so, then how is the creation order determined?
>
> --Michael
>
> -----Original Message-----
> From: Andrew C. Oliver [mailto:acoliver@buni.org]
> Sent: Friday, April 27, 2007 10:40 PM
> To: POI Users List
> Subject: Re: how to find style in existing workbook for cell position
> with
>
> That said if someone wanted to implement Sheet.findStyle(row,col) that
> wouldn't be silly. It would be silly to return HSSFCell however.
>
> Andrew C. Oliver wrote:
>
>> Guys HSSF is a file format API not a spreadsheet DOM even though
>> usermodel exhibits some of those features to make things easier. It
>> doesn't make sense for HSSF to map anything to BlankRecord except
>> BlankRecord. Besides, you want HSSF to suck down MORE memory be
>> creating MORE objects? Seriously.
>>
>> -Andy
>>
>> Levi Strope wrote:
>>
>>> I believe it can be mapped to a BlankRecord because the cell itself
>>> still contains cell formatting.
>>>
>>>
>>> -----Original Message-----
>>> From: Haggerty, Michael [mailto:michael.haggerty@sap.com] Sent:
>>> Friday, April 27, 2007 10:28 AM
>>> To: poi-user@jakarta.apache.org
>>> Subject: how to find style in existing workbook for cell position
>>>
> with
>
>>> no cell
>>>
>>> When a cell position (some row/column position such as 0,0) in an
>>> existing workbook contains a cell, then its style can be found with
>>> HSSFCell.getCellStyle(). When a cell position does not contain a
>>>
> cell
>
>>> (HSSFRow.getCell() returns null), how can I find the style that would
>>>
> be
>
>>> applied by Excel when a value is set into that cell position?
>>>
>>> For example, a column can be formatted to have a specified fill
>>>
> color.
>
>>> When working in Excel manually, a value typed into a cell in that
>>>
> column
>
>>> will retain that fill color. I want to use HSSF to find that
>>>
> column's
>
>>> style, create a new style based on it, and make some changes to it.
>>>
> I
>
>>> cannot simply create a new style, because the default style does not
>>> represent the column formatting, and the fill color is lost.
>>>
>>> This question was asked by bchalla...@sqli.com on 22 Feb 2006 in this
>>> mailing list, with the subject of "[Q] Excel -> HSFFCell, loosing
>>>
> style"
>
>>> but no response was made.
>>>
>>> Possible lead: Will the org.apache.poi.hssf.record.BlankRecord class
>>> help in this case? An extended format record can be retrieved from
>>> this, but how can a cell position be mapped to a BlankRecord?
>>>
>>> Thank you.
>>>
>>> --Michael
>>>
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
>>> Mailing List: http://jakarta.apache.org/site/mail2.html#poi
>>> The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
>>> Mailing List: http://jakarta.apache.org/site/mail2.html#poi
>>> The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
>>>
>>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
>> Mailing List: http://jakarta.apache.org/site/mail2.html#poi
>> The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
> Mailing List: http://jakarta.apache.org/site/mail2.html#poi
> The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
> Mailing List: http://jakarta.apache.org/site/mail2.html#poi
> The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
>
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
RE: how to find style in existing workbook for cell position with
Posted by "Haggerty, Michael" <mi...@sap.com>.
The main question I would have in implementing Sheet.findStyle is that
if a style (an extended format record) is found in a ColumnInfoRecord, a
RowRecord, a merged region record, etc., that all cover the same cell
position, then which one takes precedence? The last one created? If
so, then how is the creation order determined?
--Michael
-----Original Message-----
From: Andrew C. Oliver [mailto:acoliver@buni.org]
Sent: Friday, April 27, 2007 10:40 PM
To: POI Users List
Subject: Re: how to find style in existing workbook for cell position
with
That said if someone wanted to implement Sheet.findStyle(row,col) that
wouldn't be silly. It would be silly to return HSSFCell however.
Andrew C. Oliver wrote:
> Guys HSSF is a file format API not a spreadsheet DOM even though
> usermodel exhibits some of those features to make things easier. It
> doesn't make sense for HSSF to map anything to BlankRecord except
> BlankRecord. Besides, you want HSSF to suck down MORE memory be
> creating MORE objects? Seriously.
>
> -Andy
>
> Levi Strope wrote:
>> I believe it can be mapped to a BlankRecord because the cell itself
>> still contains cell formatting.
>>
>>
>> -----Original Message-----
>> From: Haggerty, Michael [mailto:michael.haggerty@sap.com] Sent:
>> Friday, April 27, 2007 10:28 AM
>> To: poi-user@jakarta.apache.org
>> Subject: how to find style in existing workbook for cell position
with
>> no cell
>>
>> When a cell position (some row/column position such as 0,0) in an
>> existing workbook contains a cell, then its style can be found with
>> HSSFCell.getCellStyle(). When a cell position does not contain a
cell
>> (HSSFRow.getCell() returns null), how can I find the style that would
be
>> applied by Excel when a value is set into that cell position?
>>
>> For example, a column can be formatted to have a specified fill
color.
>> When working in Excel manually, a value typed into a cell in that
column
>> will retain that fill color. I want to use HSSF to find that
column's
>> style, create a new style based on it, and make some changes to it.
I
>> cannot simply create a new style, because the default style does not
>> represent the column formatting, and the fill color is lost.
>>
>> This question was asked by bchalla...@sqli.com on 22 Feb 2006 in this
>> mailing list, with the subject of "[Q] Excel -> HSFFCell, loosing
style"
>> but no response was made.
>>
>> Possible lead: Will the org.apache.poi.hssf.record.BlankRecord class
>> help in this case? An extended format record can be retrieved from
>> this, but how can a cell position be mapped to a BlankRecord?
>>
>> Thank you.
>>
>> --Michael
>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
>> Mailing List: http://jakarta.apache.org/site/mail2.html#poi
>> The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
>> Mailing List: http://jakarta.apache.org/site/mail2.html#poi
>> The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
> Mailing List: http://jakarta.apache.org/site/mail2.html#poi
> The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
Re: how to find style in existing workbook for cell position with
Posted by "Andrew C. Oliver" <ac...@buni.org>.
That said if someone wanted to implement Sheet.findStyle(row,col) that
wouldn't be silly. It would be silly to return HSSFCell however.
Andrew C. Oliver wrote:
> Guys HSSF is a file format API not a spreadsheet DOM even though
> usermodel exhibits some of those features to make things easier. It
> doesn't make sense for HSSF to map anything to BlankRecord except
> BlankRecord. Besides, you want HSSF to suck down MORE memory be
> creating MORE objects? Seriously.
>
> -Andy
>
> Levi Strope wrote:
>> I believe it can be mapped to a BlankRecord because the cell itself
>> still contains cell formatting.
>>
>>
>> -----Original Message-----
>> From: Haggerty, Michael [mailto:michael.haggerty@sap.com] Sent:
>> Friday, April 27, 2007 10:28 AM
>> To: poi-user@jakarta.apache.org
>> Subject: how to find style in existing workbook for cell position with
>> no cell
>>
>> When a cell position (some row/column position such as 0,0) in an
>> existing workbook contains a cell, then its style can be found with
>> HSSFCell.getCellStyle(). When a cell position does not contain a cell
>> (HSSFRow.getCell() returns null), how can I find the style that would be
>> applied by Excel when a value is set into that cell position?
>>
>> For example, a column can be formatted to have a specified fill color.
>> When working in Excel manually, a value typed into a cell in that column
>> will retain that fill color. I want to use HSSF to find that column's
>> style, create a new style based on it, and make some changes to it. I
>> cannot simply create a new style, because the default style does not
>> represent the column formatting, and the fill color is lost.
>>
>> This question was asked by bchalla...@sqli.com on 22 Feb 2006 in this
>> mailing list, with the subject of "[Q] Excel -> HSFFCell, loosing style"
>> but no response was made.
>>
>> Possible lead: Will the org.apache.poi.hssf.record.BlankRecord class
>> help in this case? An extended format record can be retrieved from
>> this, but how can a cell position be mapped to a BlankRecord?
>>
>> Thank you.
>>
>> --Michael
>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
>> Mailing List: http://jakarta.apache.org/site/mail2.html#poi
>> The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
>> Mailing List: http://jakarta.apache.org/site/mail2.html#poi
>> The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
> Mailing List: http://jakarta.apache.org/site/mail2.html#poi
> The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
Re: how to find style in existing workbook for cell position with
no cell
Posted by "Andrew C. Oliver" <ac...@buni.org>.
Guys HSSF is a file format API not a spreadsheet DOM even though
usermodel exhibits some of those features to make things easier. It
doesn't make sense for HSSF to map anything to BlankRecord except
BlankRecord. Besides, you want HSSF to suck down MORE memory be
creating MORE objects? Seriously.
-Andy
Levi Strope wrote:
> I believe it can be mapped to a BlankRecord because the cell itself
> still contains cell formatting.
>
>
> -----Original Message-----
> From: Haggerty, Michael [mailto:michael.haggerty@sap.com]
> Sent: Friday, April 27, 2007 10:28 AM
> To: poi-user@jakarta.apache.org
> Subject: how to find style in existing workbook for cell position with
> no cell
>
> When a cell position (some row/column position such as 0,0) in an
> existing workbook contains a cell, then its style can be found with
> HSSFCell.getCellStyle(). When a cell position does not contain a cell
> (HSSFRow.getCell() returns null), how can I find the style that would be
> applied by Excel when a value is set into that cell position?
>
> For example, a column can be formatted to have a specified fill color.
> When working in Excel manually, a value typed into a cell in that column
> will retain that fill color. I want to use HSSF to find that column's
> style, create a new style based on it, and make some changes to it. I
> cannot simply create a new style, because the default style does not
> represent the column formatting, and the fill color is lost.
>
> This question was asked by bchalla...@sqli.com on 22 Feb 2006 in this
> mailing list, with the subject of "[Q] Excel -> HSFFCell, loosing style"
> but no response was made.
>
> Possible lead: Will the org.apache.poi.hssf.record.BlankRecord class
> help in this case? An extended format record can be retrieved from
> this, but how can a cell position be mapped to a BlankRecord?
>
> Thank you.
>
> --Michael
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
> Mailing List: http://jakarta.apache.org/site/mail2.html#poi
> The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
> Mailing List: http://jakarta.apache.org/site/mail2.html#poi
> The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
>
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
RE: how to find style in existing workbook for cell position with no cell
Posted by "Haggerty, Michael" <mi...@sap.com>.
Thanks, Levi. After I sent the original message, I realized that
BlankRecord probably wouldn't help, because it is set in a cell, and in
my case, I don't have a cell (getCell() returns null).
I am on the trail of using ColumnInfoRecord, ColumnInfoRecordsAggregate,
RowRecord, and others that contain an index to an ExtendedFormatRecord,
which seems to compose the essential ingredient of HSSFCellStyle.
Finding and accessing these objects is going to be a fair amount of
work, however. Has anyone followed this trail before?
--Michael
-----Original Message-----
From: Levi Strope [mailto:lstrope@besttransport.com]
Sent: Friday, April 27, 2007 11:44 AM
To: POI Users List
Subject: RE: how to find style in existing workbook for cell position
with no cell
I believe it can be mapped to a BlankRecord because the cell itself
still contains cell formatting.
-----Original Message-----
From: Haggerty, Michael [mailto:michael.haggerty@sap.com]
Sent: Friday, April 27, 2007 10:28 AM
To: poi-user@jakarta.apache.org
Subject: how to find style in existing workbook for cell position with
no cell
When a cell position (some row/column position such as 0,0) in an
existing workbook contains a cell, then its style can be found with
HSSFCell.getCellStyle(). When a cell position does not contain a cell
(HSSFRow.getCell() returns null), how can I find the style that would be
applied by Excel when a value is set into that cell position?
For example, a column can be formatted to have a specified fill color.
When working in Excel manually, a value typed into a cell in that column
will retain that fill color. I want to use HSSF to find that column's
style, create a new style based on it, and make some changes to it. I
cannot simply create a new style, because the default style does not
represent the column formatting, and the fill color is lost.
This question was asked by bchalla...@sqli.com on 22 Feb 2006 in this
mailing list, with the subject of "[Q] Excel -> HSFFCell, loosing style"
but no response was made.
Possible lead: Will the org.apache.poi.hssf.record.BlankRecord class
help in this case? An extended format record can be retrieved from
this, but how can a cell position be mapped to a BlankRecord?
Thank you.
--Michael
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
RE: how to find style in existing workbook for cell position with no cell
Posted by Levi Strope <ls...@besttransport.com>.
I believe it can be mapped to a BlankRecord because the cell itself
still contains cell formatting.
-----Original Message-----
From: Haggerty, Michael [mailto:michael.haggerty@sap.com]
Sent: Friday, April 27, 2007 10:28 AM
To: poi-user@jakarta.apache.org
Subject: how to find style in existing workbook for cell position with
no cell
When a cell position (some row/column position such as 0,0) in an
existing workbook contains a cell, then its style can be found with
HSSFCell.getCellStyle(). When a cell position does not contain a cell
(HSSFRow.getCell() returns null), how can I find the style that would be
applied by Excel when a value is set into that cell position?
For example, a column can be formatted to have a specified fill color.
When working in Excel manually, a value typed into a cell in that column
will retain that fill color. I want to use HSSF to find that column's
style, create a new style based on it, and make some changes to it. I
cannot simply create a new style, because the default style does not
represent the column formatting, and the fill color is lost.
This question was asked by bchalla...@sqli.com on 22 Feb 2006 in this
mailing list, with the subject of "[Q] Excel -> HSFFCell, loosing style"
but no response was made.
Possible lead: Will the org.apache.poi.hssf.record.BlankRecord class
help in this case? An extended format record can be retrieved from
this, but how can a cell position be mapped to a BlankRecord?
Thank you.
--Michael
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/