/*
public static void copy(MultipartFile file,String savePath,String newname) throws Exception {
try {
File targetFile = new File(savePath,newname);
if (!targetFile.exists()) {
//判断文件夹是否存在，不存在就创建
targetFile.mkdirs();
}
file.transferTo(targetFile);
} catch (Exception e) {
e.printStackTrace();
}
}
*/
private static String getCellValue(HSSFCell cell) {
DecimalFormat df = new DecimalFormat("#");
String cellValue = null;
if (cell == null) return null;
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
cellValue = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
break;
}
cellValue = df.format(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_STRING:
cellValue = String.valueOf(cell.getStringCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA:
cellValue = String.valueOf(cell.getCellFormula());
break;
case HSSFCell.CELL_TYPE_BLANK:
cellValue = null;
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_ERROR:
cellValue = String.valueOf(cell.getErrorCellValue());
break;
}
if (cellValue != null && cellValue.trim().length() <= 0) {
cellValue = null;
}
return cellValue;
}
// returns the data from a cell
public String getCellData(String sheetName, int colNum, int rowNum) {
try {
if (rowNum <= 0) return "";
int index = workBook.getSheetIndex(sheetName);
if (index == -1) return "";
sheet = workBook.getSheetAt(index);
row = sheet.getRow(rowNum - 1);
if (row == null) return "";
cell = row.getCell(colNum);
if (cell == null) return "";
if (cell.getCellType() == Cell.CELL_TYPE_STRING) return cell.getStringCellValue();
else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC
|| cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
String cellText = String.valueOf(cell.getNumericCellValue());
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// format in form of M/D/YY
double d = cell.getNumericCellValue();
Calendar cal = Calendar.getInstance();
cal.setTime(HSSFDateUtil.getJavaDate(d));
cellText = (String.valueOf(cal.get(Calendar.YEAR))).substring(2);
cellText =
cal.get(Calendar.MONTH) + 1 + "/" + cal.get(Calendar.DAY_OF_MONTH) + "/" + cellText;
// System.out.println(cellText);
}
return cellText;
} else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) return "";
else return String.valueOf(cell.getBooleanCellValue());
} catch (Exception e) {
e.printStackTrace();
return "row " + rowNum + " or column " + colNum + " does not exist in xls";
}
}
/**
* Возвращает содержимое ячейки в зависимости от ее типа.
*
* @param cell ячейка документа Excel.
* @return содержимое ячейки.
*/
public static Object getCellValue(final HSSFCell cell) {
if (cell == null) return null;
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
{
return HSSFDateUtil.isCellDateFormatted(cell)
? cell.getDateCellValue()
: cell.getNumericCellValue();
}
case HSSFCell.CELL_TYPE_STRING:
{
final HSSFRichTextString rt = cell.getRichStringCellValue();
return rt.numFormattingRuns() == 0 ? rt.getString() : rt;
}
case HSSFCell.CELL_TYPE_FORMULA:
return cell.getCellFormula();
case HSSFCell.CELL_TYPE_BLANK:
return null;
case HSSFCell.CELL_TYPE_BOOLEAN:
return cell.getBooleanCellValue();
case HSSFCell.CELL_TYPE_ERROR:
return "#ERR" + cell.getErrorCellValue();
default:
return null;
}
}
private static String getCellString(HSSFCell cell) {
String strRe = "";
try {
int iCellType = cell.getCellType();
switch (iCellType) {
case HSSFCell.CELL_TYPE_NUMERIC:
{
boolean b = HSSFDateUtil.isCellDateFormatted(cell);
if (b) {
cell.getDateCellValue();
String strDate = sdf.format(cell.getDateCellValue());
return strDate.trim();
}
double db = cell.getNumericCellValue();
if (db == 0) {
strRe = "0";
}
// 此处就是采用DecimalFormat 格式化的方式来解决的。
java.text.DecimalFormat formatter = new java.text.DecimalFormat("###########");
strRe = formatter.format(cell.getNumericCellValue());
break;
}
case HSSFCell.CELL_TYPE_STRING:
strRe = cell.getRichStringCellValue().getString();
break;
case HSSFCell.CELL_TYPE_BLANK:
default:
break;
}
return strRe.trim();
} catch (Exception e) {
return "";
}
}
/**
* 根据单元格类型，得到string的值，如果为null，则返回空
*
* @param cell
* @return
*/
public static String getCellStringValue(HSSFCell cell) {
if (cell == null) return "";
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_BLANK:
return "";
case HSSFCell.CELL_TYPE_BOOLEAN:
return String.valueOf(cell.getBooleanCellValue());
case HSSFCell.CELL_TYPE_ERROR:
return String.valueOf(cell.getErrorCellValue());
case HSSFCell.CELL_TYPE_FORMULA:
try {
return cell.getRichStringCellValue().getString().trim();
} catch (Exception e) {
}
case HSSFCell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
return DateProcess.toString(cell.getDateCellValue(), "yyyy-MM-dd");
}
double dv = cell.getNumericCellValue();
if (isInteger(dv)) {
return String.valueOf((long) dv);
} else {
return String.valueOf(dv);
}
case HSSFCell.CELL_TYPE_STRING:
return cell.getRichStringCellValue().getString().trim();
default:
return "";
}
}
public static String Season(String d) {
Date date = HSSFDateUtil.getJavaDate(Double.valueOf(d));
Calendar calendar = Calendar.getInstance();
calendar.setTime(date);
String result = "";
switch (calendar.get(Calendar.MONDAY)) {
case 12:
case 1:
case 2:
result = "Spring";
break;
case 3:
case 4:
case 5:
result = "Summer";
break;
case 6:
case 7:
case 8:
result = "Autumn";
break;
case 9:
case 10:
case 11:
result = "Winter";
break;
}
return result;
}
public static String toString(HSSFCell cell) {
if (cell == null) return "";
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_BLANK:
return "";
case HSSFCell.CELL_TYPE_BOOLEAN:
return cell.getBooleanCellValue() ? "TRUE" : "FALSE";
case HSSFCell.CELL_TYPE_ERROR:
// return ErrorEval.getText((( BoolErrRecord )
// cell.getCellValueRecord()).getErrorValue());
return "错误";
case HSSFCell.CELL_TYPE_FORMULA:
return cell.getCellFormula().replace("\"", "");
case HSSFCell.CELL_TYPE_NUMERIC:
// TODO apply the dataformat for this cell
if (HSSFDateUtil.isCellDateFormatted(cell)) {
DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
return sdf.format(cell.getDateCellValue());
} else {
// return cell.getNumericCellValue() + "";
return df.format(cell.getNumericCellValue());
}
case HSSFCell.CELL_TYPE_STRING:
return cell.getRichStringCellValue().getString().replace("\"", "");
default:
return "未知类型:" + cell.getCellType();
}
}
public static Object resolveCellValue(
Cell cell, String emptyMarker, String nullMarker, Converter<String, ?> stringPreprocessor) {
if (cell == null) return null;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
return convertString(cell, emptyMarker, nullMarker, stringPreprocessor);
case Cell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
return cell.getDateCellValue();
} else {
double numericCellValue = cell.getNumericCellValue();
if (MathUtil.isIntegralValue(numericCellValue))
return ((Double) numericCellValue).longValue();
return numericCellValue;
}
case Cell.CELL_TYPE_BOOLEAN:
return cell.getBooleanCellValue();
case Cell.CELL_TYPE_BLANK:
case Cell.CELL_TYPE_ERROR:
return cell.getRichStringCellValue().getString();
case Cell.CELL_TYPE_FORMULA:
FormulaEvaluator evaluator =
cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
CellValue cellValue = evaluator.evaluate(cell);
switch (cellValue.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
return convertString(cellValue, emptyMarker, stringPreprocessor);
case HSSFCell.CELL_TYPE_NUMERIC:
return cellValue.getNumberValue();
case Cell.CELL_TYPE_BOOLEAN:
return cellValue.getBooleanValue();
case HSSFCell.CELL_TYPE_BLANK:
case HSSFCell.CELL_TYPE_ERROR:
return null;
default:
throw new IllegalStateException("Unexpected cell type: " + cellValue.getCellType());
// CELL_TYPE_FORMULA is not supposed to be encountered here
}
default:
throw new ConfigurationError("Not a supported cell type: " + cell.getCellType());
}
}
/**
* 读取 Excel文件内容
*
* @param excel_name
* @return
* @throws Exception
*/
public static List<String[]> readExcel(String excel_name) throws Exception {
// 结果集
List<String[]> list = new ArrayList<String[]>();
HSSFWorkbook hssfworkbook = new HSSFWorkbook(new FileInputStream(excel_name));
// 遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
HSSFSheet hssfsheet = hssfworkbook.getSheetAt(0);
// 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数
for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
HSSFRow hssfrow = hssfsheet.getRow(j);
if (hssfrow != null) {
int col = hssfrow.getPhysicalNumberOfCells();
// 单行数据
String[] arrayString = new String[col];
for (int i = 0; i < col; i++) {
HSSFCell cell = hssfrow.getCell(i);
if (cell == null) {
arrayString[i] = "";
} else if (cell.getCellType() == 0) {
// arrayString[i] = new Double(cell.getNumericCellValue()).toString();
if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date d = cell.getDateCellValue();
// DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
arrayString[i] = formater.format(d);
} else {
arrayString[i] = new BigDecimal(cell.getNumericCellValue()).longValue() + "";
}
}
} else { // 如果EXCEL表格中的数据类型为字符串型
arrayString[i] = cell.getStringCellValue().trim();
}
}
list.add(arrayString);
}
}
return list;
}
/**
* 取对应单元格类型的值
*
* @param c 列数
* @return 单元格的值
*/
private String getCellValue(Cell c) {
String o = null;
switch (c.getCellType()) {
case Cell.CELL_TYPE_BLANK:
o = "";
break;
case Cell.CELL_TYPE_BOOLEAN:
o = String.valueOf(c.getBooleanCellValue());
break;
case CELL_TYPE_FORMULA:
o = String.valueOf(c.getCellFormula());
break;
case Cell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(c)) { // 处理日期格式、时间格式
SimpleDateFormat sdf;
if (c.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
sdf = new SimpleDateFormat("HH:mm");
} else { // 日期
sdf = new SimpleDateFormat("yyyy-MM-dd");
}
Date date = c.getDateCellValue();
o = sdf.format(date).equals("1899-12-31") ? "" : sdf.format(date);
} else if (c.getCellStyle().getDataFormat() == 58) {
// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
double value = c.getNumericCellValue();
Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
o = sdf.format(date);
}
break;
case Cell.CELL_TYPE_STRING:
o = c.getStringCellValue();
break;
default:
o = null;
break;
}
return o;
}
@Override
public Object getValueAt(int rowIndex, int columnIndex) {
Cell cell;
if (config != null) {
Row row = sheet.getRow(rowIndex + config.getRowOffset());
if (row == null) {
return null;
}
cell = row.getCell(columnIndex + config.getColumnOffset());
} else {
Row row = sheet.getRow(rowIndex);
if (row == null) {
return null;
}
cell = row.getCell(columnIndex);
}
if (cell == null) {
return null;
}
if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
return cell.getBooleanCellValue();
} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
return cell.getStringCellValue();
} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
if (HSSFDateUtil.isCellDateFormatted(cell)) {
return cell.getDateCellValue();
} else {
return cell.getNumericCellValue();
}
} else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
return cell.getErrorCellValue();
} else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
return cell.getNumericCellValue();
} else {
// last resort, should not come to this
// maybe return null?
return "";
}
}
private Object getCellValue(Cell cell) {
if (cell == null) return null;
int type = cell.getCellType();
if (type == Cell.CELL_TYPE_FORMULA) {
type = cell.getCachedFormulaResultType();
}
Object value = null;
switch (type) {
case Cell.CELL_TYPE_BLANK:
case Cell.CELL_TYPE_ERROR:
value = null;
break;
case Cell.CELL_TYPE_NUMERIC:
boolean isDate = HSSFDateUtil.isCellDateFormatted(cell);
if (isDate) {
if (useStringDates) {
value = dataFormatter.formatCellValue(cell);
} else {
value = getDateValue(cell);
}
} else {
double dv = cell.getNumericCellValue();
value = Double.valueOf(dv);
}
break;
default:
String svalue = cell.getStringCellValue();
if (isNullString(svalue)) {
value = null;
} else {
value = svalue;
}
}
return value;
}
@Override
public void processRecord(Record record) {
switch (record.getSid()) {
case BoundSheetRecord.sid:
BoundSheetRecord boundSheetRecord = (BoundSheetRecord) record;
parsedDataMap.put(boundSheetRecord.getSheetname(), new ArrayList<List<Object>>());
boundSheetRecords.add(boundSheetRecord);
missingRows.put(boundSheetRecord.getSheetname(), new ArrayList<Integer>());
missingCells.put(boundSheetRecord.getSheetname(), new ArrayList<int[]>());
break;
case BOFRecord.sid:
BOFRecord bofRecord = (BOFRecord) record;
bofRecordType = bofRecord.getType();
switch (bofRecordType) {
case BOFRecord.TYPE_WORKBOOK:
LOG.trace("loading excel data information.");
break;
case BOFRecord.TYPE_WORKSHEET:
sheetIndex += 1;
sheetName = boundSheetRecords.get(sheetIndex).getSheetname();
currentSheet = parsedDataMap.get(sheetName);
originalRowIndex = 0;
maxColumnlength = -1;
currentRow = new ArrayList<Object>();
break;
}
break;
case RowRecord.sid:
RowRecord rowRecord = (RowRecord) record;
int firstColumn = rowRecord.getFirstCol();
int lastColumn = rowRecord.getLastCol();
int length = lastColumn - firstColumn;
if (length > maxColumnlength) {
maxColumnlength = length;
}
break;
case BlankRecord.sid:
BlankRecord blankRecord = (BlankRecord) record;
missingCells.get(sheetName).add(new int[] {blankRecord.getColumn(), blankRecord.getRow()});
currentRow.add(null);
break;
case BoolErrRecord.sid:
BoolErrRecord boolErrRecord = (BoolErrRecord) record;
if (boolErrRecord.isBoolean()) {
currentRow.add(boolErrRecord.getErrorValue() == BOOLEAN_CELL_TRUE_FLAG);
} else if (boolErrRecord.isError()) {
LOG.warn(boolErrRecord);
}
break;
case FormulaRecord.sid:
FormulaRecord formulaRecord = (FormulaRecord) record;
LOG.trace(
"formulaRecord:[" + formulaRecord.getColumn() + "," + formulaRecord.getRow() + "]");
break;
case StringRecord.sid:
StringRecord stringRecord = (StringRecord) record;
currentRow.add(stringRecord.getString());
break;
case LabelRecord.sid:
LabelRecord labelRecord = (LabelRecord) record;
currentRow.add(labelRecord.getValue());
break;
case LabelSSTRecord.sid:
LabelSSTRecord labelSSTRecord = (LabelSSTRecord) record;
currentRow.add(
sheetRecordCollectingListener
.getSSTRecord()
.getString(labelSSTRecord.getSSTIndex())
.getString());
break;
case NoteRecord.sid:
NoteRecord notegRecord = (NoteRecord) record;
LOG.trace("formulaRecord:[" + notegRecord.getColumn() + "," + notegRecord.getRow() + "]");
break;
case NumberRecord.sid:
NumberRecord numberRecord = (NumberRecord) record;
double numberValue = numberRecord.getValue();
int formatIndex = formatTrackingHSSFListener.getFormatIndex(numberRecord);
if (HSSFDateUtil.isInternalDateFormat(formatIndex)) {
currentRow.add(HSSFDateUtil.getJavaDate(numberValue));
} else {
currentRow.add(numberValue);
}
break;
case RKRecord.sid:
RKRecord pkRecord = (RKRecord) record;
currentRow.add(pkRecord.getRKNumber());
break;
case EOFRecord.sid:
switch (bofRecordType) {
case BOFRecord.TYPE_WORKBOOK:
LOG.trace("loading excel data information complete.");
break;
case BOFRecord.TYPE_VB_MODULE:
break;
case BOFRecord.TYPE_WORKSHEET:
int size = currentSheet.size();
if (size > 0) {
if (currentSheet.get(size - 1).isEmpty()) {
currentSheet.remove(size - 1);
}
}
break;
case BOFRecord.TYPE_CHART:
break;
case BOFRecord.TYPE_EXCEL_4_MACRO:
break;
case BOFRecord.TYPE_WORKSPACE_FILE:
break;
}
break;
default:
if (record instanceof LastCellOfRowDummyRecord) {
// remove empty row.
if (!currentRow.isEmpty()) {
int nullSize = 0;
for (Object obj : currentRow) {
if (obj == null) {
nullSize += 1;
}
}
// remove the row of all member is null
if (nullSize == currentRow.size()) {
currentRow.clear();
missingRows.get(sheetName).add(originalRowIndex);
} else {
LastCellOfRowDummyRecord lastCellOfRowDummyRecord = (LastCellOfRowDummyRecord) record;
if (lastCellOfRowDummyRecord.getLastColumnNumber() > -1) {
for (int i = currentRow.size(); i < maxColumnlength; i += 1) {
missingCells.get(sheetName).add(new int[] {i, lastCellOfRowDummyRecord.getRow()});
currentRow.add(null);
}
}
currentSheet.add(currentRow);
currentRow = new ArrayList<Object>();
}
}
originalRowIndex += 1;
} else if (record instanceof MissingCellDummyRecord) {
MissingCellDummyRecord missingCellDummyRecord = (MissingCellDummyRecord) record;
currentRow.add(null);
missingCells
.get(sheetName)
.add(new int[] {missingCellDummyRecord.getColumn(), missingCellDummyRecord.getRow()});
} else if (record instanceof MissingRowDummyRecord) {
MissingRowDummyRecord missingRowDummyRecord = (MissingRowDummyRecord) record;
missingRows.get(sheetName).add(missingRowDummyRecord.getRowNumber());
}
break;
}
}
/**
* create a FeatureSource with the specified Query
*
* @param entry
* @param query - a query containing a filter that will be applied to the data
*/
public ExcelFeatureSource(ContentEntry entry, Query query) {
super(entry, query);
Date beginingOfExcelTime = HSSFDateUtil.getJavaDate(0);
dataStore = (ExcelDataStore) entry.getDataStore();
sheet = dataStore.getSheet();
latCol = dataStore.getLatColumnIndex();
lonCol = dataStore.getLonColumnIndex();
int rows = sheet.getPhysicalNumberOfRows();
int start = dataStore.getHeaderRowIndex() + 1;
latCol = dataStore.getLatColumnIndex();
lonCol = dataStore.getLonColumnIndex();
features = new ArrayList<SimpleFeature>();
filteredFeatures = new ArrayList<SimpleFeature>();
evaluator = dataStore.workbook.getCreationHelper().createFormulaEvaluator();
if (schema == null) {
schema = getSchema();
}
GeometryFactory geometryFactory = dataStore.getGeometryFactory();
SimpleFeatureBuilder builder = new SimpleFeatureBuilder(schema);
Row header = sheet.getRow(dataStore.getHeaderRowIndex());
for (int i = start; i < rows; i++) {
Row data = sheet.getRow(i);
double x = 0.0;
double y = 0.0;
for (int col = data.getFirstCellNum(); col < data.getLastCellNum(); col++) {
final Cell cell = data.getCell(col);
CellValue value = evaluator.evaluate(cell);
if (col == latCol) {
if (value.getCellType() == Cell.CELL_TYPE_NUMERIC) {
y = value.getNumberValue();
}
} else if (col == lonCol) {
if (value.getCellType() == Cell.CELL_TYPE_NUMERIC) {
x = value.getNumberValue();
}
} else {
// cast and handle
final String name = header.getCell(col).getStringCellValue().trim();
switch (value.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
AttributeType type = schema.getType(name);
Class<?> clazz = type.getBinding();
if (clazz == Double.class) {
builder.set(name, value.getNumberValue());
} else if (clazz == java.sql.Date.class) {
final java.util.Date javaDate = HSSFDateUtil.getJavaDate(value.getNumberValue());
final Calendar cal = Calendar.getInstance();
cal.clear();
cal.setTime(javaDate);
java.sql.Date date = new java.sql.Date(cal.getTimeInMillis());
builder.set(name, date);
} else if (clazz == java.util.Date.class) {
final java.util.Date javaDate = HSSFDateUtil.getJavaDate(value.getNumberValue());
builder.set(name, javaDate);
} else if (clazz == Time.class) {
final java.util.Date javaDate = HSSFDateUtil.getJavaDate(value.getNumberValue());
final Calendar cal = Calendar.getInstance();
cal.clear();
cal.setTime(javaDate);
cal.set(0, 0, 0);
Time time = new Time(cal.getTimeInMillis());
builder.set(name, time);
}
break;
case Cell.CELL_TYPE_STRING:
builder.set(name, value.getStringValue().trim());
break;
case Cell.CELL_TYPE_BOOLEAN:
builder.set(name, value.getBooleanValue());
break;
default:
System.out.println(
"We don't handle "
+ cell.getCellType()
+ " type cells "
+ cell.getStringCellValue());
}
}
}
Point p = geometryFactory.createPoint(new Coordinate(x, y));
builder.set("the_geom", p);
SimpleFeature feature = builder.buildFeature(null);
features.add(feature);
}
filterFeatures(query);
}
@Override
protected SimpleFeatureType buildFeatureType() throws IOException {
SimpleFeatureTypeBuilder tb = new SimpleFeatureTypeBuilder();
tb.setName(entry.getName());
tb.setCRS(dataStore.getProjection());
Row header = sheet.getRow(dataStore.getHeaderRowIndex());
Row data = sheet.getRow(dataStore.getHeaderRowIndex() + 1);
Row nextData = sheet.getRow(dataStore.getHeaderRowIndex() + 2);
boolean latColGood = false;
boolean lonColGood = false;
for (int i = header.getFirstCellNum(); i < header.getLastCellNum(); i++) {
// go through and guess data type from cell types!
Cell cell = data.getCell(i);
String name = header.getCell(i).getStringCellValue().trim();
CellValue value = evaluator.evaluate(cell);
int type = value.getCellType();
Class<?> clazz = null;
if (latCol == i) {
// check it's a number
if (type == Cell.CELL_TYPE_NUMERIC) {
latColGood = true;
}
} else if (lonCol == i) {
// check it's a number
if (type == Cell.CELL_TYPE_NUMERIC) {
lonColGood = true;
}
} else {
switch (type) {
case Cell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
if (value.getNumberValue() < 1.0) {
clazz = Time.class;
} else if (Math.floor(cell.getNumericCellValue())
== Math.ceil(cell.getNumericCellValue())) {
// midnight or just a date
// check the next row
Cell cell2 = nextData.getCell(i);
if (Math.floor(cell2.getNumericCellValue())
== Math.ceil(cell2.getNumericCellValue())) {
// probably a simple date
clazz = java.sql.Date.class;
} else {
// actual date/time element
clazz = java.util.Date.class;
}
} else {
// actual date/time element
clazz = java.util.Date.class;
}
} else {
clazz = Double.class;
}
break;
case Cell.CELL_TYPE_STRING:
clazz = String.class;
break;
case Cell.CELL_TYPE_BOOLEAN:
clazz = Boolean.class;
break;
}
System.out.println(name + ":" + clazz);
tb.add(name, clazz);
}
}
if (latColGood && lonColGood) {
tb.add("the_geom", Point.class);
} else {
throw new IOException("failed to find a Lat and Lon column");
}
// build the type (it is immutable and cannot be modified)
final SimpleFeatureType SCHEMA = tb.buildFeatureType();
return SCHEMA;
}
public static String Date(String d) {
Date date = HSSFDateUtil.getJavaDate(Double.valueOf(d));
SimpleDateFormat sFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
return sFormat.format(date);
}