List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getLastRowNum
@Override
public int getLastRowNum()
From source file:ik1004labb5.DAOHundExcel.java
@Override public void delete(int id) { XSSFWorkbook workbook = getExcelWorkbook(); XSSFSheet worksheet = workbook.getSheetAt(0); DataFormatter df = new DataFormatter(); //Loopa igenom nollkolumnen fr att frska hitta en matchning p ID for (Row row : worksheet) { if (df.formatCellValue(row.getCell(0)).equalsIgnoreCase(Integer.toString(id))) { if (row.getRowNum() == worksheet.getLastRowNum()) { worksheet.removeRow(row); break; } else { worksheet.shiftRows(row.getRowNum() + 1, worksheet.getLastRowNum(), -1); }//w w w. j av a 2s. c om break; } } saveToExcel(workbook); }
From source file:Import.ImportCapital.java
@Override public int getSheetRowCount(XSSFSheet sheet) { int lastRowNum = sheet.getLastRowNum(); lastRowNum++;//ww w. j a v a 2 s .c o m for (int i = START_ROW_CAPITAL; i < lastRowNum; i++) { XSSFRow row = sheet.getRow(i); XSSFCell cell1 = row.getCell(1); XSSFCell cell2 = row.getCell(2); XSSFCell cell3 = row.getCell(3); XSSFCell cell4 = row.getCell(4); if ((cell1 == null || getStringCellValueNoSetError(cell1) == null) && (cell2 == null || getStringCellValueNoSetError(cell2) == null) && (cell3 == null || getStringCellValueNoSetError(cell3) == null) && (cell4 == null || getStringCellValueNoSetError(cell4) == null)) { return i - START_ROW_CAPITAL; } } return lastRowNum - START_ROW_CAPITAL; }
From source file:Import.ImportDonation.java
@Override protected int getSheetRowCount(XSSFSheet sheet) { int lastRowNum = sheet.getLastRowNum(); lastRowNum++;//from ww w . j a va 2 s . c om int count = 0; for (int i = START_ROW_DONAION; i < lastRowNum; i++) { XSSFRow row = sheet.getRow(i); XSSFCell cell0 = row.getCell(0); XSSFCell cell3 = row.getCell(3); if ((cell0 == null || getStringCellValueNoSetError(cell0) == null) && (cell3 == null || getStringCellValueNoSetError(cell3) == null)) { return count; } count++; } return lastRowNum - START_ROW_DONAION; }
From source file:Import.ImportGuarantee.java
@Override protected int getSheetRowCount(XSSFSheet sheet) { int lastRowNum = sheet.getLastRowNum(); lastRowNum++;/*from w w w .j a va2 s.co m*/ int count = 0; for (int i = START_ROW_GUARANTEE; i < lastRowNum; i++) { XSSFRow row = sheet.getRow(i); XSSFCell cell = row.getCell(0); if (cell == null || getStringCellValueNoSetError(cell) == null) { return count - START_ROW_GUARANTEE; } count++; } return lastRowNum - START_ROW_GUARANTEE; }
From source file:in.expertsoftware.colorcheck.Metadata_Position_Of_Reporting_Qtr.java
public boolean verify_EORQ_metadata_posiition(XSSFSheet Sheet, ArrayList<TokenModel> tokenmodel) { int EORQ_rowindex = -1; boolean correct = true; for (int i = 0; i < tokenmodel.size(); i++) { if ((tokenmodel.get(i).token_name.equals("EORQ"))) EORQ_rowindex = tokenmodel.get(i).row_no; }/* w w w. j av a 2 s . c o m*/ if (EORQ_rowindex == -1) { correct = false; } else { Row row = Sheet.getRow(EORQ_rowindex - 1); Cell cell = row.getCell(1); if (Sheet.getLastRowNum() == EORQ_rowindex - 1) { correct = true; } else if (!((cell.getCellType() != CELL_TYPE_BLANK) && ((Sheet.getRow(EORQ_rowindex).getCell(0) == null) || (Sheet.getRow(EORQ_rowindex).getCell(0).getCellType() == CELL_TYPE_BLANK)) && ((Sheet.getRow(EORQ_rowindex).getCell(1) == null) || (Sheet.getRow(EORQ_rowindex).getCell(1).getCellType() == CELL_TYPE_BLANK)))) { correct = false; } } return correct; }
From source file:in.expertsoftware.colorcheck.Metadata_Position_Of_Reporting_Year.java
public boolean verify_EORY_metadata_posiition(XSSFSheet Sheet, ArrayList<TokenModel> tokenmodel) { int EORY_rowindex = -1; boolean correct = true; for (int i = 0; i < tokenmodel.size(); i++) { if ((tokenmodel.get(i).token_name.equals("EORY"))) EORY_rowindex = tokenmodel.get(i).row_no; }// ww w. j ava 2 s . c om if (EORY_rowindex == -1) { correct = false; } else { Row row = Sheet.getRow(EORY_rowindex - 1); Cell cell = row.getCell(1); if (Sheet.getLastRowNum() == EORY_rowindex - 1) { correct = true; } else if (!((cell.getCellType() != CELL_TYPE_BLANK) && ((Sheet.getRow(EORY_rowindex).getCell(0) == null) || (Sheet.getRow(EORY_rowindex).getCell(0).getCellType() == CELL_TYPE_BLANK)) && ((Sheet.getRow(EORY_rowindex).getCell(1) == null) || (Sheet.getRow(EORY_rowindex).getCell(1).getCellType() == CELL_TYPE_BLANK)))) { correct = false; } } return correct; }
From source file:localization.checkURL.java
public static void check(String desktopFile, String serverFile, String inputFolder) { try {/*from ww w . j av a2 s . com*/ String desktopFolder = desktopFile.substring(desktopFile.lastIndexOf("\\") + 1, desktopFile.length()); desktop = desktopFolder.substring(0, desktopFolder.indexOf("_")); String serverFolder = serverFile.substring(serverFile.lastIndexOf("\\") + 1, serverFile.length()); server = serverFolder.substring(0, serverFolder.indexOf("_")); pubList = new ArrayList<>(); searchFile(inputFolder); String parFolder = inputFolder.substring(0, inputFolder.lastIndexOf("\\")); HOName = parFolder.substring(parFolder.lastIndexOf("\\") + 1, parFolder.lastIndexOf("\\") + 5); lang = inputFolder.substring(inputFolder.lastIndexOf("\\") + 1, inputFolder.length()); outputFileName = parFolder.substring(parFolder.lastIndexOf("\\") + 1, parFolder.length()); outputFilePath = parFolder + "\\" + outputFileName + "_" + lang + ".xlsx"; valueArray = new String[pubList.size() + 1][6]; valueArray[0][0] = "Language"; valueArray[0][1] = "HO#"; valueArray[0][2] = "Publication Name"; valueArray[0][3] = "Type"; valueArray[0][4] = "Topic Name"; valueArray[0][5] = "URL"; for (int i = 0; i < pubList.size(); i++) { String fullPath = pubList.get(i); valueArray[i + 1][0] = lang.toUpperCase().trim(); valueArray[i + 1][1] = HOName.trim(); valueArray[i + 1][4] = fullPath.substring(fullPath.lastIndexOf("\\") + 1, fullPath.length()); if (fullPath.contains("\\topic\\")) { valueArray[i + 1][3] = "topic"; valueArray[i + 1][2] = fullPath .substring(fullPath.indexOf("\\P") + 4, fullPath.indexOf("\\topic\\")).trim(); } else if (fullPath.contains("\\map\\")) { valueArray[i + 1][3] = "map"; valueArray[i + 1][2] = fullPath .substring(fullPath.indexOf("\\P") + 4, fullPath.indexOf("\\map\\")).trim(); } } /* for(int i = 0; i < valueArray.length; i++){ for(int j = 0; j < valueArray[i].length; j++){ System.out.print(valueArray[i][j] + ","); } System.out.println(); }*/ File inputDesktopFile = new File(desktopFile); File inputServerFile = new File(serverFile); XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(inputDesktopFile)); XSSFSheet mysheet = workbook.getSheetAt(0); int desktopRowNum = mysheet.getLastRowNum(); XSSFWorkbook serverWorkbook = new XSSFWorkbook(new FileInputStream(inputServerFile)); XSSFSheet serverSheet = serverWorkbook.getSheetAt(0); int serverRowNum = serverSheet.getLastRowNum(); for (int k = 1; k < valueArray.length; k++) { //System.out.println(valueArray[k][3]); if (valueArray[k][3].equals("topic") && (!valueArray[k][4].trim().startsWith("cfg"))) { //System.out.println(k + " " +valueArray[k][3]); String source = valueArray[k][4].trim(); for (int i = 1; i < desktopRowNum + 1; i++) { Row desktopRow = mysheet.getRow(i); if (desktopRow != null) { String targetString = desktopRow.getCell(2).getStringCellValue().trim(); if (source.contains(targetString)) { String desktopURL = desktopRow.getCell(0).getStringCellValue().trim(); desktopURL = desktopURL.replace(".com/en\\", ".com/" + lang.toLowerCase() + "\\"); desktopURL = desktopURL.replace("http://" + desktop, "http://" + desktop + "uat"); valueArray[k][5] = desktopURL; } } } for (int j = 0; j < serverRowNum + 1; j++) { Row serverRow = serverSheet.getRow(j); if (serverRow != null) { String targetString1 = serverRow.getCell(2).getStringCellValue().trim(); if (source.contains(targetString1)) { String serverURL = serverRow.getCell(0).getStringCellValue().trim(); serverURL = serverURL.replace("/en\\", "/" + lang.toLowerCase() + "\\"); serverURL = serverURL.replace("http://" + server, "http://" + server + "uat"); // System.out.println(serverURL); if (valueArray[k][5] != null) { valueArray[k][5] = valueArray[k][5] + "\n" + serverURL; } else { valueArray[k][5] = serverURL; } } } } } XSSFWorkbook outputworkbook = new XSSFWorkbook(); XSSFSheet outputsheet = outputworkbook.createSheet("sheet1"); XSSFCellStyle outputstyle = outputworkbook.createCellStyle(); outputstyle.setWrapText(true); int outputRowNum = 0; int outputCellNum = 0; for (int i = 0; i < valueArray.length; i++) { Row outputRow = outputsheet.createRow(outputRowNum++); for (int j = 0; j < valueArray[1].length; j++) { Cell outputCell = outputRow.createCell(outputCellNum++); if (valueArray[i][j] != null) { outputCell.setCellValue(valueArray[i][j]); } else { outputCell.setCellValue("N/A"); } if (j == 5) { //outputsheet.autoSizeColumn(4); outputCell.setCellStyle(outputstyle); } } outputCellNum = 0; } outputsheet.autoSizeColumn(2); outputsheet.autoSizeColumn(4); outputsheet.autoSizeColumn(5); FileOutputStream out = new FileOutputStream(new File(outputFilePath)); outputworkbook.write(out); out.close(); } } catch (Exception e) { try { File file = new File(userDir + "\\log.txt"); if (!file.exists()) { file.createNewFile(); } FileWriter fw = new FileWriter(file.getAbsoluteFile()); BufferedWriter bw = new BufferedWriter(fw); bw.write(e.getMessage()); bw.write(e.getLocalizedMessage()); bw.close(); fw.close(); } catch (Exception e1) { e1.printStackTrace(); } e.printStackTrace(); } }
From source file:log_compressor.write_disk_space.java
public static void write_disk_space(HashMap<String, ArrayList<String>> map, List<String> server_list) throws FileNotFoundException, IOException { File myFile = new File("D:\\log\\log_output.xlsx"); FileInputStream fis = new FileInputStream(myFile); XSSFWorkbook myWorkBook = new XSSFWorkbook(fis); XSSFSheet mySheet = myWorkBook.getSheetAt(0); HashMap<String, String> res = new HashMap<String, String>(); ArrayList<String> key_set = new ArrayList<String>(); ArrayList<String> free_space = new ArrayList<String>(); BusinessOrInfra boi = new BusinessOrInfra(); int rownum = mySheet.getLastRowNum() + 1; Row row1 = mySheet.createRow(rownum++); Cell cell2 = row1.createCell(0);// w w w .ja v a2s .c om Date date = new Date(); cell2.setCellValue(date.toString()); int i = 0; boolean isInfra = boi.isInfrastructure(); for (String key : server_list) { free_space.clear(); ArrayList<String> disk_free = map.get(key); for (String df : disk_free) { if (!df.equals("need manual check")) { int free_position1 = df.lastIndexOf("GB"); int free_position2 = df.lastIndexOf("free"); String disk = df.substring(0, 1); String key_disk = key + ":" + disk; String free_space_percent = df.substring(free_position1 + 3, free_position2 - 1); free_space.add(free_space_percent); res.put(key_disk, free_space_percent); key_set.add(key_disk); } else { free_space.add("need manual check"); } } Row row = null; if (isInfra) { if (i != 22) { row = mySheet.createRow(rownum++); i++; } else { rownum = rownum + 2; row = mySheet.createRow(rownum++); i++; } } else { row = mySheet.createRow(rownum++); i++; } int cellnum = 0; Cell cell = row.createCell(cellnum++); cell.setCellValue(key); for (String val : free_space) { cellnum = cellnum + 1; Cell cell1 = row.createCell(cellnum); cell1.setCellValue(val); } } FileOutputStream os = new FileOutputStream(myFile); myWorkBook.write(os); }
From source file:log_compressor.write_xlsx.java
public static void write_xlsx(HashMap<String, List<String>> map, ArrayList<String> server_list) throws FileNotFoundException, IOException { File myFile = new File("D:\\log\\log_output.xlsx"); FileInputStream fis = new FileInputStream(myFile); XSSFWorkbook myWorkBook = new XSSFWorkbook(fis); XSSFSheet mySheet = myWorkBook.getSheetAt(0); BusinessOrInfra boi = new BusinessOrInfra(); boolean isInfra = boi.isInfrastructure(); int rownum = mySheet.getLastRowNum() + 1; Row row1 = mySheet.createRow(rownum++); Cell cell2 = row1.createCell(0);/*www . ja va2 s. c o m*/ Date date = new Date(); cell2.setCellValue(date.toString()); int i = 0; for (String key : server_list) { Row row = null; if (isInfra) { if (i != 22) { row = mySheet.createRow(rownum++); i++; } else { rownum = rownum + 2; row = mySheet.createRow(rownum++); i++; } } else { row = mySheet.createRow(rownum++); i++; } int cellnum = 0; List<String> event = map.get(key); Cell cell = row.createCell(cellnum); cell.setCellValue(key); Cell cell1 = row.createCell(cellnum + 2); cell1.setCellValue(event.toString().substring(1, event.toString().length() - 1)); } FileOutputStream os = new FileOutputStream(myFile); myWorkBook.write(os); }
From source file:lp.XLSXhandler.java
public Object[] opener(File uploaded, String name) { double[][] data = new double[0][0]; String[] dmuNames = new String[0]; String[] variable_names = new String[0]; Object[] obj = new Object[5]; try {//from w w w . j a v a2 s . c o m OPCPackage pkg = OPCPackage.open(uploaded); XSSFWorkbook wb = new XSSFWorkbook(pkg); XSSFSheet sheet1 = wb.getSheetAt(0); //I find the number of the rows in the file! (0-based) int rows = sheet1.getLastRowNum(); System.out.println("Total Rows of DATA in the file: " + rows); //I find the number of columns! (1-based) int columns = sheet1.getRow(0).getLastCellNum(); System.out.println("Total Columns of DATA in the file: " + columns); data = new double[rows][columns - 1]; dmuNames = new String[rows]; variable_names = new String[columns]; Row row_trav; Cell cell_trav; // Retrieve data from file to array for (int i = 0; i <= rows; i++) { row_trav = sheet1.getRow(i); for (int k = 0; k < columns; k++) { cell_trav = row_trav.getCell(k); if (i == 0) { //we are at line 0 of the uploaded file variable_names[k] = cell_trav.getStringCellValue(); } if (k == 0 && i < rows) { //we are at column 0 of the uploaded file Row row_name = sheet1.getRow(i + 1); cell_trav = row_name.getCell(0); dmuNames[i] = cell_trav.getStringCellValue(); } if (i > 0 && k > 0) { data[i - 1][k - 1] = cell_trav.getNumericCellValue(); } } } obj[0] = data; obj[1] = rows; obj[2] = columns; obj[3] = variable_names; obj[4] = dmuNames; } catch (InvalidFormatException e) { } catch (IOException e) { } return obj; }