// 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 void copyCell(XSSFCell oldCell, XSSFCell newCell) { try { switch (oldCell.getCellType()) { case XSSFCell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getStringCellValue()); break; case XSSFCell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case XSSFCell.CELL_TYPE_BLANK: newCell.setCellType(XSSFCell.CELL_TYPE_BLANK); break; case XSSFCell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case XSSFCell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case XSSFCell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; default: break; } } catch (Exception ex) { System.out.println("Error in writing Cell in reject excel file :: " + ex.getMessage()); ex.printStackTrace(); } }
private void processCell(Element tr, XSSFCell cell) { int num = cell.getSheet().getNumMergedRegions(); // System.out.println(cell.getCTCell()); for (int i = 0; i < num; i++) { CellRangeAddress c = cell.getSheet().getMergedRegion(i); System.out.println(c.getFirstColumn()); ; System.out.println(c.getLastColumn()); System.out.println(c.getFirstRow()); System.out.println(c.getLastRow()); System.out.println(); System.out.println(cell.getRowIndex()); System.out.println(cell.getColumnIndex()); System.out.println("\n\n\n"); // System.out.println(cra); } // System.exit(0); Element td = htmlDocumentFacade.createTableCell(); Object value; switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: value = "\u00a0"; break; case Cell.CELL_TYPE_NUMERIC: value = cell.getNumericCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: value = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_FORMULA: value = cell.getNumericCellValue(); break; default: value = cell.getRichStringCellValue(); break; } if (value instanceof XSSFRichTextString) { processCellStyle(td, cell.getCellStyle(), (XSSFRichTextString) value); td.setTextContent(value.toString()); } else { processCellStyle(td, cell.getCellStyle(), null); td.setTextContent(value.toString()); } // System.err.println(value); tr.appendChild(td); }
/** * 获取每个cell的数据 * * @param cell * @return object */ public Object getXssCellData(Object cell) { Object cellData = ""; if (cell != null) { XSSFCell xssCell = (XSSFCell) cell; if (xssCell != null) { switch (xssCell.getCellType()) { case XSSFCell.CELL_TYPE_STRING: cellData = xssCell.getStringCellValue(); if (cellData != null) { cellData = String.valueOf(cellData).trim(); } break; case XSSFCell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(xssCell)) { SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd"); cellData = df.format(xssCell.getDateCellValue()); df = null; } else { DecimalFormat df = new DecimalFormat("0"); cellData = df.format(xssCell.getNumericCellValue()); df = null; } break; case XSSFCell.CELL_TYPE_BLANK: cellData = ""; break; case XSSFCell.CELL_TYPE_BOOLEAN: cellData = String.valueOf(xssCell.getBooleanCellValue()); break; case XSSFCell.CELL_TYPE_ERROR: break; default: break; } } xssCell = null; } return cellData; }
/** * @param oldCell * @param newCell * @param styleMap */ public static void copyCell( XSSFCell oldCell, XSSFCell newCell, Map<Integer, XSSFCellStyle> styleMap) { if (styleMap != null) { if (oldCell.getSheet().getWorkbook() == newCell.getSheet().getWorkbook()) { newCell.setCellStyle(oldCell.getCellStyle()); } else { int stHashCode = oldCell.getCellStyle().hashCode(); XSSFCellStyle newCellStyle = styleMap.get(stHashCode); if (newCellStyle == null) { newCellStyle = newCell.getSheet().getWorkbook().createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); styleMap.put(stHashCode, newCellStyle); } newCell.setCellStyle(newCellStyle); } } switch (oldCell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getStringCellValue()); break; case HSSFCell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_BLANK: newCell.setCellType(HSSFCell.CELL_TYPE_BLANK); break; case HSSFCell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; default: break; } }