List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getLastRowNum
@Override
public int getLastRowNum()
From source file:org.xframium.page.data.provider.ExcelPageDataProvider.java
License:Open Source License
/** * Read elements./* w ww .j av a2 s .co m*/ * * @param inputStream the input stream */ private void readElements(InputStream inputStream) { XSSFWorkbook workbook = null; try { workbook = new XSSFWorkbook(inputStream); String[] tabs = tabNames.split(","); for (String tabName : tabs) { XSSFSheet sheet = workbook.getSheet(tabName); if (sheet == null) continue; addRecordType(tabName, false); XSSFRow firstRow = sheet.getRow(0); for (int i = 1; i <= sheet.getLastRowNum(); i++) { XSSFRow currentRow = sheet.getRow(i); if (getCellValue(currentRow.getCell(0)) == null || getCellValue(currentRow.getCell(0)).isEmpty()) break; DefaultPageData currentRecord = new DefaultPageData(tabName, tabName + "-" + i, true); for (int x = 0; x < firstRow.getLastCellNum(); x++) { String currentName = getCellValue(firstRow.getCell(x)); String currentValue = getCellValue(currentRow.getCell(x)); if (currentValue == null) currentValue = ""; if (currentValue.startsWith(PageData.TREE_MARKER) && currentValue.endsWith(PageData.TREE_MARKER)) { // // This is a reference to another page data table // currentRecord.addPageData(currentName); currentRecord.addValue(currentName + PageData.DEF, currentValue); currentRecord.setContainsChildren(true); } else currentRecord.addValue(currentName, currentValue); } addRecord(currentRecord); } } } catch (Exception e) { log.fatal("Error reading Excel Element File", e); } finally { try { workbook.close(); } catch (Exception e) { } } }
From source file:org.xframium.page.element.provider.ExcelElementProvider.java
License:Open Source License
/** * Read elements.//from w ww.j a va 2s. c o m * * @param inputStream the input stream */ private void readElements(InputStream inputStream) { XSSFWorkbook workbook = null; try { workbook = new XSSFWorkbook(inputStream); boolean elementsRead = true; String[] tabs = tabNames.split(","); for (String tabName : tabs) { XSSFSheet sheet = workbook.getSheet(tabName); if (sheet == null) continue; for (int i = 1; i <= sheet.getLastRowNum(); i++) { System.out.println(i); XSSFRow currentRow = sheet.getRow(i); if (getCellValue(currentRow.getCell(0)) == null || getCellValue(currentRow.getCell(0)).isEmpty()) break; ElementDescriptor elementDescriptor = new ElementDescriptor(tabName, getCellValue(currentRow.getCell(0)), getCellValue(currentRow.getCell(1))); String contextName = null; if (getCellValue(currentRow.getCell(4)) != null && !getCellValue(currentRow.getCell(4)).isEmpty()) { contextName = getCellValue(currentRow.getCell(4)); } Element currentElement = ElementFactory.instance().createElement( BY.valueOf(getCellValue(currentRow.getCell(2))), getCellValue(currentRow.getCell(3)).replace("$$", ","), getCellValue(currentRow.getCell(1)), getCellValue(currentRow.getCell(0)), contextName); if (log.isDebugEnabled()) log.debug("Adding Excel Element using [" + elementDescriptor.toString() + "] as [" + currentElement); elementsRead = elementsRead & validateElement(elementDescriptor, currentElement); elementMap.put(elementDescriptor.toString(), currentElement); } } setInitialized(elementsRead); } catch (Exception e) { log.fatal("Error reading Excel Element File", e); } finally { try { workbook.close(); } catch (Exception e) { } } }
From source file:org.xframium.page.keyWord.provider.ExcelKeyWordProvider.java
License:Open Source License
private void readElements(InputStream inputStream) { List<MatrixTest> testList = new ArrayList<MatrixTest>(10); XSSFWorkbook workbook = null;// www. j ava 2 s . c o m try { workbook = new XSSFWorkbook(inputStream); XSSFSheet sheet = workbook.getSheet("Model"); // // Extract the Tests // for (int i = 1; i <= sheet.getLastRowNum(); i++) { XSSFRow currentRow = sheet.getRow(i); String pageName = getCellValue(currentRow.getCell(0)); if (pageName.toLowerCase().equals("name")) continue; String className = getCellValue(currentRow.getCell(1)); try { Class useClass = KeyWordPage.class; if (className != null && !className.isEmpty()) useClass = (Class<Page>) Class.forName(className); if (log.isDebugEnabled()) log.debug("Creating page as " + useClass.getSimpleName() + " for " + pageName); KeyWordDriver.instance().addPage(pageName, useClass); } catch (Exception e) { log.error("Error creating instance of [" + className + "]"); } } sheet = workbook.getSheet("Tests"); // // Extract the Tests // for (int i = 1; i <= sheet.getLastRowNum(); i++) { XSSFRow currentRow = sheet.getRow(i); List<String> testDefinition = new ArrayList<String>(10); for (int j = 0; j < currentRow.getLastCellNum(); j++) testDefinition.add(getCellValue(currentRow.getCell(j))); MatrixTest currentTest = new MatrixTest(testDefinition.toArray(new String[0])); if (currentTest.getName() != null && !currentTest.getName().isEmpty() && currentTest.isActive()) testList.add(currentTest); } for (MatrixTest currentTest : testList) { List<String[]> stepList = new ArrayList<String[]>(20); sheet = workbook.getSheet(currentTest.getName()); if (sheet != null) { for (int i = 1; i <= sheet.getLastRowNum(); i++) { XSSFRow currentRow = sheet.getRow(i); List<String> stepDefinition = new ArrayList<String>(10); for (int j = 0; j < currentRow.getLastCellNum(); j++) stepDefinition.add(getCellValue(currentRow.getCell(j))); stepList.add(stepDefinition.toArray(new String[0])); } } currentTest.setStepDefinition((String[][]) stepList.toArray(new String[0][0])); } for (MatrixTest currentTest : testList) { if (currentTest.getType().equals("function")) KeyWordDriver.instance().addFunction(currentTest.createTest()); else KeyWordDriver.instance().addTest(currentTest.createTest()); } } catch (Exception e) { log.fatal("Error reading Excel Element File", e); } finally { try { workbook.close(); } catch (Exception e) { } } }
From source file:parser.CloudDSFParser.java
License:Apache License
/** * Retrieves the knowledge base of the CloudDSF from the knowledge base sheet and the relations. * //from w w w . ja v a 2 s. c o m * @return cdsf object */ public CloudDSF readExcel() { // Get desired sheet from the workbook XSSFSheet sheet = workbook.getSheet("Knowledge Base"); // setup variables String decisionName = ""; String decisionPointName = ""; DecisionPoint decisionPoint; Decision decision; Outcome outcome; int decisionPointId = 0; int decisionId = 0; int outcomeId = 0; // iterate over all rows skipping headline for (int j = 1; j <= sheet.getLastRowNum(); j++) { Row row = sheet.getRow(j); // select cell A Cell cell = row.getCell(dpCol); // if not empty than new decision Point if (cell.getStringCellValue().equals("") == false) { // calculate Ids decisionPointId++; decisionId = decisionPointId * 100 + 1; outcomeId = decisionId * 100 + 1; // create new DecisionPoint decisionPointName = cell.getStringCellValue(); decisionPoint = new DecisionPoint(decisionPointName, decisionPointId, row.getCell(dpClassCol).getStringCellValue()); // create new Decision Cell decisionCell = row.getCell(decCol); decisionName = decisionCell.getStringCellValue(); decision = new Decision(decisionName, row.getCell(decClassCol).getStringCellValue(), decisionId, decisionPointId); // create new outcome Cell outcomeCell = row.getCell(outCol); outcome = new Outcome(outcomeCell.getStringCellValue(), outcomeId, decisionId); // add outcome to decision decision.addOutcome(outcome); // add decision to decision point decisionPoint.addDecision(decision); // add decision point to cloudDSF cdsf.addDecisionPoint(decisionPoint); } else { // Select Cell B Cell decisionCell = row.getCell(decCol); // if text than new decision if (decisionCell.getStringCellValue().equals("") == false) { decisionId++; outcomeId = decisionId * 100 + 1; // create new decision decisionName = decisionCell.getStringCellValue(); decision = new Decision(decisionName, row.getCell(decClassCol).getStringCellValue(), decisionId, decisionPointId); // create new outcome Cell outcomeCell = row.getCell(outCol); outcome = new Outcome(outcomeCell.getStringCellValue(), outcomeId, decisionId); // add outcome to decision decision.addOutcome(outcome); // add decision to current decision point cdsf.getDecisionPoint(decisionPointName).addDecision(decision); } else { // if no text in dp or d than new outcome outcomeId++; // create new outcome Cell outcomeCell = row.getCell(outCol); outcome = new Outcome(outcomeCell.getStringCellValue(), outcomeId, decisionId); // add outcome to current decision in current decision point cdsf.getDecisionPoint(decisionPointName).getDecision(decisionName).addOutcome(outcome); } } } // parse the relations setInfluencingRelations(); setTasks(); setInfluencingTasks(); // sort knowledge base and relations cdsf.sortEntities(); cdsf.sortLists(); // return cdsf object return cdsf; }
From source file:parser.CloudDSFParser.java
License:Apache License
/** * Retrieve defined tasks./*from w w w . j a v a2 s. com*/ */ private void setTasks() { XSSFSheet sheet = workbook.getSheet("Task Level"); // start with fixed task id int taskId = 901; // iterate over all rows skipping headline for (int j = 2; j <= sheet.getLastRowNum(); j++) { // row 2 gets selected Row row = sheet.getRow(j); // select cell A Cell cell = row.getCell(0); // create new task Task task = new Task(taskId, cell.getStringCellValue()); taskId++; cdsf.addTask(task); } }
From source file:parser.CloudDSFPlusParser.java
License:Apache License
/** * Retrieves the knowledge base for the CloudDSFPlus from the sheet and the relations. * * @return CloudDSFPlus object// w ww.j av a 2 s . c o m */ public CloudDSF readExcel() { // Get desired sheet from the workbook XSSFSheet sheet = workbook.getSheet("Knowledge Base"); // setup variable String decisionName = ""; String decisionPointName = ""; DecisionPoint decisionPoint; Decision decision; Outcome outcome; int decisionPointId = 0; int decisionId = 0; int outcomeId = 0; // iterate over all rows skipping headline for (int j = 1; j <= sheet.getLastRowNum(); j++) { // row 2 gets selected Row row = sheet.getRow(j); // select cell A Cell cell = row.getCell(dpCol); // if not empty than new decision Point if (cell.getStringCellValue().equals("") == false) { decisionPointId++; decisionId = decisionPointId * 100 + 1; outcomeId = decisionId * 100 + 1; decisionPointName = cell.getStringCellValue(); // create new DecisionPoint decisionPoint = generateDecisionPoint(cell, decisionPointId, row); // create new Decision Cell decisionCell = row.getCell(decCol); decisionName = decisionCell.getStringCellValue(); decision = generateDecision(decisionCell, decisionId, decisionPointId, row); // create new outcome Cell outcomeCell = row.getCell(outCol); outcome = generateOutcome(outcomeCell, decisionId, decisionPointId, outcomeId, row); // add outcome to decision decision.addOutcome(outcome); // add decision to decisionPoint decisionPoint.addDecision(decision); // add decisionPoint to cloudDSFPlus cdsf.addDecisionPoint(decisionPoint); } else { // Select Cell B Cell decisionCell = row.getCell(decCol); // if text than new decision if (decisionCell.getStringCellValue().equals("") == false) { decisionId++; outcomeId = decisionId * 100 + 1; // create new decision decisionName = decisionCell.getStringCellValue(); decision = generateDecision(decisionCell, decisionId, decisionPointId, row); // create new outcome Cell outcomeCell = row.getCell(outCol); outcome = generateOutcome(outcomeCell, decisionId, decisionPointId, outcomeId, row); // add outcome to decision decision.addOutcome(outcome); // add decision to current decision point cdsf.getDecisionPoint(decisionPointName).addDecision(decision); } else { // if no text in dp or d than new outcome outcomeId++; // create new outcome Cell outcomeCell = row.getCell(outCol); outcome = generateOutcome(outcomeCell, decisionId, decisionPointId, outcomeId, row); // add outcome to current decision in current decision point cdsf.getDecisionPoint(decisionPointName).getDecision(decisionName).addOutcome(outcome); } } } // retrive relations setInfluencingRelations(); setRequiringRelations(); setInfluencingOutcomes(); // sorting cdsf.sortEntities(); cdsf.sortLists(); return cdsf; }
From source file:punchcardrecords.ui.PunchCardRecordsMainFrame.java
License:Open Source License
/** * ?excel(2007+)/*w w w .j av a2 s. c o m*/ * @param excelFile ??Excel * @param single ?? */ private Map<String, double[]> parseExcel42007(File excelFile, boolean single) { Map<String, double[]> result = new HashMap<String, double[]>(); try { // ?,?, File copyExcelFile = null; XSSFWorkbook copyWorkBook = null; if (single) {// ?? addMessage(""); copyExcelFile = new File( excelFile.getAbsolutePath().substring(0, excelFile.getAbsolutePath().lastIndexOf("\\")) + "/.xlsx"); FileUtils.copyFile(excelFile, copyExcelFile); // copyWorkBook = new XSSFWorkbook(new FileInputStream(copyExcelFile)); } // ? XSSFWorkbook workBook = new XSSFWorkbook(new FileInputStream(excelFile)); XSSFSheet sheet = workBook.getSheetAt(0); int rows = sheet.getLastRowNum(); if (rows >= 6) { // 6,??? int month = -1; // ? int year = -1;// ? if (single) {// ?? // ?3,? String dateStr = sheet.getRow(2).getCell(2).getStringCellValue(); if (StringUtils.isNotBlank(dateStr)) { addMessage("??:" + dateStr); String[] dates = dateStr.split("~"); month = Integer.parseInt(dates[0].split("\\/")[1]);// ?? year = Integer.parseInt(dates[0].split("\\/")[0]);// ?? } else { addMessage( "??,??,?"); } // ?,?? // ,?? int maxValue = (rows - 6) / 2; progressBar.setMaximum(maxValue); } int days = sheet.getRow(3).getLastCellNum(); // ? SimpleDateFormat punchFormat = new SimpleDateFormat("HH:mm"); if (single) {// ?? // ?,,,? String[] title = { "", "", "?" }; if (null != copyWorkBook) { for (int i = 0; i < title.length; i++) { copyWorkBook.getSheetAt(0).getRow(4).createCell(days + i).setCellValue(title[i]); XSSFCellStyle cellStyle = (XSSFCellStyle) copyWorkBook.getSheetAt(0).getRow(4) .getCell(0).getCellStyle().clone(); cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); copyWorkBook.getSheetAt(0).getRow(4).getCell(days + i).setCellStyle(cellStyle); copyWorkBook.getSheetAt(0).autoSizeColumn((short) (days + i)); } } } for (int i = 4; i < rows; i = i + 2) { // //,?,?+2 String userName = sheet.getRow(i).getCell(10).getStringCellValue();// ?? String userNum = sheet.getRow(i).getCell(2).getStringCellValue();// ? if (single) {// ?? addMessage("?:" + userName + "<?:" + userNum + ">"); // ?? addBar(1); } // ??,i+1 XSSFRow recordRow = sheet.getRow(i + 1); // double punchDays = 0; // (?),? double punchHours = 0, avgHours = 0; // ??? for (int j = 0; j < days; j++) {// ??? if (single) {// ?? // ?, // ?,??,?? if (month != -1 && year != -1) { // ??? if (isWeekEnd(year, month, j + 1)) { // , if (null != copyWorkBook) { XSSFCellStyle weekend = (XSSFCellStyle) copyWorkBook.getSheetAt(0) .getRow(i + 1).getCell(j).getCellStyle().clone(); weekend.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); weekend.setFillForegroundColor( new XSSFColor(new java.awt.Color(21, 225, 216))); //weekend.setFillForegroundColor(IndexedColors.YELLOW.getIndex()); copyWorkBook.getSheetAt(0).getRow(i + 1).getCell(j).setCellStyle(weekend); } } } } // ??? String record = recordRow.getCell(j).getStringCellValue();// ? if (StringUtils.isNotBlank(record)) {// ??,?? String[] records = record.split("\n"); // ???,,? if (records.length >= 2) { try { // ?start,?end,?ls,??le Date end = punchFormat.parse(records[records.length - 1]), start = punchFormat.parse(records[0]); Date ls = punchFormat.parse("11:40"), le = punchFormat.parse("13:00"); if (start.after(ls) && end.before(le)) { // ??,?? if (single) {// ?? // ?,??,?? if (null != copyWorkBook) { XSSFCellStyle excepitonStyle = (XSSFCellStyle) copyWorkBook .getSheetAt(0).getRow(i + 1).getCell(j).getCellStyle() .clone(); excepitonStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); if (month != -1 && year != -1) { // ??? if (isWeekEnd(year, month, j + 1)) { // , excepitonStyle.setFillForegroundColor( IndexedColors.PINK.getIndex()); } else { excepitonStyle.setFillForegroundColor( IndexedColors.RED.getIndex()); } } copyWorkBook.getSheetAt(0).getRow(i + 1).getCell(j) .setCellStyle(excepitonStyle); } } } else {//??? punchDays = punchDays + 1; // ? long ms = end.getTime() - start.getTime();//???? // ??,???,? long mins = 75 * 60 * 1000;//?75 // ??,??? if (start.before(ls) && end.before(le)) { // ???? mins = end.getTime() - ls.getTime(); } // ??,??? if (start.after(ls) && end.after(le)) { // ???,?:??-? if (start.before(le)) { mins = le.getTime() - start.getTime(); } else if (start.after(ls)) { // ???,?0 mins = 0; } } ms = ms - mins;// ?? punchHours = punchHours + (double) ms / (3600 * 1000); // (?) } } catch (ParseException ex) { Logger.getLogger(PunchCardRecordsMainFrame.class.getName()).log(Level.SEVERE, null, ex); } } else {// ?, if (single) {// ?? // ?,??,?? if (null != copyWorkBook) { XSSFCellStyle excepitonStyle = (XSSFCellStyle) copyWorkBook.getSheetAt(0) .getRow(i + 1).getCell(j).getCellStyle().clone(); excepitonStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); if (month != -1 && year != -1) { // ??? if (isWeekEnd(year, month, j + 1)) { // , excepitonStyle .setFillForegroundColor(IndexedColors.PINK.getIndex()); } else { excepitonStyle.setFillForegroundColor(IndexedColors.RED.getIndex()); } } copyWorkBook.getSheetAt(0).getRow(i + 1).getCell(j) .setCellStyle(excepitonStyle); } } } } } // ? if (punchDays > 0) { // ???? punchHours = new BigDecimal(punchHours).setScale(1, BigDecimal.ROUND_HALF_UP).doubleValue(); avgHours = new BigDecimal(punchHours / punchDays).setScale(1, BigDecimal.ROUND_HALF_UP) .doubleValue(); } double[] values = { punchDays, punchHours, avgHours }; result.put(userNum + ":" + userName, values); if (single) {// ?? addMessage(":" + userName + "<?:" + userNum + ">??,:" + "D:" + punchDays + ",H:" + punchHours + ",AH:" + avgHours); if (null != copyWorkBook) { for (int v = 0; v < values.length; v++) { copyWorkBook.getSheetAt(0).getRow(i + 1).createCell(days + v) .setCellValue(values[v]); XSSFCellStyle cellStyle = (XSSFCellStyle) copyWorkBook.getSheetAt(0).getRow(i + 1) .getCell(0).getCellStyle().clone(); cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); copyWorkBook.getSheetAt(0).getRow(i + 1).getCell(days + v).setCellStyle(cellStyle); } } } } if (single) {// ?? // ?? // ,? addMessage("?,??"); if (null != copyWorkBook) { FileOutputStream out = new FileOutputStream(copyExcelFile); copyWorkBook.write(out); out.close(); } // ???,?? JFileChooser fileSaveChooser = new JFileChooser(); fileSaveChooser.setDialogTitle("?"); fileSaveChooser.setSelectedFile(new File( excelFile.getAbsolutePath().substring(0, excelFile.getAbsolutePath().lastIndexOf(".")) + "-.xlsx")); String[] saveType = { "xlsx" }; fileSaveChooser.setAcceptAllFileFilterUsed(false); fileSaveChooser.setFileFilter(new FileNameExtensionFilter("*.xlsx", saveType)); int saveResult = fileSaveChooser.showSaveDialog(this); if (saveResult == JFileChooser.APPROVE_OPTION) { File saveFile = fileSaveChooser.getSelectedFile(); // ??? String saveFilePath = saveFile.getAbsolutePath(); addMessage("?,??->" + saveFilePath); FileUtils.copyFile(copyExcelFile, saveFile); Object[] options = { "", "", ",?" }; int response = JOptionPane.showOptionDialog(this, "??,???", "?", JOptionPane.YES_OPTION, JOptionPane.QUESTION_MESSAGE, null, options, options[0]); if (0 == response) {// // ?? addMessage(",??"); Desktop.getDesktop().open(saveFile); } else if (1 == response) {// addMessage(",??"); String[] cmd = new String[5]; cmd[0] = "cmd"; cmd[1] = "/c"; cmd[2] = "start"; cmd[3] = " "; cmd[4] = saveFile.getAbsolutePath().substring(0, saveFile.getAbsolutePath().lastIndexOf("\\")); Runtime.getRuntime().exec(cmd); } else { alert("??,?()"); } } else { // ??,? clearMessage(); fileName.setText(""); // ??? addMessage("??"); } // ??? if (null != copyExcelFile) { copyExcelFile.delete(); } } } else { // excel???,??????? alert("????!"); } } catch (FileNotFoundException ex) { Logger.getLogger(PunchCardRecordsMainFrame.class.getName()).log(Level.SEVERE, null, ex); alert(",??"); } catch (IOException | OfficeXmlFileException ex) { Logger.getLogger(PunchCardRecordsMainFrame.class.getName()).log(Level.SEVERE, null, ex); alert(":" + ex.getMessage()); } return result; }
From source file:resources.ministory.MinistoryFormManager.java
public Date updateUsedFormDate(List<Integer> usedMiniFormList) { FileInputStream fileInStream = null; FileOutputStream fileOutStream = null; Date curDate = new Date(); try {/*from ww w . jav a2 s .co m*/ System.out.println("Updating Used Ministory Form"); String filePath = this.filePath; fileInStream = checkFileExist(filePath); //Get the workbook instance for XLSX file XSSFWorkbook workbook = new XSSFWorkbook(fileInStream); XSSFSheet spreadsheet = workbook.getSheetAt(0); final int MY_MINIMUM_COLUMN_COUNT = 12; int rowStart = 1; int rowEnd = spreadsheet.getLastRowNum();//Math.max(1400, spreadsheet.getLastRowNum()); int writeCount = usedMiniFormList.size(); for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) { if (writeCount <= 0)// if no more thing to write { break; } Row row = spreadsheet.getRow(rowNum); if (row == null) { // This whole row is empty continue; } Cell cell = row.getCell(0, Row.RETURN_BLANK_AS_NULL); //Cell dateCell = row.getCell(11, Row.RETURN_BLANK_AS_NULL); if (cell != null) { Double numb = cell.getNumericCellValue(); Integer number = numb.intValue(); for (int containNumb : usedMiniFormList) { if (containNumb == number) { Cell dateCell = row.getCell(11, Row.RETURN_BLANK_AS_NULL); if (dateCell == null) { dateCell = row.createCell(11); } dateCell.setCellType(CELL_TYPE_NUMERIC); dateCell.setCellValue(curDate); writeCount--; } } // if(usedMiniFormList.contains(number)){// Set Date // if(dateCell == null) // dateCell = row.createCell(11); // dateCell.setCellType(CELL_TYPE_NUMERIC); // dateCell.setCellValue(curDate); // } } else {// If cell is NULL skip continue; } } fileOutStream = new FileOutputStream(filePath); workbook.write(fileOutStream); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } catch (Exception ex) { Logger.getLogger(MinistoryFormItem.class.getName()).log(Level.SEVERE, null, ex); } finally { try { fileInStream.close(); fileOutStream.close(); } catch (IOException ex) { Logger.getLogger(MinistoryFormManager.class.getName()).log(Level.SEVERE, null, ex); } return curDate; } }
From source file:resources.ministory.MinistoryFormManager.java
private List<MinistoryFormItem> loadAllFromExcel(String filePath) { System.out.println("Loading MiniStory Form Excel Database"); List<MinistoryFormItem> miniFormList = new ArrayList(); boolean cleanSucess = true; try {/*w w w . j av a 2s . co m*/ System.out.println("Reading Ministory DB"); FileInputStream fileStream = checkFileExist(filePath); //Get the workbook instance for XLSX file XSSFWorkbook workbook = new XSSFWorkbook(fileStream); XSSFSheet spreadsheet = workbook.getSheetAt(0); //XSSFRow row; final int MY_MINIMUM_COLUMN_COUNT = 12; int rowStart = 1; int rowEnd = Math.max(1400, spreadsheet.getLastRowNum()); //Startfrom row 1 for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) { Row r = spreadsheet.getRow(rowNum); if (r == null) { // This whole row is empty continue; } MinistoryFormItem miniFormTemp = new MinistoryFormItem(); int lastColumn = Math.max(r.getLastCellNum(), MY_MINIMUM_COLUMN_COUNT); for (int cellNum = 0; cellNum < lastColumn; cellNum++) { Cell cell = r.getCell(cellNum, Row.RETURN_BLANK_AS_NULL); if (cell == null) { // The spreadsheet is empty in this cell miniFormTemp.setEmptyValue(cellNum); } else { // Fill the cell's contents to MiniForm Obj miniFormTemp.setValue(cellNum, cell); } } miniFormList.add(miniFormTemp); } fileStream.close(); } catch (FileNotFoundException e) { cleanSucess = false; e.printStackTrace(); } catch (IOException e) { cleanSucess = false; e.printStackTrace(); } catch (Exception ex) { cleanSucess = false; Logger.getLogger(MinistoryFormItem.class.getName()).log(Level.SEVERE, null, ex); } finally { if (cleanSucess) { System.out.println("All Read Without Error"); } else { System.out.println("There were some Error(s)"); } } return miniFormList; }
From source file:se.nrm.dina.dina.inventory.logic.dyntaxa.DyntaxaDumpLogic.java
public void uploadSubphylum() { logger.info("uploadSubphylum - excel file path : {}", EXCEL_FILE_PATH); initBaseData();/*from w w w . j av a 2 s .c om*/ subphylumTreeDefItem = initData(RANK_SUBPHYLUM_ID); classTreeDefItem = initData(RANK_CLASS_ID); XSSFSheet sheet = getExcelSheet(); int rowEndNumber = sheet.getLastRowNum(); IntStream.range(1, rowEndNumber + 1).forEach(nbr -> { XSSFRow row = sheet.getRow(nbr); String theRank = row.getCell(1).getStringCellValue(); if (theRank.equals("Subphylum")) { uploadRootTaxon(row); } else if (theRank.equals("Class")) { smtpDao.create(createTaxon(row, subphylumTaxon, RANK_CLASS_ID, classTreeDefItem)); } }); logger.info("Number of rows : {}", rowEndNumber); }