List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getLastRowNum
@Override
public int getLastRowNum()
From source file:lp.XLSXhandler.java
public boolean fileformat(File uploaded) { boolean f = true; try {/*w w w . j a v a 2 s .c o m*/ OPCPackage pkg = OPCPackage.open(uploaded); XSSFWorkbook wb = new XSSFWorkbook(pkg); XSSFSheet sheet1 = wb.getSheetAt(0); //I find the number of the rows in the file! (0-based) int rows = sheet1.getLastRowNum() + 1; //I find the number of columns! (1-based) int columns = sheet1.getRow(0).getLastCellNum(); /* * I will check only the data part! not the names of the DMUs */ Row row_check; Cell cell_check; for (int i = 1; i < rows; i++) { row_check = sheet1.getRow(i); for (int k = 1; k < columns; k++) { cell_check = row_check.getCell(k); /*If there is something else exept a number (0) * or excel function (2) */ int current = cell_check.getCellType(); if (current == 0 || current == 2) { } else { f = false; } } } } catch (InvalidFormatException e) { e.getMessage(); new Lp_first().cleanDir(); } catch (IOException e) { } return f; }
From source file:Model.Picture.java
private static ArrayList<String> enterUploadedData(String fileName) { ArrayList<String> errors = new ArrayList<String>(); try {/*from w w w . j a va 2 s.co m*/ FileInputStream file = new FileInputStream(Constants.TEMP_DIR + fileName); XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(0); int rowStart = sheet.getFirstRowNum(); int rowEnd = sheet.getLastRowNum() + 1; int colStart = sheet.getRow(rowStart).getFirstCellNum(); int colEnd = sheet.getRow(rowStart).getLastCellNum(); int[] indices = ExcelTools.getColumnIndices(colStart, colEnd, sheet.getRow(rowStart)); if (Tools.arrayContains(indices, -1)) { errors.add(Constants.IMPROPER_EXCEL_FORMAT); return errors; } errors.addAll(ExcelTools.readFile(indices, sheet, rowStart + 1, rowEnd)); } catch (IOException e) { e.printStackTrace(System.out); } return errors; }
From source file:mpqq.MPQQ.java
private static XSSFWorkbook procTab1(XSSFWorkbook referenceWB, XSSFWorkbook mpqqWB, int referenceFirstRow) { XSSFSheet trackerTab = referenceWB.getSheetAt(USE_FOR_TAB1); XSSFSheet tab1 = mpqqWB.getSheetAt(1); //Iterator<Row> rowIterator = trackerTab.iterator(); DataFormatter df = new DataFormatter(); //MPQQ first row int rowIdx = 11; for (int refCurRow = referenceFirstRow; refCurRow <= trackerTab.getLastRowNum(); refCurRow++) { Row row = trackerTab.getRow(refCurRow); //Check if row is visible if (!row.getZeroHeight() || (row.isFormatted() && row.getRowStyle().getHidden())) { int colIdx = 1; //Iterate trough the Columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getColumnIndex()) { case 3: Cell currentCell = checkRowCellExists(tab1, rowIdx, colIdx); currentCell.setCellValue(df.formatCellValue(row.getCell(T2PEPSICO_STOCK_CODE))); //Go to next Column colIdx++;/* w w w .j ava2 s. c o m*/ break; case 4: break; default: } } //Jump Next Row rowIdx++; } } return mpqqWB; }
From source file:nc.noumea.mairie.appock.util.StockSpreadsheetExporterImporterTest.java
License:Open Source License
@Test public void test() throws Exception { AppUser appUser = authHelper.getCurrentUser(); Service service = ajouterServiceToAppUser(appUser); List<ArticleStock> listeArticleStock = genererListeArticleStock(service); File exportFile = File.createTempFile("stock_test_export-", ".xlsx"); logger.info("Export pour inventaire : " + exportFile); // Export// w w w . ja v a 2 s . com StockSpreadsheetExporter.exportToXls(service, listeArticleStock, catalogueService, new FileOutputStream(exportFile)); XSSFWorkbook workbookExport = new XSSFWorkbook(exportFile); XSSFSheet worksheet = workbookExport.getSheet("Inventaire"); Assert.assertNotNull(worksheet); // Verif for (int i = 0; i < worksheet.getLastRowNum(); i++) { XSSFRow row = worksheet.getRow(i); int col = 0; if (i == 0) { Assert.assertEquals("Photo", row.getCell(col++).getStringCellValue()); Assert.assertEquals("Rfrence", row.getCell(col++).getStringCellValue()); Assert.assertEquals("Libell", row.getCell(col++).getStringCellValue()); Assert.assertEquals("Stock\n Appock", row.getCell(col++).getStringCellValue()); Assert.assertEquals("Stock\n rel", row.getCell(col++).getStringCellValue()); } else { col++; Assert.assertEquals("REF_" + (i - 1), row.getCell(col++).getStringCellValue()); Assert.assertEquals("ARTICLE CATALOGUE " + (i - 1), row.getCell(col++).getStringCellValue()); Assert.assertEquals(i, (long) row.getCell(col++).getNumericCellValue()); } } // Import : toutes les quantits ont t augments de 1 File importFile1 = File.createTempFile("stock_test_import1-", ".xlsx"); logger.info("Import de l'inventaire : " + importFile1); OutputStream outputStream1 = new FileOutputStream(importFile1); XSSFWorkbook workbookImport = new XSSFWorkbook(exportFile); XSSFSheet worksheetImport = workbookImport.getSheet("Inventaire"); for (int i = 1; i < worksheetImport.getLastRowNum() + 1; i++) { XSSFRow row = worksheetImport.getRow(i); if (i == 5) { // Cellule en erreur row.getCell(4).setCellValue(" 05 "); } else { row.getCell(4).setCellValue(row.getCell(3).getNumericCellValue() + 1); } } workbookImport.write(outputStream1); outputStream1.flush(); outputStream1.close(); List<String> warnings = StockSpreadsheetImporter.importFromXls(service, stockService, new FileInputStream(importFile1)); // Verif Stock stock = stockService.findOne(service.getStock().getId()); Assert.assertEquals(10, stock.getListeArticleStock().size()); Assert.assertEquals(1, warnings.size()); logger.info("Warning message : " + warnings.get(0)); for (int i = 0; i < stock.getListeArticleStock().size(); i++) { ArticleStock articleStock = stock.getListeArticleStock().get(i); int oldQunatite = i + 1; if (i == 4) { // Quantit inchange Assert.assertEquals(oldQunatite, (int) articleStock.getQuantiteStock()); } else { Assert.assertEquals(oldQunatite + 1, (int) articleStock.getQuantiteStock()); } } }
From source file:nc.noumea.mairie.appock.util.StockSpreadsheetImporter.java
License:Open Source License
public static List<String> importFromXls(Service service, StockService stockService, InputStream in) throws Exception { List<String> warnings = new ArrayList<>(); try {// w ww . j a v a2 s . com XSSFWorkbook workbook = new XSSFWorkbook(in); XSSFSheet worksheet = workbook.getSheet(NOM_ONGLET_CLASSEUR); if (worksheet == null) { throw new Exception("L'onglet '" + NOM_ONGLET_CLASSEUR + "' du classeur est introuvable"); } for (int i = 1; i < worksheet.getLastRowNum() + 1; i++) { try { traiterLigne(i, worksheet, service, stockService); } catch (ImportExcelException e) { warnings.add(e.getMessage()); } } } finally { in.close(); } return warnings; }
From source file:net.clementlevallois.gradingpics.io.Excel.java
public void writeFGradeForOneStudent(String name, String grade) throws FileNotFoundException, IOException { XSSFWorkbook workbook;//from w w w .ja va 2 s . c o m FileInputStream file = null; if (Files.exists(Paths.get("grades_2018.xlsx"))) { file = new FileInputStream(new File("grades_2018.xlsx")); workbook = new XSSFWorkbook(file); } else { workbook = new XSSFWorkbook(); } while (workbook.getNumberOfSheets() < 4) { workbook.createSheet(String.valueOf(String.valueOf(workbook.getNumberOfSheets() + 1))); } XSSFSheet sheet = workbook.getSheetAt(1); int lastRowNumber = sheet.getLastRowNum(); //Create a new row in current sheet XSSFRow row = sheet.createRow(lastRowNumber + 1); //Create a new cell in current row XSSFCell cellName = row.createCell(0); //Set value to new value cellName.setCellValue(name); XSSFCell cellGrade = row.createCell(1); cellGrade.setCellValue(grade); //close the excel file when done if (file != null) { file.close(); } FileOutputStream fos = new FileOutputStream(new File("grades_2018.xlsx")); workbook.write(fos); fos.close(); }
From source file:net.openchrom.xxd.processor.supplier.rscripting.ui.jobs.LoadXlsxExcelJob.java
License:Open Source License
public static int endOfRow(XSSFSheet sheet) { int lastRowNum = sheet.getLastRowNum(); if (lastRowNum > 0) { return (lastRowNum + 1); } else {/*from w w w . j a va2 s . c o m*/ return sheet.getPhysicalNumberOfRows() > 0 ? 1 : 0; } }
From source file:nl.architolk.ldt.processors.ExcelConverter.java
License:Open Source License
public void generateData(PipelineContext context, ContentHandler contentHandler) throws SAXException { try {// www . jav a 2 s . co m // Read binary content of Excel file ByteArrayOutputStream os = new ByteArrayOutputStream(); Base64XMLReceiver base64ContentHandler = new Base64XMLReceiver(os); readInputAsSAX(context, INPUT_DATA, base64ContentHandler); final byte[] fileContent = os.toByteArray(); final java.io.ByteArrayInputStream bais = new ByteArrayInputStream(fileContent); // Create workbook XSSFWorkbook workbook = new XSSFWorkbook(bais); DataFormatter formatter = new DataFormatter(); XSSFFormulaEvaluator evaluator = new XSSFFormulaEvaluator(workbook); contentHandler.startDocument(); contentHandler.startElement("", "workbook", "workbook", new AttributesImpl()); for (int s = 0; s < workbook.getNumberOfSheets(); s++) { XSSFSheet sheet = workbook.getSheetAt(s); AttributesImpl sheetAttr = new AttributesImpl(); sheetAttr.addAttribute("", "name", "name", "CDATA", sheet.getSheetName()); contentHandler.startElement("", "sheet", "sheet", sheetAttr); for (int r = 0; r <= sheet.getLastRowNum(); r++) { XSSFRow row = sheet.getRow(r); if (row != null) { AttributesImpl rowAttr = new AttributesImpl(); rowAttr.addAttribute("", "id", "id", "CDATA", Integer.toString(r)); contentHandler.startElement("", "row", "row", rowAttr); for (int c = 0; c < row.getLastCellNum(); c++) { XSSFCell cell = row.getCell(c); if (cell != null) { try { String cellvalue = formatter.formatCellValue(cell, evaluator); if (cellvalue != "") { AttributesImpl columnAttr = new AttributesImpl(); columnAttr.addAttribute("", "id", "id", "CDATA", Integer.toString(cell.getColumnIndex())); contentHandler.startElement("", "column", "column", columnAttr); contentHandler.characters(cellvalue.toCharArray(), 0, cellvalue.length()); contentHandler.endElement("", "column", "column"); } } catch (Exception e) { } } } contentHandler.endElement("", "row", "row"); } } contentHandler.endElement("", "sheet", "sheet"); } contentHandler.endElement("", "workbook", "workbook"); contentHandler.endDocument(); } catch (IOException e) { throw new OXFException(e); } }
From source file:Opm_Package.OpenFileName.java
public List<String> readReposNames(String file) throws Exception { int x = 0;/*w w w . j a v a 2 s.c o m*/ OpenFileName fname = new OpenFileName(); // array list to store the Repos names ArrayList<String> list = new ArrayList<String>(); //calling the file name..... XSSFWorkbook workbook = readFileName(file); // setting the sheet number... XSSFSheet spreadsheet = workbook.getSheetAt(x); String sname = workbook.getSheetName(x); Row row; Cell cell = null; for (int j = 0; j < spreadsheet.getLastRowNum() + 1; ++j) {//To loop thru the rows in a sheet row = spreadsheet.getRow(j); cell = row.getCell(0); //forks are in the eighth column... switch (cell.getCellType()) { //Checking for strings values inthe cells.. case Cell.CELL_TYPE_STRING: if (!cell.getStringCellValue().equals("")) { // adding the call value to the arraylist called forksList list.add(cell.getStringCellValue()); } //end of if statement... break; //Checking for numeric values inthe cells.. case Cell.CELL_TYPE_NUMERIC: list.add(String.valueOf(cell.getNumericCellValue())); break; //Checking for bank in the cells.. case Cell.CELL_TYPE_BLANK: break; }//end of switch statement } // end of for loop for the rows.. //returns the arraylist to the main class.... return list; }
From source file:org.apache.commons.g.QueryStorePicUI.java
/** * ??//from ww w .j a va 2 s. c om * @param path * @throws Exception */ public void queryStorePic(String filePath, String outPath) throws Exception { final MyDialog dialog = new MyDialog(mContext, " ..."); final List<String> noPics = new ArrayList<String>(); final List<String> noStores = new ArrayList<String>(); InputStream in = new FileInputStream(filePath); XSSFWorkbook xssfWorkbook = new XSSFWorkbook(in); final XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0); int rows = xssfSheet.getLastRowNum(); if (rows > 100) { rows = 100; } System.out.println(); System.out.println(" " + rows + "..."); dialog.setWorkText(" " + rows + "..."); HashMap<String, String> headers = new HashMap<String, String>(); headers.put("Content-Type", "application/json"); headers.put("Cookie", Config.JSESSIONID); headers.put("Referer", "https://homesis.homecredit.cn/hsis/index.html"); final XSSFCell cell0 = xssfSheet.getRow(0).createCell(0); cell0.setCellValue(""); System.out.println("?? | | ? | ?? | ??"); for (int i = 1; i <= rows; i++) { final XSSFRow xssfRow = xssfSheet.getRow(i); if (xssfRow == null) continue; // ?? String visitName = XssfUtil.getCellValue(xssfRow.getCell(0)); if (TextUtils.isEmpty(visitName)) continue; // ? final String storeCode = XssfUtil.getCellValue(xssfRow.getCell(3)); final String data = "{\"code\":\"" + storeCode + "\",\"name\":null,\"licenseNumber\":null,\"salesDistrictId\":null,\"offset\":0,\"limit\":15}"; // ?? final String storeName = XssfUtil.getCellValue(xssfRow.getCell(10)); final XSSFCell cell = xssfRow.createCell(0); String space2 = " "; // ??2 if (storeName.length() < 20) space2 += Util.getFormateSpace(20 - storeName.length()); ; String space = " "; // ??2 if (visitName.length() == 3) space = space.substring(2); // ????? if (i < 10) { System.out.print( " " + i + " " + visitName + space + storeCode + " " + storeName + space2); } else { System.out.print( " " + i + " " + visitName + space + storeCode + " " + storeName + space2); } dialog.setWorkText(" " + storeCode + "-" + storeName); Thread.sleep(1234); String store = null; boolean run = true; while (run) { try { // ? HttpPost seachStore = mHttpUtil .doPost("https://homesis.homecredit.cn/hsis/api/salesroom/search", headers, data); store = mHttpUtil.executeForBody(seachStore); //System.out.println(store); run = false; } catch (Exception e) { System.out.print("/"); } } if (store.contains("HTTP/1.1 500 Internal Server Error")) { System.out.println("?(?)"); break; } JSONObject json = null; try { json = JSONObject.parseObject(store); } catch (Exception e) { System.out.println(e.getMessage()); System.out.println(store); continue; } if (json == null) { System.out.println("?"); continue; } JSONArray array = json.getJSONArray("data"); if (array.size() == 0) { noStores.add(storeCode); cell.setCellValue("?"); System.out.println("?"); continue; } JSONObject obj = (JSONObject) array.get(0); // ID int id = obj.getIntValue("id"); //System.out.println(id); String storePic = null; run = true; while (run) { try { // HttpGet seachPic = mHttpUtil .doGet("https://homesis.homecredit.cn/hsis/api/document/salesroom/all/" + id, headers); storePic = mHttpUtil.executeForBody(seachPic); // System.out.println(storePic); run = false; } catch (Exception e) { System.out.print("/"); } } json = JSONObject.parseObject(storePic); array = json.getJSONArray("documents"); if (array.size() == 0) { noPics.add(storeCode); cell.setCellValue(""); System.out.println(""); continue; } boolean print = true; for (int j = 0; j < array.size(); j++) { obj = (JSONObject) array.get(j); // ID int picid = obj.getIntValue("id"); String fileName = obj.getString("fileName"); if (fileName.contains("POSRepID")) continue; // fileName = fileName.substring(0, fileName.indexOf("-"));//+fileName.substring(fileName.indexOf(".")); run = true; while (run) { try { // HttpGet down = mHttpUtil.doGet( "https://homesis.homecredit.cn/hsis/api/document/download/" + picid, headers); String result = mHttpUtil.downPic(down, Util .mkdirsIfNeed(outPath + File.separator + visitName, storeCode + "-" + fileName)); if (print) { print = false; System.out.println(fileName.substring(0, fileName.indexOf("-"))); } run = false; } catch (Exception e) { System.out.println(); e.printStackTrace(); System.out.println(); System.out.println("?/"); System.out.println(); } } } } xssfWorkbook.write(new FileOutputStream(filePath.replace(".xlsx", "_pic.xlsx"))); xssfWorkbook.close(); xssfWorkbook = null; in.close(); in = null; if (noStores.size() > 0) { System.out.println(); System.out.println(); System.out.println("??"); System.out.println( ""); for (int k = 0; k < noStores.size(); k++) { System.out.println(noStores.get(k)); } } if (noPics.size() > 0) { System.out.println(); System.out.println(); System.out.println("?"); System.out.println( ""); for (int k = 0; k < noPics.size(); k++) { System.out.println(noPics.get(k)); } } dialog.dispose(2); }