List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getPhysicalNumberOfRows
@Override public int getPhysicalNumberOfRows()
From source file:com.ctb.importdata.ImportSFDataProcessor.java
public static ArrayList<SalesForceLicenseData> readDataFromXLSXFile(String fileName) { File sfDataFile = new File(fileName); FileInputStream fileInputStream = null; ArrayList<SalesForceLicenseData> sfLicenseDataList = null; //read the file in to stream if (sfDataFile.exists()) { //System.out.println("Reading data from .xlsx file started."); logger.info("Reading data from .xlsx file : Started :: Timestamp >> " + new Date(System.currentTimeMillis())); try {// w w w . j a v a 2 s .c o m fileInputStream = new FileInputStream(sfDataFile); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream); //Get first/desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); sfLicenseDataList = new ArrayList<SalesForceLicenseData>(); SalesForceLicenseData sfld = null; if (sheet != null) { int totalRows = sheet.getPhysicalNumberOfRows(); //System.out.println("Total no. of physical rows in file = "+ totalRows); logger.info("Total no. of physical rows in file = " + totalRows); Row headerRow = sheet.getRow(0); Cell headerCell; Cell dataCell; if (headerRow == null) { //System.out.println("No file header content found.") ; logger.info("No file header content found."); } else { int totalHeaderColumns = headerRow.getPhysicalNumberOfCells(); //System.out.println("Total no. of header cells = "+ totalHeaderColumns); //logger.info("Total no. of header cells = "+ totalHeaderColumns); for (int rowCtr = 1; rowCtr < totalRows; rowCtr++) { //System.out.println("Row No. >> "+rowCtr); Row dataRow = sheet.getRow(rowCtr); if (dataRow != null) { int totalRowColumns = dataRow.getPhysicalNumberOfCells(); //System.out.println("Total no. of current data row cells = "+ totalRowColumns); //logger.info("Total no. of current data row cells = "+ totalRowColumns); logger.info( "Row No. [" + rowCtr + "] :: Header Column Count = [" + totalHeaderColumns + "] :: Current Data Row Column Count = [" + totalRowColumns + "]"); //Discard dummy rows in spreadsheet if the count of current data row columns not equal to header columns if (totalHeaderColumns == totalRowColumns) { boolean isCustomerIdBlank = dataRow.getCell(0) .getCellType() == Cell.CELL_TYPE_BLANK ? true : false; boolean isOrgNodeIdBlank = dataRow.getCell(5) .getCellType() == Cell.CELL_TYPE_BLANK ? true : false; //System.out.println("isCustomerIdBlank >> "+isCustomerIdBlank+" :: isOrgNodeIdBlank >> "+isOrgNodeIdBlank); logger.info("Row No. [" + rowCtr + "] :: isCustomerIdBlank >> " + isCustomerIdBlank + " :: isOrgNodeIdBlank >> " + isOrgNodeIdBlank); //Condition to skip row for SF data object population if customer id or orgnode id is blank if (!isCustomerIdBlank && !isOrgNodeIdBlank) { sfld = new SalesForceLicenseData(); // For each row, loop through each column for (int colCtr = 0; colCtr < totalHeaderColumns; colCtr++) { //System.out.println("Column No. >> "+colCtr); headerCell = headerRow.getCell(colCtr); dataCell = dataRow.getCell(colCtr); if (dataCell != null) { //System.out.println("dataCell.getCellType() >> "+dataCell.getCellType()); switch (dataCell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: //Do nothing System.out.println(dataCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: //System.out.println(dataCell.getNumericCellValue()); populateSFDataNumericColValue(sfld, dataCell, headerCell); break; case Cell.CELL_TYPE_STRING: //System.out.println(dataCell.getStringCellValue()); populateSFDataStrColValue(sfld, dataCell, headerCell); break; case Cell.CELL_TYPE_BLANK: //System.out.println(" "); populateSFDataBlankColValue(sfld, dataCell, headerCell); break; default: System.out.println(dataCell); break; } } } sfLicenseDataList.add(sfld); } } } } } } } catch (FileNotFoundException e) { logger.error("FileNotFoundException : occurred while procesing :: Filename >> [" + fileName + "]"); e.printStackTrace(); // unexpected } catch (IOException e) { logger.error("IOException : occurred while procesing :: Filename >> [" + fileName + "]"); e.printStackTrace(); } finally { try { if (fileInputStream != null) fileInputStream.close(); } catch (IOException e) { logger.error("IOException : occurred while closing file input stream."); e.printStackTrace(); } } //System.out.println("Reading data from .xlsx file completed."); logger.info("Reading data from .xlsx file : Completed :: Timestamp >> " + new Date(System.currentTimeMillis())); } else { //System.out.println("File does not exists"); logger.error("File does not exists :: Filename >> [" + fileName + "]"); } return sfLicenseDataList; }
From source file:com.gnadenheimer.ddjjinformativa.FXMLDocumentController.java
@FXML private void cmdGenerar(ActionEvent event) { try {//from w w w.j av a2 s. c om String cabecera = "1\t"; XSSFSheet sheet = getSheet(); String[] sFecha = sheet.getRow(1).getCell(0).getStringCellValue().split(" "); System.out.print(sheet.getPhysicalNumberOfRows()); cabecera += sFecha[1].split("/")[2] + sFecha[1].split("/")[1] + "\t"; cabecera += "1\t"; if (sheet.getRow(0).getCell(0).getStringCellValue().contains("VENTAS")) { cabecera += "921\t221\t"; } else { cabecera += "911\t211\t"; } cabecera += "3605563\t8\tDOROTEHA GABRIELLE AGANETHA TOEWS HARDER\t\t0\t\t"; Integer totalRows = sheet.getPhysicalNumberOfRows() - 8 + 1; cabecera += totalRows.toString() + "\t"; Integer montoTotal = ((Double) sheet.getRow(sheet.getPhysicalNumberOfRows()).getCell(9) .getNumericCellValue()).intValue(); cabecera += montoTotal.toString() + "\t"; cabecera += "2\n"; String detalle = ""; SimpleDateFormat sm = new SimpleDateFormat("dd/MM/yyyy"); for (int i = 7; i < totalRows + 7; i++) { XSSFRow row = sheet.getRow(i); detalle += "2\t"; detalle += row.getCell(1).getStringCellValue().split("-")[0] + "\t"; detalle += row.getCell(1).getStringCellValue().split("-")[1] + "\t"; detalle += row.getCell(2).getStringCellValue() + "\t"; detalle += "1\t"; detalle += row.getCell(3).getStringCellValue() + "\t"; detalle += sm.format(row.getCell(0).getDateCellValue()) + "\t"; detalle += ((Integer) ((Double) row.getCell(6).getNumericCellValue()).intValue()).toString() + "\t"; detalle += ((Integer) ((Double) row.getCell(7).getNumericCellValue()).intValue()).toString() + "\t"; detalle += "0\t0\t0\t"; detalle += ((Integer) ((Double) row.getCell(9).getNumericCellValue()).intValue()).toString() + "\t"; detalle += "1\t0\t"; detalle += row.getCell(4).getStringCellValue() + "\n"; } cabecera += detalle; String home = System.getProperty("user.home"); File file = new File(home + "/Downloads/DJI" + sFecha[1].split("/")[2] + sFecha[1].split("/")[1] + "_3605563_921.txt"); try (BufferedWriter writer = Files.newBufferedWriter(file.toPath())) { writer.write(cabecera); } } catch (Exception ex) { ex.printStackTrace(); } }
From source file:com.griffinslogistics.book.BookForTransportationJSFController.java
public void postProcessXLS(Object document) { XSSFWorkbook wb = (XSSFWorkbook) document; try {/*from w w w .j av a 2 s . c o m*/ XSSFSheet sheet = wb.getSheetAt(0); for (int i = 2; i <= sheet.getPhysicalNumberOfRows(); i++) { Row row = sheet.getRow(i); row.setHeightInPoints(30); XSSFCell cell = sheet.getRow(i).getCell(0); String text = cell.getStringCellValue(); boolean value = Boolean.parseBoolean(text); if (value) { cell.setCellValue(""); } else { cell.setCellValue("?"); } } } catch (Exception e) { String message = e.getMessage(); e.printStackTrace(); } }
From source file:com.gtja.qh.TransferCtrl.TransferCtrl.java
private boolean transferToTxt(String inputFilePath, String outFileDir) { File inputFile = new File(inputFilePath); String inputFileName = inputFile.getName(); String extension = inputFileName.lastIndexOf(".") == -1 ? "" : inputFileName.substring(inputFileName.lastIndexOf(".") + 1); StringBuffer input = null;/*from w ww. j av a 2 s .c o m*/ if ("xls".equals(extension)) { //JXL?excel 2003??xlsx? try { //?excel InputStream is = new FileInputStream(inputFilePath); jxl.Workbook rwb = jxl.Workbook.getWorkbook(is); jxl.Sheet rs = rwb.getSheet(0); int rsRows = rs.getRows(); input = new StringBuffer(); for (int i = 1; i < rsRows; i++) { if (rs.getCell(5, i).getContents().equals("")) { String line = "A999@" + rs.getCell(4, i).getContents() + "@" + rs.getCell(6, i).getContents() + "\r\n"; line = line.replaceAll(",", ""); input.append(line); } else { continue; } } } catch (Exception e) { e.printStackTrace(); return false; } } else if ("xlsx".equals(extension)) { //POI ?excel 2007,??excel 2003 try { InputStream fs = new FileInputStream(inputFilePath); XSSFWorkbook wb; wb = new XSSFWorkbook(fs); XSSFSheet sheet = wb.getSheetAt(0); int rows = sheet.getPhysicalNumberOfRows(); input = new StringBuffer(); for (int i = 1; i < rows; i++) { Row row = sheet.getRow(i); if (row == null) { continue; } if (row.getCell(5).getStringCellValue().equals("")) { if (row.getCell(4) == null) { row.createCell(4); row.getCell(4).setCellValue(""); } if (row.getCell(6) == null) { row.createCell(6); row.getCell(6).setCellValue(""); } String tradeCode = row.getCell(4).getStringCellValue(); Double amount = null; String amt = null; if (row.getCell(6).getCellType() == CELL_TYPE_NUMERIC) { amount = row.getCell(6).getNumericCellValue(); amt = new DecimalFormat("0.00").format(amount); } else { if (row.getCell(6).getStringCellValue().length() == 0) { amount = null; amt = ""; } else { amount = new DecimalFormat("0.00").parse(row.getCell(6).getStringCellValue()) .doubleValue(); //String?Double amt = amount.toString(); } } String line = "A999@" + tradeCode + "@" + amt + "\r\n"; line = line.replaceAll(",", ""); input.append(line); } else { continue; } } } catch (Exception e) { e.printStackTrace(); return false; } } try { // String fileName = "0004_00000001_" + frame.getDate().getText() + "_DailyFundChg"; String outFile = outFileDir + "\\" + fileName + ".txt"; File file = new File(outFile); if (!file.exists()) { file.createNewFile(); } // OutputStream os = new FileOutputStream(file); os.write(input.toString().getBytes()); os.flush(); os.close(); return true; } catch (Exception e) { e.printStackTrace(); JOptionPane.showMessageDialog(null, "???", "?", JOptionPane.ERROR_MESSAGE); return false; } }
From source file:com.gtja.qh.TransferCtrl.TransferCtrl.java
private boolean transferToDbf(String inputFilePath, String outFileDir) throws DBFException { //DBF,?// w w w .java 2s . co m DBFField[] fields = new DBFField[6]; fields[0] = new DBFField(); fields[0].setName("ACCOUNTID"); fields[0].setDataType(DBFField.FIELD_TYPE_C); fields[0].setFieldLength(6); fields[1] = new DBFField(); fields[1].setName("PARTID"); fields[1].setDataType(DBFField.FIELD_TYPE_C); fields[1].setFieldLength(4); fields[2] = new DBFField(); fields[2].setName("CLIENTID"); fields[2].setDataType(DBFField.FIELD_TYPE_C); fields[2].setFieldLength(8); fields[3] = new DBFField(); fields[3].setName("AMOUNT"); fields[3].setDataType(DBFField.FIELD_TYPE_N); fields[3].setFieldLength(23); fields[3].setDecimalCount(2); fields[4] = new DBFField(); fields[4].setName("MONEYTYPE"); fields[4].setDataType(DBFField.FIELD_TYPE_C); fields[4].setFieldLength(4); fields[5] = new DBFField(); fields[5].setName("TYPEMEMO"); fields[5].setDataType(DBFField.FIELD_TYPE_C); fields[5].setFieldLength(40); DBFWriter writer = new DBFWriter(); try { writer.setFields(fields); } catch (Exception e) { e.printStackTrace(); return false; } //excel?? File inputFile = new File(inputFilePath); String inputFileName = inputFile.getName(); String extension = inputFileName.lastIndexOf(".") == -1 ? "" : inputFileName.substring(inputFileName.lastIndexOf(".") + 1); if ("xls".equals(extension)) { //JXL?excel 2003??xlsx? try { //?excel InputStream is = new FileInputStream(inputFilePath); jxl.Workbook rwb = jxl.Workbook.getWorkbook(is); jxl.Sheet rs = rwb.getSheet(0); int rsRows = rs.getRows(); for (int i = 1; i < rsRows; i++) { if (rs.getCell(5, i).getContents().equals("")) { Object[] rowData = new Object[6]; rowData[0] = "000101"; rowData[1] = "0001"; rowData[4] = "A999"; String tradeCode = rs.getCell(4, i).getContents(); Double amount = null; if (rs.getCell(6, i).getType() == CellType.NUMBER) { NumberCell numberCell = (NumberCell) rs.getCell(6, i); amount = numberCell.getValue(); } else { if (rs.getCell(6, i).getContents().length() == 0) { amount = null; } else { amount = new DecimalFormat("0.00").parse(rs.getCell(6, i).getContents()) .doubleValue(); //String?Double } } String typeMemo = rs.getCell(7, i).getContents(); rowData[2] = tradeCode; rowData[3] = amount; rowData[5] = typeMemo; writer.addRecord(rowData); } else { continue; } } } catch (Exception e) { e.printStackTrace(); return false; } } else if ("xlsx".equals(extension)) { //POI ?excel 2007,??excel 2003 try { InputStream fs = new FileInputStream(inputFilePath); XSSFWorkbook wb; wb = new XSSFWorkbook(fs); //wb = new XSSFWorkbook(inputFilePath); XSSFSheet sheet = wb.getSheetAt(0); int rows = sheet.getPhysicalNumberOfRows(); for (int i = 1; i < rows; i++) { Row row = sheet.getRow(i); if (row == null) { continue; } if (row.getCell(5).getStringCellValue().equals("")) { Object[] rowData = new Object[6]; rowData[0] = "000101"; rowData[1] = "0001"; rowData[4] = "A999"; if (row.getCell(4) == null) { row.createCell(4); row.getCell(4).setCellValue(""); } if (row.getCell(6) == null) { row.createCell(6); row.getCell(6).setCellValue(""); } if (row.getCell(7) == null) { row.createCell(7); row.getCell(7).setCellValue(""); } String tradeCode = row.getCell(4).getStringCellValue(); Double amount = null; if (row.getCell(6).getCellType() == CELL_TYPE_NUMERIC) { amount = row.getCell(6).getNumericCellValue(); } else { row.getCell(6).setCellType(CELL_TYPE_STRING); if (row.getCell(6).getStringCellValue().length() == 0) { amount = null; } else { amount = new DecimalFormat("0.00").parse(row.getCell(6).getStringCellValue()) .doubleValue(); } } String typeMemo = row.getCell(7).getStringCellValue(); rowData[2] = tradeCode; rowData[3] = amount; rowData[5] = typeMemo; writer.addRecord(rowData); } else { continue; } } } catch (Exception e) { e.printStackTrace(); return false; } } try { // String fileName = "0001_SG01_" + frame.getDate().getText() + "_1_ClientCapitalDetail"; String outFile = outFileDir + "\\" + fileName + ".dbf"; File file = new File(outFile); if (!file.exists()) { file.createNewFile(); } OutputStream os = new FileOutputStream(file); writer.write(os); //? os.close(); return true; } catch (Exception e) { e.printStackTrace(); JOptionPane.showMessageDialog(null, "???", "?", JOptionPane.ERROR_MESSAGE); return false; } }
From source file:com.mycompany.fyp_emr.ExcelReaders.java
public static Diagnostic_Algorithm ReadFullGraph(String file) throws IOException { Diagnostic_Algorithm DA = new Diagnostic_Algorithm(); FileInputStream fs = new FileInputStream(file); XSSFWorkbook wb = new XSSFWorkbook(fs); XSSFSheet sheet = wb.getSheetAt(0); XSSFRow row;// w w w .j av a 2 s . c o m int id, id1, id2; String label, type, color, available, label_e; Node n; Edge e; LinkedList<Edge> edges = new LinkedList<Edge>(); int rows = sheet.getPhysicalNumberOfRows(); for (int r = 2; r < rows; r++) { row = sheet.getRow(r); if (row != null) { if (row.getCell(0) != null) { row.getCell(0).setCellType(Cell.CELL_TYPE_NUMERIC); id = (int) row.getCell(0).getNumericCellValue(); if (id != 0) { label = row.getCell(1).getStringCellValue(); type = row.getCell(2).getStringCellValue(); color = row.getCell(3).getStringCellValue(); available = row.getCell(4).getStringCellValue(); n = new Node(); n.setID(id); n.setLabel(label); n.setType(type); n.setAvailable(available); n.setColor(color); DA.addNode(n); } } if (row.getCell(6) != null) { row.getCell(6).setCellType(Cell.CELL_TYPE_NUMERIC); row.getCell(7).setCellType(Cell.CELL_TYPE_NUMERIC); id1 = (int) row.getCell(6).getNumericCellValue(); id2 = (int) row.getCell(7).getNumericCellValue(); label_e = row.getCell(8).getStringCellValue(); e = new Edge(id1, id2); e.setLabel(label_e); edges.add(e); } } } for (int i = 0; i < edges.size(); i++) DA.addEdge(edges.get(i)); return DA; }
From source file:com.mycompany.owl.fxml.FXMLFilterController.java
@FXML public void saveFileTransformed() throws FileNotFoundException, IOException { FileInputStream fileInputStream = new FileInputStream(file); XSSFWorkbook workbookToModify = new XSSFWorkbook(fileInputStream); XSSFSheet sheet = workbookToModify.getSheetAt(0); XSSFRow row;/*from www . j av a 2 s.c o m*/ String atcMask = getATCMask(); ArrayList<String> firstRowCells = new ArrayList<>(); for (int i = 0; i <= sheet.getLastRowNum(); i++) { row = sheet.getRow(i); for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) { System.out.print(row.getCell(j).getRawValue() + "\t"); if (i == 0) firstRowCells.add(row.getCell(j).getRawValue()); } System.out.println(""); } XSSFWorkbook transformedWB = new XSSFWorkbook(); transformedWB.createSheet(); XSSFSheet transformedS = transformedWB.getSheetAt(0); for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) { transformedS.createRow(i); } /* elkezdnk vgigmenni az alap sheeten ha megvan az index, ahol van match, akkor createrow(0) s bele a tbbit 0. helyre */ ArrayList<Integer> matchingIndexes = matchingIndexes(firstRowCells); for (int i = 0; i <= sheet.getLastRowNum(); i++) { row = sheet.getRow(i); int sum = 0; for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) { if (i != 0 && j != 0) { for (int index : matchingIndexes) { if (index == j) { sum += Integer.valueOf(row.getCell(j).getRawValue()); } } } } System.out.println(sum); if (i > 0) { row = sheet.getRow(i); row.createCell(row.getLastCellNum()).setCellValue(sum); } } for (int index : matchingIndexes) { for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) { row = sheet.getRow(i); row.getCell(index).setCellValue(3.14159); } /*for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) { if(i == index){ for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) { row = sheet.getRow(j); transformedS.getRow(j).createCell(transformedColumnCount).setCellValue( row.getCell(i).getRawValue() ); } transformedColumnCount++; } }*/ } int columnsInTransformed = 0; for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) { row = sheet.getRow(i); for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) { String cellValue = row.getCell(j).getRawValue(); if (!cellValue.equals("3.14159")) { transformedS.getRow(i).createCell(columnsInTransformed); transformedS.getRow(i).getCell(columnsInTransformed).setCellValue(cellValue); columnsInTransformed++; } } columnsInTransformed = 0; } row = transformedS.getRow(0); row.createCell(row.getLastCellNum()).setCellValue(atcMask); File file = fileChooser.showSaveDialog(new Stage()); if (file != null) { try { FileOutputStream fop = new FileOutputStream(file); transformedWB.write(fop); fop.close(); } catch (Exception e) { System.out.println("Exception: " + e.getMessage()); } } }
From source file:com.photon.phresco.framework.commons.FrameworkUtil.java
License:Apache License
public void addNew(String filePath, String testName, String cellValue[]) throws PhrescoException { try {/*w w w .j a v a 2 s .co m*/ //FileInputStream myInput = new FileInputStream(filePath); int numCol; int cellno = 0; CellStyle tryStyle[] = new CellStyle[20]; String sheetName = testName; //String cellValue[] = {"","",testName,success, fail,"","","",total,testCoverage,"","",""}; Iterator<Row> rowIterator; File testDir = new File(filePath); StringBuilder sb = new StringBuilder(filePath); if (testDir.isDirectory()) { FilenameFilter filter = new PhrescoFileFilter("", "xlsx"); File[] listFiles = testDir.listFiles(filter); if (listFiles.length != 0) { for (File file1 : listFiles) { if (file1.isFile()) { sb.append(File.separator); sb.append(file1.getName()); break; } } FileInputStream myInput = new FileInputStream(sb.toString()); OPCPackage opc = OPCPackage.open(myInput); XSSFWorkbook myWorkBook = new XSSFWorkbook(opc); XSSFSheet mySheet = myWorkBook.getSheetAt(0); rowIterator = mySheet.rowIterator(); numCol = 13; Row next; for (Cell cell : mySheet.getRow((mySheet.getLastRowNum()) - 2)) { tryStyle[cellno] = cell.getCellStyle(); cellno = cellno + 1; } do { int flag = 0; next = rowIterator.next(); if ((mySheet.getSheetName().trim()).equalsIgnoreCase("Index") && ((mySheet.getLastRowNum() - next.getRowNum()) < 3)) { for (Cell cell : next) { cell.setCellType(1); if (cell.getStringCellValue().equalsIgnoreCase("total")) { mySheet.shiftRows((mySheet.getLastRowNum() - 1), (mySheet.getPhysicalNumberOfRows() - 1), 1); flag = 1; } if (flag == 1) break; } if (flag == 1) break; } } while (rowIterator.hasNext()); Row r = null; if ((mySheet.getSheetName().trim()).equalsIgnoreCase("Index")) { r = mySheet.createRow(next.getRowNum() - 1); } else { r = mySheet.createRow(next.getRowNum() + 1); } for (int i = 0; i < numCol; i++) { Cell cell = r.createCell(i); cell.setCellValue(cellValue[i]); // used only when sheet is 'index' if (i == 2) sheetName = cellValue[i]; cell.setCellStyle(tryStyle[i]); } if ((mySheet.getSheetName().trim()).equalsIgnoreCase("Index")) { Sheet fromSheet = myWorkBook.getSheetAt((myWorkBook.getNumberOfSheets() - 1)); Sheet toSheet = myWorkBook.createSheet(sheetName); int i = 0; Iterator<Row> copyFrom = fromSheet.rowIterator(); Row fromRow, toRow; CellStyle newSheetStyle[] = new CellStyle[20]; Integer newSheetType[] = new Integer[100]; String newSheetValue[] = new String[100]; do { fromRow = copyFrom.next(); if (fromRow.getRowNum() == 24) { break; } toRow = toSheet.createRow(i); int numCell = 0; for (Cell cell : fromRow) { Cell newCell = toRow.createCell(numCell); cell.setCellType(1); newSheetStyle[numCell] = cell.getCellStyle(); newCell.setCellStyle(newSheetStyle[numCell]); newSheetType[numCell] = cell.getCellType(); newCell.setCellType(newSheetType[numCell]); if (fromRow.getCell(0).getStringCellValue().length() != 1 && fromRow.getCell(0).getStringCellValue().length() != 2 && fromRow.getCell(0).getStringCellValue().length() != 3) { newSheetValue[numCell] = cell.getStringCellValue(); newCell.setCellValue(newSheetValue[numCell]); } numCell = numCell + 1; } i = i + 1; } while (copyFrom.hasNext()); } // write to file FileOutputStream fileOut = new FileOutputStream(sb.toString()); myWorkBook.write(fileOut); myInput.close(); fileOut.close(); } else { FilenameFilter xlsFilter = new PhrescoFileFilter("", "xls"); File[] xlsListFiles = testDir.listFiles(xlsFilter); if (xlsListFiles.length != 0) { for (File file2 : xlsListFiles) { if (file2.isFile()) { sb.append(File.separator); sb.append(file2.getName()); break; } } FileInputStream myInput = new FileInputStream(sb.toString()); HSSFWorkbook myWorkBook = new HSSFWorkbook(myInput); HSSFSheet mySheet = myWorkBook.getSheetAt(0); rowIterator = mySheet.rowIterator(); numCol = 13; Row next; for (Cell cell : mySheet.getRow((mySheet.getLastRowNum()) - 2)) { tryStyle[cellno] = cell.getCellStyle(); cellno = cellno + 1; } do { int flag = 0; next = rowIterator.next(); if ((mySheet.getSheetName().trim()).equalsIgnoreCase("Index") && ((mySheet.getLastRowNum() - next.getRowNum()) < 3)) { for (Cell cell : next) { cell.setCellType(1); if (cell.getStringCellValue().equalsIgnoreCase("total")) { mySheet.shiftRows((mySheet.getLastRowNum() - 1), (mySheet.getPhysicalNumberOfRows() - 1), 1); flag = 1; } if (flag == 1) break; } if (flag == 1) break; } } while (rowIterator.hasNext()); Row r = null; if ((mySheet.getSheetName().trim()).equalsIgnoreCase("Index")) { r = mySheet.createRow(mySheet.getLastRowNum() - 2); } else { r = mySheet.createRow(next.getRowNum() + 1); } for (int i = 0; i < numCol; i++) { Cell cell = r.createCell(i); cell.setCellValue(cellValue[i]); // used only when sheet is 'index' if (i == 2) sheetName = cellValue[i]; cell.setCellStyle(tryStyle[i]); } if ((mySheet.getSheetName().trim()).equalsIgnoreCase("Index")) { Sheet fromSheet = myWorkBook.getSheetAt((myWorkBook.getNumberOfSheets() - 1)); Sheet toSheet = myWorkBook.createSheet(sheetName); int i = 0; Iterator<Row> copyFrom = fromSheet.rowIterator(); Row fromRow, toRow; CellStyle newSheetStyle[] = new CellStyle[20]; Integer newSheetType[] = new Integer[100]; String newSheetValue[] = new String[100]; do { fromRow = copyFrom.next(); if (fromRow.getRowNum() == 24) { break; } toRow = toSheet.createRow(i); int numCell = 0; for (Cell cell : fromRow) { Cell newCell = toRow.createCell(numCell); cell.setCellType(1); newSheetStyle[numCell] = cell.getCellStyle(); newCell.setCellStyle(newSheetStyle[numCell]); newSheetType[numCell] = cell.getCellType(); newCell.setCellType(newSheetType[numCell]); if (fromRow.getCell(0).getStringCellValue().length() != 1 && fromRow.getCell(0).getStringCellValue().length() != 2 && fromRow.getCell(0).getStringCellValue().length() != 3) { newSheetValue[numCell] = cell.getStringCellValue(); newCell.setCellValue(newSheetValue[numCell]); } numCell = numCell + 1; if (numCell == 15) { break; } } i = i + 1; } while (copyFrom.hasNext()); } // write to file FileOutputStream fileOut = new FileOutputStream(sb.toString()); myWorkBook.write(fileOut); myInput.close(); fileOut.close(); } else { FilenameFilter odsFilter = new PhrescoFileFilter("", "ods"); File[] odsListFiles = testDir.listFiles(odsFilter); for (File file1 : odsListFiles) { if (file1.isFile()) { sb.append(File.separator); sb.append(file1.getName()); break; } } File file = new File(sb.toString()); addTestSuiteToOds(file, cellValue); } } } } catch (Exception e) { // throw new PhrescoException(e); } }
From source file:com.phucdk.emailsender.utils.ExcelUtils.java
private static int getNumberOfRows(XSSFSheet sheet) { return sheet.getPhysicalNumberOfRows(); }
From source file:com.phucdk.emailsender.utils.ExcelUtils.java
private static int getNumberOfRows(XSSFWorkbook myWorkBook) { XSSFSheet sheet = myWorkBook.getSheetAt(1); return sheet.getPhysicalNumberOfRows(); }