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-unsubscr...@poi.apache.org For additional commands, e-mail: user-h...@poi.apache.org
