/* 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"; } }
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; }
@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); }
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); }