/* ====================================================================
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(CellStyle.ALIGN_GENERAL, A1.getCellStyle().getAlignment());
assertEquals(CellStyle.ALIGN_GENERAL, B1.getCellStyle().getAlignment());
// get/set alignment modifies the cell's style
CellUtil.setAlignment(A1, null, CellStyle.ALIGN_RIGHT);
assertEquals(CellStyle.ALIGN_RIGHT, A1.getCellStyle().getAlignment());
// 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(CellStyle.ALIGN_GENERAL, B1.getCellStyle().getAlignment());
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>();
// FIXME: Use FillPatternType.BRICKS enum
properties.put(CellUtil.FILL_PATTERN, CellStyle.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();
// FIXME: Use FillPatternType.BRICKS enum
assertEquals("fill pattern", CellStyle.BRICKS, style.getFillPattern());
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>();
// FIXME: Use FillPatternType.BRICKS enum
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();
// FIXME: Use FillPatternType.BRICKS enum
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();
}
}