Java tutorial
/*$Id: BaseExcelParserTest.java 16165 2011-01-30 14:59:06Z jens $*/ /* **************************************************************************** * * * (c) Copyright 2008 ABM-utvikling * * * * This program is free software; you can redistribute it and/or modify it * * under the terms of the GNU General Public License as published by the * * Free Software Foundation; either version 2 of the License, or (at your * * option) any later version. * * * * This program is distributed in the hope that it will be useful, but * * WITHOUT ANY WARRANTY; without even the implied warranty of * * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General * * Public License for more details. http://www.gnu.org/licenses/gpl.html * * * **************************************************************************** */ package no.abmu.common.excel; import java.io.File; import java.text.DateFormat; import java.text.SimpleDateFormat; import java.util.Date; import java.util.List; import junit.framework.Assert; import junit.framework.TestCase; import no.abmu.util.date.DateUtil; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.apache.poi.hssf.usermodel.HSSFWorkbook; /** * ExcelParser. * * @author Jens.Vindvad, Jens.Vindvad@abm-utvikling.no * @author $Author: jens $ * @version $Rev: 16165 $ * @date $Date: 2011-01-30 15:59:06 +0100 (Sun, 30 Jan 2011) $ * @copyright ABM-Utvikling * */ public class BaseExcelParserTest extends TestCase { @SuppressWarnings("unused") private static final Log logger = (Log) LogFactory.getLog(BaseExcelParserTest.class); private final String excelFileName1 = "import1.xls"; private String resourceDir; private String fileName1; private String sheetName1; protected void setUp() throws Exception { if (resourceDir == null) { resourceDir = LocalTestUtil.getTestImportResourceDir(excelFileName1); } fileName1 = resourceDir + File.separator + excelFileName1; sheetName1 = "Ark1"; } public void testNoFileNameGiven() { BaseExcelParser baseExcelParser = new BaseExcelParserImpl(); try { baseExcelParser.load(); fail("Should have thrown exception."); } catch (IllegalStateException e) { String expectedErrorMessage = "Can't parse Excel document. No filename specified"; assertEquals(expectedErrorMessage, e.getMessage()); } } public void testNonExistingFile() { String nonExistentFileName = resourceDir + File.separator + "nonExistentFile.xls"; BaseExcelParser baseExcelParser = new BaseExcelParserImpl(); baseExcelParser.setExcelFileName(nonExistentFileName); baseExcelParser.setSheetName(sheetName1); try { baseExcelParser.load(); fail("Should have thrown exception."); } catch (IllegalArgumentException e) { String expectedErrorMessage = "Can't parse Excel document. File " + nonExistentFileName + " does not exist"; assertEquals(expectedErrorMessage, e.getMessage()); } } public void testNotExcelFile() { String dummyFileName = resourceDir + File.separator + "dummy.txt"; BaseExcelParser baseExcelParser = new BaseExcelParserImpl(); baseExcelParser.setExcelFileName(dummyFileName); baseExcelParser.setSheetName(sheetName1); try { baseExcelParser.load(); fail("Should have thrown exception."); } catch (IllegalStateException e) { String expectedErrorMessage = "Can't parse Excel document. Failed to read file '" + dummyFileName + "' java.io.IOException: Unable to read entire header; 27 bytes read; expected 512 bytes"; assertEquals(expectedErrorMessage, e.getMessage()); } } public void testNoSheetNameGiven() { BaseExcelParser baseExcelParser = new BaseExcelParserImpl(); baseExcelParser.setExcelFileName(fileName1); try { baseExcelParser.load(); fail("Should have thrown exception."); } catch (IllegalArgumentException e) { String expectedErrorMessage = "Can't extract information, sheet not found"; assertEquals(expectedErrorMessage, e.getMessage()); } } public void testNonExistingSheetName() { BaseExcelParser baseExcelParser = new BaseExcelParserImpl(); baseExcelParser.setExcelFileName(fileName1); baseExcelParser.setSheetName("NonExistingSheetName_Ark1"); try { baseExcelParser.load(); fail("Should have thrown exception."); } catch (IllegalArgumentException e) { String expectedErrorMessage = "Can't extract information, sheet not found"; assertEquals(expectedErrorMessage, e.getMessage()); } } public void testEmptySheet() { BaseExcelParser baseExcelParser = new BaseExcelParserImpl(); baseExcelParser.setExcelFileName(fileName1); baseExcelParser.setSheetName("Ark6"); try { baseExcelParser.load(); fail("Should have thrown exception."); } catch (IllegalArgumentException e) { String expectedErrorMessage = "Can't extract information, missing row on sheet"; assertEquals(expectedErrorMessage, e.getMessage()); } } public void testConstructorBasedOnExistingBook() { BaseExcelParser baseExcelParser = new BaseExcelParserImpl(); baseExcelParser.setExcelFileName(fileName1); baseExcelParser.setSheetName(sheetName1); baseExcelParser.load(); HSSFWorkbook workBook = baseExcelParser.getWorkBook(); BaseExcelParser baseExcelParser2; String sheetName2 = "Ark2"; baseExcelParser2 = new BaseExcelParserImpl(workBook, sheetName2, 1); int numberOfRowsWithData = baseExcelParser2.countNumberOfRowsWithData(); Assert.assertEquals(4, numberOfRowsWithData); } public void testReadingCell() { BaseExcelParser baseExcelParser = new BaseExcelParserImpl(); baseExcelParser.setExcelFileName(fileName1); baseExcelParser.setSheetName(sheetName1); baseExcelParser.load(); String expectedSchemaName = "MuseumFinanceApplication"; String schemaName = baseExcelParser.getString("SCHEMANAME"); assertEquals(expectedSchemaName, schemaName); Long schemaNameAsLong = baseExcelParser.getLong("SCHEMANAME"); assertNull("We expected schemaNameAsLong to be null", schemaNameAsLong); String expectedPostCode = "401"; String postCode = baseExcelParser.getString("POSTCODE"); assertEquals(expectedPostCode, postCode); Long expectedPostCodeAsLong = Long.valueOf("401"); Long postCodeAsLong = baseExcelParser.getLong("POSTCODE"); assertEquals(expectedPostCodeAsLong, postCodeAsLong); } public void testGetDouble() { BaseExcelParser baseExcelParser = new BaseExcelParserImpl(); baseExcelParser.setExcelFileName(fileName1); baseExcelParser.setSheetName("Ark4"); baseExcelParser.load(); Double expectedAmountDoubleValue = Double.valueOf("3.56"); String columnNameAmount = "AMOUNT"; Double doubleValueAmountFromExcel = baseExcelParser.getDouble(columnNameAmount); assertEquals(expectedAmountDoubleValue, doubleValueAmountFromExcel); String columnNameNoNumeric = "NO_NUMERIC"; Double doubleValueNoNumericAmountFromExcel = baseExcelParser.getDouble(columnNameNoNumeric); assertNull("We expect no numeric value from excel to be null", doubleValueNoNumericAmountFromExcel); String columnNameBlank = "BLANK"; Double doubleValueBlankAmountFromExcel = baseExcelParser.getDouble(columnNameBlank); assertNull("We expect blank value from excel to be null", doubleValueBlankAmountFromExcel); Double expectedAmountAsStringDoubleValue = Double.valueOf("456"); String columnNameAmountAsString = "AMOUNT_AS_STRING"; Double doubleValueAmountAsStringFromExcel = baseExcelParser.getDouble(columnNameAmountAsString); assertEquals(expectedAmountAsStringDoubleValue, doubleValueAmountAsStringFromExcel); // test empty string String columnNameEmptyString = "EMPTY_STRING"; Double doubleValueEmptyStringFromExcel = baseExcelParser.getDouble(columnNameEmptyString); assertNull("We expect blank value from excel to be null", doubleValueEmptyStringFromExcel); // test non existence columnName String nonExistingColumnName = "NonXXXEXistingColumnName"; // Exercise SUT and verify try { baseExcelParser.getDouble(nonExistingColumnName); fail("Should have thrown IllegalArgumentException"); } catch (IllegalArgumentException e) { String errorMessage = "The sheet does not have a column with name '" + nonExistingColumnName + "'"; assertEquals(errorMessage, e.getMessage()); } try { baseExcelParser.next(); baseExcelParser.next(); baseExcelParser.next(); fail("Should have thrown IllegalStateException"); } catch (IllegalStateException e) { String errorMessage = "Can't move to next row, no more rows."; assertEquals(errorMessage, e.getMessage()); } } public void testGetDate() { BaseExcelParser baseExcelParser = new BaseExcelParserImpl(); baseExcelParser.setExcelFileName(fileName1); baseExcelParser.setSheetName("Ark5"); baseExcelParser.load(); // test non existence columnName String nonExistingColumnName = "NonXXXEXistingColumnName"; // Exercise SUT and verify try { baseExcelParser.getDate(nonExistingColumnName); fail("Should have thrown IllegalArgumentException"); } catch (IllegalArgumentException e) { String errorMessage = "The sheet does not have a column with name '" + nonExistingColumnName + "'"; assertEquals(errorMessage, e.getMessage()); } // test reading excel date String excelDateColumnName = "EXCEL_DATE"; Date expectedExcelDate = DateUtil.parseISODate("2004-12-12"); Date dateValueExcelDate = baseExcelParser.getDate(excelDateColumnName); assertEquals(expectedExcelDate, dateValueExcelDate); // test empty string String columnNameEmptyString = "EMPTY_STRING"; Date dateValueEmptyStringFromExcel = baseExcelParser.getDate(columnNameEmptyString); assertNull("We expect blank value from excel to be null", dateValueEmptyStringFromExcel); String columnNameNoStringDate = "NO_DATE_STRING"; Date dateValueNoStringDateFromExcel = baseExcelParser.getDate(columnNameNoStringDate); assertNull("We expect no numeric value from excel to be null", dateValueNoStringDateFromExcel); // test reading no date number String columnNameNoDateNumber = "NO_DATE_NUMBER"; Date dateValueNoDateNumberFromExcel = baseExcelParser.getDate(columnNameNoDateNumber); System.out.println("No date number has value ='" + dateValueNoDateNumberFromExcel + "'."); assertNull("We expect no numeric value from excel to be null", dateValueNoDateNumberFromExcel); // test reading date as string String excelDateAsStringColumnName = "DATE_AS_ISO_STRING"; Date exceptedDateAsStringExcelDate = DateUtil.parseISODate("2008-01-02"); Date dateValueAsStringExcelDate = baseExcelParser.getDate(excelDateAsStringColumnName); assertEquals(exceptedDateAsStringExcelDate, dateValueAsStringExcelDate); // test reading date as string with custom dateFormat String customDateFormatColumnName = "CUSTOM_STRING_DATEFORMAT"; DateFormat customDateFormat = new SimpleDateFormat("dd.MM.yyyy"); Date exceptedCustomDateFormatExcelDate = DateUtil.parseISODate("2006-12-12"); Date dateValueCustomDateFormat = baseExcelParser.getDate(customDateFormatColumnName, customDateFormat); assertEquals(exceptedCustomDateFormatExcelDate, dateValueCustomDateFormat); } public void testMethodGetListOfColumnHeaderName() { BaseExcelParser baseExcelParser = new BaseExcelParserImpl(); baseExcelParser.setExcelFileName(fileName1); baseExcelParser.setSheetName("Ark4"); baseExcelParser.load(); List<String> columnHeaderNames = baseExcelParser.getListOfColumnHeaderName(); assertEquals("USER", columnHeaderNames.get(0)); assertEquals("AMOUNT", columnHeaderNames.get(1)); } }