List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getDateCellValue
public Date getDateCellValue()
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 w w w. j ava2 s. co 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
public DataImportIFace.Status getData(final Workbench workbench) { if (config.getStatus() == ConfigureExternalDataIFace.Status.Valid) { DateWrapper scrDateFormat = AppPrefsCache.getDateWrapper("ui", "formatting", "scrdateformat"); try {//w ww .j a v a2 s . c o m 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.utilapps.BuildSampleDatabase.java
License:Open Source License
/** * @param cell//from w w w .ja v a 2s. co 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.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/*ww w. j a va 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; }
From source file:gov.nih.nci.evs.browser.utils.ResolvedValueSetIteratorHolder.java
License:Open Source License
/** * (Each Excel sheet cell becomes an HTML table cell) Generates an HTML * table cell which has the same font styles, alignments, colours and * borders as the Excel cell./* ww w . j av a2 s . c o m*/ * * @param cell * The Excel cell. */ private void td(final HSSFCell cell) { int colspan = 1; if (colIndex == mergeStart) { // First cell in the merging region - set colspan. colspan = mergeEnd - mergeStart + 1; } else if (colIndex == mergeEnd) { // Last cell in the merging region - no more skipped cells. mergeStart = -1; mergeEnd = -1; return; } else if (mergeStart != -1 && mergeEnd != -1 && colIndex > mergeStart && colIndex < mergeEnd) { // Within the merging region - skip the cell. return; } //KLO 05022018 //out.append("<td "); out.append("<td height=\"15\" "); if (colspan > 1) { out.append("colspan='").append(colspan).append("' "); } if (cell == null) { out.append("/>\n"); return; } out.append("style='"); final HSSFCellStyle style = cell.getCellStyle(); // Text alignment switch (style.getAlignment()) { case CellStyle.ALIGN_LEFT: out.append("text-align: left; "); break; case CellStyle.ALIGN_RIGHT: out.append("text-align: right; "); break; case CellStyle.ALIGN_CENTER: out.append("text-align: center; "); break; default: break; } // Font style, size and weight final HSSFFont font = style.getFont(book); if (font == null) return; if (font.getBoldweight() == HSSFFont.BOLDWEIGHT_BOLD) { out.append("font-weight: bold; "); } if (font.getItalic()) { out.append("font-style: italic; "); } if (font.getUnderline() != HSSFFont.U_NONE) { out.append("text-decoration: underline; "); } out.append("font-size: ").append(Math.floor(font.getFontHeightInPoints() * 0.8)).append("pt; "); // Cell background and font colours final short[] backRGB = style.getFillForegroundColorColor().getTriplet(); final HSSFColor foreColor = palette.getColor(font.getColor()); if (foreColor != null) { final short[] foreRGB = foreColor.getTriplet(); if (foreRGB[0] != 0 || foreRGB[1] != 0 || foreRGB[2] != 0) { out.append("color: rgb(").append(foreRGB[0]).append(',').append(foreRGB[1]).append(',') .append(foreRGB[2]).append(");"); } } if (backRGB[0] != 0 || backRGB[1] != 0 || backRGB[2] != 0) { out.append("background-color: rgb(").append(backRGB[0]).append(',').append(backRGB[1]).append(',') .append(backRGB[2]).append(");"); } // Border if (style.getBorderTop() != HSSFCellStyle.BORDER_NONE) { out.append("border-top-style: solid; "); } if (style.getBorderRight() != HSSFCellStyle.BORDER_NONE) { out.append("border-right-style: solid; "); } if (style.getBorderBottom() != HSSFCellStyle.BORDER_NONE) { out.append("border-bottom-style: solid; "); } if (style.getBorderLeft() != HSSFCellStyle.BORDER_NONE) { out.append("border-left-style: solid; "); } out.append("'>"); String val = ""; try { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: val = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_NUMERIC: // POI does not distinguish between integer and double, thus: final double original = cell.getNumericCellValue(), rounded = Math.round(original); if (Math.abs(rounded - original) < 0.00000000000000001) { val = String.valueOf((int) rounded); } else { val = String.valueOf(original); } break; case HSSFCell.CELL_TYPE_FORMULA: final CellValue cv = evaluator.evaluate(cell); if (cv == null) return; switch (cv.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: out.append(cv.getBooleanValue()); break; case Cell.CELL_TYPE_NUMERIC: out.append(cv.getNumberValue()); break; case Cell.CELL_TYPE_STRING: out.append(cv.getStringValue()); break; case Cell.CELL_TYPE_BLANK: break; case Cell.CELL_TYPE_ERROR: break; default: break; } break; default: // Neither string or number? Could be a date. try { val = sdf.format(cell.getDateCellValue()); } catch (final Exception e1) { } } } catch (final Exception e) { val = e.getMessage(); } if ("null".equals(val)) { val = ""; } if (pix.containsKey(rowIndex)) { if (pix.get(rowIndex).containsKey(colIndex)) { for (final HSSFPictureData pic : pix.get(rowIndex).get(colIndex)) { out.append("<img alt='Image in Excel sheet' src='data:"); out.append(pic.getMimeType()); out.append(";base64,"); try { out.append(new String(Base64.encodeBase64(pic.getData()), "US-ASCII")); } catch (final UnsupportedEncodingException e) { throw new RuntimeException(e); } out.append("'/>"); } } } if (isCode(val) && this.URL != null) { val = getHyperLink(val); } out.append(val); out.append("</td>\n"); }
From source file:gov.nih.nci.evs.browser.utils.ResolvedValueSetIteratorHolder.java
License:Open Source License
private void td(final HSSFCell cell, StringBuffer buf) { int colspan = 1; if (colIndex == mergeStart) { // First cell in the merging region - set colspan. colspan = mergeEnd - mergeStart + 1; } else if (colIndex == mergeEnd) { // Last cell in the merging region - no more skipped cells. mergeStart = -1;/*from w ww.jav a 2 s . c o m*/ mergeEnd = -1; return; } else if (mergeStart != -1 && mergeEnd != -1 && colIndex > mergeStart && colIndex < mergeEnd) { // Within the merging region - skip the cell. return; } //KLO 05022018 //buf.append("<td "); buf.append("<td height=\"15\" "); if (colspan > 1) { buf.append("colspan='").append(colspan).append("' "); } if (cell == null) { buf.append("/>"); return; } buf.append("style='"); final HSSFCellStyle style = cell.getCellStyle(); // Text alignment switch (style.getAlignment()) { case CellStyle.ALIGN_LEFT: buf.append("text-align: left; "); break; case CellStyle.ALIGN_RIGHT: buf.append("text-align: right; "); break; case CellStyle.ALIGN_CENTER: buf.append("text-align: center; "); break; default: break; } // Font style, size and weight final HSSFFont font = style.getFont(book); if (font == null) return; if (font.getBoldweight() == HSSFFont.BOLDWEIGHT_BOLD) { buf.append("font-weight: bold; "); } if (font.getItalic()) { buf.append("font-style: italic; "); } if (font.getUnderline() != HSSFFont.U_NONE) { buf.append("text-decoration: underline; "); } buf.append("font-size: ").append(Math.floor(font.getFontHeightInPoints() * 0.8)).append("pt; "); // Cell background and font colours final short[] backRGB = style.getFillForegroundColorColor().getTriplet(); final HSSFColor foreColor = palette.getColor(font.getColor()); if (foreColor != null) { final short[] foreRGB = foreColor.getTriplet(); if (foreRGB[0] != 0 || foreRGB[1] != 0 || foreRGB[2] != 0) { buf.append("color: rgb(").append(foreRGB[0]).append(',').append(foreRGB[1]).append(',') .append(foreRGB[2]).append(");"); } } if (backRGB[0] != 0 || backRGB[1] != 0 || backRGB[2] != 0) { buf.append("background-color: rgb(").append(backRGB[0]).append(',').append(backRGB[1]).append(',') .append(backRGB[2]).append(");"); } // Border if (style.getBorderTop() != HSSFCellStyle.BORDER_NONE) { buf.append("border-top-style: solid; "); } if (style.getBorderRight() != HSSFCellStyle.BORDER_NONE) { buf.append("border-right-style: solid; "); } if (style.getBorderBottom() != HSSFCellStyle.BORDER_NONE) { buf.append("border-bottom-style: solid; "); } if (style.getBorderLeft() != HSSFCellStyle.BORDER_NONE) { buf.append("border-left-style: solid; "); } buf.append("'>"); String val = ""; try { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: val = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_NUMERIC: // POI does not distinguish between integer and double, thus: final double original = cell.getNumericCellValue(), rounded = Math.round(original); if (Math.abs(rounded - original) < 0.00000000000000001) { val = String.valueOf((int) rounded); } else { val = String.valueOf(original); } break; case HSSFCell.CELL_TYPE_FORMULA: final CellValue cv = evaluator.evaluate(cell); if (cv == null) return; switch (cv.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: buf.append(cv.getBooleanValue()); break; case Cell.CELL_TYPE_NUMERIC: buf.append(cv.getNumberValue()); break; case Cell.CELL_TYPE_STRING: buf.append(cv.getStringValue()); break; case Cell.CELL_TYPE_BLANK: break; case Cell.CELL_TYPE_ERROR: break; default: break; } break; default: // Neither string or number? Could be a date. try { val = sdf.format(cell.getDateCellValue()); } catch (final Exception e1) { } } } catch (final Exception e) { val = e.getMessage(); } if ("null".equals(val)) { val = ""; } if (pix.containsKey(rowIndex)) { if (pix.get(rowIndex).containsKey(colIndex)) { for (final HSSFPictureData pic : pix.get(rowIndex).get(colIndex)) { buf.append("<img alt='Image in Excel sheet' src='data:"); buf.append(pic.getMimeType()); buf.append(";base64,"); try { buf.append(new String(Base64.encodeBase64(pic.getData()), "US-ASCII")); } catch (final UnsupportedEncodingException e) { throw new RuntimeException(e); } buf.append("'/>"); } } } if (isCode(val) && this.URL != null) { val = getHyperLink(val); } buf.append(val); buf.append("</td>"); }
From source file:gov.nih.nci.evs.browser.utils.ResolvedValueSetIteratorHolder.java
License:Open Source License
private String getCellData(final HSSFCell cell) { if (cell == null) return null; int colspan = 1; if (colIndex == mergeStart) { // First cell in the merging region - set colspan. colspan = mergeEnd - mergeStart + 1; } else if (colIndex == mergeEnd) { // Last cell in the merging region - no more skipped cells. mergeStart = -1;/* w w w .j ava 2 s . c o m*/ mergeEnd = -1; return null; } else if (mergeStart != -1 && mergeEnd != -1 && colIndex > mergeStart && colIndex < mergeEnd) { // Within the merging region - skip the cell. return null; } //StringBuffer buf = new StringBuffer(); String val = ""; try { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: val = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_NUMERIC: // POI does not distinguish between integer and double, thus: final double original = cell.getNumericCellValue(), rounded = Math.round(original); if (Math.abs(rounded - original) < 0.00000000000000001) { val = String.valueOf((int) rounded); } else { val = String.valueOf(original); } break; case HSSFCell.CELL_TYPE_FORMULA: final CellValue cv = evaluator.evaluate(cell); if (cv == null) return null; switch (cv.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: out.append(cv.getBooleanValue()); break; case Cell.CELL_TYPE_NUMERIC: out.append(cv.getNumberValue()); break; case Cell.CELL_TYPE_STRING: out.append(cv.getStringValue()); break; case Cell.CELL_TYPE_BLANK: break; case Cell.CELL_TYPE_ERROR: break; default: break; } break; default: // Neither string or number? Could be a date. try { val = sdf.format(cell.getDateCellValue()); } catch (final Exception e1) { } } } catch (final Exception e) { val = e.getMessage(); } if ("null".equals(val)) { val = ""; } return val; }
From source file:gov.nih.nci.ncicb.cadsr.bulkloader.util.excel.ExcelUtility.java
License:BSD License
public static Date getDate(HSSFSheet sheet, int row, short col) { HSSFRow hssfRow = getRow(sheet, row); if (hssfRow == null) { return null; }/*w ww .ja v a2s.c om*/ HSSFCell cell = getRow(sheet, row).getCell(col); if (isNull(cell)) { return null; } try { return cell.getDateCellValue(); } catch (Throwable t) { t.printStackTrace(); log.error("Unable to read date from " + row + "," + col, t); return null; } }
From source file:gr.abiss.calipso.domain.ExcelFile.java
License:Open Source License
public void convertSelectedColumnsToDate() { if (selCols == null) { return;//from w w w .j av a 2 s. c om } // could not find a better way to convert excel number to date HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); HSSFRow row = sheet.createRow(0); HSSFCell cell = row.createCell((short) 0); for (int i : selCols) { for (List<Cell> cells : rows) { Cell c = cells.get(i); if (c != null && c.value instanceof Double) { cell.setCellValue((Double) c.value); c.value = cell.getDateCellValue(); } } } }
From source file:include.excel_import.Outter.java
License:Open Source License
private void pump(Vector vector, HSSFCell hssfcell) { String s = getCellType(hssfcell); if (s.equals("INT")) { DecimalFormat decimalformat = new DecimalFormat("##"); String s1 = String.valueOf(decimalformat.format(hssfcell.getNumericCellValue())); vector.addElement(s1);//from w w w .j ava 2 s . c o m } else if (s.equals("DOUBLE")) { DecimalFormat decimalformat1 = new DecimalFormat("##.##"); String s2 = String.valueOf(decimalformat1.format(hssfcell.getNumericCellValue())); vector.addElement(s2); } else if (s.equals("STRING")) vector.addElement(hssfcell.getStringCellValue()); else if (s.equals("DATE")) { SimpleDateFormat simpledateformat = new SimpleDateFormat("yyyy-MM-dd"); String s3 = String.valueOf(simpledateformat.format(hssfcell.getDateCellValue())); vector.addElement(s3); } }