| /* ==================================================================== |
| 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.ss.util; |
| |
| import static org.junit.Assert.assertEquals; |
| import static org.junit.Assert.assertNotEquals; |
| import static org.junit.Assert.assertNotNull; |
| import static org.junit.Assert.assertSame; |
| import static org.junit.Assert.fail; |
| |
| import java.io.IOException; |
| import java.util.HashMap; |
| import java.util.Map; |
| |
| import org.apache.poi.ss.ITestDataProvider; |
| import org.apache.poi.ss.usermodel.BorderStyle; |
| import org.apache.poi.ss.usermodel.Cell; |
| import org.apache.poi.ss.usermodel.CellStyle; |
| import org.apache.poi.ss.usermodel.FillPatternType; |
| import org.apache.poi.ss.usermodel.Font; |
| import org.apache.poi.ss.usermodel.HorizontalAlignment; |
| import org.apache.poi.ss.usermodel.IndexedColors; |
| import org.apache.poi.ss.usermodel.Row; |
| import org.apache.poi.ss.usermodel.Sheet; |
| import org.apache.poi.ss.usermodel.VerticalAlignment; |
| import org.apache.poi.ss.usermodel.Workbook; |
| import org.junit.Test; |
| |
| /** |
| * Tests Spreadsheet CellUtil |
| * |
| * @see org.apache.poi.ss.util.CellUtil |
| */ |
| public class BaseTestCellUtil { |
| protected final ITestDataProvider _testDataProvider; |
| |
| protected BaseTestCellUtil(ITestDataProvider testDataProvider) { |
| _testDataProvider = testDataProvider; |
| } |
| |
| @Test |
| public void setCellStyleProperty() throws IOException { |
| Workbook wb = _testDataProvider.createWorkbook(); |
| Sheet s = wb.createSheet(); |
| Row r = s.createRow(0); |
| Cell c = r.createCell(0); |
| |
| // Add a border should create a new style |
| int styCnt1 = wb.getNumCellStyles(); |
| CellUtil.setCellStyleProperty(c, CellUtil.BORDER_BOTTOM, BorderStyle.THIN); |
| int styCnt2 = wb.getNumCellStyles(); |
| assertEquals(styCnt1+1, styCnt2); |
| |
| // Add same border to another cell, should not create another style |
| c = r.createCell(1); |
| CellUtil.setCellStyleProperty(c, CellUtil.BORDER_BOTTOM, BorderStyle.THIN); |
| int styCnt3 = wb.getNumCellStyles(); |
| assertEquals(styCnt2, styCnt3); |
| |
| wb.close(); |
| } |
| |
| @Test(expected=RuntimeException.class) |
| public void setCellStylePropertyWithInvalidValue() throws IOException { |
| Workbook wb = _testDataProvider.createWorkbook(); |
| try { |
| Sheet s = wb.createSheet(); |
| Row r = s.createRow(0); |
| Cell c = r.createCell(0); |
| |
| // An invalid BorderStyle constant |
| CellUtil.setCellStyleProperty(c, CellUtil.BORDER_BOTTOM, 42); |
| } finally { |
| wb.close(); |
| } |
| } |
| |
| @Test() |
| public void setCellStylePropertyBorderWithShortAndEnum() throws IOException { |
| Workbook wb = _testDataProvider.createWorkbook(); |
| Sheet s = wb.createSheet(); |
| Row r = s.createRow(0); |
| Cell c = r.createCell(0); |
| |
| // A valid BorderStyle constant, as a Short |
| CellUtil.setCellStyleProperty(c, CellUtil.BORDER_BOTTOM, BorderStyle.DASH_DOT.getCode()); |
| assertEquals(BorderStyle.DASH_DOT, c.getCellStyle().getBorderBottomEnum()); |
| |
| // A valid BorderStyle constant, as an Enum |
| CellUtil.setCellStyleProperty(c, CellUtil.BORDER_TOP, BorderStyle.MEDIUM_DASH_DOT); |
| assertEquals(BorderStyle.MEDIUM_DASH_DOT, c.getCellStyle().getBorderTopEnum()); |
| |
| wb.close(); |
| } |
| |
| @Test |
| public void setCellStyleProperties() throws IOException { |
| System.out.println("setCellStyleProps start"); |
| Workbook wb = _testDataProvider.createWorkbook(); |
| Sheet s = wb.createSheet(); |
| Row r = s.createRow(0); |
| Cell c = r.createCell(0); |
| |
| // Add multiple border properties to cell should create a single new style |
| int styCnt1 = wb.getNumCellStyles(); |
| Map<String, Object> props = new HashMap<String, Object>(); |
| props.put(CellUtil.BORDER_TOP, BorderStyle.THIN); |
| props.put(CellUtil.BORDER_BOTTOM, BorderStyle.THIN); |
| props.put(CellUtil.BORDER_LEFT, BorderStyle.THIN); |
| props.put(CellUtil.BORDER_RIGHT, BorderStyle.THIN); |
| props.put(CellUtil.ALIGNMENT, HorizontalAlignment.CENTER.getCode()); // try it both with a Short (deprecated) |
| props.put(CellUtil.VERTICAL_ALIGNMENT, VerticalAlignment.CENTER); // and with an enum |
| CellUtil.setCellStyleProperties(c, props); |
| int styCnt2 = wb.getNumCellStyles(); |
| assertEquals("Only one additional style should have been created", styCnt1 + 1, styCnt2); |
| |
| // Add same border another to same cell, should not create another style |
| c = r.createCell(1); |
| CellUtil.setCellStyleProperties(c, props); |
| int styCnt3 = wb.getNumCellStyles(); |
| System.out.println("setCellStyleProps nearing end"); |
| assertEquals("No additional styles should have been created", styCnt2, styCnt3); |
| |
| wb.close(); |
| |
| } |
| |
| @Test |
| public void getRow() throws IOException { |
| Workbook wb = _testDataProvider.createWorkbook(); |
| Sheet sh = wb.createSheet(); |
| Row row1 = sh.createRow(0); |
| |
| // Get row that already exists |
| Row r1 = CellUtil.getRow(0, sh); |
| assertNotNull(r1); |
| assertSame("An existing row should not be recreated", row1, r1); |
| |
| // Get row that does not exist yet |
| assertNotNull(CellUtil.getRow(1, sh)); |
| |
| wb.close(); |
| } |
| |
| @Test |
| public void getCell() throws IOException { |
| Workbook wb = _testDataProvider.createWorkbook(); |
| Sheet sh = wb.createSheet(); |
| Row row = sh.createRow(0); |
| Cell A1 = row.createCell(0); |
| |
| // Get cell that already exists |
| Cell a1 = CellUtil.getCell(row, 0); |
| assertNotNull(a1); |
| assertSame("An existing cell should not be recreated", A1, a1); |
| |
| // Get cell that does not exist yet |
| assertNotNull(CellUtil.getCell(row, 1)); |
| |
| wb.close(); |
| } |
| |
| @Test |
| public void createCell() throws IOException { |
| Workbook wb = _testDataProvider.createWorkbook(); |
| Sheet sh = wb.createSheet(); |
| Row row = sh.createRow(0); |
| |
| CellStyle style = wb.createCellStyle(); |
| style.setWrapText(true); |
| |
| // calling createCell on a non-existing cell should create a cell and set the cell value and style. |
| Cell F1 = CellUtil.createCell(row, 5, "Cell Value", style); |
| |
| assertSame(row.getCell(5), F1); |
| assertEquals("Cell Value", F1.getStringCellValue()); |
| assertEquals(style, F1.getCellStyle()); |
| // should be assertSame, but a new HSSFCellStyle is returned for each getCellStyle() call. |
| // HSSFCellStyle wraps an underlying style record, and the underlying |
| // style record is the same between multiple getCellStyle() calls. |
| |
| // calling createCell on an existing cell should return the existing cell and modify the cell value and style. |
| Cell f1 = CellUtil.createCell(row, 5, "Overwritten cell value", null); |
| assertSame(row.getCell(5), f1); |
| assertSame(F1, f1); |
| assertEquals("Overwritten cell value", f1.getStringCellValue()); |
| assertEquals("Overwritten cell value", F1.getStringCellValue()); |
| assertEquals("cell style should be unchanged with createCell(..., null)", style, f1.getCellStyle()); |
| assertEquals("cell style should be unchanged with createCell(..., null)", style, F1.getCellStyle()); |
| |
| // test createCell(row, column, value) (no CellStyle) |
| f1 = CellUtil.createCell(row, 5, "Overwritten cell with default style"); |
| assertSame(F1, f1); |
| |
| wb.close(); |
| |
| } |
| |
| /** |
| * @deprecated by {@link #setAlignmentEnum()} |
| * |
| * @throws IOException |
| * |
| */ |
| @Deprecated |
| @SuppressWarnings("deprecated") |
| @Test |
| public void setAlignment() throws IOException { |
| Workbook wb = _testDataProvider.createWorkbook(); |
| Sheet sh = wb.createSheet(); |
| Row row = sh.createRow(0); |
| Cell A1 = row.createCell(0); |
| Cell B1 = row.createCell(1); |
| |
| // Assumptions |
| assertEquals(A1.getCellStyle(), B1.getCellStyle()); |
| // should be assertSame, but a new HSSFCellStyle is returned for each getCellStyle() call. |
| // HSSFCellStyle wraps an underlying style record, and the underlying |
| // style record is the same between multiple getCellStyle() calls. |
| assertEquals(HorizontalAlignment.GENERAL, A1.getCellStyle().getAlignmentEnum()); |
| assertEquals(HorizontalAlignment.GENERAL, B1.getCellStyle().getAlignmentEnum()); |
| |
| // get/set alignment modifies the cell's style |
| CellUtil.setAlignment(A1, HorizontalAlignment.RIGHT); |
| assertEquals(HorizontalAlignment.RIGHT, A1.getCellStyle().getAlignmentEnum()); |
| |
| // get/set alignment doesn't affect the style of cells with |
| // the same style prior to modifying the style |
| assertNotEquals(A1.getCellStyle(), B1.getCellStyle()); |
| assertEquals(HorizontalAlignment.GENERAL, B1.getCellStyle().getAlignmentEnum()); |
| |
| wb.close(); |
| } |
| |
| @Test |
| public void setAlignmentEnum() throws IOException { |
| Workbook wb = _testDataProvider.createWorkbook(); |
| Sheet sh = wb.createSheet(); |
| Row row = sh.createRow(0); |
| Cell A1 = row.createCell(0); |
| Cell B1 = row.createCell(1); |
| |
| // Assumptions |
| assertEquals(A1.getCellStyle(), B1.getCellStyle()); |
| // should be assertSame, but a new HSSFCellStyle is returned for each getCellStyle() call. |
| // HSSFCellStyle wraps an underlying style record, and the underlying |
| // style record is the same between multiple getCellStyle() calls. |
| assertEquals(HorizontalAlignment.GENERAL, A1.getCellStyle().getAlignmentEnum()); |
| assertEquals(HorizontalAlignment.GENERAL, B1.getCellStyle().getAlignmentEnum()); |
| |
| // get/set alignment modifies the cell's style |
| CellUtil.setAlignment(A1, HorizontalAlignment.RIGHT); |
| assertEquals(HorizontalAlignment.RIGHT, A1.getCellStyle().getAlignmentEnum()); |
| |
| // get/set alignment doesn't affect the style of cells with |
| // the same style prior to modifying the style |
| assertNotEquals(A1.getCellStyle(), B1.getCellStyle()); |
| assertEquals(HorizontalAlignment.GENERAL, B1.getCellStyle().getAlignmentEnum()); |
| |
| wb.close(); |
| } |
| |
| @Test |
| public void setVerticalAlignmentEnum() throws IOException { |
| Workbook wb = _testDataProvider.createWorkbook(); |
| Sheet sh = wb.createSheet(); |
| Row row = sh.createRow(0); |
| Cell A1 = row.createCell(0); |
| Cell B1 = row.createCell(1); |
| |
| // Assumptions |
| assertEquals(A1.getCellStyle(), B1.getCellStyle()); |
| // should be assertSame, but a new HSSFCellStyle is returned for each getCellStyle() call. |
| // HSSFCellStyle wraps an underlying style record, and the underlying |
| // style record is the same between multiple getCellStyle() calls. |
| assertEquals(VerticalAlignment.BOTTOM, A1.getCellStyle().getVerticalAlignmentEnum()); |
| assertEquals(VerticalAlignment.BOTTOM, B1.getCellStyle().getVerticalAlignmentEnum()); |
| |
| // get/set alignment modifies the cell's style |
| CellUtil.setVerticalAlignment(A1, VerticalAlignment.TOP); |
| assertEquals(VerticalAlignment.TOP, A1.getCellStyle().getVerticalAlignmentEnum()); |
| |
| // get/set alignment doesn't affect the style of cells with |
| // the same style prior to modifying the style |
| assertNotEquals(A1.getCellStyle(), B1.getCellStyle()); |
| assertEquals(VerticalAlignment.BOTTOM, B1.getCellStyle().getVerticalAlignmentEnum()); |
| |
| wb.close(); |
| } |
| |
| @Test |
| public void setFont() throws IOException { |
| Workbook wb = _testDataProvider.createWorkbook(); |
| Sheet sh = wb.createSheet(); |
| Row row = sh.createRow(0); |
| Cell A1 = row.createCell(0); |
| Cell B1 = row.createCell(1); |
| final short defaultFontIndex = 0; |
| Font font = wb.createFont(); |
| font.setItalic(true); |
| final short customFontIndex = font.getIndex(); |
| |
| // Assumptions |
| assertNotEquals(defaultFontIndex, customFontIndex); |
| assertEquals(A1.getCellStyle(), B1.getCellStyle()); |
| // should be assertSame, but a new HSSFCellStyle is returned for each getCellStyle() call. |
| // HSSFCellStyle wraps an underlying style record, and the underlying |
| // style record is the same between multiple getCellStyle() calls. |
| assertEquals(defaultFontIndex, A1.getCellStyle().getFontIndex()); |
| assertEquals(defaultFontIndex, B1.getCellStyle().getFontIndex()); |
| |
| // get/set alignment modifies the cell's style |
| CellUtil.setFont(A1, font); |
| assertEquals(customFontIndex, A1.getCellStyle().getFontIndex()); |
| |
| // get/set alignment doesn't affect the style of cells with |
| // the same style prior to modifying the style |
| assertNotEquals(A1.getCellStyle(), B1.getCellStyle()); |
| assertEquals(defaultFontIndex, B1.getCellStyle().getFontIndex()); |
| |
| wb.close(); |
| } |
| |
| @Test |
| public void setFontFromDifferentWorkbook() throws IOException { |
| Workbook wb1 = _testDataProvider.createWorkbook(); |
| Workbook wb2 = _testDataProvider.createWorkbook(); |
| Font font1 = wb1.createFont(); |
| Font font2 = wb2.createFont(); |
| // do something to make font1 and font2 different |
| // so they are not same or equal. |
| font1.setItalic(true); |
| Cell A1 = wb1.createSheet().createRow(0).createCell(0); |
| |
| // okay |
| CellUtil.setFont(A1, font1); |
| |
| // font belongs to different workbook |
| try { |
| CellUtil.setFont(A1, font2); |
| fail("setFont not allowed if font belongs to a different workbook"); |
| } catch (final IllegalArgumentException e) { |
| // one specific message is expected |
| if (!e.getMessage().startsWith("Font does not belong to this workbook")) { |
| throw e; |
| } |
| } finally { |
| wb1.close(); |
| wb2.close(); |
| } |
| } |
| |
| /** |
| * bug 55555 |
| * @deprecated Replaced by {@link #setFillForegroundColorBeforeFillBackgroundColorEnum()} |
| * @since POI 3.15 beta 3 |
| */ |
| @Deprecated |
| // bug 55555 |
| @Test |
| public void setFillForegroundColorBeforeFillBackgroundColor() throws IOException { |
| Workbook wb1 = _testDataProvider.createWorkbook(); |
| Cell A1 = wb1.createSheet().createRow(0).createCell(0); |
| Map<String, Object> properties = new HashMap<String, Object>(); |
| properties.put(CellUtil.FILL_PATTERN, FillPatternType.BRICKS); |
| properties.put(CellUtil.FILL_FOREGROUND_COLOR, IndexedColors.BLUE.index); |
| properties.put(CellUtil.FILL_BACKGROUND_COLOR, IndexedColors.RED.index); |
| |
| CellUtil.setCellStyleProperties(A1, properties); |
| CellStyle style = A1.getCellStyle(); |
| assertEquals("fill pattern", FillPatternType.BRICKS, style.getFillPatternEnum()); |
| assertEquals("fill foreground color", IndexedColors.BLUE, IndexedColors.fromInt(style.getFillForegroundColor())); |
| assertEquals("fill background color", IndexedColors.RED, IndexedColors.fromInt(style.getFillBackgroundColor())); |
| wb1.close(); |
| } |
| /** |
| * bug 55555 |
| * @since POI 3.15 beta 3 |
| */ |
| @Test |
| public void setFillForegroundColorBeforeFillBackgroundColorEnum() throws IOException { |
| Workbook wb1 = _testDataProvider.createWorkbook(); |
| Cell A1 = wb1.createSheet().createRow(0).createCell(0); |
| Map<String, Object> properties = new HashMap<String, Object>(); |
| properties.put(CellUtil.FILL_PATTERN, FillPatternType.BRICKS); |
| properties.put(CellUtil.FILL_FOREGROUND_COLOR, IndexedColors.BLUE.index); |
| properties.put(CellUtil.FILL_BACKGROUND_COLOR, IndexedColors.RED.index); |
| |
| CellUtil.setCellStyleProperties(A1, properties); |
| CellStyle style = A1.getCellStyle(); |
| assertEquals("fill pattern", FillPatternType.BRICKS, style.getFillPatternEnum()); |
| assertEquals("fill foreground color", IndexedColors.BLUE, IndexedColors.fromInt(style.getFillForegroundColor())); |
| assertEquals("fill background color", IndexedColors.RED, IndexedColors.fromInt(style.getFillBackgroundColor())); |
| |
| wb1.close(); |
| } |
| } |