List of usage examples for org.apache.poi.hssf.usermodel HSSFRow getPhysicalNumberOfCells
@Override public int getPhysicalNumberOfCells()
From source file:pe.gob.mef.gescon.web.ui.WikiMB.java
public void postProcessXLS(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0);//from www . j a va2 s. c o m // Para la cabecera HSSFRow header = sheet.getRow(0); HSSFCellStyle headerStyle = wb.createCellStyle(); HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); headerStyle.setFont(font); for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) { HSSFCell cell = header.getCell(i); cell.setCellStyle(headerStyle); sheet.autoSizeColumn(i); } HSSFCellStyle centerStyle = wb.createCellStyle(); centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCellStyle centerGrayStyle = wb.createCellStyle(); centerGrayStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); centerGrayStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); centerGrayStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); HSSFCellStyle grayBG = wb.createCellStyle(); grayBG.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); grayBG.setFillPattern(CellStyle.SOLID_FOREGROUND); int i = 1; for (Conocimiento c : this.getListaWiki()) { HSSFRow row = sheet.getRow(i); for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) { HSSFCell cell = row.getCell(j); if (i % 2 == 0) { if (j > 0) { cell.setCellStyle(centerGrayStyle); } else { cell.setCellStyle(grayBG); } } else { if (j > 0) { cell.setCellStyle(centerStyle); } } } i++; } }
From source file:poi.hssf.usermodel.examples.HSSFReadWrite.java
License:Apache License
/** * Method main//from w w w . ja va 2s . c om * * Given 1 argument takes that as the filename, inputs it and dumps the * cell values/types out to sys.out.<br/> * * given 2 arguments where the second argument is the word "write" and the * first is the filename - writes out a sample (test) spreadsheet * see {@link HSSFReadWrite#testCreateSampleSheet(String)}.<br/> * * given 2 arguments where the first is an input filename and the second * an output filename (not write), attempts to fully read in the * spreadsheet and fully write it out.<br/> * * given 3 arguments where the first is an input filename and the second an * output filename (not write) and the third is "modify1", attempts to read in the * spreadsheet, deletes rows 0-24, 74-99. Changes cell at row 39, col 3 to * "MODIFIED CELL" then writes it out. Hence this is "modify test 1". If you * take the output from the write test, you'll have a valid scenario. */ public static void main(String[] args) { if (args.length < 1) { System.err.println("At least one argument expected"); return; } String fileName = args[0]; try { if (args.length < 2) { HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); System.out.println("Data dump:\n"); for (int k = 0; k < wb.getNumberOfSheets(); k++) { HSSFSheet sheet = wb.getSheetAt(k); int rows = sheet.getPhysicalNumberOfRows(); System.out.println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows + " row(s)."); for (int r = 0; r < rows; r++) { HSSFRow row = sheet.getRow(r); if (row == null) { continue; } int cells = row.getPhysicalNumberOfCells(); System.out.println("\nROW " + row.getRowNum() + " has " + cells + " cell(s)."); for (int c = 0; c < cells; c++) { HSSFCell cell = row.getCell(c); String value = null; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA: value = "FORMULA value=" + cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC: value = "NUMERIC value=" + cell.getNumericCellValue(); break; case HSSFCell.CELL_TYPE_STRING: value = "STRING value=" + cell.getStringCellValue(); break; default: } System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value); } } } } else if (args.length == 2) { if (args[1].toLowerCase().equals("write")) { System.out.println("Write mode"); long time = System.currentTimeMillis(); HSSFReadWrite.testCreateSampleSheet(fileName); System.out.println("" + (System.currentTimeMillis() - time) + " ms generation time"); } else { System.out.println("readwrite test"); HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); FileOutputStream stream = new FileOutputStream(args[1]); wb.write(stream); stream.close(); } } else if (args.length == 3 && args[2].toLowerCase().equals("modify1")) { // delete row 0-24, row 74 - 99 && change cell 3 on row 39 to string "MODIFIED CELL!!" HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); FileOutputStream stream = new FileOutputStream(args[1]); HSSFSheet sheet = wb.getSheetAt(0); for (int k = 0; k < 25; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } for (int k = 74; k < 100; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } HSSFRow row = sheet.getRow(39); HSSFCell cell = row.getCell(3); cell.setCellValue("MODIFIED CELL!!!!!"); wb.write(stream); stream.close(); } } catch (Exception e) { e.printStackTrace(); } }
From source file:poi.HSSFReadWrite.java
License:Apache License
/** * Method main/* w w w. ja v a2s . co m*/ * * Given 1 argument takes that as the filename, inputs it and dumps the * cell values/types out to sys.out.<br/> * * given 2 arguments where the second argument is the word "write" and the * first is the filename - writes out a sample (test) spreadsheet * see {@link HSSFReadWrite#testCreateSampleSheet(String)}.<br/> * * given 2 arguments where the first is an input filename and the second * an output filename (not write), attempts to fully read in the * spreadsheet and fully write it out.<br/> * * given 3 arguments where the first is an input filename and the second an * output filename (not write) and the third is "modify1", attempts to read in the * spreadsheet, deletes rows 0-24, 74-99. Changes cell at row 39, col 3 to * "MODIFIED CELL" then writes it out. Hence this is "modify test 1". If you * take the output from the write test, you'll have a valid scenario. */ public static void main(String[] args) { if (args.length < 1) { System.err.println("At least one argument expected"); return; } String fileName = args[0]; try { if (args.length < 2) { HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); System.out.println("Data dump:\n"); for (int k = 0; k < wb.getNumberOfSheets(); k++) { HSSFSheet sheet = wb.getSheetAt(k); int rows = sheet.getPhysicalNumberOfRows(); System.out.println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows + " row(s)."); for (int r = 0; r < rows; r++) { HSSFRow row = sheet.getRow(r); if (row == null) { continue; } int cells = row.getPhysicalNumberOfCells(); System.out.println("\nROW " + row.getRowNum() + " has " + cells + " cell(s)."); for (int c = 0; c < cells; c++) { HSSFCell cell = row.getCell(c); String value = null; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA: value = "FORMULA value=" + cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC: value = "NUMERIC value=" + cell.getNumericCellValue(); break; case HSSFCell.CELL_TYPE_STRING: value = "STRING value=" + cell.getStringCellValue(); break; default: } System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value); } } } // wb.close(); } else if (args.length == 2) { if (args[1].toLowerCase(Locale.ROOT).equals("write")) { System.out.println("Write mode"); long time = System.currentTimeMillis(); HSSFReadWrite.testCreateSampleSheet(fileName); System.out.println("" + (System.currentTimeMillis() - time) + " ms generation time"); } else { System.out.println("readwrite test"); HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); FileOutputStream stream = new FileOutputStream(args[1]); wb.write(stream); stream.close(); // wb.close(); } } else if (args.length == 3 && args[2].toLowerCase(Locale.ROOT).equals("modify1")) { // delete row 0-24, row 74 - 99 && change cell 3 on row 39 to string "MODIFIED CELL!!" HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); FileOutputStream stream = new FileOutputStream(args[1]); HSSFSheet sheet = wb.getSheetAt(0); for (int k = 0; k < 25; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } for (int k = 74; k < 100; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } HSSFRow row = sheet.getRow(39); HSSFCell cell = row.getCell(3); cell.setCellValue("MODIFIED CELL!!!!!"); wb.write(stream); stream.close(); // wb.close(); } } catch (Exception e) { e.printStackTrace(); } }
From source file:ReadExcel.HSSFReadWrite.java
License:Apache License
private static void startReadXlsFile(String fileName) { try {//from ww w.ja v a2 s. c om HSSFWorkbook wb = HSSFReadWrite.readxlsFile(fileName); System.out.println("Data dump:\n"); for (int k = 0; k < wb.getNumberOfSheets(); k++) { HSSFSheet sheet = wb.getSheetAt(k); int rows = sheet.getPhysicalNumberOfRows(); System.out.println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows + " row(s)."); for (int r = 0; r < rows; r++) { HSSFRow row = sheet.getRow(r); if (row == null) { continue; } int cells = row.getPhysicalNumberOfCells(); System.out.println("\nROW " + row.getRowNum() + " has " + cells + " cell(s)."); for (int c = 0; c < cells; c++) { HSSFCell cell = row.getCell(c); String value = null; switch (cell.getCellTypeEnum()) { case FORMULA: value = "FORMULA value=" + cell.getCellFormula(); break; case NUMERIC: value = "NUMERIC value=" + cell.getNumericCellValue(); break; case STRING: value = "STRING value=" + cell.getStringCellValue(); break; default: } System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value); } } } wb.close(); } catch (Exception e) { e.printStackTrace(); } }
From source file:senselogic.excelbundle.ExcelImporter.java
License:Apache License
/** * Fetch all LanguageFiles in the specified language from the specified * sheet and add them to the specified LanguagePack. *//* w ww. j a va2s . co m*/ public void getFromSheet(HSSFSheet sheet, String language, LanguagePack pack) { //First, let's fetch all of the defined rows List<HSSFRow> rows = new ArrayList<HSSFRow>(); int lastRow = sheet.getLastRowNum(); for (int i = sheet.getFirstRowNum(); i <= lastRow; i++) { //Don't add empty rows HSSFRow r = sheet.getRow(i); if ((r != null) && (r.getPhysicalNumberOfCells() != 0)) { rows.add(r); } } //Now go, through them one at a time ListIterator<HSSFRow> it = rows.listIterator(); while (it.hasNext()) { HSSFRow r = it.next(); HSSFCell c = r.getCell(0); String value = getString(c); if ((value.charAt(0) != '/') && (value.charAt(0) != '\\')) continue; String bundlePath = value; //Let's see if this language is included if (!it.hasNext()) break; r = it.next(); int valueCol = getIndexOf(r, language); //Skip to next if the language isn't included in this bundle if (valueCol < 0) continue; LanguageFile langFile = new LanguageFile(bundlePath, language); //Now, search through all the keys while (it.hasNext()) { r = it.next(); String firstCell = getString(r.getCell(0)); if (firstCell == null) continue; //If this is a bundle path, rewind and break the loop to parse //the next bundle if ((firstCell.charAt(0) == '/') || (firstCell.charAt(0) == '\\')) { it.previous(); break; } langFile.setValue(firstCell, getString(r.getCell(valueCol))); } if (!langFile.getPairs().isEmpty()) pack.addLanguageFile(langFile); } }
From source file:swift.selenium.ExcelUtility.java
License:Open Source License
public static Reporter CompareExcel(HSSFSheet actualSheet, HSSFSheet expectedSheet, List<String> columns, List<String> columnsData, String testCaseID, String transactionType, int actualSheetRowCount) throws IOException { boolean isrowFound = false; int expSheetRowCount = getRowCount(expectedSheet); //expectedSheet.getPhysicalNumberOfRows(); Reporter report = new Reporter(); report.setReport(report);/* w w w .j a v a2 s. c om*/ int passCount = 0, failCount = 0, colCount = 0, finalRowCount = 0; //TM-28/09/2015: if expected sheet row count is greater than actual sheet row count than comparison should be on basis of expected sheet row count else vice-versa if (expSheetRowCount >= actualSheetRowCount) finalRowCount = expSheetRowCount; else finalRowCount = actualSheetRowCount; for (int rowIndex = firstRow; rowIndex < firstRow + finalRowCount; rowIndex++) { passCount = 0; failCount = 0; int currentRow = ++WebVerification.currentRowIndex; HSSFRow actualRow = actualSheet.getRow(currentRow); HSSFRow expectedRow = expectedSheet.getRow(rowIndex); //TM-28/09/2015: if actual and expected sheet row count does not match then break after following the steps in this code block. if (actualRow == null || expectedRow == null) { status.clear(); report.strStatus = "FAIL"; report.setStrStatus(report.strStatus); rowStatus.add(report.strStatus); failCount += 1; //TM-28/09/2015: if expected sheet row count is greater than actual sheet row count else vice-versa if (actualRow == null) report.strActualValue = "Expected No. of rows are greater than Actual No. of rows."; else report.strActualValue = "Actual No. of rows are greater than Expected No. of rows."; actualValue = new ArrayList<String>(); actualValue.add(report.strActualValue); actualRows.add(actualValue); PassCount.add(passCount); FailCount.add(failCount); report.setReport(report); break; } if (actualRow.getCell(0).toString().equals(expectedRow.getCell(0).toString()) && actualRow.getCell(1).toString().equals(expectedRow.getCell(1).toString())) { isrowFound = true; actualValue = new ArrayList<String>(); //TM:6/08/15-This is unreachable code /*if(actualRow == null || expectedRow == null) { break; }*/ colCount = expectedRow.getPhysicalNumberOfCells(); for (int columnIndex = 3; columnIndex < colCount; columnIndex++) { HSSFCell actualCell = actualRow.getCell(columnIndex); DataFormatter fmt = new DataFormatter(); HSSFCell expectedCell = expectedRow.getCell(columnIndex); //TM: commented the code to find replacement of continue /*if(actualCell == null || expectedCell == null) { continue; }*/ //TM: Following 'if' is replacement of the above if (actualCell != null || expectedCell != null) { String expectedValue = fmt.formatCellValue(expectedCell); if (!actualCell.toString().equalsIgnoreCase(expectedValue)) { report.strStatus = "FAIL"; report.setStrStatus(report.strStatus); failCount += 1; report.strActualValue = "FAIL |" + expectedValue + "|" + actualCell.toString(); } else { passCount += 1; report.strStatus = "PASS"; report.setStrStatus(report.strStatus); report.strActualValue = actualCell.toString(); //System.out.println(actualCell.toString()); } status.add(report.strStatus); actualValue.add(report.strActualValue); } } if (status.contains("FAIL")) { report.strStatus = "FAIL"; } else { report.strStatus = "PASS"; } status.clear(); rowStatus.add(report.strStatus); PassCount.add(passCount); FailCount.add(failCount); actualRows.add(actualValue); report.setReport(report); } else if (isrowFound == false) { continue; /*MainController.pauseFun("No Rows Found For Comparision"); break;*/ } } if (rowStatus.contains("FAIL")) { report.strStatus = "FAIL"; } WriteToDetailResults(testCaseID, transactionType, columns, actualRows, passCount, failCount, expSheetRowCount, colCount, report, rowStatus); PassCount.clear(); FailCount.clear(); return report; }
From source file:swift.selenium.ExcelUtility.java
License:Open Source License
public static Reporter CompareExcelRowWise(HSSFSheet actualSheet, HSSFSheet expectedSheet, List<String> columns, List<String> columnsData, String testCaseID, String transactionType) throws IOException { boolean isrowFound = false; int expSheetRowCount = getRowCount(expectedSheet);//expectedSheet.getPhysicalNumberOfRows(); int effectivefirstRow = firstRow; int actualSheetRowCount = ExcelUtility.getRowCount(actualSheet); Reporter report = new Reporter(); report.setReport(report);//from www. j a va 2 s.c o m int passCount = 0, failCount = 0, colCount = 0, finalRowCount = 0; WebVerification.currentRowIndex = 0; //TM-28/09/2015: if expected sheet row count not matching actual sheet row count than Come out if (expSheetRowCount != actualSheetRowCount) { TransactionMapping.report.strStatus = "FAIL"; TransactionMapping.report.strMessage = "Expected No. of rows does not match Actual No. of rows, Please check Expected and Actual sheets"; } else { for (int rowIndex = effectivefirstRow; rowIndex < effectivefirstRow + expSheetRowCount; rowIndex++) { passCount = 0; failCount = 0; int currentRow = ++WebVerification.currentRowIndex; HSSFRow actualRow = actualSheet.getRow(currentRow); HSSFRow expectedRow = expectedSheet.getRow(rowIndex); //TM-28/09/2015: if actual and expected sheet row count does not match then break after following the steps in this code block. if (actualRow == null || expectedRow == null) { status.clear(); report.strStatus = "FAIL"; report.setStrStatus(report.strStatus); rowStatus.add(report.strStatus); failCount += 1; //TM-28/09/2015: if expected sheet row count is greater than actual sheet row count else vice-versa if (actualRow == null) report.strActualValue = "Expected No. of rows are greater than Actual No. of rows."; else report.strActualValue = "Actual No. of rows are greater than Expected No. of rows."; actualValue = new ArrayList<String>(); actualValue.add(report.strActualValue); actualRows.add(actualValue); PassCount.add(passCount); FailCount.add(failCount); report.setReport(report); break; } if (actualRow.getCell(0).toString().equals(expectedRow.getCell(0).toString()) && actualRow.getCell(1).toString().equals(expectedRow.getCell(1).toString())) { isrowFound = true; actualValue = new ArrayList<String>(); colCount = expectedRow.getPhysicalNumberOfCells(); for (int columnIndex = 3; columnIndex < colCount; columnIndex++) { HSSFCell actualCell = actualRow.getCell(columnIndex); DataFormatter fmt = new DataFormatter(); HSSFCell expectedCell = expectedRow.getCell(columnIndex); //TM: Following 'if' is replacement of the above if (actualCell != null || expectedCell != null) { String expectedValue = fmt.formatCellValue(expectedCell); if (!actualCell.toString().equalsIgnoreCase(expectedValue)) { report.strStatus = "FAIL"; report.setStrStatus(report.strStatus); failCount += 1; report.strActualValue = "FAIL |" + expectedValue + "|" + actualCell.toString(); } else { passCount += 1; report.strStatus = "PASS"; report.setStrStatus(report.strStatus); report.strActualValue = actualCell.toString(); System.out.println(actualCell.toString()); } status.add(report.strStatus); actualValue.add(report.strActualValue); } } if (status.contains("FAIL")) { report.strStatus = "FAIL"; TransactionMapping.report.strStatus = "FAIL"; TransactionMapping.report.strMessage = "Please refer to Detailed Results for more info.."; } else { report.strStatus = "PASS"; } status.clear(); rowStatus.add(report.strStatus); PassCount.add(passCount); FailCount.add(failCount); actualRows.add(actualValue); report.setReport(report); } else if (isrowFound == false) { continue; } } if (rowStatus.contains("FAIL")) { report.strStatus = "FAIL"; } WriteToDetailResults(testCaseID, transactionType, columns, actualRows, passCount, failCount, expSheetRowCount, colCount, report, rowStatus); } PassCount.clear(); FailCount.clear(); return report; }
From source file:swift.selenium.TransactionMapping.java
License:Open Source License
public static HashMap<String, Object> getValueFromHashMap(HSSFSheet reqSheet) { HashMap<String, Object> inputHashTable = new HashMap<String, Object>(); HSSFRow rowHeader = reqSheet.getRow(0); int columnCount = rowHeader.getPhysicalNumberOfCells(); for (int colIndex = 0; colIndex < columnCount; colIndex++) { inputHashTable.put(rowHeader.getCell(colIndex).toString(), colIndex); }/*from ww w . ja v a 2 s. c o m*/ return inputHashTable; }
From source file:ucar.unidata.data.DataUtil.java
License:Open Source License
/** * Convert excel to csv//from w w w .ja v a2s .com * * @param filename excel file * @param skipToFirstNumeric if true, skip to first numeric * @param sdf If non null then use this to format any date cells * * @return csv * * @throws Exception On badness */ public static String xlsToCsv(String filename, boolean skipToFirstNumeric, SimpleDateFormat sdf) throws Exception { StringBuffer sb = new StringBuffer(); InputStream myxls = IOUtil.getInputStream(filename, DataUtil.class); HSSFWorkbook wb = new HSSFWorkbook(myxls); HSSFSheet sheet = wb.getSheetAt(0); // first sheet boolean seenNumber = false; for (int rowIdx = sheet.getFirstRowNum(); rowIdx <= sheet.getLastRowNum(); rowIdx++) { HSSFRow row = sheet.getRow(rowIdx); if (row == null) { sb.append("\n"); continue; } boolean rowOk = true; for (short colIdx = row.getFirstCellNum(); colIdx < row.getPhysicalNumberOfCells(); colIdx++) { HSSFCell cell = row.getCell(colIdx); if (cell == null) { continue; } if (skipToFirstNumeric && !seenNumber) { if (cell.getCellType() != HSSFCell.CELL_TYPE_NUMERIC) { rowOk = false; break; } seenNumber = true; } String cellValue = null; if ((sdf != null) && (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)) { if (HSSFDateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); cellValue = sdf.format(date); } } if (cellValue == null) { cellValue = cell.toString(); } if (colIdx > 0) { sb.append(","); } sb.append(cellValue); /* if(false && comment!=null) { String author = comment.getAuthor(); String str = comment.getString().getString(); str = StringUtil.replace(str, author+":",""); str = StringUtil.replace(str, "\n",""); sb.append("("+str+")"); }*/ } if (rowOk) { sb.append("\n"); } } return sb.toString(); }