List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getRow
@Override public HSSFRow getRow(int rowIndex)
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); 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); }/*from ww w .j a v a 2 s.c om*/ 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:no.abmu.abmstatistikk.annualstatistic.util.ExcelWithLibraryInformationAndDataParser.java
License:Open Source License
/** * Iterates the rows in the Spreadsheet data and builds up the * LibraryInformation instances.// w w w. ja v a 2s. c om */ protected void extractLibraryInformation() { HSSFSheet sheet; HSSFRow row; LibraryInformation libraryInformation; String organisationUnitId; libraryInformationList = new ArrayList(); sheet = workBook.getSheet(getSheetName()); if (sheet == null) { logger.error("Can't extract information. The Excel document does not have a sheet with name '" + getSheetName() + "'"); throw new IllegalArgumentException("Can't extract information, sheet not found"); } logger.info("Reading " + sheet.getLastRowNum() + " rows."); for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) { row = sheet.getRow(rowIndex); if (row == null) { /* No more rows */ break; } libraryInformation = new LibraryInformation(); organisationUnitId = getStringValue(row, getOrganisationUnitIdColumnIdx()); logger.debug("No OU on line " + rowIndex); if (rowIndex == fieldLine) { // Building "coloumn nr" => "Felt nr" hash; short max = row.getLastCellNum(); for (short i = 0; i < max; i++) { HSSFCell x = row.getCell(i); if (x != null) { String cellString = getStringValue(row, new Short(i)); if (cellString != null) { Pattern p = Pattern.compile("^(Felt)?\\s*(\\d+)$"); Matcher m = p.matcher(cellString); if (m.matches()) { String fieldNumber = m.group(2); if (fieldNumber.length() == 1) { fieldNumber = "00" + fieldNumber; } else if (fieldNumber.length() == 2) { fieldNumber = "0" + fieldNumber; } logger.info("Found field " + fieldNumber); feltMap.put(fieldNumber, new Short(i)); } } } } continue; } if (rowIndex < firstDataLine) { continue; } libraryInformation.setOrganisationUnitId(organisationUnitId); /* Scan all "Felt" fields */ Map resultMap = new HashMap(); Set keys = feltMap.keySet(); Iterator it = keys.iterator(); String key = null; while (it.hasNext()) { key = (String) (it.next()); String val = getStringValue(row, (Short) feltMap.get(key)); resultMap.put(key, val); } libraryInformation.addResultMap(resultMap); logger.debug("Adding resultmap: " + resultMap); if (organisationType.equals("Fagbibliotek")) { Long orgTypeNumber = getLongValue(row, new Short((short) 11)); String fagBibType = null; if (orgTypeNumber != null) { switch (orgTypeNumber.intValue()) { case 0: fagBibType = OrganisationTypeNameConst.NATIONAL_LIBRARY; break; case 1: fagBibType = OrganisationTypeNameConst.UNIVERITY_OF_OSLO; break; case 2: fagBibType = OrganisationTypeNameConst.UNIVERITY_OF_BERGEN; break; case 3: fagBibType = OrganisationTypeNameConst.UNIVERITY_OF_TRONDHEIM; break; case 4: fagBibType = OrganisationTypeNameConst.UNIVERITY_OF_TROMSOE; break; case 5: fagBibType = OrganisationTypeNameConst.SPECIALISED_UNIVERISTY_LIBRARY; break; case 6: fagBibType = OrganisationTypeNameConst.PUBLIC_COLLEGE_LIBRARY; break; case 7: fagBibType = OrganisationTypeNameConst.PRIVATE_COLLEGE_LIBRARY; break; case 8: fagBibType = OrganisationTypeNameConst.PUBLIC_SPECIAL_LIBRARY; break; case 9: fagBibType = OrganisationTypeNameConst.PRIVATE_SPECIAL_LIBRARY; break; default: fagBibType = OrganisationTypeNameConst.FAG_LIBRARY; break; } } libraryInformation.organisationType = fagBibType; } else if (organisationType.equals("Folkebibliotek")) { if (organisationUnitId != null && organisationUnitId.equals("456")) { libraryInformation.organisationType = "Folkebibliotek Svalbard"; } else { libraryInformation.organisationType = organisationType; } } else if (organisationType.equals("Grunnskolebibliotek")) { if (organisationUnitId != null && organisationUnitId.equals("3580")) { libraryInformation.organisationType = "Grunnskolebibliotek Svalbard"; } else { libraryInformation.organisationType = organisationType; } } else { libraryInformation.organisationType = organisationType; } libraryInformationList.add(libraryInformation); } }
From source file:no.abmu.organisationregister.util.ExcelWithLibraryInformationParser.java
License:Open Source License
/** * Iterates the rows in the Spreadsheet data and builds up the * LibraryInformation instances./* w w w .ja v a 2 s. c om*/ */ protected void extractLibraryInformation() { HSSFSheet sheet; HSSFRow row; LibraryInformation libraryInformation; Long organisationUnitId; libraryInformationList = new ArrayList(); sheet = workBook.getSheet(getSheetName()); if (sheet == null) { logger.error("Can't extract information. The Excel document does not have a sheet with name '" + getSheetName() + "'"); throw new IllegalArgumentException("Can't extract information, sheet not found"); } for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) { row = sheet.getRow(rowIndex); if (row == null) { /* No more rows */ break; } libraryInformation = new LibraryInformation(); organisationUnitId = getLongValue(row, getOrganisationUnitIdColumnIdx()); if (organisationUnitId == null) { continue; } libraryInformation.setOrganisationUnitId(organisationUnitId); libraryInformation.setOrganizationName(getStringValue(row, getOrganisactionNameColumnIdx())); libraryInformation.setAddress(getStringValue(row, getAddressColumnIdx())); libraryInformation.setLibraryCode(getLongValue(row, getLibraryCodeColumnIdx())); libraryInformation.setPostCodeName(getStringValue(row, getPostCodeNameColumnIdx())); libraryInformation.setPostCodeNumber(getStringValue(row, getPostCodeNumberColumnIdx())); libraryInformationList.add(libraryInformation); } }
From source file:no.uio.medicine.virsurveillance.parsers.XlsPopulationParser.java
private void readCountries(String inputFile) { try {//from ww w . ja va2s . c om POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(inputFile)); HSSFWorkbook wb = new HSSFWorkbook(fs); // HSSFSheet sheet = wb.getSheetAt(1); //page with the information of the countries HSSFRow row; HSSFCell cell; int rows; // No of rows rows = sheet.getPhysicalNumberOfRows(); int cols = 0; // No of columns int tmp = 0; // This trick ensures that we get the data properly even if it doesn't start from first few rows. // taken from stack overflow for (int i = 0; i < 10 || i < rows; i++) { row = sheet.getRow(i); if (row != null) { tmp = sheet.getRow(i).getPhysicalNumberOfCells(); if (tmp > cols) { cols = tmp; } } } HSSFRow header = sheet.getRow(0); int ccInd = 0; int regInd = 0; int igInd = 0; int namInd = 0; for (int i = 0; i < header.getLastCellNum(); i++) { if (header.getCell(i).toString().equalsIgnoreCase("Country Code")) { ccInd = i; } if (header.getCell(i).toString().equalsIgnoreCase("Region")) { regInd = i; } if (header.getCell(i).toString().equalsIgnoreCase("IncomeGroup")) { igInd = i; } if (header.getCell(i).toString().equalsIgnoreCase("TableName")) { namInd = i; } } for (int r = 1; r < rows; r++) { row = sheet.getRow(r); if (row != null) { CountryData cd; if (row.getCell(regInd) != null && row.getCell(igInd) != null) { cd = new CountryData(row.getCell(namInd).toString().replace("'", "`"), row.getCell(ccInd).toString().replace("'", "`"), row.getCell(regInd).toString().replace("'", "`"), row.getCell(igInd).toString().replace("'", "`")); } else { cd = new CountryData(row.getCell(namInd).toString(), row.getCell(ccInd).toString(), "", "", true); } countries.add(cd); } } } catch (Exception ioe) { ioe.printStackTrace(); System.out.println("##### ERROR: It looks like " + inputFile + " is not the appropriate type of file or it is not propperly structured"); } }
From source file:no.uio.medicine.virsurveillance.parsers.XlsPopulationParser.java
private void readAndStorePopulations(String inputFile) { try {/*ww w . ja v a2 s . c om*/ POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(inputFile)); HSSFWorkbook wb = new HSSFWorkbook(fs); // HSSFSheet sheet = wb.getSheetAt(0); //page with the population of the countries HSSFRow row; HSSFCell cell; int rows; // No of rows rows = sheet.getPhysicalNumberOfRows(); int cols = 0; // No of columns int tmp = 0; // This trick ensures that we get the data properly even if it doesn't start from first few rows. // taken from stack overflow for (int i = 0; i < 10 || i < rows; i++) { row = sheet.getRow(i); if (row != null) { tmp = sheet.getRow(i).getPhysicalNumberOfCells(); if (tmp > cols) { cols = tmp; } } } //Start reading countries; HSSFRow header = sheet.getRow(3); ArrayList<Integer> years = new ArrayList<>(); for (int i = 4; i < header.getLastCellNum(); i++) { years.add(Integer.parseInt(header.getCell(i).toString())); } for (int r = 4; r < rows; r++) { row = sheet.getRow(r); if (row != null) { String countryName = row.getCell(1).toString(); int count = 0; for (int i = 4; i < row.getLastCellNum(); i++) { if (row.getCell(i) != null) { //System.out.print(years.get(count)+": "+Float.parseFloat(row.getCell(i).toString())+ " - "); try { sqlM.addPopulation(countryName, years.get(count), row.getCell(i).toString()); } catch (Exception e) { e.printStackTrace(); } } count++; } } } } catch (Exception ioe) { ioe.printStackTrace(); System.out.println("##### ERROR: It looks like " + inputFile + " is not the appropriate type of file or it is not propperly structured"); } }
From source file:npv.importer.XlsImporter.java
private Double[] parseFile() throws IOException { InputStream inputStream = new FileInputStream(file); POIFSFileSystem fs = new POIFSFileSystem(inputStream); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); System.out.println("Testing.First row num=" + sheet.getFirstRowNum()); findTag(sheet, tag);/* w w w.j av a 2s. c om*/ //reading an array of Ri values after '#Ri' tag HSSFRow row = sheet.getRow(rPosition[0]); ArrayList<Double> cellValues = new ArrayList<Double>(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getColumnIndex() >= rPosition[1] + 1) { cellValues.add(cell.getNumericCellValue()); } } rValues = new Double[cellValues.size()]; rValues = cellValues.toArray(new Double[rValues.length]); System.out.println("Values from sheet:"); for (int i = 0; i < rValues.length; i++) { System.out.println(rValues[i]); } return this.rValues; }
From source file:npv.importer.XlsImporter.java
private void findTag(HSSFSheet sheet, String searchTag) { //looking for '#Ri' tag HSSFRow row = sheet.getRow(0); Iterator<Row> rowIterator = sheet.iterator(); Iterator<Cell> cellIterator; boolean isFound = false; while (rowIterator.hasNext()) { if (!isFound) { Row rRow = rowIterator.next(); cellIterator = rRow.cellIterator(); while (cellIterator.hasNext()) { Cell rCell = cellIterator.next(); if (rCell.getCellType() == Cell.CELL_TYPE_STRING && rCell.getStringCellValue().equals(tag)) { rPosition[0] = rRow.getRowNum(); rPosition[1] = rCell.getColumnIndex(); isFound = true;/*w ww.jav a2 s . c om*/ break; } } } else { break; } } }
From source file:Ontogrator_kupkb.OWLNestedSetGenerator.java
License:Open Source License
private void loadData(String s) { File file = new File(s); InputStream inputStream = null; try {/*from w ww . ja v a2 s. c om*/ inputStream = file.toURI().toURL().openStream(); HSSFWorkbook workbook = new HSSFWorkbook(new BufferedInputStream(inputStream)); HSSFSheet sheet = workbook.getSheetAt(0); int lastRow = sheet.getLastRowNum(); for (int x = 1; x <= 17; x++) { HSSFRow row = sheet.getRow(x); if (row.getCell(0) != null) { int tabid = (int) row.getCell(0).getNumericCellValue(); int paneid = (int) row.getCell(1).getNumericCellValue(); String docid = String.valueOf(row.getCell(2).getNumericCellValue()).replace(".0", ""); String ontologyid = row.getCell(3).getStringCellValue(); String geneid = String.valueOf(row.getCell(4).getNumericCellValue()).replace(".0", ""); String genesymbol = row.getCell(5).getStringCellValue(); String unprotAcc = row.getCell(6).getStringCellValue(); String expName = row.getCell(7).getStringCellValue(); String expDesc = row.getCell(8).getStringCellValue(); String species = row.getCell(9).getStringCellValue(); String bioMaterial = row.getCell(10).getStringCellValue(); String bioMaterialName = row.getCell(11).getStringCellValue(); String quality = row.getCell(12).getStringCellValue(); System.out.println(tabid + "\t" + paneid + "\t" + docid + "\t" + ontologyid + "\t" + geneid + "\t" + genesymbol + "\t" + unprotAcc + "\t" + expName + "\t" + expDesc + "\t" + species + "\t" + bioMaterial + "\t" + bioMaterialName + "\t" + quality); Statement stmt = null; try { stmt = connect.createStatement(); stmt.execute("call ontogrator_kupkb.InsertHit('" + tabid + "','" + paneid + "','" + docid + "','" + ontologyid + "','" + geneid + "','" + genesymbol + "','" + unprotAcc + "','" + expName + "','" + expDesc + "','" + species + "','" + bioMaterial + "','" + bioMaterialName + "','" + quality + "')"); } catch (SQLException e) { e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates. } } Statement s2 = null; try { s2 = connect.createStatement(); s2.execute("call ontogrator_kupkb.UpdateOntologySubset()"); } catch (SQLException e) { e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates. } } } catch (IOException e) { e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates. } }
From source file:opisiame.controller.gestion_resultat.Choix_exportController.java
@FXML public void excel_export() { File excel_file = choix_chemin_enregistrement("Excel files (*.xls)", "*.xls"); if (onglet_actif.equals("questions")) { if (excel_file != null) { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Resultat par question"); sheet.autoSizeColumn(5);/*from w w w . java 2 s. com*/ create_data1(sheet, 0, "Question", "Pourcentage reponse A", "Pourcentage reponse B", "Pourcentage reponse C", "Pourcentage reponse D", "Pourcentage bonne rponse"); Row row = sheet.getRow(0); HSSFCellStyle cellStyle = null; HSSFFont font = wb.createFont(); font.setBold(true); cellStyle = wb.createCellStyle(); cellStyle.setFont(font); row.setRowStyle(cellStyle); for (int i = 0; i < reponse_questions.size(); i++) { Reponse_question rq = reponse_questions.get(i); create_data1(sheet, i + 1, rq.getQuestion(), rq.getStr_pourcentage_rep_a(), rq.getStr_pourcentage_rep_b(), rq.getStr_pourcentage_rep_c(), rq.getStr_pourcentage_rep_d(), rq.getStr_pourcentage()); } FileOutputStream fileOut; try { fileOut = new FileOutputStream(excel_file); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } } else if (onglet_actif.equals("eleves")) { if (excel_file != null) { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Resultat des tudiants"); sheet.autoSizeColumn(5); create_data2(sheet, 0, "Nom", "Prnom", "N tudiant", "Note", "Pourcentage"); Row row = sheet.getRow(0); HSSFCellStyle cellStyle = null; HSSFFont font = wb.createFont(); font.setBold(true); cellStyle = wb.createCellStyle(); cellStyle.setFont(font); row.setRowStyle(cellStyle); for (int i = 0; i < resultats_eleves.size(); i++) { Rep_eleves_quiz re = resultats_eleves.get(i); create_data2(sheet, i + 1, re.getNom_eleve(), re.getPrenom_eleve(), re.getNum_eleve().toString(), re.getNote_eleve().toString(), re.getPourcent_eleve().toString()); } FileOutputStream fileOut; try { fileOut = new FileOutputStream(excel_file); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } } else if (onglet_actif.equals("eleves_pas_num")) { if (excel_file != null) { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Resultat des tudiants"); sheet.autoSizeColumn(4); create_data3(sheet, 0, "Nom", "Prnom", "Note", "Pourcentage"); Row row = sheet.getRow(0); HSSFCellStyle cellStyle = null; HSSFFont font = wb.createFont(); font.setBold(true); cellStyle = wb.createCellStyle(); cellStyle.setFont(font); row.setRowStyle(cellStyle); for (int i = 0; i < resultats_eleves.size(); i++) { Rep_eleves_quiz re = resultats_eleves.get(i); create_data3(sheet, i + 1, re.getNom_eleve(), re.getPrenom_eleve(), re.getNote_eleve().toString(), re.getPourcent_eleve().toString()); } FileOutputStream fileOut; try { fileOut = new FileOutputStream(excel_file); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } } close_window(); }