List of usage examples for org.apache.poi.ss.usermodel PrintSetup setLandscape
void setLandscape(boolean ls);
From source file:de.maklerpoint.office.Schnittstellen.Excel.ExportExcelXLSX.java
License:Open Source License
public void write() throws FileNotFoundException, IOException { FileOutputStream out = new FileOutputStream(new File(filename)); Workbook wb;//w w w . j a va 2 s.c o m wb = new XSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet(sheetName); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setAutobreaks(true); printSetup.setFitHeight((short) 1); printSetup.setFitWidth((short) 1); Row headerRow = sheet.createRow(0); headerRow.setHeightInPoints(12.75f); int[][] width = new int[titles.length][titles.length]; for (int i = 0; i < titles.length; i++) { Cell cell = headerRow.createCell((short) i); cell.setCellValue(titles[i]); cell.setCellStyle(styles.get("header")); width[i][0] = titles[i].length(); } 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((short) j); if (data[i][j] == null) data[i][j] = ""; cell.setCellValue(data[i][j].toString()); if (data[i][j].toString().length() > width[j][0]) width[j][0] = data[i][j].toString().length(); } } for (short i = 0; i < titles.length; i++) { short widthShort = (short) (256 * (width[i][0] + 3)); sheet.setColumnWidth(i, widthShort); } int position = (titles.length / 2) - 1; row = sheet.createRow(rownum + 3); cell = row.createCell((short) position); if (footName == null) { SimpleDateFormat df = new SimpleDateFormat("dd.MM.yyyy HH:mm"); cell.setCellValue("Export MaklerPoint vom " + df.format(new Date(System.currentTimeMillis())) + " - www.maklerpoint.de"); } else { cell.setCellValue(footName); } sheet.setZoom(3, 4); wb.write(out); out.close(); }
From source file:de.tudarmstadt.ukp.csniper.webapp.statistics.page.export.ExportExcelTask.java
License:Apache License
@Override protected void run() { Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("Summary"); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true);/*from w ww . j a v a 2 s . co m*/ sheet.setHorizontallyCenter(true); contextProvider.setOutputPos(exportModel.includePos); outputFile = null; OutputStream os = null; try { List<AggregatedEvaluationResult> results = repository.listAggregatedResults(formModel.getCollections(), formModel.getTypes(), formModel.getUsers(), formModel.getUserThreshold(), formModel.getConfidenceThreshold()); List<AdditionalColumn> ac = exportModel.additionalColumns; Collections.sort(results, new Comparator<AggregatedEvaluationResult>() { @Override public int compare(AggregatedEvaluationResult aO1, AggregatedEvaluationResult aO2) { String id1 = aO1.getItem().getCollectionId() + "|" + aO1.getItem().getDocumentId(); String id2 = aO2.getItem().getCollectionId() + "|" + aO2.getItem().getDocumentId(); return id1.compareTo(id2); } }); // Write header row List<String> colIds = new ArrayList<String>(Arrays.asList("User", "Collection", "Document", "Begin", "End", "Left", "Unit", "Right", "Type", "Class", "Confidence", "Correct", "Wrong")); for (int i = 0; i < ac.size(); i++) { colIds.add(ac.get(i).getName()); } Row headerRow = sheet.createRow(0); for (int i = 0; i < colIds.size(); i++) { Cell cell = headerRow.createCell(i); cell.setCellValue(colIds.get(i)); } // Write rest setTotal(results.size()); int rowNum = 1; for (AggregatedEvaluationResult aer : results) { ResultFilter classification = aer.getClassification(); if (formModel.getFilters().contains(classification)) { ItemContext context = contextProvider.getContext(aer.getItem(), exportModel.contextSize, exportModel.contextSize); // only differentiate between users if additional columns are being exported Set<String> users; if (ac.isEmpty()) { users = new HashSet<String>(Arrays.asList("")); } else { users = aer.getUsers(false); } // output the AggregatedEvaluationResult for every user (because the additional // columns entries might differ) for (String user : users) { Row row = sheet.createRow(rowNum); row.createCell(0).setCellValue(user); row.createCell(1).setCellValue(aer.getItem().getCollectionId()); row.createCell(2).setCellValue(aer.getItem().getDocumentId()); row.createCell(3).setCellValue(aer.getItem().getBeginOffset()); row.createCell(4).setCellValue(aer.getItem().getEndOffset()); row.createCell(5).setCellValue(context.getLeft()); row.createCell(6).setCellValue(context.getUnit()); row.createCell(7).setCellValue(context.getRight()); row.createCell(8).setCellValue(aer.getItem().getType()); row.createCell(9).setCellValue(classification.toString()); row.createCell(10).setCellValue(aer.getConfidence()); row.createCell(11).setCellValue(aer.getCorrect()); row.createCell(12).setCellValue(aer.getWrong()); for (int i = 0; i < ac.size(); i++) { String cellValue = repository.getEvaluationResult(aer.getItem().getId(), user) .getAdditionalColumns().get(ac.get(i)); if (cellValue == null) { cellValue = ""; } row.createCell(colIds.size() - ac.size() + i).setCellValue(cellValue); } rowNum++; } } // Make sure we do not get to 100% before we did the classification, because // otherwise ProgressBar.onFinish() will trigger!!! increment(); if (isCancelled()) { break; } } outputFile = File.createTempFile("date", ".csv"); os = new FileOutputStream(outputFile); wb.write(os); } catch (IOException e) { e.printStackTrace(); error("Export failed: " + ExceptionUtils.getRootCauseMessage(e)); cancel(); } finally { IOUtils.closeQuietly(os); if (isCancelled()) { clean(); } } }
From source file:de.tudarmstadt.ukp.dkpro.lab.reporting.FlexTable.java
License:Apache License
public StreamWriter getExcelWriter() { return new StreamWriter() { @Override/*from www . j av a 2s.c o m*/ public void write(OutputStream aStream) throws Exception { String[] colIds = compact ? getCompactColumnIds(false) : getColumnIds(); Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("Summary"); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); // Header row { Row row = sheet.createRow(0); Cell rowIdCell = row.createCell(0); rowIdCell.setCellValue("ID"); int colNum = 1; for (String colId : colIds) { Cell cell = row.createCell(colNum); cell.setCellValue(colId); colNum++; } } // Body rows { int rowNum = 1; for (String rowId : getRowIds()) { Row row = sheet.createRow(rowNum); Cell rowIdCell = row.createCell(0); rowIdCell.setCellValue(rowId); int colNum = 1; for (String colId : colIds) { Cell cell = row.createCell(colNum); String value = getValueAsString(rowId, colId); try { cell.setCellValue(Double.valueOf(value)); } catch (NumberFormatException e) { cell.setCellValue(value); } colNum++; } rowNum++; } } wb.write(aStream); } }; }
From source file:de.unioninvestment.eai.portal.portlet.crud.export.streaming.ExcelExporter.java
License:Apache License
private void initialSheetSetup() { final PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true);//from w w w .j ava 2 s . c o m sheet.setHorizontallyCenter(true); }
From source file:demo.poi.BusinessPlan.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("Business Plan"); // turn off gridlines sheet.setDisplayGridlines(false);//from ww w . j a v a 2 s.c om 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++) { String str = data[i][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(3, 4); // Write the output to a file String file = "target/businessplan.xls"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:demo.poi.LoanCalculator.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;//from ww w . ja va2 s . c o m if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); else wb = new HSSFWorkbook(); Map<String, CellStyle> 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, 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 = "target/loan-calculator.xls"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:demo.poi.TimesheetDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;/* w w w. ja va 2 s . c om*/ if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); else wb = new HSSFWorkbook(); 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 = "target/timesheet.xls"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:Demos.CalendarDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Calendar calendar = Calendar.getInstance(); boolean xlsx = true; for (int i = 0; i < args.length; i++) { if (args[i].charAt(0) == '-') { xlsx = args[i].equals("-xlsx"); } else {//from w w w . ja va 2 s .co m calendar.set(Calendar.YEAR, Integer.parseInt(args[i])); } } int year = calendar.get(Calendar.YEAR); Workbook wb = xlsx ? new XSSFWorkbook() : 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); 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] + " " + year); 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 = "C:\\Users\\BaldiniHP\\Desktop\\calendar.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:edu.duke.cabig.c3pr.service.Summary3ReportServiceTest.java
License:BSD License
public void testGenerateEXCEL() throws Exception { HealthcareSite hcs = healthcareSiteDao.getById(1100); DateFormat format = new SimpleDateFormat("MM/dd/yyyy"); Date startDate = (Date) format.parse("01/11/1990"); Date endDate = (Date) format.parse("01/01/2007"); String grantNumber = "GRANT-NO 1232"; Summary3Report summary3Report = new Summary3Report(hcs, grantNumber, startDate, endDate); String reportingSource = healthcareSiteDao.getById(1000).getName(); summary3Report.setReportingSource(reportingSource); summaryReportService.buildSummary3Report(summary3Report); String xmlString = summaryReportService.generateXML(summary3Report); File file = new File("testReport.xml"); FileWriter fileWriter = new FileWriter(file); fileWriter.write(xmlString);//w ww . j a v a 2s . c o m fileWriter.flush(); fileWriter.close(); System.out.println(xmlString); // creating the workbook and the spreadsheet try { DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance(); DocumentBuilder builder = factory.newDocumentBuilder(); InputStream inputStream = new FileInputStream(file); Document document = builder.parse(inputStream); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet spreadSheet = wb.createSheet("Sumary 3 Report"); PrintSetup printSetup = spreadSheet.getPrintSetup(); printSetup.setLandscape(true); spreadSheet.setFitToPage(true); spreadSheet.setHorizontallyCenter(true); spreadSheet.setColumnWidth((short) 0, (short) (60 * 256)); spreadSheet.setColumnWidth((short) 1, (short) (15 * 256)); spreadSheet.setColumnWidth((short) 2, (short) (30 * 256)); HSSFRow titleRow = spreadSheet.createRow(0); HSSFCell titleCell = titleRow.createCell((short) 0); titleRow.setHeightInPoints(40); HSSFCellStyle titleCellStyle = wb.createCellStyle(); titleCellStyle.setAlignment(titleCellStyle.ALIGN_CENTER_SELECTION); titleCellStyle.setVerticalAlignment(titleCellStyle.VERTICAL_CENTER); titleCell.setCellStyle(titleCellStyle); String nullSafeGrantNumber = (document.getElementsByTagName("grantNumber").item(0) != null && document.getElementsByTagName("grantNumber").item(0).getFirstChild() != null) ? (document.getElementsByTagName("grantNumber")).item(0).getFirstChild().getNodeValue() : ""; titleCell.setCellValue("Summary 3: Reportable Patients/Participation " + "in Therapeutic Protocols" + " " + nullSafeGrantNumber); HSSFRow orgRow = spreadSheet.createRow(1); orgRow.setHeightInPoints(30); HSSFCell organizationCell = orgRow.createCell((short) 0); HSSFCellStyle orgCellStyle = wb.createCellStyle(); orgCellStyle.setAlignment(titleCellStyle.ALIGN_CENTER_SELECTION); HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); orgCellStyle.setFont(font); organizationCell.setCellStyle(orgCellStyle); organizationCell .setCellValue(((Element) (document.getElementsByTagName("reportingOrganization").item(0))) .getElementsByTagName("name").item(0).getFirstChild().getNodeValue()); HSSFRow reportingPeriodRow = spreadSheet.createRow(2); HSSFFont reportingPeriodFont = wb.createFont(); reportingPeriodFont.setFontHeightInPoints((short) 9); HSSFCellStyle reportingPeriodStyle = wb.createCellStyle(); reportingPeriodStyle.setFont(reportingPeriodFont); reportingPeriodRow.setHeightInPoints(20); HSSFCell reportingPeriodCell = reportingPeriodRow.createCell((short) 0); reportingPeriodCell.setCellStyle(titleCellStyle); reportingPeriodCell.setCellValue("Reporting Period " + (document.getElementsByTagName("startDate").item(0).getFirstChild().getNodeValue()) + " - " + (document.getElementsByTagName("endDate").item(0).getFirstChild().getNodeValue())); // creating the first row of table the table header HSSFRow row = spreadSheet.createRow(3); HSSFCell tableHeaderCell1 = row.createCell((short) 0); HSSFCellStyle tableHeaderCellStyle1 = wb.createCellStyle(); tableHeaderCellStyle1.setWrapText(true); tableHeaderCellStyle1.setBorderRight(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle1.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); tableHeaderCellStyle1.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); tableHeaderCellStyle1.setBorderBottom(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle1.setFont(font); tableHeaderCell1.setCellStyle(tableHeaderCellStyle1); tableHeaderCell1.setCellValue("Disease Site"); // creating table header 2nd & 3rd cells HSSFCell tableHeaderCell2 = row.createCell((short) 1); HSSFCellStyle tableHeaderCellStyle2 = wb.createCellStyle(); tableHeaderCellStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle2.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); tableHeaderCellStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle2.setWrapText(true); tableHeaderCell2.setCellStyle(tableHeaderCellStyle2); tableHeaderCell2.setCellValue("Newly Registered Patients"); HSSFCell tableHeaderCell3 = row.createCell((short) 2); HSSFCellStyle tableHeaderCellStyle3 = wb.createCellStyle(); tableHeaderCellStyle3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); tableHeaderCellStyle3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); tableHeaderCellStyle3.setBorderLeft(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle3.setBorderBottom(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle3.setWrapText(true); tableHeaderCell3.setCellStyle(tableHeaderCellStyle3); tableHeaderCell3.setCellValue("Total patients newly enrolled in therapeutic protocols"); NodeList nodeList = document.getElementsByTagName("reportData"); HSSFCellStyle tableCellStyle = wb.createCellStyle(); tableCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); tableCellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); tableCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); tableCellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$C$1")); spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$2:$C$2")); spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$3:$C$3")); for (int i = 4; i < nodeList.getLength() + 4; i++) { row = spreadSheet.createRow(i); HSSFCell cell = row.createCell((short) 0); if (i == (4 + nodeList.getLength() - 1)) { HSSFCellStyle totalCellStyle = wb.createCellStyle(); totalCellStyle.setFont(font); totalCellStyle.setRightBorderColor((short) 10); totalCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); tableCellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); totalCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); totalCellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); cell.setCellStyle(totalCellStyle); } else { cell.setCellStyle(tableCellStyle); } cell.setCellValue(((Element) ((Element) (nodeList.item(i - 4))).getElementsByTagName("key").item(0)) .getAttribute("name")); cell = row.createCell((short) 1); cell.setCellStyle(tableCellStyle); cell.setCellValue(""); cell = row.createCell((short) 2); cell.setCellStyle(tableCellStyle); cell.setCellValue( ((Element) (((Element) (nodeList.item(i - 4))).getElementsByTagName("value").item(3))) .getFirstChild().getNodeValue()); } File outputFile = new File(System.getProperty("user.home") + File.separator + "Summary3Report.xls"); FileOutputStream output = new FileOutputStream(outputFile); wb.write(output); output.flush(); output.close(); } catch (IOException e) { e.printStackTrace(); } catch (ParserConfigurationException e) { e.printStackTrace(); } catch (SAXException e) { e.printStackTrace(); } }
From source file:edu.duke.cabig.c3pr.xml.Summary3ReportGenerator.java
License:BSD License
public void generateExcel(String summary3ReportXml, String file) throws Exception { // creating the workbook and the spreadsheet try {/*from ww w. ja va 2 s . c o m*/ DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance(); DocumentBuilder builder = factory.newDocumentBuilder(); String fileName = ""; fileName = file; InputStream inputStream = new FileInputStream((fileName + ".xml")); Document document = builder.parse(inputStream); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet spreadSheet = wb.createSheet("Sumary 3 Report"); PrintSetup printSetup = spreadSheet.getPrintSetup(); printSetup.setLandscape(true); spreadSheet.setFitToPage(true); spreadSheet.setHorizontallyCenter(true); spreadSheet.setColumnWidth((short) 0, (short) (60 * 256)); spreadSheet.setColumnWidth((short) 1, (short) (15 * 256)); spreadSheet.setColumnWidth((short) 2, (short) (30 * 256)); HSSFRow titleRow = spreadSheet.createRow(0); HSSFCell titleCell = titleRow.createCell((short) 0); titleRow.setHeightInPoints(40); HSSFCellStyle titleCellStyle = wb.createCellStyle(); titleCellStyle.setAlignment(titleCellStyle.ALIGN_CENTER_SELECTION); titleCellStyle.setVerticalAlignment(titleCellStyle.VERTICAL_CENTER); titleCell.setCellStyle(titleCellStyle); String nullSafeGrantNumber = (document.getElementsByTagName("grantNumber").item(0) != null && document.getElementsByTagName("grantNumber").item(0).getFirstChild() != null) ? (document.getElementsByTagName("grantNumber")).item(0).getFirstChild().getNodeValue() : ""; titleCell.setCellValue("Summary 3: Reportable Patients/Participation " + "in Therapeutic Protocols" + " " + nullSafeGrantNumber); HSSFRow orgRow = spreadSheet.createRow(1); orgRow.setHeightInPoints(30); HSSFCell organizationCell = orgRow.createCell((short) 0); HSSFCellStyle orgCellStyle = wb.createCellStyle(); orgCellStyle.setAlignment(titleCellStyle.ALIGN_CENTER_SELECTION); HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); orgCellStyle.setFont(font); organizationCell.setCellStyle(orgCellStyle); organizationCell .setCellValue(((Element) (document.getElementsByTagName("reportingOrganization").item(0))) .getElementsByTagName("name").item(0).getFirstChild().getNodeValue()); HSSFRow reportingPeriodRow = spreadSheet.createRow(2); HSSFFont reportingPeriodFont = wb.createFont(); reportingPeriodFont.setFontHeightInPoints((short) 9); HSSFCellStyle reportingPeriodStyle = wb.createCellStyle(); reportingPeriodStyle.setFont(reportingPeriodFont); reportingPeriodRow.setHeightInPoints(20); HSSFCell reportingPeriodCell = reportingPeriodRow.createCell((short) 0); reportingPeriodCell.setCellStyle(titleCellStyle); reportingPeriodCell.setCellValue("Reporting Period " + (document.getElementsByTagName("startDate").item(0).getFirstChild().getNodeValue()) + " - " + (document.getElementsByTagName("endDate").item(0).getFirstChild().getNodeValue())); // creating the first row of table the table header HSSFRow row = spreadSheet.createRow(3); HSSFCell tableHeaderCell1 = row.createCell((short) 0); HSSFCellStyle tableHeaderCellStyle1 = wb.createCellStyle(); tableHeaderCellStyle1.setWrapText(true); tableHeaderCellStyle1.setBorderRight(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle1.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); tableHeaderCellStyle1.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); tableHeaderCellStyle1.setBorderBottom(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle1.setFont(font); tableHeaderCell1.setCellStyle(tableHeaderCellStyle1); tableHeaderCell1.setCellValue("Disease Site"); // creating table header 2nd & 3rd cells HSSFCell tableHeaderCell2 = row.createCell((short) 1); HSSFCellStyle tableHeaderCellStyle2 = wb.createCellStyle(); tableHeaderCellStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle2.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); tableHeaderCellStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle2.setWrapText(true); tableHeaderCell2.setCellStyle(tableHeaderCellStyle2); tableHeaderCell2.setCellValue("Newly Registered Patients"); HSSFCell tableHeaderCell3 = row.createCell((short) 2); HSSFCellStyle tableHeaderCellStyle3 = wb.createCellStyle(); tableHeaderCellStyle3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); tableHeaderCellStyle3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); tableHeaderCellStyle3.setBorderLeft(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle3.setBorderBottom(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle3.setWrapText(true); tableHeaderCell3.setCellStyle(tableHeaderCellStyle3); tableHeaderCell3.setCellValue("Total patients newly enrolled in therapeutic protocols"); NodeList nodeList = document.getElementsByTagName("reportData"); HSSFCellStyle tableCellStyle = wb.createCellStyle(); tableCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); tableCellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); tableCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); tableCellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$C$1")); spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$2:$C$2")); spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$3:$C$3")); for (int i = 4; i < nodeList.getLength() + 4; i++) { row = spreadSheet.createRow(i); HSSFCell cell = row.createCell((short) 0); if (i == (4 + nodeList.getLength() - 1)) { HSSFCellStyle totalCellStyle = wb.createCellStyle(); totalCellStyle.setFont(font); totalCellStyle.setRightBorderColor((short) 10); totalCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); tableCellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); totalCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); totalCellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); cell.setCellStyle(totalCellStyle); } else { cell.setCellStyle(tableCellStyle); } cell.setCellValue(((Element) ((Element) (nodeList.item(i - 4))).getElementsByTagName("key").item(0)) .getAttribute("name")); cell = row.createCell((short) 1); cell.setCellStyle(tableCellStyle); cell.setCellValue(""); cell = row.createCell((short) 2); cell.setCellStyle(tableCellStyle); cell.setCellValue( ((Element) (((Element) (nodeList.item(i - 4))).getElementsByTagName("value").item(3))) .getFirstChild().getNodeValue()); } FileOutputStream output = new FileOutputStream(new File(addExtension(file, "Excel"))); wb.write(output); output.flush(); output.close(); } catch (IOException e) { e.printStackTrace(); } catch (ParserConfigurationException e) { e.printStackTrace(); } catch (SAXException e) { e.printStackTrace(); } }