Java tutorial
/* * Copyright 2004-2014 the Seasar Foundation and the Others. * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, * either express or implied. See the License for the specific language * governing permissions and limitations under the License. */ package org.seasar.extension.dataset.impl; import java.io.File; import java.io.IOException; import java.io.InputStream; import java.math.BigDecimal; import org.apache.poi.hssf.record.RowRecord; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFDataFormat; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.seasar.extension.dataset.ColumnType; import org.seasar.extension.dataset.DataReader; import org.seasar.extension.dataset.DataRow; import org.seasar.extension.dataset.DataSet; import org.seasar.extension.dataset.DataSetConstants; import org.seasar.extension.dataset.DataTable; import org.seasar.extension.dataset.types.ColumnTypes; import org.seasar.framework.exception.IORuntimeException; import org.seasar.framework.util.Base64Util; import org.seasar.framework.util.FileInputStreamUtil; import org.seasar.framework.util.ResourceUtil; import org.seasar.framework.util.StringUtil; import org.seasar.framework.util.TimestampConversionUtil; /** * Excel? {@link DataReader}?? * * @author higa * @author manhole * @author azusa */ public class XlsReader implements DataReader, DataSetConstants { /** * ?? */ protected DataSet dataSet; /** * ?? */ protected HSSFWorkbook workbook; /** * ?? */ protected HSSFDataFormat dataFormat; /** * ????? */ protected boolean trimString = true; /** * {@link XlsReader}???? * * @param path * */ public XlsReader(String path) { this(path, true); } /** * {@link XlsReader}???? * * @param path * * @param trimString * ????? */ public XlsReader(String path, boolean trimString) { this(ResourceUtil.getResourceAsStream(path), trimString); } /** * {@link XlsReader}???? * * @param dirName * ?? * @param fileName * ?? */ public XlsReader(String dirName, String fileName) { this(dirName, fileName, true); } /** * {@link XlsReader}???? * * @param dirName * ?? * @param fileName * ?? * @param trimString * ????? */ public XlsReader(String dirName, String fileName, boolean trimString) { this(ResourceUtil.getResourceAsFile(dirName), fileName, trimString); } /** * {@link XlsReader}???? * * @param dir * * @param fileName * ?? */ public XlsReader(File dir, String fileName) { this(dir, fileName, true); } /** * {@link XlsReader}???? * * @param dir * * @param fileName * ?? * @param trimString * ????? */ public XlsReader(File dir, String fileName, boolean trimString) { this(new File(dir, fileName), trimString); } /** * {@link XlsReader}???? * * @param file * */ public XlsReader(File file) { this(file, true); } /** * {@link XlsReader}???? * * @param file * * @param trimString * ????? */ public XlsReader(File file, boolean trimString) { this(FileInputStreamUtil.create(file), trimString); } /** * {@link XlsReader}???? * * @param in * */ public XlsReader(InputStream in) { this(in, true); } /** * {@link XlsReader}???? * * @param in * * @param trimString * ????? */ public XlsReader(InputStream in, boolean trimString) { this.trimString = trimString; try { workbook = new HSSFWorkbook(in); } catch (IOException ex) { throw new IORuntimeException(ex); } dataFormat = workbook.createDataFormat(); dataSet = new DataSetImpl(); for (int i = 0; i < workbook.getNumberOfSheets(); ++i) { createTable(workbook.getSheetName(i), workbook.getSheetAt(i)); } } public DataSet read() { return dataSet; } /** * ???? * * @param sheetName * ?? * @param sheet * * @return */ protected DataTable createTable(String sheetName, HSSFSheet sheet) { DataTable table = dataSet.addTable(sheetName); int rowCount = sheet.getLastRowNum(); if (rowCount > 0) { setupColumns(table, sheet); setupRows(table, sheet); } else if (rowCount == 0) { setupColumns(table, sheet); } return table; } /** * ???? * * @param table * * @param sheet * */ protected void setupColumns(DataTable table, HSSFSheet sheet) { HSSFRow nameRow = sheet.getRow(0); HSSFRow valueRow = sheet.getRow(1); for (int i = 0; i <= Short.MAX_VALUE; ++i) { HSSFCell nameCell = nameRow.getCell((short) i); if (nameCell == null) { break; } String columnName = nameCell.getRichStringCellValue().getString(); if (columnName.length() == 0) { break; } HSSFCell valueCell = null; if (valueRow != null) { for (int j = 1; j <= sheet.getLastRowNum(); j++) { valueCell = sheet.getRow(j).getCell((short) i); if (valueCell != null && !StringUtil.isEmpty(valueCell.toString())) { break; } } } if (valueCell != null) { table.addColumn(columnName, getColumnType(valueCell)); } else { table.addColumn(columnName); } } } /** * ???? * * @param table * * @param sheet * */ protected void setupRows(DataTable table, HSSFSheet sheet) { for (int i = 1; i <= RowRecord.MAX_ROW_NUMBER; ++i) { HSSFRow row = sheet.getRow(i); if (row == null) { break; } setupRow(table, row); } } /** * ??? * * @param table * * @param row * */ protected void setupRow(DataTable table, HSSFRow row) { DataRow dataRow = table.addRow(); for (int i = 0; i < table.getColumnSize(); ++i) { HSSFCell cell = row.getCell((short) i); Object value = getValue(cell); dataRow.setValue(i, value); } } /** * ?Base64??????????? * * @param cell * * @return ?Base64???????? */ public boolean isCellBase64Formatted(HSSFCell cell) { HSSFCellStyle cs = cell.getCellStyle(); short dfNum = cs.getDataFormat(); return BASE64_FORMAT.equals(dataFormat.getFormat(dfNum)); } /** * ????????? * * @param cell * * @return ?????? */ public boolean isCellDateFormatted(HSSFCell cell) { HSSFCellStyle cs = cell.getCellStyle(); short dfNum = cs.getDataFormat(); String format = dataFormat.getFormat(dfNum); if (StringUtil.isEmpty(format)) { return false; } if (format.indexOf('/') > 0 || format.indexOf('y') > 0 || format.indexOf('m') > 0 || format.indexOf('d') > 0) { return true; } return false; } /** * ???? * * @param cell * * @return ? */ public Object getValue(HSSFCell cell) { if (cell == null) { return null; } switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: if (isCellDateFormatted(cell)) { return TimestampConversionUtil.toTimestamp(cell.getDateCellValue()); } final double numericCellValue = cell.getNumericCellValue(); if (isInt(numericCellValue)) { return new BigDecimal((int) numericCellValue); } return new BigDecimal(Double.toString(numericCellValue)); case HSSFCell.CELL_TYPE_STRING: String s = cell.getRichStringCellValue().getString(); if (s != null) { s = StringUtil.rtrim(s); if (!trimString && s.length() > 1 && s.startsWith("\"") && s.endsWith("\"")) { s = s.substring(1, s.length() - 1); } } if ("".equals(s)) { s = null; } if (isCellBase64Formatted(cell)) { return Base64Util.decode(s); } return s; case HSSFCell.CELL_TYPE_BOOLEAN: boolean b = cell.getBooleanCellValue(); return Boolean.valueOf(b); default: return null; } } /** * ???? * * @param cell * * @return ? */ protected ColumnType getColumnType(HSSFCell cell) { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: if (isCellDateFormatted(cell)) { return ColumnTypes.TIMESTAMP; } return ColumnTypes.BIGDECIMAL; case HSSFCell.CELL_TYPE_BOOLEAN: return ColumnTypes.BOOLEAN; case HSSFCell.CELL_TYPE_STRING: if (isCellBase64Formatted(cell)) { return ColumnTypes.BINARY; } else if (trimString) { return ColumnTypes.STRING; } else { return ColumnTypes.NOT_TRIM_STRING; } default: return ColumnTypes.STRING; } } /** * ??????? * * @param numericCellValue * numeric? * @return ???? */ protected boolean isInt(final double numericCellValue) { return ((int) numericCellValue) == numericCellValue; } }