no.abmu.common.excel.BaseExcelParserTest.java Source code

Java tutorial

Introduction

Here is the source code for no.abmu.common.excel.BaseExcelParserTest.java

Source

/*$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));
    }

}