List of usage examples for org.apache.poi.ss.usermodel Sheet getSheetName
String getSheetName();
From source file:gov.va.isaac.request.uscrs.USCRSBatchTemplate.java
License:Apache License
public USCRSBatchTemplate(InputStream spreadsheetTemplate) throws IOException { wb = new HSSFWorkbook(spreadsheetTemplate); ch = wb.getCreationHelper();//from www . j av a2 s .co m for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet s = wb.getSheetAt(i); SHEET sheetEnum = SHEET.valueOf(enumSafeCharExchange(s.getSheetName())); if (sheetEnum == null) { throw new RuntimeException( "No enum type found for sheet " + s.getSheetName() + " - code out of sync with template"); } else { sheetNamePositionMap.put(sheetEnum, i); } if (s.getSheetName().equals("Help")) { continue; } LinkedHashMap<COLUMN, Integer> colList = new LinkedHashMap<>(); columnNamePositionMap.put(sheetEnum, colList); wb.getSheetAt(i).getRow(0).forEach(headerCell -> { if (s.getSheetName().equals("metadata") && headerCell.getCellType() == Cell.CELL_TYPE_NUMERIC) { //SKIP - the metadata tab has a cell that is just a number - likely the release date } else { COLUMN colEnum = COLUMN.valueOf(enumSafeCharExchange(toString(headerCell))); if (colEnum == null) { throw new RuntimeException("No enum type found for colum " + toString(headerCell) + " on sheet " + s.getSheetName() + " - code out of sync with template"); } else { colList.put(colEnum, headerCell.getColumnIndex()); } } }); } }
From source file:gov.va.isaac.request.uscrs.USCRSBatchTemplate.java
License:Apache License
/** * Generate the enums and constants from a template file for use at the top of this class *///from ww w . j ava2 s. c o m public static void main(String[] args) throws IOException { //USCRSBatchTemplate b = new USCRSBatchTemplate(USCRSBatchTemplate.class.getResourceAsStream("/USCRS_Batch_Template-2015-01-27.xls")); Workbook wb = new HSSFWorkbook( USCRSBatchTemplate.class.getResourceAsStream("/USCRS_Batch_Template-2015-01-27.xls")); ArrayList<String> sheets = new ArrayList<>(); HashSet<String> columns = new HashSet<>(); LinkedHashMap<String, ArrayList<String>> pickLists = new LinkedHashMap<>(); for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet sheet = wb.getSheetAt(i); String sheetName = sheet.getSheetName(); sheets.add(sheetName); if (sheetName.equals("Help")) { continue; } sheet.getRow(0).forEach(headerCell -> { if (sheetName.equals("metadata") && headerCell.getCellType() == Cell.CELL_TYPE_NUMERIC) { //SKIP - the metadata tab has a cell that is just a number - likely the release date } else { String stringValue = toString(headerCell); columns.add(stringValue); if (sheetName.equals("metadata")) { pickLists.put(stringValue, new ArrayList<>()); for (int row = 1; row < sheet.getLastRowNum(); row++) { Cell valueCell = sheet.getRow(row).getCell(headerCell.getColumnIndex()); if (valueCell != null) { String s = toString(valueCell); if (s.length() > 0) { pickLists.get(stringValue).add(s); } } } } } }); } String eol = System.getProperty("line.separator"); StringBuilder sb = new StringBuilder(); int i = 0; sb.append("public enum SHEET {"); for (String s : sheets) { sb.append(enumSafeCharExchange(s)); sb.append(", "); i++; if (i % 8 == 0) { i = 0; sb.append(eol); } } sb.setLength(sb.length() - (i == 0 ? 3 : 2)); sb.append("};"); System.out.println(sb); System.out.println(); sb.setLength(0); i = 0; sb.append("public enum COLUMN {"); for (String c : columns) { sb.append(enumSafeCharExchange(c)); sb.append(", "); i++; if (i % 8 == 0) { i = 0; sb.append(eol); } } sb.setLength(sb.length() - (i == 0 ? 3 : 2)); sb.append("};"); System.out.println(sb); sb.setLength(0); i = 0; for (Entry<String, ArrayList<String>> x : pickLists.entrySet()) { sb.append("public enum PICKLIST_"); sb.append(enumSafeCharExchange(x.getKey())); sb.append(" {"); for (String s : x.getValue()) { sb.append(enumSafeCharExchange(s)); sb.append("(\""); sb.append(s); sb.append("\")"); sb.append(", "); i++; if (i % 2 == 0) { i = 0; sb.append(eol); } } sb.setLength(sb.length() - (i == 0 ? 3 : 2)); sb.append(";" + eol); sb.append("\tprivate String value;" + eol + eol); sb.append("\tprivate PICKLIST_" + enumSafeCharExchange(x.getKey()) + " (String pickListValue)" + eol); sb.append("\t{" + eol); sb.append("\t\tvalue = pickListValue;" + eol); sb.append("\t}" + eol); sb.append("" + eol); sb.append("\t@Override" + eol); sb.append("\tpublic String toString()" + eol); sb.append("\t{" + eol); sb.append("\t\treturn value;" + eol); sb.append("\t}" + eol); sb.append("" + eol); sb.append("\tpublic static PICKLIST_" + enumSafeCharExchange(x.getKey()) + " find(String value)" + eol); sb.append("\t{" + eol); sb.append("\t\treturn PICKLIST_" + enumSafeCharExchange(x.getKey()) + ".valueOf(enumSafeCharExchange(value));" + eol); sb.append("\t}" + eol); sb.append("};"); System.out.println(sb); sb.setLength(0); i = 0; System.out.println(); } }
From source file:hjow.hgtable.util.XLSXUtil.java
License:Apache License
/** * <p>XLSX ? ? ?? . ? ? ?? ?? , ? ? ?? ? ? ?? ?.</p> * /*from ww w. j a v a 2s .co m*/ * @param file : XLSX ? * @return ? ? */ public static List<TableSet> toTableSets(File file) { List<TableSet> tableSets = new Vector<TableSet>(); org.apache.poi.ss.usermodel.Workbook workbook = null; if (file == null) throw new NullPointerException(Manager.applyStringTable("Please select file !!")); if (!file.exists()) throw new NullPointerException(Manager.applyStringTable("File") + " " + file.getAbsolutePath() + " " + Manager.applyStringTable("is not exist")); boolean isHead = true; int rowNum = 0; int cellNum = 0; int cellCount = 0; FileInputStream fileStream = null; try { if (file.getAbsolutePath().endsWith(".xlsx") || file.getAbsolutePath().endsWith(".XLSX")) { workbook = new org.apache.poi.xssf.usermodel.XSSFWorkbook(file); } else if (file.getAbsolutePath().endsWith(".xls") || file.getAbsolutePath().endsWith(".XLS")) { fileStream = new FileInputStream(file); workbook = new org.apache.poi.hssf.usermodel.HSSFWorkbook(fileStream); } org.apache.poi.ss.usermodel.FormulaEvaluator evals = workbook.getCreationHelper() .createFormulaEvaluator(); org.apache.poi.ss.usermodel.Sheet sheet = null; for (int x = 0; x < workbook.getNumberOfSheets(); x++) { TableSet newTableSet = new DefaultTableSet(); newTableSet.setColumns(new Vector<Column>()); sheet = workbook.getSheetAt(x); newTableSet.setName(sheet.getSheetName()); rowNum = 0; isHead = true; String targetData = null; for (org.apache.poi.ss.usermodel.Row row : sheet) { cellNum = 0; for (org.apache.poi.ss.usermodel.Cell cell : row) { try { if (cellNum >= cellCount) { throw new IndexOutOfBoundsException( Manager.applyStringTable("There are some cells not have their heads") + ", " + Manager.applyStringTable("Head count") + " : " + cellCount + ", " + Manager.applyStringTable("Cell Number") + " : " + cellNum); } switch (cell.getCellType()) { case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING: if (isHead) { newTableSet.getColumns().add(new Column( cell.getRichStringCellValue().getString(), Column.TYPE_STRING)); } else { targetData = cell.getRichStringCellValue().getString(); newTableSet.getColumns().get(cellNum).setType(cell.getCellType()); } break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC: if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) { if (isHead) { newTableSet.getColumns().add(new Column( String.valueOf(cell.getStringCellValue()), Column.TYPE_DATE)); } else { targetData = String.valueOf(cell.getDateCellValue()); newTableSet.getColumns().get(cellNum).setType(cell.getCellType()); } } else { if (isHead) { newTableSet.getColumns().add(new Column( String.valueOf(cell.getStringCellValue()), Column.TYPE_NUMERIC)); } else { double values = cell.getNumericCellValue(); double intPart = values - ((double) ((int) values)); if (intPart == 0.0) { targetData = String.valueOf(((int) values)); newTableSet.getColumns().get(cellNum).setType(Column.TYPE_INTEGER); } else { targetData = String.valueOf(values); newTableSet.getColumns().get(cellNum).setType(cell.getCellType()); } } } break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN: if (isHead) { newTableSet.getColumns().add(new Column( String.valueOf(cell.getStringCellValue()), Column.TYPE_BOOLEAN)); } else { targetData = String.valueOf(cell.getBooleanCellValue()); newTableSet.getColumns().get(cellNum).setType(cell.getCellType()); } break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA: if (isHead) { newTableSet.getColumns().add(new Column( String.valueOf(cell.getStringCellValue()), Column.TYPE_NUMERIC)); } else { if (evals.evaluateFormulaCell(cell) == 0) { targetData = String.valueOf(cell.getNumericCellValue()); newTableSet.getColumns().get(cellNum).setType(Column.TYPE_NUMERIC); } else if (evals.evaluateFormulaCell(cell) == 1) { targetData = String.valueOf(cell.getStringCellValue()); newTableSet.getColumns().get(cellNum).setType(Column.TYPE_STRING); } else if (evals.evaluateFormulaCell(cell) == 4) { targetData = String.valueOf(cell.getBooleanCellValue()); newTableSet.getColumns().get(cellNum).setType(Column.TYPE_BOOLEAN); } else { targetData = String.valueOf(cell.getCellFormula()); newTableSet.getColumns().get(cellNum).setType(cell.getCellType()); } } break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK: if (isHead) { newTableSet.getColumns().add(new Column("", Column.TYPE_STRING)); } else { targetData = ""; newTableSet.getColumns().get(cellNum).setType(Column.TYPE_BLANK); } break; default: if (isHead) { newTableSet.getColumns().add(new Column("", Column.TYPE_STRING)); } else { try { targetData = cell.getStringCellValue(); } catch (Exception e1) { e1.printStackTrace(); } newTableSet.getColumns().get(cellNum).setType(cell.getCellType()); } break; } if (isHead) { cellCount++; } else { while (rowNum > 0 && newTableSet.getColumns().get(cellNum).getData().size() < rowNum) { newTableSet.getColumns().get(cellNum).getData().add(""); } if (targetData != null) newTableSet.getColumns().get(cellNum).getData().add(targetData); else { newTableSet.getColumns().get(cellNum).getData().add(""); } } } catch (ArrayIndexOutOfBoundsException e1) { StringBuffer err = new StringBuffer(""); for (StackTraceElement errEl : e1.getStackTrace()) { err = err.append("\t " + errEl + "\n"); } String cellObject = null; try { cellObject = cell.getStringCellValue(); } catch (Exception e2) { } throw new ArrayIndexOutOfBoundsException( Manager.applyStringTable("Array index out of range") + " <- " + Manager.applyStringTable("Reading xlsx file") + " : " + file.getName() + ", " + sheet.getSheetName() + "\n" + Manager.applyStringTable("On") + " " + Manager.applyStringTable("Row") + " " + rowNum + ", " + Manager.applyStringTable("Cell") + " " + cellNum + ", " + Manager.applyStringTable("Value") + " : " + String.valueOf(cellObject) + "\n " + Manager.applyStringTable("<-\n") + err + "\n " + Manager.applyStringTable("Original Message") + "...\n" + e1.getMessage() + "\n" + Manager.applyStringTable("End")); } cellNum++; } isHead = false; rowNum++; } fillTableSet(newTableSet); newTableSet.removeEmptyColumn(true); tableSets.add(newTableSet); } return tableSets; } catch (Throwable e) { if (Main.MODE >= DebuggingUtil.DEBUG) e.printStackTrace(); Main.logError(e, Manager.applyStringTable("On reading xlsx") + " : " + file + "\n" + Manager.applyStringTable("At rownum") + " " + rowNum + ", " + Manager.applyStringTable("cellnum") + " " + cellNum); return null; } finally { try { workbook.close(); } catch (Throwable e) { } try { if (fileStream != null) fileStream.close(); } catch (Throwable e) { } } }
From source file:Import.SheetFrameController.java
private void updateListSheet() { // initalisation du listSheet ArrayList l = new ArrayList(); l.clear();/* ww w. j ava 2 s .co m*/ // chargement du fichier excel if (modelDataSearch.getFile() != null) { try { book = WorkbookFactory.create(modelDataSearch.getFile()); // rcupration du nombre de sheet int nbSheet = book.getNumberOfSheets(); // rcupration des sheets for (int i = 0; i < nbSheet; i++) { Sheet sh = book.getSheetAt(i); l.add(sh.getSheetName()); } } catch (FileNotFoundException ex) { Logger.getLogger(SheetFrameController.class.getName()).log(Level.SEVERE, null, ex); this.alertException(ex.getMessage()); } catch (IOException ex) { this.alertException(ex.getMessage()); Logger.getLogger(SheetFrameController.class.getName()).log(Level.SEVERE, null, ex); } catch (InvalidFormatException ex) { this.alertException(ex.getMessage()); Logger.getLogger(SheetFrameController.class.getName()).log(Level.SEVERE, null, ex); } catch (EncryptedDocumentException ex) { this.alertException(ex.getMessage()); Logger.getLogger(SheetFrameController.class.getName()).log(Level.SEVERE, null, ex); } } ObservableList<String> ol = FXCollections.observableArrayList(l); listSheet.setItems(ol); }
From source file:info.informationsea.java.excel2csv.UtilitiesTest.java
License:Open Source License
@Test public void testCreateUniqueNameSheetForWorkbook() throws Exception { Workbook workbook = new HSSFWorkbook(); Sheet sheet1 = Utilities.createUniqueNameSheetForWorkbook(workbook, "Sheet", false); Assert.assertEquals("Sheet", sheet1.getSheetName()); Sheet sheet2 = Utilities.createUniqueNameSheetForWorkbook(workbook, "Sheet", false); Assert.assertEquals("Sheet-1", sheet2.getSheetName()); Sheet sheet3 = Utilities.createUniqueNameSheetForWorkbook(workbook, "Sheet", false); Assert.assertEquals("Sheet-2", sheet3.getSheetName()); Sheet sheet4 = Utilities.createUniqueNameSheetForWorkbook(workbook, "Sheet", true); Assert.assertEquals("Sheet", sheet4.getSheetName()); }
From source file:it.greenvulcano.excel.reader.ToXMLReader.java
License:Open Source License
@Override protected boolean processSheet(Sheet sheet, int sNum) throws ExcelException { if (sheet.getPhysicalNumberOfRows() > 0) { shE = parser.createElement(doc, "sheet"); root.appendChild(shE);//w w w . j a va2 s . co m parser.setAttribute(shE, "n", String.valueOf(sNum)); Node name = shE.appendChild(parser.createElement(doc, "name")); name.appendChild(doc.createTextNode(sheet.getSheetName())); return true; } return false; }
From source file:it.smartcommunitylab.riciclo.app.importer.converter.DataImporter.java
License:Apache License
private Rifiuti readExcel(InputStream inp) throws Exception { HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp)); ExcelExtractor extractor = new ExcelExtractor(wb); extractor.setFormulasNotResults(true); extractor.setIncludeSheetNames(false); Rifiuti rifiuti = new Rifiuti(); Set<String> sheetNames = Sets.newHashSet(); for (int i = 0; i < wb.getNumberOfSheets(); i++) { sheetNames.add(wb.getSheetAt(i).getSheetName()); }/*from w w w . j a v a 2 s . com*/ Set missingExpected = Sets.newHashSet(expectedSheets); missingExpected.removeAll(sheetNames); Set additionalFound = Sets.newHashSet(sheetNames); additionalFound.removeAll(expectedSheets); if (!missingExpected.isEmpty() || !additionalFound.isEmpty()) { throw new ImportError(Lists.newArrayList("Missing sheet(s) expected: " + missingExpected, "Additional sheet(s) found: " + additionalFound)); } for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet sheet = wb.getSheetAt(i); Thread.sleep(500); // System.out.println(sheet.getSheetName()); // if (sheet.getRow(0).getLastCellNum() == 1 && !oneColumnAsMany.contains(sheet.getSheetName())) { // } else { { System.err.println(">" + sheet.getSheetName()); List<Map<String, String>> result = getSheetMap(sheet); mapMap(rifiuti, sheet.getSheetName(), result); } } completePuntiRaccolta(rifiuti); return rifiuti; }
From source file:it.smartcommunitylab.ungiorno.importer.Importer.java
License:Apache License
/** * @param appId//from ww w . j a v a 2 s. c om * @param schoolId * @param wb * @throws ImportError */ private void mapChildrenData(String appId, String schoolId, HSSFWorkbook wb) throws ImportError { Map<String, KidProfile> kidMap = null; for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet sheet = wb.getSheetAt(i); List<Map<String, String>> result = getSheetMap(sheet); if (SHEET_PROFILO.equals(sheet.getSheetName())) { kidMap = parseKidProfiles(appId, schoolId, result); } if (SHEET_UTENTI.equals(sheet.getSheetName())) { parseUsers(appId, schoolId, result, kidMap); } if (SHEET_DELEGHE.equals(sheet.getSheetName())) { parseDeleghe(appId, schoolId, result, kidMap); } if (SHEET_ALLERGIE.equals(sheet.getSheetName())) { parseAllergie(appId, schoolId, result, kidMap); } if (SHEET_BUS.equals(sheet.getSheetName())) { parseKidBus(appId, schoolId, result, kidMap); } if (SHEET_INSEGNANTI.equals(sheet.getSheetName())) { parseInsegnanti(appId, schoolId, result, kidMap); } } }
From source file:it.smartcommunitylab.ungiorno.importer.Importer.java
License:Apache License
/** * @param wb/*from w ww .j a va 2 s.c o m*/ * @param schoolProfile * @param teachers */ private void mapSchoolData(String appId, String schoolId, HSSFWorkbook wb) { for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet sheet = wb.getSheetAt(i); { List<Map<String, String>> result = getSheetMap(sheet); if (SHEET_PROFILO.equals(sheet.getSheetName())) { parseProfile(schoolProfile, result); } if (SHEET_ASSENZE.equals(sheet.getSheetName())) { List<TypeDef> types = parseTypes(result); schoolProfile.setAbsenceTypes(types); } if (SHEET_MALATTIE.equals(sheet.getSheetName())) { List<TypeDef> types = parseTypes(result); schoolProfile.setFrequentIllnesses(types); } if (SHEET_TIPOLOGIE_NOTE.equals(sheet.getSheetName())) { List<TypeDef> types = parseTypes(result); schoolProfile.setTeacherNoteTypes(types); } if (SHEET_CIBI.equals(sheet.getSheetName())) { List<TypeDef> types = parseTypes(result); schoolProfile.setFoodTypes(types); } if (SHEET_BUS.equals(sheet.getSheetName())) { schoolProfile.setBuses(parseBuses(result)); } if (SHEET_SEZIONI.equals(sheet.getSheetName())) { schoolProfile.setSections(parseSections(result)); } if (SHEET_INSEGNANTI.equals(sheet.getSheetName())) { parseTeachers(appId, schoolId, result); } } } }
From source file:it.smartcommunitylab.ungiorno.importer.Importer.java
License:Apache License
private List<Map<String, String>> getSheetMap(Sheet sheet) { System.err.println(sheet.getSheetName()); Row row = sheet.getRow(0);//w ww . j a v a 2 s. c o m List<String> keys = new ArrayList<String>(); int firstRow = 2; if (row.getLastCellNum() != 1) { for (int j = 0; j < row.getLastCellNum(); j++) { String key = getCellValue(row.getCell(j), null).toUpperCase().replace(' ', '_').trim(); keys.add(key); } } else { keys.add("valore"); } List<Map<String, String>> result = new ArrayList<Map<String, String>>(); for (int i = firstRow; i <= sheet.getLastRowNum(); i++) { row = sheet.getRow(i); if (row == null) { continue; } Map<String, String> map = new TreeMap<String, String>(); boolean add = false; for (int j = 0; j < row.getLastCellNum(); j++) { if (j >= keys.size()) { continue; } if (row.getCell(j) != null) { String value = getCellValue(row.getCell(j), keys.get(j)).replace("_", " ").trim(); if (!value.isEmpty()) { add = true; } try { map.put(keys.get(j), value); } catch (Exception e) { e.printStackTrace(); } } else { map.put(keys.get(j), ""); } } if (add) { result.add(map); } } return result; }