List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet addMergedRegion
@Override public int addMergedRegion(CellRangeAddress region)
From source file:kr.co.blackducksoftware.rg.displayexcel.Ex.java
License:Open Source License
/** * "OSS Check List sheet" /*from w w w . j a va 2 s . c om*/ * */ public static void OSSC(HSSFWorkbook wb) { LogMaker.makelog("Making OSS Sheet"); HSSFSheet sheet = wb.createSheet("OSS Checklist");// ??????? ArrayList<String> Header = new ArrayList<String>(); OSSCLineHeader(sheet);// 0?? ???? ??????? //Header.clear(); Header.add("3rd Party Name"); Header.add("Checklist File"); Header.add("Analyzer's Comments");//drop down HSSFRow row = sheet.createRow(1); row.setHeight((short) 500); //src2ndrow(HSSFSheet sheet, ArrayList<String> al,int i,HSSFRow row, int a, int b) src2ndrow(sheet, Header, 0, row, 3, 5); Header.clear(); Header.add("Person In Charge"); Header.add("Reviewer's Ccomments"); src2ndrow(sheet, Header, 3, row, 3, 5); Header.clear(); Header.add("Review Date"); Header.add("Reviewer's Comments"); src2ndrow(sheet, Header, 5, row, 3, 5); Header.clear(); sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) 2)); sheet.addMergedRegion(new Region(0, (short) 3, 0, (short) 4)); sheet.addMergedRegion(new Region(0, (short) 5, 0, (short) 6)); //?? ???? for (int i = 2; i < 200; i++) { HSSFRow row2 = sheet.createRow(i); row2.setHeight((short) 800); for (int j = 0; j < 7; j++) { row2.createCell(j, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row2.getCell(j).setCellStyle(Style.componentCellStyle); } } }
From source file:kr.co.blackducksoftware.rg.displayexcel.Style.java
@SuppressWarnings("deprecation") public static void createSheetHeaders(HSSFSheet currentSheet) { HSSFRow mainHeader = currentSheet.createRow(1); // HSSFRow topMergedHeaders = currentSheet.createRow(1); /**// ww w.j a v a 2 s .c o m * ???? ??? ???? */ HSSFRichTextString sourceCodeIdentificationReportHeader = new HSSFRichTextString( "?? ???? SW ????? ???? ???"); mainHeader.createCell(0).setCellValue(sourceCodeIdentificationReportHeader); currentSheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));// 7->6?? // ???? // for // comment mainHeader.getCell(0).setCellStyle(mainHeaderStyle); /* * Menu 1 */ HSSFRow menu1 = currentSheet.createRow(3); HSSFRichTextString menuString = new HSSFRichTextString( "1. ?????? ????"); menu1.createCell(0).setCellValue(menuString); currentSheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));// 7->6?? // ???? // for // comment menu1.getCell(0).setCellStyle(menuLineStyle); /* * BOM (menu2) */ HSSFRow BOMHead = currentSheet.createRow(11);// 12?? ???? BOMHead.createCell(0).setCellValue( "2. ?????? ??????? ???(Bill of Materials)"); currentSheet.autoSizeColumn((short) 0, true); BOMHead.getCell(0).setCellStyle(menuLineStyle); /** * ?? ??? ???? ???? */ HSSFRichTextString homepageHeader = new HSSFRichTextString("??????"); HSSFRichTextString matchedObHeader = new HSSFRichTextString("??? ??"); HSSFRichTextString matchedFilesCountHeader = new HSSFRichTextString("File / Folder"); HSSFRichTextString componentHeader = new HSSFRichTextString("Component"); HSSFRichTextString versionHeader = new HSSFRichTextString("Version"); // HSSFRichTextString licenseHeader = new // HSSFRichTextString("Total Lines"); HSSFRichTextString identifierHeader = new HSSFRichTextString("Component"); HSSFRichTextString identifiedDateHeader = new HSSFRichTextString("Version"); HSSFRichTextString commentHeader = new HSSFRichTextString("????????"); HSSFRichTextString firstLineHeader = new HSSFRichTextString("Matched First Line");// firstline HSSFRichTextString matchedRatioHeader = new HSSFRichTextString("Matched Ratio");// MachedRatioPercent HSSFRichTextString realCommentHeader = new HSSFRichTextString("Comment");// add // for // comment HSSFRichTextString licenseHeader = new HSSFRichTextString("?????");// add // for // license HSSFRichTextString jointHeader = new HSSFRichTextString("????????");// add // for // license HSSFRichTextString fileCountHeader = new HSSFRichTextString("?? ???? ??");// add // for // license HSSFRichTextString percentHeader = new HSSFRichTextString("%");// add // for // license // currentSheet.addMergedRegion(new CellRangeAddress(1, 1, 9, 13)); /* * 2?? ?? ??????? ???. */ HSSFRow BOM = currentSheet.createRow(12);// 13?? ???? // topMergedHeaders.createCell(0).setCellValue(categoryHeader); BOM.createCell(0).setCellValue(matchedObHeader); BOM.createCell(1).setCellValue(componentHeader); BOM.createCell(2).setCellValue(versionHeader); BOM.createCell(3).setCellValue(homepageHeader); BOM.createCell(4).setCellValue(licenseHeader); BOM.createCell(5).setCellValue(jointHeader); BOM.createCell(6).setCellValue(fileCountHeader); // BOM.createCell(7).setCellValue(commentHeader); /* * 2?? ?? ?????? ? ?? */ for (int a = 0; a < BOM.getLastCellNum(); a++) { BOM.getCell(a).setCellStyle(firstMergedCellStyle); currentSheet.autoSizeColumn((short) a, true); } /* * ????sw????? ???? (menu3) */ int BOMCount = 0;// ?? HSSFRow OSSSMenu = currentSheet.createRow(15 + BOMCount);// 3?? ?? ???? ???? OSSSMenu.createCell(0) .setCellValue("3. ????SW????? ????"); currentSheet.autoSizeColumn((short) 0, true); OSSSMenu.getCell(0).setCellStyle(menuLineStyle); /* * 3?? ?? ??????? ???. */ HSSFRow OSSS = currentSheet.createRow(16 + BOMCount);// 3?? ???? // topMergedHeaders.createCell(0).setCellValue(categoryHeader); OSSS.createCell(0).setCellValue(licenseHeader); OSSS.createCell(1).setCellValue(jointHeader); OSSS.createCell(2).setCellValue(fileCountHeader); OSSS.createCell(3).setCellValue(percentHeader); // OSSS.createCell(7).setCellValue(commentHeader); for (int a = 0; a < OSSS.getLastCellNum(); a++) { OSSS.getCell(a).setCellStyle(firstMergedCellStyle); currentSheet.autoSizeColumn((short) a, true); } /* * ????? ????? (menu4) */ int OSSSCount = 0;// ?? HSSFRow analMenu = currentSheet.createRow(19 + BOMCount + OSSSCount);// 4?? // ?? // ???? // ???? analMenu.createCell(0).setCellValue("4. ????? ?????"); currentSheet.autoSizeColumn((short) 0, true); analMenu.getCell(0).setCellStyle(menuLineStyle); /* * 4?? ?? ??????? ???. */ HSSFRow anal = currentSheet.createRow(20 + BOMCount + OSSSCount);// 3?? // ???? anal.createCell(0).setCellValue("????? ?????"); anal.getCell(0).setCellStyle(analCellStyle); currentSheet.autoSizeColumn((short) 0, true); }
From source file:matriz.core.GerarXLS.java
public static void expExcel(String nomeArquivo, String demanda, List<LineMatriz> linhastabela) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet firstSheet = workbook.createSheet("Aba1"); HSSFFont fontBranca = workbook.createFont(); fontBranca.setFontHeightInPoints((short) 16); fontBranca.setColor(HSSFColor.WHITE.index); fontBranca.setBoldweight(Font.BOLDWEIGHT_BOLD); HSSFFont fontBrancaMenor = workbook.createFont(); fontBrancaMenor.setFontHeightInPoints((short) 10); fontBrancaMenor.setColor(HSSFColor.WHITE.index); fontBrancaMenor.setBoldweight(Font.BOLDWEIGHT_BOLD); FileOutputStream fos = null;/*www . j av a 2s . com*/ nomeArquivo = nomeArquivo + "/Matriz de Rastreabilidade " + demanda + ".xls"; try { fos = new FileOutputStream(new File(nomeArquivo)); HSSFRow rowa = firstSheet.createRow(0); rowa.createCell(1); // criar titulo HSSFRow row0 = firstSheet.createRow(1); HSSFCellStyle style = workbook.createCellStyle(); style.setFont(fontBranca); style.setFillForegroundColor(HSSFColor.DARK_BLUE.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); row0.createCell(0).setCellValue("Matriz de classes alteradas na " + demanda); row0.getCell(0).setCellStyle(style); //nome das colunas HSSFRow row1 = firstSheet.createRow(2); HSSFCellStyle style2 = workbook.createCellStyle(); style2.setFillForegroundColor(HSSFColor.DARK_BLUE.index); style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style2.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); style2.setFont(fontBrancaMenor); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); row1.createCell(0).setCellValue("Sistema"); row1.createCell(1).setCellValue("Mdulo"); row1.createCell(2).setCellValue("Diretrio"); row1.createCell(3).setCellValue("Arquivo"); row1.createCell(4).setCellValue("Ambiente"); row1.createCell(5).setCellValue("Tipo do Arquivo"); row1.createCell(6).setCellValue("Ao"); row1.createCell(7).setCellValue("Data"); row1.createCell(8).setCellValue("Reviso SVN"); row1.createCell(9).setCellValue("Autor"); row1.createCell(10).setCellValue("Motivo da alterao"); row1.getCell(0).setCellStyle(style2); row1.getCell(1).setCellStyle(style2); row1.getCell(2).setCellStyle(style2); row1.getCell(3).setCellStyle(style2); row1.getCell(4).setCellStyle(style2); row1.getCell(5).setCellStyle(style2); row1.getCell(6).setCellStyle(style2); row1.getCell(7).setCellStyle(style2); row1.getCell(8).setCellStyle(style2); row1.getCell(9).setCellStyle(style2); row1.getCell(10).setCellStyle(style2); firstSheet.setColumnWidth(0, 10000); firstSheet.setColumnWidth(1, 9000); firstSheet.setColumnWidth(2, 10000); firstSheet.setColumnWidth(3, 20000); firstSheet.setColumnWidth(4, 10000); firstSheet.setColumnWidth(5, 4000); firstSheet.setColumnWidth(6, 4000); firstSheet.setColumnWidth(7, 4000); firstSheet.setColumnWidth(8, 4000); firstSheet.setColumnWidth(9, 6000); firstSheet.setColumnWidth(10, 10000); HSSFCellStyle style3 = workbook.createCellStyle(); style3.setBorderBottom(HSSFCellStyle.BORDER_THIN); style3.setBorderTop(HSSFCellStyle.BORDER_THIN); style3.setBorderLeft(HSSFCellStyle.BORDER_THIN); style3.setBorderRight(HSSFCellStyle.BORDER_THIN); int i = 3; for (LineMatriz linha : linhastabela) { HSSFRow row = firstSheet.createRow(i); row.createCell(0).setCellValue(linha.getSistema()); row.createCell(1).setCellValue(linha.getModulo()); row.createCell(2).setCellValue(linha.getDiretorio()); row.createCell(3).setCellValue(linha.getArquivo()); row.createCell(4).setCellValue(linha.getAmbiente()); row.createCell(5).setCellValue(linha.getTipoArquivo()); row.createCell(6).setCellValue(linha.getAcao()); row.createCell(7).setCellValue(linha.getData()); row.createCell(8).setCellValue(linha.getRevisao()); row.createCell(9).setCellValue(linha.getAutor()); row.createCell(10).setCellValue(linha.getMotivo()); row.getCell(0).setCellStyle(style3); row.getCell(1).setCellStyle(style3); row.getCell(2).setCellStyle(style3); row.getCell(3).setCellStyle(style3); row.getCell(4).setCellStyle(style3); row.getCell(5).setCellStyle(style3); row.getCell(6).setCellStyle(style3); row.getCell(7).setCellStyle(style3); row.getCell(8).setCellStyle(style3); row.getCell(9).setCellStyle(style3); row.getCell(10).setCellStyle(style3); i++; } // fim do for firstSheet.addMergedRegion(new CellRangeAddress(1, //first row (0-based) 1, //last row (0-based) 0, //first column (0-based) 10 //last column (0-based) )); workbook.write(fos); JOptionPane.showMessageDialog(null, "Arquivo criado em " + nomeArquivo); } catch (Exception e) { e.printStackTrace(); JOptionPane.showMessageDialog(null, e.getMessage()); System.out.println("Erro ao exportar arquivo"); } finally { try { fos.flush(); fos.close(); } catch (Exception e) { JOptionPane.showMessageDialog(null, e.getMessage()); } } }
From source file:model.Reports.java
public void generateXSL(List<SmsOutUserBean> smsOutUserBeans, int count) { try {/* w w w . ja va 2 s .co m*/ HSSFWorkbook wb = new HSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); HSSFSheet sheet = wb.createSheet("Users_Sheet1"); 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("SMS OUT REPORT"); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$H$1")); String[] titles = { "Mobile", "Source Address", "Message", "Time Sent", "Last Update", "User", "Status", "Number of SMS", }; HSSFRow row = sheet.createRow(1); row.setHeightInPoints(40); Cell headerCell; for (int i = 0; i < titles.length; i++) { headerCell = row.createCell(i); headerCell.setCellValue(titles[i]); headerCell.setCellStyle(styles.get("header")); } int rowNum = 2; for (SmsOutUserBean bean : smsOutUserBeans) { row = sheet.createRow(rowNum); row.createCell(0).setCellValue(bean.getSmsOutModel().getDestinationAddress()); row.createCell(1).setCellValue(bean.getSmsOutModel().getSourceAddress()); row.createCell(2).setCellValue(bean.getSmsOutModel().getMessagePayload()); row.createCell(3) .setCellValue(HelperUtil.conDateToString(bean.getSmsOutModel().getTimeSubmitted())); row.createCell(4) .setCellValue(HelperUtil.conDateToString(bean.getSmsOutModel().getTimeProcessed())); row.createCell(5).setCellValue(bean.getUserBean().getUsername()); row.createCell(6).setCellValue(bean.getSmsOutModel().getRealStatus()); row.createCell(7).setCellValue(bean.getSmsOutModel().getSmsCount()); rowNum++; } sheet.setColumnWidth(0, 20 * 256); //30 characters wide sheet.setColumnWidth(1, 15 * 256); for (int i = 2; i < 5; i++) { sheet.setColumnWidth(i, 20 * 256); //6 characters wide } sheet.setColumnWidth(5, 10 * 256); sheet.setColumnWidth(6, 20 * 256); sheet.setColumnWidth(7, 10 * 256); //10 characters wide FacesContext context = FacesContext.getCurrentInstance(); HttpServletResponse res = (HttpServletResponse) context.getExternalContext().getResponse(); res.setContentType("application/vnd.ms-excel"); res.setHeader("Content-disposition", "attachment;filename=mydata.xls"); ServletOutputStream out = res.getOutputStream(); wb.write(out); out.flush(); out.close(); FacesContext.getCurrentInstance().responseComplete(); } catch (Exception e) { e.printStackTrace(); } }
From source file:module.siadap.domain.SiadapRootModule.java
License:Open Source License
private void populateSheet(HSSFSheet sheetToWriteTo, boolean considerQuotas, UnitSiadapWrapper unitToSearchIn, HSSFWorkbook wb, boolean shouldIncludeEndOfRole, boolean includeHarmonizationResponsibles, boolean shouldIncludeUniverse) { CreationHelper creationHelper = wb.getCreationHelper(); // make the sheet fit the page PrintSetup ps = sheetToWriteTo.getPrintSetup(); sheetToWriteTo.setAutobreaks(true);// w ww .ja va2 s . c om ps.setFitHeight((short) 1); ps.setFitWidth((short) 1); /* ** styles ** */ // CostCenter style HSSFFont costCenterFont = wb.createFont(); costCenterFont.setColor(HSSFColor.DARK_BLUE.index); costCenterFont.setFontHeightInPoints((short) 12); costCenterFont.setBoldweight(Font.BOLDWEIGHT_BOLD); CellStyle costCenterStyle = wb.createCellStyle(); costCenterStyle.setFont(costCenterFont); // make the Unit header style CellStyle unitHeaderStyle = wb.createCellStyle(); unitHeaderStyle.setBorderBottom(CellStyle.BORDER_THIN); unitHeaderStyle.setBorderTop(CellStyle.BORDER_THIN); unitHeaderStyle.setBorderLeft(CellStyle.BORDER_THIN); unitHeaderStyle.setBorderRight(CellStyle.BORDER_THIN); unitHeaderStyle.setAlignment(CellStyle.ALIGN_CENTER); unitHeaderStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); HSSFFont headerFont = wb.createFont(); headerFont.setFontHeightInPoints((short) 12); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setItalic(true); unitHeaderStyle.setFont(headerFont); // make the default name style CellStyle defaultTextNameStyle = wb.createCellStyle(); defaultTextNameStyle.setBorderLeft(CellStyle.BORDER_THIN); defaultTextNameStyle.setBorderRight(CellStyle.BORDER_THIN); defaultTextNameStyle.setBorderBottom(CellStyle.BORDER_NONE); defaultTextNameStyle.setBorderTop(CellStyle.BORDER_NONE); defaultTextNameStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); HSSFFont defaultFont = wb.createFont(); defaultFont.setFontHeightInPoints((short) 11); defaultTextNameStyle.setFont(defaultFont); // make the last line name style CellStyle defaultTextNameLastStyle = wb.createCellStyle(); defaultTextNameLastStyle.setBorderLeft(CellStyle.BORDER_THIN); defaultTextNameLastStyle.setBorderRight(CellStyle.BORDER_THIN); defaultTextNameLastStyle.setBorderBottom(CellStyle.BORDER_THIN); defaultTextNameLastStyle.setBorderTop(CellStyle.BORDER_NONE); defaultTextNameLastStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); defaultTextNameLastStyle.setFont(defaultFont); // make the default IST-ID last line style CellStyle defaultTextIstIdLastStyle = wb.createCellStyle(); defaultTextIstIdLastStyle.setBorderLeft(CellStyle.BORDER_THIN); defaultTextIstIdLastStyle.setBorderBottom(CellStyle.BORDER_THIN); defaultTextIstIdLastStyle.setBorderTop(CellStyle.BORDER_NONE); defaultTextIstIdLastStyle.setBorderRight(CellStyle.BORDER_THIN); defaultTextIstIdLastStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); defaultTextIstIdLastStyle.setAlignment(CellStyle.ALIGN_CENTER); defaultTextIstIdLastStyle.setFont(defaultFont); // make the default IST-ID style CellStyle defaultTextIstIdStyle = wb.createCellStyle(); defaultTextIstIdStyle.setBorderLeft(CellStyle.BORDER_THIN); defaultTextIstIdStyle.setBorderBottom(CellStyle.BORDER_NONE); defaultTextIstIdStyle.setBorderTop(CellStyle.BORDER_NONE); defaultTextIstIdStyle.setBorderRight(CellStyle.BORDER_THIN); defaultTextIstIdStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); defaultTextIstIdStyle.setAlignment(CellStyle.ALIGN_CENTER); defaultTextIstIdStyle.setFont(defaultFont); // header style // CellStyle headerStyle = wb.createCellStyle(); // HSSFFont headerFont = wb.createFont(); // headerFont.setFontName(HSSFFont.FONT_ARIAL); // headerFont.setFontHeightInPoints((short) 10); // headerStyle.setFont(headerFont); // // first line style CellStyle firstLineStyle = wb.createCellStyle(); HSSFFont firstLineFont = wb.createFont(); firstLineFont.setColor(HSSFColor.DARK_BLUE.index); firstLineFont.setFontHeightInPoints((short) 14); firstLineFont.setBoldweight(Font.BOLDWEIGHT_BOLD); firstLineStyle.setFont(firstLineFont); firstLineStyle.setAlignment(CellStyle.ALIGN_CENTER); firstLineStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); // second line style CellStyle secondLineStyle = wb.createCellStyle(); HSSFFont secondLineFont = wb.createFont(); secondLineFont.setBoldweight(Font.BOLDWEIGHT_BOLD); secondLineFont.setFontHeightInPoints((short) 14); secondLineStyle.setFont(secondLineFont); secondLineStyle.setAlignment(CellStyle.ALIGN_CENTER); secondLineStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); // the style for Unit Harmonization responsibles - title CellStyle unitHarmonizationTitleStyle = wb.createCellStyle(); // the BLUE title font - is equal to 'firstLineFont' unitHarmonizationTitleStyle.setFont(firstLineFont); // now we just have to shade it unitHarmonizationTitleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); unitHarmonizationTitleStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); unitHarmonizationTitleStyle.setAlignment(CellStyle.ALIGN_CENTER); // the style for Unit Harmonization responsibles - normal // let's create the BLUE Arial 14 font for the responsibles of // harmonization HSSFFont harmonizationResponsibleFont = wb.createFont(); harmonizationResponsibleFont.setColor(HSSFColor.DARK_BLUE.index); harmonizationResponsibleFont.setFontHeightInPoints((short) 14); CellStyle unitHarmonizationResponsibleStyle = wb.createCellStyle(); unitHarmonizationResponsibleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); unitHarmonizationResponsibleStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); unitHarmonizationResponsibleStyle.setFont(harmonizationResponsibleFont); unitHarmonizationResponsibleStyle.setAlignment(CellStyle.ALIGN_CENTER); /* ** END of styles ** */ /* ** Immutable IST header ** */ HSSFHeader header = sheetToWriteTo.getHeader(); header.setCenter(HSSFHeader.font("Arial", "Normal") + HSSFHeader.fontSize((short) 10)); header.setCenter("Instituto Superior Tcnico"); int rowIndex = START_ROW_INDEX; int cellIndex = START_CELL_INDEX; int firstLineIndex = rowIndex++; int secondLineIndex = rowIndex++; /* ** Write the first lines with the dates ** */ HSSFRow row = sheetToWriteTo.createRow(firstLineIndex); HSSFCell cell = row.createCell(cellIndex); cell.setCellValue("SIADAP - LISTA DE AVALIADORES " + unitToSearchIn.getYear()); cell.setCellStyle(firstLineStyle); sheetToWriteTo .addMergedRegion(new CellRangeAddress(firstLineIndex, firstLineIndex, cellIndex, cellIndex + 3)); // second line if (!considerQuotas) { cellIndex = START_CELL_INDEX; row = sheetToWriteTo.createRow(secondLineIndex); cell = row.createCell(cellIndex); cell.setCellValue("PESSOAL CONTRATADO PELA ADIST"); cell.setCellStyle(secondLineStyle); } /* ** write the IST logo ** */ int pictureIdx = wb.addPicture(istLogoBytes, Workbook.PICTURE_TYPE_PNG); HSSFPatriarch drawingPatriarch = sheetToWriteTo.createDrawingPatriarch(); ClientAnchor clientAnchor = creationHelper.createClientAnchor(); clientAnchor.setCol1(cellIndex); clientAnchor.setRow1(rowIndex); HSSFPicture picture = drawingPatriarch.createPicture(clientAnchor, pictureIdx); // let's give the next item some space rowIndex += 6; /* ** Dynamic IST footer ** */ HSSFFooter footer = sheetToWriteTo.getFooter(); footer.setLeft("Lista gerada em: " + HSSFFooter.date() + " " + HSSFFooter.time()); footer.setCenter(HSSFFooter.page()); footer.setRight("SIADAP - Lista de avaliadores " + unitToSearchIn.getYear()); for (UnitSiadapWrapper eachUnit : unitToSearchIn .getAllChildUnits(unitToSearchIn.getConfiguration().getUnitRelations())) { Collection<Person> harmonizationResponsibles = eachUnit.getHarmonizationResponsibles(); if (includeHarmonizationResponsibles && !harmonizationResponsibles.isEmpty()) { // let's add the section stating the responsible for // Harmonization cellIndex = START_CELL_INDEX; row = sheetToWriteTo.createRow(++rowIndex); // let's merge the row sheetToWriteTo.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, cellIndex, cellIndex + 3)); cell = row.createCell(cellIndex); cell.setCellStyle(unitHarmonizationTitleStyle); cell.setCellValue("UNIDADE DE HARMONIZAO: " + eachUnit.getName()); // a 'blank' styled line row = sheetToWriteTo.createRow(++rowIndex); // merge it sheetToWriteTo.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, cellIndex, cellIndex + 3)); row.createCell(cellIndex).setCellStyle(unitHarmonizationResponsibleStyle); // each responsible has one of the following lines for (Person harmonizationResponsible : harmonizationResponsibles) { cellIndex = START_CELL_INDEX; row = sheetToWriteTo.createRow(++rowIndex); // merge it sheetToWriteTo .addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, cellIndex, cellIndex + 3)); cell = row.createCell(cellIndex); cell.setCellStyle(unitHarmonizationResponsibleStyle); cell.setCellValue("RESPONS?VEL PELA HARMONIZAO: " + harmonizationResponsible.getName()); } // and let's add an extra 'blank' styled line row = sheetToWriteTo.createRow(++rowIndex); sheetToWriteTo.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, cellIndex, cellIndex + 3)); row.createCell(cellIndex).setCellStyle(unitHarmonizationResponsibleStyle); // and a regular one! (skip one in the index) ++rowIndex; } if (eachUnit.getQuotaAwareTotalPeopleWorkingInUnit(false, considerQuotas) > 0) { row = sheetToWriteTo.createRow(++rowIndex); cellIndex = START_CELL_INDEX; // write the unit name and cost center String unitNameWithCC = eachUnit.getUnit().getPartyName().getContent(); if (eachUnit.getUnit().getPartyTypesSet().contains(PartyType.readBy("CostCenter"))) { unitNameWithCC += " - " + eachUnit.getUnit().getAcronym(); } cell = row.createCell(cellIndex++); sheetToWriteTo.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, --cellIndex, ++cellIndex)); cell.setCellValue(unitNameWithCC); cell.setCellStyle(costCenterStyle); /* **** write the Unit header ***** */ // restart the cell's index cellIndex = START_CELL_INDEX; // IST id avaliado int firstLineAfterUnitNameIndex = ++rowIndex; int secondLineAfterUnitNameIndex = ++rowIndex; row = sheetToWriteTo.createRow(firstLineAfterUnitNameIndex); cell = row.createCell(cellIndex); cell.setCellStyle(unitHeaderStyle); cell.setCellValue("IST id."); row = sheetToWriteTo.createRow(secondLineAfterUnitNameIndex); cell = row.createCell(cellIndex); cell.setCellStyle(unitHeaderStyle); // merge the IST id sheetToWriteTo.addMergedRegion(new CellRangeAddress(firstLineAfterUnitNameIndex, secondLineAfterUnitNameIndex, cellIndex, cellIndex)); // Nome avaliado row = sheetToWriteTo.getRow(firstLineAfterUnitNameIndex); cell = row.createCell(++cellIndex); cell.setCellStyle(unitHeaderStyle); cell.setCellValue("Nome"); row = sheetToWriteTo.getRow(secondLineAfterUnitNameIndex); cell = row.createCell(cellIndex); cell.setCellStyle(unitHeaderStyle); // merge sheetToWriteTo.addMergedRegion(new CellRangeAddress(firstLineAfterUnitNameIndex, secondLineAfterUnitNameIndex, cellIndex, cellIndex)); if (shouldIncludeUniverse) { // SIADAP do avaliado row = sheetToWriteTo.getRow(firstLineAfterUnitNameIndex); cell = row.createCell(++cellIndex); cell.setCellStyle(unitHeaderStyle); cell.setCellValue("SIADAP"); row = sheetToWriteTo.getRow(secondLineAfterUnitNameIndex); cell = row.createCell(cellIndex); cell.setCellStyle(unitHeaderStyle); // merge sheetToWriteTo.addMergedRegion(new CellRangeAddress(firstLineAfterUnitNameIndex, secondLineAfterUnitNameIndex, cellIndex, cellIndex)); } // Ist id do avaliador row = sheetToWriteTo.getRow(firstLineAfterUnitNameIndex); cell = row.createCell(++cellIndex); cell.setCellStyle(unitHeaderStyle); cell.setCellValue("IST id."); row = sheetToWriteTo.getRow(secondLineAfterUnitNameIndex); cell = row.createCell(cellIndex); cell.setCellStyle(unitHeaderStyle); // merge sheetToWriteTo.addMergedRegion(new CellRangeAddress(firstLineAfterUnitNameIndex, secondLineAfterUnitNameIndex, cellIndex, cellIndex)); // avaliador row = sheetToWriteTo.getRow(firstLineAfterUnitNameIndex); cell = row.createCell(++cellIndex); cell.setCellStyle(unitHeaderStyle); cell.setCellValue("Avaliador"); row = sheetToWriteTo.getRow(secondLineAfterUnitNameIndex); cell = row.createCell(cellIndex); cell.setCellStyle(unitHeaderStyle); // merge sheetToWriteTo.addMergedRegion(new CellRangeAddress(firstLineAfterUnitNameIndex, secondLineAfterUnitNameIndex, cellIndex, cellIndex)); List<PersonSiadapWrapper> listToUse = (considerQuotas) ? eachUnit.getUnitEmployeesWithQuotas(false) : eachUnit.getUnitEmployeesWithoutQuotas(true); // now let's take care of exporting the persons for (PersonSiadapWrapper personWrapper : listToUse) { row = sheetToWriteTo.createRow(++rowIndex); // restart the cell's index cellIndex = START_CELL_INDEX; String istIdEvaluated = personWrapper.getPerson().getUser().getUsername(); cell = row.createCell(cellIndex++); cell.setCellValue(istIdEvaluated); cell.setCellStyle(defaultTextIstIdStyle); String nameEvaluatedPerson = personWrapper.getPerson().getName(); cell = row.createCell(cellIndex++); cell.setCellValue(nameEvaluatedPerson); cell.setCellStyle(defaultTextNameStyle); if (shouldIncludeUniverse) { Siadap siadap = personWrapper.getSiadap(); String siadapUniverseToBeWritten = (siadap == null || siadap.getDefaultSiadapUniverse() == null) ? "No definido" : siadap.getDefaultSiadapUniverse().getLocalizedName(); cell = row.createCell(cellIndex++); cell.setCellValue(siadapUniverseToBeWritten); cell.setCellStyle(defaultTextNameStyle); } PersonSiadapWrapper evaluatorWrapper = personWrapper.getEvaluator(); String istIdEvaluator = evaluatorWrapper == null ? "-" : evaluatorWrapper.getPerson().getUser().getUsername(); cell = row.createCell(cellIndex++); cell.setCellValue(istIdEvaluator); cell.setCellStyle(defaultTextIstIdStyle); String nameEvaluatorWrapper = evaluatorWrapper == null ? "-" : evaluatorWrapper.getName(); cell = row.createCell(cellIndex++); cell.setCellValue(nameEvaluatorWrapper); cell.setCellStyle(defaultTextNameStyle); } // let's make a bottom border on the last four cells for (int i = START_CELL_INDEX; i < START_CELL_INDEX + 4; i++) { cell = row.getCell(i); // let's diferentaitate between the IST-id and the name if (i == START_CELL_INDEX || i == START_CELL_INDEX + 2) // first // cell, // IST-ID // then. // or // third // cell // the // other // IST-ID { cell.setCellStyle(defaultTextIstIdLastStyle); } else { cell.setCellStyle(defaultTextNameLastStyle); } } row = sheetToWriteTo.createRow(++rowIndex); row = sheetToWriteTo.createRow(++rowIndex); } } sheetToWriteTo.autoSizeColumn(START_CELL_INDEX); sheetToWriteTo.autoSizeColumn(START_CELL_INDEX + 1); sheetToWriteTo.autoSizeColumn(START_CELL_INDEX + 2); sheetToWriteTo.autoSizeColumn(START_CELL_INDEX + 3); sheetToWriteTo.autoSizeColumn(START_CELL_INDEX + 4); // now let's resize the logo picture.resize(); }
From source file:mx.avanti.siract.ui.FiltrosBeanUI.java
public void Export2ExcelSemaforoProgramasEd() throws Throwable { //variables listaux ReporteAux reporteUI = new ReporteAux(); // objeto de hoja de excel HSSFWorkbook workbook = new HSSFWorkbook(); String nombreLibro = ""; //guardamos imagen UABC en Documento URL location = FiltrosBeanUI.class.getProtectionDomain().getCodeSource().getLocation(); String path = location.getFile(); String replace = path.replace("FiltrosBeanUI.class", "uabclogo.png"); //InputStream uabc_image = new FileInputStream(replace); //InputStream uabc_image = new FileInputStream("C://decode/uabclogo.png"); InputStream uabc_image = new FileInputStream("/home/user/decode/uabclogo.png"); byte[] bytes = IOUtils.toByteArray(uabc_image); int uabcLogo = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG); uabc_image.close();//from w w w.j a va 2 s . c o m //definimos estilo a celda titulo de los encabezados con logo escuela HSSFCellStyle style = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); font.setFontName(HSSFFont.FONT_ARIAL); font.setFontHeightInPoints((short) 20); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.GREEN.index); style.setFont(font); //setCellStyle(style); //definimos los bordes de celdas en tabla HSSFCellStyle borderstabla = workbook.createCellStyle(); borderstabla.setBorderBottom(HSSFCellStyle.BORDER_THIN); borderstabla.setBorderTop(HSSFCellStyle.BORDER_THIN); borderstabla.setBorderRight(HSSFCellStyle.BORDER_THIN); borderstabla.setBorderLeft(HSSFCellStyle.BORDER_THIN); font = workbook.createFont(); borderstabla.setFont(font); //definimos el estilo de las cabezeras de una tabla HSSFCellStyle headerTabla = workbook.createCellStyle(); HSSFFont fontwit = workbook.createFont(); fontwit.setColor(HSSFColor.WHITE.index); headerTabla.setBorderBottom(HSSFCellStyle.BORDER_THIN); headerTabla.setBorderTop(HSSFCellStyle.BORDER_THIN); headerTabla.setBorderRight(HSSFCellStyle.BORDER_THIN); headerTabla.setBorderLeft(HSSFCellStyle.BORDER_THIN); headerTabla.setFillForegroundColor(HSSFColor.GREEN.index); headerTabla.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); headerTabla.setFont(fontwit); //aqui cambie headerTabla.setWrapText(true); //aqui cambie //if(criterio.equalsIgnoreCase("programa_educativo")){ HSSFSheet sheet = workbook.createSheet("Programas Educativos"); nombreLibro = "- Director"; // if(reporte.equalsIgnoreCase("entregados")){ nombreLibro = "RACT General " + nombreLibro; //definimos encabezado sheet = cabezeraGeneralExcel(sheet, uabcLogo, style); HSSFRow row1 = sheet.createRow(7); row1.setHeight((short) 600); HSSFCell cell = row1.createCell(1); cell.setCellValue( "Concentrado de Reporte de Avance de Contenido Temtico General por Programa Educativo"); cell.setCellStyle(style); // HSSFRow row1 = sheet.createRow(14); // row1.setHeightInPoints(56); // HSSFRow row2 = sheet.createRow(28); // row2.setHeightInPoints(56); // HSSFRow row3 = sheet.createRow(42); // row3.setHeightInPoints(56); //aqui cambie jesus ruelas //for(int i=0;i<28;i++){ // sheet.autoSizeColumn(1); // sheet.autoSizeColumn(2); // sheet.autoSizeColumn(3); // sheet.autoSizeColumn(4); //merge cells de Total de RACT General de + ProgEd sheet.addMergedRegion(new CellRangeAddress(13, 13, 1, 5)); sheet.addMergedRegion(new CellRangeAddress(13, 13, 7, 11)); sheet.addMergedRegion(new CellRangeAddress(13, 13, 13, 17)); sheet.addMergedRegion(new CellRangeAddress(13, 13, 19, 23)); sheet.addMergedRegion(new CellRangeAddress(13, 13, 25, 29)); sheet.addMergedRegion(new CellRangeAddress(27, 27, 1, 5)); sheet.addMergedRegion(new CellRangeAddress(27, 27, 7, 11)); sheet.addMergedRegion(new CellRangeAddress(27, 27, 13, 17)); sheet.addMergedRegion(new CellRangeAddress(27, 27, 19, 23)); sheet.addMergedRegion(new CellRangeAddress(27, 27, 25, 29)); sheet.addMergedRegion(new CellRangeAddress(41, 41, 1, 5)); sheet.addMergedRegion(new CellRangeAddress(41, 41, 7, 11)); sheet.addMergedRegion(new CellRangeAddress(41, 41, 13, 17)); sheet.addMergedRegion(new CellRangeAddress(41, 41, 19, 23)); sheet.addMergedRegion(new CellRangeAddress(41, 41, 25, 29)); sheet.addMergedRegion(new CellRangeAddress(55, 55, 1, 5)); sheet.addMergedRegion(new CellRangeAddress(55, 55, 7, 11)); sheet.addMergedRegion(new CellRangeAddress(55, 55, 13, 17)); sheet.addMergedRegion(new CellRangeAddress(55, 55, 19, 23)); sheet.addMergedRegion(new CellRangeAddress(55, 55, 25, 29)); sheet.addMergedRegion(new CellRangeAddress(69, 69, 1, 5)); sheet.addMergedRegion(new CellRangeAddress(69, 69, 7, 11)); sheet.addMergedRegion(new CellRangeAddress(69, 69, 13, 17)); sheet.addMergedRegion(new CellRangeAddress(69, 69, 19, 23)); sheet.addMergedRegion(new CellRangeAddress(69, 69, 25, 29)); //merge cells de programa educativo sheet.addMergedRegion(new CellRangeAddress(11, 11, 2, 3)); sheet.addMergedRegion(new CellRangeAddress(11, 11, 8, 9)); sheet.addMergedRegion(new CellRangeAddress(11, 11, 14, 15)); sheet.addMergedRegion(new CellRangeAddress(11, 11, 20, 21)); sheet.addMergedRegion(new CellRangeAddress(11, 11, 26, 27)); sheet.addMergedRegion(new CellRangeAddress(25, 25, 2, 3)); sheet.addMergedRegion(new CellRangeAddress(25, 25, 8, 9)); sheet.addMergedRegion(new CellRangeAddress(25, 25, 14, 15)); sheet.addMergedRegion(new CellRangeAddress(25, 25, 20, 21)); sheet.addMergedRegion(new CellRangeAddress(25, 25, 26, 27)); sheet.addMergedRegion(new CellRangeAddress(39, 39, 2, 3)); sheet.addMergedRegion(new CellRangeAddress(39, 39, 8, 9)); sheet.addMergedRegion(new CellRangeAddress(39, 39, 14, 15)); sheet.addMergedRegion(new CellRangeAddress(39, 39, 20, 21)); sheet.addMergedRegion(new CellRangeAddress(39, 39, 26, 27)); sheet.addMergedRegion(new CellRangeAddress(53, 53, 2, 3)); sheet.addMergedRegion(new CellRangeAddress(53, 53, 8, 9)); sheet.addMergedRegion(new CellRangeAddress(53, 53, 14, 15)); sheet.addMergedRegion(new CellRangeAddress(53, 53, 20, 21)); sheet.addMergedRegion(new CellRangeAddress(53, 53, 26, 27)); sheet.addMergedRegion(new CellRangeAddress(67, 67, 2, 3)); sheet.addMergedRegion(new CellRangeAddress(67, 67, 8, 9)); sheet.addMergedRegion(new CellRangeAddress(67, 67, 14, 15)); sheet.addMergedRegion(new CellRangeAddress(67, 67, 20, 21)); sheet.addMergedRegion(new CellRangeAddress(67, 67, 26, 27)); //} //aqui cambie jesus ruelas //mostraremos tabla con // Programas educativos // Total Racs entregados // Total Esperados //preparamos informacion para insertar List<Programaeducativo> programasByUnidad = getProgramasByUnidad(); List<Planestudio> planesByPrograma = getPlanesByPrograma(); ArrayList<String> listaProgEdContar = esperadosDelegate.getSemadoroProgEdValor(1); //setCellStyle(style); //setExDat(sheet, 7, 1, "Concentrado de Reporte de Avance de Contenido Temtico General por Programa Educativo"); //imrpiimiremos la tabla // Definimos los encabezados de la tabla int pos = 0; int row = 11; int col = 1; Boolean bandPe = false; for (Programaeducativo pe : programasByUnidad) { for (String peContar : listaProgEdContar) { String[] contarEntEspProgEd; if (listaProgEdContar.size() >= pos) { if (peContar.contains(pe.getPednombre())) { contarEntEspProgEd = listaProgEdContar.get(pos).split("-"); setExDat(sheet, row, col, "Clave"); setExDat(sheet, row, col + 1, "Programa Educativo"); setExDat(sheet, row, col + 3, "Plan de Estudios"); setExDat(sheet, row, col + 4, "Responsable"); // if (contarEntEspProgEd[0].equalsIgnoreCase(programasByUnidad.get(0).getPednombre())) { setExDat(sheet, row + 1, col, pe.getPedclave()); setExDat(sheet, row + 1, col + 1, pe.getPednombre()); if (!(planesByPrograma.isEmpty())) { setExDat(sheet, row + 1, col + 2, planesByPrograma.get(0).getPesvigenciaPlan()); } setExDat(sheet, row + 1, col + 3, ""); // } setExDat(sheet, row + 2, col, "Total de RACT General de " + programasByUnidad.get(0).getPednombre()); setExDat(sheet, row + 3, col, "Resumen de totales \r\n por numero de RACT"); setExDat(sheet, row + 3, col + 1, "Total entregados"); setExDat(sheet, row + 3, col + 2, "%"); setExDat(sheet, row + 3, col + 3, "Total esperados"); setExDat(sheet, row + 3, col + 4, "%"); sheet.getRow(row + 3).getCell(1);//aqui modifique jesus ruelas float porcentEnt = (Float.parseFloat(contarEntEspProgEd[1])) / (Float.parseFloat(contarEntEspProgEd[2])) * 100; float porcentEsp = 100 - porcentEnt; // setExDat(sheet, 15, 1, "Todos los RACTS"); // setExDat(sheet, 15, 2, contarEntEspProgEd[1]); // setExDat(sheet, 15, 3, "" + porcentEnt + "%"); // setExDat(sheet, 15, 4, contarEntEspProgEd[2]); // setExDat(sheet, 15, 5, "" + porcentEsp + "%"); //String[] contarEntEspProgEd; String[] contarEntRact1; String[] contarEntRact2; String[] contarEntRact3; int esperadosProgEdUnicoRact = 0; contarEntRact1 = contarEntEspProgEd[3].split(":"); contarEntRact2 = contarEntEspProgEd[4].split(":"); contarEntRact3 = contarEntEspProgEd[5].split(":"); esperadosProgEdUnicoRact = (Integer.parseInt(contarEntEspProgEd[2])) / 3; float contarPorcentEntRact1 = (Float.parseFloat(contarEntRact1[1])) / ((float) esperadosProgEdUnicoRact) * 100; float contarPorcentEntRact2 = (Float.parseFloat(contarEntRact2[1])) / ((float) esperadosProgEdUnicoRact) * 100; float contarPorcentEntRact3 = (Float.parseFloat(contarEntRact3[1])) / ((float) esperadosProgEdUnicoRact) * 100; float contarPorcentEspRact1 = 100 - contarPorcentEntRact1; float contarPorcentEspRact2 = 100 - contarPorcentEntRact2; float contarPorcentEspRact3 = 100 - contarPorcentEntRact3; setExDat(sheet, row + 4, col, "RACT 1"); setExDat(sheet, row + 4, col + 1, contarEntRact1[1]); setExDat(sheet, row + 4, col + 2, "" + contarPorcentEntRact1 + "%"); setExDat(sheet, row + 4, col + 3, esperadosProgEdUnicoRact); setExDat(sheet, row + 4, col + 4, "" + contarPorcentEspRact1 + "%"); setExDat(sheet, row + 5, col, "RACT 2"); setExDat(sheet, row + 5, col + 1, contarEntRact2[1]); setExDat(sheet, row + 5, col + 2, "" + contarPorcentEntRact2 + "%"); setExDat(sheet, row + 5, col + 3, esperadosProgEdUnicoRact); setExDat(sheet, row + 5, col + 4, "" + contarPorcentEspRact2 + "%"); setExDat(sheet, row + 6, col, "RACT 3"); setExDat(sheet, row + 6, col + 1, contarEntRact3[1]); setExDat(sheet, row + 6, col + 2, "" + contarPorcentEntRact3 + "%"); setExDat(sheet, row + 6, col + 3, esperadosProgEdUnicoRact); setExDat(sheet, row + 6, col + 4, "" + contarPorcentEspRact3 + "%"); setExDat(sheet, row + 7, col, "Todos los RACTS"); setExDat(sheet, row + 7, col + 1, contarEntEspProgEd[1]); setExDat(sheet, row + 7, col + 2, "" + porcentEnt + "%"); setExDat(sheet, row + 7, col + 3, contarEntEspProgEd[2]); setExDat(sheet, row + 7, col + 4, "" + porcentEsp + "%"); setStyleCell(sheet, headerTabla, row, col); setStyleCell(sheet, headerTabla, row, col + 1); setStyleCell(sheet, headerTabla, row, col + 2); setStyleCell(sheet, headerTabla, row, col + 3); setStyleCell(sheet, headerTabla, row, col + 4); setStyleCell(sheet, headerTabla, row + 2, col); setStyleCell(sheet, headerTabla, row + 2, col + 1); setStyleCell(sheet, headerTabla, row + 2, col + 2); setStyleCell(sheet, headerTabla, row + 2, col + 3); setStyleCell(sheet, headerTabla, row + 3, col); setStyleCell(sheet, headerTabla, row + 3, col + 1); setStyleCell(sheet, headerTabla, row + 3, col + 2); setStyleCell(sheet, headerTabla, row + 3, col + 3); setStyleCell(sheet, headerTabla, row + 3, col + 4); bandPe = true; pos++; } } if (bandPe == true) { if (col >= 25) { row = row + 14; col = -5; } col = col + 6; bandPe = false; } } } /*sheet.autoSizeColumn(2); sheet.autoSizeColumn(3); sheet.autoSizeColumn(4);*/ //imprimimos la informacion en su lugar int prow = 14; for (Programaeducativo programa : programasByUnidad) { if (selectProgramEducativo.contains(programa.getPedid().toString())) { setExDat(sheet, prow, 17, programa.getPednombre()); setExDat(sheet, prow, 18, 75); setExDat(sheet, prow, 19, 100); prow++; } } // } //} //finalizamos con //metodo para descargar el objeto System.out.println("Generando Excel"); if (criterio != "" && reporte != "") { FacesContext facesContext = FacesContext.getCurrentInstance(); ExternalContext externalContext = facesContext.getExternalContext(); externalContext.setResponseContentType("application/vnd.ms-excel"); externalContext.setResponseHeader("Content-Disposition", "attachment; filename=\"" + nombreLibro + ".xls\""); workbook.write(externalContext.getResponseOutputStream()); facesContext.responseComplete(); } else { System.out.println("No se Genero por: Criterio->" + criterio + " , Reporte->" + reporte); } }
From source file:net.mindengine.oculus.frontend.service.report.ExcelTestSearchReportGenerator.java
License:Open Source License
/** * Generates Excel spreadsheet.//from w ww. j a v a 2 s. co m * * @param searchResult Search result for test or document (test-case) * @throws Exception */ public void writeExcelReports(BrowseResult<?> searchResult, Long projectId, HttpServletRequest request, HttpServletResponse response) throws Exception { int cellOffset = 5; /* * Customizations which will be exported to XLS spreadsheet. */ Collection<Customization> customizationsExport = new LinkedList<Customization>(); /* * Here will be cell ids stored for each customization. This is needed because there might be a lot of merged cells for list customizations */ Map<Long, Integer> customizationCells = new HashMap<Long, Integer>(); if (projectId != null && projectId > 0) { Collection<Customization> customizations = customizationDAO.getCustomizations(projectId, unit); for (Customization customization : customizations) { //Checking if the user has selected this specific customization for exporting if ("on".equals(request.getParameter("cexport" + customization.getId()))) { customizationCells.put(customization.getId(), cellOffset); //Checking if the customization contains possible values and fetching them all if (customization.getType().equals(Customization.TYPE_CHECKLIST) || customization.getType().equals(Customization.TYPE_LIST)) { customization.setPossibleValues( customizationDAO.getCustomizationPossibleValues(customization.getId())); cellOffset += customization.getPossibleValues().size(); } else { cellOffset += 1; } customizationsExport.add(customization); } } } /* * Generating the Excel spreadsheet */ OutputStream outputStream = response.getOutputStream(); response.setContentType("application/ms-excel"); HSSFWorkbook workBook = new HSSFWorkbook(); HSSFSheet sheet = workBook.createSheet(); HSSFFont fontHeader = workBook.createFont(); fontHeader.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); fontHeader.setColor(HSSFColor.WHITE.index); HSSFCellStyle columnHeaderStyle = workBook.createCellStyle(); columnHeaderStyle.setBorderTop((short) 2); columnHeaderStyle.setBorderLeft((short) 2); columnHeaderStyle.setBorderRight((short) 2); columnHeaderStyle.setBorderBottom((short) 2); columnHeaderStyle.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index); columnHeaderStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); columnHeaderStyle.setFont(fontHeader); HSSFCellStyle columnRootHeaderStyle = workBook.createCellStyle(); //columnRootHeaderStyle.cloneStyleFrom(columnHeaderStyle); columnRootHeaderStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCell cell; HSSFRow headerRow = sheet.createRow(0); HSSFRow header2Row = sheet.createRow(1); sheet.addMergedRegion(new Region((short) 0, (short) 0, (short) 0, (short) 4)); cell = headerRow.createCell((short) 0); cell.setCellValue("Common"); cell.setCellStyle(columnRootHeaderStyle); cell = header2Row.createCell((short) 0); cell.setCellValue("Test"); cell.setCellStyle(columnHeaderStyle); cell = header2Row.createCell((short) 1); cell.setCellValue("Project"); cell.setCellStyle(columnHeaderStyle); cell = header2Row.createCell((short) 2); cell.setCellValue("Sub-Project"); cell.setCellStyle(columnHeaderStyle); cell = header2Row.createCell((short) 3); cell.setCellValue("Author"); cell.setCellStyle(columnHeaderStyle); cell = header2Row.createCell((short) 4); cell.setCellValue("Created"); cell.setCellStyle(columnHeaderStyle); for (Customization customization : customizationsExport) { int cellId = customizationCells.get(customization.getId()); if (customization.getPossibleValues() != null) { int size = customization.getPossibleValues().size(); if (size > 1) { sheet.addMergedRegion( new Region((short) 0, (short) 0, (short) cellId, (short) (cellId + size - 1))); } /* * Exporting possible values */ int offset = 0; for (CustomizationPossibleValue cpv : customization.getPossibleValues()) { cell = header2Row.createCell((short) (cellId + offset)); cell.setCellValue(cpv.getPossibleValue()); cell.setCellStyle(columnHeaderStyle); offset++; } } else { cell = header2Row.createCell((short) cellId); cell.setCellStyle(columnHeaderStyle); } cell = headerRow.createCell((short) cellId); cell.setCellValue(customization.getName()); cell.setCellStyle(columnRootHeaderStyle); } HSSFCellStyle cellStyle = workBook.createCellStyle(); HSSFCellStyle checkboxStyle = workBook.createCellStyle(); checkboxStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index); checkboxStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); checkboxStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont fontCheckbox = workBook.createFont(); fontCheckbox.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); fontCheckbox.setColor(HSSFColor.BLACK.index); checkboxStyle.setFont(fontCheckbox); HSSFCellStyle boolYesStyle = workBook.createCellStyle(); boolYesStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index); boolYesStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFCellStyle boolNoStyle = workBook.createCellStyle(); boolNoStyle.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index); boolNoStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); /* * Used in order to cache user names. Key = ID, Value = User name */ Map<Long, String> usersCache = new HashMap<Long, String>(); int j = 2; for (Object object : searchResult.getResults()) { HSSFRow row = sheet.createRow(j); String name, parentProjectName, projectName, authorName; Date date; Long objectId; if (object instanceof Test) { Test test = (Test) object; name = test.getName(); parentProjectName = test.getParentProjectName(); projectName = test.getProjectName(); authorName = test.getAuthorName(); objectId = test.getId(); date = test.getDate(); } else throw new IllegalArgumentException(object.getClass().getName()); cell = row.createCell((short) 0); cell.setCellValue(name); cell.setCellStyle(cellStyle); cell = row.createCell((short) 1); cell.setCellValue(parentProjectName); cell.setCellStyle(cellStyle); cell = row.createCell((short) 2); cell.setCellValue(projectName); cell.setCellStyle(cellStyle); cell = row.createCell((short) 3); cell.setCellValue(authorName); cell.setCellStyle(cellStyle); cell = row.createCell((short) 4); SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yyyy"); cell.setCellValue(sdf.format(date)); cell.setCellStyle(cellStyle); int offset = 5; for (Customization customization : customizationsExport) { UnitCustomizationValue ucv = customizationDAO.getUnitCustomizationValue(customization.getId(), objectId); if (customization.getType().equals(Customization.TYPE_CHECKLIST) || customization.getType().equals(Customization.TYPE_LIST)) { for (CustomizationPossibleValue cpv : customization.getPossibleValues()) { boolean matches = false; if (customization.getType().equals(Customization.TYPE_LIST)) { if (ucv != null && ucv.getValue() != null && ucv.getValue().equals("" + cpv.getId())) { matches = true; } } else { String s = "(" + cpv.getId() + ")"; if (ucv != null && ucv.getValue() != null && ucv.getValue().contains(s)) { matches = true; } } if (matches) { cell = row.createCell((short) offset); cell.setCellValue("X"); cell.setCellStyle(checkboxStyle); } offset++; } } else { if (ucv != null) { cell = row.createCell((short) offset); cell.setCellStyle(cellStyle); if (customization.getType().equals(Customization.TYPE_ASSIGNEE)) { if (ucv.getValue() != null && !ucv.getValue().isEmpty()) { try { Long userId = Long.parseLong(ucv.getValue()); /* * Chaching user names by their ids */ String userName = null; if (!usersCache.containsKey(userId)) { User user = userDAO.getUserById(userId); if (user != null) { userName = user.getName(); } else userName = ""; usersCache.put(userId, userName); } else userName = usersCache.get(userId); cell.setCellValue(userName); } catch (Exception e) { e.printStackTrace(); } } } else if (customization.getType().equals(Customization.TYPE_CHECKBOX)) { if (ucv.getValue() != null) { if (ucv.getValue().equals("true")) { cell.setCellValue("Yes"); cell.setCellStyle(boolYesStyle); } else { cell.setCellValue("No"); cell.setCellStyle(boolNoStyle); } } } else { cell.setCellValue(ucv.getValue()); } } offset++; } } j++; } /* * Making the text to fit in all cells */ for (short i = 0; i < (short) cellOffset + 1; i++) { sheet.autoSizeColumn(i); } workBook.write(outputStream); outputStream.flush(); outputStream.close(); }
From source file:net.sourceforge.fenixedu.domain.phd.reports.PhdReport.java
License:Open Source License
protected void addHeaderCell(HSSFSheet sheet, String value, int columnNumber) { HSSFRow row = sheet.getRow(0);//w w w. j a va 2 s . c o m if (row == null) { row = sheet.createRow(0); } HSSFCell cell = row.createCell(columnNumber); cell.setCellValue(value); cell.setCellStyle(headerStyle); cell.setCellValue(value); sheet.addMergedRegion(new CellRangeAddress(0, 1, columnNumber, columnNumber)); }
From source file:net.sourceforge.fenixedu.presentationTier.Action.administrativeOffice.scholarship.utl.report.ReportStudentsUTLCandidates.java
License:Open Source License
protected void addHeaders(HSSFSheet sheet) { sheet.createRow(0);// www . ja v a2 s.c o m sheet.createRow(1); addHeaderCell(sheet, getHeaderInBundle("institutionCode"), 0); addHeaderCell(sheet, getHeaderInBundle("institutionName"), 1); addHeaderCell(sheet, getHeaderInBundle("candidacyNumber"), 2); addHeaderCell(sheet, getHeaderInBundle("studentNumberForPrint"), 3); addHeaderCell(sheet, getHeaderInBundle("studentName"), 4); addHeaderCell(sheet, getHeaderInBundle("documentTypeName"), 5); addHeaderCell(sheet, getHeaderInBundle("documentNumber"), 6); addHeaderCell(sheet, getHeaderInBundle("degreeCode"), 7); addHeaderCell(sheet, getHeaderInBundle("degreeName"), 8); addHeaderCell(sheet, getHeaderInBundle("degreeTypeName"), 9); addHeaderCell(sheet, getHeaderInBundle("code"), 10); addHeaderCell(sheet, getHeaderInBundle("countNumberOfDegreeChanges"), 11); addHeaderCell(sheet, getHeaderInBundle("hasMadeDegreeChange"), 12); addHeaderCell(sheet, getHeaderInBundle("firstEnrolmentOnCurrentExecutionYear"), 13); addHeaderCell(sheet, getHeaderInBundle("regime"), 14); addHeaderCell(sheet, getHeaderInBundle("code"), 15); HSSFRow row = sheet.getRow(0); HSSFCell cell = row.createCell(16); cell.setCellValue(getHeaderInBundle("ingression.year.on.cycle.studies")); cell.setCellStyle(headerStyle); sheet.addMergedRegion(new CellRangeAddress(0, 0, 16, 18)); cell = sheet.getRow(1).createCell(16); cell.setCellValue(getHeaderInBundle("ingression.year.on.cycle.studies.year")); cell.setCellStyle(headerStyle); cell = sheet.getRow(1).createCell(17); cell.setCellValue(getHeaderInBundle("ingression.year.on.cycle.studies.count")); cell.setCellStyle(headerStyle); cell = sheet.getRow(1).createCell(18); cell.setCellValue(getHeaderInBundle("ingression.year.on.cycle.studies.integral.count")); cell.setCellStyle(headerStyle); addHeaderCell(sheet, getHeaderInBundle("numberOfDoneECTS"), 19); addHeaderCell(sheet, getHeaderInBundle("numberOfDegreeCurricularYears"), 20); addHeaderCell(sheet, getHeaderInBundle("curricularYearOneYearAgo"), 21); addHeaderCell(sheet, getHeaderInBundle("numberOfEnrolledEctsOneYearAgo"), 22); addHeaderCell(sheet, getHeaderInBundle("numberOfApprovedEctsOneYearAgo"), 23); addHeaderCell(sheet, getHeaderInBundle("curricularYearInCurrentYear"), 24); addHeaderCell(sheet, getHeaderInBundle("numberOfEnrolledECTS"), 25); addHeaderCell(sheet, getHeaderInBundle("gratuityAmount"), 26); addHeaderCell(sheet, getHeaderInBundle("numberOfMonthsExecutionYear"), 27); addHeaderCell(sheet, getHeaderInBundle("firstMonthOfPayment"), 28); addHeaderCell(sheet, getHeaderInBundle("ownerOfCETQualification"), 29); addHeaderCell(sheet, getHeaderInBundle("degreeQualificationOwner"), 30); addHeaderCell(sheet, getHeaderInBundle("masterQualificationOwner"), 31); addHeaderCell(sheet, getHeaderInBundle("phdQualificationOwner"), 32); addHeaderCell(sheet, getHeaderInBundle("ownerOfCollegeQualification"), 33); addHeaderCell(sheet, getHeaderInBundle("observations"), 34); addHeaderCell(sheet, getHeaderInBundle("lastEnrolledExecutionYear"), 35); addHeaderCell(sheet, getHeaderInBundle("nif"), 36); addHeaderCell(sheet, getHeaderInBundle("last.conclusion.academic.facts"), 37); }
From source file:net.sourceforge.fenixedu.presentationTier.Action.administrativeOffice.scholarship.utl.report.ReportStudentsUTLCandidates.java
License:Open Source License
protected void addHeaderCell(HSSFSheet sheet, String value, int columnNumber) { HSSFRow row = sheet.getRow(0);/*from w w w. jav a 2 s .co m*/ HSSFCell cell = row.createCell(columnNumber); cell.setCellValue(value); cell.setCellStyle(headerStyle); sheet.addMergedRegion(new CellRangeAddress(0, 1, columnNumber, columnNumber)); }