List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getLastRowNum
@Override
public int getLastRowNum()
From source file:edu.vt.vbi.patric.common.ExcelHelper.java
License:Apache License
/** * Returns the width the Column should be (XSSF version) * @param sheet - sheet of workbook// ww w .ja va2 s. co m * @param col - the column to work with * @return length (in characters) of that column */ private int decideXColumnWidth(XSSFSheet sheet, int col) { int titleLength = sheet.getRow(0).getCell(col).getStringCellValue().length(); int longestString = titleLength; for (int i = 0; i < sheet.getLastRowNum(); i++) { XSSFRow row = sheet.getRow(i); XSSFCell cell = row.getCell(col); int temp = cell.getStringCellValue().length(); if (temp > titleLength * 2) { longestString = temp; } } if (longestString > titleLength * 4) { longestString = titleLength * 4; } return longestString; }
From source file:eu.alpinweiss.filegen.command.steps.impl.ReadInputParametersStepImpl.java
License:Apache License
@Override public void execute(Model model) { String parameter = model.getParameter(INPUT_PARAMETER); model.getFieldDefinitionList().clear(); try {/*from www .j a v a 2 s . c o m*/ FileInputStream file = new FileInputStream(new File(parameter)); try (XSSFWorkbook workbook = new XSSFWorkbook(file)) { XSSFSheet sheet = workbook.getSheetAt(0); int iterationCount = readIterationCount(sheet); String lineSeparator = readLineSeparator(sheet); String outputFileName = readOutputFileName(sheet); int sheetCount = readSheetCount(sheet); outputWriterHolder .writeValueInLine("Iterations: " + iterationCount + " lineSeparator: " + lineSeparator); List<Object[]> fields = new ArrayList<>(sheet.getLastRowNum() - 4); for (int i = 5; i <= sheet.getLastRowNum(); i++) { XSSFRow row = sheet.getRow(i); Object[] fieldDefinition = new Object[row.getLastCellNum()]; for (int y = 0; y < row.getLastCellNum(); y++) { XSSFCell cell = row.getCell(y); if (cell == null) { fieldDefinition[y] = null; break; } cell.setCellType(Cell.CELL_TYPE_STRING); fieldDefinition[y] = cell.toString(); } fields.add(fieldDefinition); } file.close(); for (Object[] field : fields) { FieldDefinition fieldDefinition = new FieldDefinition(); String name = getStringName(field[0]); if (name == null || "".equals(name)) { break; } fieldDefinition.setFieldName(name); String fieldType = (String) field[1]; fieldDefinition.setType( fieldType != null ? FieldType.valueOf(fieldType.toUpperCase()) : FieldType.STRING); String fieldNeedToGenerate = (String) field[2]; fieldDefinition.setGenerate( fieldNeedToGenerate != null ? Generate.valueOf(fieldNeedToGenerate.toUpperCase()) : Generate.N); if ((field.length > 3)) { if (field[3] != null && field[3] instanceof Number) { fieldDefinition.setPattern(field[3].toString()); } else { fieldDefinition.setPattern((String) field[3]); } } model.getFieldDefinitionList().add(fieldDefinition); } model.setRowCount(iterationCount); model.setLineSeparator(lineSeparator); model.setOutputFileName(outputFileName); model.setDataStorageCount(sheetCount); outputWriterHolder.writeValueInLine(""); workbook.close(); } } catch (FileNotFoundException e) { LOGGER.error("Can't read input parameters file", e); } catch (IOException e) { LOGGER.error("Error while reading xlsx file", e); } }
From source file:features.UfmCreateXmlFilesForTestData.java
/** * Read the test data in Excel data file and convert data to HashMap and return as output. * @param locOfFile: location of test data file(Excel file with UFM test cases information). * argument is a specifier that is relative to the url argument. *///w w w . j a v a 2s .c om public static HashMap getUfmTestData(String locOfFile) { HashMap<String, String> rowData = new HashMap<String, String>(); try { DataFormatter formatter = new DataFormatter(); FileInputStream file = new FileInputStream(new File(locOfInputUfmExcelFile)); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get first/desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); int noOfRows = sheet.getLastRowNum(); //System.out.println("no of rows:" + noOfRows); int i = 0; Row rowWithColumnNames = sheet.getRow(2); int noOfColumns = rowWithColumnNames.getPhysicalNumberOfCells(); //System.out.println(noOfColumns); String testCaseName = ""; String columnNamesAndValuesOfOneRow = ""; //Iterate through each rows one by one Iterator<Row> rowIterator = sheet.iterator(); // System.out.println(rowIterator for (int m = 0; m < noOfRows; m++) { //System.out.println("Ieration number : " + m); Row rowCurrent = rowIterator.next(); if (m <= 3) { continue; } testCaseName = String.valueOf(rowCurrent.getCell(0)); // System.out.println("test case name " + testCaseName); for (int p = 0; p < 84; p++) { //Igonre the columns without any column name in test case excel file if (formatter.formatCellValue(rowWithColumnNames.getCell(p)) == "") { continue; } columnNamesAndValuesOfOneRow = columnNamesAndValuesOfOneRow + formatter.formatCellValue((rowWithColumnNames.getCell(p))).trim() + ":" + formatter.formatCellValue((rowCurrent.getCell(p))).trim() + ";"; } rowData.put(testCaseName, columnNamesAndValuesOfOneRow); columnNamesAndValuesOfOneRow = ""; } file.close(); return rowData; } catch (Exception e) { e.printStackTrace(); } return null; }
From source file:features.UfmCreateXmlFilesForTestData.java
/** * Method to read the Ufm input excel file location and the folder location where need store the xml file *//*from ww w . ja va2 s . c o m*/ public static void getUfmLocations() { HashMap<String, String> rowData = new HashMap<String, String>(); try { DataFormatter formatter = new DataFormatter(); FileInputStream file = new FileInputStream( new File(ReportLibrary.getPath() + "\\testdata\\Ufm_InputData.xlsx")); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get first/desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); int noOfRows = sheet.getLastRowNum(); Row rowWithColumnNames = sheet.getRow(1); locOfInputUfmExcelFile = sheet.getRow(1).getCell(1).toString(); locOfFolderInWhichXmlToBeStored = sheet.getRow(1).getCell(2).toString(); } catch (Exception e) { } }
From source file:FLCSS.floridaautomationtestsuite.ufm.UfmCreateXmlFilesForTestData.java
/** * Read the test data in Excel data file and convert data to HashMap and return as output. * @param locOfFile: location of test data file(Excel file with UFM test cases information). * argument is a specifier that is relative to the url argument. *//*from w w w . jav a2 s . co m*/ public static HashMap getUfmTestData(String locOfFile) { HashMap<String, String> rowData = new HashMap<String, String>(); try { DataFormatter formatter = new DataFormatter(); FileInputStream file = new FileInputStream( new File("C:\\Users\\23319\\Downloads\\FLCSS-java(5)\\a1\\src\\QATP_R0.xlsx")); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get first/desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); int noOfRows = sheet.getLastRowNum(); //System.out.println("no of rows:" + noOfRows); int i = 0; Row rowWithColumnNames = sheet.getRow(2); int noOfColumns = rowWithColumnNames.getPhysicalNumberOfCells(); //System.out.println(noOfColumns); String testCaseName = ""; String columnNamesAndValuesOfOneRow = ""; // HashMap<String,String> headerColumnNames = new HashMap<String,String>(); // //int[][] rowWithData = new int[5][]; // for (i = 0; i < 84; i++) { // // System.out.println("hello"); // headerColumnNames.put(formatter.formatCellValue((rowWithColumnNames.getCell(i))),""); // // System.out.println(headerColumnNames.size()); // } //Iterate through each rows one by one Iterator<Row> rowIterator = sheet.iterator(); // System.out.println(rowIterator for (int m = 0; m < noOfRows; m++) { //System.out.println("Ieration number : " + m); Row rowCurrent = rowIterator.next(); if (m <= 3) { continue; } testCaseName = String.valueOf(rowCurrent.getCell(0)); // System.out.println("test case name " + testCaseName); for (int p = 0; p < 84; p++) { //Igonre the columns without any column name in test case excel file if (formatter.formatCellValue(rowWithColumnNames.getCell(p)) == "") { continue; } columnNamesAndValuesOfOneRow = columnNamesAndValuesOfOneRow + formatter.formatCellValue((rowWithColumnNames.getCell(p))).trim() + ":" + formatter.formatCellValue((rowCurrent.getCell(p))).trim() + ";"; } rowData.put(testCaseName, columnNamesAndValuesOfOneRow); columnNamesAndValuesOfOneRow = ""; } file.close(); return rowData; } catch (Exception e) { e.printStackTrace(); } return null; }
From source file:foodbankyfs.FbMainFx.java
private List<String[]> saveSpreadsheetData() { // Copy spreadsheet contents into memory try {/*from w w w . j av a 2 s . com*/ // Initialize xls reading objects FileInputStream fileInputStream = new FileInputStream(spreadsheet); XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream); XSSFSheet worksheet = workbook.getSheet(FbConstants.SHEET_NAME); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); List<String[]> tmpData = new ArrayList(); // Save XSSF objects for rewrite wrksheet = worksheet; wrkbook = workbook; // Iterate through all rows in the sheet for (int rowNum = FbConstants.DATA_ROW_START_INDEX; rowNum < worksheet.getLastRowNum(); rowNum++) { // Initialize array that will store cell contents String values[] = new String[FbConstants.NUMBER_OF_COLUMNS]; XSSFRow row = worksheet.getRow(rowNum); // Iterate through cells in each row and store values to an array for (int cellNum = 0; cellNum < FbConstants.NUMBER_OF_COLUMNS; cellNum++) { XSSFCell cell = row.getCell(cellNum, Row.CREATE_NULL_AS_BLANK); String value = ""; if (cell != null) { if (cell.getCellType() == XSSFCell.CELL_TYPE_FORMULA) { evaluator.evaluateInCell(cell); } // If cell type is numeric convert the number value to a string if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { double tmpVal = cell.getNumericCellValue(); value = String.format("%.0f", tmpVal); } if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) { value = cell.getStringCellValue().toLowerCase(); } } // If a cell row has an empty ID do not include it in data if (cellNum == 0 && value.equals("")) { break; } // Initialize value to 0 if cell is empty if (value.isEmpty()) { // If value is from email or notes field then put empty instead if (cellNum == FbConstants.EMAIL_FIELD || cellNum == FbConstants.NOTES_FIELD) { value = "empty"; } else { value = "0"; } } // Store value in array values[cellNum] = value; } // Store array of values in list tmpData.add(values); } return tmpData; } catch (IOException e) { System.err.println(e); } return null; }
From source file:FormatConvert.exceloperation.Excel2csv.java
public static void copySheets2CSV(XSSFSheet sheet, String csvfile) { int maxColumnNum = 0; Map<Integer, XSSFCellStyle> styleMap = null; try {//from w ww. j av a 2 s . c o m FileWriter fw = new FileWriter(csvfile); String str = ""; for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) { XSSFRow srcRow = sheet.getRow(i); if (srcRow != null) { System.out.println(srcRow.getLastCellNum()); System.out.println(srcRow.getFirstCellNum()); // System.out.println(srcRow.getCell(srcRow.getLastCellNum()).toString()); for (int j = srcRow.getFirstCellNum(); j < srcRow.getLastCellNum(); j++) { if (srcRow.getCell(j) != null && j != srcRow.getLastCellNum() - 1) { srcRow.getCell(j).setCellType(1); str = str + srcRow.getCell(j).getReference() + ","; } else if (srcRow.getCell(j) != null) { srcRow.getCell(j).setCellType(1); str = str + srcRow.getCell(j).getStringCellValue() + "\r\n"; } // } fw.append(str); } str = ""; } fw.flush(); fw.close(); } catch (IOException ex) { } //Util.copyPictures(newSheet,sheet) ; }
From source file:gov.anl.cue.arcane.engine.matrix.MatrixModel.java
License:Open Source License
/** * Reads the dimensions for a matrix model from a spreadsheet. * This method is necessary because Excel spreadsheets * do not reliably store the row and column dimension * in the meta-information. The values that are stored * there are not guaranteed to be correct in all cases. * * @param fileName the file name * @return the matrix dimensions//from ww w . j av a2 s . c om */ public static MatrixDimensions readDimensions(String fileName) { // Create the results holder. MatrixDimensions matrixDimensions = new MatrixModel.MatrixDimensions(); // Try to read the spreadsheet. try { // Attempt to open the spreadsheet. XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(new File(fileName))); // Scan the sheets. Iterator<XSSFSheet> sheets = workbook.iterator(); // Skip the first sheet. XSSFSheet sheet = sheets.next(); // Move to the sheet for the first variable. sheet = sheets.next(); // Find the number of rows. matrixDimensions.rows = sheet.getLastRowNum(); // Prepare to check the first row. Iterator<Row> rowIterator = sheet.iterator(); // Check the header row length Row row = rowIterator.next(); matrixDimensions.columns = row.getLastCellNum() - 2; // Close the workbook. workbook.close(); // Catch errors. } catch (Exception e) { // Note an error. matrixDimensions = null; } // Return the results. return matrixDimensions; }
From source file:icalendarconverter.ExcelConverter.java
public List<ScheduleClass> readExcel() throws FileNotFoundException, IOException { ArrayList<ScheduleClass> scheduleList = new ArrayList<>(); FileInputStream fis = new FileInputStream(pathFile); XSSFWorkbook wb = new XSSFWorkbook(fis); XSSFSheet sheet = wb.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); CellRangeAddress add;/* www .ja v a 2 s .c o m*/ int colNoIdx = 0; ArrayList<String> dosen = new ArrayList<>(); ArrayList<Integer> idxDosen = new ArrayList<>(); ArrayList<Integer> colDosen = new ArrayList<>(); ArrayList<String> location = new ArrayList<>(); int idxNumber = 0; ArrayList<Integer> locationIdx = new ArrayList<>(); outerloop: for (int j = 0; j < sheet.getLastRowNum(); j++) { row = sheet.getRow(j); for (int f = 0; f < row.getLastCellNum(); f++) { Cell cell = row.getCell(f); if (cell.getCellType() == Cell.CELL_TYPE_STRING && cell.getStringCellValue().equalsIgnoreCase("No.")) { rowNoIdx = j; colNoIdx = cell.getColumnIndex(); } else if (cell.getCellType() == Cell.CELL_TYPE_STRING && cell.getStringCellValue().equalsIgnoreCase("Nama Mata Kuliah")) { colMatkulIdx = cell.getColumnIndex(); break outerloop; } } } //System.out.println("col matkul = "+colMatkulIdx); System.out.println("sheet = " + sheet.getLastRowNum()); outerloop2: for (int i = 0; i < sheet.getLastRowNum(); i++) { outerloop: for (int j = 0; j < row.getLastCellNum(); j++) { row = sheet.getRow(i); if (row == null) { i = sheet.getLastRowNum(); break outerloop2; } Cell cell = row.getCell(j); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); if (cell.getColumnIndex() == colNoIdx && i > rowNoIdx + 3 && cell.getCellType() != Cell.CELL_TYPE_BLANK && evaluator.evaluate(cell).getCellType() != Cell.CELL_TYPE_NUMERIC) { i = sheet.getLastRowNum(); break outerloop2; } if (cell.getColumnIndex() == colNoIdx && i > rowNoIdx + 3 && cell.getCellType() == Cell.CELL_TYPE_BLANK) { i = i + 1; break outerloop; } if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == (colNoIdx + 1)) { if (cell.getCellType() == Cell.CELL_TYPE_BLANK) { i = i + 1; break outerloop; } String delims = "[,. ]"; String[] sumary = cell.getStringCellValue().split(delims); for (int l = 0; l < sumary.length; l++) { if (sumary[l].equalsIgnoreCase("Mrt")) { sumary[l] = "3"; } if (sumary[l].equalsIgnoreCase("Okt")) { sumary[l] = "10"; } if (sumary[l].equalsIgnoreCase("`16")) { sumary[l] = "2016"; } } lc = LocalDate.of(Integer.parseInt(sumary[5]), Integer.parseInt(sumary[3]), Integer.parseInt(sumary[2])); // System.out.println("LC = "+lc); // sp = new SimpleDateFormat("EEEE, MMMM d, yyyy"); // String b = sumary[3] + "/" + sumary[2] + "/" + sumary[5]; // date = new Date(b); //System.out.println(sp.format(date)); } if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == (colNoIdx + 2)) { if (cell.getStringCellValue().equalsIgnoreCase("LIBUR")) { i = i + 1; break outerloop; } else { if (cell.getStringCellValue().equalsIgnoreCase("Shift 1") || cell.getStringCellValue().equalsIgnoreCase("Shift 2")) { CellReference cr = new CellReference(cell.getRowIndex() + 1, cell.getColumnIndex()); Row row2 = sheet.getRow(cr.getRow()); Cell c = row2.getCell(cr.getCol()); String delimsJam = "[-]"; String[] arrJam = c.getStringCellValue().split(delimsJam); for (int k = 0; k < arrJam.length; k++) { arrJam[k] = arrJam[k].replace('.', ':'); } // indoFormatter = DateTimeFormatter // .ofLocalizedTime(FormatStyle.SHORT) // .withLocale(Locale.getDefault()); //System.out.println("I3 = " + i); lt = LocalTime.parse(arrJam[0]); //System.out.println(lt+"-"+lt.plusHours(2)); } else { String delimsJam = "[-]"; String[] arrJam = cell.getStringCellValue().split(delimsJam); for (int k = 0; k < arrJam.length; k++) { arrJam[k] = arrJam[k].replace('.', ':'); } // indoFormatter = DateTimeFormatter // .ofLocalizedTime(FormatStyle.SHORT) // .withLocale(Locale.getDefault()); //System.out.println("I3 = " + i); lt = LocalTime.parse(arrJam[0]); //System.out.println(lt+"-"+lt.plusHours(2)); } } } if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == colMatkulIdx) { subject = cell.getStringCellValue(); //System.out.println("Subject = "+subject); } if (cell.getRowIndex() > rowNoIdx && cell.getColumnIndex() >= colMatkulIdx + 1 && cell.getColumnIndex() < row.getLastCellNum()) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { // location.add(String.valueOf((int)cell.getNumericCellValue())); // locationIdx.add(cell.getColumnIndex()); } if (cell.getCellType() == Cell.CELL_TYPE_STRING) { if (cell.getStringCellValue().contains(":")) { String[] splt = cell.getStringCellValue().split(":"); String[] splt2 = splt[1].split(","); for (int l = 0; l < splt2.length; l++) { dosen.add(splt2[l].trim()); location.add("Lab"); //System.out.println(splt2[l] + "= lab"); } } else { CellReference cr = new CellReference(1, cell.getColumnIndex()); Row row2 = sheet.getRow(cr.getRow()); Cell c = row2.getCell(cr.getCol()); if (!cell.getStringCellValue().isEmpty()) { dosen.add(cell.getStringCellValue().trim()); location.add(String.valueOf((int) c.getNumericCellValue()).trim()); } //System.out.print(cell.getStringCellValue() + " Ruang =" + (int) c.getNumericCellValue() + " "); } } if (cell.getCellType() == Cell.CELL_TYPE_BLANK && cell.getRowIndex() > 2) { CellReference cr = new CellReference(cell.getRowIndex() - 1, cell.getColumnIndex()); Row row2 = sheet.getRow(cr.getRow()); Cell c = row2.getCell(cr.getCol()); CellReference cr2 = new CellReference(1, cell.getColumnIndex()); Row row3 = sheet.getRow(cr2.getRow()); Cell c2 = row3.getCell(cr2.getCol()); if (c.getStringCellValue().contains(":")) { String[] splt = c.getStringCellValue().split(":"); String[] splt2 = splt[1].split(","); for (int l = 0; l < splt2.length; l++) { dosen.add("".trim()); location.add(""); //System.out.println(splt2[l] + "= lab"); } } else { if (!c.getStringCellValue().isEmpty()) { dosen.add(""); location.add(""); } //System.out.print(c.getStringCellValue() + " Ruang = " + (int) c2.getNumericCellValue() + " "); } } // scheduleList.add(new ScheduleClass(lc, lt, lt, subject, dosen.get(j), location.get(j))); } // System.out.println("lc = "+lc+",lt = "+lt+",subject = "+subject+",dosen = "+dosen.get(i)+",location = "+location.get(i)); // scheduleList.add(new ScheduleClass(lc, lt, lt, subject, dosen.get(j), location.get(j))); } for (int j = 0; j < dosen.size(); j++) { //System.out.println("lc = "+lc+",lt = "+lt+",subject = "+subject+",dosen = "+dosen.get(j)+",location = "+location.get(j)); scheduleList .add(new ScheduleClass(lc, lt, lt.plusHours(2), subject, dosen.get(j), location.get(j))); } dosen.clear(); location.clear(); } return mergeringList(scheduleList); }
From source file:ik1004labb5.DAOHundExcel.java
@Override public void add(DTOHund dtoHund) { XSSFWorkbook workbook = getExcelWorkbook(); XSSFSheet worksheet = workbook.getSheetAt(0); //G ner i hierarkun frn workbook, till sheet row osv. XSSFRow row = worksheet.createRow(worksheet.getLastRowNum() + 1); XSSFCell id = row.createCell(0); //skapa celler fr varje "instans", namn, ras osv. XSSFCell namn = row.createCell(1);/*from w ww . j a va 2 s . c om*/ XSSFCell ras = row.createCell(2); XSSFCell bildURL = row.createCell(3); //XSSFCell iHundgrd = row.createCell(4); id.setCellValue(Integer.toString(dtoHund.getId())); namn.setCellValue(dtoHund.getNamn()); ras.setCellValue(dtoHund.getRas()); bildURL.setCellValue(dtoHund.getBildURL()); //iHundgrd.setCellValue(dtoHund.isiHundgrd().get()); //worksheet.createRow(worksheet.getLastRowNum() + 1); FRBANNELSENS RAD! Visa Elin. saveToExcel(workbook); }