Esempio n. 1
0
  // 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();
   }
 }
Esempio n. 3
0
  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;
   }
 }