Beispiel #1
0
  /*
   * Don't hardcode US Locale for datetimes in Excel.  Experiment a bit.
   */
  public static void issue26() throws InvalidFormatException, IOException {

    System.out.println("Testing issue26 =================================");
    Workbook wb = new XSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();
    Sheet sheet = wb.createSheet("Sheet1");
    Row row = sheet.createRow(0);

    // first cell
    Cell cell0 = row.createCell(0);
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm:ss"));
    cell0.setCellValue(new Date(1385903303326L)); // 12/1/2013 08:08 AM
    cell0.setCellStyle(cellStyle);

    // second cell using another format with French locale
    CellStyle cs2 = wb.createCellStyle();
    String excelFormatPrefix = DateFormatConverter.getPrefixForLocale(Locale.FRENCH);
    System.out.println("The LOCALE prefix is: " + excelFormatPrefix);
    String excelFormatPattern = excelFormatPrefix + "dd MMM, yyyy;@";
    System.out.println(
        "Converted pattern in FRENCH locale is: "
            + DateFormatConverter.convert(Locale.FRENCH, "m/d/yy h:mm:ss"));

    DataFormat df = wb.createDataFormat();
    cs2.setDataFormat(df.getFormat(excelFormatPattern));
    Cell cell1 = row.createCell(1);
    cell1.setCellValue(new Date(1385903303326L));
    cell1.setCellStyle(cs2);

    FileOutputStream out = new FileOutputStream("/tmp/issue26_out.xlsx");
    wb.write(out);
    out.close();
    System.out.println("Wrote /tmp/issue26_out.xlsx");
  }
Beispiel #2
0
  /**
   * Creates a new instance.
   *
   * @param workbook target workbook
   * @throws IllegalArgumentException if some parameters were {@code null}
   */
  WorkbookInfo(Workbook workbook) {
    if (workbook == null) {
      throw new IllegalArgumentException("workbook must not be null"); // $NON-NLS-1$
    }
    this.workbook = workbook;

    Font font = workbook.createFont();

    commonStyle = workbook.createCellStyle();
    commonStyle.setFont(font);
    commonStyle.setBorderTop(BorderStyle.THIN);
    commonStyle.setBorderBottom(BorderStyle.THIN);
    commonStyle.setBorderLeft(BorderStyle.THIN);
    commonStyle.setBorderRight(BorderStyle.THIN);

    titleStyle = workbook.createCellStyle();
    titleStyle.cloneStyleFrom(commonStyle);
    titleStyle.setLocked(true);
    titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    titleStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
    titleStyle.setAlignment(HorizontalAlignment.CENTER);

    CreationHelper helper = workbook.getCreationHelper();
    DataFormat df = helper.createDataFormat();

    dataStyle = workbook.createCellStyle();
    dataStyle.cloneStyleFrom(commonStyle);

    dateDataStyle = workbook.createCellStyle();
    dateDataStyle.cloneStyleFrom(commonStyle);
    dateDataStyle.setDataFormat(df.getFormat("yyyy-mm-dd")); // $NON-NLS-1$

    timeDataStyle = workbook.createCellStyle();
    timeDataStyle.cloneStyleFrom(commonStyle);
    timeDataStyle.setDataFormat(df.getFormat("hh:mm:ss")); // $NON-NLS-1$

    datetimeDataStyle = workbook.createCellStyle();
    datetimeDataStyle.cloneStyleFrom(commonStyle);
    datetimeDataStyle.setDataFormat(df.getFormat("yyyy-mm-dd hh:mm:ss")); // $NON-NLS-1$
  }
  public CellStyle getNumberFormat(
      int j,
      Map<Integer, CellStyle> decimalFormats,
      Sheet sheet,
      CreationHelper createHelper,
      CellType celltype) {

    int mapPosition = j;

    if (celltype.equals(CellType.TOTAL)) {
      mapPosition = j + 90000;
    } else if (celltype.equals(CellType.SUBTOTAL)) {
      mapPosition = j + 80000;
    } else if (celltype.equals(CellType.CF)) {
      mapPosition = j + 60000;
    }

    if (decimalFormats.get(mapPosition) != null) return decimalFormats.get(mapPosition);

    if (celltype.equals(CellType.CF)) {
      j = this.getCalculatedFieldDecimals();
    }

    String decimals = "";

    for (int i = 0; i < j; i++) {
      decimals += "0";
    }

    CellStyle cellStyle = this.buildDataCellStyle(sheet);
    DataFormat df = createHelper.createDataFormat();
    String format = "#,##0";
    if (decimals.length() > 0) {
      format += "." + decimals;
    }
    cellStyle.setDataFormat(df.getFormat(format));

    if (celltype.equals(CellType.TOTAL)) {
      cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
    }
    if (celltype.equals(CellType.CF)) {
      cellStyle.setFillForegroundColor(IndexedColors.DARK_YELLOW.getIndex());
    }
    if (celltype.equals(CellType.SUBTOTAL)) {
      cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    }

    decimalFormats.put(mapPosition, cellStyle);
    return cellStyle;
  }
 /**
  * 添加一个单元格
  *
  * @param row 添加的行
  * @param column 添加列号
  * @param val 添加值
  * @param align 对齐方式(1:靠左;2:居中;3:靠右)
  * @return 单元格对象
  */
 public Cell addCell(Row row, int column, Object val, int align, Class<?> fieldType) {
   Cell cell = row.createCell(column);
   CellStyle style = styles.get("data" + (align >= 1 && align <= 3 ? align : ""));
   try {
     if (val == null) {
       cell.setCellValue("");
     } else if (val instanceof String) {
       cell.setCellValue((String) val);
     } else if (val instanceof Integer) {
       cell.setCellValue((Integer) val);
     } else if (val instanceof Long) {
       cell.setCellValue((Long) val);
     } else if (val instanceof Double) {
       cell.setCellValue((Double) val);
     } else if (val instanceof Float) {
       cell.setCellValue((Float) val);
     } else if (val instanceof Date) {
       DataFormat format = wb.createDataFormat();
       style.setDataFormat(format.getFormat("yyyy-MM-dd"));
       cell.setCellValue((Date) val);
     } else {
       if (fieldType != Class.class) {
         cell.setCellValue(
             (String) fieldType.getMethod("setValue", Object.class).invoke(null, val));
       } else {
         cell.setCellValue(
             (String)
                 Class.forName(
                         this.getClass()
                             .getName()
                             .replaceAll(
                                 this.getClass().getSimpleName(),
                                 "fieldtype." + val.getClass().getSimpleName() + "Type"))
                     .getMethod("setValue", Object.class)
                     .invoke(null, val));
       }
     }
   } catch (Exception ex) {
     log.info("Set cell value [" + row.getRowNum() + "," + column + "] error: " + ex.toString());
     cell.setCellValue(val.toString());
   }
   cell.setCellStyle(style);
   return cell;
 }
Beispiel #5
0
  public void setCellMoney(int numRow, int numCol) {
    try {
      if (wb.getSheetAt(0) != null) {
        Sheet aSheet = wb.getSheetAt(0);
        DataFormat format = wb.createDataFormat();
        Row row = aSheet.getRow((short) numRow);
        Cell csCell = row.getCell((short) numCol);
        CellStyle style = wb.createCellStyle();
        style.setDataFormat(format.getFormat("0.00"));
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.BLACK.index);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        csCell.setCellStyle(style);
      }
    } catch (Exception e) {
      System.out.println("insertDataToExcel" + e);
    }
  }
Beispiel #6
0
  private static Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();

    short borderColor = IndexedColors.GREY_50_PERCENT.getIndex();

    CellStyle style;
    DataFormat format = wb.createDataFormat();
    Font titleFont = wb.createFont();
    titleFont.setFontName("Tahoma");
    titleFont.setFontHeightInPoints((short) 12);
    titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFont(titleFont);
    styles.put("title", style);

    Font headFont = wb.createFont();
    headFont.setFontName("Tahoma");
    headFont.setFontHeightInPoints((short) 11);
    headFont.setColor(IndexedColors.WHITE.getIndex());
    headFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(headFont);
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setWrapText(true);
    styles.put("header", style);

    Font groupFont = wb.createFont();
    groupFont.setFontName("Tahoma");
    groupFont.setFontHeightInPoints((short) 11);
    groupFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setWrapText(true);
    style.setFont(groupFont);
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    styles.put("groupleft", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setWrapText(true);
    style.setFont(groupFont);
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    styles.put("groupright", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setWrapText(true);
    /*  style.setFont(groupFont);  */
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    styles.put("groupcenter", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setWrapText(true);
    style.setDataFormat(format.getFormat("#,##0"));
    /*  style.setFont(groupFont); */
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    styles.put("groupnumber", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    style.setWrapText(true);
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    /*        style.setBorderTop(CellStyle.BORDER_THIN);
            style.setTopBorderColor(IndexedColors.BLACK.getIndex());
            style.setBorderBottom(CellStyle.BORDER_THIN);
            style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    */ styles.put("cellleft", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    style.setWrapText(true);
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    styles.put("cellcenter", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    style.setWrapText(true);
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    styles.put("cellright", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    style.setWrapText(true);
    style.setDataFormat(format.getFormat("#,##0"));
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    styles.put("cellnumber", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    style.setWrapText(true);
    style.setDataFormat(format.getFormat("#,##0.00"));
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    styles.put("cellnumber2", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    style.setWrapText(true);
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    styles.put("celltop", style);

    return styles;
  }
  /*
   * (non-Javadoc)
   *
   * @see
   * com.toolsverse.etl.connector.DataSetConnector#prePersist(com.toolsverse
   * .etl.connector.DataSetConnectorParams, com.toolsverse.etl.common.DataSet,
   * com.toolsverse.etl.driver.Driver)
   */
  @SuppressWarnings("resource")
  public void prePersist(ExcelConnectorParams params, DataSet dataSet, Driver driver)
      throws Exception {
    String fileName = null;

    OutputStream out = null;

    if (params.getOutputStream() == null) {
      fileName =
          SystemConfig.instance()
              .getPathUsingAppFolders(
                  params.getFileName(
                      dataSet.getOwnerName() != null ? dataSet.getOwnerName() : dataSet.getName(),
                      ".xls",
                      true));

      params.setRealFileName(fileName);

      out = new FileOutputStream(fileName);

      if (params.getTransactionMonitor() != null) params.getTransactionMonitor().addFile(fileName);
    } else out = params.getOutputStream();

    params.setOut(out);

    Workbook workbook = new HSSFWorkbook();

    params.setWorkbook(workbook);

    Sheet sheet =
        workbook.createSheet(
            Utils.isNothing(params.getSheetName()) ? dataSet.getName() : params.getSheetName());

    params.setSheet(sheet);

    Font labelFont = workbook.createFont();
    labelFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    CellStyle labelCellStyle = workbook.createCellStyle();
    labelCellStyle.setFont(labelFont);

    DataFormat dateTimeFormat = workbook.createDataFormat();
    CellStyle dateTimeCellStyle = workbook.createCellStyle();
    dateTimeCellStyle.setDataFormat(dateTimeFormat.getFormat(params.getDateTimeFormat()));

    params.setDateTimeCellStyle(dateTimeCellStyle);

    DataFormat dateFormat = workbook.createDataFormat();
    CellStyle dateCellStyle = workbook.createCellStyle();
    dateCellStyle.setDataFormat(dateFormat.getFormat(params.getDateFormat()));

    params.setDateCellStyle(dateCellStyle);

    DataFormat timeFormat = workbook.createDataFormat();
    CellStyle timeCellStyle = workbook.createCellStyle();
    timeCellStyle.setDataFormat(timeFormat.getFormat(params.getTimeFormat()));

    params.setTimeCellStyle(timeCellStyle);

    // column names
    Row excelRow = sheet.createRow(0);

    // metadata
    int col = 0;
    for (FieldDef fieldDef : dataSet.getFields().getList()) {
      if (!fieldDef.isVisible()) continue;

      Cell labelCell = excelRow.createCell(col++, Cell.CELL_TYPE_STRING);
      labelCell.setCellStyle(labelCellStyle);
      labelCell.setCellValue(fieldDef.getName());
    }

    params.setPrePersistOccured(true);
  }