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; import static org.opentestsystem.delivery.testreg.upload.parser.ParserTextUtils.isEmptyRecord; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.Arrays; import java.util.List; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.DataFormatter; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Row.MissingCellPolicy; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; import org.apache.poi.xssf.usermodel.XSSFCell; import org.opentestsystem.shared.exception.LocalizedException; public class ExcelUtils { private static final int DEFAULT_SHEET_INDEX = 1; // Just First Sheet private final boolean iterateAllSheets; private boolean ignoreEmptyRows = false; /** * Default Constructor. By design we don't need to iterate over all the worksheets in a workbook. */ public ExcelUtils() { this(false); } /** * This constructor is not used anywhere as of now. There is no reason other than that to make it private. * * @param iterateAllSheets * Does the sheet processor need to iterate over all worksheets */ private ExcelUtils(final boolean iterateAllSheets) { this.iterateAllSheets = iterateAllSheets; } /* * Default Separation Policy. Returns null and blank as they are. */ private static final MissingCellPolicy DEFAULT_POLICY = Row.RETURN_NULL_AND_BLANK; public Object[] getCellValues(final Row row) { return getCellValues(row, DEFAULT_POLICY); } private Object[] getCellValues(final Row row, final MissingCellPolicy policy) { final List<Object> excelColumnList = new ArrayList<Object>(); final DataFormatter dataFormat = new DataFormatter(); final int lastCellNo = row.getLastCellNum(); for (int cellNum = 0; cellNum < lastCellNo; cellNum++) { final Cell cell = row.getCell(cellNum, policy); if (cell != null) { switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_STRING: excelColumnList.add(cell.getStringCellValue()); break; case XSSFCell.CELL_TYPE_BOOLEAN: cell.setCellType(XSSFCell.CELL_TYPE_STRING); excelColumnList.add(cell.getStringCellValue()); break; case XSSFCell.CELL_TYPE_NUMERIC: excelColumnList.add(dataFormat.formatCellValue(cell)); break; case XSSFCell.CELL_TYPE_BLANK: excelColumnList.add(""); break; case XSSFCell.CELL_TYPE_FORMULA: excelColumnList.add(cell.getCellFormula()); break; case XSSFCell.CELL_TYPE_ERROR: excelColumnList.add(cell.getErrorCellValue()); break; default: excelColumnList.add(cell.toString()); } } } return excelColumnList.toArray(new Object[excelColumnList.size()]); } public String[] getCellTextValues(final Row row) { return getCellTextValues(row, DEFAULT_POLICY); } public String[] getCellTextValuesWithNullAsBlank(final Row row) { return getCellTextValues(row, Row.CREATE_NULL_AS_BLANK); } private String[] getCellTextValues(final Row row, final MissingCellPolicy policy) { final Object[] recordObjects = getCellValues(row, policy); return Arrays.asList(recordObjects).toArray(new String[recordObjects.length]); } /** * Process an excel file using ExcelWorksheetProcessor iterating through individual spreadsheets. * * @param excelFile * Excel File that needs to be processed. * @param worksheetProcessor * A Worksheet processor. * @throws InvalidFormatException * InvalidFormatException when parsing excel files. * @throws IOException * When reading the excel file. */ public void processExcelFile(final InputStream excelFile, final ExcelWorksheetProcessor worksheetProcessor) throws InvalidFormatException, IOException { final Workbook workbook = WorkbookFactory.create(excelFile); // Find Iterate index final int iterateIndex = this.iterateAllSheets ? workbook.getNumberOfSheets() : DEFAULT_SHEET_INDEX; for (int i = 0; i < iterateIndex; i++) { worksheetProcessor.process(workbook.getSheetAt(i)); } } /** * Iterate rows on a excel spreadsheet. * * @param sheet * Sheet to iterate over * @param rowMapper * A mapper to the rows on the sheet. * @param skipHeader * If the sheet's header needed to be skipped. */ public void iterateRows(final Sheet sheet, final ExcelRowMapper rowMapper, final boolean skipHeader, final boolean isPreview) { // Using for loop here instead of Iterator because iterator is skipping blank rows for (int i = 0; i <= sheet.getLastRowNum(); i++) { if (!skipHeader) { final Row row = sheet.getRow(i); if (row == null && i < 2 && !isPreview) { throw new LocalizedException("file.row.empty", new String[] { String.valueOf(i + 1), sheet.getSheetName() }); } // do not process if empty row is the last row final String[] records = getRecordsWithNullRowsAsBlank(row); if (i == sheet.getLastRowNum() && this.ignoreEmptyRows && isEmptyRecord(records)) { continue; } else { final boolean continueMapping = rowMapper.mapRow(row); if (!continueMapping) { break; } } } } } public static int getRowNum(final Row row) { if (row == null) { return -1; // Exception Scenario } return row.getRowNum() + 1; // Excel Rows are 0 based } public String[] getRecordsWithNullRowsAsBlank(final Row row) { return row == null ? new String[0] : getCellTextValuesWithNullAsBlank(row); } public void setIgnoreEmptyRows(final boolean ignoreEmptyRows) { this.ignoreEmptyRows = ignoreEmptyRows; } /** * Processes a single excel worksheet. */ public interface ExcelWorksheetProcessor { /** * Processes a worksheet. Keep in mind that using sheet iterator inside this method would undesired results. * * @param sheet * an excel spread sheet */ void process(Sheet sheet); } /** * Abstracts the mapping of an excel row. */ public interface ExcelRowMapper { /** * The method that does the actual mapping. * * @param row * an Excel based Row * @return Code that does the actual mapping should let the iterator know whether to continue mapping or exit * the process. true means mapper would be served with the next row if available. false indicates a hard * exit. */ boolean mapRow(Row row); } }