List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getFirstRowNum
@Override public int getFirstRowNum()
From source file:org.ramadda.util.XlsUtil.java
License:Apache License
/** * _more_//from ww w .j a va 2s. c o m * * @param filename _more_ * * @return _more_ */ public static String xlsxToCsv(String filename) { try { StringBuffer sb = new StringBuffer(); InputStream myxls = IOUtil.getInputStream(filename, XlsUtil.class); XSSFWorkbook wb = new XSSFWorkbook(myxls); XSSFSheet sheet = wb.getSheetAt(0); boolean seenNumber = false; for (int rowIdx = sheet.getFirstRowNum(); rowIdx <= sheet.getLastRowNum(); rowIdx++) { XSSFRow row = sheet.getRow(rowIdx); if (row == null) { continue; } short firstCol = row.getFirstCellNum(); for (short col = firstCol; col < row.getLastCellNum(); col++) { XSSFCell cell = row.getCell(col); if (cell == null) { break; } String value = cell.toString(); if (col > firstCol) { sb.append(","); } sb.append(clean(value)); } sb.append("\n"); } return sb.toString(); } catch (Exception exc) { throw new RuntimeException(exc); } }
From source file:org.talend.dataprep.qa.util.ExcelComparator.java
License:Open Source License
public static boolean compareTwoSheets(XSSFSheet sheet1, XSSFSheet sheet2) { int firstRow1 = sheet1.getFirstRowNum(); int lastRow1 = sheet1.getLastRowNum(); boolean equalSheets = true; for (int i = firstRow1; i <= lastRow1; i++) { XSSFRow row1 = sheet1.getRow(i); XSSFRow row2 = sheet2.getRow(i); if (!compareTwoRows(row1, row2)) { equalSheets = false;/*from w w w .j a v a2 s. c om*/ break; } } return equalSheets; }
From source file:Search.IDSSearchFunctionRunner.java
@Test public void searchAction() throws FileNotFoundException, IOException, InterruptedException { FileInputStream file = new FileInputStream(new File("D:\\Book13.xlsx")); workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(0); CellStyle style = workbook.createCellStyle(); Cell cell;//from w w w. j a va2 s . co m Row row; navigateToLogin(); LoginAction loginAction = new LoginAction(driver); loginAction.enterUserName("maxval"); loginAction.enterPassword("Qcom2015*"); TermsAndCondition termsAndConditions = loginAction.loginSubmit(); Dashboard dashboard = termsAndConditions.Accept(); System.out.println("abcd"); System.out.println("abcde"); int rowStart = Math.min(15, sheet.getFirstRowNum()); int rowEnd = Math.max(1400, sheet.getLastRowNum()); for (int rowNum = rowStart + 1; rowNum < rowEnd; rowNum++) { row = sheet.getRow(rowNum); if (row != null) { int columnNumber = 0; cell = row.getCell(columnNumber, Row.RETURN_BLANK_AS_NULL); try { if (cell != null) { cell.setCellType(Cell.CELL_TYPE_STRING); RichTextString fullTextQuery = cell.getRichStringCellValue(); String myQuery = fullTextQuery.toString(); System.out.println(myQuery); SearchList searchList = new SearchList(driver); if (rowNum == 1) { dashboard.enterFullTextSearchQuery(myQuery); searchList = dashboard.submitFullTextSearchQueryFromDashboard(); } else { searchList.enterFullTextSearchQuery(myQuery); searchList.submitFullTextSearchQueryFromSearchList(); } String searchRecordList = searchList.getRecordIdInSearchList(); cell = row.createCell(2); cell.setCellType(Cell.CELL_TYPE_STRING); style.setWrapText(true); cell.setCellStyle(style); cell.setCellValue(searchRecordList); cell = row.getCell(1); String expectedResult = cell.getStringCellValue(); if (expectedResult.equals(searchRecordList)) { cell = row.createCell(3); cell.setCellValue("Seach result matched with Expected"); } else { cell = row.createCell(3); cell.setCellValue("Seach result NOT matched with Expected"); } try (FileOutputStream fileOut = new FileOutputStream("D:\\result.xlsx")) { workbook.write(fileOut); } } } catch (AssertionError Ae) { } } } }
From source file:steffen.haertlein.file.FileObject.java
License:Apache License
private void readExcelDocument() { try {/*from w ww .jav a2s . c om*/ FileInputStream fs = new FileInputStream(f); XSSFWorkbook wb = new XSSFWorkbook(fs); XSSFSheet sh; String text = ""; for (int i = 0; i < wb.getNumberOfSheets(); i++) { sh = wb.getSheetAt(i); for (int j = sh.getFirstRowNum(); j <= sh.getLastRowNum(); j++) { XSSFRow currRow = sh.getRow(j); if (currRow == null || currRow.getFirstCellNum() == -1) { continue; } else { for (int k = currRow.getFirstCellNum(); k < currRow.getLastCellNum(); k++) { if (currRow.getCell(k, Row.RETURN_BLANK_AS_NULL) == null) { continue; } else { text += currRow.getCell(k) + "; "; } } text += System.lineSeparator(); } } } fs.close(); wb.close(); String[] xlsxLines = text.split(System.lineSeparator()); for (String line : xlsxLines) { lines.add(line); } } catch (IOException e) { JOptionPane.showMessageDialog(null, "Fehler in readExcelDocument", "Fehler", JOptionPane.ERROR_MESSAGE); e.printStackTrace(); } }
From source file:tubessc.Dataset.java
public void addDataSetTrainingExcel(String InputFile, int numOfInput) throws IOException { FileInputStream file = new FileInputStream(new File(InputFile)); XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(0); int rowStart = sheet.getFirstRowNum(); int rowEnd = sheet.getLastRowNum(); for (int i = rowStart; i <= rowEnd; i++) { double price[] = new double[numOfInput]; double target = 0; if ((i + numOfInput) <= rowEnd) { for (int j = 0; j <= numOfInput; j++) { Row row = sheet.getRow(i + j); if (j != numOfInput) { Cell cell = row.getCell(0); price[j] = Double.parseDouble(String.valueOf(cell.getNumericCellValue())); } else { Cell cell = row.getCell(0); target = Double.parseDouble(String.valueOf(cell.getNumericCellValue())); }/* ww w . java2s. c om*/ } GoldPrice gp = new GoldPrice(price, target); dataSetTraining.add(gp); } } file.close(); }
From source file:tubessc.Dataset.java
public void addDataSetTestingExcel(String InputFile, int numOfInput) throws IOException { FileInputStream file = new FileInputStream(new File(InputFile)); XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(0); int rowStart = sheet.getFirstRowNum(); int rowEnd = sheet.getLastRowNum(); for (int i = rowStart; i < rowEnd; i++) { double price[] = new double[numOfInput]; double target = 0; if ((i + numOfInput) <= rowEnd) { for (int j = 0; j <= numOfInput; j++) { Row row = sheet.getRow(i + j); if (j != numOfInput) { Cell cell = row.getCell(0); price[j] = Double.parseDouble(String.valueOf(cell.getNumericCellValue())); } else { Cell cell = row.getCell(0); target = Double.parseDouble(String.valueOf(cell.getNumericCellValue())); }/* w ww. ja v a 2 s . c om*/ } GoldPrice gp = new GoldPrice(price, target); dataSetTesting.add(gp); } } file.close(); }
From source file:tubessc.Dataset.java
public void calculateFluctuation(String InputFile, String OutputFile) throws FileNotFoundException, IOException { FileInputStream file = new FileInputStream(new File(InputFile)); XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(0); XSSFWorkbook output = new XSSFWorkbook(); XSSFSheet sheetOutput = output.createSheet("new sheet"); FileOutputStream fileOut = new FileOutputStream(OutputFile); int rowStart = sheet.getFirstRowNum(); int rowEnd = sheet.getLastRowNum(); for (int i = rowStart; i <= rowEnd - 1; i++) { Row rowIn1 = sheet.getRow(i);/*from w w w . j a v a2 s. c o m*/ Cell cellIn1 = rowIn1.getCell(0); Row rowIn2 = sheet.getRow(i + 1); Cell cellIn2 = rowIn2.getCell(0); double value1 = Double.parseDouble(String.valueOf(cellIn1.getNumericCellValue())); double value2 = Double.parseDouble(String.valueOf(cellIn2.getNumericCellValue())); Row rowOut = sheetOutput.createRow(i); Cell cellOut = rowOut.createCell(0); cellOut.setCellValue(value2 - value1); } output.write(fileOut); fileOut.close(); }
From source file:tubessc.Dataset.java
public void normalization(String InputFile, String outputFile, double minValue, double maxValue) throws FileNotFoundException, IOException { this.minValue = minValue; this.maxValue = maxValue; FileInputStream file = new FileInputStream(new File(InputFile)); XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(0); XSSFWorkbook output = new XSSFWorkbook(); XSSFSheet sheetOutput = output.createSheet("new sheet"); FileOutputStream fileOut = new FileOutputStream(outputFile); int rowStart = sheet.getFirstRowNum(); int rowEnd = sheet.getLastRowNum(); Row row = sheet.getRow(rowStart);/*ww w.j a v a2 s.com*/ Cell cell = row.getCell(0); max = Double.parseDouble(String.valueOf(cell.getNumericCellValue())); min = Double.parseDouble(String.valueOf(cell.getNumericCellValue())); for (int i = rowStart + 1; i <= rowEnd; i++) { row = sheet.getRow(i); cell = row.getCell(0); double value = Double.parseDouble(String.valueOf(cell.getNumericCellValue())); if (value > max) { max = value; } if (value < min) { min = value; } } for (int i = rowStart; i <= rowEnd; i++) { row = sheet.getRow(i); cell = row.getCell(0); double value = Double.parseDouble(String.valueOf(cell.getNumericCellValue())); double newValue = minValue + ((value - min) * (maxValue - minValue) / (max - min)); Row rowOut = sheetOutput.createRow(i); Cell cellOut = rowOut.createCell(0); cellOut.setCellValue(newValue); } output.write(fileOut); fileOut.close(); }
From source file:Utility.CSV_File_Generator.java
public static void compareTwoSheets(XSSFSheet sheet1, XSSFSheet sheet2) { int firstRow1 = sheet1.getFirstRowNum(); int lastRow1 = sheet1.getLastRowNum(); boolean equalSheets = true; int i;//from w ww . j av a 2 s. com for (i = firstRow1; i <= lastRow1; i++) { XSSFRow row1 = sheet1.getRow(i); XSSFRow row2 = sheet2.getRow(i); if (!compareTwoRows(row1, row2)) { equalSheets = false; break; } } if (!equalSheets) { write_single_row(sheet1, sheet2, i); } }