List of usage examples for org.apache.poi.ss.usermodel PrintSetup setLandscape
void setLandscape(boolean ls);
From source file:output.ExcelM3Upgrad.java
private void writeMigration() { Sheet sheet = workbook.getSheetAt(0); workbook.setSheetName(0, "Migration"); sheet.setDisplayGridlines(false);//from w w w . j a v a 2s. co m sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); styles = createStyles(workbook); int rownum = beginROW; int cellnum = beginCOL; Row row = sheet.createRow(rownum++); for (int k = 0; k < model.getListColumn().length; k++) { Cell cell = row.createCell(cellnum++); cell.setCellValue(i18n.Language.getLabel(model.getListColumn()[k].getIdLng())); cell.setCellStyle(styles.get("header")); sheet.setColumnHidden(cell.getColumnIndex(), model.getListColumn()[k].isHidden()); sheet.autoSizeColumn(k); dialStatus(); } ArrayList<Integer> listHeader = new ArrayList<>(); for (int i = 0; i < M3UpdObjModel.header.length; i++) { listHeader.add(M3UpdObjModel.header[i]); } String[] listLevel = i18n.Language.traduce(Ressource.listLevel) .toArray(new String[Ressource.listLevel.length]); data = model.getData(); for (int i = 0; i < data.length; i++) { busyDial.setText("Alimentation de la ligne " + (i + 1) + " sur " + data.length); row = sheet.createRow(rownum++); Object[] objArr = data[i]; cellnum = beginCOL; boolean first = true; int j = 0; for (Object obj : objArr) { Cell cell = row.createCell(cellnum++); if (obj instanceof Date) { cell.setCellValue((Date) obj); } else if (obj instanceof Boolean) { if (first) { first = false; if ((Boolean) obj) { cell.setCellValue("Oui"); } else { cell.setCellValue("Non"); } } else { if ((Boolean) obj) { cell.setCellValue("OK"); } else { cell.setCellValue("KO"); } } } else if (obj instanceof String) { cell.setCellValue((String) obj); } else if (obj instanceof Double) { cell.setCellValue((Double) obj); } if (listHeader.indexOf(218) == j) { try { int n = Integer.parseInt(obj.toString().trim()); if (n == -1) { cell.setCellValue("ERROR"); } else { cell.setCellValue(listLevel[n]); } } catch (NumberFormatException ex) { cell.setCellValue(""); } } if (j < objArr.length - 3) { cell.setCellStyle(styles.get("cell_b_centered_locked")); } else { cell.setCellStyle(styles.get("cell_b_centered")); } j++; dialStatus(); } dialStatus(); } dialStatus(); busyDial.setText("Formatage du document"); CellRangeAddressList userList = new CellRangeAddressList(beginROW + 1, beginROW + data.length, beginCOL + data[0].length - 1, beginCOL + data[0].length - 1); DataValidationConstraint userConstraint; DataValidation userValidation; if (type == 0) { userConstraint = DVConstraint.createExplicitListConstraint((String[]) model.getM3UserModel() .getListUserSelect().toArray(new String[model.getM3UserModel().getListUserSelect().size()])); userValidation = new HSSFDataValidation(userList, userConstraint); } else { XSSFDataValidationHelper userHelper = new XSSFDataValidationHelper((XSSFSheet) sheet); userConstraint = (XSSFDataValidationConstraint) userHelper .createExplicitListConstraint((String[]) model.getM3UserModel().getListUserSelect() .toArray(new String[model.getM3UserModel().getListUserSelect().size()])); userValidation = (XSSFDataValidation) userHelper.createValidation(userConstraint, userList); } sheet.addValidationData(userValidation); CellRangeAddressList migList = new CellRangeAddressList(beginROW + 1, beginROW + data.length, beginCOL + data[0].length - 2, beginCOL + data[0].length - 2); DataValidationConstraint migConstraint; DataValidation migValidation; if (type == 0) { migConstraint = DVConstraint.createExplicitListConstraint(new String[] { "OK", "KO" }); migValidation = new HSSFDataValidation(migList, migConstraint); } else { XSSFDataValidationHelper migHelper = new XSSFDataValidationHelper((XSSFSheet) sheet); migConstraint = (XSSFDataValidationConstraint) migHelper .createExplicitListConstraint(new String[] { "OK", "KO" }); migValidation = (XSSFDataValidation) migHelper.createValidation(migConstraint, migList); } sheet.addValidationData(migValidation); CellRangeAddressList levelList = new CellRangeAddressList(beginROW + 1, beginROW + data.length, beginCOL + data[0].length - 3, beginCOL + data[0].length - 3); DataValidationConstraint levelConstraint; DataValidation levelValidation; ArrayList<String> listNameLevel = new ArrayList<>(); listNameLevel.add("ERROR"); listNameLevel.addAll(i18n.Language.traduce(Ressource.listLevel));//.toArray(new String[Ressource.listLevel.length]) if (type == 0) { levelConstraint = DVConstraint .createExplicitListConstraint(listNameLevel.toArray(new String[listNameLevel.size()])); levelValidation = new HSSFDataValidation(levelList, levelConstraint); } else { XSSFDataValidationHelper levelHelper = new XSSFDataValidationHelper((XSSFSheet) sheet); levelConstraint = (XSSFDataValidationConstraint) levelHelper.createExplicitListConstraint( i18n.Language.traduce(Ressource.listLevel).toArray(new String[Ressource.listLevel.length])); levelValidation = (XSSFDataValidation) levelHelper.createValidation(levelConstraint, levelList); } sheet.addValidationData(levelValidation); int irow = beginROW; int icol = beginCOL + model.getListColumn().length + 2; row = sheet.getRow(irow); Cell cell = row.createCell(icol); sheet.addMergedRegion(new CellRangeAddress(irow, irow, icol, icol + 1)); cell.setCellValue("Estimation de la charge"); cell.setCellStyle(styles.get("header")); irow++; row = sheet.getRow(irow); int cpt = 0; ArrayList<String> listStringLevel = i18n.Language.traduce(Ressource.listLevel); for (String s : listStringLevel) { cell = row.createCell(icol); cell.setCellValue(s); cell.setCellStyle(styles.get("cell_b_centered_locked")); cell = row.createCell(icol + 1); cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); String columnLetter = CellReference.convertNumToColString(listHeader.indexOf(218) + beginCOL); cell.setCellFormula( "COUNTIF(" + workbook.getSheetName(0) + "!" + columnLetter + (beginROW + 2) + ":" + columnLetter + (beginROW + data.length + 1) + ",\"" + s + "\")*" + Ressource.listWeightLevel[cpt]); cell.setCellStyle(styles.get("cell_b_centered_locked")); irow++; row = sheet.getRow(irow); cpt++; } row = sheet.getRow(irow); cell = row.createCell(icol); cell.setCellValue("Total des charges"); cell.setCellStyle(styles.get("cell_b_centered_locked")); cell = row.createCell(icol + 1); cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); String columnLetter = CellReference.convertNumToColString(listHeader.indexOf(icol + 1)); cell.setCellFormula("SUM(" + workbook.getSheetName(0) + "!" + columnLetter + (beginROW + 2) + ":" + columnLetter + (beginROW + Ressource.listLevel.length + 1) + ")"); cell.setCellStyle(styles.get("cell_b_centered_locked")); for (int k = 0; k < model.getListColumn().length + 3; k++) { sheet.autoSizeColumn(k); } sheet.protectSheet("3kles2014"); }
From source file:project1.TimesheetDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;// w w w . j a va 2 s.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(); }
From source file:ro.dabuno.office.integration.BusinessPlan.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;//from w w w .ja v a2 s. co 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("Business Plan"); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); //the following three statements are required only for HSSF sheet.setAutobreaks(true); printSetup.setFitHeight((short) 1); printSetup.setFitWidth((short) 1); //the header row: centered text in 48pt font Row headerRow = sheet.createRow(0); headerRow.setHeightInPoints(12.75f); for (int i = 0; i < titles.length; i++) { Cell cell = headerRow.createCell(i); cell.setCellValue(titles[i]); cell.setCellStyle(styles.get("header")); } //columns for 11 weeks starting from 9-Jul Calendar calendar = Calendar.getInstance(); int year = calendar.get(Calendar.YEAR); calendar.setTime(fmt.parse("9-Jul")); calendar.set(Calendar.YEAR, year); for (int i = 0; i < 11; i++) { Cell cell = headerRow.createCell(titles.length + i); cell.setCellValue(calendar); cell.setCellStyle(styles.get("header_date")); calendar.roll(Calendar.WEEK_OF_YEAR, true); } //freeze the first row sheet.createFreezePane(0, 1); Row row; Cell cell; int rownum = 1; for (int i = 0; i < data.length; i++, rownum++) { row = sheet.createRow(rownum); if (data[i] == null) continue; for (int j = 0; j < data[i].length; j++) { cell = row.createCell(j); String styleName; boolean isHeader = i == 0 || data[i - 1] == null; switch (j) { case 0: if (isHeader) { styleName = "cell_b"; cell.setCellValue(Double.parseDouble(data[i][j])); } else { styleName = "cell_normal"; cell.setCellValue(data[i][j]); } break; case 1: if (isHeader) { styleName = i == 0 ? "cell_h" : "cell_bb"; } else { styleName = "cell_indented"; } cell.setCellValue(data[i][j]); break; case 2: styleName = isHeader ? "cell_b" : "cell_normal"; cell.setCellValue(data[i][j]); break; case 3: styleName = isHeader ? "cell_b_centered" : "cell_normal_centered"; cell.setCellValue(Integer.parseInt(data[i][j])); break; case 4: { calendar.setTime(fmt.parse(data[i][j])); calendar.set(Calendar.YEAR, year); cell.setCellValue(calendar); styleName = isHeader ? "cell_b_date" : "cell_normal_date"; break; } case 5: { int r = rownum + 1; String fmla = "IF(AND(D" + r + ",E" + r + "),E" + r + "+D" + r + ",\"\")"; cell.setCellFormula(fmla); styleName = isHeader ? "cell_bg" : "cell_g"; break; } default: styleName = data[i][j] != null ? "cell_blue" : "cell_normal"; } cell.setCellStyle(styles.get(styleName)); } } //group rows for each phase, row numbers are 0-based sheet.groupRow(4, 6); sheet.groupRow(9, 13); sheet.groupRow(16, 18); //set column widths, the width is measured in units of 1/256th of a character width sheet.setColumnWidth(0, 256 * 6); sheet.setColumnWidth(1, 256 * 33); sheet.setColumnWidth(2, 256 * 20); sheet.setZoom(75); //75% scale // Write the output to a file String file = "businessplan.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); wb.close(); }
From source file:sample.poi.CalendarDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Calendar calendar = Calendar.getInstance(); SimpleDateFormat formatFile = new SimpleDateFormat("dd-MM-yyyy-hh-mm-ss"); HSSFWorkbook wb = new HSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); for (int month = 0; month < 12; month++) { calendar.set(Calendar.MONTH, month); calendar.set(Calendar.DAY_OF_MONTH, 1); //create a sheet for each month Sheet sheet = wb.createSheet(months[month]); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false);/*w w w. j a va 2s . c o m*/ sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); //the following three statements are required only for HSSF sheet.setAutobreaks(true); printSetup.setFitHeight((short) 1); printSetup.setFitWidth((short) 1); //the header row: centered text in 48pt font Row headerRow = sheet.createRow(0); headerRow.setHeightInPoints(80); Cell titleCell = headerRow.createCell(0); titleCell.setCellValue(months[month] + " " + "2013"); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1")); //header with month titles Row monthRow = sheet.createRow(1); for (int i = 0; i < days.length; i++) { //set column widths, the width is measured in units of 1/256th of a character width sheet.setColumnWidth(i * 2, 5 * 256); //the column is 5 characters wide sheet.setColumnWidth(i * 2 + 1, 13 * 256); //the column is 13 characters wide sheet.addMergedRegion(new CellRangeAddress(1, 1, i * 2, i * 2 + 1)); Cell monthCell = monthRow.createCell(i * 2); monthCell.setCellValue(days[i]); monthCell.setCellStyle(styles.get("month")); } int cnt = 1, day = 1; int rownum = 2; for (int j = 0; j < 6; j++) { Row row = sheet.createRow(rownum++); row.setHeightInPoints(100); for (int i = 0; i < days.length; i++) { Cell dayCell_1 = row.createCell(i * 2); Cell dayCell_2 = row.createCell(i * 2 + 1); int day_of_week = calendar.get(Calendar.DAY_OF_WEEK); if (cnt >= day_of_week && calendar.get(Calendar.MONTH) == month) { dayCell_1.setCellValue(day); calendar.set(Calendar.DAY_OF_MONTH, ++day); if (i == 0 || i == days.length - 1) { dayCell_1.setCellStyle(styles.get("weekend_left")); dayCell_2.setCellStyle(styles.get("weekend_right")); } else { dayCell_1.setCellStyle(styles.get("workday_left")); dayCell_2.setCellStyle(styles.get("workday_right")); } } else { dayCell_1.setCellStyle(styles.get("grey_left")); dayCell_2.setCellStyle(styles.get("grey_right")); } cnt++; } if (calendar.get(Calendar.MONTH) > month) break; } } // Write the output to a file String file = "calendar.xls"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:servlet.exportScoreSheet.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods./*from w ww . ja v a 2 s. co m*/ * * @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);//from w w w . j ava 2s. 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); sheet.setFitToPage(true);//from w w w.j a va2s. c o m 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); sheet.setFitToPage(true);/* ww w . j a va2s.c o m*/ 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:uk.co.spudsoft.birt.emitters.excel.handlers.PageHandler.java
License:Open Source License
private void setupPageSize(HandlerState state, IPageContent page) { PrintSetup printSetup = state.currentSheet.getPrintSetup(); printSetup.setPaperSize(state.getSmu().getPaperSizeFromString(page.getPageType())); if (page.getOrientation() != null) { if ("landscape".equals(page.getOrientation())) { printSetup.setLandscape(true); }/*from w ww.ja v a 2s .co m*/ } }
From source file:vistas.reportes.procesos.pruebaExcel.java
public static void main(String[] args) throws Exception { Workbook wb;//from w ww. 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(); } }