List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook setSheetName
@Override public void setSheetName(int sheetIndex, String sheetname)
From source file:com.anritsu.mcrepositorymanager.utils.GenerateRSS.java
public String getRSS() { FileInputStream file = null;//from w w w . j a v a 2s . com String rssFileName = rssTemplateFileName.replaceAll("template", mcVersion); try { file = new FileInputStream( new File(Configuration.getInstance().getRssTemplatePath() + rssTemplateFileName)); XSSFWorkbook workbook = new XSSFWorkbook(file); workbook.setSheetName(workbook.getSheetIndex("MC X.X.X"), "MC " + mcVersion); XSSFSheet sheet = workbook.getSheet("MC " + mcVersion); CreationHelper createHelper = workbook.getCreationHelper(); Cell cell = null; // Update the sheet title cell = sheet.getRow(0).getCell(0); cell.setCellValue(cell.getStringCellValue().replaceAll("template", mcVersion)); XSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); XSSFCellStyle hlinkstyle = workbook.createCellStyle(); XSSFFont hlinkfont = workbook.createFont(); hlinkfont.setUnderline(XSSFFont.U_SINGLE); hlinkfont.setColor(HSSFColor.BLUE.index); hlinkstyle.setFont(hlinkfont); hlinkstyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); hlinkstyle.setBorderTop(XSSFCellStyle.BORDER_THIN); hlinkstyle.setBorderRight(XSSFCellStyle.BORDER_THIN); hlinkstyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); XSSFCellStyle dateCellStyle = workbook.createCellStyle(); dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd-MMMM-yyyy")); dateCellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); dateCellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); dateCellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); dateCellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); // Populate the table int rowCount = 1; for (RecommendedMcPackage rmcp : sortedMcPackages) { if (rmcp.getRecommendedVersion() != null && rmcp.isShowInTable()) { Row row = sheet.createRow(rowCount + 1); rowCount++; cell = row.createCell(0); cell.setCellValue(rmcp.getTier().replaceAll("Anritsu/MasterClaw/", "")); cell.setCellStyle(cellStyle); cell = row.createCell(1); cell.setCellValue(rmcp.getGroup()); cell.setCellStyle(cellStyle); cell = row.createCell(2); cell.setCellValue(rmcp.getPackageName()); UrlValidator defaultValidator = new UrlValidator(UrlValidator.ALLOW_LOCAL_URLS); if (rmcp.getRecommendedVersion().getReleaseNote() != null && defaultValidator.isValid(rmcp.getRecommendedVersion().getReleaseNote())) { XSSFHyperlink releaseNotelink = (XSSFHyperlink) createHelper .createHyperlink(Hyperlink.LINK_URL); releaseNotelink.setAddress(rmcp.getRecommendedVersion().getReleaseNote()); //System.out.println("Inside(if) RN: " + rmcp.getRecommendedVersion().getReleaseNote() + " Valid: " + defaultValidator.isValid(rmcp.getRecommendedVersion().getReleaseNote())); cell.setHyperlink(releaseNotelink); } cell.setCellStyle(hlinkstyle); cell = row.createCell(3); cell.setCellValue(rmcp.getRecommendedVersion().getPackageVersion()); cell.setCellStyle(cellStyle); cell = row.createCell(4); cell.setCellValue(rmcp.getAvailability()); cell.setCellStyle(cellStyle); cell = row.createCell(5); String customers = Arrays.asList(rmcp.getRecommendedVersion().getCustomerList().toArray()) .toString(); if (customers.equalsIgnoreCase("[All]")) { customers = ""; } cell.setCellValue(customers); cell.setCellStyle(cellStyle); cell = row.createCell(6); cell.setCellValue(rmcp.getRecommendedVersion().getRisk()); cell.setCellStyle(cellStyle); cell = row.createCell(7); cell.setCellValue(rmcp.getPackageName()); XSSFHyperlink link = (XSSFHyperlink) createHelper.createHyperlink(Hyperlink.LINK_URL); link.setAddress(rmcp.getRecommendedVersion().getDownloadLinks().iterator().next()); cell.setHyperlink((XSSFHyperlink) link); cell.setCellStyle(hlinkstyle); cell = row.createCell(8); cell.setCellValue((rmcp.getRecommendedVersion() != null && rmcp.getRecommendedVersion().isLessRecommended()) ? "#" : ""); cell.setCellStyle(cellStyle); cell = row.createCell(9); cell.setCellValue(rmcp.getRecommendedVersion().getNotes()); cell.setCellStyle(cellStyle); StringBuilder newFeatures = new StringBuilder(); for (MCPackageActivities mcpa : rmcp.getRecommendedVersion().getActivities()) { if (!mcpa.getActivityType().equalsIgnoreCase("epr")) { newFeatures.append(mcpa.getActivityType() + " " + mcpa.getActivityId() + "; "); } } cell = row.createCell(10); cell.setCellValue(newFeatures.toString()); cell.setCellStyle(cellStyle); cell = row.createCell(11); cell.setCellValue(rmcp.getRecommendedVersion().getReleaseDate()); cell.setCellStyle(dateCellStyle); } sheet.autoSizeColumn(0); sheet.autoSizeColumn(1); sheet.autoSizeColumn(2); sheet.autoSizeColumn(3); sheet.autoSizeColumn(4); sheet.autoSizeColumn(6); sheet.autoSizeColumn(7); sheet.autoSizeColumn(8); sheet.autoSizeColumn(11); } FileOutputStream outFile = new FileOutputStream( new File(Configuration.getInstance().getRssTemplatePath() + rssFileName)); workbook.write(outFile); outFile.close(); return Configuration.getInstance().getRssTemplatePath() + rssFileName; } catch (FileNotFoundException ex) { Logger.getLogger(GenerateRSS.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(GenerateRSS.class.getName()).log(Level.SEVERE, null, ex); } finally { try { file.close(); } catch (IOException ex) { Logger.getLogger(GenerateRSS.class.getName()).log(Level.SEVERE, null, ex); } } return ""; }
From source file:nl.detoren.ijc.io.OutputExcel.java
License:Open Source License
/** * Create the Excel version of the sheet Original Empty file is stored in * Empty.xlsx Create version with round matches is stored in Indeling.xlsx * * @param wedstrijden//from w ww. jav a 2 s . c o m * The round to store in the Excel file */ public boolean export(Wedstrijden wedstrijden) { try { logger.log(Level.INFO, "Wedstrijden wegschrijven naar Excel"); int periode = wedstrijden.getPeriode(); int ronde = wedstrijden.getRonde(); String rpString = "Periode " + periode + ", Ronde " + ronde; String datum = new SimpleDateFormat("dd-MM-yyyy HH:mm").format(Calendar.getInstance().getTime()); // Open the empty schedule file, matches are stored in the // second sheet (id = 1) FileInputStream file = new FileInputStream("Template.xlsx"); XSSFWorkbook workbook = new XSSFWorkbook(file); ArrayList<Groepswedstrijden> gws = wedstrijden.getGroepswedstrijden(); for (Groepswedstrijden gw : gws) { int groepID = gw.getNiveau(); int nrSeries = gw.getSeries().size(); // Open sheet voor deze groep XSSFSheet sheet = workbook.getSheetAt(groepID); workbook.setSheetName(groepID, Groep.geefNaam(groepID)); updateCell(sheet, 2, 8, rpString); updateCell(sheet, 2, 4, datum); // Export Series int currentRow = 5; for (int s = 0; s < nrSeries; ++s) { // For each serie Serie serie = gw.getSerie(s); updateCell(sheet, currentRow, 2, "Serie " + (s + 1)); borderLeft(getCell(sheet, currentRow, 1)); borderLeft(getCell(sheet, currentRow + 1, 1)); borderRight(getCell(sheet, currentRow, 9)); borderRight(getCell(sheet, currentRow + 1, 9)); currentRow += 2; for (Wedstrijd w : serie.getWedstrijden()) { exportWedstrijd(sheet, w, currentRow); borderLeft(getCell(sheet, currentRow, 1)); borderLeft(getCell(sheet, currentRow + 1, 1)); borderRight(getCell(sheet, currentRow, 9)); borderRight(getCell(sheet, currentRow + 1, 9)); currentRow += 2; } } // Export trio ArrayList<Wedstrijd> trio = gw.getTriowedstrijden(); if (trio != null && trio.size() > 0) { updateCell(sheet, currentRow, 2, "Trio"); borderLeft(getCell(sheet, currentRow, 1)); borderLeft(getCell(sheet, currentRow + 1, 1)); borderRight(getCell(sheet, currentRow, 9)); borderRight(getCell(sheet, currentRow + 1, 9)); currentRow += 2; for (Wedstrijd w : trio) { exportWedstrijd(sheet, w, currentRow); borderLeft(getCell(sheet, currentRow, 1)); borderLeft(getCell(sheet, currentRow + 1, 1)); borderRight(getCell(sheet, currentRow, 9)); borderRight(getCell(sheet, currentRow + 1, 9)); currentRow += 2; } } currentRow--; for (int j = 2; j <= 8; j++) borderBottom(getCell(sheet, currentRow, j)); borderLeftBottom(getCell(sheet, currentRow, 1)); borderRightBottom(getCell(sheet, currentRow, 9)); } // Close input file file.close(); // Store Excel to new file String dirName = "R" + periode + "-" + ronde; new File(dirName).mkdirs(); String filename = dirName + File.separator + "Indeling " + periode + "-" + ronde + ".xlsx"; File outputFile = new File(filename); FileOutputStream outFile = new FileOutputStream(outputFile); workbook.write(outFile); // Close output file workbook.close(); outFile.close(); // And open it in the system editor Desktop.getDesktop().open(outputFile); return true; } catch (Exception e) { logger.log(Level.WARNING, "Error writing output: " + e.toString()); FoutMelding.melding("Fout bij opslaan Excel bestand: " + e.getMessage()); return false; } }
From source file:org.cgiar.ccafs.ap.summaries.projects.xlsx.BudgetByMOGSummaryXLS.java
License:Open Source License
/** * This method is used to generate the csv file for the ProjectLeading institutions. * // w w w . j a va2 s .c o m * @param projectPartnerInstitutions is the list of institutions to be added * @param projectList is the list with the projects related to each institution */ public byte[] generateXLS(List<Map<String, Object>> informationBudgetReportByMOGDetail, List<Map<String, Object>> informationBudgetReportByMOG) { try { XSSFWorkbook workbook = xls.initializeWorkbook(true); /***************** Budget Summary By MOG Report ******************/ // Writting headers String[] headersPOWB = new String[] { "Outcome 2019", "MOG", "Total Budget W1/W2 (USD)", "Gender W1/W2 (USD)", "Total Budget W3/Bilateral (USD)", "Gender W3/Bilateral (USD)" }; // defining header types. int[] headerTypesPOWB = new int[] { BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_BUDGET, BaseXLS.COLUMN_TYPE_BUDGET, BaseXLS.COLUMN_TYPE_BUDGET, BaseXLS.COLUMN_TYPE_BUDGET }; // creating sheet Sheet[] sheets = new Sheet[2]; sheets[0] = workbook.getSheetAt(0); sheets[1] = workbook.cloneSheet(0); workbook.setSheetName(0, "Level - 1 "); xls.initializeSheet(sheets[0], headerTypesPOWB); xls.writeHeaders(sheets[0], headersPOWB); this.addContent(informationBudgetReportByMOG, sheets[0], 0, workbook); // Set description xls.writeDescription(sheets[0], xls.getText("summaries.budget.summary.sheet.description")); // write text box xls.writeTitleBox(sheets[0], " \t\t Budget Summary"); // write text box xls.createLogo(workbook, sheets[0]); /***************** Budget Summary By MOG Detail ******************/ // Sheet cleanSheet = // Writting headers String[] headersPOWBDetail = new String[] { "Project Id", "Project title", "Project type", "MOG", "Expected annual contribution", "Expected plan of the gender and social inclusion", "Total Budget W1/W2 (USD)", " Gender W1/W2 (USD)", "Total Budget W3/Bilateral (USD)", "Gender W3/Bilateral (USD)" }; // defining header types. int[] headerTypesPOWBDetail = new int[] { BaseXLS.COLUMN_TYPE_HYPERLINK, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_SHORT, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_BUDGET, BaseXLS.COLUMN_TYPE_BUDGET, BaseXLS.COLUMN_TYPE_BUDGET, BaseXLS.COLUMN_TYPE_BUDGET }; workbook.setSheetName(1, "Level - 2"); xls.initializeSheet(sheets[1], headerTypesPOWBDetail); xls.writeHeaders(sheets[1], headersPOWBDetail); this.addContent(informationBudgetReportByMOGDetail, sheets[1], 1, workbook); // Set description xls.writeDescription(sheets[1], xls.getText("summaries.budget.summary.sheet.description")); // write text box xls.writeTitleBox(sheets[1], " Budget Summary Detail"); // write text box xls.createLogo(workbook, sheets[1]); // this.flush(); xls.writeWorkbook(); byte[] byteArray = xls.getBytes(); // Closing streams. xls.closeStreams(); return byteArray; } catch (IOException e) { e.printStackTrace(); } return null; }
From source file:org.cgiar.ccafs.ap.summaries.projects.xlsx.DeliverablePlanningSummaryXLS.java
License:Open Source License
/** * This method is used to generate the csv file for the ProjectLeading institutions. * // w w w .j a va 2s . c o m * @param projectPartnerInstitutions is the list of institutions to be added * @param projectList is the list with the projects related to each institution */ public byte[] generateXLS(List<Map<String, Object>> deliverableList) { try { // Writting headers String[] headers = new String[] { "Project id", "Project title", "Flagship(s)", "Region(s)", "Deliverable title", "MOG", "Year of expected completion", "Main type", "Sub type", "Partner responsible", "Other responsibles" }; // defining header types. int[] headerTypes = new int[] { BaseXLS.COLUMN_TYPE_HYPERLINK, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_SHORT, BaseXLS.COLUMN_TYPE_TEXT_SHORT, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_NUMERIC, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_LONG }; XSSFWorkbook workbook = xls.initializeWorkbook(true); this.xls.initializeSheet(workbook.getSheetAt(0), headerTypes); // renaming sheet workbook.setSheetName(0, "Deliverable Report"); Sheet sheet = workbook.getSheetAt(0); xls.writeHeaders(sheet, headers); this.addContent(deliverableList, workbook); // Set description xls.writeDescription(sheet, xls.getText("summaries.expected.deliverable.summary.sheetone.description")); // write text box xls.writeTitleBox(sheet, xls.getText("summaries.expected.deliverable.summary.name")); // write text box xls.createLogo(workbook, sheet); xls.writeWorkbook(); byte[] byteArray = xls.getBytes(); // Closing streams. xls.closeStreams(); return byteArray; } catch (IOException e) { e.printStackTrace(); } return null; }