private void createSummerySheet() {
    sheet0 = workbook.createSheet("Summary");
    PrintSetup printSetup = sheet0.getPrintSetup();
    printSetup.setLandscape(true);
    sheet0.setFitToPage(true);
    sheet0.setHorizontallyCenter(true);

    // title row
    Row titleRow = sheet0.createRow(0);
    titleRow.setHeightInPoints(45);
    Cell titleCell = titleRow.createCell(0);
    titleCell.setCellValue("File Health Report");
    titleCell.setCellStyle(styles.get("title"));
    sheet0.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1"));

    for (int i = 0; i < titles.length; i++) {
      Row _row = sheet0.createRow(i + 1);
      Cell headerCell = _row.createCell(0);
      headerCell.setCellValue(titles[i]);
      headerCell.setCellStyle(styles.get("header"));
      _row.setHeightInPoints(20);
    }

    // finally set column widths, the width is measured in units of 1/256th
    // of a character width
    sheet0.setColumnWidth(0, 50 * 256); // 30 characters wide
  }
Exemple #2
0
  public void format() {

    wb = new XSSFWorkbook();

    Map styles = createStyles(wb);
    Sheet sheet = wb.createSheet("Loan Calculator");
    sheet.setPrintGridlines(false);
    sheet.setDisplayGridlines(false);
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);
    sheet.setColumnWidth(0, 768);
    sheet.setColumnWidth(1, 768);
    sheet.setColumnWidth(2, 2816);
    sheet.setColumnWidth(3, 3584);
    sheet.setColumnWidth(4, 3584);
    sheet.setColumnWidth(5, 3584);
    sheet.setColumnWidth(6, 3584);

    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(35F);
    for (int i = 1; i <= 7; i++)
      titleRow.createCell(i).setCellStyle((CellStyle) styles.get("title"));

    Cell titleCell = titleRow.getCell(2);
    titleCell.setCellValue("Simple");
  }
Exemple #3
0
  public static void main(String[] args) throws Exception {
    Workbook wb;

    if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook();
    else wb = new XSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);

    Sheet sheet = wb.createSheet("Timesheet");
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

    // title row
    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(45);
    Cell titleCell = titleRow.createCell(0);
    titleCell.setCellValue("Weekly Timesheet");
    titleCell.setCellStyle(styles.get("title"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1"));

    // header row
    Row headerRow = sheet.createRow(1);
    headerRow.setHeightInPoints(40);
    Cell headerCell;
    for (int i = 0; i < titles.length; i++) {
      headerCell = headerRow.createCell(i);
      headerCell.setCellValue(titles[i]);
      headerCell.setCellStyle(styles.get("header"));
    }

    int rownum = 2;
    for (int i = 0; i < 10; i++) {
      Row row = sheet.createRow(rownum++);
      for (int j = 0; j < titles.length; j++) {
        Cell cell = row.createCell(j);
        if (j == 9) {
          // the 10th cell contains sum over week days, e.g. SUM(C3:I3)
          String ref = "C" + rownum + ":I" + rownum;
          cell.setCellFormula("SUM(" + ref + ")");
          cell.setCellStyle(styles.get("formula"));
        } else if (j == 11) {
          cell.setCellFormula("J" + rownum + "-K" + rownum);
          cell.setCellStyle(styles.get("formula"));
        } else {
          cell.setCellStyle(styles.get("cell"));
        }
      }
    }

    // row with totals below
    Row sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(35);
    Cell cell;
    cell = sumRow.createCell(0);
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellValue("Total Hrs:");
    cell.setCellStyle(styles.get("formula"));

    for (int j = 2; j < 12; j++) {
      cell = sumRow.createCell(j);
      String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12";
      cell.setCellFormula("SUM(" + ref + ")");
      if (j >= 9) cell.setCellStyle(styles.get("formula_2"));
      else cell.setCellStyle(styles.get("formula"));
    }
    rownum++;
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Regular Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("L13");
    cell.setCellStyle(styles.get("formula_2"));
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Overtime Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("K13");
    cell.setCellStyle(styles.get("formula_2"));

    // set sample data
    for (int i = 0; i < sample_data.length; i++) {
      Row row = sheet.getRow(2 + i);
      for (int j = 0; j < sample_data[i].length; j++) {
        if (sample_data[i][j] == null) continue;

        if (sample_data[i][j] instanceof String) {
          row.getCell(j).setCellValue((String) sample_data[i][j]);
        } else {
          row.getCell(j).setCellValue((Double) sample_data[i][j]);
        }
      }
    }

    // finally set column widths, the width is measured in units of 1/256th of a character width
    sheet.setColumnWidth(0, 30 * 256); // 30 characters wide
    for (int i = 2; i < 9; i++) {
      sheet.setColumnWidth(i, 6 * 256); // 6 characters wide
    }
    sheet.setColumnWidth(10, 10 * 256); // 10 characters wide

    // Write the output to a file
    String file = "timesheet.xls";
    if (wb instanceof XSSFWorkbook) file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
  }
  /*
   * import sheet scope content from POI Sheet.
   */
  protected SSheet importSheet(Sheet poiSheet, int poiSheetIndex) {
    SSheet sheet = book.createSheet(poiSheet.getSheetName());
    sheet.setDefaultRowHeight(UnitUtil.twipToPx(poiSheet.getDefaultRowHeight()));
    // ZSS-952
    importSheetDefaultColumnWidth(poiSheet, sheet);
    // reference FreezeInfoLoaderImpl.getRowFreeze()
    sheet.getViewInfo().setNumOfRowFreeze(BookHelper.getRowFreeze(poiSheet));
    sheet.getViewInfo().setNumOfColumnFreeze(BookHelper.getColumnFreeze(poiSheet));
    sheet
        .getViewInfo()
        .setDisplayGridlines(
            poiSheet
                .isDisplayGridlines()); // Note isDisplayGridlines() and isPrintGridlines() are
                                        // different
    sheet.getViewInfo().setColumnBreaks(poiSheet.getColumnBreaks());
    sheet.getViewInfo().setRowBreaks(poiSheet.getRowBreaks());

    SPrintSetup sps = sheet.getPrintSetup();

    SHeader header = sheet.getViewInfo().getHeader();
    if (header != null) {
      header.setCenterText(poiSheet.getHeader().getCenter());
      header.setLeftText(poiSheet.getHeader().getLeft());
      header.setRightText(poiSheet.getHeader().getRight());
      sps.setHeader(header);
    }

    SFooter footer = sheet.getViewInfo().getFooter();
    if (footer != null) {
      footer.setCenterText(poiSheet.getFooter().getCenter());
      footer.setLeftText(poiSheet.getFooter().getLeft());
      footer.setRightText(poiSheet.getFooter().getRight());
      sps.setFooter(footer);
    }

    if (poiSheet.isDiffOddEven()) {
      Header poiEvenHeader = poiSheet.getEvenHeader();
      if (poiEvenHeader != null) {
        SHeader evenHeader = new HeaderFooterImpl();
        evenHeader.setCenterText(poiEvenHeader.getCenter());
        evenHeader.setLeftText(poiEvenHeader.getLeft());
        evenHeader.setRightText(poiEvenHeader.getRight());
        sps.setEvenHeader(evenHeader);
      }
      Footer poiEvenFooter = poiSheet.getEvenFooter();
      if (poiEvenFooter != null) {
        SFooter evenFooter = new HeaderFooterImpl();
        evenFooter.setCenterText(poiEvenFooter.getCenter());
        evenFooter.setLeftText(poiEvenFooter.getLeft());
        evenFooter.setRightText(poiEvenFooter.getRight());
        sps.setEvenFooter(evenFooter);
      }
    }

    if (poiSheet.isDiffFirst()) {
      Header poiFirstHeader = poiSheet.getFirstHeader();
      if (poiFirstHeader != null) {
        SHeader firstHeader = new HeaderFooterImpl();
        firstHeader.setCenterText(poiFirstHeader.getCenter());
        firstHeader.setLeftText(poiFirstHeader.getLeft());
        firstHeader.setRightText(poiFirstHeader.getRight());
        sps.setFirstHeader(firstHeader);
      }
      Footer poiFirstFooter = poiSheet.getFirstFooter();
      if (poiFirstFooter != null) {
        SFooter firstFooter = new HeaderFooterImpl();
        firstFooter.setCenterText(poiFirstFooter.getCenter());
        firstFooter.setLeftText(poiFirstFooter.getLeft());
        firstFooter.setRightText(poiFirstFooter.getRight());
        sps.setFirstFooter(firstFooter);
      }
    }

    PrintSetup poips = poiSheet.getPrintSetup();

    sps.setBottomMargin(poiSheet.getMargin(Sheet.BottomMargin));
    sps.setTopMargin(poiSheet.getMargin(Sheet.TopMargin));
    sps.setLeftMargin(poiSheet.getMargin(Sheet.LeftMargin));
    sps.setRightMargin(poiSheet.getMargin(Sheet.RightMargin));
    sps.setHeaderMargin(poiSheet.getMargin(Sheet.HeaderMargin));
    sps.setFooterMargin(poiSheet.getMargin(Sheet.FooterMargin));

    sps.setAlignWithMargins(poiSheet.isAlignMargins());
    sps.setErrorPrintMode(poips.getErrorsMode());
    sps.setFitHeight(poips.getFitHeight());
    sps.setFitWidth(poips.getFitWidth());
    sps.setHCenter(poiSheet.getHorizontallyCenter());
    sps.setLandscape(poips.getLandscape());
    sps.setLeftToRight(poips.getLeftToRight());
    sps.setPageStart(poips.getUsePage() ? poips.getPageStart() : 0);
    sps.setPaperSize(PoiEnumConversion.toPaperSize(poips.getPaperSize()));
    sps.setCommentsMode(poips.getCommentsMode());
    sps.setPrintGridlines(poiSheet.isPrintGridlines());
    sps.setPrintHeadings(poiSheet.isPrintHeadings());

    sps.setScale(poips.getScale());
    sps.setScaleWithDoc(poiSheet.isScaleWithDoc());
    sps.setDifferentOddEvenPage(poiSheet.isDiffOddEven());
    sps.setDifferentFirstPage(poiSheet.isDiffFirst());
    sps.setVCenter(poiSheet.getVerticallyCenter());

    Workbook poiBook = poiSheet.getWorkbook();
    String area = poiBook.getPrintArea(poiSheetIndex);
    if (area != null) {
      sps.setPrintArea(area);
    }

    CellRangeAddress rowrng = poiSheet.getRepeatingRows();
    if (rowrng != null) {
      sps.setRepeatingRowsTitle(rowrng.getFirstRow(), rowrng.getLastRow());
    }

    CellRangeAddress colrng = poiSheet.getRepeatingColumns();
    if (colrng != null) {
      sps.setRepeatingColumnsTitle(colrng.getFirstColumn(), colrng.getLastColumn());
    }

    sheet.setPassword(poiSheet.getProtect() ? "" : null);

    // import hashed password directly
    importPassword(poiSheet, sheet);

    // ZSS-832
    // import sheet visible
    if (poiBook.isSheetHidden(poiSheetIndex)) {
      sheet.setSheetVisible(SheetVisible.HIDDEN);
    } else if (poiBook.isSheetVeryHidden(poiSheetIndex)) {
      sheet.setSheetVisible(SheetVisible.VERY_HIDDEN);
    } else {
      sheet.setSheetVisible(SheetVisible.VISIBLE);
    }

    // ZSS-1130
    // import conditionalFormatting
    importConditionalFormatting(sheet, poiSheet);
    return sheet;
  }
Exemple #5
0
  private void printSetup(Sheet oldSheet, Sheet newSheet) {
    // print setup
    PrintSetup oldPS = oldSheet.getPrintSetup();
    PrintSetup newPS = newSheet.getPrintSetup();

    newPS.setPaperSize(oldPS.getPaperSize());
    newPS.setScale(oldPS.getScale());
    newPS.setPageStart(oldPS.getPageStart());
    newPS.setFitWidth(oldPS.getFitWidth());
    newPS.setFitHeight(oldPS.getFitHeight());
    newPS.setLeftToRight(oldPS.getLeftToRight());
    newPS.setLandscape(oldPS.getLandscape());
    newPS.setValidSettings(oldPS.getValidSettings());
    newPS.setNoColor(oldPS.getNoColor());
    newPS.setDraft(oldPS.getDraft());
    newPS.setNotes(oldPS.getNotes());
    newPS.setNoOrientation(oldPS.getNoOrientation());
    newPS.setUsePage(oldPS.getUsePage());
    newPS.setHResolution(oldPS.getHResolution());
    newPS.setVResolution(oldPS.getVResolution());
    newPS.setHeaderMargin(oldPS.getHeaderMargin());
    newPS.setFooterMargin(oldPS.getFooterMargin());
    newPS.setCopies(oldPS.getCopies());

    newSheet.setRightToLeft(oldSheet.isRightToLeft());
    newSheet.setHorizontallyCenter(oldSheet.getHorizontallyCenter());
    newSheet.setVerticallyCenter(oldSheet.getVerticallyCenter());
    newSheet.setAutobreaks(oldSheet.getAutobreaks());
    newSheet.setDisplayZeros(oldSheet.isDisplayZeros());
    newSheet.setFitToPage(oldSheet.getFitToPage());
    newSheet.setAutobreaks(oldSheet.getAutobreaks());
    newSheet.setPrintGridlines(oldSheet.isPrintGridlines());

    newSheet.getHeader().setCenter(oldSheet.getHeader().getCenter());
    newSheet.getHeader().setLeft(oldSheet.getHeader().getLeft());
    newSheet.getHeader().setRight(oldSheet.getHeader().getRight());

    newSheet.getFooter().setCenter(oldSheet.getFooter().getCenter());
    newSheet.getFooter().setLeft(oldSheet.getFooter().getLeft());
    newSheet.getFooter().setRight(oldSheet.getFooter().getRight());

    newSheet.setMargin(Sheet.LeftMargin, oldSheet.getMargin(Sheet.LeftMargin));
    newSheet.setMargin(Sheet.RightMargin, oldSheet.getMargin(Sheet.RightMargin));
    newSheet.setMargin(Sheet.HeaderMargin, oldSheet.getMargin(Sheet.HeaderMargin));
    newSheet.setMargin(Sheet.FooterMargin, oldSheet.getMargin(Sheet.FooterMargin));
    newSheet.setMargin(Sheet.BottomMargin, oldSheet.getMargin(Sheet.BottomMargin));
    newSheet.setMargin(Sheet.TopMargin, oldSheet.getMargin(Sheet.TopMargin));
  }