There is my example. To work with CTRow I had to change it to XSSF only. If
you want to change something fell free to do it. I tested the code and it
works 100%.
Cristian
/* ====================================================================
Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements. See the NOTICE file distributed with
this work for additional information regarding copyright ownership.
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
==================================================================== */
package org.apache.poi.xssf.usermodel.examples;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTRowImpl;
/**
* Shows how various alignment options work.
*
* Modified by Cristian Petrula, Romania on May 26, 2010
* New method was added centerAcrossSelection to center a column content
over one selection using ALIGN_CENTER_SELECTION
* To create this method example was change for XSSF only
*/
public class AligningCells {
public static void main(String[] args) throws IOException {
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet();
XSSFRow row = sheet.createRow((short) 2);
row.setHeightInPoints(30);
for (int i = 0; i < 8; i++)
{
//column width is set in units of 1/256th of a character width
sheet.setColumnWidth(i, 256*15);
}
createCell(wb, row, (short) 0, XSSFCellStyle.ALIGN_CENTER,
XSSFCellStyle.VERTICAL_BOTTOM);
createCell(wb, row, (short) 1, XSSFCellStyle.ALIGN_CENTER_SELECTION,
XSSFCellStyle.VERTICAL_BOTTOM);
createCell(wb, row, (short) 2, XSSFCellStyle.ALIGN_FILL,
XSSFCellStyle.VERTICAL_CENTER);
createCell(wb, row, (short) 3, XSSFCellStyle.ALIGN_GENERAL,
XSSFCellStyle.VERTICAL_CENTER);
createCell(wb, row, (short) 4, XSSFCellStyle.ALIGN_JUSTIFY,
XSSFCellStyle.VERTICAL_JUSTIFY);
createCell(wb, row, (short) 5, XSSFCellStyle.ALIGN_LEFT,
XSSFCellStyle.VERTICAL_TOP);
createCell(wb, row, (short) 6, XSSFCellStyle.ALIGN_RIGHT,
XSSFCellStyle.VERTICAL_TOP);
//center text over B4, C4, D4
row = sheet.createRow((short) 3);
centerAcrossSelection(wb, row, (short)1, (short)3,
XSSFCellStyle.VERTICAL_CENTER);
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("xssf-align.xlsx");
wb.write(fileOut);
fileOut.close();
}
/**
* Creates a cell and aligns it a certain way.
*
* @param wb the workbook
* @param row the row to create the cell in
* @param column the column number to create the cell in
* @param halign the horizontal alignment for the cell.
*/
private static void createCell( XSSFWorkbook wb, XSSFRow row, short
column, short halign, short valign) {
XSSFCell cell = row.createCell(column);
cell.setCellValue(new XSSFRichTextString("Align It"));
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(halign);
cellStyle.setVerticalAlignment(valign);
cell.setCellStyle(cellStyle);
}
/**
* Center a text over multiple columns using ALIGN_CENTER_SELECTION
* @param wb the workbook
* @param row the row to create the cell in
* @param start_column the column number to create the cell in and
where the selection starts
* @param end_column the column number where the selection ends
* @param valign the horizontal alignment for the cell.
*
* @author Cristian Petrula, Romania
*/
private static void centerAcrossSelection( XSSFWorkbook wb, XSSFRow row,
short start_column, short end_column, short valign)
{
//create cell style with ALIGN_CENTER_SELECTION
XSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment( XSSFCellStyle.ALIGN_CENTER_SELECTION);
cellStyle.setVerticalAlignment( valign);
//create cells over the selected area
for( int i = start_column; i <= end_column; i++)
{
XSSFCell cell = row.createCell( i);
cell.setCellStyle( cellStyle);
}
//set value to the first cell
XSSFCell cell = row.getCell( start_column);
cell.setCellValue( new XSSFRichTextString("Align It"));
//make the selection
CTRowImpl ctRow = (CTRowImpl) row.getCTRow();
List spanList = new ArrayList();
//add object with format start_coll:end_coll. For example 1:3 will span
from cell 1 to cell 3, where the column index starts with 0
//you can add multiple spans for one row
Object span = start_column + ":" + end_column;
spanList.add( span);
//add spns to the row
ctRow.setSpans( spanList);
}
}
MSB wrote:
>
> Thanks Christian.
>
> There already is some code called AligningCells in the examples section
> and I think that your work could best be added to that class which I have
> simply copied below;
>
> /* ====================================================================
> Licensed to the Apache Software Foundation (ASF) under one or more
> contributor license agreements. See the NOTICE file distributed with
> this work for additional information regarding copyright ownership.
> The ASF licenses this file to You under the Apache License, Version 2.0
> (the "License"); you may not use this file except in compliance with
> the License. You may obtain a copy of the License at
>
> http://www.apache.org/licenses/LICENSE-2.0
>
> Unless required by applicable law or agreed to in writing, software
> distributed under the License is distributed on an "AS IS" BASIS,
> WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or
> implied.
> See the License for the specific language governing permissions and
> limitations under the License.
> ==================================================================== */
>
> package org.apache.poi.xssf.usermodel.examples;
>
> import java.io.FileOutputStream;
> import java.io.IOException;
>
> import org.apache.poi.ss.usermodel.*;
> import org.apache.poi.xssf.usermodel.*;
>
> /**
> * Shows how various alignment options work.
> */
> public class AligningCells {
>
> public static void main(String[] args) throws IOException {
> Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
>
> Sheet sheet = wb.createSheet();
> Row row = sheet.createRow((short) 2);
> row.setHeightInPoints(30);
> for (int i = 0; i < 8; i++) {
> //column width is set in units of 1/256th of a character width
> sheet.setColumnWidth(i, 256*15);
> }
>
> createCell(wb, row, (short) 0, XSSFCellStyle.ALIGN_CENTER,
> XSSFCellStyle.VERTICAL_BOTTOM);
> createCell(wb, row, (short) 1,
> XSSFCellStyle.ALIGN_CENTER_SELECTION, XSSFCellStyle.VERTICAL_BOTTOM);
> createCell(wb, row, (short) 2, XSSFCellStyle.ALIGN_FILL,
> XSSFCellStyle.VERTICAL_CENTER);
> createCell(wb, row, (short) 3, XSSFCellStyle.ALIGN_GENERAL,
> XSSFCellStyle.VERTICAL_CENTER);
> createCell(wb, row, (short) 4, XSSFCellStyle.ALIGN_JUSTIFY,
> XSSFCellStyle.VERTICAL_JUSTIFY);
> createCell(wb, row, (short) 5, XSSFCellStyle.ALIGN_LEFT,
> XSSFCellStyle.VERTICAL_TOP);
> createCell(wb, row, (short) 6, XSSFCellStyle.ALIGN_RIGHT,
> XSSFCellStyle.VERTICAL_TOP);
>
> // Write the output to a file
> FileOutputStream fileOut = new
> FileOutputStream("xssf-align.xlsx");
> wb.write(fileOut);
> fileOut.close();
> }
>
> /**
> * Creates a cell and aligns it a certain way.
> *
> * @param wb the workbook
> * @param row the row to create the cell in
> * @param column the column number to create the cell in
> * @param halign the horizontal alignment for the cell.
> */
> private static void createCell(Workbook wb, Row row, short column,
> short halign, short valign) {
> Cell cell = row.createCell(column);
> cell.setCellValue(new XSSFRichTextString("Align It"));
> CellStyle cellStyle = wb.createCellStyle();
> cellStyle.setAlignment(halign);
> cellStyle.setVerticalAlignment(valign);
> cell.setCellStyle(cellStyle);
> }
> }
>
> I think that if you could modify this by adding a new method - called
> something like centerAcrossSelection() that is similar to the existing
> createCell() method in that it accepts a workbook, row, and the the from
> and to column numbers, then you couls place in there the code to create
> the cells, insert some content and ensure that it is centered across the
> specified cells.
>
> If you want to make the changes to the example above and then simply copy
> it into another message as I have done, then I will commit it to the
> examples section of the website for you. There are a couple of things you
> need to take note of though. The header in the example makes the code
> public property and anyone will be able to use it so you need to make sure
> that you are free to post the code here - check with your managers to make
> sure they have no objections for example. Secondly, make sure to add your
> name to the comments block that currently says 'Shows how various
> alignment options work.' I do not know who wrote this example originally
> but I would suggest you add something to say that you wrote the new
> method, that seems only fair.
>
> Once you are happy with it, and once you know you are free to make this
> contribution, simply copy and paste the code into a message like this one
> and I will insert it into the examples section, make sure it works with
> the other code and then commit the change.
>
> Yours
>
> Mark B
>
>
> MerlinCristi wrote:
>>
>> It will be nice to have this example. For me it was a special request
>> from the client and I had to find solutions. Tell me where to post the
>> code with comments and I will make time for this.
>>
>> Cristian
>>
>>
>> MSB wrote:
>>>
>>> Thank you for this, really good piece of work and I was wondering if you
>>> would be willing to post it as an example on the POI website so that
>>> others can see how to perform this operation? If you do not have the
>>> time, I would be quite willing to work it up into an example and post
>>> the code here before submitting it to the website; and it goes without
>>> saying that you would be credited as the author.
>>>
>>> Yours
>>>
>>> Mark B
>>>
>>>
>>> MerlinCristi wrote:
>>>>
>>>> Update
>>>>
>>>> After some time I found the solution. And it is like this
>>>>
>>>> XSSFWorkbook wb = new XSSFWorkbook();
>>>> XSSFSheet s = wb.createSheet("Sheet1");
>>>> XSSFRow r;
>>>> XSSFCell c;
>>>> CTRowImpl ctr;
>>>> r = s.createRow( 0);
>>>> c = r.createCell( 0);
>>>> c.setCellStyle( timeblockLabel);
>>>> c.setCellValue( "Some Text");
>>>>
>>>> c = r.createCell( 1);
>>>> c.setCellStyle( timeblockLabel);
>>>>
>>>> c = r.createCell( 2);
>>>> c.setCellStyle( timeblockLabel);
>>>>
>>>> ctr = (CTRowImpl) r.getCTRow();
>>>> List spans = new ArrayList();
>>>> Object o = "1:3";
>>>> spans.add( o);
>>>> ctr.setSpans( spans);
>>>>
>>>> String filename = "workbook1.xlsx";
>>>> FileOutputStream out = new FileOutputStream(filename);
>>>> wb.write(out);
>>>> out.close();
>>>>
>>>> and
>>>>
>>>> timeblockLabel.setAlignment( XSSFCellStyle.ALIGN_CENTER_SELECTION);
>>>>
>>>> I tested and it works fine. The label is centered over selection.
>>>> If you decide to span something over cell 1,2,3 for example cell 1, 2,
>>>> 3 must exist.
>>>>
>>>> Enjoy,
>>>> Cristian
>>>>
>>>>
>>>> MerlinCristi wrote:
>>>>>
>>>>> Hi. I made one xlsx file with some cells selected and saved. After
>>>>> that I loded with poi and
>>>>> if you call this code
>>>>>
>>>>> InputStream myxls = new FileInputStream("ex1.xlsx");
>>>>> XSSFWorkbook wb = new XSSFWorkbook(myxls);
>>>>> XSSFSheet s = wb.getSheetAt( 0);
>>>>> XSSFRow r = s.getRow(0);
>>>>> CTRowImpl cr = (CTRowImpl) r.getCTRow();
>>>>>
>>>>> the content of cr is
>>>>>
>>>>> <xml-fragment r="1" spans="1:3" x14ac:dyDescent="0.25"
>>>>> xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships";
>>>>> xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006";
>>>>> xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac";
>>>>> xmlns:main="http://schemas.openxmlformats.org/spreadsheetml/2006/main";>
>>>>> <main:c r="A1" s="1" t="s">
>>>>> <main:v>0</main:v>
>>>>> </main:c>
>>>>> <main:c r="B1" s="1"/>
>>>>> <main:c r="C1" s="1"/>
>>>>> </xml-fragment>
>>>>>
>>>>> You can see in the first row attribute spans="1:3"
>>>>>
>>>>> Also if you call cr.getSpans() you will get the list of spans, in my
>>>>> case 1:3. But I am unable to edit this list. There is a method
>>>>> setSpans and it requires a parameter of type XMLSimpleList but I am
>>>>> unable to add values to this list.
>>>>>
>>>>> Probably it is possible to somehow edit this values from java but it
>>>>> seams to be rocket science.
>>>>>
>>>>> I am waiting for some replay regarding this issue.
>>>>>
>>>>> Cristian
>>>>>
>>>>> MSB wrote:
>>>>>>
>>>>>> Well, there is an option to center - no, pre Webster so it ought to
>>>>>> be centre - the cells contents across a selection in the CellStyle
>>>>>> class just as there is in Excel, but like you, I cannot see how to
>>>>>> obviously make use of it. Currently, I am thinking about trying to
>>>>>> use the ability to create a name to see if that will help but I
>>>>>> cannot be sure.
>>>>>>
>>>>>> Do you know in advance which cells need to be treated in this way? If
>>>>>> so, you could create a template that POI would then be able to open
>>>>>> and populate with data. Secondly, if you do know which cells are to
>>>>>> be treated in this way, you could create styles on another sheet in
>>>>>> the workbook and then try applying them to other cells in another
>>>>>> sheet of the same workbook. Again, I do not know if this would work
>>>>>> but it should be quick enough and easy enough to try out.
>>>>>>
>>>>>> When I get back homw after work today, I will have a play to see what
>>>>>> I can come up with.
>>>>>>
>>>>>> Yours
>>>>>>
>>>>>> Mark B
>>>>>>
>>>>>>
>>>>>> Cristian Petrula wrote:
>>>>>>>
>>>>>>> Hi. I have a huge problem. I need to use ALIGN_CENTER_SELECTION over
>>>>>>> 2, 3..
>>>>>>> cells but I am not allowed to merge cells And I don't have any idea
>>>>>>> how to
>>>>>>> simulate cell selection. I saw in xml file attribute "spans" in row
>>>>>>> element.
>>>>>>> Do you know how to use this align style with selected cells?
>>>>>>>
>>>>>>> Cristian
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
--
View this message in context:
http://old.nabble.com/ALIGN_CENTER_SELECTION-question-tp28660795p28678108.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscr...@poi.apache.org
For additional commands, e-mail: user-h...@poi.apache.org
