Java tutorial
/******************************************************************************* * Educational Online Test Delivery System * Copyright (c) 2013 American Institutes for Research * * Distributed under the AIR Open Source License, Version 1.0 * See accompanying file AIR-License-1_0.txt or at * http://www.smarterapp.org/documents/American_Institutes_for_Research_Open_Source_Software_License.pdf ******************************************************************************/ package org.opentestsystem.delivery.testreg.upload.parser; import static org.hamcrest.CoreMatchers.notNullValue; import static org.hamcrest.core.Is.is; import static org.junit.Assert.assertThat; import java.io.File; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.util.List; import java.util.Map; import org.apache.commons.io.FileUtils; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.junit.After; import org.junit.Before; import org.junit.Test; import org.opentestsystem.delivery.testreg.upload.DataRecord; import org.opentestsystem.delivery.testreg.upload.ExcelUtils; import org.springframework.test.util.ReflectionTestUtils; public class ExcelFileUploadParserTest { private ExcelFileUploadParser excelParser; private final File excelFile = new File(System.getProperty("java.io.tmpdir"), "excel_test_file.xlsx"); @Before public void setup() throws FileNotFoundException, IOException, InvalidFormatException { this.excelParser = new ExcelFileUploadParser(); ReflectionTestUtils.setField(this.excelParser, "excelUtils", new ExcelUtils()); deleteFiles(); } private void deleteFileIfExists(final File file) { if (file.isFile() && file.exists()) { file.delete(); } } @After public void deleteFiles() { deleteFileIfExists(this.excelFile); } private Workbook makeNormalExcelFile(final File file) throws FileNotFoundException, IOException { final Workbook excelWorkbook = new HSSFWorkbook(); final Sheet testSheet = excelWorkbook.createSheet("Test"); final Row headerRow = testSheet.createRow(0); final Cell cell0 = headerRow.createCell(0); cell0.setCellValue("countries"); final Cell continent = headerRow.createCell(1); continent.setCellValue("continent"); final Cell population = headerRow.createCell(2); population.setCellType(Cell.CELL_TYPE_NUMERIC); population.setCellValue("population"); final Cell onePercent = headerRow.createCell(3); onePercent.setCellType(Cell.CELL_TYPE_STRING); onePercent.setCellValue("1percent"); final Cell economy = headerRow.createCell(4); economy.setCellType(Cell.CELL_TYPE_STRING); economy.setCellValue("economy"); final Cell headOfState = headerRow.createCell(5); headOfState.setCellValue("HeadOfState"); final Cell yearsInOffice = headerRow.createCell(6); yearsInOffice.setCellValue("YearsInOffice"); final Row dataRow1 = testSheet.createRow(1); dataRow1.createCell(0).setCellValue("Cananda"); dataRow1.createCell(1).setCellValue("North America"); dataRow1.createCell(2).setCellValue("39M"); dataRow1.createCell(3).setCellValue("246000"); dataRow1.createCell(4).setCellValue("free market"); dataRow1.createCell(5).setCellValue("Queen Elizabeth II"); dataRow1.createCell(6).setCellValue("for ever"); final Row dataRow2 = testSheet.createRow(2); dataRow2.createCell(0).setCellValue("America"); dataRow2.createCell(1).setCellValue("North America"); dataRow2.createCell(2).setCellValue("250M"); dataRow2.createCell(3).setCellValue("6200000"); dataRow2.createCell(4).setCellValue("laissez faire"); dataRow2.createCell(5).setCellValue("President"); dataRow2.createCell(6).setCellValue("8 yrs"); return excelWorkbook; } private void addBlankRows(final Workbook workbook) throws InvalidFormatException, IOException { final Sheet sheet = workbook.getSheet("Test"); addBlankCells(sheet.createRow(3)); addBlankCells(sheet.createRow(4)); addBlankCells(sheet.createRow(5)); addBlankCells(sheet.createRow(6)); addBlankCells(sheet.createRow(7)); addBlankCells(sheet.createRow(8)); addBlankCells(sheet.createRow(9)); } private void addSomeRecordsWithWhitespaces(final Workbook workbook) { final Sheet sheet = workbook.getSheet("Test"); final Row dataRow4 = sheet.createRow(3); dataRow4.createCell(0).setCellValue(" China "); dataRow4.createCell(1).setCellValue("Asia"); dataRow4.createCell(2).setCellValue(" 1.35B "); dataRow4.createCell(3).setCellValue("200000 "); dataRow4.createCell(4).setCellValue(" Socialist-Communist"); dataRow4.createCell(5).setCellValue(" President "); dataRow4.createCell(6).setCellValue(" 8 yrs"); final Row dataRow5 = sheet.createRow(4); dataRow5.createCell(0).setCellValue("Singapore "); dataRow5.createCell(1).setCellValue(" Asia"); dataRow5.createCell(2).setCellValue("5.3M"); dataRow5.createCell(3).setCellValue(" 10000 "); dataRow5.createCell(4).setCellValue(" Capitalist "); dataRow5.createCell(5).setCellValue(" President"); dataRow5.createCell(6).setCellValue("4 yrs "); } private void addBlankCells(final Row row) { for (int i = 0; i < 3; i++) { final Cell cell = row.createCell(i, Cell.CELL_TYPE_BLANK); cell.setAsActiveCell(); } } private void addBlankFormulaRows(final Workbook workbook) throws InvalidFormatException, IOException { final Sheet sheet = workbook.getSheet("Test"); addFormulaCells(sheet.createRow(3)); addFormulaCells(sheet.createRow(4)); addFormulaCells(sheet.createRow(5)); addFormulaCells(sheet.createRow(6)); addFormulaCells(sheet.createRow(7)); addFormulaCells(sheet.createRow(8)); addFormulaCells(sheet.createRow(9)); } private void addFormulaCells(final Row row) { for (int i = 0; i < 3; i++) { final Cell cell = row.createCell(i, Cell.CELL_TYPE_FORMULA); cell.setAsActiveCell(); } } @Test public void testNormalExcel() throws Exception { final Workbook excelWb = makeNormalExcelFile(this.excelFile); excelWb.write(new FileOutputStream(this.excelFile)); final Map<String, List<DataRecord>> recordMap = this.excelParser .parse(FileUtils.openInputStream(this.excelFile), "GROUPOFSTATES").getParsedObject(); assertThat(recordMap, notNullValue()); assertThat(recordMap.get("GROUPOFSTATES").size(), is(3)); } @Test public void testNormalExcelWithBlankRows() throws Exception { final Workbook blankExcelWb = makeNormalExcelFile(this.excelFile); addBlankRows(blankExcelWb); blankExcelWb.write(new FileOutputStream(this.excelFile)); final Map<String, List<DataRecord>> recordMap = this.excelParser .parse(FileUtils.openInputStream(this.excelFile), "GROUPOFSTATES").getParsedObject(); assertThat(recordMap, notNullValue()); assertThat(recordMap.get("GROUPOFSTATES").size(), is(10)); } @Test(expected = IllegalArgumentException.class) public void testNormalExcelWithBlankRowsWithZeroRecordLength() throws Exception { final Workbook blankExcelWb = makeNormalExcelFile(this.excelFile); addBlankFormulaRows(blankExcelWb); blankExcelWb.write(new FileOutputStream(this.excelFile)); final Map<String, List<DataRecord>> recordMap = this.excelParser .parse(FileUtils.openInputStream(this.excelFile), "GROUPOFSTATES").getParsedObject(); assertThat(recordMap, notNullValue()); assertThat(recordMap.get("GROUPOFSTATES").size(), is(10)); } @Test public void testIgnoreBlankRows() throws Exception { this.excelParser.setIgnoreEmptyRows(true); final Workbook blankExcelWb = makeNormalExcelFile(this.excelFile); addBlankRows(blankExcelWb); blankExcelWb.write(new FileOutputStream(this.excelFile)); final Map<String, List<DataRecord>> recordMap = this.excelParser .parse(FileUtils.openInputStream(this.excelFile), "GROUPOFSTATES").getParsedObject(); assertThat(recordMap, notNullValue()); assertThat(recordMap.get("GROUPOFSTATES").size(), is(3)); } @Test public void testRowsWithWhitespaces() throws Exception { this.excelParser.setTrimRecords(true); final Workbook excelWb = makeNormalExcelFile(this.excelFile); addSomeRecordsWithWhitespaces(excelWb); excelWb.write(new FileOutputStream(this.excelFile)); final Map<String, List<DataRecord>> recordMap = this.excelParser .parse(FileUtils.openInputStream(this.excelFile), "GROUPOFSTATES").getParsedObject(); assertThat(recordMap, notNullValue()); assertThat(recordMap.get("GROUPOFSTATES").size(), is(5)); assertThat(recordMap.get("GROUPOFSTATES").get(3).getColumns(), is( new String[] { "China", "Asia", "1.35B", "200000", "Socialist-Communist", "President", "8 yrs" })); assertThat(recordMap.get("GROUPOFSTATES").get(4).getColumns(), is(new String[] { "Singapore", "Asia", "5.3M", "10000", "Capitalist", "President", "4 yrs" })); } }