Java tutorial
/*$Id: PoiExcelImpl.java 16242 2011-02-04 09:02:44Z jens $*/ /* **************************************************************************** * * * (c) Copyright 2011 Norsk kulturrd * * * * 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.util.ArrayList; import java.util.Date; import java.util.Iterator; import java.util.List; import java.util.Map; import no.abmu.util.test.Assert; import org.apache.log4j.Logger; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRichTextString; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; /** * <code>PoiExcelImpl</code> implements <code>PoiExcel</code> is a general * class for processing of Excel workbook and Excel sheets. * * @author Jens Vindvad, Jens.Vindvad@kulturrad.no * @author $Author: jens $ * @version $Rev: 16242 $ * $Date: 2011-02-04 10:02:44 +0100 (Fri, 04 Feb 2011) $ * @since 2011-01-30 * copyright Norsk kulturrd */ public class PoiExcelImpl implements IExcel { private final int DEFAULT_FIRST_KEY_ROW = 0; private final int DEFAULT_FIRST_DATA_ROW = 1; private final Logger logger = Logger.getLogger(PoiExcelImpl.class); /** * Represents the Excel document. */ private HSSFWorkbook workBook; /** * The the active sheet to handle. */ private HSSFSheet activeSheet; /** * The name of the active sheet to handle. */ private String activeSheetName; /** * The index of the row where the data starts. * * The rows is 0-based, so first row in a sheet has index 0. * The default firstDataRow is set to 1, eg. the second row in a sheet. */ private int firstDataRowIdx = DEFAULT_FIRST_DATA_ROW; /** * The index of the row having the key values (ie column names). * * The rows is 0-based, so first row in a sheet has index 0. * The default keyRow is set to 0, eg. the first row in a sheet. */ private int keyRowIdx = DEFAULT_FIRST_KEY_ROW; /** * The index of the current working data row. * * The rows is 0-based, so first row in a sheet has index 0. * The default currentDataRow is to 1, same value as firstDataRow, * eg. the second row in a sheet. */ private int currentDataRowIdx = DEFAULT_FIRST_DATA_ROW; /** * Construct the PoiExcel object based on an existing workbook, * and set first sheet in the book as sheet to handle. * * @param workBook Poi based ExcelBook. * @throws IllegalArgumentException if workbook is <code>null</code>. * @throws IllegalStateException if workbook doesn't have any sheets, eg. empty. */ public PoiExcelImpl(HSSFWorkbook workBook) { Assert.checkRequiredArgument("workBook", workBook); int numberOfSheets = workBook.getNumberOfSheets(); if (numberOfSheets < 1) { String errorMessage = "The supplied workBook doesn't have any sheets."; logger.error(errorMessage); throw new IllegalStateException(errorMessage); } this.workBook = workBook; activeSheet = workBook.getSheetAt(0); activeSheetName = workBook.getSheetName(0); } /** * Fills the active sheet with values from <code>Iterator<Map<String,Object>></code>. * <p> * The column names (or key names) in key the row is used to * pick values from <code>mapIterator</code>. * <p> * * @param mapIterator map iterator with reference to data values to fill into active sheet. * @throws IllegalArgumentException if mapIterator is set to <code>null</code>. */ public void fillActiveSheetWithValues(Iterator<Map<String, Object>> mapIterator) { Assert.checkRequiredArgument("mapIterator", mapIterator); List<String> columnKeyNames = getKeyNames(); reset(); while (mapIterator.hasNext()) { fillRowByColumnKeyNames(columnKeyNames, mapIterator.next()); } } /** * Sets the index of the first row having data. * <p> * Please be aware that this will reset the counters - ie move the * current row to firstDataRowIdx. * <p> * The rows is 0-based, so first row in a sheet has index 0. * <p> * @param firstDataRowIdx 0-based row index which specify first data row. */ public void setFirstDataRowIdx(int firstDataRowIdx) { this.firstDataRowIdx = firstDataRowIdx; reset(); } /** * Sets the row index of the key row. * * @param keyRowIdx 0-based row index which specify the key row. */ public void setKeyRowIdx(int keyRowIdx) { this.keyRowIdx = keyRowIdx; } /** * Sets active sheet to handle processing. * * @param sheetName name of sheet to set as active sheet. * @throws IllegalArgumentException if sheet name to set is <code>null</code>. * @throws IllegalArgumentException if sheet name to set * doesn't exist in workbook. */ public void setActiveSheet(String sheetName) { Assert.checkRequiredArgument("sheetName", sheetName); HSSFSheet sheet = workBook.getSheet(sheetName); if (sheet == null) { String errorMessage = "Can't find sheet with name=[" + sheetName + "]."; logger.error(errorMessage); throw new IllegalArgumentException(errorMessage); } activeSheet = sheet; activeSheetName = sheetName; reset(); } /** * Returns name of the active sheet. * * @return name of active sheet. */ public String getActiveSheetName() { return activeSheetName; } /** * Return true if workbook has a sheet with specified sheet name. * * @param sheetName to ask for. * @return true if workbook has sheet with specified sheetName, else return false. * @throws IllegalArgumentException if parameter sheetName is null. */ public Boolean hasSheetName(String sheetName) { Assert.checkRequiredArgument("sheetName", sheetName); // Should return 0 or bigger if sheet name exist in workbook. // Should return -1 if sheet name doesn't exist in workbook. int index = workBook.getSheetIndex(sheetName); return (index >= 0); } /** * Moves the row pointer to the first data row. */ public void reset() { currentDataRowIdx = firstDataRowIdx; } private void fillRowByColumnKeyNames(List<String> columnKeyNames, Map<String, Object> map) { HSSFRow row = getOrCreateRow(currentDataRowIdx++); int columnNumber = 0; for (String columnKeyName : columnKeyNames) { HSSFCell cell = getOrCreateCell(row, columnNumber++); Object object = map.get(columnKeyName); if (object == null) { // Do nothing. continue; } else if (object instanceof Long) { Long longValue = (Long) object; if (columnKeyName.equals("id")) { HSSFRichTextString hssfRichTextString = new HSSFRichTextString(Long.toString(longValue)); cell.setCellValue(hssfRichTextString); } else { cell.setCellValue(longValue); } } else if (object instanceof Integer) { Integer integerValue = (Integer) object; cell.setCellValue(integerValue); } else if (object instanceof Float) { Float floatValue = (Float) object; cell.setCellValue(floatValue); } else if (object instanceof Double) { Double doubleValue = (Double) object; cell.setCellValue(doubleValue); } else if (object instanceof Date) { Date dateValue = (Date) object; cell.setCellValue(dateValue); } else if (object instanceof String) { String stringValue = (String) object; HSSFRichTextString hssfRichTextString = new HSSFRichTextString(stringValue); cell.setCellValue(hssfRichTextString); } else if (object instanceof Boolean) { Boolean booleanValue = (Boolean) object; cell.setCellValue(booleanValue); } else { String errorMessage = "For column header '" + columnKeyName + "' data type '" + object.getClass().getName() + "' is unknown."; throw new IllegalArgumentException(errorMessage); } } } private List<String> getKeyNames() { HSSFRow row = activeSheet.getRow(keyRowIdx); if (row == null) { String errorMessage = "The Excel sheet '" + getActiveSheetName() + "' does not have row '" + keyRowIdx + "'"; logger.error(errorMessage); throw new IllegalStateException(errorMessage); } List<String> keyNames = new ArrayList<String>(); for (int columnNumber = 0; columnNumber < row.getLastCellNum(); columnNumber++) { String keyName = getStringValue(row, columnNumber); if (keyName == null) { keyNames.add(""); } else { keyNames.add(keyName.trim()); } } return keyNames; } /** * Returns the value of the specified column as a String. * * @param row * @param columnNumber * @return */ private String getStringValue(HSSFRow row, int columnNumber) { Assert.checkRequiredArgument("row", row); Assert.checkRequiredArgument("columnNumber", columnNumber); HSSFCell cell = row.getCell(columnNumber); if (cell == null) { return null; } switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_BLANK: return null; case HSSFCell.CELL_TYPE_NUMERIC: /* For now - convert to long */ return Long.toString(Double.valueOf(cell.getNumericCellValue()).longValue()); case HSSFCell.CELL_TYPE_STRING: HSSFRichTextString richTextString = cell.getRichStringCellValue(); return richTextString.toString(); default: return null; } } private HSSFRow getOrCreateRow(int rowNumber) { HSSFRow row = activeSheet.getRow(rowNumber); if (row == null) { return activeSheet.createRow(rowNumber); } return row; } private HSSFCell getOrCreateCell(HSSFRow row, int cellNumber) { Assert.checkRequiredArgument("row", row); HSSFCell cell = row.getCell(cellNumber); if (cell == null) { return row.createCell(cellNumber); } return cell; } }