Java tutorial
/*$Id: BaseExcelParser.java 9202 2008-03-19 17:11:36Z 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.util.Date; 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; /** * ExcelParser. * * @author Jens.Vindvad, Jens.Vindvad@abm-utvikling.no * @author $Author: jens $ * @version $Rev: 9202 $ * @date $Date: 2008-03-19 18:11:36 +0100 (Wed, 19 Mar 2008) $ * @copyright ABM-Utvikling * */ public class ExcelParserTest extends TestCase { @SuppressWarnings("unused") private static final Log logger = (Log) LogFactory.getLog(ExcelParserTest.class); private final String excelFileName1 = "import1.xls"; private String resourceDir; private ExcelParser excelParser1; protected void setUp() throws Exception { if (resourceDir == null) { resourceDir = LocalTestUtil.getTestImportResourceDir(excelFileName1); } excelParser1 = new ExcelParserImpl(); String fileName1 = resourceDir + File.separator + excelFileName1; excelParser1.setExcelFileName(fileName1); excelParser1.setSheetName("Ark1"); } public void testCheckInputValues() { String[] notNullNames = { "ACCOUNTNAME", "POSTCODE" }; String errorMessage1 = excelParser1.checkInputValues(notNullNames); Assert.assertNull("This error message should have been null.", errorMessage1); // Column with name "SUBSCHEMANAME" have empty values. excelParser1.setInputStopTagColumnName("SUBSCHEMANAME"); String[] notNullNamesWithError = { "ACCOUNTNAME", "POSTCODE", "SUBSCHEMANAME" }; String errorMessage2 = excelParser1.checkInputValues(notNullNamesWithError); Assert.assertNotNull("Her we should have an error message.", errorMessage2); } public void testNumberOfRowWithData() { excelParser1.load(); int numberOfRowsWithData = excelParser1.countNumberOfRowsWithData(); Assert.assertEquals(32, numberOfRowsWithData); } public void testHasColumn() { excelParser1.load(); boolean hasColumn1 = excelParser1.hasColumn("ACCOUNTNAME"); Assert.assertTrue("We expect that the excel file has columnName='ACCOUNTNAME'", hasColumn1); boolean hasColumn2 = excelParser1.hasColumn("WRONG WRONG XXX"); Assert.assertFalse("We do not expect that the excel file has columnName='WRONG WRONG XXX'", hasColumn2); } public void testEndOfInputTag() { excelParser1.setSheetName("Ark3"); excelParser1.load(); int numberOfRowBeforEndOfInputTag = excelParser1.countNumberOfRowsInSheetBeforeStopTag(); Assert.assertEquals("We expected 22 rows not '" + numberOfRowBeforEndOfInputTag + "'", 22, numberOfRowBeforEndOfInputTag); } public void testCopyCopyTagOnStringValues() { String columnName = "ORG TYPE"; excelParser1.setSheetName("Ark3"); excelParser1.load(); // Just to be sure. boolean hasColumn4 = excelParser1.hasColumn(columnName); Assert.assertTrue(hasColumn4); String stringValue = "Archive"; for (; excelParser1.hasNext(); excelParser1.next()) { assertEquals("Has not expected value on row '" + excelParser1.getCurrentRowNumber() + "'", stringValue, excelParser1.getCellValueAsString(columnName)); } } public void testCopyCoyTagOnLongValues() { String columnName = "USER"; excelParser1.setSheetName("Ark3"); excelParser1.load(); // Just to be sure. boolean hasColumn1 = excelParser1.hasColumn(columnName); Assert.assertTrue(hasColumn1); Long longValuesRow02To09 = Long.valueOf(1); Long longValueRow10 = null; Long longValueRow11To17 = Long.valueOf(20); Long longValueRow18To23 = null; for (; excelParser1.hasNext(); excelParser1.next()) { int row = excelParser1.getCurrentRowNumber(); Long rowValue = excelParser1.getCellValueAsLong(columnName); if (row < 2) { // We start parsing on row 2 row 1 are header fail("We do not expect to this on row '" + excelParser1.getCurrentRowNumber() + "'"); } else if (row >= 2 && row <= 9) { assertEquals("Has not expected value on row '" + excelParser1.getCurrentRowNumber() + "'", longValuesRow02To09, rowValue); } else if (row == 10) { assertEquals("Has not expected value on row '" + excelParser1.getCurrentRowNumber() + "'", longValueRow10, rowValue); } else if (row > 10 && row <= 17) { assertEquals("Has not expected value on row '" + excelParser1.getCurrentRowNumber() + "'", longValueRow11To17, rowValue); } else if (row >= 18) { assertEquals("Has not expected value on row '" + excelParser1.getCurrentRowNumber() + "'", longValueRow18To23, rowValue); } } } public void testGetCellValueAsDouble() { excelParser1.setSheetName("Ark4"); excelParser1.load(); Double expectedAmountDoubleValue = Double.valueOf("3.56"); String columnNameAmount = "AMOUNT"; Double doubleValueAmountFromExcel = excelParser1.getCellValueAsDouble(columnNameAmount); assertEquals(expectedAmountDoubleValue, doubleValueAmountFromExcel); // test empty string String columnNameEmptyString = "EMPTY_STRING"; Double doubleValueEmptyStringFromExcel = excelParser1.getCellValueAsDouble(columnNameEmptyString); assertNull("We expect blank value from excel to be null", doubleValueEmptyStringFromExcel); // test empty string excelParser1.next(); Double doubleValueEmptyStringFromExcel2 = excelParser1.getCellValueAsDouble(columnNameEmptyString); assertNull("We expect blank value from excel to be null", doubleValueEmptyStringFromExcel2); } public void testGetCellValueAsDate() { excelParser1.setSheetName("Ark5"); excelParser1.load(); // test reading excel date String excelDateColumnName = "EXCEL_DATE"; Date expectedExcelDate = DateUtil.parseISODate("2004-12-12"); Date dateValueExcelDate = excelParser1.getCellValueAsDate(excelDateColumnName); assertEquals(expectedExcelDate, dateValueExcelDate); // test empty string String columnNameEmptyString = "EMPTY_STRING"; Date dateValueEmptyStringFromExcel = excelParser1.getCellValueAsDate(columnNameEmptyString); assertNull("We expect blank value from excel to be null", dateValueEmptyStringFromExcel); String columnNameNoStringDate = "NO_DATE_STRING"; Date dateValueNoStringDateFromExcel = excelParser1.getCellValueAsDate(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 = excelParser1.getCellValueAsDate(columnNameNoDateNumber); System.out.println("No date number has value ='" + dateValueNoDateNumberFromExcel + "'."); assertNull("We expect no numeric value from excel to be null", dateValueNoDateNumberFromExcel); // test empty string excelParser1.next(); Date dateValueEmptyStringFromExcel2 = excelParser1.getCellValueAsDate(columnNameEmptyString); assertNull("We expect blank value from excel to be null", dateValueEmptyStringFromExcel2); } }