List of usage examples for org.apache.poi.ss.usermodel Sheet getLastRowNum
int getLastRowNum();
From source file:CreateExcel.java
public static void add_column() throws FileNotFoundException, IOException { String excelFilePath = "C:\\Users\\aryan_000\\Desktop\\output.xlsx"; FileOutputStream outputStream = new FileOutputStream(new File(excelFilePath)); FileInputStream inputStream = new FileInputStream(new File(excelFilePath)); Workbook wbout = new XSSFWorkbook(); Workbook wbin = new XSSFWorkbook(inputStream); Sheet firstsheet = wbin.getSheetAt(0); XSSFSheet sheet = (XSSFSheet) wbout.createSheet("version 1"); int max_row = firstsheet.getLastRowNum(); for (int i = 0; i < max_row; i++) { Row row = sheet.createRow(i);//from ww w. j a va2s .c om for (int j = 0; j < firstsheet.getLeftCol(); j++) { // String str = firstsheet.get // Cell col = row.createCell(j).setCellValue(firstsheet.getRow(i).getCell(j).getStringCellValue()); } } }
From source file:CatalogMain.java
License:BSD License
public void createClassObject() { storeClassObjects.clear();// w w w . j a va 2 s .c o m Sheet layersSheet = workbook.getSheet("Layers"); // Adds all rows as class objects for (int rowIndex = 4; rowIndex <= layersSheet.getLastRowNum(); rowIndex++) { Row row = layersSheet.getRow(rowIndex); if ((row.getCell(1) != null && row.getCell(1).getCellType() != Cell.CELL_TYPE_BLANK) && (row.getCell(11) != null && row.getCell(11).getCellType() != Cell.CELL_TYPE_BLANK)) { classObjects = new LayersClassObject(row.getCell(0).toString(), row.getCell(1).toString(), row.getCell(2).toString(), String.valueOf(rowIndex + 1), Double.parseDouble(row.getCell(11).toString())); } else if ((row.getCell(1) != null && row.getCell(1).getCellType() != Cell.CELL_TYPE_BLANK) && (row.getCell(11) == null || row.getCell(11).getCellType() == Cell.CELL_TYPE_BLANK)) { classObjects = new LayersClassObject(row.getCell(0).toString(), row.getCell(1).toString(), row.getCell(2).toString(), String.valueOf(rowIndex + 1), 1); } else if ((row.getCell(1) == null || row.getCell(1).getCellType() == Cell.CELL_TYPE_BLANK) && (row.getCell(11) != null && row.getCell(11).getCellType() != Cell.CELL_TYPE_BLANK)) { classObjects = new LayersClassObject(row.getCell(0).toString(), null, row.getCell(2).toString(), String.valueOf(rowIndex + 1), Double.parseDouble(row.getCell(11).toString())); } else { classObjects = new LayersClassObject(row.getCell(0).toString(), null, row.getCell(2).toString(), String.valueOf(rowIndex + 1), 1); } storeClassObjects.add(classObjects); } // Sets true to objects which class has duplicates if (storeClassObjects.isEmpty() == false) { for (int count = 0; count < storeClassObjects.size(); count++) { String tempString = storeClassObjects.get(count).getClassName(); for (int count1 = count + 1; count1 < storeClassObjects.size(); count1++) { if (storeClassObjects.get(count1).getClassName().equalsIgnoreCase(tempString)) { storeClassObjects.get(count).setHaveSame(true); storeClassObjects.get(count1).setHaveSame(true); } } } } }
From source file:adams.data.io.input.ExcelSpreadSheetReader.java
License:Open Source License
/** * Reads the spreadsheet content from the specified file. * * @param in the input stream to read from * @return the spreadsheets or null in case of an error *//* w w w . jav a 2 s . c o m*/ @Override protected List<SpreadSheet> doReadRange(InputStream in) { List<SpreadSheet> result; int[] indices; Workbook workbook; Sheet sheet; SpreadSheet spsheet; Row exRow; Cell exCell; adams.data.spreadsheet.Row spRow; int i; int n; int cellType; DateFormat dformat; boolean numeric; int dataRowStart; int firstRow; int lastRow; List<String> header; result = new ArrayList<>(); workbook = null; dformat = DateUtils.getTimestampFormatter(); try { workbook = WorkbookFactory.create(in); m_SheetRange.setMax(workbook.getNumberOfSheets()); indices = m_SheetRange.getIntIndices(); firstRow = m_FirstRow - 1; dataRowStart = getNoHeader() ? firstRow : firstRow + 1; for (int index : indices) { if (m_Stopped) break; spsheet = m_SpreadSheetType.newInstance(); spsheet.setDataRowClass(m_DataRowType.getClass()); result.add(spsheet); if (isLoggingEnabled()) getLogger().info("sheet: " + (index + 1)); sheet = workbook.getSheetAt(index); if (sheet.getLastRowNum() == 0) { getLogger().severe("No rows in sheet #" + index); return null; } spsheet.setName(sheet.getSheetName()); // header if (isLoggingEnabled()) getLogger().info("header row"); exRow = sheet.getRow(firstRow); if (exRow == null) { getLogger().warning("No data in sheet #" + (index + 1) + "?"); } else if (exRow != null) { spRow = spsheet.getHeaderRow(); m_TextColumns.setMax(exRow.getLastCellNum()); if (getNoHeader()) { header = SpreadSheetUtils.createHeader(exRow.getLastCellNum(), m_CustomColumnHeaders); for (i = 0; i < header.size(); i++) spRow.addCell("" + (i + 1)).setContent(header.get(i)); } else { if (!m_CustomColumnHeaders.trim().isEmpty()) { header = SpreadSheetUtils.createHeader(exRow.getLastCellNum(), m_CustomColumnHeaders); for (i = 0; i < header.size(); i++) spRow.addCell("" + (i + 1)).setContent(header.get(i)); } else { for (i = 0; i < exRow.getLastCellNum(); i++) { if (m_Stopped) break; exCell = exRow.getCell(i); if (exCell == null) { spRow.addCell("" + (i + 1)).setMissing(); continue; } numeric = !m_TextColumns.isInRange(i); switch (exCell.getCellType()) { case Cell.CELL_TYPE_BLANK: case Cell.CELL_TYPE_ERROR: spRow.addCell("" + (i + 1)).setContent("column-" + (i + 1)); break; case Cell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(exCell)) spRow.addCell("" + (i + 1)).setContent(new DateTime( HSSFDateUtil.getJavaDate(exCell.getNumericCellValue()))); else if (numeric) spRow.addCell("" + (i + 1)).setContent(exCell.getNumericCellValue()); else spRow.addCell("" + (i + 1)).setContentAsString(numericToString(exCell)); break; default: spRow.addCell("" + (i + 1)).setContentAsString(exCell.getStringCellValue()); } } } } } // data if (spsheet.getColumnCount() > 0) { if (m_NumRows < 1) lastRow = sheet.getLastRowNum(); else lastRow = Math.min(firstRow + m_NumRows - 1, sheet.getLastRowNum()); for (i = dataRowStart; i <= lastRow; i++) { if (m_Stopped) break; if (isLoggingEnabled()) getLogger().info("data row: " + (i + 1)); spRow = spsheet.addRow("" + spsheet.getRowCount()); exRow = sheet.getRow(i); if (exRow == null) continue; for (n = 0; n < exRow.getLastCellNum(); n++) { // too few columns in header? if ((n >= spsheet.getHeaderRow().getCellCount()) && m_AutoExtendHeader) spsheet.insertColumn(spsheet.getColumnCount(), ""); m_TextColumns.setMax(spsheet.getHeaderRow().getCellCount()); exCell = exRow.getCell(n); if (exCell == null) { spRow.addCell(n).setMissing(); continue; } cellType = exCell.getCellType(); if (cellType == Cell.CELL_TYPE_FORMULA) cellType = exCell.getCachedFormulaResultType(); numeric = !m_TextColumns.isInRange(n); switch (cellType) { case Cell.CELL_TYPE_BLANK: case Cell.CELL_TYPE_ERROR: if (m_MissingValue.isEmpty()) spRow.addCell(n).setMissing(); else spRow.addCell(n).setContent(""); break; case Cell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(exCell)) spRow.addCell(n).setContent( dformat.format(HSSFDateUtil.getJavaDate(exCell.getNumericCellValue()))); else if (numeric) spRow.addCell(n).setContent(exCell.getNumericCellValue()); else spRow.addCell(n).setContentAsString(numericToString(exCell)); break; default: if (m_MissingValue.isMatch(exCell.getStringCellValue())) spRow.addCell(n).setMissing(); else spRow.addCell(n).setContentAsString(exCell.getStringCellValue()); } } } } } } catch (Exception ioe) { getLogger().log(Level.SEVERE, "Failed to read range '" + m_SheetRange + "':", ioe); result = null; m_LastError = "Failed to read range '" + m_SheetRange + "' from stream!\n" + Utils.throwableToString(ioe); } return result; }
From source file:annualleave.PersonelTara.java
static public void Detection(int ilk, int son, String URL) throws FileNotFoundException, IOException { char gun[] = new char[100]; int gunler[] = new int[32]; for (int j = ilk; j <= son; j++) { gunler[j] = j;//w w w. j av a 2 s . c o m } String oncekiAd; // C:\\Users\\talha\\Documents\\NetBeansProjects\\AnnualLeave\\src\\annualleave\\Mays 23.xlsx String excelFilePath = URL; FileInputStream inputStream = new FileInputStream(new File(excelFilePath)); Workbook workbook = new XSSFWorkbook(inputStream); Sheet firstSheet = workbook.getSheetAt(0); int sonuncuIndex = firstSheet.getLastRowNum(); Iterator<Row> iterator = firstSheet.iterator(); while (iterator.hasNext()) { Row nextRow = iterator.next(); Iterator<Cell> cellIterator = nextRow.cellIterator(); if (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getRowIndex() >= 6) { oncekiAd = ad; cell = cellIterator.next(); cell = cellIterator.next(); cell = cellIterator.next(); ad = cell.getStringCellValue(); cell = cellIterator.next(); cell = cellIterator.next(); tarih = cell.getStringCellValue(); if (ad != oncekiAd && i != 0 && !(oncekiAd.equals("Personel Ad Soyad")) && !(oncekiAd.isEmpty()) && !(ad.isEmpty()) && !(ad.equals("Personel Ad Soyad")) || cell.getRowIndex() == sonuncuIndex) { for (int j = ilk; j <= son; j++) { if (gunler[j] != 0) { GETIR[z] = oncekiAd + " " + gunler[j]; z++; test = 1; } if (isBuilt) { int left = tarih.indexOf("."); int right = tarih.lastIndexOf("."); String sub = tarih.substring(left + 1, right); ay = Integer.parseInt(sub); int left2 = tarih.lastIndexOf("."); int right2 = tarih.lastIndexOf(""); String sub2 = tarih.substring(left2 + 1, right2); yil = Integer.parseInt(sub2); } Build(); } if (test == 1) { yeniAd[c] = oncekiAd; for (int j = ilk; j <= son; j++) { if (gunler[j] != 0) { Calendar date = Calendar.getInstance(); date.set(yil, ay - 1, gunler[j]); if (date.get(Calendar.DAY_OF_WEEK) == Calendar.SATURDAY || date.get(Calendar.DAY_OF_WEEK) == Calendar.SUNDAY) { } else { yeniAd[c] += " " + gunler[j]; } } } c++; test = 0; } for (int j = ilk; j <= son; j++) { gunler[j] = j; } } if (!(cell.getStringCellValue().isEmpty()) && !(ad.equals("Personel Ad Soyad"))) { int left = tarih.indexOf(0); int right = tarih.indexOf("."); String sub = tarih.substring(left + 1, right); gunler[Integer.parseInt(sub)] = 0; i++; } } } } }
From source file:athena.Controller.java
License:Open Source License
private void convertExceltoCSV(String inputFile, String outputFilePath) { InputStream inp = null;/* ww w . j a v a 2s . co m*/ try { inp = new FileInputStream(inputFile); wb = new XSSFWorkbook(inp); for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet thisSheet = wb.getSheetAt(i); int rowEnd = Math.max(1400, thisSheet.getLastRowNum()); view.setOutput("Writting.. " + thisSheet.getSheetName()); String csvRawString = ""; String outputFileName = outputFilePath + thisSheet.getSheetName() + ".csv"; try { OutputStream os; File testFile = new File(outputFileName); if (testFile.exists() && !testFile.isDirectory()) { os = new FileOutputStream(outputFilePath + thisSheet.getSheetName() + "(1).csv"); } else { os = new FileOutputStream(outputFileName); } PrintWriter w = new PrintWriter(new OutputStreamWriter(os, "UTF-8")); for (int j = 0; j < rowEnd; j++) { Row row = thisSheet.getRow(j); if (row != null) { for (int k = 0; k < row.getLastCellNum(); k++) { if (k == (row.getLastCellNum() - 1)) { if (row.getCell(k) != null) { csvRawString = csvRawString + row.getCell(k); } } else { if (row.getCell(k) == null) { csvRawString = csvRawString + ","; } else { csvRawString = csvRawString + row.getCell(k) + ","; } } } } else { csvRawString = csvRawString + ","; } csvRawString = csvRawString + "\n"; w.print(csvRawString); w.flush(); csvRawString = ""; } w.close(); view.setOutput("Done.. " + thisSheet.getSheetName()); } catch (FileNotFoundException e) { view.setOutput("I'm confused.. File not found!"); } catch (UnsupportedEncodingException e) { view.setOutput("Call 911.. or Jake"); } } } catch (IOException e) { view.setOutput("Uh oh.. Fail to read file!"); } finally { try { inp.close(); view.setOutput("Done conversion.. " + model.getInputFilePath() + "\n"); model.setInputFilePath(null); model.setOutputFilePath(null); view.refreshIntputPath(); view.refreshOutputPath(); } catch (IOException e) { view.setOutput("Damn input stream.."); } } }
From source file:au.com.onegeek.lambda.parser.Excel2SeleniumParser.java
License:Apache License
private void parse(InputStream stream) throws CannotCompileException, NotFoundException, CannotCreateTestClassException, InstantiationException, IllegalAccessException, IllegalArgumentException, InvocationTargetException { logger.debug("Parsing..."); if (this.dataMap != null && this.tests != null) { return;//from w ww .j av a2 s . c om } this.dataMap = new ArrayList<Map<String, Object>>(); this.tests = new ArrayList<Class<Test>>(); Workbook workbook = null; try { workbook = new XSSFWorkbook(stream); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } logger.debug("workbook" + workbook.toString()); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { Sheet sheet = workbook.getSheetAt(i); if (sheet.getSheetName().startsWith("data")) { // parse key\value pairs HashMap<String, Object> map = new HashMap<String, Object>(); dataMap.add(map); boolean done = false; Row row = sheet.getRow(sheet.getFirstRowNum()); while (!done && row != null && row.getPhysicalNumberOfCells() > 0) { // TODO: parse numerics correctly (i.e. don't add decimal points if not needed) String key = (String) XslxUtil.objectFrom(workbook, row.getCell(0)); String value = null; try { value = (String) XslxUtil.objectFrom(workbook, row.getCell(1)); logger.debug("Adding variable to map: " + key + ":" + value); map.put(key, value); row = sheet.getRow(row.getRowNum() + 1); if (row == null || (row.getRowNum() == sheet.getLastRowNum() + 1)) { done = true; } } catch (NullPointerException e) { //throw new CannotCreateVariableException("No value found for variable '" + key + "' in dataset: " + sheet.getSheetName()); done = true; } } } } JavassistTestBuilderImpl builder = JavassistTestBuilderImpl.getInstance(); // Parse Test sheets into Test objects for (int s = 0; s < workbook.getNumberOfSheets(); s++) { Sheet sheet = workbook.getSheetAt(s); int i = 0; // Ignore data sheets if (sheet.getSheetName().startsWith("suite")) { int maxRows = sheet.getPhysicalNumberOfRows(); int currentRow = sheet.getFirstRowNum(); logger.debug("Nr rows in sheet: " + maxRows); // Create Test Class String testCaseName = "Test" + Excel2SeleniumParser.toCamelCase(sheet.getSheetName()); logger.debug("Creating Test class with name: " + testCaseName); builder.makeTestClass(testCaseName, this.dataMap); boolean testCaseInProgress = false; boolean dataProviderAdded = false; // Get First row, containing the test name and the data to be injected while (i < maxRows) { logger.debug("i: " + i); logger.debug("currentRow: " + currentRow); Row row = sheet.getRow(currentRow); TestCommand command = null; // Check for empty row if (row != null && row.getPhysicalNumberOfCells() != 0) { i++; // Get Cells Iterator<Cell> iterator = row.cellIterator(); while (iterator.hasNext()) { Cell cell = iterator.next(); String cellValue = (cell == null || cell.toString() == "") ? "" : XslxUtil.objectFrom(workbook, cell).toString(); logger.debug("Cell: " + cellValue); if (cellValue.startsWith("test")) { logger.debug("Test case found: " + cellValue + ". Creating Test Case"); // Create new Test CASE try { builder.addTest(cellValue); testCaseInProgress = true; dataProviderAdded = false; } catch (CannotModifyTestMethodException e) { e.printStackTrace(); throw new CannotCreateTestClassException( "Could not create Test Class as there was a variable not found in test assertion. Embedded exception: " + e.getMessage()); } catch (VariableNotFoundException e) { e.printStackTrace(); throw new CannotCreateTestClassException( "Could not create Test Class as there was a variable not found in test assertion. Embedded exception: " + e.getMessage()); } break; } else { if (command == null & !cellValue.equals("")) { logger.debug("Command found: " + cellValue + ". Creating new TestCommand"); command = new TestCommand(cellValue); } else if (!cellValue.equals("")) { logger.debug("Command argument found: " + cellValue); command.addParameter(cellValue); } } } } else { // Blank row could mean a test case has just been completed // Complete last test case by adding a data provider if (testCaseInProgress && !dataProviderAdded) { try { logger.debug("In Progress Test Case now being closed off and added to class..."); builder.addDataProvider(); dataProviderAdded = true; logger.debug("In Progress Test Case now closed off!"); } catch (CannotCreateDataProviderException e) { throw new CannotCreateTestClassException( "Could not create Test case as a DataProvider for the method could not be created. Embedded exception: " + e.getMessage()); } } } try { if (command != null) { logger.debug("Adding command to method"); builder.appendTestToLastMethod(command); } } catch (CannotModifyTestMethodException e) { throw new CannotCreateTestClassException("Unable to add Test Case '" + command.toString() + "' to Test Class. Embedded exception: " + e.getMessage()); } catch (VariableNotFoundException e) { throw new CannotCreateTestClassException("Unable to add Test Case '" + command.toString() + "' to Test Class as a variable was not found. Embedded exception: " + e.getMessage()); } currentRow++; } // Blank row could mean a test case has just been completed // Complete last test case by adding a data provider logger.debug( "End of rows...Checking if In Progress Test Case now being closed off and added to class..."); if (testCaseInProgress && !dataProviderAdded) { logger.debug(" In Progress Test Case now being closed off and added to class..."); try { builder.addDataProvider(); dataProviderAdded = true; logger.debug("In Progress Test Case now closed off!"); } catch (CannotCreateDataProviderException e) { throw new CannotCreateTestClassException( "Could not create Test case as a DataProvider for the method could not be created. Embedded exception: " + e.getMessage()); } } if (testCaseInProgress) { logger.debug("Generating class file"); try { this.tests.add(builder.getCreatedClass()); } catch (CannotModifyTestMethodException e) { e.printStackTrace(); throw new CannotCreateTestClassException( "Could not create Test case as a DataProvider for the method could not be created. Embedded exception: " + e.getMessage()); } testCaseInProgress = false; } } } try { stream.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } logger.info("Looking at our classes..."); // Look at the Test Objects for (Class<Test> clazz : tests) { logger.info("Class: " + clazz.getName()); for (Method m : clazz.getMethods()) { logger.info("Method: " + m); if (m.getName().equalsIgnoreCase("testRetailDataProvider")) { logger.info("invoking data provider"); Test test = clazz.newInstance(); Object[][] data = (Object[][]) m.invoke(test); for (Object[] obs : data) { for (Object o : obs) { logger.info("data value: " + o); } } } } } }
From source file:au.gov.ansto.bragg.quokka.experiment.util.ExperimentModelUtils.java
License:Open Source License
public static void refineExperimentFromExcel(Experiment experiment, InputStream input) throws IOException { // Sample environment is not supported at this stage if (experiment.isControlledEnvironment()) { return;//from w w w . j a v a 2 s .co m } // Clear existing acquisition entries experiment.getNormalAcquisition().getEntries().clear(); // Read from a Excel file Workbook workbook = new HSSFWorkbook(input); Sheet sheet = workbook.getSheetAt(0); // Start from row 3 for (int i = 2; i < sheet.getLastRowNum(); i++) { Row row = sheet.getRow(i); int samplePosition = Integer.parseInt(row.getCell(1).getStringCellValue()); Sample sample = experiment.getSamples().get(samplePosition); AcquisitionEntry entry = new AcquisitionEntry(sample); experiment.getNormalAcquisition().getEntries().add(entry); } }
From source file:bad.robot.excel.matchers.RowNumberMatcher.java
License:Apache License
@Override protected boolean matchesSafely(Sheet actual, Description mismatch) { if (expected.getLastRowNum() != actual.getLastRowNum()) { mismatch.appendText("got ").appendValue(numberOfRowsIn(actual)).appendText(" row(s) in sheet ") .appendValue(actual.getSheetName()).appendText(" expected ") .appendValue(numberOfRowsIn(expected)); return false; }/*from w w w . j a va 2 s .c o m*/ return true; }
From source file:bad.robot.excel.matchers.RowNumberMatcher.java
License:Apache License
private static int numberOfRowsIn(Sheet sheet) { return sheet.getLastRowNum() + 1; }
From source file:bad.robot.excel.row.CopyRow.java
License:Apache License
/** * Copies a row from a row index on the given workbook and sheet to another row index. If the destination row is * already occupied, shift all rows down to make room. * */// w w w . j ava2 s . com public static void copyRow(Workbook workbook, Sheet worksheet, RowIndex from, RowIndex to) { Row sourceRow = worksheet.getRow(from.value()); Row newRow = worksheet.getRow(to.value()); if (alreadyExists(newRow)) worksheet.shiftRows(to.value(), worksheet.getLastRowNum(), 1); else newRow = worksheet.createRow(to.value()); for (int i = 0; i < sourceRow.getLastCellNum(); i++) { Cell oldCell = sourceRow.getCell(i); Cell newCell = newRow.createCell(i); if (oldCell != null) { copyCellStyle(workbook, oldCell, newCell); copyCellComment(oldCell, newCell); copyCellHyperlink(oldCell, newCell); copyCellDataTypeAndValue(oldCell, newCell); } } copyAnyMergedRegions(worksheet, sourceRow, newRow); }