You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Cristian Petrula <cp...@rbidr.com> on 2010/05/24 21:11:54 UTC
ALIGN_CENTER_SELECTION question
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
Re: ALIGN_CENTER_SELECTION question
Posted by MSB <ma...@tiscali.co.uk>.
Thanks again for this Christian, I will commit the example later today. Must
admit that as it is in the xssf.examples section, I cannot see any problem
with making the code target that codebase completely and I will re-create
the earlier version of the example and place it into the SS usermodel
examples section so that all bases are covered.
Yours
Mark B
MerlinCristi wrote:
>
> 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-tp28660795p28690098.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: ALIGN_CENTER_SELECTION question
Posted by MerlinCristi <cr...@yahoo.com>.
Thanks. I hope it will help other developers.
Cristian
--
View this message in context: http://old.nabble.com/ALIGN_CENTER_SELECTION-question-tp28660795p28691058.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: ALIGN_CENTER_SELECTION question
Posted by MSB <ma...@tiscali.co.uk>.
Hello again Christian,
Just to let you know, I have committed your example to SVN (and slightly
modified then moved the original AligningCells.java example into the SS
examples section).
Thanks again.
Yours
Mark B
MerlinCristi wrote:
>
> 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-tp28660795p28690852.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: ALIGN_CENTER_SELECTION question
Posted by MerlinCristi <cr...@yahoo.com>.
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-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: ALIGN_CENTER_SELECTION question
Posted by MSB <ma...@tiscali.co.uk>.
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-tp28660795p28677203.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: ALIGN_CENTER_SELECTION question
Posted by MerlinCristi <cr...@yahoo.com>.
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-tp28660795p28671048.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: ALIGN_CENTER_SELECTION question
Posted by MSB <ma...@tiscali.co.uk>.
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-tp28660795p28669349.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: ALIGN_CENTER_SELECTION question
Posted by MerlinCristi <cr...@yahoo.com>.
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-tp28660795p28667887.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: ALIGN_CENTER_SELECTION question
Posted by MerlinCristi <cr...@yahoo.com>.
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-tp28660795p28667389.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: ALIGN_CENTER_SELECTION question
Posted by MSB <ma...@tiscali.co.uk>.
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-tp28660795p28665054.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