List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getRichStringCellValue
public HSSFRichTextString getRichStringCellValue()
From source file:edu.ku.brc.specify.tasks.subpane.wb.DataImportDialog.java
License:Open Source License
/** * Parses the given import xls file according to the users selection and creates/updates the * Preview table, showing the user how the import options effect the way the data will be * imported into the spreadsheet.//from www. j a va2s. c o m * * @param table - the table to display the data * @return JTable - the table to display the data */ private JTable setXLSTableData(final JTable table) { int numRows = 0; int numCols = 0; String[] headers = {}; Vector<Vector<String>> tableDataVector = new Vector<Vector<String>>(); Vector<String> rowData = new Vector<String>(); Vector<String> headerVector = new Vector<String>(); DateWrapper scrDateFormat = AppPrefsCache.getDateWrapper("ui", "formatting", "scrdateformat"); try { log.debug("setXLSTableData - file - " + configXLS.getFile().toString()); InputStream input = new FileInputStream(configXLS.getFile()); POIFSFileSystem fs = new POIFSFileSystem(input); HSSFWorkbook workBook = new HSSFWorkbook(fs); HSSFSheet sheet = workBook.getSheetAt(0); Vector<Integer> badHeads = new Vector<Integer>(); Vector<Integer> emptyCols = new Vector<Integer>(); ((ConfigureXLS) config).checkHeadsAndCols(sheet, badHeads, emptyCols); if (badHeads.size() > 0 && doesFirstRowHaveHeaders) { if (table != null) { ((ConfigureXLS) config).showBadHeadingsMsg(badHeads, emptyCols, getTitle()); } this.doesFirstRowHaveHeaders = false; try { ignoreActions = true; this.containsHeaders.setSelected(false); } finally { ignoreActions = false; } if (table != null) { return table; } } boolean firstRow = true; //quick fix to prevent ".0" at end of catalog numbers etc NumberFormat nf = NumberFormat.getInstance(); nf.setMinimumFractionDigits(0); nf.setMaximumFractionDigits(20); nf.setGroupingUsed(false); //gets rid of commas int maxCols = 0; // Iterate over each row in the sheet Iterator<?> rows = sheet.rowIterator(); while (rows.hasNext()) { numCols = 0; rowData = new Vector<String>(); HSSFRow row = (HSSFRow) rows.next(); //log.debug(row.getLastCellNum()+" "+row.getPhysicalNumberOfCells()); int maxSize = Math.max(row.getPhysicalNumberOfCells(), row.getLastCellNum()); if (maxSize > maxCols) { maxCols = maxSize; } while (numCols < maxSize) { if (emptyCols.indexOf(new Integer(numCols)) == -1) { HSSFCell cell = row.getCell(numCols); String value = null; // if cell is blank, set value to "" if (cell == null) { value = ""; } else { int type = cell.getCellType(); switch (type) { case HSSFCell.CELL_TYPE_NUMERIC: // The best I can do at this point in the app is to guess if a // cell is a date. // Handle dates carefully while using HSSF. Excel stores all // dates as numbers, internally. // The only way to distinguish a date is by the formatting of // the cell. (If you // have ever formatted a cell containing a date in Excel, you // will know what I mean.) // Therefore, for a cell containing a date, cell.getCellType() // will return // HSSFCell.CELL_TYPE_NUMERIC. However, you can use a utility // function, // HSSFDateUtil.isCellDateFormatted(cell), to check if the cell // can be a date. // This function checks the format against a few internal // formats to decide the issue, // but by its very nature it is prone to false negatives. if (HSSFDateUtil.isCellDateFormatted(cell)) { value = scrDateFormat.getSimpleDateFormat().format(cell.getDateCellValue()); //value = scrDateFormat.getSimpleDateFormat().format(cell.getDateCellValue()); } else { double numeric = cell.getNumericCellValue(); value = nf.format(numeric); } break; case HSSFCell.CELL_TYPE_STRING: value = cell.getRichStringCellValue().getString(); break; case HSSFCell.CELL_TYPE_BLANK: value = ""; break; case HSSFCell.CELL_TYPE_BOOLEAN: value = Boolean.toString(cell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: value = UIRegistry.getResourceString("WB_FORMULA_IMPORT_NO_PREVIEW"); break; default: value = ""; log.error("unsuported cell type"); break; } } if (firstRow && doesFirstRowHaveHeaders) { checkUserColInfo(value, numCols); } if (isUserCol(numCols)) { rowData.add(value.toString()); } } numCols++; } if (doesFirstRowHaveHeaders && firstRow) { headerVector = rowData; headers = new String[rowData.size()]; } else if (!doesFirstRowHaveHeaders && firstRow) { //headers = createDummyHeaders(rowData.size()); tableDataVector.add(rowData); } else { tableDataVector.add(rowData); } firstRow = false; numRows++; } maxCols -= emptyCols.size(); if (!doesFirstRowHaveHeaders) { headerVector = createDummyHeadersAsVector(maxCols); headers = new String[maxCols]; } for (int i = 0; i < headerVector.size(); i++) { headers[i] = headerVector.elementAt(i); } printArray(headers); String[][] tableData = new String[tableDataVector.size()][maxCols]; for (int i = 0; i < tableDataVector.size(); i++) { Vector<String> v = tableDataVector.get(i); for (int j = 0; j < v.size(); j++) { tableData[i][j] = v.get(j).toString(); } } if (checkForErrors(headers, tableData)) { errorPanel.showDataImportStatusPanel(true); } else { errorPanel.showDataImportStatusPanel(false); } if ((doesFirstRowHaveHeaders ? numRows - 1 : numRows) > WorkbenchTask.MAX_ROWS) { hasTooManyRows = true; showTooManyRowsErrorDialog(); } else { hasTooManyRows = false; } log.debug(headers); log.debug(tableData); model = new PreviewTableModel(headers, tableData); JTable result = null; if (table == null) { result = new JTable(); result.setColumnSelectionAllowed(false); result.setRowSelectionAllowed(false); result.setCellSelectionEnabled(false); result.getTableHeader().setReorderingAllowed(false); result.setPreferredScrollableViewportSize(new Dimension(500, 100)); result.setAutoResizeMode(JTable.AUTO_RESIZE_OFF); } else { result = table; } result.setModel(model); result.setDefaultRenderer(String.class, new BiColorTableCellRenderer(false)); model.fireTableDataChanged(); model.fireTableStructureChanged(); return result; } catch (Exception ex) { UIRegistry.displayErrorDlgLocalized(UIRegistry.getResourceString("WB_ERROR_READING_IMPORT_FILE")); if (table != null) { String[] columnNames = {}; String[][] blankData = { {} }; model = new PreviewTableModel(columnNames, blankData); table.setModel(model); table.setColumnSelectionAllowed(false); table.setRowSelectionAllowed(false); table.setCellSelectionEnabled(false); table.getTableHeader().setReorderingAllowed(false); table.setPreferredScrollableViewportSize(new Dimension(500, 100)); table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF); table.setDefaultRenderer(String.class, new BiColorTableCellRenderer(false)); model.fireTableDataChanged(); model.fireTableStructureChanged(); return table; } //log.error("Error attempting to parse input xls file:" + ex); //ex.printStackTrace(); } return null; }
From source file:edu.ku.brc.specify.tasks.subpane.wb.XLSImport.java
License:Open Source License
/** * @param headerRow/*from w w w.j a va 2s.c om*/ */ private void getSystemCols(final HSSFRow headerRow) { for (int c = headerRow.getFirstCellNum(); c <= headerRow.getLastCellNum(); c++) { HSSFCell cell = headerRow.getCell(c); int nulls = 0; if (cell != null) { String header = cell.getRichStringCellValue().getString(); if (header != null) { if (header.equals(IMAGE_PATH_HEADING)) { cardImageCols.add(c - nulls); } if (header.equals(GEO_DATA_HEADING)) { geoCol = c - nulls; } } } else { nulls++; } } }
From source file:edu.ku.brc.specify.tasks.subpane.wb.XLSImport.java
License:Open Source License
public DataImportIFace.Status getData(final Workbench workbench) { if (config.getStatus() == ConfigureExternalDataIFace.Status.Valid) { DateWrapper scrDateFormat = AppPrefsCache.getDateWrapper("ui", "formatting", "scrdateformat"); try {// w w w . j a v a 2 s . com InputStream input = new FileInputStream(config.getFile()); POIFSFileSystem fs = new POIFSFileSystem(input); HSSFWorkbook workBook = new HSSFWorkbook(fs); HSSFSheet sheet = workBook.getSheetAt(0); int numRows = 0; // Calculate the number of rows and columns Set<WorkbenchTemplateMappingItem> wbtmiSet = workbench.getWorkbenchTemplate() .getWorkbenchTemplateMappingItems(); Vector<WorkbenchTemplateMappingItem> wbtmiList = new Vector<WorkbenchTemplateMappingItem>(); NumberFormat nf = NumberFormat.getInstance(); nf.setMinimumFractionDigits(0); nf.setMaximumFractionDigits(20); nf.setGroupingUsed(false); //gets rid of commas NumberFormat nfGeoCoord = NumberFormat.getInstance(); nfGeoCoord.setMinimumFractionDigits(0); nfGeoCoord.setMaximumFractionDigits( LatLonConverter.DECIMAL_SIZES[LatLonConverter.FORMAT.DDDDDD.ordinal()]); nfGeoCoord.setGroupingUsed(false); //gets rid of commas char decSep = new DecimalFormatSymbols().getDecimalSeparator(); wbtmiList.addAll(wbtmiSet); Collections.sort(wbtmiList); this.truncations.clear(); Vector<HSSFHyperlink> activeHyperlinks = new Vector<HSSFHyperlink>(); // Iterate over each row in the sheet Iterator<?> rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); if (numRows == 0 && config.getFirstRowHasHeaders()) { numRows++; getSystemCols(row); continue; } WorkbenchRow wbRow = workbench.addRow(); for (WorkbenchTemplateMappingItem wbtmi : wbtmiList) { int cellNum = wbtmi.getOrigImportColumnIndex().intValue(); if (cellNum == -1) { if (wbtmi.getViewOrder() != null) { cellNum = wbtmi.getViewOrder().intValue(); if (cellNum == -1) { continue; } } } HSSFCell cell = row.getCell(cellNum); if (cell == null) { continue; } int type = cell.getCellType(); if (type == HSSFCell.CELL_TYPE_FORMULA) { type = cell.getCachedFormulaResultType(); } String value = ""; boolean skip = false; switch (type) { case HSSFCell.CELL_TYPE_NUMERIC: { if (HSSFDateUtil.isCellDateFormatted(cell)) { //even if WorkbenchTask.getDataType(wbtmi) is not Calendar or Date. Hmmmm. value = scrDateFormat.getSimpleDateFormat().format(cell.getDateCellValue()); } else { Class<?> classObj = WorkbenchTask.getDataType(wbtmi); if (classObj.equals(Integer.class)) { double numeric = cell.getNumericCellValue(); value = Integer.toString((int) numeric); } else if (classObj.equals(Calendar.class) || classObj.equals(Date.class)) { Date d = cell.getDateCellValue(); if (d != null) { value = scrDateFormat.getSimpleDateFormat().format(cell.getDateCellValue()); } else { value = null; } } else { double numeric = cell.getNumericCellValue(); value = nf.format(numeric); if (isGeoCoordinate(wbtmi)) { int sepInx = value.indexOf(decSep); if (sepInx > -1 && value.substring(sepInx).length() > nfGeoCoord .getMaximumFractionDigits()) { String value2 = nfGeoCoord.format(numeric); int maxlen = wbtmi.getFieldName().startsWith("latitude") ? nfGeoCoord.getMaximumFractionDigits() + 3 : nfGeoCoord.getMaximumFractionDigits() + 4; if (numeric < 0) { maxlen++; } //System.out.println(value + " " + trackTrunc(value, numRows, wbtmi.getViewOrder(), wbtmi.getCaption(), // maxlen) + " " + value2); value = value2; } } } } break; } case HSSFCell.CELL_TYPE_STRING: HSSFHyperlink hl = checkHyperlinks(cell, activeHyperlinks); if (hl == null /*|| (hl != null && hl.getType() == HSSFHyperlink.LINK_EMAIL)*/) { value = cell.getRichStringCellValue().getString(); } else { //value = hl.getAddress(); value = hl.getLabel(); } break; case HSSFCell.CELL_TYPE_BLANK: value = ""; type = HSSFCell.CELL_TYPE_STRING; break; case HSSFCell.CELL_TYPE_BOOLEAN: boolean bool = cell.getBooleanCellValue(); value = Boolean.toString(bool); break; default: skip = true; break; } if (!skip && value != null && !value.trim().equals("")) { wbRow.setData(truncateIfNecessary(value, numRows, wbtmi), wbtmi.getViewOrder(), true); } } addImageInfo(row, wbRow); addGeoInfo(row, wbRow); numRows++; } if (activeHyperlinks.size() > 0) { log.warn("Hyperlinks vector not empty after import. Overlapping hyperlink ranges?"); } return status = this.truncations.size() == 0 && this.messages.size() == 0 ? DataImportIFace.Status.Valid : DataImportIFace.Status.Modified; } catch (Exception ex) { edu.ku.brc.af.core.UsageTracker.incrHandledUsageCount(); edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(XLSImport.class, ex); log.error(ex); } } return status = DataImportIFace.Status.Error; }
From source file:edu.ku.brc.specify.tasks.subpane.wb.XLSImport.java
License:Open Source License
private void addImageInfo(final HSSFRow row, final WorkbenchRow wbRow) { for (Integer c : cardImageCols) { HSSFCell imgCell = row.getCell(c); if (imgCell != null) { String imageSpec[] = imgCell.getRichStringCellValue().getString().split("\\t"); String imagePath = imageSpec[0]; String attachToTblName = imageSpec.length > 1 ? imageSpec[1] : null; if (imagePath != null) { try { wbRow.addImage(new File(imagePath), attachToTblName); } catch (IOException e) { //edu.ku.brc.af.core.UsageTracker.incrHandledUsageCount(); //edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(XLSImport.class, e); wbRow.addImagePath(imagePath, attachToTblName); UIRegistry.getStatusBar().setErrorMessage(e.getMessage()); StringBuilder errMsg = new StringBuilder(getResourceString("WB_IMG_IMPORT_ERROR")); errMsg.append(": "); errMsg.append(getResourceString("WB_ROW")); errMsg.append(" "); errMsg.append(row.getRowNum()); errMsg.append(", "); errMsg.append(imagePath); messages.add(errMsg.toString()); }// ww w . ja va2 s . com } } } }
From source file:edu.ku.brc.specify.tasks.subpane.wb.XLSImport.java
License:Open Source License
public void addGeoInfo(final HSSFRow row, final WorkbenchRow wbRow) { if (geoCol != -1) { HSSFCell c = row.getCell(geoCol); if (c != null) { String geoData = c.getRichStringCellValue().getString(); if (geoData != null) { // TEMP FIX FOR BUG 4562 RELEASE // Only allow 255 chars - Note this really shouldn't happen because // the WB should have never been able to save something larger wbRow.setBioGeomancerResults(geoData.length() <= 255 ? geoData : geoData.substring(0, 254)); }/*from www.jav a 2s . c o m*/ } } }
From source file:edu.ku.brc.specify.utilapps.BuildSampleDatabase.java
License:Open Source License
/** * @param treeDef/*from w ww .j a v a 2s . c o m*/ * @return */ public Geography convertGeographyFromXLS(final GeographyTreeDef treeDef) { frame.setDesc("Building Geography Tree..."); Hashtable<String, Geography> geoHash = new Hashtable<String, Geography>(); geoHash.clear(); String fileName = "Geography.xls"; File file = XMLHelper.getConfigDir("../demo_files/" + fileName); if (!file.exists()) { log.error("Couldn't file[" + file.getAbsolutePath() + "] checking the config dir"); file = XMLHelper.getConfigDir(fileName); if (!file.exists()) { file = new File("Specify/demo_files/" + fileName); } } if (file == null || !file.exists()) { log.error("Couldn't file[" + file.getAbsolutePath() + "]"); return null; } // setup the root Geography record (planet Earth) Geography earth = new Geography(); earth.initialize(); earth.setName(getResourceString("Earth")); earth.setFullName(earth.getName()); earth.setNodeNumber(1); earth.setHighestChildNodeNumber(1); earth.setRankId(0); earth.setDefinition(treeDef); GeographyTreeDefItem defItem = treeDef.getDefItemByRank(0); earth.setDefinitionItem(defItem); int counter = 0; try { startTx(); persist(earth); String[] cells = new String[4]; InputStream input = new FileInputStream(file); POIFSFileSystem fs = new POIFSFileSystem(input); HSSFWorkbook workBook = new HSSFWorkbook(fs); HSSFSheet sheet = workBook.getSheetAt(0); Iterator<?> rows = sheet.rowIterator(); int lastRowNum = sheet.getLastRowNum(); if (frame != null) { final int mx = lastRowNum; SwingUtilities.invokeLater(new Runnable() { public void run() { frame.setProcess(0, mx); } }); } while (rows.hasNext()) { if (counter == 0) { counter = 1; rows.next(); continue; } if (counter % 100 == 0) { if (frame != null) frame.setProcess(counter); log.info("Converted " + counter + " Geography records"); } HSSFRow row = (HSSFRow) rows.next(); Iterator<?> cellsIter = row.cellIterator(); int i = 0; while (cellsIter.hasNext() && i < 4) { HSSFCell cell = (HSSFCell) cellsIter.next(); if (cell != null) { cells[i] = StringUtils.trim(cell.getRichStringCellValue().getString()); i++; } } // Sets nulls to unused cells for (int j = i; j < 4; j++) { cells[j] = null; } //System.out.println(); @SuppressWarnings("unused") Geography newGeo = convertGeographyRecord(cells[0], cells[1], cells[2], cells[3], earth); counter++; } } catch (Exception ex) { ex.printStackTrace(); } if (frame != null) frame.setProcess(counter); log.info("Converted " + counter + " Geography records"); frame.setDesc("Saving Geography Tree..."); frame.getProcessProgress().setIndeterminate(true); TreeHelper.fixFullnameForNodeAndDescendants(earth); earth.setNodeNumber(1); fixNodeNumbersFromRoot(earth); commitTx(); /*startTx(); TreeHelper.fixFullnameForNodeAndDescendants(earth); earth.setNodeNumber(1); fixNodeNumbersFromRoot(earth); printTree(earth, 0); saveTree(earth); commitTx();*/ log.info("Converted " + counter + " Stratigraphy records"); // set up Geography foreign key mapping for locality geoHash.clear(); return earth; }
From source file:edu.ku.brc.specify.utilapps.BuildSampleDatabase.java
License:Open Source License
/** * @param fileName/* w w w .j a va2 s. c o m*/ * @return */ public HashSet<String> getColumnNamesFromXLS(final String fileName, final boolean doUserProvidedFile) { File file = TaxonLoadSetupPanel.getFileForTaxon(fileName, doUserProvidedFile); if (file == null) { return null; } HashSet<String> nameHash = new HashSet<String>(); try { String[] cells = new String[35]; InputStream input = new FileInputStream(file); POIFSFileSystem fs = new POIFSFileSystem(input); HSSFWorkbook workBook = new HSSFWorkbook(fs); HSSFSheet sheet = workBook.getSheetAt(0); Iterator<?> rows = sheet.rowIterator(); rows = sheet.rowIterator(); if (rows.hasNext()) { for (int i = 0; i < cells.length; i++) { cells[i] = null; } HSSFRow row = (HSSFRow) rows.next(); Iterator<?> cellsIter = row.cellIterator(); while (cellsIter.hasNext()) { HSSFCell cell = (HSSFCell) cellsIter.next(); if (cell != null) { nameHash.add(StringUtils.trim(cell.getRichStringCellValue().getString())); } } } } catch (Exception ex) { ex.printStackTrace(); } return nameHash; }
From source file:edu.ku.brc.specify.utilapps.BuildSampleDatabase.java
License:Open Source License
/** * @param cell/* w w w. j a v a2 s. c o m*/ * @return */ public String getXLSCellValueAsStr(final HSSFCell cell) { String value = null; // if cell is blank, set value to "" if (cell == null) { value = ""; } else { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: // The best I can do at this point in the app is to guess if a // cell is a date. // Handle dates carefully while using HSSF. Excel stores all // dates as numbers, internally. // The only way to distinguish a date is by the formatting of // the cell. (If you // have ever formatted a cell containing a date in Excel, you // will know what I mean.) // Therefore, for a cell containing a date, cell.getCellType() // will return // HSSFCell.CELL_TYPE_NUMERIC. However, you can use a utility // function, // HSSFDateUtil.isCellDateFormatted(cell), to check if the cell // can be a date. // This function checks the format against a few internal // formats to decide the issue, // but by its very nature it is prone to false negatives. if (HSSFDateUtil.isCellDateFormatted(cell)) { DateWrapper scrDateFormat = AppPrefsCache.getDateWrapper("ui", "formatting", "scrdateformat"); SimpleDateFormat simpDateFmt = scrDateFormat != null && scrDateFormat.getSimpleDateFormat() != null ? scrDateFormat.getSimpleDateFormat() : sdf; value = simpDateFmt.format(cell.getDateCellValue()); } else { double numeric = cell.getNumericCellValue(); value = numFmt.format(numeric); } break; case HSSFCell.CELL_TYPE_STRING: value = cell.getRichStringCellValue().getString(); break; case HSSFCell.CELL_TYPE_BLANK: value = ""; break; case HSSFCell.CELL_TYPE_BOOLEAN: value = Boolean.toString(cell.getBooleanCellValue()); break; default: value = ""; log.error("unsuported cell type[" + cell.getCellType() + "]"); break; } } return value; }
From source file:edu.ku.brc.specify.utilapps.BuildSampleDatabase.java
License:Open Source License
/** * @param treeDef/*from w w w .j a v a2s. c o m*/ * @return */ public GeologicTimePeriod convertChronoStratFromXLS(final GeologicTimePeriodTreeDef treeDef, final Agent userAgent) { startTx(); GeologicTimePeriodTreeDefItem root = createGeologicTimePeriodTreeDefItem(null, treeDef, "Root", 0); GeologicTimePeriodTreeDefItem era = createGeologicTimePeriodTreeDefItem(root, treeDef, "Erathem/Era", 100); GeologicTimePeriodTreeDefItem period = createGeologicTimePeriodTreeDefItem(era, treeDef, "System/Period", 200); GeologicTimePeriodTreeDefItem series = createGeologicTimePeriodTreeDefItem(period, treeDef, "Series/Epoch", 300); @SuppressWarnings("unused") GeologicTimePeriodTreeDefItem member = createGeologicTimePeriodTreeDefItem(series, treeDef, "Stage/Age", 400); persist(root); commitTx(); series.setIsInFullName(true); frame.setDesc("Building ChronoStratigraphy Tree..."); Hashtable<String, GeologicTimePeriod> chronoHash = new Hashtable<String, GeologicTimePeriod>(); chronoHash.clear(); String fileName = "chronostrat_tree.xls"; File file = XMLHelper.getConfigDir("../demo_files/" + fileName); if (!file.exists()) { log.error("Couldn't file[" + file.getAbsolutePath() + "] checking the config dir"); file = XMLHelper.getConfigDir(fileName); if (!file.exists()) { file = new File("Specify/demo_files/" + fileName); } } if (file == null || !file.exists()) { log.error("Couldn't file[" + file.getAbsolutePath() + "]"); return null; } // setup the root ChronoStrat record (planet Earth) GeologicTimePeriod rootNode = new GeologicTimePeriod(); rootNode.initialize(); rootNode.setName(getResourceString("Root")); rootNode.setFullName(rootNode.getName()); rootNode.setRankId(0); rootNode.setDefinition(treeDef); rootNode.setDefinitionItem(root); rootNode.setCreatedByAgent(userAgent); int counter = 0; try { startTx(); persist(rootNode); String[] cells = new String[4]; InputStream input = new FileInputStream(file); POIFSFileSystem fs = new POIFSFileSystem(input); HSSFWorkbook workBook = new HSSFWorkbook(fs); HSSFSheet sheet = workBook.getSheetAt(0); Iterator<?> rows = sheet.rowIterator(); int lastRowNum = sheet.getLastRowNum(); if (frame != null) { final int mx = lastRowNum; SwingUtilities.invokeLater(new Runnable() { public void run() { frame.setProcess(0, mx); } }); } while (rows.hasNext()) { if (counter == 0) { counter = 1; continue; } if (counter % 100 == 0) { if (frame != null) frame.setProcess(counter); log.info("Converted " + counter + " ChronoStrat records"); } HSSFRow row = (HSSFRow) rows.next(); Iterator<?> cellsIter = row.cellIterator(); int i = 0; while (cellsIter.hasNext() && i < 4) { HSSFCell cell = (HSSFCell) cellsIter.next(); if (cell != null) { cells[i] = StringUtils.trim(cell.getRichStringCellValue().getString()); i++; } } for (int j = i; j < 4; j++) { cells[j] = null; } //System.out.println(); @SuppressWarnings("unused") GeologicTimePeriod newGeo = convertChronoStratRecord(cells[0], cells[1], cells[2], cells[3], rootNode, userAgent); counter++; } input.close(); } catch (Exception ex) { ex.printStackTrace(); } if (frame != null) frame.setProcess(counter); log.info("Converted " + counter + " ChronoStrat records"); TreeHelper.fixFullnameForNodeAndDescendants(rootNode); rootNode.setNodeNumber(1); fixNodeNumbersFromRoot(rootNode); commitTx(); log.info("Converted " + counter + " Stratigraphy records"); // set up ChronoStrat foreign key mapping for locality chronoHash.clear(); return rootNode; }
From source file:edu.wustl.catissuecore.webservice.util.ExcelFileReader.java
License:BSD License
/** * Method to read content of one row of Excel sheet * @param row HSSFRow row whose content to be read * @return/*from w ww . ja v a 2 s . c o m*/ */ private String[] getRowContent(HSSFRow row) { HSSFCell cell = null; int noOfColumn = row.getPhysicalNumberOfCells(); String[] rowContent = new String[noOfColumn]; for (short i = 0; i < noOfColumn; i++) { try { cell = row.getCell(i); if (cell == null) rowContent[i] = ""; else { if (columnDataType.length > i && columnDataType[i] != null) { switch (columnDataType[i]) { case 0: { HSSFRichTextString strCell = cell.getRichStringCellValue(); rowContent[i] = strCell.toString(); break; } case 1: { rowContent[i] = String.valueOf(cell.getNumericCellValue()); break; } case 2: { Date date = cell.getDateCellValue(); rowContent[i] = parseDateToString(date, DATE_PATTERN_MM_DD_YYYY); break; } } } else { HSSFRichTextString strCell = cell.getRichStringCellValue(); rowContent[i] = strCell.toString(); } } } catch (Exception e) { System.out.println("columnDataType[" + i + "]" + columnDataType[i]); } } return rowContent; }