List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getCreationHelper
@Override
public HSSFCreationHelper getCreationHelper()
From source file:jexcelcompi.JExcelCompi.java
public void ejemplo() throws FileNotFoundException, IOException { FileInputStream fis = new FileInputStream(new File("hoja2.xls")); HSSFWorkbook wb = new HSSFWorkbook(fis); HSSFSheet sheet = wb.getSheetAt(0);//from www.ja v a 2s . c o m FormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator(); int x = 0; for (Row row : sheet) { for (Cell cell : row) { switch (formulaEvaluator.evaluateInCell(cell).getCellType()) { case Cell.CELL_TYPE_NUMERIC: { System.out.println(cell.getNumericCellValue() + "\t\t"); } //case Cell.CELL_TYPE_STRING: //System.out.println(cell.getStringCellValue()+"\t\tcadena"); x++; } } System.out.println(); } }
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);/*from w w w . j a v a 2 s . c o m*/ 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:net.vpc.app.vainruling.core.web.jsf.Vr.java
public void postProcessDataExporterXLS(Object document) { HSSFWorkbook book = (HSSFWorkbook) document; HSSFSheet sheet = book.getSheetAt(0); HSSFRow header = sheet.getRow(0);/*ww w . j av a2s . c om*/ int rowCount = sheet.getPhysicalNumberOfRows(); HSSFCellStyle headerCellStyle = book.createCellStyle(); headerCellStyle.setFillForegroundColor(HSSFColor.AQUA.index); headerCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER); HSSFCreationHelper creationHelper = book.getCreationHelper(); for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) { HSSFCell cell = header.getCell(i); cell.setCellStyle(headerCellStyle); } HSSFCellStyle intStyle = book.createCellStyle(); intStyle.setDataFormat((short) 1); HSSFCellStyle decStyle = book.createCellStyle(); decStyle.setDataFormat((short) 2); HSSFCellStyle dollarStyle = book.createCellStyle(); dollarStyle.setDataFormat((short) 5); int maxColumn = -1; Map<String, HSSFCellStyle> datFormats = new HashMap<>(); for (int rowInd = 1; rowInd < rowCount; rowInd++) { HSSFRow row = sheet.getRow(rowInd); int colCount = row.getPhysicalNumberOfCells(); if (maxColumn < colCount) { maxColumn = colCount; } for (int cellInd = 0; cellInd < colCount; cellInd++) { HSSFCell cell = row.getCell(cellInd); String strVal = cell.getStringCellValue(); if (strVal.startsWith("$")) { //do nothing } else { if (strVal.startsWith("'")) { strVal = strVal.substring(1); } if (PlatformUtils.isDouble(strVal)) { cell.setCellType(HSSFCell.CELL_TYPE_BLANK); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); if (PlatformUtils.isInteger(strVal)) { int intVal = Integer.valueOf(strVal.trim()); cell.setCellStyle(intStyle); cell.setCellValue(intVal); } else if (PlatformUtils.isDouble(strVal)) { double dblVal = Double.valueOf(strVal.trim()); cell.setCellStyle(decStyle); cell.setCellValue(dblVal); } } else { boolean isDate = false; for (String dteFormat : new String[] { "yyyy-MM-dd HH:mm:ss.SSS", "yyyy-MM-dd HH:mm:ss", "yyyy-MM-dd HH:mm", "yyyy-MM-dd", "HH:mm" }) { if (PlatformUtils.isDate(strVal, dteFormat)) { HSSFCellStyle dateStyle = datFormats.get(dteFormat.trim()); if (dateStyle == null) { dateStyle = book.createCellStyle(); dateStyle.setDataFormat(creationHelper.createDataFormat().getFormat(dteFormat)); datFormats.put(dteFormat, dateStyle); } cell.setCellStyle(dateStyle); try { cell.setCellValue(new SimpleDateFormat(dteFormat).parse(strVal)); } catch (ParseException e) { // } isDate = true; break; } } } } } } if (maxColumn >= 0) { for (int cellInd = 0; cellInd < maxColumn; cellInd++) { sheet.autoSizeColumn(cellInd); } } }
From source file:org.deployom.core.AuditService.java
License:Open Source License
public HSSFWorkbook saveAudit() { // Create book HSSFWorkbook workbook = new HSSFWorkbook(); HSSFCreationHelper creationHelper = workbook.getCreationHelper(); // Default Style HSSFCellStyle style = workbook.createCellStyle(); style.setWrapText(true);//w ww .j av a2 s . c o m HSSFFont font = workbook.createFont(); font.setFontName("Courier New"); style.setFont(font); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); // Header Style HSSFCellStyle styleHeader = workbook.createCellStyle(); styleHeader.cloneStyleFrom(style); styleHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); font = workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.WHITE.index); styleHeader.setFillForegroundColor(IndexedColors.BLACK.getIndex()); styleHeader.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); styleHeader.setFont(font); // Error Style HSSFCellStyle styleError = workbook.createCellStyle(); styleError.cloneStyleFrom(style); styleError.setFillForegroundColor(IndexedColors.CORAL.getIndex()); styleError.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); styleError.setWrapText(true); // Link Style HSSFCellStyle styleLink = workbook.createCellStyle(); styleLink.cloneStyleFrom(style); font = workbook.createFont(); font.setUnderline(HSSFFont.U_SINGLE); font.setColor(IndexedColors.BLUE.getIndex()); styleLink.setFont(font); // Create Summary HSSFSheet summarySheet = workbook.createSheet("Summary"); int summaryRownum = 0; int summaryCellnum = 0; //Create a new row in current sheet Row summaryRow = summarySheet.createRow(summaryRownum++); // 0 Cell summaryCell = summaryRow.createCell(summaryCellnum++); summaryCell.setCellValue("Job"); summaryCell.setCellStyle(styleHeader); // 1 summaryCell = summaryRow.createCell(summaryCellnum++); summaryCell.setCellValue("Finished"); summaryCell.setCellStyle(styleHeader); // 2 summaryCell = summaryRow.createCell(summaryCellnum++); summaryCell.setCellValue("Errors"); summaryCell.setCellStyle(styleHeader); for (Job job : releaseService.getJobs()) { // Open Job JobService jobService = new JobService(siteService.getSiteName(), job.getJobName()); // Create Sheet HSSFSheet sheet = workbook.createSheet(job.getJobName()); int rownum = 0; int cellnum = 0; int errors = 0; //Create a new row in current sheet Row row = sheet.createRow(rownum++); // 0 Cell cell = row.createCell(cellnum++); cell.setCellValue("Host"); cell.setCellStyle(styleHeader); // 1 cell = row.createCell(cellnum++); cell.setCellValue("Service"); cell.setCellStyle(styleHeader); // 2 cell = row.createCell(cellnum++); cell.setCellValue("Command"); cell.setCellStyle(styleHeader); // 3 cell = row.createCell(cellnum++); cell.setCellValue("Executable"); cell.setCellStyle(styleHeader); // 4 cell = row.createCell(cellnum++); cell.setCellValue("Error"); cell.setCellStyle(styleHeader); // 5 cell = row.createCell(cellnum++); cell.setCellValue("Output"); cell.setCellStyle(styleHeader); // Check all hosts for (Host host : jobService.getHosts()) { // Check all services for (Service service : host.getServices()) { // Get a Commands for (Command command : service.getCommands()) { //Create a new row in current sheet row = sheet.createRow(rownum++); cellnum = 0; // 0 cell = row.createCell(cellnum++); cell.setCellValue(host.getHostName()); cell.setCellStyle(style); // 1 cell = row.createCell(cellnum++); cell.setCellValue(service.getServiceName()); cell.setCellStyle(style); // 2 cell = row.createCell(cellnum++); cell.setCellValue(command.getTitle()); cell.setCellStyle(style); // 3 cell = row.createCell(cellnum++); cell.setCellValue(command.getExec()); cell.setCellStyle(style); // 4 cell = row.createCell(cellnum++); cell.setCellValue("N"); cell.setCellStyle(style); // 5 cell = row.createCell(cellnum++); if (command.getOut().length() > 1024) { cell.setCellValue(command.getOut().substring(0, 1024) + "..."); } else { cell.setCellValue(command.getOut()); } cell.setCellStyle(style); // Error if (command.isError() == true) { row.getCell(0).setCellStyle(styleError); row.getCell(1).setCellStyle(styleError); row.getCell(2).setCellStyle(styleError); row.getCell(3).setCellStyle(styleError); row.getCell(4).setCellStyle(styleError); row.getCell(5).setCellStyle(styleError); row.getCell(4).setCellValue("Y"); errors++; } } } } // Set Size sheet.setColumnWidth(0, 6000); sheet.setColumnWidth(1, 4000); sheet.setColumnWidth(2, 8000); sheet.setColumnWidth(3, 14000); sheet.setColumnWidth(4, 3000); sheet.setColumnWidth(5, 20000); // Summary summaryRow = summarySheet.createRow(summaryRownum++); summaryCellnum = 0; // 0 summaryCell = summaryRow.createCell(summaryCellnum++); summaryCell.setCellValue(job.getJobName()); summaryCell.setCellStyle(style); // Set Link HSSFHyperlink link = creationHelper.createHyperlink(Hyperlink.LINK_DOCUMENT); link.setAddress("" + job.getJobName() + "!A1"); summaryCell.setHyperlink(link); summaryCell.setCellStyle(styleLink); // 1 summaryCell = summaryRow.createCell(summaryCellnum++); summaryCell.setCellValue(jobService.getJob().getFinished()); summaryCell.setCellStyle(style); // 2 summaryCell = summaryRow.createCell(summaryCellnum++); summaryCell.setCellValue(errors); summaryCell.setCellStyle(style); // If errors found if (errors > 0) { summaryRow.getCell(0).setCellStyle(styleError); summaryRow.getCell(1).setCellStyle(styleError); summaryRow.getCell(2).setCellStyle(styleError); } } // Set Summary Size summarySheet.setColumnWidth(0, 6000); summarySheet.setColumnWidth(1, 10000); summarySheet.setColumnWidth(2, 4000); // Save try { FileOutputStream out = new FileOutputStream(new File(getFileName())); workbook.write(out); out.close(); logger.log(Level.INFO, "{0} generated successfully..", getFileName()); return workbook; } catch (FileNotFoundException ex) { logger.log(Level.WARNING, "Audit: {0}", ex); } catch (IOException ex) { logger.log(Level.WARNING, "Audit: {0}", ex); } return null; }
From source file:org.deri.tarql.XLSToValues.java
License:Apache License
public TableData read() { try {//ww w . j av a2 s .c o m List<Binding> bindings = new ArrayList<Binding>(); // Read workbook into HSSFWorkbook HSSFWorkbook workbook = new HSSFWorkbook(this.is); HSSFSheet sheet = workbook.getSheetAt(this.sheet); this.evaluator = workbook.getCreationHelper().createFormulaEvaluator(); this.formatter = new DataFormatter(true); // To iterate over the rows Iterator<Row> rowIterator = sheet.iterator(); String[] row; try { if (varsFromHeader) { while (rowIterator.hasNext()) { row = getRow(rowIterator.next()); boolean foundValidColumnName = false; for (int i = 0; i < row.length; i++) { if (toVar(row[i]) == null) continue; foundValidColumnName = true; } // If row was empty or didn't contain anything usable // as column name, then try next row if (!foundValidColumnName) continue; for (int i = 0; i < row.length; i++) { Var var = toVar(row[i]); if (var == null || vars.contains(var) || var.equals(TarqlQuery.ROWNUM)) { getVar(i); } else { vars.add(var); } } break; } } rownum = 1; while (rowIterator.hasNext()) { row = getRow(rowIterator.next()); // Skip rows without data if (isEmpty(row)) continue; bindings.add(toBinding(row)); rownum++; } vars.add(TarqlQuery.ROWNUM); //Make sure variables exists for all columns even if no data is available, otherwise ARQ will complain. for (int i = 0; i < vars.size(); i++) { if (vars.get(i) == null) { getVar(i); } } return new TableData(vars, bindings); } finally { this.is.close(); } } catch (IOException ex) { throw new JenaException(ex); } }
From source file:org.eclipse.titanium.markers.export.XlsProblemExporter.java
License:Open Source License
/** * Export the code smells of a project to an excel workbook. * <p>/* w w w .j a v a 2 s . c om*/ * The first sheet of the workbook is a summary page, showing the number of * hits for each code smell, and an expressive bar chart of these data. The * further sheets enumerate the specific code smells of each kind, including * the message of the code smell, and the file name and line where it * occurred. * <p> * Note: All code smell types are used in the analysis and are written in * the output. Some code smells use external settings, which can be fine * tuned on the preference page. * * @param filename * the file to save the xls * @param date * the time stamp to write on the summary page * * @throws IOException * when writing the file fails */ @Override // Flow analysis thinks 'sheet' may be referenced as null, but it is // guaranteed to be initialized first. public void exportMarkers(final IProgressMonitor monitor, final String filename, final Date date) throws IOException { final SubMonitor progress = SubMonitor.convert(monitor, 100); final File file = new File(filename); POIFSFileSystem fs = null; HSSFWorkbook workbook = null; try { fs = new POIFSFileSystem(XlsProblemExporter.class.getResourceAsStream("ProblemMarkers.xlt")); workbook = new HSSFWorkbook(fs, true); } catch (IOException e) { ErrorReporter.logExceptionStackTrace("Error while exporting to excel", e); // Error on opening the template xls. Create an empty // one (without the chart). if (reportDebugInformation) { TITANDebugConsole.println("Error on opening ProblemMarkers.xlt. Chartless xls will be generated"); } workbook = new HSSFWorkbook(new FileInputStream(file)); workbook.createSheet("Summary"); workbook.setSheetOrder("Summary", 0); } catch (Exception e) { ErrorReporter.logExceptionStackTrace("Error while exporting to excel", e); return; } progress.worked(10); try { final HSSFSheet summarySheet = workbook.getSheetAt(0); createTimeSheet(workbook); final Map<String, Integer> smellCount = new HashMap<String, Integer>(); int summaryRow = 4; Cell label = null; Cell numberCell = null; final Map<TaskType, List<IMarker>> markers = collectMarkers(); // export the task markers: for (final TaskType t : TaskType.values()) { createTaskSheet(workbook, t, markers.get(t)); final Row row1 = summarySheet.createRow(summaryRow++); label = row1.createCell(0); label.setCellValue(t.getHumanReadableName()); final int nofMarkers = markers.get(t).size(); numberCell = row1.createCell(1); numberCell.setCellValue(nofMarkers); // row-1 is the number of found markers smellCount.put(t.name(), nofMarkers); } progress.worked(20); final MarkerHandler mh = AnalyzerCache.withAll().analyzeProject(progress.newChild(30), project); progress.setWorkRemaining(CodeSmellType.values().length + 1); // export the semantic problem markers: for (final CodeSmellType t : CodeSmellType.values()) { createCodeSmellSheet(workbook, mh, t); final Row row1 = summarySheet.createRow(summaryRow++); label = row1.createCell(0); label.setCellValue(t.getHumanReadableName()); smellCount.put(t.name(), mh.numberOfOccurrences(t)); numberCell = row1.createCell(1); numberCell.setCellValue(mh.numberOfOccurrences(t)); progress.worked(1); } final Row row0 = summarySheet.createRow(0); row0.createCell(0).setCellValue("Project: " + project.getName()); final Row row1 = summarySheet.createRow(1); row1.createCell(0).setCellValue("Code smell \\ date"); final CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat("yyyy.mm.dd")); label = row1.createCell(1); label.setCellValue(date); label.setCellStyle(cellStyle); final Row row2 = summarySheet.createRow(2); row2.createCell(0).setCellValue("Commulative Project Risk Factor"); final int riskFactor = new RiskFactorCalculator().measure(project, smellCount); row2.createCell(1).setCellValue(riskFactor); summarySheet.autoSizeColumn(0); summarySheet.autoSizeColumn(1); progress.worked(1); } catch (Exception e) { ErrorReporter.logExceptionStackTrace("Error while exporting to excel", e); } finally { FileOutputStream fileOutputStream = null; try { fileOutputStream = new FileOutputStream(file); workbook.write(fileOutputStream); } catch (Exception e) { ErrorReporter.logExceptionStackTrace("Error while closing the generated excel", e); } finally { IOUtils.closeQuietly(fileOutputStream); } } }
From source file:org.everit.jira.reporting.plugin.export.ExcelToCsvConverter.java
License:Apache License
public ExcelToCsvConverter(final HSSFWorkbook workbook) { this.workbook = workbook; evaluator = workbook.getCreationHelper().createFormulaEvaluator(); }
From source file:org.hil.children.service.impl.ChildrenManagerImpl.java
License:Open Source License
public String printListChildren(ChildrenSearchVO params) { List<ChildrenPrintVO> children = childrenDaoExt.searchChildrenForPrint(params); SimpleDateFormat format = new SimpleDateFormat("dd/MM/yyyy"); String strDOBFrom = format.format(params.getDateOfBirthFrom()); String strDOBTo = format.format(params.getDateOfBirthTo()); String path = ""; String prefixFileName = ""; Commune commune = communeDao.get(params.getCommuneId()); prefixFileName = commune.getDistrict().getProvince().getProvinceId() + commune.getDistrict().getDistrictId() + commune.getCommuneId();/*from w w w . j a v a2 s. c om*/ GraniteContext gc = GraniteContext.getCurrentInstance(); ServletContext sc = ((HttpGraniteContext) gc).getServletContext(); String reportDir = sc.getRealPath(config.getBaseReportDir()); long currentTime = System.currentTimeMillis(); String filePath = reportDir + "/" + prefixFileName + "_List_Children_Excel_" + currentTime; POIFSFileSystem fs; try { filePath += ".xls"; fs = new POIFSFileSystem(new FileInputStream(reportDir + "/excel/ListOfChildrenInCommune.xls")); HSSFWorkbook wb = new HSSFWorkbook(fs, true); HSSFSheet s = wb.getSheetAt(0); HSSFRow r = null; HSSFCell c = null; r = s.getRow(0); c = r.getCell(1); c.setCellValue(commune.getCommuneName()); c = r.getCell(2); c.setCellValue("(" + strDOBFrom + " - " + strDOBTo + ")"); HSSFCellStyle cs = wb.createCellStyle(); cs.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs.setBorderTop(HSSFCellStyle.BORDER_THIN); cs.setBorderLeft(HSSFCellStyle.BORDER_THIN); cs.setBorderRight(HSSFCellStyle.BORDER_THIN); HSSFCellStyle cs1 = wb.createCellStyle(); cs1.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs1.setBorderTop(HSSFCellStyle.BORDER_THIN); cs1.setBorderLeft(HSSFCellStyle.BORDER_THIN); cs1.setBorderRight(HSSFCellStyle.BORDER_THIN); cs1.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCellStyle cs2 = wb.createCellStyle(); cs2.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs2.setBorderTop(HSSFCellStyle.BORDER_THIN); cs2.setBorderLeft(HSSFCellStyle.BORDER_THIN); cs2.setBorderRight(HSSFCellStyle.BORDER_THIN); CreationHelper createHelper = wb.getCreationHelper(); cs2.setDataFormat(createHelper.createDataFormat().getFormat("dd/MM/yyyy")); int rownum = 3; for (rownum = 3; rownum < children.size() + 3; rownum++) { r = s.createRow(rownum); c = r.createCell(0); c.setCellStyle(cs1); c.setCellValue(rownum - 2); c = r.createCell(1); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).getFullName()); c = r.createCell(2); c.setCellStyle(cs2); c.setCellValue(children.get(rownum - 3).getDateOfBirth()); c = r.createCell(3); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).isGender() == true ? "N" : "Nam"); c = r.createCell(4); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).getVillageName()); c = r.createCell(5); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).getMotherName()); c = r.createCell(6); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).getMotherBirthYear() != null ? children.get(rownum - 3).getMotherBirthYear() : 0); c = r.createCell(7); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).getMotherMobile()); c = r.createCell(8); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).getFatherName()); c = r.createCell(9); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).getFatherBirthYear() != null ? children.get(rownum - 3).getFatherBirthYear() : 0); c = r.createCell(10); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).getFatherMobile()); c = r.createCell(11); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).getCaretakerName()); c = r.createCell(12); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).getCaretakerMobile()); c = r.createCell(13); c.setCellStyle(cs2); c.setCellValue(children.get(rownum - 3).getVGB()); c = r.createCell(14); c.setCellStyle(cs2); c.setCellValue(children.get(rownum - 3).getBCG()); c = r.createCell(15); c.setCellStyle(cs2); c.setCellValue(children.get(rownum - 3).getDPT_VGB_Hib1()); c = r.createCell(16); c.setCellStyle(cs2); c.setCellValue(children.get(rownum - 3).getDPT_VGB_Hib2()); c = r.createCell(17); c.setCellStyle(cs2); c.setCellValue(children.get(rownum - 3).getDPT_VGB_Hib3()); c = r.createCell(18); c.setCellStyle(cs2); c.setCellValue(children.get(rownum - 3).getOPV1()); c = r.createCell(19); c.setCellStyle(cs2); c.setCellValue(children.get(rownum - 3).getOPV2()); c = r.createCell(20); c.setCellStyle(cs2); c.setCellValue(children.get(rownum - 3).getOPV3()); c = r.createCell(21); c.setCellStyle(cs2); c.setCellValue(children.get(rownum - 3).getMeasles1()); } FileOutputStream fileOut = new FileOutputStream(filePath); wb.write(fileOut); fileOut.close(); path = "/reports/" + prefixFileName + "_List_Children_Excel_" + currentTime + ".xls"; } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return path; }
From source file:org.hil.children.service.impl.ChildrenManagerImpl.java
License:Open Source License
public String printListVaccinatedInLocationReport(String type, String timeFrom, String timeTo, Commune commune, District district, Vaccination vaccine, List<ChildrenVaccinatedInLocationVO> statistics) { SimpleDateFormat format = new SimpleDateFormat("dd/MM/yyyy"); String path = ""; String prefixFileName = ""; if (commune != null) prefixFileName = district.getDistrictId() + "_" + commune.getCommuneId(); else if (district != null) prefixFileName = district.getDistrictId(); GraniteContext gc = GraniteContext.getCurrentInstance(); ServletContext sc = ((HttpGraniteContext) gc).getServletContext(); String reportDir = sc.getRealPath(config.getBaseReportDir()); long currentTime = System.currentTimeMillis(); String filePath = reportDir + "/" + prefixFileName + "_DanhSachTreDenTiem_" + vaccine.getName() + "_" + currentTime;/*from www.ja va 2 s . co m*/ POIFSFileSystem fs; try { filePath += ".xls"; fs = new POIFSFileSystem( new FileInputStream(reportDir + "/excel/ListOfChildrenVaccinatedInLocation.xls")); HSSFWorkbook wb = new HSSFWorkbook(fs, true); HSSFSheet s = wb.getSheetAt(0); HSSFRow r = null; HSSFCell c = null; r = s.getRow(0); c = r.getCell(0); c.setCellValue("Danh sch tr n tim chng " + vaccine.getName() + " (bao gm c tr tim bnh vin/phng khm)"); r = s.getRow(1); c = r.getCell(1); c.setCellValue(district.getDistrictName()); if (commune != null) { c = r.getCell(2); c.setCellValue("X"); c = r.getCell(3); c.setCellValue(commune.getCommuneName()); c = r.getCell(4); c.setCellValue("(" + timeFrom + " - " + timeTo + ")"); } else { c = r.getCell(2); c.setCellValue("(" + timeFrom + " - " + timeTo + ")"); } HSSFCellStyle cs = wb.createCellStyle(); cs.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs.setBorderTop(HSSFCellStyle.BORDER_THIN); cs.setBorderLeft(HSSFCellStyle.BORDER_THIN); cs.setBorderRight(HSSFCellStyle.BORDER_THIN); HSSFCellStyle cs1 = wb.createCellStyle(); cs1.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs1.setBorderTop(HSSFCellStyle.BORDER_THIN); cs1.setBorderLeft(HSSFCellStyle.BORDER_THIN); cs1.setBorderRight(HSSFCellStyle.BORDER_THIN); cs1.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCellStyle cs2 = wb.createCellStyle(); cs2.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs2.setBorderTop(HSSFCellStyle.BORDER_THIN); cs2.setBorderLeft(HSSFCellStyle.BORDER_THIN); cs2.setBorderRight(HSSFCellStyle.BORDER_THIN); CreationHelper createHelper = wb.getCreationHelper(); cs2.setDataFormat(createHelper.createDataFormat().getFormat("dd/MM/yyyy")); int rownum = 3; for (rownum = 3; rownum < statistics.size() + 3; rownum++) { r = s.createRow(rownum); c = r.createCell(0); c.setCellStyle(cs1); c.setCellValue(rownum - 2); c = r.createCell(1); c.setCellStyle(cs); c.setCellValue(statistics.get(rownum - 3).getCommuneName()); c = r.createCell(2); c.setCellStyle(cs); c.setCellValue(statistics.get(rownum - 3).getVillageName()); c = r.createCell(3); c.setCellStyle(cs); c.setCellValue(statistics.get(rownum - 3).getChildCode()); c = r.createCell(4); c.setCellStyle(cs); c.setCellValue(statistics.get(rownum - 3).getFullName()); c = r.createCell(5); c.setCellStyle(cs1); c.setCellValue(statistics.get(rownum - 3).getGender() == true ? "N" : "Nam"); c = r.createCell(6); c.setCellStyle(cs2); c.setCellValue(statistics.get(rownum - 3).getDateOfBirth()); c = r.createCell(7); c.setCellStyle(cs); c.setCellValue(statistics.get(rownum - 3).getMotherName()); c = r.createCell(8); c.setCellStyle(cs2); c.setCellValue(statistics.get(rownum - 3).getDateOfImmunization()); c = r.createCell(9); c.setCellStyle(cs); String vaccinatedLocation = ""; if (statistics.get(rownum - 3).getOtherLocation() != null && statistics.get(rownum - 3).getOtherLocation() >= 1 && statistics.get(rownum - 3).getOtherLocation() <= 4) { if (statistics.get(rownum - 3).getOtherLocation() == 1) vaccinatedLocation = "Bnh vin TW"; else if (statistics.get(rownum - 3).getOtherLocation() == 2) vaccinatedLocation = "Bnh vin tnh"; else if (statistics.get(rownum - 3).getOtherLocation() == 3) vaccinatedLocation = "Bnh vin huyn"; else if (statistics.get(rownum - 3).getOtherLocation() == 4) vaccinatedLocation = "Phng khm/Bnh vin t nhn"; } else vaccinatedLocation = statistics.get(rownum - 3).getVaccinatedCommune(); c.setCellValue(vaccinatedLocation); } FileOutputStream fileOut = new FileOutputStream(filePath); wb.write(fileOut); fileOut.close(); path = "/reports/" + prefixFileName + "_DanhSachTreDenTiem_" + vaccine.getName() + "_" + currentTime + ".xls"; } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return path; }
From source file:org.isource.util.CSVUtils.java
private static HSSFWorkbook evaluateFormulas(HSSFWorkbook wb) { FormulaEvaluator evaluator = null;/* w ww .j a v a 2s .c o m*/ evaluator = wb.getCreationHelper().createFormulaEvaluator(); for (int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) { Sheet sheet = wb.getSheetAt(sheetNum); for (Row r : sheet) { for (Cell c : r) { if (c.getCellType() == Cell.CELL_TYPE_FORMULA) { evaluator.evaluateFormulaCell(c); if (sheetNum == 0 && c.getColumnIndex() == r.getPhysicalNumberOfCells() - 1) { switch (c.getCachedFormulaResultType()) { case Cell.CELL_TYPE_NUMERIC: break; case Cell.CELL_TYPE_STRING: break; } } } } } } return wb; }