List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getPhysicalNumberOfRows
@Override public int getPhysicalNumberOfRows()
From source file:com.ms.commons.test.datareader.impl.ExcelReadUtil.java
License:Open Source License
/** * A column means an item in table. With horizontal style, in contrast, a row represents an item in table. * /*from w w w. j a va2s.c om*/ * @author Qiu Shuo */ private static MemoryTable readSheetWithVerticalStyle(String name, HSSFSheet sheet) { MemoryTable table = new MemoryTable(name.trim()); List<MemoryRow> itemList = new ArrayList<MemoryRow>(); int maxRows = sheet.getPhysicalNumberOfRows(); int maxItemNumPlusOne = 0; // get maxItemNumPlusOne { for (int i = 0; i < maxRows; i++) { HSSFRow row = sheet.getRow(i); int cur = row.getLastCellNum(); maxItemNumPlusOne = (cur > maxItemNumPlusOne) ? cur : maxItemNumPlusOne; } } for (int i = 0; i < maxRows; i++) { HSSFRow row = sheet.getRow(i); HSSFCell columnNameCell = row.getCell((short) 0); String columnName = columnNameCell.getRichStringCellValue().getString(); for (short j = 1; j < maxItemNumPlusOne; j++) { HSSFCell cell = row.getCell(j); MemoryField field = readCellValue(columnName, cell); while (itemList.size() <= j - 1) { itemList.add(new MemoryRow(new ArrayList<MemoryField>())); } MemoryRow item = itemList.get(j - 1); item.getFieldList().add(field); } } table.setRowList(itemList); return table; }
From source file:com.qms.operations.rates.ejb.sls.BuyRatesSessionBean.java
License:Open Source License
public String processExcel(String fileName, ESupplyGlobalParameters loginBean) throws EJBException { long startTime = System.currentTimeMillis(); String s1 = ""; PreparedStatement pstmtMainHeader = null; PreparedStatement pstmtDetails = null; PreparedStatement pstmtDetailsData = null; PreparedStatement MainHeader = null; PreparedStatement Details = null; PreparedStatement DetailsData = null; Connection connection = null; OperationsImpl operationsImpl = null; String dateFormat = null;/*w w w. j a v a 2 s . c o m*/ String deleteDtldata = "DELETE FROM QMS_STG_BUYRATES_DETAILS_DATA"; String deleteDtl = "DELETE FROM QMS_STG_BUYRATES_DETAILS"; String deleteHeader = "DELETE FROM QMS_STG_BUYRATES_MAIN_HEADER"; String selectMainHeader = "SELECT WEIGHT_BREAK FROM QMS_STG_BUYRATES_MAIN_HEADER"; ResultSet rs = null; PreparedStatement pstmtHeader = null; //@@Added by Kameswari on 15/04/09 String wtbreak = null; String returnstr = null; CallableStatement cstmt = null; double time = 0.0; String data = null; try { String s = ""; String mainHeaderData[] = new String[8]; int rows = 0; int cols = 0; int notesIndex = 0; int batchCount = 0; HSSFRow row = null; HSSFCell cell = null; InputStream myxls = new FileInputStream(fileName); HSSFWorkbook wb = new HSSFWorkbook(myxls); HSSFSheet sheet = wb.getSheetAt(0); rows = sheet.getPhysicalNumberOfRows(); dateFormat = loginBean.getUserPreferences().getDateFormat(); String queryMainHeader = "INSERT INTO QMS_STG_BUYRATES_MAIN_HEADER (SHIPMENT_MODE,CURRENCY,WEIGHT_BREAK,WEIGHT_CLASS,RATE_TYPE,UOM,CONSOLETYPE,DENSITY_RATIO,TERMINALID) VALUES (?,?,?,?,?,?,?,?,?)"; // String queryDetails = "INSERT INTO QMS_STG_BUYRATES_DETAILS (ROW_ID,ORIGIN,DESTINATION,CARRIER_ID,SERVICELEVEL,FREQUENCY,TRANSIT,EFFECTIVE_FROM,VALID_UPTO,NOTES) VALUES (?,?,?,?,?,?,?,?,?,?)"; String queryDetails = "INSERT INTO QMS_STG_BUYRATES_DETAILS (ROW_ID,ORIGIN,DESTINATION,CARRIER_ID,SERVICELEVEL,FREQUENCY,TRANSIT,EFFECTIVE_FROM,VALID_UPTO,NOTES) VALUES (?,?,?,?,?,?,?,TO_DATE(?),TO_DATE(?),?)"; String queryDetailsData = "INSERT INTO QMS_STG_BUYRATES_DETAILS_DATA (ROW_ID,LINE_NO,CONTAINER_NO,CONTAINERS_VALUE,LOWER_BOUND,UPPER_BOUND,RATE_DESCRIPTION) VALUES (?,?,?,?,?,?,?)"; String msgquery = "SELECT ERROR_MSG FROM QMS_STG_BUYRATES_DETAILS"; // connection=ConnectionUtil.getConnection(); operationsImpl = new OperationsImpl(); connection = operationsImpl.getConnection(); DetailsData = connection.prepareStatement(deleteDtldata); Details = connection.prepareStatement(deleteDtl); MainHeader = connection.prepareStatement(deleteHeader); DetailsData.execute(); Details.execute(); MainHeader.execute(); // long startTime1=System.currentTimeMillis(); //For MainHeader row = sheet.getRow(1); for (int k = 0; k < 8; k++) { cell = row.getCell((short) k); mainHeaderData[k] = getCellData(cell, 0); } pstmtMainHeader = connection.prepareStatement(queryMainHeader); insertMainHeaderData(mainHeaderData, pstmtMainHeader, loginBean); //End pstmtHeader = connection.prepareStatement(selectMainHeader); rs = pstmtHeader.executeQuery(); if (rs.next()) { wtbreak = rs.getString("WEIGHT_BREAK"); } //To get columns count , column index for NOTES and containers ids row = sheet.getRow(3); cols = row.getPhysicalNumberOfCells(); int l = cols - 1; for (; l >= 0; l--) { cell = row.getCell((short) l); if ("NOTES".equalsIgnoreCase(getCellData(cell, l))) break; } notesIndex = l + 1; String detailsData[] = new String[notesIndex]; String containersData[] = new String[notesIndex - 9]; for (l = 8; l < notesIndex - 1; l++) { cell = row.getCell((short) l); containersData[l - 8] = getCellData(cell, l); } //End //For Details pstmtDetails = connection.prepareStatement(queryDetails); ; pstmtDetailsData = connection.prepareStatement(queryDetailsData); for (int i = 4; i <= rows; i++) { row = sheet.getRow(i); //cols=row.getPhysicalNumberOfCells(); //s=row.getRowNum() + " : "; for (int j = 0; j < notesIndex; j++) { if (row != null) { cell = row.getCell((short) j); detailsData[j] = getCellData(cell, j); } // s = s+ " -- " + detailsData[j]; if (j == 6 && (cell.CELL_TYPE_NUMERIC == cell.getCellType()) && (detailsData[j].indexOf(".") != -1) && (Double.parseDouble(detailsData[j].substring(0, detailsData[j].indexOf("."))) < 1)) { time = 1 / Double.parseDouble(detailsData[j]); Double t = new Double(time); if (t.toString().indexOf(".") != -1) { data = t.toString().substring(0, t.toString().indexOf(".")); } if (data != null) { detailsData[j] = data + ":" + "00"; } } if (j == 6 && (cell.CELL_TYPE_NUMERIC == cell.getCellType()) && (detailsData[j].indexOf(".") != -1) && (!(Double .parseDouble(detailsData[j].substring(0, detailsData[j].indexOf("."))) < 1))) { if (detailsData[j].indexOf(".") != -1) { detailsData[j] = detailsData[j].substring(0, detailsData[j].indexOf(".")); } } if (j == 5 && detailsData[j].indexOf(".") != -1) { detailsData[j] = detailsData[j].substring(0, detailsData[j].indexOf(".")); } } //long EndTime1=System.currentTimeMillis(); //System.out.println("\n------->>>> M.SEC : "+(EndTime1-startTime1) +" SEC : " + (EndTime1-startTime1)/(1000) + " MIN : "+(EndTime1-startTime1)/(1000*60)); batchCount++; insertDetails(detailsData, containersData, pstmtDetails, pstmtDetailsData, notesIndex, i, dateFormat, wtbreak); if (batchCount == 150) { pstmtDetails.executeBatch(); pstmtDetailsData.executeBatch(); pstmtDetails.clearBatch(); pstmtDetailsData.clearBatch(); batchCount = 0; } // s1=s1+s + "\n"; } if (batchCount > 0) { pstmtDetails.executeBatch(); pstmtDetailsData.executeBatch(); pstmtDetails.clearBatch(); pstmtDetailsData.clearBatch(); batchCount = 0; } cstmt = connection.prepareCall("{ ?=call QMS_BUY_RATES_UPLOAD_PKG.BUY_RATES_PROC(?)}"); cstmt.registerOutParameter(1, Types.VARCHAR); cstmt.setString(2, loginBean.getTerminalId()); cstmt.execute(); returnstr = (String) cstmt.getString(1); //End return returnstr; } catch (Exception e) { logger.error("Exception"); e.printStackTrace(); } finally { try { if (pstmtMainHeader != null) pstmtMainHeader.close(); if (pstmtDetails != null) pstmtDetails.close(); if (pstmtDetailsData != null) pstmtDetailsData.close(); if (cstmt != null) cstmt.close(); if (connection != null) connection.close(); if (rs != null)//Added by govind on 16-02-2010 for Connection Leakages rs.close(); if (pstmtHeader != null)//Added by govind on 16-02-2010 for Connection Leakages pstmtHeader.close(); if (MainHeader != null)//Added by govind on 16-02-2010 for Connection Leakages MainHeader.close(); if (DetailsData != null) //Added by govind on 16-02-2010 for Connection Leakages DetailsData.close(); if (Details != null)//Added by govind on 16-02-2010 for Connection Leakages Details.close(); } catch (Exception e) { logger.error("Error Occured while closing Resources" + e); } } long EndTime = System.currentTimeMillis(); s1 = " M.SEC : " + (EndTime - startTime) + " SEC : " + (EndTime - startTime) / (1000) + " MIN : " + (EndTime - startTime) / (1000 * 60); return s1; }
From source file:com.qms.operations.rates.ejb.sls.BuyRatesSessionBean.java
License:Open Source License
public String processExcelDelete(String fileName) throws EJBException { String s1 = ""; PreparedStatement pstmtDetails = null; // PreparedStatement pstmtDetails1 = null;Commented by govind on 16-02-2010 for connectionLeakages PreparedStatement Details = null; // PreparedStatement Details1 = null;Commented by govind on 16-02-2010 for connectionLeakages Connection connection = null; OperationsImpl operationsImpl = null; String deleteDtl = "TRUNCATE TABLE QMS_BUYRATES_DELETE_DETAILS"; // String deleteDtl1 = "DELETE FROM QMS_BUYRATES_DELETE_DATA"; String queryDetails = "INSERT INTO QMS_BUYRATES_DELETE_DETAILS (ROW_ID,ORIGIN,DESTINATION,CARRIER_ID,SERVICE_LEVEL,FREQUENCY,TRANSIT_TIME,CURRENCY,TERMINAL_ID,DENSITY_CODE) VALUES (?,?,?,?,?,?,?,?,?,?)"; // String queryDetails1 = "INSERT INTO QMS_BUYRATES_DELETE_DATA (ROW_ID,LINE_NO,CONTAINER_NO,CONTAINER_VALUE) VALUES (?,?,?,?)"; String msgquery = "SELECT ERROR_MSG FROM QMS_BUYRATES_DELETE_DETAILS"; // ResultSet rs = null;Commeneted by Govind on 16-02-2010 for Connection Leakages String returnstr = null;//from www. j a va 2s . co m CallableStatement cstmt = null; try { String s = ""; String mainHeaderData[] = new String[8]; int rows = 0; int cols = 0; int notesIndex = 0; int batchCount = 0; HSSFRow row = null; HSSFCell cell = null; InputStream myxls = new FileInputStream(fileName); HSSFWorkbook wb = new HSSFWorkbook(myxls); HSSFSheet sheet = wb.getSheetAt(0); rows = sheet.getPhysicalNumberOfRows(); // connection=ConnectionUtil.getConnection(); operationsImpl = new OperationsImpl(); connection = operationsImpl.getConnection(); Details = connection.prepareStatement(deleteDtl); Details.execute(); // Details1 = connection.prepareStatement(deleteDtl1); // Details1.execute(); row = sheet.getRow(0); cols = row.getPhysicalNumberOfCells(); int l = cols - 1; for (; l >= 0; l--) { cell = row.getCell((short) l); if ("TERMINALID:".equalsIgnoreCase(getCellData(cell, 0))) break; } notesIndex = l + 1; // notesIndex=cols; String detailsData[] = new String[notesIndex]; /*String containersData[] =new String[notesIndex-7]; for(l=7;l<notesIndex-2;l++) { cell=row.getCell((short)l); containersData[l-7]=getCellData(cell); }*/ pstmtDetails = connection.prepareStatement(queryDetails); // pstmtDetails1= connection.prepareStatement(queryDetails1); for (int i = 1; i < rows; i++) { row = sheet.getRow(i); double time = 0.0; String data = null; for (int j = 0; j < notesIndex; j++) { if (row != null) { cell = row.getCell((short) j); /* { cell.setCellType(cell.CELL_TYPE_STRING); }*/ detailsData[j] = getCellData(cell, 0); if (j == 4) // Added by Gowtham. { if (detailsData[j].toString().indexOf(".") != -1) detailsData[j] = detailsData[j].substring(0, detailsData[j].toString().indexOf(".")); } if (j == 6 && (cell.CELL_TYPE_NUMERIC == cell.getCellType()) && (detailsData[j].indexOf(".") != -1) && (Double.parseDouble( detailsData[j].substring(0, detailsData[j].indexOf("."))) < 1)) { time = 1 / Double.parseDouble(detailsData[j]); Double t = new Double(time); if (t.toString().indexOf(".") != -1) { data = t.toString().substring(0, t.toString().indexOf(".")); } if (data != null) { detailsData[j] = data + ":" + "00"; } } if (j == 6 && (cell.CELL_TYPE_NUMERIC == cell.getCellType()) && (detailsData[j].indexOf(".") != -1) && (!(Double.parseDouble( detailsData[j].substring(0, detailsData[j].indexOf("."))) < 1))) { if (detailsData[j].indexOf(".") != -1) { detailsData[j] = detailsData[j].substring(0, detailsData[j].indexOf(".")); } } if (j == 5 && detailsData[j].indexOf(".") != -1) { detailsData[j] = detailsData[j].substring(0, detailsData[j].indexOf(".")); } /*if(j==8&&detailsData[j].indexOf(".")!=-1) { detailsData[j] =detailsData[j].substring(0, detailsData[j].indexOf(".")); }*/ } } batchCount++; insertDeleteDetails(detailsData, pstmtDetails, notesIndex, i); if (batchCount == 150) { pstmtDetails.executeBatch(); pstmtDetails.clearBatch(); batchCount = 0; } } if (batchCount > 0) { pstmtDetails.executeBatch(); pstmtDetails.clearBatch(); batchCount = 0; } cstmt = connection.prepareCall("{ ?=call QMS_BUY_RATES_UPLOAD_PKG.BUY_RATES_DELETE_PROC}"); cstmt.registerOutParameter(1, Types.VARCHAR); cstmt.execute(); returnstr = (String) cstmt.getString(1); //End return returnstr; } catch (Exception e) { logger.error("Exception"); e.printStackTrace(); } finally { try { /* if(rs!=null) rs.close();-*/ //Commented by govind on 16-02-2010 fro connection leakages. if (pstmtDetails != null) pstmtDetails.close(); if (Details != null) Details.close(); if (cstmt != null) cstmt.close(); if (connection != null) connection.close(); } catch (Exception e) { logger.error("Error Occured while closing Resources" + e); } } return s1; }
From source file:com.report.excel.ExcelToHtmlConverter.java
License:Apache License
protected void processSheet(HSSFSheet sheet) { //processSheetHeader( htmlDocumentFacade.getBody(), sheet ); sheet.setAutobreaks(true);//www . java 2 s . co m final int physicalNumberOfRows = sheet.getPhysicalNumberOfRows(); if (physicalNumberOfRows <= 0) return; Element table = htmlDocumentFacade.createTable(); htmlDocumentFacade.addStyleClass(table, cssClassPrefixTable, "border-collapse:collapse;border-spacing:0;"); Element tableBody = htmlDocumentFacade.createTableBody(); final CellRangeAddress[][] mergedRanges = ExcelToHtmlUtils.buildMergedRangesMap(sheet); final List<Element> emptyRowElements = new ArrayList<Element>(physicalNumberOfRows); int maxSheetColumns = 1; for (int r = sheet.getFirstRowNum(); r <= sheet.getLastRowNum(); r++) { HSSFRow row = sheet.getRow(r); if (row == null) continue; if (!isOutputHiddenRows() && row.getZeroHeight()) continue; Element tableRowElement = htmlDocumentFacade.createTableRow(); htmlDocumentFacade.addStyleClass(tableRowElement, cssClassPrefixRow, "height:" + (row.getHeight() / 20f) + "pt;"); int maxRowColumnNumber = processRow(mergedRanges, row, tableRowElement); if (maxRowColumnNumber == 0) { emptyRowElements.add(tableRowElement); } else { if (!emptyRowElements.isEmpty()) { for (Element emptyRowElement : emptyRowElements) { tableBody.appendChild(emptyRowElement); } emptyRowElements.clear(); } tableBody.appendChild(tableRowElement); } maxSheetColumns = Math.max(maxSheetColumns, maxRowColumnNumber); } processColumnWidths(sheet, maxSheetColumns, table); if (isOutputColumnHeaders()) { //processColumnHeaders( sheet, maxSheetColumns, table ); } table.appendChild(tableBody); htmlDocumentFacade.getBody().appendChild(table); }
From source file:com.ro.ssc.app.client.utils.ExcelReader.java
/** * * @param file//from www . j a v a 2 s . com * @return */ public static Map<String, User> readExcel(File file) { Map<String, User> result = new HashMap<>(); List<Event> events; try { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row; HSSFCell cell; int rows; // No of rows rows = sheet.getPhysicalNumberOfRows(); int cols = 0; // No of columns int tmp; // This trick ensures that we get the data properly even if it doesn't start from first few rows for (int i = 0; i < 10 || i < rows; i++) { row = sheet.getRow(i); if (row != null) { tmp = sheet.getRow(i).getPhysicalNumberOfCells(); if (tmp > cols) { cols = tmp; } } } DateTimeFormatter dtf = DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss EEEE"); for (int r = 1; r < rows; r++) { row = sheet.getRow(r); if (row != null) { try { String user = WordUtils .capitalizeFully(row.getCell(ExcelEnum.USER_NAME.getAsInteger()).toString().trim()); if (row.getCell(ExcelEnum.PASSED.getAsInteger()).toString().trim().equals("1.0")) { if (result.containsKey(user)) { events = result.get(user).getEvents(); events.add(new Event( DateTime.parse(row.getCell(ExcelEnum.TIMESTAMP.getAsInteger()).toString(), dtf), row.getCell(ExcelEnum.DESCRIPTION.getAsInteger()).toString(), row.getCell(ExcelEnum.ADDRESS.getAsInteger()).toString().trim(), row.getCell(ExcelEnum.PASSED.getAsInteger()).toString().trim() .equals("1.0"))); result.get(user).setEvents(events); } else { events = new ArrayList(); events.add(new Event( DateTime.parse(row.getCell(ExcelEnum.TIMESTAMP.getAsInteger()).toString(), dtf), row.getCell(ExcelEnum.DESCRIPTION.getAsInteger()).toString().trim(), row.getCell(ExcelEnum.ADDRESS.getAsInteger()).toString().trim(), row.getCell(ExcelEnum.PASSED.getAsInteger()).toString().trim() .equals("1.0"))); String id = row.getCell(ExcelEnum.USER_ID.getAsInteger()).toString().trim(); result.put(user, new User(user, id.contains(".") ? id.split("\\.")[0] : id, row.getCell(ExcelEnum.CARD_NO.getAsInteger()).toString().trim(), WordUtils.capitalizeFully( row.getCell(ExcelEnum.DEPARTMENT.getAsInteger()).toString().trim()), events)); } } } catch (Exception e) { log.error("Exception" + e.getMessage()); } } } } catch (Exception ioe) { log.error("Exception" + ioe.getMessage()); } return result; }
From source file:com.schneider.tsm.process.addXPRDataManager.java
private void add2ExistingReport() throws InterruptedException { final SwingWorker worker = new SwingWorker() { @Override//w w w . j av a2s .c o m protected Object doInBackground() throws Exception { try { FileInputStream file = new FileInputStream(new File( "C:\\softwaretest\\FileOutput\\Manager\\NPR_CPR_Report_" + requestorManager + ".xls")); HSSFWorkbook workbook = new HSSFWorkbook(file); HSSFSheet sheet = workbook.getSheetAt(0); int sheetsize = sheet.getPhysicalNumberOfRows(); Cell cell = null; int numm = 1; for (int i = 7; i < sheetsize; i++) { cell = sheet.getRow(i).getCell(1); if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) { cell.setCellValue("" + numm); cell = sheet.getRow(i).getCell(2); cell.setCellValue(requestID); cell = sheet.getRow(i).getCell(3); cell.setCellValue(requestorID); cell = sheet.getRow(i).getCell(4); cell.setCellValue(dayFromSubmit); cell = sheet.getRow(i).getCell(5); cell.setCellValue(requestStatus); cell = sheet.getRow(i).getCell(6); cell.setCellValue(submitDate); cell = sheet.getRow(i).getCell(7); cell.setCellValue(requestType); i = sheetsize; } numm++; } file.close(); FileOutputStream outFile = new FileOutputStream(new File( "C:\\softwaretest\\FileOutput\\Manager\\NPR_CPR_Report_" + requestorManager + ".xls")); workbook.write(outFile); outFile.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return null; } }; worker.execute(); Thread.sleep(160); }
From source file:com.schneider.tsm.process.addXPRDataManager.java
private String getManager() { String manager = "No_Manager_Assigned"; try {/*from w w w. ja v a 2 s . c o m*/ FileInputStream file = new FileInputStream(new File("C:\\softwaretest\\LibraryTest.xls")); HSSFWorkbook workbook = new HSSFWorkbook(file); HSSFSheet sheet = workbook.getSheetAt(0); Cell cell = null; int sheetsize = sheet.getPhysicalNumberOfRows(); for (int i = 1; i < sheetsize; i++) { cell = sheet.getRow(i).getCell(0); if (cell.getStringCellValue().equals(requestorID)) { cell = sheet.getRow(i).getCell(2); manager = cell.getStringCellValue(); } } file.close(); FileOutputStream outFile = new FileOutputStream(new File("C:\\softwaretest\\LibraryTest.xls")); workbook.write(outFile); outFile.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return manager; }
From source file:com.schneider.tsm.process.addXPRDataQuality.java
private void add2ExistingReport() throws InterruptedException { final SwingWorker worker = new SwingWorker() { @Override//from w w w .j a v a2s . c om protected Object doInBackground() throws Exception { try { FileInputStream file = new FileInputStream(new File( "C:\\softwaretest\\FileOutput\\Quality\\NPR_CPR_Report_" + requestorQuality + ".xls")); HSSFWorkbook workbook = new HSSFWorkbook(file); HSSFSheet sheet = workbook.getSheetAt(0); int sheetsize = sheet.getPhysicalNumberOfRows(); Cell cell = null; int numm = 1; for (int i = 7; i < sheetsize; i++) { cell = sheet.getRow(i).getCell(1); if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) { cell.setCellValue("" + numm); cell = sheet.getRow(i).getCell(2); cell.setCellValue(requestID); cell = sheet.getRow(i).getCell(3); cell.setCellValue(requestorID); cell = sheet.getRow(i).getCell(4); cell.setCellValue(dayFromSubmit); cell = sheet.getRow(i).getCell(5); cell.setCellValue(requestStatus); cell = sheet.getRow(i).getCell(6); cell.setCellValue(submitDate); cell = sheet.getRow(i).getCell(7); cell.setCellValue(requestType); i = sheetsize; } numm++; } file.close(); FileOutputStream outFile = new FileOutputStream(new File( "C:\\softwaretest\\FileOutput\\Quality\\NPR_CPR_Report_" + requestorQuality + ".xls")); workbook.write(outFile); outFile.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return null; } }; worker.execute(); Thread.sleep(160); }
From source file:com.schneider.tsm.process.addXPRDataQuality.java
private String getManager() { String manager = null;//ww w. j a v a2 s . c om try { FileInputStream file = new FileInputStream(new File("C:\\softwaretest\\LibraryTest.xls")); HSSFWorkbook workbook = new HSSFWorkbook(file); HSSFSheet sheet = workbook.getSheetAt(0); Cell cell = null; int sheetsize = sheet.getPhysicalNumberOfRows(); for (int i = 1; i < sheetsize; i++) { cell = sheet.getRow(i).getCell(0); if (cell.getStringCellValue().equals(requestorID)) { cell = sheet.getRow(i).getCell(2); manager = cell.getStringCellValue(); } } file.close(); FileOutputStream outFile = new FileOutputStream(new File("C:\\softwaretest\\LibraryTest.xls")); workbook.write(outFile); outFile.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return manager; }
From source file:com.schneider.tsm.process.addXPRDataQuality.java
private String getQuality() { String Quality = "No_Exist"; try {//www . j a v a 2 s. co m FileInputStream file = new FileInputStream(new File("C:\\softwaretest\\LibraryTest.xls")); HSSFWorkbook workbook = new HSSFWorkbook(file); HSSFSheet sheet = workbook.getSheetAt(0); Cell cell = null; int sheetsize = sheet.getPhysicalNumberOfRows(); for (int i = 1; i < sheetsize; i++) { cell = sheet.getRow(i).getCell(0); if (cell.getStringCellValue().equals(requestorID)) { cell = sheet.getRow(i).getCell(4); Quality = cell.getStringCellValue(); } } file.close(); FileOutputStream outFile = new FileOutputStream(new File("C:\\softwaretest\\LibraryTest.xls")); workbook.write(outFile); outFile.close(); } catch (FileNotFoundException e) { } catch (IOException e) { } return Quality; }