Java tutorial
/* * Copyright 2012 pcal.net * * 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 net.pcal.sqlsheet; import org.apache.poi.ss.usermodel.*; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Types; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; /** * SqlSheet implementation of java.sql.ResultSetMetaData. * * @author <a href='http://www.pcal.net'>pcal</a> * @author <a href='http://code.google.com/p/sqlsheet'>sqlsheet</a> */ public class XlsResultSetMetaData implements ResultSetMetaData { private List<String> columnNames; private final DataFormatter formatter; private XlsResultSet resultset; /** * A map to get consistently the same data type */ Map<Integer, Integer> columnTypeMap = new HashMap<Integer, Integer>(); /** * A map between the code ID and the type name */ static Map<Integer, String> columnTypeNameMap = new HashMap<Integer, String>(); /** * A map between the code ID and the type class */ static Map<Integer, String> columnTypeClassMap = new HashMap<Integer, String>(); static { columnTypeNameMap.put(Types.VARCHAR, "VARCHAR"); columnTypeNameMap.put(Types.DOUBLE, "DOUBLE"); columnTypeNameMap.put(Types.DATE, "DATE"); columnTypeClassMap.put(Types.VARCHAR, "java.lang.String.class"); columnTypeClassMap.put(Types.DOUBLE, "java.lang.Double.class"); columnTypeClassMap.put(Types.DATE, "java.sql.Date.class"); } static { } public XlsResultSetMetaData(Sheet sheet, XlsResultSet resultset, int firstSheetRowOffset) throws SQLException { if (sheet == null) throw new IllegalArgumentException(); this.resultset = resultset; Row row = sheet.getRow(firstSheetRowOffset - 1); if (row == null) { throw new SQLException("No header row in sheet"); } formatter = new DataFormatter(); columnNames = new ArrayList<String>(); for (short c = 0; c < row.getLastCellNum(); c++) { Cell cell = row.getCell(c); String columnName = formatter.formatCellValue(cell); // Is it unique in the column name set int suffix; while (columnNames.contains(columnName)) { suffix = 1; columnName += "_" + suffix; } columnNames.add(columnName); } // Data Type profiling on the whole excel file int currentRowNumber = resultset.getRow(); // A double map to back the relation between the column Id and the count of type Map<Integer, Map<Integer, Integer>> columnTypeScan = new HashMap<Integer, Map<Integer, Integer>>(); while (resultset.next()) { int typeCode; for (int columnId = 1; columnId <= getColumnCount(); columnId++) { Cell cell = resultset.getCell(columnId); if (cell != null) { int excelCellType = cell.getCellType(); switch (excelCellType) { case Cell.CELL_TYPE_BOOLEAN: typeCode = Types.VARCHAR; break; case Cell.CELL_TYPE_STRING: typeCode = Types.VARCHAR; break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { typeCode = Types.DATE; } else { typeCode = Types.DOUBLE; } break; case Cell.CELL_TYPE_BLANK: typeCode = Types.NULL; break; case Cell.CELL_TYPE_FORMULA: try { cell.getStringCellValue(); typeCode = Types.VARCHAR; } catch (Exception e) { cell.getNumericCellValue(); typeCode = Types.DOUBLE; } break; case Cell.CELL_TYPE_ERROR: throw new RuntimeException("The ExcelType ( ERROR ) is not supported - Cell (" + resultset.getRow() + "," + columnId + ")"); default: throw new RuntimeException("The ExcelType (" + excelCellType + ") is not supported - Cell (" + resultset.getRow() + "," + columnId + ")"); } } else { typeCode = Types.NULL; } Map<Integer, Integer> columnIdTypeMap = columnTypeScan.get(columnId); if (columnIdTypeMap == null) { columnIdTypeMap = new HashMap<Integer, Integer>(); columnIdTypeMap.put(typeCode, 1); columnTypeScan.put(columnId, columnIdTypeMap); } else { Integer columnIdType = columnIdTypeMap.get(typeCode); if (columnIdType == null) { columnIdTypeMap.put(typeCode, 1); } else { int count = columnIdTypeMap.get(typeCode) + 1; columnIdTypeMap.put(typeCode, count); } } } // Retrieve only one type for (Integer columnId : columnTypeScan.keySet()) { Integer numberOfVarchar = 0; Integer numberOfDouble = 0; Integer numberOfDate = 0; for (Map.Entry<Integer, Integer> columnIdTypeMap : columnTypeScan.get(columnId).entrySet()) { if (columnIdTypeMap.getKey() == Types.VARCHAR) { numberOfVarchar = columnIdTypeMap.getValue(); } else if (columnIdTypeMap.getKey() == Types.DOUBLE) { numberOfDouble = columnIdTypeMap.getValue(); } else if (columnIdTypeMap.getKey() == Types.DATE) { numberOfDate = columnIdTypeMap.getValue(); } } Integer finalColumnType = null; if (numberOfVarchar != 0) { finalColumnType = Types.VARCHAR; } else { if (numberOfDouble != 0 && numberOfDate == 0) { finalColumnType = Types.DOUBLE; } if (numberOfDouble == 0 && numberOfDate != 0) { finalColumnType = Types.DATE; } } if (finalColumnType == null) { finalColumnType = Types.VARCHAR; } columnTypeMap.put(columnId, finalColumnType); } } // Go back to the current row resultset.absolute(currentRowNumber); } public int getColumnCount() { return columnNames.size(); } public String getColumnLabel(int jdbcCol) { return columnNames.get(jdbcCol - 1); } public String getColumnName(int jdbcCol) { return columnNames.get(jdbcCol - 1); } public String getCatalogName(int arg0) throws SQLException { return null; } public String getColumnClassName(int jdbcColumn) throws SQLException { return columnTypeClassMap.get(getColumnType(jdbcColumn)); } public int getColumnDisplaySize(int arg0) { return 0; } public int getColumnType(int jdbcColumn) throws SQLException { return columnTypeMap.get(jdbcColumn); } public String getColumnTypeName(int jdbcColumn) throws SQLException { return columnTypeNameMap.get(getColumnType(jdbcColumn)); } public int getPrecision(int arg0) throws SQLException { return 0; } public int getScale(int arg0) throws SQLException { return 0; } public String getSchemaName(int arg0) throws SQLException { return null; } public String getTableName(int arg0) throws SQLException { return null; } public boolean isAutoIncrement(int arg0) throws SQLException { return false; } public boolean isCaseSensitive(int arg0) throws SQLException { return false; } public boolean isCurrency(int arg0) throws SQLException { return false; } public boolean isDefinitelyWritable(int arg0) throws SQLException { return false; } public int isNullable(int arg0) throws SQLException { return 0; } public boolean isReadOnly(int arg0) throws SQLException { return false; } public boolean isSearchable(int arg0) throws SQLException { return false; } public boolean isSigned(int arg0) throws SQLException { return false; } public boolean isWritable(int arg0) throws SQLException { return false; } public boolean isWrapperFor(Class<?> iface) throws SQLException { return false; } public <T> T unwrap(Class<T> iface) throws SQLException { return null; } }