List of usage examples for org.apache.poi.ss.usermodel Sheet setAutoFilter
AutoFilter setAutoFilter(CellRangeAddress range);
From source file:com.adobe.acs.commons.mcp.impl.ProcessErrorReportExcelServlet.java
License:Apache License
@SuppressWarnings("squid:S3776") protected Workbook createSpreadsheet(ManagedProcess report) { Workbook wb = new XSSFWorkbook(); String name = report.getName(); for (char ch : new char[] { '\\', '/', '*', '[', ']', ':', '?' }) { name = StringUtils.remove(name, ch); }// w w w . j a v a2s . c o m Sheet sheet = wb.createSheet(name); sheet.createFreezePane(0, 1, 0, 1); Row headerRow = sheet.createRow(0); CellStyle headerStyle = createHeaderStyle(wb); CellStyle dateStyle = wb.createCellStyle(); CreationHelper createHelper = wb.getCreationHelper(); dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyy/mm/dd h:mm:ss")); for (String columnName : Arrays.asList("Time", "Path", "Error", "Stack trace")) { Cell headerCell = headerRow.createCell(headerRow.getPhysicalNumberOfCells()); headerCell.setCellValue(columnName); headerCell.setCellStyle(headerStyle); } Collection<ArchivedProcessFailure> rows = report.getReportedErrorsList(); //make rows, don't forget the header row for (ArchivedProcessFailure error : rows) { Row row = sheet.createRow(sheet.getPhysicalNumberOfRows()); Cell c; c = row.createCell(0); c.setCellValue(error.time); c.setCellStyle(dateStyle); c = row.createCell(1); c.setCellValue(error.nodePath); c = row.createCell(2); c.setCellValue(error.error); c = row.createCell(3); c.setCellValue(error.stackTrace); } autosize(sheet, 4); sheet.setAutoFilter(new CellRangeAddress(0, 1 + rows.size(), 0, 3)); return wb; }
From source file:com.github.igor_kudryashov.utils.excel.ExcelWriter.java
License:Apache License
/** * Format a table of worksheet/*from w w w. j ava 2s .c o m*/ * * @param sheet * Name of sheet * @param withHeader * <code>true</code> for create auto filter and freeze pane in * first row, otherwise <code>false</code> */ public void setAutoSizeColumns(Sheet sheet, boolean withHeader) { if (sheet.getLastRowNum() > 0) { if (withHeader) { int x = sheet.getRow(sheet.getLastRowNum()).getLastCellNum(); CellRangeAddress range = new CellRangeAddress(0, 0, 0, x - 1); sheet.setAutoFilter(range); sheet.createFreezePane(0, 1); } // auto-sizing columns if (columnWidth.containsKey(sheet.getSheetName())) { Map<Integer, Integer> width = columnWidth.get(sheet.getSheetName()); for (Map.Entry<Integer, Integer> entry : width.entrySet()) { sheet.setColumnWidth(entry.getKey(), entry.getValue()); } } } }
From source file:com.ipcglobal.fredimport.xls.DistinctCategoriesSpreadsheet.java
License:Apache License
/** * Creates the sheet./*from w w w .j a v a 2 s. co m*/ * * @param distinctCategoryItems the distinct category items * @throws Exception the exception */ public void createSheet(Collection<DistinctCategoryItem> distinctCategoryItems) throws Exception { List<XlsDefItem> xlsDefItems = initHhdrWidthItems(); String sheetName = "DistinctCategoryItems"; Sheet sheet = wb.createSheet(sheetName); processColumnWidths(sheet, xlsDefItems); sheet.createFreezePane(0, 1, 0, 1); // freeze top row sheet.setAutoFilter(CellRangeAddress.valueOf("A1:AB1")); // hack - i know the number of columns sheet.getPrintSetup().setLandscape(true); sheet.setAutobreaks(true); sheet.getPrintSetup().setFitWidth((short) 1); sheet.getPrintSetup().setFitHeight((short) 1); int rowCnt = 0; // Header int colCnt = 0; Row rowHdr = sheet.createRow(rowCnt); for (XlsDefItem xlsDefItem : xlsDefItems) { Cell cellHdr = rowHdr.createCell(colCnt, Cell.CELL_TYPE_STRING); CellStyle style = findCellStyle("Arial", HSSFColor.WHITE.index, (short) 11, XSSFFont.BOLDWEIGHT_BOLD, cellStyleFromHdrAlign(HdrAlign.Left), XSSFCellStyle.VERTICAL_TOP, HSSFColor.LIGHT_BLUE.index, CellBorder.All_Thin, formatGeneral); style.setWrapText(true); cellHdr.setCellStyle(style); cellHdr.setCellValue(xlsDefItem.getName()); colCnt++; } rowCnt++; // Data for (DistinctCategoryItem distinctCategoryItem : distinctCategoryItems) { Row rowData = sheet.createRow(rowCnt); int colNum = 0; for (XlsDefItem xlsDefItem : xlsDefItems) populateCell(rowData, colNum++, xlsDefItem.getDataType(), getByNameAsString(distinctCategoryItem, xlsDefItem.getName())); rowCnt++; } }
From source file:das.pf.io.IOExcel.java
License:Open Source License
public boolean processFile(Path input, boolean openFile) { boolean result = false; int endRow = 0; try {//from w w w . j a va 2 s . c o m updateMessages(String.format("Inicializando el documento: %s", input.toString())); Path copy = createCopy(input); if (copy != null && Files.exists(copy, LinkOption.NOFOLLOW_LINKS)) { Workbook workbook = WorkbookFactory.create(copy.toFile()); Sheet sheet = workbook.getSheetAt(0); Sheet newSheet = workbook.createSheet("Procesado"); workbook.setSheetName(0, "Crudo"); endRow = getLasRow(sheet); // seccion para la creacion de los encabezados updateMessages("Creando la cabecera de los datos"); createHeaderData(newSheet, getCellStyleHeaderData(workbook)); // seccion para los values USD updateMessages( String.format("Creando la cabecera para los: 'values USD' %s", TypeUnits.MTH.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.VALUES, TypeUnits.MTH), 11, 35, 14); updateMessages( String.format("Creando la cabecera para los: 'values USD' %s", TypeUnits.QRT.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.VALUES, TypeUnits.QRT), 35, 49, 38); updateMessages( String.format("Creando la cabecera para los: 'values USD' %s", TypeUnits.YTD.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.VALUES, TypeUnits.YTD), 49, 54, 52); updateMessages( String.format("Creando la cabecera para los: 'values USD' %s", TypeUnits.MAT.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.VALUES, TypeUnits.MAT), 54, 59, 57); // seccion para los values units updateMessages( String.format("Creando la cabecera para los: 'values Units' %s", TypeUnits.MTH.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.UNITS, TypeUnits.MTH), 59, 83, 63); updateMessages( String.format("Creando la cabecera para los: 'values Units' %s", TypeUnits.QRT.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.UNITS, TypeUnits.QRT), 83, 97, 87); updateMessages( String.format("Creando la cabecera para los: 'values Units' %s", TypeUnits.YTD.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.UNITS, TypeUnits.YTD), 97, 102, 101); updateMessages( String.format("Creando la cabecera para los: 'values Units' %s", TypeUnits.MAT.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.UNITS, TypeUnits.MAT), 102, 107, 106); // // // seccion para los values units standars updateMessages(String.format("Creando la cabecera para los: 'values Standard Units' %s", TypeUnits.MTH.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.U_E, TypeUnits.MTH), 107, 131, 112); updateMessages(String.format("Creando la cabecera para los: 'values Standard Units' %s", TypeUnits.QRT.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.U_E, TypeUnits.QRT), 131, 145, 136); updateMessages(String.format("Creando la cabecera para los: 'values Standard Units' %s", TypeUnits.YTD.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.U_E, TypeUnits.YTD), 145, 150, 150); updateMessages(String.format("Creando la cabecera para los: 'values Standard Units' %s", TypeUnits.MAT.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.U_E, TypeUnits.MAT), 150, 155, 155); // fin de la seccion para la creacion de los encabezados // seccion para escribir los CT updateMessages("Escribiendo las clases terampeuticas..."); writeCT(newSheet, sheet, 13, endRow); // seccion para escribir los productos updateMessages("Escribiendo los productos..."); writeProducts(newSheet, sheet, 14); // seccion para escribir los otros valores updateMessages("Escribiendo datos en general..."); writerOthersValues(newSheet, sheet, 15); // seccion para escribir los key competitors updateMessages("Escribiendo los Key Competitors..."); writeKeyCompetitors(newSheet, 3, endRow, 9, 5); // seccion para escribir el pais XmlContry contry = writeContries(newSheet, 3, 0, input); // seccion para escribir la region writeRegions(contry, newSheet, 3, 1); for (int i = 0; i < 155; i++) newSheet.autoSizeColumn(i); newSheet.setAutoFilter(CellRangeAddress.valueOf(String.format("A3:K%d", newSheet.getLastRowNum()))); String pathOutput = "DAS PF - " + input.getFileName().toString(); try (FileOutputStream fos = new FileOutputStream( Paths.get(this.out.toString(), pathOutput).toFile())) { updateMessages(String.format("Guadando el trabajo en la ruta: '%s'", Paths.get(this.out.toString(), pathOutput))); workbook.write(fos); } catch (IOException ex) { Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, null, ex); } finally { workbook.close(); } if (openFile && Desktop.isDesktopSupported() && Desktop.getDesktop().isSupported(Desktop.Action.OPEN)) Desktop.getDesktop().open(Paths.get(this.out.toString(), pathOutput).toFile()); result = true; newSheet = null; sheet = null; workbook = null; Files.delete(copy); } } catch (IOException | InvalidFormatException ex) { Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, null, ex); Util.showException("No se pudo guardar el archivo", ex); } return result; }
From source file:das.pf.io.IOExcel.java
License:Open Source License
public boolean consolidateFiles() { boolean result = false; AtomicInteger rowIndex = new AtomicInteger(3); String outputName = "DAS - " + this.out.getFileName().toString() + " consolidate.xlsx"; Workbook consolidateWb = new XSSFWorkbook(); try {/*from w w w. j ava2 s.co m*/ Sheet sheetConsolidate = consolidateWb.createSheet("Consolidado"); Files.list(this.out).filter((p) -> { String name = p.toString(); return (name.endsWith(".xlsx") || name.endsWith(".xls")) && !p.getFileName().toString().equals(outputName); }).sorted((p1, p2) -> { String acronym = getAcromynName(p1); String acronym2 = getAcromynName(p2); return acronym.compareToIgnoreCase(acronym2); }).forEach(p -> { try { Workbook wb = WorkbookFactory.create(p.toFile()); Sheet sheet = wb.getSheet("Procesado"); updateMessages(String.format("Copiando los datos del archivo: %s dentro del archivo: %s", p.toString(), outputName)); for (int index = 3; index < sheet.getLastRowNum(); index++) { Row row = sheet.getRow(index); Row r = sheetConsolidate.createRow(rowIndex.getAndIncrement()); row.forEach(c -> { if (c != null && c.getCellType() != Cell.CELL_TYPE_BLANK) { final Cell cell = r.createCell(c.getColumnIndex(), c.getCellType()); updateMessages( String.format("Copiando los datos de la fila: #%d", c.getRowIndex())); switch (c.getCellType()) { case Cell.CELL_TYPE_NUMERIC: cell.setCellValue(c.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: cell.setCellValue(c.getRichStringCellValue()); break; } } }); row = null; } sheet = null; wb.close(); wb = null; } catch (IOException | InvalidFormatException | EncryptedDocumentException ex) { Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, null, ex); } }); Path path = Files.list(this.out).filter((p) -> { String name = p.toString(); return (name.endsWith(".xlsx") || name.endsWith(".xls")) && !p.getFileName().toString().equals(outputName); }).findFirst().get(); createHeadersConsolidateFile(consolidateWb, path); for (int i = 0; i < 155; i++) sheetConsolidate.autoSizeColumn(i); sheetConsolidate.setAutoFilter( CellRangeAddress.valueOf(String.format("A3:K%d", sheetConsolidate.getLastRowNum()))); try (FileOutputStream fos = new FileOutputStream(Paths.get(out.toString(), outputName).toFile())) { updateMessages(String.format("Guadando el trabajo en la ruta: '%s'", Paths.get(this.out.toString(), outputName))); consolidateWb.write(fos); result = true; } catch (IOException ex) { Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, "Ocurrio un error al intenatr guardar el archivo consolidado", ex); } finally { consolidateWb.close(); } } catch (IOException ex) { Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, null, ex); } consolidateWb = null; return result; }
From source file:nl.meine.scouting.solparser.writer.ExcelWriter.java
License:Open Source License
private void createHeading(Sheet sheet) { Row r = sheet.createRow(0);/*from w w w .j a v a 2s . c o m*/ r.createCell(0).setCellValue("Lidnummer"); r.createCell(1).setCellValue("Achternaam"); r.createCell(2).setCellValue("Tussenvoegsel"); r.createCell(3).setCellValue("Voornaam"); r.createCell(4).setCellValue("Initialen"); r.createCell(5).setCellValue("Geslacht"); r.createCell(6).setCellValue("Straat"); r.createCell(7).setCellValue("Adres"); r.createCell(8).setCellValue("Postcode"); r.createCell(9).setCellValue("Plaats"); r.createCell(10).setCellValue("Telefoonnummer"); r.createCell(11).setCellValue("Mobiel"); r.createCell(12).setCellValue("Mail lid"); r.createCell(13).setCellValue("Naam ouder/verzorger 1"); r.createCell(14).setCellValue("Mail ouder/verzorger 1"); r.createCell(15).setCellValue("Telefoonnummer ouder/verzorger 1"); r.createCell(16).setCellValue("Naam ouder/verzorger 2"); r.createCell(17).setCellValue("Mail ouder/verzorger 2"); r.createCell(18).setCellValue("Telefoonnummer ouder/verzorger 2"); r.createCell(19).setCellValue("Speltak"); r.createCell(20).setCellValue("Functie"); r.createCell(21).setCellValue("Geboortedatum"); r.createCell(22).setCellValue("Functie startdatum"); r.createCell(23).setCellValue("Overige informatie"); Iterator<Cell> it = r.cellIterator(); while (it.hasNext()) { Cell c = it.next(); c.setCellStyle(headingStyle); } sheet.setAutoFilter(new CellRangeAddress(0, 0, 0, 23)); }
From source file:nl.meine.scouting.solparser.writer.ExcelWriter.java
License:Open Source License
private void processQuitters() { if (hasPrevious()) { Sheet sheet = workbook.getSheet(SorterFactory.GROUP_NAME_ALL); List<Row> quitters = new ArrayList<Row>(); FileInputStream previousStream = null; try {//from w w w . j a v a2s .com previousStream = new FileInputStream(previous); //Get the workbook instance for XLS file HSSFWorkbook prevWorkbook = new HSSFWorkbook(previousStream); Sheet prevSheet = prevWorkbook.getSheet(SorterFactory.GROUP_NAME_ALL); if (prevSheet == null) { return; } // Check of er mensen vertrokken zijn for (Row row : prevSheet) { if (row.getRowNum() > 0) { String lidnummer = row.getCell(NUM_LIDNUMMER_CELL).getStringCellValue(); Row currentRow = getLidFromSheet(lidnummer, sheet); if (currentRow == null) { quitters.add(row); } } } } catch (IOException ex) { System.out.println("Error Reading the previous file: " + ex.getLocalizedMessage()); return; } finally { try { if (previousStream != null) { previousStream.close(); } } catch (IOException ex) { System.out.println("Problems closing file: " + ex.getLocalizedMessage()); } } if (quitters.isEmpty()) { return; } Sheet removedSheet = workbook.createSheet(SHEET_REMOVED_PERSONS); // Create header Row header = removedSheet.createRow(0); //Lidnummer Achternaam Tussenvoegsel Voornaam Geslacht Telefoonnummer Mobiel Geboortedatum Cell lidnummer = header.createCell(0); lidnummer.setCellValue("Lidnummer"); lidnummer.setCellStyle(headingStyle); Cell achternaam = header.createCell(1); achternaam.setCellValue("Achternaam"); achternaam.setCellStyle(headingStyle); Cell tussenvoegsel = header.createCell(2); tussenvoegsel.setCellValue("Tussenvoegsel"); tussenvoegsel.setCellStyle(headingStyle); Cell voornaam = header.createCell(3); voornaam.setCellValue("Voornaam"); voornaam.setCellStyle(headingStyle); Cell geslacht = header.createCell(4); geslacht.setCellValue("Geslacht"); geslacht.setCellStyle(headingStyle); Cell telefoonnummer = header.createCell(5); telefoonnummer.setCellValue("Telefoonnummer"); telefoonnummer.setCellStyle(headingStyle); Cell geboortedatum = header.createCell(6); geboortedatum.setCellValue("Geboortedatum"); geboortedatum.setCellStyle(headingStyle); // Iterate over quitters int index = 1; for (Row quitter : quitters) { Row r = removedSheet.createRow(index); r.createCell(0).setCellValue(quitter.getCell(0).getStringCellValue()); r.createCell(1).setCellValue(quitter.getCell(1).getStringCellValue()); r.createCell(2).setCellValue(quitter.getCell(2).getStringCellValue()); r.createCell(3).setCellValue(quitter.getCell(3).getStringCellValue()); r.createCell(4).setCellValue(quitter.getCell(5).getStringCellValue()); r.createCell(5).setCellValue(quitter.getCell(10).getStringCellValue()); r.createCell(6).setCellValue(quitter.getCell(21).getStringCellValue()); index++; } removedSheet.setAutoFilter(new CellRangeAddress(0, 0, 0, 6)); int numcells = removedSheet.getRow(0).getLastCellNum(); for (int i = 0; i < numcells; i++) { removedSheet.autoSizeColumn(i); } } }
From source file:org.cgiar.ccafs.ap.summaries.projects.xlsx.BaseXLS.java
License:Open Source License
/** * Method used for to initialize an Excel Workbook object. * It creates a Workbook object using a predefined template. * //from www . j av a 2s . com * @param excelFormat is the format that you want to create (i.e. 'xls' or 'xlsx'). * @param useTemplate is true if you want to use a templa, false if you want to create the Workbook empty. * @return a Workbook Object representing the Workbook instance where is going to be written all the information in * XLS format. * @throws IOException */ public void initializeSheet(Sheet sheet, int[] columnTypes) throws IOException { // initializing values rowStart = 12; columnStart = 1; rowCounter = rowStart; columnCounter = columnStart; // Initializing styles depending on the cell type. this.initializeStyles(columnTypes); // applying header. this.addHeader(sheet); StringBuilder rangeString = new StringBuilder(); char initialColumn = 'B'; char endColumn = (char) (initialColumn + (columnTypes.length - 1)); // Set filter in cell rangeString = new StringBuilder(); rangeString.append(initialColumn); rangeString.append("12:"); rangeString.append(endColumn); rangeString.append("12"); sheet.setAutoFilter(CellRangeAddress.valueOf(rangeString.toString())); }
From source file:ru.wmbdiff.ExportIntoExcel.java
License:Apache License
public void export(File file, WMBDiffNoRootTreeTableModel model) { logger.info("export begin"); Workbook workbook = new HSSFWorkbook(); Sheet sheet = workbook.createSheet("WMBDiff"); int rowNum = 0; //Create Header CellStyle style;/* w w w.j a va2 s . co m*/ Font headerFont = workbook.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setColor(IndexedColors.WHITE.getIndex()); headerFont.setFontHeightInPoints((short) 10); style = workbook.createCellStyle(); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.WHITE.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.WHITE.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.WHITE.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.WHITE.getIndex()); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFillForegroundColor(IndexedColors.AQUA.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(headerFont); Row row = sheet.createRow(rowNum++); Cell cell; cell = row.createCell(0); cell.setCellValue("Result"); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue("Broker"); cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue("Execution Group"); cell.setCellStyle(style); cell = row.createCell(3); cell.setCellValue("Name"); cell.setCellStyle(style); cell = row.createCell(4); cell.setCellValue("Type"); cell.setCellStyle(style); cell = row.createCell(5); cell.setCellValue("Last Modification"); cell.setCellStyle(style); cell = row.createCell(6); cell.setCellValue("Deployment Date"); cell.setCellStyle(style); cell = row.createCell(7); cell.setCellValue("Bar File"); cell.setCellStyle(style); cell = row.createCell(8); cell.setCellValue("Result Description"); cell.setCellStyle(style); sheet.createFreezePane(0, 1); List<DiffExecutionGroup> dEG = model.getDiffExecutionGroupList(); ListIterator<DiffExecutionGroup> litr = dEG.listIterator(); while (litr.hasNext()) { DiffExecutionGroup element = litr.next(); element.getDiffResultList(); ListIterator<DiffDeployedObjectResult> litr2 = element.getDiffResultList().listIterator(); while (litr2.hasNext()) { DiffDeployedObjectResult res = litr2.next(); switch (res.getResult()) { case ONLY_IN_A: createRow(rowNum++, sheet, res.getAObject(), "A", res.getResultDesc()); break; case ONLY_IN_B: createRow(rowNum++, sheet, res.getBObject(), "B", res.getResultDesc()); break; case EQUAL: createRow(rowNum++, sheet, res.getAObject(), "=", res.getResultDesc()); createRow(rowNum++, sheet, res.getBObject(), "=", res.getResultDesc()); sheet.groupRow(rowNum - 2, rowNum - 2); break; case DIFF: createRow(rowNum++, sheet, res.getAObject(), "!=", res.getResultDesc()); createRow(rowNum++, sheet, res.getBObject(), "!=", res.getResultDesc()); sheet.groupRow(rowNum - 2, rowNum - 2); break; } ; } ; } ; //Adjust column width to fit the contents for (int i = 0; i < 9; i++) sheet.autoSizeColumn(i); //set Filter sheet.setAutoFilter(new CellRangeAddress(0, rowNum - 1, 0, 8)); try { FileOutputStream out = new FileOutputStream(file); workbook.write(out); workbook.close(); out.close(); } catch (Exception e) { logger.error("export", e); } logger.info("export end"); }