Java tutorial
/* * Copyright 2011 Alibaba Group Holding Limited. * All rights reserved. * * 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 cn.baiweigang.qtaf.toolkit.dbunit.data.excel; import java.math.BigDecimal; import java.text.DecimalFormat; import java.text.DecimalFormatSymbols; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.List; import java.util.regex.Pattern; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.dbunit.dataset.AbstractTable; import org.dbunit.dataset.Column; import org.dbunit.dataset.DataSetException; import org.dbunit.dataset.DefaultTableMetaData; import org.dbunit.dataset.ITableMetaData; import org.dbunit.dataset.datatype.DataType; import org.dbunit.dataset.datatype.DataTypeException; import org.dbunit.dataset.excel.XlsDataSetWriter; import org.slf4j.Logger; import org.slf4j.LoggerFactory; /** * ?dbunitXlsTable * */ public class XlsTable extends AbstractTable { static final Pattern pattern = Pattern.compile("[eE]"); private static final Logger logger = LoggerFactory.getLogger(XlsTable.class); private final ITableMetaData _metaData; private final HSSFSheet _sheet; private final DecimalFormatSymbols symbols = new DecimalFormatSymbols(); public XlsTable(HSSFWorkbook workbook, String sheetName, HSSFSheet sheet) throws DataSetException { int rowCount = sheet.getLastRowNum(); if (rowCount >= 0 && sheet.getRow(0) != null) { _metaData = createMetaData(sheetName, sheet.getRow(0), workbook); } else { _metaData = new DefaultTableMetaData(sheetName, new Column[0]); } _sheet = sheet; // Needed for later "BigDecimal"/"Number" conversion symbols.setDecimalSeparator('.'); } static ITableMetaData createMetaData(String tableName, HSSFRow sampleRow, HSSFWorkbook workbook) { logger.debug("createMetaData(tableName={}, sampleRow={}) - start", tableName, sampleRow); List<Column> columnList = new ArrayList<Column>(); List<String> primaryKeyList = new ArrayList<String>(); for (int i = 0;; i++) { HSSFCell cell = sampleRow.getCell(i); if (cell == null) { break; } String columnName = cell.getRichStringCellValue().getString(); if (columnName != null) { columnName = columnName.trim(); } // Bugfix for issue ID 2818981 - if a cell has a formatting but no // name also ignore it if (columnName.length() <= 0) { logger.debug( "The column name of column # {} is empty - will skip here assuming the last column was reached", String.valueOf(i)); break; } Column column = new Column(columnName, DataType.UNKNOWN); columnList.add(column); // Unique identification key byte underline = cell.getCellStyle().getFont(workbook).getUnderline(); if (underline == 1) { primaryKeyList.add(columnName); } } Column[] columns = columnList.toArray(new Column[0]); if (!primaryKeyList.isEmpty()) { return new DefaultTableMetaData(tableName, columns, primaryKeyList.toArray(new String[primaryKeyList.size()])); } else { return new DefaultTableMetaData(tableName, columns); } } // ////////////////////////////////////////////////////////////////////////// // ITable interface public int getRowCount() { logger.debug("getRowCount() - start"); return _sheet.getLastRowNum(); } public ITableMetaData getTableMetaData() { logger.debug("getTableMetaData() - start"); return _metaData; } protected Object getDateValueFromJavaNumber(HSSFCell cell) { logger.debug("getDateValueFromJavaNumber(cell={}) - start", cell); double numericValue = cell.getNumericCellValue(); BigDecimal numericValueBd = new BigDecimal(String.valueOf(numericValue)); numericValueBd = stripTrailingZeros(numericValueBd); return new Long(numericValueBd.longValue()); // return new Long(numericValueBd.unscaledValue().longValue()); } /** * Removes all trailing zeros from the end of the given BigDecimal value * up to the decimal point. * @param value The value to be stripped * @return The value without trailing zeros */ private BigDecimal stripTrailingZeros(BigDecimal value) { if (value.scale() <= 0) { return value; } String valueAsString = String.valueOf(value); int idx = valueAsString.indexOf("."); if (idx == -1) { return value; } for (int i = valueAsString.length() - 1; i > idx; i--) { if (valueAsString.charAt(i) == '0') { valueAsString = valueAsString.substring(0, i); } else if (valueAsString.charAt(i) == '.') { valueAsString = valueAsString.substring(0, i); // Stop when decimal point is reached break; } else { break; } } BigDecimal result = new BigDecimal(valueAsString); return result; } protected BigDecimal getNumericValue(HSSFCell cell) { logger.debug("getNumericValue(cell={}) - start", cell); String formatString = cell.getCellStyle().getDataFormatString(); String resultString = null; double cellValue = cell.getNumericCellValue(); if ((formatString != null)) { if (!formatString.equals("General") && !formatString.equals("@")) { logger.debug("formatString={}", formatString); DecimalFormat nf = new DecimalFormat(formatString, symbols); resultString = nf.format(cellValue); } } BigDecimal result; if (resultString != null) { try { result = new BigDecimal(resultString); } catch (NumberFormatException e) { logger.debug("Exception occurred while trying create a BigDecimal. value={}", resultString); // Probably was not a BigDecimal format retrieved from the // excel. Some // date formats are not yet recognized by HSSF as DateFormats so // that // we could get here. result = toBigDecimal(cellValue); } } else { result = toBigDecimal(cellValue); } return result; } /** * @param cellValue * @return * @since 2.4.6 */ private BigDecimal toBigDecimal(double cellValue) { String resultString = String.valueOf(cellValue); // Treatment for scientific notation (for accurate to five decimal places) if (pattern.matcher(resultString).find()) { DecimalFormat format = new DecimalFormat("#####.#####"); resultString = format.format(cellValue); } // To ensure that intergral numbers do not have decimal point and // trailing zero // (to restore backward compatibility and provide a string // representation consistent with Excel) if (resultString.endsWith(".0")) { resultString = resultString.substring(0, resultString.length() - 2); } BigDecimal result = new BigDecimal(resultString); return result; } public Object getValue(int row, String column) throws DataSetException { if (logger.isDebugEnabled()) logger.debug("getValue(row={}, columnName={}) - start", Integer.toString(row), column); assertValidRowIndex(row); int columnIndex = getColumnIndex(column); HSSFCell cell = _sheet.getRow(row + 1).getCell(columnIndex); if (cell == null) { return null; } int type = cell.getCellType(); switch (type) { case HSSFCell.CELL_TYPE_NUMERIC: HSSFCellStyle style = cell.getCellStyle(); if (HSSFDateUtil.isCellDateFormatted(cell)) { return getDateValue(cell); } else if (XlsDataSetWriter.DATE_FORMAT_AS_NUMBER_DBUNIT.equals(style.getDataFormatString())) { // The special dbunit date format return getDateValueFromJavaNumber(cell); } else { return getNumericValue(cell); } case HSSFCell.CELL_TYPE_STRING: return cell.getRichStringCellValue().getString(); case HSSFCell.CELL_TYPE_FORMULA: throw new DataTypeException("Formula not supported at row=" + row + ", column=" + column); case HSSFCell.CELL_TYPE_BLANK: return null; case HSSFCell.CELL_TYPE_BOOLEAN: return cell.getBooleanCellValue() ? Boolean.TRUE : Boolean.FALSE; case HSSFCell.CELL_TYPE_ERROR: throw new DataTypeException("Error at row=" + row + ", column=" + column); default: throw new DataTypeException("Unsupported type at row=" + row + ", column=" + column); } } protected Object getDateValue(HSSFCell cell) { // Simplification and the use of Chinese date format return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(cell.getDateCellValue()); } }