List of usage examples for org.apache.poi.ss.usermodel Sheet setFitToPage
void setFitToPage(boolean value);
From source file:se.mithlond.services.content.impl.ejb.report.ExcelReportServiceBean.java
License:Apache License
/** * {@inheritDoc}/*from w w w . j a va 2 s. c o m*/ */ @Override public Sheet createStandardExcelSheet(@NotNull final Workbook workbook, @NotNull final String sheetName, @NotNull final String sheetTitle, @NotNull final List<String> columnTitles) { // Check sanity Validate.notEmpty(sheetName, "sheetName"); Validate.notEmpty(sheetTitle, "sheetTitle"); Validate.notEmpty(columnTitles, "columnTitles"); Validate.notNull(workbook, "workbook"); // Create a new Workbook if required. final LocalDateTime timestamp = LocalDateTime.now(); final String now = TimeFormat.YEAR_MONTH_DATE_HOURS_MINUTES.print(timestamp).replace(":", " "); // Create the Sheet to return final Sheet toReturn = workbook.createSheet(sheetName + "_" + now); toReturn.setFitToPage(true); toReturn.setHorizontallyCenter(true); // Create a "Title" row containing a single cell (i.e. merged cells) // and where the sheet title is presented and centered. final Row titleRow = toReturn.createRow(0); titleRow.setHeightInPoints(45); final Cell titleCell = titleRow.createCell(0); titleCell.setCellValue(sheetTitle + " " + now); titleCell.setCellStyle(getCellStyle(ExcelElement.TITLE, workbook)); // toReturn.addMergedRegion(CellRangeAddress.valueOf("$A$1:$E$1")); toReturn.addMergedRegion(CellRangeAddress .valueOf("$A$1:$" + CellReference.convertNumToColString(columnTitles.size() - 1) + "1")); // Create a header Row with the column names defined above. final Row headerRow = toReturn.createRow(1); // headerRow.setHeightInPoints(40); // This *could* adjust the header row to fit its internal height. titleRow.setHeight((short) -1); Cell headerCell; for (int i = 0; i < columnTitles.size(); i++) { headerCell = headerRow.createCell(i); headerCell.setCellValue(columnTitles.get(i)); headerCell.setCellStyle(getCellStyle(ExcelElement.HEADER, workbook)); toReturn.setDefaultColumnStyle(i, getCellStyle(ExcelElement.NON_WRAPPING, workbook)); toReturn.autoSizeColumn(i); } // All done return toReturn; }
From source file:servlet.exportScoreSheet.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods./*from w w w . j a va 2 s. com*/ * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { HttpSession ss = request.getSession(); Account ac = (Account) ss.getAttribute("ac"); int cId = Integer.parseInt((Long) ss.getAttribute("cId") + ""); Course c = Course.getCourseByID(cId); Workbook wb = new XSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("scoresheet"); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(45); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue("Score sheet of " + c.getName() + " course"); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1")); List<Account> listStudentScore = (List<Account>) ss.getAttribute("listStudentScore"); int rownum = 2; int cellcount = 1; Row sumRow = sheet.createRow(rownum); sumRow.setHeightInPoints(55); Cell cell; cell = sumRow.createCell(0); cell.setCellValue("Student name"); cell.setCellStyle(styles.get("header")); int countback = listStudentScore.get(0).getListStudentScore().size(); int maxScore = 0; for (int i = countback - 1; i >= 0; i--) { cell = sumRow.createCell(cellcount); UserScore u = listStudentScore.get(0).getListStudentScore().get(i); cell.setCellValue("(" + cellcount + ") " + u.getAm_name() + " (" + u.getFull_mark() + ")"); cell.setCellStyle(styles.get("header")); cellcount++; maxScore += u.getFull_mark(); } cell = sumRow.createCell(cellcount); cell.setCellValue("Total (" + maxScore + ")"); cell.setCellStyle(styles.get("header")); rownum++; for (Account account : listStudentScore) { sumRow = sheet.createRow(rownum); sumRow.setHeightInPoints(35); cell = sumRow.createCell(0); cell.setCellValue(account.getFirstname() + " " + account.getLastname()); int j = 1; for (int i = account.getListStudentScore().size() - 1; i >= 0; i--) { UserScore usc = (UserScore) account.getListStudentScore().get(i); cell = sumRow.createCell(j); Assignment a = null; if (usc.getAss_type().equalsIgnoreCase("web")) { a = Assignment.getAmTimeByAmID(usc.getStof().getAm_id()); String status = Assignment.lastedSentStatus(usc.getStof().getLasted_send_date(), a); if (status.equalsIgnoreCase("ontime") || status.equalsIgnoreCase("hurryup") || status.equalsIgnoreCase("late")) { cell.setCellValue(usc.getStof().getScore()); } else { status = Assignment.calculateTime(a); if (status.equalsIgnoreCase("miss")) { cell.setCellValue(usc.getStof().getScore()); } else { cell.setCellValue("-"); } } } else if (usc.getAss_type().equalsIgnoreCase("file")) { a = Assignment.getAmTimeByAmID(usc.getStf().getAm_id()); String status = Assignment.lastedSentStatus(usc.getStf().getLasted_send_date(), a); if (status.equalsIgnoreCase("ontime") || status.equalsIgnoreCase("hurryup") || status.equalsIgnoreCase("late")) { cell.setCellValue(usc.getStf().getScore()); } else { status = Assignment.calculateTime(a); if (status.equalsIgnoreCase("miss")) { cell.setCellValue(usc.getStf().getScore()); } else { cell.setCellValue("-"); } } } j++; } cell = sumRow.createCell(j); int lastcol = account.getListStudentScore().size(); //calculate column int dv = lastcol / 26; String coltmp = ""; for (int i = 0; i < dv; i++) { coltmp += "A"; } coltmp += (char) ('A' + (lastcol - (dv * 26))); System.out.println(coltmp); // String ref = (char) ('A' + 1) + "" + (rownum + 1) + ":" + coltmp + (rownum + 1); System.out.println(ref); cell.setCellFormula("SUM(" + ref + ")"); rownum++; } // Write the output to a file String filename = "scoresheet_" + c.getName() + ".xlsx"; String file = getServletContext().getRealPath("/") + "/file/scoresheet/" + filename; // String file = "C:\\Users\\Orarmor\\Desktop\\scoresheet.xlsx"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); response.sendRedirect("file/scoresheet/" + filename); // // Workbook wb = new XSSFWorkbook(); // Sheet sheet = wb.createSheet("scoresheet"); // 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("Score sheet of " + "...." + " course"); // sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$D$1")); // // //row with totals below // int rownum = 2; // Row sumRow = sheet.createRow(rownum); // sumRow.setHeightInPoints(35); // Cell cell; // cell = sumRow.createCell(0); // cell.setCellValue("Name:"); // // for (int j = 1; j < 12; j++) { // cell = sumRow.createCell(j); // String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12"; // cell.setCellFormula("SUM(" + ref + ")"); // } // // // Write the output to a file // String file = "C:\\Users\\Orarmor\\Desktop\\scoresheet.xlsx"; // FileOutputStream out = new FileOutputStream(file); // wb.write(out); // out.close(); }
From source file:test.poi.LoanCalculator.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb = new HSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Loan Calculator"); sheet.setPrintGridlines(false);// w ww .j a v a 2 s .c o m sheet.setDisplayGridlines(false); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); sheet.setColumnWidth(0, 3 * 256); sheet.setColumnWidth(1, 3 * 256); sheet.setColumnWidth(2, 11 * 256); sheet.setColumnWidth(3, 14 * 256); sheet.setColumnWidth(4, 14 * 256); sheet.setColumnWidth(5, 14 * 256); sheet.setColumnWidth(6, 14 * 256); createNames(wb); Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(35); for (int i = 1; i <= 7; i++) { titleRow.createCell(i).setCellStyle(styles.get("title")); } Cell titleCell = titleRow.getCell(2); titleCell.setCellValue("Simple Loan Calculator"); sheet.addMergedRegion(CellRangeAddress.valueOf("$C$1:$H$1")); Row row = sheet.createRow(2); Cell cell = row.createCell(4); cell.setCellValue("Enter values"); cell.setCellStyle(styles.get("item_right")); row = sheet.createRow(3); cell = row.createCell(2); cell.setCellValue("Loan amount"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_$")); cell.setAsActiveCell(); row = sheet.createRow(4); cell = row.createCell(2); cell.setCellValue("Annual interest rate"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_%")); row = sheet.createRow(5); cell = row.createCell(2); cell.setCellValue("Loan period in years"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_i")); row = sheet.createRow(6); cell = row.createCell(2); cell.setCellValue("Start date of loan"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_d")); row = sheet.createRow(8); cell = row.createCell(2); cell.setCellValue("Monthly payment"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Monthly_Payment,\"\")"); cell.setCellStyle(styles.get("formula_$")); row = sheet.createRow(9); cell = row.createCell(2); cell.setCellValue("Number of payments"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Loan_Years*12,\"\")"); cell.setCellStyle(styles.get("formula_i")); row = sheet.createRow(10); cell = row.createCell(2); cell.setCellValue("Total interest"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Total_Cost-Loan_Amount,\"\")"); cell.setCellStyle(styles.get("formula_$")); row = sheet.createRow(11); cell = row.createCell(2); cell.setCellValue("Total cost of loan"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Monthly_Payment*Number_of_Payments,\"\")"); cell.setCellStyle(styles.get("formula_$")); // Write the output to a file String file = "E:/loan-calculator.xls"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:test.poi.MyExcelDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb = new HSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Timesheet"); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true);/*from w w w . j a va 2 s. c o m*/ sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); //title row Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(45); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue("XX????20130506-20140503"); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$O$1")); //header row Row headerRow = sheet.createRow(1); sheet.setDefaultColumnWidth(20); headerRow.setHeightInPoints(20); Cell headerCell; for (int i = 1; i < titles.length; i++) { headerCell = headerRow.createCell(i); headerCell.setCellValue(titles[i - 1]); CellRangeAddress cra = new CellRangeAddress(1, 2, i, i); sheet.addMergedRegion(cra); setBorder(cra, sheet, wb); headerCell.setCellStyle(styles.get("header")); } // Write the output to a file String file = "E:\\test.xls"; // if(wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:test.poi.TimesheetDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb = new HSSFWorkbook(); // if(args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); // else wb = new Workbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Timesheet"); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true);//from w w w . j a v a2s . co m 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 = "E:\\timesheet.xls"; // if(wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:vistas.reportes.procesos.pruebaExcel.java
public static void main(String[] args) throws Exception { Workbook wb;/*from www .j a v a2s .c o m*/ 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(); try { File path = new File(file); Desktop.getDesktop().open(path); } catch (IOException ex) { ex.printStackTrace(); } }
From source file:vistas.reportes.procesos.rptVacacionesExcel.java
public void crearExcel(List<Empleado> empleados, Date fechaInicio, Date fechaFin) throws FileNotFoundException, IOException { Workbook wb;// w ww . j a v a2 s . com wb = new XSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Vacaciones"); 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); SimpleDateFormat formateador = new SimpleDateFormat("dd/MM/yyyy"); titleCell.setCellValue( "VACACIONES MINEDU " + formateador.format(fechaInicio) + " - " + formateador.format(fechaFin)); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$D$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 < empleados.size(); i++) { Row row = sheet.createRow(rownum++); for (int j = 0; j < titles.length; j++) { Cell cell = row.createCell(j); cell.setCellStyle(styles.get("cell")); } } for (int i = 0; i < empleados.size(); i++) { Row row = sheet.getRow(2 + i); row.getCell(0).setCellValue(empleados.get(i).getNroDocumento()); row.getCell(1).setCellValue(empleados.get(i).getNombre() + " " + empleados.get(i).getApellidoPaterno()); row.getCell(2).setCellValue("Departamento"); Vacacion vacaciones = vc.buscarXDia(empleados.get(i).getNroDocumento(), fechaInicio); if (vacaciones != null) { row.getCell(3).setCellValue(formateador.format(vacaciones.getFechaInicio()) + " al " + formateador.format(vacaciones.getFechaFin())); } else { row.getCell(3).setCellValue("No tiene vacaciones"); } } sheet.setColumnWidth(0, 10 * 256); sheet.setColumnWidth(1, 30 * 256); sheet.setColumnWidth(2, 30 * 256); sheet.setColumnWidth(3, 25 * 256); //Write the output to a file String file = "rptVacaciones.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); try { File path = new File(file); Desktop.getDesktop().open(path); } catch (IOException ex) { ex.printStackTrace(); } }