You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Jan Stette <ja...@gmail.com> on 2010/03/30 14:55:33 UTC
Reading Merged Cells
Hi all,
I have some questions related to reading spreadsheets that contain merged
cells. Basically, how should I get the value from a merged region? I see
the methods in the Sheet interface, e.g. getMergedRegion(int index), but
this just returns a range, not the related data. I guessed that the data
would be in one of the cells in the region, but this didn't seem to be the
case. I created an example spreadsheet, which had a row with a merged
region across two cells on the row. When reading this back through POI, the
cells were both null, that is, getCell(num) on the Row returned null for
both cells. (I created this example spreadsheet in OpenOffice 3.1).
When reading another spreadsheet, which had been generated in Excel (unknown
version, Excel '97 format), POI claimed not to find any merged regions,
though they were definitely there in the spreadsheet, even when opening it
in OpenOffice.
Some pointers on how this should work would be greatly appreciated (I am
using POI 3.5).
Regards,
Jan
Re: Reading Merged Cells
Posted by MSB <ma...@tiscali.co.uk>.
Thanks for the explanation Michael. Just like Microsoft to keep things
simple!
Yours
Mark B
Michael Zalewski wrote:
>
> Here is how (I think) it works
>
> When you use "Center Across Selection", Excel simply sets the field for
> horizontal alignment to the special value '6'. (The field is masked 3 bits
> in a
> XF record. So if this XF record is not already created, you will make a
> new XF
> record). This 'Horizontal Alignment' value is applied to all the cells in
> the
> selection.
>
> The data to be centered is not merged or stored in any special place.
> There is
> no information kept on the selection that was active when you did the
> "Center
> Across Selection" action.
>
> To determine how the centered text should display, when excel finds
> something to
> display where the XF record has the special value of '6', it looks to the
> right
> (maybe sometimes to the left if the display order is right to left) for
> blank
> cells which also have a 'Horizontal Alignment = 6'. The text is centered
> over
> the cell with the value, and as many blank cells as have an XF with
> 'Horizontal
> Alignment = 6'.
>
> Let's say you put the string 'Hello World' in B1, then select A1:E1 and
> choose
> 'Center Across Selection'. Then the text 'Hello World' will be centered in
> B1:E1, and Excel will generate BLANK (or MULBLANK) records in A1,
> C1,D1,E1, and
> F1. Each of the cells from A1:E1 will have 'Horizontal Alignment = '6'.
> The
> extra BLANK (or piece of a MULBLANK) record at F1 has 'Horizontal
> Alignment =
> '0', which corresponds to 'General'. That cell is needed to stop the
> horizontal
> alignment across selection.
>
> [snip]
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
--
View this message in context: http://old.nabble.com/Reading-Merged-Cells-tp28082116p28104997.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: Reading Merged Cells
Posted by Michael Zalewski <za...@optonline.net>.
Here is how (I think) it works
When you use "Center Across Selection", Excel simply sets the field for
horizontal alignment to the special value '6'. (The field is masked 3 bits in a
XF record. So if this XF record is not already created, you will make a new XF
record). This 'Horizontal Alignment' value is applied to all the cells in the
selection.
The data to be centered is not merged or stored in any special place. There is
no information kept on the selection that was active when you did the "Center
Across Selection" action.
To determine how the centered text should display, when excel finds something to
display where the XF record has the special value of '6', it looks to the right
(maybe sometimes to the left if the display order is right to left) for blank
cells which also have a 'Horizontal Alignment = 6'. The text is centered over
the cell with the value, and as many blank cells as have an XF with 'Horizontal
Alignment = 6'.
Let's say you put the string 'Hello World' in B1, then select A1:E1 and choose
'Center Across Selection'. Then the text 'Hello World' will be centered in
B1:E1, and Excel will generate BLANK (or MULBLANK) records in A1, C1,D1,E1, and
F1. Each of the cells from A1:E1 will have 'Horizontal Alignment = '6'. The
extra BLANK (or piece of a MULBLANK) record at F1 has 'Horizontal Alignment =
'0', which corresponds to 'General'. That cell is needed to stop the horizontal
alignment across selection.
[snip]
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Reading Merged Cells
Posted by MSB <ma...@tiscali.co.uk>.
Hello Jan,
Well, I thought I was fairly sure but I have been proven to be incorrect in
my assumption. When faced with something like this, the first thing I
usually do is to create a very simple Excel file and save it using the ner
xml based file format. That way, I can simply unzip the archive - as that is
all the xlsx file really is - and look at the contents of the individual xml
files using a simple text editor. Doing that, I found when I opened the xml
for the worksheet, that the row element has a spans attribute that denotes
the columns the value entered into a specific cell should span. It looks
something like this;
<row r="1" spans="1:6" ht="18.75">
Couple that with the style applied to the cell that says to centre across
the columns the cell spans and that is how the centre across selection is
achieved, at least within the xml files. It seems reasonable to assume that
there should be something similar in the binary file format as well but I
cannot say where such a setting would be stored without digging into the
binary file specification document; though I would bet it is associated with
either the row, cell or cell style.
What I do feel confident in saying that I have not be able to find where the
API exposes this value in the HSSFRow, HSSFCell or HSSFCellStyle class - or
their companion XSSFXXXX classes - yet. Unless someone else knows otherwise,
it may be the case that you will either have to request this as an
enhancement or join in the development yourself and contribute a patch for
the API. Take a look at this page - http://poi.apache.org/guidelines.html -
it gives you quite a lot of information about how to contribute to the
development of POI; patches and enhancements are always very welcome.
Sorry I cannot help any further currently but I will continue to attempt to
locate where, in the binary file format, this spans attribute is held.
Yours
Mark B
PS Have you tried to see whether POI treats a group of cells with content
that is centred across them as a merged region? I know it sounds stupid but
it might be the case.
Jan Stette-2 wrote:
>
> Are you sure the selection isn't recorded in the file? Excel certainly
> doesn't pad the text content of the cell, that remains unchanged. Yet,
> this
> formatting is obviously retained when the spreadsheet is saved. I
> wouldn't
> believe that Excel calculates a fixed offset either, as that wouldn't
> retain
> the centering when columns are resized.
>
> From what I've seen about this feature, it was an early way to get a
> similar
> effect to merging cells (which I believe was introduced in Excel 97). In
> Excel 95 there was apparently by default a "center across selection"
> button
> in the toolbar, which was replaced by the "merge cells" button in '97.
> But,
> the "merge across selection" functionality is retained, just a bit harder
> to
> access.
>
> In any case, being able to read this formatting would be very useful, as
> it
> would enable cells "merged" in this way to be treated in a similar way to
> actually merged cells.
>
> Regards,
> Jan
>
> On 31 March 2010 07:07, MSB <ma...@tiscali.co.uk> wrote:
>
>>
>> Not that I know of because once the operation has been performed the
>> selection the user made will not be recorded in the file. If the
>> selection
>> is not in the file, then it will not be available when you parse the file
>> with POI. I cannot say this with any certainty however as I have never
>> had
>> to work this out before.
>>
>> The only other thing I could think of is to find out how Excel ensures
>> the
>> text is centered when the user selects cells in this manner. Does it
>> simply
>> pad the string with leading spaces to ensure that it is centered or does
>> it
>> 'move' the text the user enters into another cell - say the one in the
>> middle of the selection - and then align it within that cell? If we could
>> find the answer to this question, then it may be possible to work
>> backwards.
>> For example, if Excel simply adds spaces to the start of the text to pad
>> it
>> out, it may be possible to calculate the total length of the text and
>> from
>> that work out how many columns and/or rows the selection originally
>> spanned.
>> That is the most obvious approach to my mind but it does depend upon how
>> Excel stores the information. If I have the time tonight, I will create a
>> test file and have a play with some code but I cannot make any promises.
>>
>> Yours
>>
>> Mark B
>>
>>
>> Jan Stette-2 wrote:
>> >
>> > Actually, another question related to merged cells: some spreadsheets
>> use
>> > "center across selection" alignment as a way to create cells that look
>> > like
>> > they've been merged. I see that POI's CellStyle interface includes the
>> > ALIGN_CENTER_SELECTION flag as one of the aligment options. But, is it
>> > possible to get the range over which a cell's content is centered?
>> >
>> > Regards,
>> > Jan
>> >
>> >
>> > On 30 March 2010 17:45, MSB <ma...@tiscali.co.uk> wrote:
>> >
>> >>
>> >> Not too worry Jan, any other questions just drop a message onto the
>> list.
>> >>
>> >> Yours
>> >>
>> >> Mark B
>> >>
>> >>
>> >> Jan Stette-2 wrote:
>> >> >
>> >> > Thanks for the info. I tried to reproduce this in a simple unit
>> test
>> >> but
>> >> > it
>> >> > seems to work as you described it. So it must have been some other
>> >> > problem
>> >> > in the code I was looking at before... sorry about the
>> inconvenience.
>> >> >
>> >> > Jan
>> >> >
>> >> >
>> >> > On 30 March 2010 15:43, MSB <ma...@tiscali.co.uk> wrote:
>> >> >
>> >> >>
>> >> >> The data should be contained within the cell at the top left hand
>> >> corner
>> >> >> of
>> >> >> the merged range. That is to say, if the merged range includes
>> cells
>> >> A1,
>> >> >> B1,
>> >> >> C1, A2, B2 and C2, that the data ought to be found by reading the
>> >> >> contents
>> >> >> of cell A1.
>> >> >>
>> >> >> I will try to put together some code later today - I am not at the
>> >> >> correct
>> >> >> machine just now - to double check my hypothesis but it is correct
>> >> IME.
>> >> >>
>> >> >> Yours
>> >> >>
>> >> >> Mark B
>> >> >>
>> >> >>
>> >> >> Jan Stette-2 wrote:
>> >> >> >
>> >> >> > Hi all,
>> >> >> >
>> >> >> > I have some questions related to reading spreadsheets that
>> contain
>> >> >> merged
>> >> >> > cells. Basically, how should I get the value from a merged
>> region?
>> >> I
>> >> >> see
>> >> >> > the methods in the Sheet interface, e.g. getMergedRegion(int
>> index),
>> >> >> but
>> >> >> > this just returns a range, not the related data. I guessed that
>> the
>> >> >> data
>> >> >> > would be in one of the cells in the region, but this didn't seem
>> to
>> >> be
>> >> >> the
>> >> >> > case. I created an example spreadsheet, which had a row with a
>> >> merged
>> >> >> > region across two cells on the row. When reading this back
>> through
>> >> >> POI,
>> >> >> > the
>> >> >> > cells were both null, that is, getCell(num) on the Row returned
>> null
>> >> >> for
>> >> >> > both cells. (I created this example spreadsheet in OpenOffice
>> 3.1).
>> >> >> >
>> >> >> > When reading another spreadsheet, which had been generated in
>> Excel
>> >> >> > (unknown
>> >> >> > version, Excel '97 format), POI claimed not to find any merged
>> >> regions,
>> >> >> > though they were definitely there in the spreadsheet, even when
>> >> opening
>> >> >> it
>> >> >> > in OpenOffice.
>> >> >> >
>> >> >> > Some pointers on how this should work would be greatly
>> appreciated
>> >> (I
>> >> >> am
>> >> >> > using POI 3.5).
>> >> >> >
>> >> >> > Regards,
>> >> >> > Jan
>> >> >> >
>> >> >> >
>> >> >>
>> >> >> --
>> >> >> View this message in context:
>> >> >> http://old.nabble.com/Reading-Merged-Cells-tp28082116p28083606.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
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >> --
>> >> View this message in context:
>> >> http://old.nabble.com/Reading-Merged-Cells-tp28082116p28085269.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
>> >>
>> >>
>> >
>> >
>>
>> --
>> View this message in context:
>> http://old.nabble.com/Reading-Merged-Cells-tp28082116p28091766.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
>>
>>
>
>
--
View this message in context: http://old.nabble.com/Reading-Merged-Cells-tp28082116p28098426.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: Reading Merged Cells
Posted by Jan Stette <ja...@gmail.com>.
Are you sure the selection isn't recorded in the file? Excel certainly
doesn't pad the text content of the cell, that remains unchanged. Yet, this
formatting is obviously retained when the spreadsheet is saved. I wouldn't
believe that Excel calculates a fixed offset either, as that wouldn't retain
the centering when columns are resized.
>From what I've seen about this feature, it was an early way to get a similar
effect to merging cells (which I believe was introduced in Excel 97). In
Excel 95 there was apparently by default a "center across selection" button
in the toolbar, which was replaced by the "merge cells" button in '97. But,
the "merge across selection" functionality is retained, just a bit harder to
access.
In any case, being able to read this formatting would be very useful, as it
would enable cells "merged" in this way to be treated in a similar way to
actually merged cells.
Regards,
Jan
On 31 March 2010 07:07, MSB <ma...@tiscali.co.uk> wrote:
>
> Not that I know of because once the operation has been performed the
> selection the user made will not be recorded in the file. If the selection
> is not in the file, then it will not be available when you parse the file
> with POI. I cannot say this with any certainty however as I have never had
> to work this out before.
>
> The only other thing I could think of is to find out how Excel ensures the
> text is centered when the user selects cells in this manner. Does it simply
> pad the string with leading spaces to ensure that it is centered or does it
> 'move' the text the user enters into another cell - say the one in the
> middle of the selection - and then align it within that cell? If we could
> find the answer to this question, then it may be possible to work
> backwards.
> For example, if Excel simply adds spaces to the start of the text to pad it
> out, it may be possible to calculate the total length of the text and from
> that work out how many columns and/or rows the selection originally
> spanned.
> That is the most obvious approach to my mind but it does depend upon how
> Excel stores the information. If I have the time tonight, I will create a
> test file and have a play with some code but I cannot make any promises.
>
> Yours
>
> Mark B
>
>
> Jan Stette-2 wrote:
> >
> > Actually, another question related to merged cells: some spreadsheets use
> > "center across selection" alignment as a way to create cells that look
> > like
> > they've been merged. I see that POI's CellStyle interface includes the
> > ALIGN_CENTER_SELECTION flag as one of the aligment options. But, is it
> > possible to get the range over which a cell's content is centered?
> >
> > Regards,
> > Jan
> >
> >
> > On 30 March 2010 17:45, MSB <ma...@tiscali.co.uk> wrote:
> >
> >>
> >> Not too worry Jan, any other questions just drop a message onto the
> list.
> >>
> >> Yours
> >>
> >> Mark B
> >>
> >>
> >> Jan Stette-2 wrote:
> >> >
> >> > Thanks for the info. I tried to reproduce this in a simple unit test
> >> but
> >> > it
> >> > seems to work as you described it. So it must have been some other
> >> > problem
> >> > in the code I was looking at before... sorry about the inconvenience.
> >> >
> >> > Jan
> >> >
> >> >
> >> > On 30 March 2010 15:43, MSB <ma...@tiscali.co.uk> wrote:
> >> >
> >> >>
> >> >> The data should be contained within the cell at the top left hand
> >> corner
> >> >> of
> >> >> the merged range. That is to say, if the merged range includes cells
> >> A1,
> >> >> B1,
> >> >> C1, A2, B2 and C2, that the data ought to be found by reading the
> >> >> contents
> >> >> of cell A1.
> >> >>
> >> >> I will try to put together some code later today - I am not at the
> >> >> correct
> >> >> machine just now - to double check my hypothesis but it is correct
> >> IME.
> >> >>
> >> >> Yours
> >> >>
> >> >> Mark B
> >> >>
> >> >>
> >> >> Jan Stette-2 wrote:
> >> >> >
> >> >> > Hi all,
> >> >> >
> >> >> > I have some questions related to reading spreadsheets that contain
> >> >> merged
> >> >> > cells. Basically, how should I get the value from a merged region?
> >> I
> >> >> see
> >> >> > the methods in the Sheet interface, e.g. getMergedRegion(int
> index),
> >> >> but
> >> >> > this just returns a range, not the related data. I guessed that
> the
> >> >> data
> >> >> > would be in one of the cells in the region, but this didn't seem to
> >> be
> >> >> the
> >> >> > case. I created an example spreadsheet, which had a row with a
> >> merged
> >> >> > region across two cells on the row. When reading this back through
> >> >> POI,
> >> >> > the
> >> >> > cells were both null, that is, getCell(num) on the Row returned
> null
> >> >> for
> >> >> > both cells. (I created this example spreadsheet in OpenOffice
> 3.1).
> >> >> >
> >> >> > When reading another spreadsheet, which had been generated in Excel
> >> >> > (unknown
> >> >> > version, Excel '97 format), POI claimed not to find any merged
> >> regions,
> >> >> > though they were definitely there in the spreadsheet, even when
> >> opening
> >> >> it
> >> >> > in OpenOffice.
> >> >> >
> >> >> > Some pointers on how this should work would be greatly appreciated
> >> (I
> >> >> am
> >> >> > using POI 3.5).
> >> >> >
> >> >> > Regards,
> >> >> > Jan
> >> >> >
> >> >> >
> >> >>
> >> >> --
> >> >> View this message in context:
> >> >> http://old.nabble.com/Reading-Merged-Cells-tp28082116p28083606.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
> >> >>
> >> >>
> >> >
> >> >
> >>
> >> --
> >> View this message in context:
> >> http://old.nabble.com/Reading-Merged-Cells-tp28082116p28085269.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
> >>
> >>
> >
> >
>
> --
> View this message in context:
> http://old.nabble.com/Reading-Merged-Cells-tp28082116p28091766.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: Reading Merged Cells
Posted by MSB <ma...@tiscali.co.uk>.
Not that I know of because once the operation has been performed the
selection the user made will not be recorded in the file. If the selection
is not in the file, then it will not be available when you parse the file
with POI. I cannot say this with any certainty however as I have never had
to work this out before.
The only other thing I could think of is to find out how Excel ensures the
text is centered when the user selects cells in this manner. Does it simply
pad the string with leading spaces to ensure that it is centered or does it
'move' the text the user enters into another cell - say the one in the
middle of the selection - and then align it within that cell? If we could
find the answer to this question, then it may be possible to work backwards.
For example, if Excel simply adds spaces to the start of the text to pad it
out, it may be possible to calculate the total length of the text and from
that work out how many columns and/or rows the selection originally spanned.
That is the most obvious approach to my mind but it does depend upon how
Excel stores the information. If I have the time tonight, I will create a
test file and have a play with some code but I cannot make any promises.
Yours
Mark B
Jan Stette-2 wrote:
>
> Actually, another question related to merged cells: some spreadsheets use
> "center across selection" alignment as a way to create cells that look
> like
> they've been merged. I see that POI's CellStyle interface includes the
> ALIGN_CENTER_SELECTION flag as one of the aligment options. But, is it
> possible to get the range over which a cell's content is centered?
>
> Regards,
> Jan
>
>
> On 30 March 2010 17:45, MSB <ma...@tiscali.co.uk> wrote:
>
>>
>> Not too worry Jan, any other questions just drop a message onto the list.
>>
>> Yours
>>
>> Mark B
>>
>>
>> Jan Stette-2 wrote:
>> >
>> > Thanks for the info. I tried to reproduce this in a simple unit test
>> but
>> > it
>> > seems to work as you described it. So it must have been some other
>> > problem
>> > in the code I was looking at before... sorry about the inconvenience.
>> >
>> > Jan
>> >
>> >
>> > On 30 March 2010 15:43, MSB <ma...@tiscali.co.uk> wrote:
>> >
>> >>
>> >> The data should be contained within the cell at the top left hand
>> corner
>> >> of
>> >> the merged range. That is to say, if the merged range includes cells
>> A1,
>> >> B1,
>> >> C1, A2, B2 and C2, that the data ought to be found by reading the
>> >> contents
>> >> of cell A1.
>> >>
>> >> I will try to put together some code later today - I am not at the
>> >> correct
>> >> machine just now - to double check my hypothesis but it is correct
>> IME.
>> >>
>> >> Yours
>> >>
>> >> Mark B
>> >>
>> >>
>> >> Jan Stette-2 wrote:
>> >> >
>> >> > Hi all,
>> >> >
>> >> > I have some questions related to reading spreadsheets that contain
>> >> merged
>> >> > cells. Basically, how should I get the value from a merged region?
>> I
>> >> see
>> >> > the methods in the Sheet interface, e.g. getMergedRegion(int index),
>> >> but
>> >> > this just returns a range, not the related data. I guessed that the
>> >> data
>> >> > would be in one of the cells in the region, but this didn't seem to
>> be
>> >> the
>> >> > case. I created an example spreadsheet, which had a row with a
>> merged
>> >> > region across two cells on the row. When reading this back through
>> >> POI,
>> >> > the
>> >> > cells were both null, that is, getCell(num) on the Row returned null
>> >> for
>> >> > both cells. (I created this example spreadsheet in OpenOffice 3.1).
>> >> >
>> >> > When reading another spreadsheet, which had been generated in Excel
>> >> > (unknown
>> >> > version, Excel '97 format), POI claimed not to find any merged
>> regions,
>> >> > though they were definitely there in the spreadsheet, even when
>> opening
>> >> it
>> >> > in OpenOffice.
>> >> >
>> >> > Some pointers on how this should work would be greatly appreciated
>> (I
>> >> am
>> >> > using POI 3.5).
>> >> >
>> >> > Regards,
>> >> > Jan
>> >> >
>> >> >
>> >>
>> >> --
>> >> View this message in context:
>> >> http://old.nabble.com/Reading-Merged-Cells-tp28082116p28083606.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
>> >>
>> >>
>> >
>> >
>>
>> --
>> View this message in context:
>> http://old.nabble.com/Reading-Merged-Cells-tp28082116p28085269.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
>>
>>
>
>
--
View this message in context: http://old.nabble.com/Reading-Merged-Cells-tp28082116p28091766.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: Reading Merged Cells
Posted by Jan Stette <ja...@gmail.com>.
Actually, another question related to merged cells: some spreadsheets use
"center across selection" alignment as a way to create cells that look like
they've been merged. I see that POI's CellStyle interface includes the
ALIGN_CENTER_SELECTION flag as one of the aligment options. But, is it
possible to get the range over which a cell's content is centered?
Regards,
Jan
On 30 March 2010 17:45, MSB <ma...@tiscali.co.uk> wrote:
>
> Not too worry Jan, any other questions just drop a message onto the list.
>
> Yours
>
> Mark B
>
>
> Jan Stette-2 wrote:
> >
> > Thanks for the info. I tried to reproduce this in a simple unit test but
> > it
> > seems to work as you described it. So it must have been some other
> > problem
> > in the code I was looking at before... sorry about the inconvenience.
> >
> > Jan
> >
> >
> > On 30 March 2010 15:43, MSB <ma...@tiscali.co.uk> wrote:
> >
> >>
> >> The data should be contained within the cell at the top left hand corner
> >> of
> >> the merged range. That is to say, if the merged range includes cells A1,
> >> B1,
> >> C1, A2, B2 and C2, that the data ought to be found by reading the
> >> contents
> >> of cell A1.
> >>
> >> I will try to put together some code later today - I am not at the
> >> correct
> >> machine just now - to double check my hypothesis but it is correct IME.
> >>
> >> Yours
> >>
> >> Mark B
> >>
> >>
> >> Jan Stette-2 wrote:
> >> >
> >> > Hi all,
> >> >
> >> > I have some questions related to reading spreadsheets that contain
> >> merged
> >> > cells. Basically, how should I get the value from a merged region? I
> >> see
> >> > the methods in the Sheet interface, e.g. getMergedRegion(int index),
> >> but
> >> > this just returns a range, not the related data. I guessed that the
> >> data
> >> > would be in one of the cells in the region, but this didn't seem to be
> >> the
> >> > case. I created an example spreadsheet, which had a row with a merged
> >> > region across two cells on the row. When reading this back through
> >> POI,
> >> > the
> >> > cells were both null, that is, getCell(num) on the Row returned null
> >> for
> >> > both cells. (I created this example spreadsheet in OpenOffice 3.1).
> >> >
> >> > When reading another spreadsheet, which had been generated in Excel
> >> > (unknown
> >> > version, Excel '97 format), POI claimed not to find any merged
> regions,
> >> > though they were definitely there in the spreadsheet, even when
> opening
> >> it
> >> > in OpenOffice.
> >> >
> >> > Some pointers on how this should work would be greatly appreciated (I
> >> am
> >> > using POI 3.5).
> >> >
> >> > Regards,
> >> > Jan
> >> >
> >> >
> >>
> >> --
> >> View this message in context:
> >> http://old.nabble.com/Reading-Merged-Cells-tp28082116p28083606.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
> >>
> >>
> >
> >
>
> --
> View this message in context:
> http://old.nabble.com/Reading-Merged-Cells-tp28082116p28085269.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: Reading Merged Cells
Posted by MSB <ma...@tiscali.co.uk>.
Not too worry Jan, any other questions just drop a message onto the list.
Yours
Mark B
Jan Stette-2 wrote:
>
> Thanks for the info. I tried to reproduce this in a simple unit test but
> it
> seems to work as you described it. So it must have been some other
> problem
> in the code I was looking at before... sorry about the inconvenience.
>
> Jan
>
>
> On 30 March 2010 15:43, MSB <ma...@tiscali.co.uk> wrote:
>
>>
>> The data should be contained within the cell at the top left hand corner
>> of
>> the merged range. That is to say, if the merged range includes cells A1,
>> B1,
>> C1, A2, B2 and C2, that the data ought to be found by reading the
>> contents
>> of cell A1.
>>
>> I will try to put together some code later today - I am not at the
>> correct
>> machine just now - to double check my hypothesis but it is correct IME.
>>
>> Yours
>>
>> Mark B
>>
>>
>> Jan Stette-2 wrote:
>> >
>> > Hi all,
>> >
>> > I have some questions related to reading spreadsheets that contain
>> merged
>> > cells. Basically, how should I get the value from a merged region? I
>> see
>> > the methods in the Sheet interface, e.g. getMergedRegion(int index),
>> but
>> > this just returns a range, not the related data. I guessed that the
>> data
>> > would be in one of the cells in the region, but this didn't seem to be
>> the
>> > case. I created an example spreadsheet, which had a row with a merged
>> > region across two cells on the row. When reading this back through
>> POI,
>> > the
>> > cells were both null, that is, getCell(num) on the Row returned null
>> for
>> > both cells. (I created this example spreadsheet in OpenOffice 3.1).
>> >
>> > When reading another spreadsheet, which had been generated in Excel
>> > (unknown
>> > version, Excel '97 format), POI claimed not to find any merged regions,
>> > though they were definitely there in the spreadsheet, even when opening
>> it
>> > in OpenOffice.
>> >
>> > Some pointers on how this should work would be greatly appreciated (I
>> am
>> > using POI 3.5).
>> >
>> > Regards,
>> > Jan
>> >
>> >
>>
>> --
>> View this message in context:
>> http://old.nabble.com/Reading-Merged-Cells-tp28082116p28083606.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
>>
>>
>
>
--
View this message in context: http://old.nabble.com/Reading-Merged-Cells-tp28082116p28085269.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: Reading Merged Cells
Posted by Jan Stette <ja...@gmail.com>.
Thanks for the info. I tried to reproduce this in a simple unit test but it
seems to work as you described it. So it must have been some other problem
in the code I was looking at before... sorry about the inconvenience.
Jan
On 30 March 2010 15:43, MSB <ma...@tiscali.co.uk> wrote:
>
> The data should be contained within the cell at the top left hand corner of
> the merged range. That is to say, if the merged range includes cells A1,
> B1,
> C1, A2, B2 and C2, that the data ought to be found by reading the contents
> of cell A1.
>
> I will try to put together some code later today - I am not at the correct
> machine just now - to double check my hypothesis but it is correct IME.
>
> Yours
>
> Mark B
>
>
> Jan Stette-2 wrote:
> >
> > Hi all,
> >
> > I have some questions related to reading spreadsheets that contain merged
> > cells. Basically, how should I get the value from a merged region? I
> see
> > the methods in the Sheet interface, e.g. getMergedRegion(int index), but
> > this just returns a range, not the related data. I guessed that the data
> > would be in one of the cells in the region, but this didn't seem to be
> the
> > case. I created an example spreadsheet, which had a row with a merged
> > region across two cells on the row. When reading this back through POI,
> > the
> > cells were both null, that is, getCell(num) on the Row returned null for
> > both cells. (I created this example spreadsheet in OpenOffice 3.1).
> >
> > When reading another spreadsheet, which had been generated in Excel
> > (unknown
> > version, Excel '97 format), POI claimed not to find any merged regions,
> > though they were definitely there in the spreadsheet, even when opening
> it
> > in OpenOffice.
> >
> > Some pointers on how this should work would be greatly appreciated (I am
> > using POI 3.5).
> >
> > Regards,
> > Jan
> >
> >
>
> --
> View this message in context:
> http://old.nabble.com/Reading-Merged-Cells-tp28082116p28083606.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: Reading Merged Cells
Posted by MSB <ma...@tiscali.co.uk>.
The data should be contained within the cell at the top left hand corner of
the merged range. That is to say, if the merged range includes cells A1, B1,
C1, A2, B2 and C2, that the data ought to be found by reading the contents
of cell A1.
I will try to put together some code later today - I am not at the correct
machine just now - to double check my hypothesis but it is correct IME.
Yours
Mark B
Jan Stette-2 wrote:
>
> Hi all,
>
> I have some questions related to reading spreadsheets that contain merged
> cells. Basically, how should I get the value from a merged region? I see
> the methods in the Sheet interface, e.g. getMergedRegion(int index), but
> this just returns a range, not the related data. I guessed that the data
> would be in one of the cells in the region, but this didn't seem to be the
> case. I created an example spreadsheet, which had a row with a merged
> region across two cells on the row. When reading this back through POI,
> the
> cells were both null, that is, getCell(num) on the Row returned null for
> both cells. (I created this example spreadsheet in OpenOffice 3.1).
>
> When reading another spreadsheet, which had been generated in Excel
> (unknown
> version, Excel '97 format), POI claimed not to find any merged regions,
> though they were definitely there in the spreadsheet, even when opening it
> in OpenOffice.
>
> Some pointers on how this should work would be greatly appreciated (I am
> using POI 3.5).
>
> Regards,
> Jan
>
>
--
View this message in context: http://old.nabble.com/Reading-Merged-Cells-tp28082116p28083606.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