List of usage examples for org.apache.poi.ss.usermodel Sheet getRow
Row getRow(int rownum);
From source file:business.SongExcelParser.java
private void songsToWorkbook(Workbook wb, ArrayList<SongContainer> songContainerList) { Row row;/*from w w w.j a va2 s . c o m*/ int rowNumber = 1; Sheet sheet = wb.createSheet(); row = setRowHeader(sheet); for (SongContainer container : songContainerList) { for (Song song : container.getSongs()) { row = sheet.createRow(rowNumber); setRowInfo(row, song, container); rowNumber++; } } for (int x = 0; x < sheet.getRow(0).getPhysicalNumberOfCells(); x++) { sheet.autoSizeColumn(x); } }
From source file:cartel.DynamicDegreeCalculation.java
License:Open Source License
public void compute(Graph graph) throws FileNotFoundException, IOException { Map<String, Integer> weightedDegreeForOneCountryfForOneYear; Workbook wb = new XSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); Sheet sheet = wb.createSheet("weighted degree"); Row row;//from ww w.ja v a2 s . c om Cell cell; Map<String, Integer> countryIndices = new TreeMap(); int index = 0; for (String country : CartelDynamic.europeanCountries) { index++; countryIndices.put(country, index); } //COLUMNS HEADER row = sheet.createRow((short) 0); index = 1; for (int i = 1948; i < 2009; i++) { cell = row.createCell(index); index++; cell.setCellValue(String.valueOf(i)); } //CREATING EMPTY CELLS FOR EACH ROW for (String country : countryIndices.keySet()) { row = sheet.createRow((countryIndices.get(country))); index = 0; for (int i = 1948; i <= 2009; i++) { row.createCell(index); index++; } } //FILLING FIRST COLUMN WITH COUNTRIES for (String country : countryIndices.keySet()) { row = sheet.getRow(countryIndices.get(country)); row.getCell(0).setCellValue(country); } int indexYear = 1; for (int i = 1948; i < 2009; i++) { weightedDegreeForOneCountryfForOneYear = new TreeMap(); for (Node node : graph.getNodes()) { String nodeLabel = node.getLabel(); int sumDegrees = 0; for (Edge edge : graph.getAllEdges()) { if (!edge.getSource().getLabel().equals(nodeLabel) & !edge.getTarget().getLabel().equals(nodeLabel)) { continue; } if (edge.getSource().getLabel().equals(edge.getTarget().getLabel())) { continue; } AttributeValueList attributeValueList = edge.getAttributeValues(); for (AttributeValue attributeValue : attributeValueList) { if (!attributeValue.getAttribute().getTitle().equals("freq")) { continue; } if (((Integer) attributeValue.getStartValue()) != i) { continue; } sumDegrees = sumDegrees + Integer.parseInt(attributeValue.getValue()); } } sumDegrees = sumDegrees / 2; row = sheet.getRow(countryIndices.get(nodeLabel)); cell = row.getCell(indexYear); cell.setCellValue(String.valueOf(sumDegrees)); } indexYear++; } String pathFile = "D:/workbook weighted degree.xlsx"; FileOutputStream fileOut = new FileOutputStream(pathFile); wb.write(fileOut); fileOut.close(); }
From source file:Categorization.CategoriesLoader.java
License:Open Source License
public static void echoAsCSV(Sheet sheet) throws IOException { Row row;/*from w w w . jav a 2s . c o m*/ int startingRow = 2; Category category; boolean breakNow = false; for (int i = startingRow; i <= sheet.getLastRowNum(); i++) { if (breakNow) { break; } row = sheet.getRow(i); if (row == null) { break; } category = new Category(); for (int j = 1; j < row.getLastCellNum(); j++) { //label of the category if (j == 1) { if (row.getCell(j).getStringCellValue().isEmpty() || row.getCell(j).getStringCellValue() == null) { breakNow = true; break; } category.setCategoryName("CAT_" + row.getCell(j).getStringCellValue()); } //if a cell is null, the row is empty. if (row.getCell(j) == null) { continue; } //check the keywords if (j > 1 & j <= maxColKeyWords) { System.out.println(row.getCell(j).getStringCellValue()); if (row.getCell(j).getStringCellValue().startsWith("NOT ")) { category.addExclusionKeyword( row.getCell(j).getStringCellValue().toLowerCase().substring(4).trim()); } else { category.addKeyword(row.getCell(j).getStringCellValue().toLowerCase().trim()); } } //check the min number of keywords that should be present in the text to match a classification if (j == maxColKeyWords + 1) { System.out.println("min Words: " + row.getCell(j).getStringCellValue()); category.setMinNumberKeywords(Integer.parseInt(row.getCell(j).getStringCellValue())); } //check if single terms can lead to a direct classification if (j == maxColKeyWords + 2) { String[] directWords = row.getCell(j).getStringCellValue().split(";"); for (String string : directWords) { category.addDecisiveKeyword(string.toLowerCase().trim()); } } //what supercategory the category belongs to if (j == maxColKeyWords + 3) { category.setSuperCategory(row.getCell(j).getStringCellValue()); } } if (!category.getMinNumberKeywords().equals(0)) { categories.add(category); } } }
From source file:cdiscisa.StreamUtil.java
private static Curso llenarCurso(Workbook wbLista, String unidadCapacitadora, String instructor) throws Exception { Sheet wbListaSheet = wbLista.getSheetAt(0); Curso c = new Curso(); if (!(wbListaSheet.getRow(2) == null || wbListaSheet.getRow(2).getCell(4) == null || wbListaSheet.getRow(2).getCell(4).getStringCellValue().isEmpty())) { try { c.nombre_empresa = wbListaSheet.getRow(2).getCell(4).getStringCellValue(); } catch (Exception ex) { JOptionPane.showMessageDialog(null, "El nombre de la empresa en la Lista de Participantes parece tener datos no vlidos"); throw new netoCustomException("Error al leer los datos del curso"); }/*www.j a v a 2 s. co m*/ } if (!(wbListaSheet.getRow(4) == null || wbListaSheet.getRow(4).getCell(4) == null || wbListaSheet.getRow(4).getCell(4).getStringCellValue().isEmpty())) { try { c.nombre_curso = wbListaSheet.getRow(4).getCell(4).getStringCellValue(); } catch (Exception ex) { JOptionPane.showMessageDialog(null, "El nombre de el curso Lista de Participantes parece tener datos no vlidos"); throw new netoCustomException("Error al leer los datos del curso"); } } if (!(wbListaSheet.getRow(6) == null || wbListaSheet.getRow(6).getCell(4) == null || wbListaSheet.getRow(6).getCell(4).getStringCellValue().isEmpty())) { try { c.nombre_instructor = wbListaSheet.getRow(6).getCell(4).getStringCellValue(); } catch (Exception ex) { JOptionPane.showMessageDialog(null, "El nombre de el instructor Lista de Participantes parece tener datos no vlidos"); throw new netoCustomException("Error al leer los datos del curso"); } } if (!(wbListaSheet.getRow(8) == null || wbListaSheet.getRow(8).getCell(4) == null || wbListaSheet.getRow(8).getCell(4).getStringCellValue().isEmpty())) { try { c.horas_texto = wbListaSheet.getRow(8).getCell(4).getStringCellValue(); } catch (Exception ex) { JOptionPane.showMessageDialog(null, "La casilla de Horas en la Lista de Participantes parece tener datos no vlidos"); throw new netoCustomException("Error al leer los datos del curso"); } } if (!(wbListaSheet.getRow(11) == null || wbListaSheet.getRow(11).getCell(4) == null || wbListaSheet.getRow(11).getCell(4).getStringCellValue().isEmpty())) { try { c.razon_social = wbListaSheet.getRow(11).getCell(4).getStringCellValue(); if (!c.razon_social.equalsIgnoreCase("NUEVA WAL?MART DE MEXICO S DE RL DE C.V.") && !c.razon_social.equalsIgnoreCase("NUEVA WAL-MART DE MEXICO S DE RL DE C.V.")) { c.walmart = false; } } catch (Exception ex) { JOptionPane.showMessageDialog(null, "La casilla de Razn Social de la empresa en la Lista de Participantes parece tener datos no vlidos"); throw new netoCustomException("Error al leer los datos del curso"); } } if (!(wbListaSheet.getRow(13) == null || wbListaSheet.getRow(13).getCell(4) == null || wbListaSheet.getRow(13).getCell(4).getStringCellValue().isEmpty())) { try { c.rfc_empresa = wbListaSheet.getRow(13).getCell(4).getStringCellValue(); } catch (Exception ex) { JOptionPane.showMessageDialog(null, "La casill de RFC de la empresa en Lista de Participantes parece tener datos no vlidos"); throw new netoCustomException("Error al leer los datos del curso"); } } else { JOptionPane.showMessageDialog(null, "El RFC de la empresa no puede estar vacio"); throw new netoCustomException("Error al leer los datos del curso"); } if (!(wbListaSheet.getRow(15) == null || wbListaSheet.getRow(15).getCell(4) == null || wbListaSheet.getRow(15).getCell(4).getStringCellValue().isEmpty())) { try { c.fecha_certificado = wbListaSheet.getRow(15).getCell(4).getStringCellValue(); } catch (Exception ex) { JOptionPane.showMessageDialog(null, "La casilla de la fecha de certificado de la empresa en la Lista de Participantes parece tener datos no vlidos"); throw new netoCustomException("Error al leer los datos del curso"); } } if (!(wbListaSheet.getRow(17) == null || wbListaSheet.getRow(17).getCell(4) == null || wbListaSheet.getRow(17).getCell(4).getStringCellValue().isEmpty())) { try { c.fecha_texto_diploma = wbListaSheet.getRow(17).getCell(4).getStringCellValue(); } catch (Exception ex) { JOptionPane.showMessageDialog(null, "La casilla de la fecha para diploma de la empresa en la Lista de Participantes parece tener datos no vlidos"); throw new netoCustomException("Error al leer los datos del curso"); } } if (!unidadCapacitadora.isEmpty()) { c.uCapacitadora = unidadCapacitadora; } if (!instructor.isEmpty()) { c.capacitador = instructor; } Calendar cal = Calendar.getInstance(); if (!(wbListaSheet.getRow(4) == null || wbListaSheet.getRow(4).getCell(6) == null)) { if (wbListaSheet.getRow(4).getCell(6).getCellType() == 1) { cal.set(Calendar.DAY_OF_MONTH, Integer.parseInt(wbListaSheet.getRow(4).getCell(6).getStringCellValue())); } else { cal.set(Calendar.DAY_OF_MONTH, (int) wbListaSheet.getRow(4).getCell(6).getNumericCellValue()); } } if (!(wbListaSheet.getRow(6) == null || wbListaSheet.getRow(6).getCell(6) == null)) { cal.set(Calendar.MONTH, Integer.parseInt(wbListaSheet.getRow(6).getCell(6).getStringCellValue()) - 1); } if (!(wbListaSheet.getRow(8) == null || wbListaSheet.getRow(8).getCell(6) == null)) { cal.set(Calendar.YEAR, (int) wbListaSheet.getRow(8).getCell(6).getNumericCellValue()); } c.fecha_inicio = cal.getTime(); if (!(wbListaSheet.getRow(4) == null || wbListaSheet.getRow(4).getCell(7) == null)) { if (wbListaSheet.getRow(4).getCell(7).getCellType() == 1) { cal.set(Calendar.DAY_OF_MONTH, Integer.parseInt(wbListaSheet.getRow(4).getCell(7).getStringCellValue())); } else { cal.set(Calendar.DAY_OF_MONTH, (int) wbListaSheet.getRow(4).getCell(7).getNumericCellValue()); } } if (!(wbListaSheet.getRow(6) == null || wbListaSheet.getRow(6).getCell(7) == null)) { cal.set(Calendar.MONTH, Integer.parseInt(wbListaSheet.getRow(6).getCell(7).getStringCellValue()) - 1); } if (!(wbListaSheet.getRow(8) == null || wbListaSheet.getRow(8).getCell(7) == null)) { cal.set(Calendar.YEAR, (int) wbListaSheet.getRow(8).getCell(7).getNumericCellValue()); } c.fecha_termino = cal.getTime(); return c; }
From source file:ch.bfh.lca._15h.library.export.ExportToExcel.java
/*** * Prototype function. Not yet functional. * Allow to generate an age pyramid graphic in Excel by using an existing Excel Template. * @param language Language of the label in the Excel file * @param rows Arrays of rows to include in the listing * @param excelFilePath Path of the outputed file * @throws FileNotFoundException//from w ww.j a v a 2 s . c o m * @throws IOException * @throws InvalidFormatException */ public static void exportToAgePyramid(Translation.TRANSLATION_LANGUAGE language, GenericResultRow[] rows, String excelFilePath) throws FileNotFoundException, IOException, InvalidFormatException { //open template URL url = Translation.class.getClassLoader() .getResource("ch/bfh/lca/_15h/library/export/template/alter-pyramide-v2.xlsx"); //Workbook wb = WorkbookFactory.create(new File(url.getPath())); XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(new File(url.getPath()))); Sheet sheet = wb.getSheetAt(0); //http://www.programming-free.com/2012/12/create-charts-in-excel-using-java.html //https://poi.apache.org/spreadsheet/quick-guide.html#NewWorkbook Row row; Cell cell; for (int i = 0; i < 20; i++) { row = sheet.getRow(i + 1); if (row == null) { row = sheet.createRow(i + 1); } for (int j = 0; j < 3; j++) { cell = row.getCell(j); if (cell == null) { cell = row.createCell(j); } switch (j) { case 0: cell.setCellValue(i); break; case 1: cell.setCellValue(i * j * -1); break; case 2: cell.setCellValue(i * j); break; } } } //redefine data range //http://thinktibits.blogspot.ch/2014/09/Excel-Insert-Format-Table-Apache-POI-Example.html XSSFSheet sheet1 = wb.getSheetAt(0); XSSFTable table = sheet1.getTables().get(0); CTTable cttable = table.getCTTable(); AreaReference my_data_range = new AreaReference(new CellReference(0, 0), new CellReference(20, 2)); /* Set Range to the Table */ cttable.setRef(my_data_range.formatAsString()); // cttable.setDisplayName("DATEN"); /* this is the display name of the table */ //cttable.setName("test"); /* This maps to "displayName" attribute in <table>, OOXML */ //cttable.setId(1L); //id attribute against table as long value /* //redefine data range Name rangeCell = wb.getName("DATEN"); //Set new range for named range //String reference = sheetName + "!$C$" + (deface + 1) + ":$C$" + (rowNum + deface); String reference = sheet.getSheetName() + "!$A$2:$C$20"; //Assigns range value to named range rangeCell.setRefersToFormula(reference); */ //write to the file FileOutputStream fileOut = new FileOutputStream(excelFilePath); wb.write(fileOut); fileOut.close(); wb.close(); }
From source file:ch.ledcom.log4jtools.config.ConfigReader.java
License:Apache License
public List<CategorizationFilter> loadConfig(InputStream in) throws InvalidFormatException, IOException { List<CategorizationFilter> result = new ArrayList<CategorizationFilter>(); Workbook wb = WorkbookFactory.create(in); Sheet sheet = wb.getSheetAt(0); int i = 1;/*w w w . ja v a 2 s.co m*/ while (true) { Row row = sheet.getRow(i++); String description = readCell(row, 0); if (description == null) { break; } String loggerName = readCell(row, 1); Level level = readCell(row, 2) == null ? (Level) null : Level.toLevel(readCell(row, 2)); Pattern messagePattern = readPattern(row, 3); Pattern throwablePattern = readPattern(row, 4); String category = readCell(row, 5); String bugTrackerRef = readCell(row, 6); CategorizationFilter filter = new CategorizationFilter(description, loggerName, level, messagePattern, throwablePattern, category, bugTrackerRef); result.add(filter); } return result; }
From source file:ch.oakmountain.tpa.parser.TpaParser.java
License:Apache License
private void emptyWorksheetColumns(String wsName, int rowsFrom, List<Integer> colsToDelete) { String trainpathsNoOperatingDayMarker = getPropertyValue(tpaProps.TRAINPATHS_NO_OPERATING_DAY_MARKER); Sheet sheet = wb.getSheet(wsName); for (int i = rowsFrom; i <= sheet.getLastRowNum(); i++) { Row row = sheet.getRow(i); for (Integer j : colsToDelete) { Cell cell = row.getCell(j);//from w ww . j av a2s .com if (cell != null && !formatter.formatCellValue(cell).equals(trainpathsNoOperatingDayMarker)) { cell.setCellValue((String) null); } } } }
From source file:ch.oakmountain.tpa.parser.TpaParser.java
License:Apache License
/** * @param wsName//from w ww .ja v a 2 s . c o m * @param rowsFrom * @param colsToDelete */ private void resetRequestAllocations(String wsName, int rowsFrom, List<Integer> colsToDelete) { String requestsAllocatedDayMarker = getPropertyValue(tpaProps.REQUESTS_ALLOCATED_DAY_MARKER); String requestsRequestedDayMarker = getPropertyValue(tpaProps.REQUESTS_REQUESTED_DAY_MARKER); Sheet sheet = wb.getSheet(wsName); for (int i = rowsFrom; i <= sheet.getLastRowNum(); i++) { Row row = sheet.getRow(i); for (Integer j : colsToDelete) { Cell cell = row.getCell(j); if (cell != null && formatter.formatCellValue(cell).equals(requestsAllocatedDayMarker)) { cell.setCellValue(requestsRequestedDayMarker); } } } }
From source file:ch.oakmountain.tpa.parser.TpaParser.java
License:Apache License
/** * Put the train path ids in the format <train_path_slot_id><hour_of_day_>-<three_digit_sequence_number_within_hour> * * @param wsName// w w w .jav a2 s . com * @param rowsFrom * @param cols */ private void correctTrainPathIds(String wsName, int rowsFrom, Map<ColumnIdentifier, Integer> cols) { Sheet sheet = wb.getSheet(wsName); for (int i = rowsFrom; i <= sheet.getLastRowNum(); i++) { Row row = sheet.getRow(i); if (row == null) { continue; } Map<ColumnIdentifier, String> line = getWorksheetPointerStringMap(cols, row); String uncorrectedSlotName = line.get(trainPathLayout.ID); if (StringUtils.isBlank(uncorrectedSlotName)) { continue; } try { LocalTime startTime = LocalTime.parse(line.get(trainPathLayout.DEPTIME)); String correctedSlotName = getNextSlotId(wsName, startTime.getHourOfDay()); if (!correctedSlotName.equals(uncorrectedSlotName)) { LOGGER.warn("Correcting slot name " + uncorrectedSlotName + " => " + correctedSlotName); row.getCell(cols.get(trainPathLayout.ID)).setCellValue(correctedSlotName); } } catch (IllegalArgumentException e) { LOGGER.warn(corrupt_input, "Illegal start time \"" + line.get(trainPathLayout.DEPTIME) + "\" for slot " + uncorrectedSlotName + " in sheet " + wsName + " found; skipping this slot.", e); continue; } } }
From source file:ch.oakmountain.tpa.parser.TpaParser.java
License:Apache License
private List<Map<ColumnIdentifier, String>> readWorksheet(String wsName, int headerRowsNb, Map<ColumnIdentifier, Integer> cols) { List<Map<ColumnIdentifier, String>> output = new LinkedList<Map<ColumnIdentifier, String>>(); Sheet sheet = wb.getSheet(wsName); for (int i = headerRowsNb; i <= sheet.getLastRowNum(); i++) { Row row = sheet.getRow(i); if (row == null) { continue; }/*ww w . j a va 2s .co m*/ Map<ColumnIdentifier, String> outputItem = getWorksheetPointerStringMap(cols, row); output.add(outputItem); } return output; }