ExcelUtilTest.java example

Explorer
openmrs-module-reporting-master
/**
 * The contents of this file are subject to the OpenMRS Public License
 * Version 1.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://license.openmrs.org
 *
 * Software distributed under the License is distributed on an "AS IS"
 * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
 * License for the specific language governing rights and limitations
 * under the License.
 *
 * Copyright (C) OpenMRS, LLC.  All Rights Reserved.
 */
package org.openmrs.module.reporting.common;

import org.junit.Assert;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.junit.Test;
import org.openmrs.test.BaseModuleContextSensitiveTest;

import java.util.Date;
import java.util.HashSet;
import java.util.Iterator;
import java.util.Set;

import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertTrue;

/**
 * Testing the ExcelUtil class.
 */
public class ExcelUtilTest extends BaseModuleContextSensitiveTest {

	protected Log log = LogFactory.getLog(this.getClass());

	@Test
	public void shouldGetCellContents() throws Exception {
		Workbook wb = ExcelUtil.loadWorkbookFromResource("org/openmrs/module/reporting/common/ExcelUtilTest.xls");
		Sheet sheet = wb.getSheet("Testing");
		testCellContentsToTheRightOf(sheet, "String", "This is a String");
		testCellContentsToTheRightOf(sheet, "Bold String", "This is a bold String");
		testCellContentsToTheRightOf(sheet, "Integer", 100);
		testCellContentsToTheRightOf(sheet, "Number", 100.5);
		testCellContentsToTheRightOf(sheet, "Boolean", true);
		testCellContentsToTheRightOf(sheet, "Formula", "B5*2");
		testCellContentsToTheRightOf(sheet, "Date", DateUtil.getDateTime(2011,10,31));
		testCellContentsToTheRightOf(sheet, "Time", DateUtil.getDateTime(2011,10,31,11,32,0,0));
	}

	@Test
	public void shouldSetCellContents() throws Exception {
		Workbook wb = ExcelUtil.loadWorkbookFromResource("org/openmrs/module/reporting/common/ExcelUtilTest.xls");
		Sheet sheet = wb.getSheet("Testing");
		Date testDate = DateUtil.getDateTime(1999,3,17);
		int testDateExcel = (int)ExcelUtil.getDateAsNumber(testDate);
		testSettingCellContents(sheet, "String", "New String", Cell.CELL_TYPE_STRING, "New String");
		testSettingCellContents(sheet, "String", 100, Cell.CELL_TYPE_NUMERIC, 100);
		testSettingCellContents(sheet, "Integer", 20.2, Cell.CELL_TYPE_NUMERIC, 20.2);
		testSettingCellContents(sheet, "Boolean", Boolean.FALSE, Cell.CELL_TYPE_BOOLEAN, false);
		testSettingCellContents(sheet, "Date", testDate, Cell.CELL_TYPE_NUMERIC, testDate);
		testSettingCellContents(sheet, "String", testDate, Cell.CELL_TYPE_NUMERIC, testDateExcel);
		testSettingCellContents(sheet, "Formula", "B5*3", Cell.CELL_TYPE_FORMULA, "B5*3");
	}

	@Test
	public void shouldAddStyle() throws Exception {
		Workbook wb = ExcelUtil.loadWorkbookFromResource("org/openmrs/module/reporting/common/ExcelUtilTest.xls");
		Sheet sheet = wb.getSheet("Testing");

		// Test Fonts
		Cell cell = getCellToTheRightOf(sheet, "String");
		Assert.assertEquals("This is a String", ExcelUtil.getCellContents(cell));

		Assert.assertEquals(Font.BOLDWEIGHT_NORMAL, ExcelUtil.getFont(cell).getBoldweight());
		cell.setCellStyle(ExcelUtil.createCellStyle(wb, "bold"));
		Assert.assertEquals(Font.BOLDWEIGHT_BOLD, ExcelUtil.getFont(cell).getBoldweight());

		Assert.assertFalse(ExcelUtil.getFont(cell).getItalic());
		Assert.assertEquals(Font.U_NONE, ExcelUtil.getFont(cell).getUnderline());
		cell.setCellStyle(ExcelUtil.createCellStyle(wb, "italic,underline"));
		Assert.assertTrue(ExcelUtil.getFont(cell).getItalic());
		Assert.assertEquals(Font.U_SINGLE, ExcelUtil.getFont(cell).getUnderline());

		int fontSize = ExcelUtil.getFont(cell).getFontHeightInPoints() + 1;
		cell.setCellStyle(ExcelUtil.createCellStyle(wb, "size="+fontSize));
		Assert.assertEquals((short)fontSize, ExcelUtil.getFont(cell).getFontHeightInPoints());

		// Test other styles
		Assert.assertFalse(cell.getCellStyle().getWrapText());
		Assert.assertEquals(CellStyle.ALIGN_GENERAL, cell.getCellStyle().getAlignment());
		Assert.assertEquals(CellStyle.BORDER_NONE, cell.getCellStyle().getBorderBottom());
		cell.setCellStyle(ExcelUtil.createCellStyle(wb, "wraptext,align=center,border=bottom"));
		Assert.assertTrue(cell.getCellStyle().getWrapText());
		Assert.assertEquals(CellStyle.ALIGN_CENTER, cell.getCellStyle().getAlignment());
		Assert.assertEquals(CellStyle.BORDER_THIN, cell.getCellStyle().getBorderBottom());

		// Test Date
		Date date = DateUtil.getDateTime(2013, 10, 31);
		cell.setCellValue(date);
		ExcelUtil.formatAsDate(cell);
		Assert.assertEquals(Cell.CELL_TYPE_NUMERIC, cell.getCellType());
		Assert.assertTrue(ExcelUtil.isCellDateFormatted(cell));
		Assert.assertEquals(date, ExcelUtil.getCellContents(cell));
	}

	@Test
	public void shouldFormatSheetTitle() throws Exception {

		Assert.assertEquals("TestSheet", ExcelUtil.formatSheetTitle("TestSheet"));
		Assert.assertEquals("Sheet", ExcelUtil.formatSheetTitle(null));
		Assert.assertEquals("Illegal Characters", ExcelUtil.formatSheetTitle("Illegal [Characters]"));
		Assert.assertEquals("This is a title with over 31 ch", ExcelUtil.formatSheetTitle("This is a title with over 31 characters"));

		Set<String> usedTitles = new HashSet<String>();
		String startingTitle = "Starting Title With Too Many Characters";

		String title1 = ExcelUtil.formatSheetTitle(startingTitle, usedTitles);
		Assert.assertEquals("Starting Title With Too Many Ch", title1);
		usedTitles.add(title1);

		String title2 = ExcelUtil.formatSheetTitle(startingTitle, usedTitles);
		Assert.assertEquals("Starting Title With Too Many-1", title2);
		usedTitles.add(title2);

		String title3 = ExcelUtil.formatSheetTitle(startingTitle, usedTitles);
		Assert.assertEquals("Starting Title With Too Many-2", title3);
		usedTitles.add(title3);
	}

    @Test
    public void shouldCheckWhetherCellValueIsSet() throws Exception {
        ExcelBuilder builder = new ExcelBuilder();
        builder.newSheet("Sheet1");
        builder.addCell("One");

        assertFalse(ExcelUtil.cellHasValueSet(builder.getCurrentRow().getCell(1)));

        builder.addCell("Two");
        assertTrue(ExcelUtil.cellHasValueSet(builder.getCurrentRow().getCell(1)));
    }

	protected void testCellContentsToTheRightOf(Sheet sheet, String contentsBefore, Object contentsToTest) {
		Cell c = getCellToTheRightOf(sheet, contentsBefore);
		Object contentsToCheck = ExcelUtil.getCellContents(c);
		Assert.assertEquals(contentsToTest, contentsToCheck);
	}

	protected void testSettingCellContents(Sheet sheet, String contentsBefore, Object valueToSet, int expectedCellType, Object expectedContents) {
		Cell cell = getCellToTheRightOf(sheet, contentsBefore);
		ExcelUtil.setCellContents(cell, valueToSet);
		Assert.assertEquals(expectedCellType, cell.getCellType());
		Object actualContents = ExcelUtil.getCellContents(cell);
		Assert.assertEquals(expectedContents, actualContents);
	}

	protected Cell getCellToTheRightOf(Sheet sheet, Object contents) {
		for (Iterator<Row> ri = sheet.rowIterator(); ri.hasNext();) {
			Row row = ri.next();
			for (Iterator<Cell> ci = row.cellIterator(); ci.hasNext();) {
				Cell cell = ci.next();
				if (contents.equals(ExcelUtil.getCellContents(cell))) {
					return ci.next();
				}
			}
		}
		return null;
	}
}