List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getLastRowNum
@Override
public int getLastRowNum()
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);//from w w w . jav a 2 s . c om 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:update2viva.ConvertXLSX.java
static void readXlsx(File inputFile, String outputfile) throws FileNotFoundException, UnsupportedEncodingException, IOException { String[] args;/* w ww . ja va 2 s. c o m*/ //File to store data in form of CSV File f = new File(outputfile + "\\out_.csv"); OutputStream os = (OutputStream) new FileOutputStream(f); String encoding = "ISO-8859-1"; OutputStreamWriter osw = new OutputStreamWriter(os, encoding); BufferedWriter bw = new BufferedWriter(osw); // Get the workbook instance for XLSX file XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(inputFile)); // Get first sheet from the workbook XSSFSheet sheet = wb.getSheetAt(0); Row row; Cell cell; String acrow = ""; for (int i = 0; i <= sheet.getLastRowNum(); i++) { row = sheet.getRow(i); for (int j = 0; j < row.getLastCellNum(); j++) { if (!(row.getCell(j) == null)) { switch (row.getCell(j).getCellType()) { case Cell.CELL_TYPE_BOOLEAN: acrow = "" + row.getCell(j).getBooleanCellValue(); break; case Cell.CELL_TYPE_NUMERIC: acrow = "" + row.getCell(j).getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: acrow = row.getCell(j).getStringCellValue(); break; case Cell.CELL_TYPE_BLANK: System.out.println(" "); break; } // acrow=row.getCell(j).getStringCellValue(); if (acrow.contains("\n")) acrow = acrow.replaceAll("\n", ""); if (!(acrow.contains("\n"))) { bw.write(acrow + ";"); } } if (row.getCell(j) == null) { bw.write(';'); } } bw.newLine(); } bw.flush(); bw.close(); inputFile.delete(); }
From source file:utilities.TableReader.java
private static String getUpdate(XSSFSheet sheet, int[] columns, ArrayList<TableMetaData> meta, ArrayList<String> errors, int col) { String update = Helper.process(meta.get(col - 1).getName()) + " = case " + Helper.process(meta.get(0).getIdentifier()); for (int i = 1; i < sheet.getLastRowNum(); i++) { Row row = sheet.getRow(i);//ww w . ja va 2 s . c o m update += " WHEN '" + getCellContents(row.getCell(columns[0])) + "' THEN '" + getCellContents(row.getCell(columns[col])) + "'"; } update += " ELSE " + Helper.process(meta.get(col - 1).getName()) + " END"; return update; }
From source file:Utility.CSV_File_Generator.java
public static void Traffic_Data() { Create_Excel_File();// w ww . j ava 2s. com File csv_file = new File(file_details("ML_CSV_File")); try { FileInputStream fis = new FileInputStream(new File(file_details("Excel_Traffic_Rows"))); XSSFWorkbook workbook1 = new XSSFWorkbook(fis); XSSFSheet sheet1 = workbook1.getSheetAt(0); int last_row_index = sheet1.getLastRowNum(); XSSFRow last_row = sheet1.getRow(last_row_index); ArrayList<String> data = new ArrayList<String>(); String str = ""; StringBuilder sb = new StringBuilder(); Iterator<Cell> cellIterator = last_row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: str = cell.getNumericCellValue() + ","; break; case Cell.CELL_TYPE_STRING: str = cell.getStringCellValue() + ","; break; } sb.append(str); } data.add(sb.substring(0, sb.length() - 1)); PrintWriter pw = new PrintWriter(csv_file); pw.write(data.get(0) + "\n"); pw.close(); fis.close(); System.out.println("Data written in Sample File successfully."); } catch (FileNotFoundException ex) { Logger.getLogger(CSV_File_Generator.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(CSV_File_Generator.class.getName()).log(Level.SEVERE, null, ex); } }
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 a v a 2 s . c o m 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); } }
From source file:utils.EXCELTODB.java
public static void main(String[] args) { try {//ww w . j ava 2 s . c o m Class.forName("com.mysql.jdbc.Driver"); Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/lldval", "root", "passw0rd"); con.setAutoCommit(false); PreparedStatement pstm = null; FileInputStream input = new FileInputStream("./PCAT_AnalysisFile.xlsx"); XSSFWorkbook wb = new XSSFWorkbook(input); XSSFSheet sheet = wb.getSheetAt(1); Row row; for (int i = 1; i <= sheet.getLastRowNum(); i++) { row = sheet.getRow(i); int id = (int) row.getCell(0).getNumericCellValue(); String name = row.getCell(1).getStringCellValue(); String address = row.getCell(2).getStringCellValue(); String sql = "INSERT INTO lldval.test VALUES('" + id + "','" + name + "','" + address + "')"; pstm = (PreparedStatement) con.prepareStatement(sql); pstm.execute(); System.out.println("Import rows " + i); } con.commit(); // pstm.close(); con.close(); input.close(); System.out.println("Success import excel to mysql table"); } catch (ClassNotFoundException e) { System.out.println(e); } catch (SQLException ex) { System.out.println(ex); } catch (IOException ioe) { System.out.println(ioe); } }