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 }
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"); }
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)); }
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(); }