List of usage examples for org.apache.poi.ss.usermodel Workbook getSheetName
String getSheetName(int sheet);
From source file:org.wso2.carbon.user.mgt.bulkimport.ExcelUserBulkImport.java
License:Open Source License
public void addUserList(UserStoreManager userStore) throws UserAdminException { try {//from w ww .j a va2 s . co m Workbook wb = this.createWorkbook(); Sheet sheet = wb.getSheet(wb.getSheetName(0)); String password = config.getDefaultPassword(); if (sheet == null || sheet.getLastRowNum() == -1) { throw new UserAdminException("The first sheet is empty"); } int limit = sheet.getLastRowNum(); boolean isDuplicate = false; boolean fail = false; boolean success = false; String lastError = "UNKNOWN"; for (int i = 1; i < limit + 1; i++) { Row row = sheet.getRow(i); Cell cell = row.getCell(0); String userName = cell.getStringCellValue(); if (userName != null && userName.trim().length() > 0) { try { if (!userStore.isExistingUser(userName)) { userStore.addUser(userName, password, null, null, null, true); success = true; } else { isDuplicate = true; } } catch (Exception e) { if (log.isDebugEnabled()) { log.debug(e); } lastError = e.getMessage(); fail = true; } } } if (fail && success) { throw new UserAdminException("Error occurs while importing user names. " + "Some user names were successfully imported. Some were not. Last error was : " + lastError); } if (fail && !success) { throw new UserAdminException("Error occurs while importing user names. " + "All user names were not imported. Last error was : " + lastError); } if (isDuplicate) { throw new UserAdminException("Detected duplicate user names. " + "Failed to import duplicate users. Non-duplicate user names were successfully imported."); } } catch (UserAdminException e) { throw e; } }
From source file:org.wso2.carbon.user.mgt.bulkImport.JsonConverterTest.java
License:Open Source License
@Test(description = "Test the conversion of XLS to JSON.") public void testConvertXLSToJSON() throws IOException { InputStream inputStream = getInputStreamForFile(XLS_FILENAME); Workbook testWorkBook = new HSSFWorkbook(inputStream); Sheet sheet = testWorkBook.getSheet(testWorkBook.getSheetName(0)); String usersJson = jsonConverter.xlsToJSON(sheet); Assert.assertEquals(usersJson, USERS_LIST_JSON); }
From source file:org.wso2.samples.RegistryResourceImporter.java
License:Open Source License
private static void addAssets(Registry registry, Workbook[] workbooks, String artifactType, Properties artifactMappings) throws Exception { for (Workbook workbook : workbooks) { Sheet sheet = workbook.getSheet(workbook.getSheetName(0)); if (sheet == null || sheet.getLastRowNum() == -1) { throw new RuntimeException("The first sheet is empty"); } else {/*from ww w .ja va 2s . c o m*/ System.out.println("Adding data in Sheet : " + sheet.getSheetName()); } int limit = sheet.getLastRowNum(); if (limit < 1) { throw new RuntimeException("Column headers were not specified in Asset Data Spreadsheet"); } else { System.out.println("Total number of rows in the sheet : " + limit); } Row row = sheet.getRow(0); // We use a linked list to keep the order List<String> headersAttributeNames = new LinkedList<String>(); String value; int count = 0; Set artifactAttributes = artifactMappings.keySet(); while ((value = getCellValue(row.getCell(count++), null)) != null) { headersAttributeNames.add(getMappingName(artifactMappings, value)); } Registry governanceRegistry = GovernanceUtils.getGovernanceUserRegistry(registry, USERNAME); addAssetValues(governanceRegistry, sheet, limit, artifactType, headersAttributeNames, artifactAttributes); } }
From source file:packtest.IterateCells.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb = new XSSFWorkbook(new FileInputStream(args[0])); for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet sheet = wb.getSheetAt(i);/*from w w w . j a va2 s. c om*/ System.out.println(wb.getSheetName(i)); for (Row row : sheet) { System.out.println("rownum: " + row.getRowNum()); for (Cell cell : row) { System.out.println(cell.toString()); } } } }
From source file:ro.dabuno.office.integration.Xlsx2Word.java
public static void main(String[] args) throws Exception { log.info("starting app"); // Workbook wb = new XSSFWorkbook(new FileInputStream(args[0])); Workbook wb = new XSSFWorkbook(new FileInputStream("office-files/Input.xlsx")); DataFormatter formatter = new DataFormatter(); for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet sheet = wb.getSheetAt(i);/*from ww w . jav a2s . c o m*/ System.out.println(wb.getSheetName(i)); int j = 4; for (Row row : sheet) { System.out.println("rownum: " + row.getRowNum()); for (Cell cell : row) { CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex()); System.out.print(cellRef.formatAsString()); System.out.print(" - "); // get the text that appears in the cell by getting the cell value and applying any data formats (Date, 0.00, 1.23e9, $1.23, etc) String text = formatter.formatCellValue(cell); System.out.println(text); System.out.println("------------"); // Alternatively, get the value and format it yourself switch (cell.getCellTypeEnum()) { case STRING: System.out.println(cell.getRichStringCellValue().getString()); break; case NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { System.out.println(cell.getDateCellValue()); } else { System.out.print(cellRef.formatAsString()); System.out.print(" - "); System.out.println((long) cell.getNumericCellValue()); } break; case BOOLEAN: System.out.println(cell.getBooleanCellValue()); break; case FORMULA: System.out.println(cell.getCellFormula()); break; case BLANK: System.out.println(); break; default: System.out.println(); } } j--; if (j == 0) { break; } } } XWPFDocument doc = new XWPFDocument(); XWPFParagraph p0 = doc.createParagraph(); XWPFRun r0 = p0.createRun(); r0.setBold(false); r0.setText("Domnule"); XWPFRun r00 = p0.createRun(); r00.setBold(true); r00.setText(" Ionescu Ion"); FileOutputStream out = new FileOutputStream("out/xlsx2word.docx"); doc.write(out); out.close(); }
From source file:se.nrm.dina.inventory.client.fileupload.ExcelFileHandler.java
public boolean validateSheets(Workbook workbook) { if (!workbook.getSheetName(0).equals("Metadata")) { throw new InvalidExcelFileException(CommonMessages.getInstance().MISSING_METADAT_SHEET); }/*from w w w.j a v a2 s .c o m*/ if (!workbook.getSheetName(1).equals("Observation")) { throw new InvalidExcelFileException(CommonMessages.getInstance().MISSING_OBSERVATION_SHEET); } if (!workbook.getSheetName(2).equals("Trap")) { throw new InvalidExcelFileException(CommonMessages.getInstance().MISSING_TRAP_SHEET); } if (!workbook.getSheetName(3).equals("CollectingEvent")) { throw new InvalidExcelFileException(CommonMessages.getInstance().MISSING_COLLECTINGEVENT_SHEET); } if (!workbook.getSheetName(4).equals("TaxonList")) { throw new InvalidExcelFileException(CommonMessages.getInstance().MISSING_TAXONLIST_SHEET); } return true; }
From source file:sqlitemanager.Excel2Dataset.java
public static List<DataTable> readExcel(String inPath, boolean hasIntColumns, int colsHasInt) { List<DataTable> out = new ArrayList(); try {// w w w .j ava2s . c o m // Create a work book reference Workbook workbook = null; if (inPath.endsWith(".xls")) { workbook = new HSSFWorkbook(new FileInputStream(inPath)); } else if (inPath.endsWith(".xlsx")) { workbook = new XSSFWorkbook(new FileInputStream(inPath)); } else { System.err.println("No XLS or XLSX file found!"); return out; } //Create a count of the sheets in the file short sheetsCount = (short) workbook.getNumberOfSheets(); //create a reference of sheet, cell, first head, last head, head name, //sheet name, row count and row content Sheet sheet; Row row; Cell cell; int firstIndex = Integer.MIN_VALUE; int lastIndex = Integer.MAX_VALUE; String[] headName; fieldType[] fieldTypes; String sheetName; int rowCount; Object cellValue; for (int i = 0; i < sheetsCount; i++) { sheetName = workbook.getSheetName(i); try { sheet = workbook.getSheetAt(i); rowCount = sheet.getLastRowNum() + 1; if (rowCount < 1) { break; } // row = sheet.getRow(0); // for (int j = 0; j < rowCount; j++) { // row = sheet.getRow(j); // if (firstIndex < row.getFirstCellNum()) { // firstIndex = row.getFirstCellNum(); // } // if (lastIndex > row.getLastCellNum()) { // lastIndex = row.getLastCellNum(); // } // } row = sheet.getRow(0); // Head row firstIndex = row.getFirstCellNum(); lastIndex = row.getLastCellNum(); headName = new String[lastIndex]; fieldTypes = new fieldType[lastIndex]; List<String> names = new ArrayList(); for (int index = firstIndex; index < lastIndex; index++) { String name = row.getCell(index).toString(); if (names.contains(name)) { JOptionPane.showMessageDialog(null, String.format("Field \"%s\" duplicated!", name), "Notice", JOptionPane.ERROR_MESSAGE); return null; } else { names.add(name); } headName[index] = name; fieldTypes[index] = fieldType.Double; } // Detect field types for (int k = 1; k < rowCount; k++) { row = sheet.getRow(k); if (row == null) { break; } for (int index = firstIndex; index < lastIndex; index++) { if (fieldTypes[index] != fieldType.String) { if (row.getCell(index) != null) { fieldTypes[index] = fieldType .getType(getCellType(row.getCell(index).getCellType())); } else { fieldTypes[index] = fieldType.String; } } } } DataTable tempTable = new DataTable(sheetName); for (int index = firstIndex; index < lastIndex; index++) { tempTable.addField(headName[index], fieldTypes[index]); } for (int k = 1; k < rowCount; k++) { row = sheet.getRow(k); if (row == null) { break; } tempTable.addRecord(); for (int index = firstIndex; index < lastIndex; index++) { cell = row.getCell(index); if (fieldTypes[index] == fieldType.Double) { try { cellValue = cell.getNumericCellValue(); } catch (Exception e) { System.err.println(String.format("Error reading Sheet: %s, Row: %d, Column: %d", cell.getSheet().getSheetName(), cell.getRowIndex(), cell.getColumnIndex())); cellValue = cell.getStringCellValue().trim(); } } else if (fieldTypes[index] == fieldType.Integer) { try { cellValue = (int) cell.getNumericCellValue(); } catch (Exception e) { System.err.println(String.format("Error reading Sheet: %s, Row: %d, Column: %d", cell.getSheet().getSheetName(), cell.getRowIndex(), cell.getColumnIndex())); cellValue = cell.getStringCellValue().trim(); } } else { if (cell == null) { cellValue = ""; } else { try { try { cellValue = cell.getNumericCellValue(); } catch (Exception e) { cellValue = cell.getStringCellValue().trim(); } } catch (Exception e) { System.err.println( String.format("Error reading Sheet: %s, Row: %d, Column: %d", cell.getSheet().getSheetName(), cell.getRowIndex(), cell.getColumnIndex())); cellValue = cell.getNumericCellValue(); } } } tempTable.getField(index).set(tempTable.getRecordCount() - 1, cellValue); } } if (hasIntColumns) { DataTable table = new DataTable(tempTable.getName()); List<Integer> updateFields = new ArrayList(); if (colsHasInt < 1) { // 0 or negative means check all columns colsHasInt = tempTable.getRecordCount(); } int cols4Check = Math.min(colsHasInt, tempTable.getRecordCount()); for (int j = 0; j < cols4Check; j++) { Field f = tempTable.getField(j); if (f.getType() != fieldType.Double) { continue; } boolean isIntColumn = true; for (int recNum = 0; recNum < tempTable.getRecordCount(); recNum++) { double value = Double.valueOf(f.get(recNum).toString()); double checkValue = Double.valueOf(String.valueOf((int) value)); if (value != checkValue) { isIntColumn = false; break; } } if (isIntColumn) { updateFields.add(j); } } for (int j = 0; j < tempTable.getFieldCount(); j++) { fieldType type = tempTable.getField(j).getType(); if (updateFields.contains(j)) { type = fieldType.Integer; } table.addField(tempTable.getField(j).getName(), type); } for (int recNum = 0; recNum < tempTable.getRecordCount(); recNum++) { table.addRecord(); for (int col = 0; col < tempTable.getFieldCount(); col++) { Object rowItem; if (updateFields.contains(col)) { Double value = (double) tempTable.getRecord(recNum).get(col); rowItem = value.intValue(); } else { rowItem = tempTable.getRecord(recNum).get(col); } table.getField(col).set(table.getRecordCount() - 1, rowItem); } } out.add(table); } else { out.add(tempTable); } } catch (Exception e) { Logger.getLogger(Excel2Dataset.class.getName()).log(Level.SEVERE, null, e); JOptionPane.showMessageDialog(null, String.format("Loading sheet %s error!", sheetName), "Notice", JOptionPane.ERROR_MESSAGE); } } } catch (Exception ex) { Logger.getLogger(Excel2Dataset.class.getName()).log(Level.SEVERE, null, ex); } return out; }
From source file:utilities.ExchangeManager.java
License:Open Source License
public ArrayList<String> getFormsFromXLSX(InputStream inputStream) throws Exception { ArrayList<String> forms = new ArrayList<String>(); Workbook wb = null; try {//from w w w. java2 s . c o m wb = new XSSFWorkbook(inputStream); int sheetCount = wb.getNumberOfSheets(); for (int i = 0; i < sheetCount; i++) { String name = wb.getSheetName(i); if (name.startsWith("d_")) { // Legacy forms remove prefix added by older results exports 30th January 2018 name = name.substring(2); } forms.add(name); } } finally { try { wb.close(); } catch (Exception e) { } } return forms; }
From source file:xlsParser.hffsimpl.SSReader.java
@Override public XLSWorkbook read(String file) throws Exception { Workbook workbook = null; if (file.endsWith(".xls")) { workbook = new HSSFWorkbook(new FileInputStream(file)); } else if (file.endsWith(".xlsx")) { workbook = new XSSFWorkbook(new FileInputStream(file)); }/*from w w w .j av a2s .com*/ if (workbook == null) return null; Sheet[] sheets = new Sheet[workbook.getNumberOfSheets()]; String[] sheetNames = new String[workbook.getNumberOfSheets()]; for (int i = 0; i < sheets.length; ++i) { sheets[i] = workbook.getSheetAt(i); sheetNames[i] = workbook.getSheetName(i); } List<XLSSheet> xlsSheets = new ArrayList(sheets.length); for (int i = 0; i < sheets.length; ++i) { xlsSheets.add(this.createSheet(sheetNames[i], sheets[i])); } return new BaseXLSWorkbook(xlsSheets); }