poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From MSB <markbrd...@tiscali.co.uk>
Subject Re: Reading Merged Cells
Date Wed, 31 Mar 2010 16:31:23 GMT

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 <markbrdsly@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 <markbrdsly@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 <markbrdsly@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


Mime
View raw message