List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getPhysicalNumberOfRows
@Override public int getPhysicalNumberOfRows()
From source file:com.schneider.tsm.process.addXPRDataTSEAlert.java
private void add2ExistingReport() throws InterruptedException { final SwingWorker worker = new SwingWorker() { @Override//ww w .ja v a2s .c o m protected Object doInBackground() throws Exception { try { Thread.sleep(160); FileInputStream file = new FileInputStream( new File("C:\\softwaretest\\FileOutput\\TSE_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\\TSE_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.tsmteam.MainTSMWindow.java
private void ProcesaXLS() { final SwingWorker worker = new SwingWorker() { @Override//from w ww . java 2s.c o m protected Object doInBackground() throws Exception { String contenido = "s"; int unidadProgresBAR = contador / 100; for (int uy = 0; uy < contador; uy++) { jLabelState.setText(listadearchivos[uy] + " is in Processing"); if (listadearchivos[uy].endsWith("all.xls")) { System.out.println(listadearchivos[uy] + "Termina con All"); try { FileInputStream file = new FileInputStream( new File(jTextFieldDirectory.getText() + "\\" + listadearchivos[uy])); HSSFWorkbook workbook = new HSSFWorkbook(file); HSSFSheet sheet = workbook.getSheetAt(0); Cell cell = null; int sheetsize = sheet.getPhysicalNumberOfRows(); cell = sheet.getRow(0).getCell(21); if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) { if (sheetsize > 1) { cell = sheet.getRow(1).getCell(21); for (int i = 1; i < sheetsize; i++) { cell = sheet.getRow(i).getCell(21); if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) { String cellContents = cell.getStringCellValue(); cell = sheet.getRow(i).getCell(1); cell.setCellValue(cellContents); cell = sheet.getRow(i).getCell(21); cell.setCellType(Cell.CELL_TYPE_BLANK); } } } cell = sheet.getRow(0).getCell(21); cell.setCellType(Cell.CELL_TYPE_BLANK); } file.close(); // ORIGINAL FileOutputStream outFile =new FileOutputStream(new File(jTextFieldDirectory.getText()+ "\\" + listadearchivos[uy])); /* TEST*/ FileOutputStream outFile = new FileOutputStream( new File(jTextFieldDirectory.getText() + "\\" + listadearchivos[uy])); workbook.write(outFile); outFile.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } else { System.out.println(listadearchivos[uy] + "Termina con Upd"); try { FileInputStream file = new FileInputStream( new File(jTextFieldDirectory.getText() + "\\" + listadearchivos[uy])); HSSFWorkbook workbook = new HSSFWorkbook(file); HSSFSheet sheet = workbook.getSheetAt(0); Cell cell = null; int sheetsize = sheet.getPhysicalNumberOfRows(); cell = sheet.getRow(0).getCell(22); if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) { if (sheetsize > 1) { cell = sheet.getRow(1).getCell(22); for (int i = 1; i < sheetsize; i++) { cell = sheet.getRow(i).getCell(22); if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) { String cellContents = cell.getStringCellValue(); cell = sheet.getRow(i).getCell(1); cell.setCellValue(cellContents); cell = sheet.getRow(i).getCell(22); cell.setCellType(Cell.CELL_TYPE_BLANK); } } } cell = sheet.getRow(0).getCell(22); cell.setCellType(Cell.CELL_TYPE_BLANK); } file.close(); FileOutputStream outFile = new FileOutputStream( new File(jTextFieldDirectory.getText() + "\\" + listadearchivos[uy])); workbook.write(outFile); outFile.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } jLabelState.setText(listadearchivos[uy] + " is Completed"); Thread.sleep(300); } JOptionPane hola = new JOptionPane(); jButton2.setVisible(true); jButton1.setVisible(true); jLabelState.setText("Complete"); JOptionPane.showMessageDialog(hola, "Complete"); System.out.println(contador); return null; } }; worker.execute(); }
From source file:com.Servlet.SaveServlet.java
public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { empId = request.getParameter("employeeid"); empName = request.getParameter("employeename"); teamname = request.getParameter("projectteam"); tcat = request.getParameter("trainingcategory"); tname = request.getParameter("nameoftrainer"); thours = request.getParameter("hoursoftraining"); ttopic = request.getParameter("topic"); sdate = request.getParameter("startdate"); cdate = request.getParameter("completiondate"); request.setAttribute("uname", empName); request.setAttribute("emId", empId); request.setAttribute("teName", teamname); try {/* w ww . j a v a 2 s .c om*/ FileInputStream file = new FileInputStream(new File("C:\\Users\\mc13082\\Desktop\\form\\new.xls")); HSSFWorkbook workbook = new HSSFWorkbook(file); HSSFSheet sheet = workbook.getSheetAt(0); Map<String, Object[]> data = new HashMap<>(); //data.put("1", new Object[] {"Employee_Name", "Email"}); data.put("2", new Object[] { empId, empName, teamname, tcat, tname, thours, ttopic, sdate, cdate }); counter = sheet.getPhysicalNumberOfRows(); Set<String> keyset = data.keySet(); int rownum = counter; for (String key : keyset) { HSSFRow row = sheet.createRow(rownum++); Object[] objArr = data.get(key); int cellnum = 0; for (Object obj : objArr) { Cell cell = row.createCell(cellnum++); if (obj instanceof Date) cell.setCellValue((Date) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Double) cell.setCellValue((Double) obj); } } file.close(); FileOutputStream out = new FileOutputStream(new File("C:\\Users\\mc13082\\Desktop\\form\\new.xls")); workbook.write(out); out.close(); System.out.println("Excel written successfully.."); counter++; System.out.println(counter); RequestDispatcher rd = request.getRequestDispatcher("success.jsp"); rd.forward(request, response); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
From source file:com.siva.javamultithreading.MultiThreadExecutor.java
public static void main(String[] args) throws ExecutionException, IOException { //Populate the data List<DomainObject> list = new ArrayList<>(); DomainObject object = null;//from w w w .ja va 2s . com for (int i = 0; i < 230000; i++) { object = new DomainObject(); object.setId("ID" + i); object.setName("NAME" + i); object.setComment("COMMENT" + i); list.add(object); } int maxNoOfRows = 40000; int noOfThreads = 1; int remaining = 0; if (list.size() > 40000) { noOfThreads = list.size() / maxNoOfRows; remaining = list.size() % maxNoOfRows; if (remaining > 0) { noOfThreads++; } } List<List<DomainObject>> dos = ListUtils.partition(list, maxNoOfRows); ExecutorService threadPool = Executors.newFixedThreadPool(noOfThreads); CompletionService<HSSFWorkbook> pool = new ExecutorCompletionService<>(threadPool); // Excel creation through multiple threads long startTime = System.currentTimeMillis(); for (List<DomainObject> listObj : dos) { pool.submit(new ExcelChunkSheetWriter(listObj)); } HSSFWorkbook hSSFWorkbook = null; HSSFWorkbook book = new HSSFWorkbook(); HSSFSheet sheet = book.createSheet("Report"); try { for (int i = 0; i < 5; i++) { hSSFWorkbook = pool.take().get(); System.out.println( "sheet row count : sheet.PhysicalNumberOfRows() = " + sheet.getPhysicalNumberOfRows()); int currentCount = sheet.getPhysicalNumberOfRows(); int incomingCount = hSSFWorkbook.getSheetAt(0).getPhysicalNumberOfRows(); if ((currentCount + incomingCount) > 60000) { sheet = book.createSheet("Report" + i); } ExcelUtil.copySheets(book, sheet, hSSFWorkbook.getSheetAt(0)); } } catch (InterruptedException ex) { Logger.getLogger(MultiThreadExecutor.class.getName()).log(Level.SEVERE, null, ex); } catch (ExecutionException ex) { Logger.getLogger(MultiThreadExecutor.class.getName()).log(Level.SEVERE, null, ex); } try { writeFile(book, new FileOutputStream("Report.xls")); } catch (Exception e) { e.printStackTrace(); } //System.out.println("No of Threads : " + noOfThreads + " Size : " + list.size() + " remaining : " + remaining); long endTime = System.currentTimeMillis(); System.out.println("Time taken: " + (endTime - startTime) + " ms"); threadPool.shutdown(); //startProcess(); }
From source file:com.siva.javamultithreading.MultiThreadExecutor.java
/** * This is sample.//from w w w .j a v a2 s . c om */ private static void startProcess() { ExecutorService threadPool = Executors.newFixedThreadPool(4); CompletionService<HSSFWorkbook> pool = new ExecutorCompletionService<>(threadPool); // Excel creation through multiple threads long startTime = System.currentTimeMillis(); pool.submit(new ExcelChunkSheetWriter(0, 1000)); pool.submit(new ExcelChunkSheetWriter(1001, 20000)); pool.submit(new ExcelChunkSheetWriter(2, 3000)); pool.submit(new ExcelChunkSheetWriter(3, 40000)); pool.submit(new ExcelChunkSheetWriter(4, 50000)); HSSFWorkbook hSSFWorkbook = null; HSSFWorkbook book = new HSSFWorkbook(); HSSFSheet sheet = book.createSheet("Report"); try { for (int i = 0; i < 5; i++) { hSSFWorkbook = pool.take().get(); System.out.println( "sheet row count : sheet.PhysicalNumberOfRows() = " + sheet.getPhysicalNumberOfRows()); int currentCount = sheet.getPhysicalNumberOfRows(); int incomingCount = hSSFWorkbook.getSheetAt(0).getPhysicalNumberOfRows(); if ((currentCount + incomingCount) > 60000) { sheet = book.createSheet("Report" + i); } ExcelUtil.copySheets(book, sheet, hSSFWorkbook.getSheetAt(0)); } } catch (InterruptedException ex) { Logger.getLogger(MultiThreadExecutor.class.getName()).log(Level.SEVERE, null, ex); } catch (ExecutionException ex) { Logger.getLogger(MultiThreadExecutor.class.getName()).log(Level.SEVERE, null, ex); } try { writeFile(book, new FileOutputStream("Report.xls")); } catch (Exception e) { e.printStackTrace(); } /* FileOutputStream fos = new FileOutputStream("all.zip"); ZipOutputStream zos = new ZipOutputStream(fos); for (int i = 0; i < 5; i++) { try { hSSFWorkbook = pool.take().get(); ZipEntry ze = new ZipEntry("Excel" + i + ".xls"); zos.putNextEntry(ze); hSSFWorkbook.write(zos); zos.closeEntry(); } catch (InterruptedException ex) { Logger.getLogger(MultiThreadExecutor.class.getName()).log(Level.SEVERE, null, ex); } } zos.close(); */ long endTime = System.currentTimeMillis(); System.out.println("Time taken: " + (endTime - startTime) + " ms"); threadPool.shutdown(); }
From source file:com.square.core.util.poi.DocumentXls.java
License:Open Source License
/** * Recupere la page courante ou la suivante si la courante est pleine. * @return la page/* w ww .j a v a 2 s . com*/ */ private HSSFSheet recupererPage() { // recuperation de la derniere page HSSFSheet page = classeur.getSheetAt(classeur.getNumberOfSheets() - 1); // on verifie si il y a encore de la place sur la page if (page.getPhysicalNumberOfRows() == SHEET_MAX_ROWS) { page = creerPage(); } return page; }
From source file:com.verticon.treatment.poi.handlers.EventImportHandler.java
License:Open Source License
private int processWorkSheet(Object o, Program program, EditingDomain ed, IProgressMonitor monitor) throws Exception { File f = convert(o);//from w w w .j a v a2 s. c o m HSSFSheet ws = getWorkSheet(f); ExecutableProcreator procreator; if (PoiUtils.isWorkSheetMatch(PoiUtils.TESTLOG_HEADER, ws)) { procreator = ExecutableProcreatorFactory.newTestEventProcreator(); System.out.printf("Processing Test Event Log with %s rows%n", ws.getLastRowNum()); } else { procreator = ExecutableProcreatorFactory.newTreatmentEventProcreator(); System.out.printf("Processing Treatment Event Log with %s rows%n", ws.getLastRowNum()); } // Exception ex = null; int count = 0; try { int rowsInSheet = ws.getPhysicalNumberOfRows(); for (int i = 1; i < rowsInSheet; i++) { System.out.printf("Processing row %s in spreadsheet with %s rows.%n", i + 1, rowsInSheet); monitor.worked(1); procreator.prepare(program, ws.getRow(i), ed); count++; } procreator.execute(ed); message.append(procreator.getStatus()); } catch (Exception e) { ex = e; } finally { procreator.dispose(); } if (ex != null) { throw ex; } return count; }
From source file:com.wangzhu.poi.ExcelToHtmlConverter.java
License:Apache License
protected void processSheet(HSSFSheet sheet) { // this.processSheetHeader(this.htmlDocumentFacade.getBody(), sheet); final int physicalNumberOfRows = sheet.getPhysicalNumberOfRows(); if (physicalNumberOfRows <= 0) { return;//from w ww . j a v a 2s.c o m } Element table = this.htmlDocumentFacade.createTable(); this.htmlDocumentFacade.addStyleClass(table, this.cssClassPrefixTable, "border-collapse:collapse;border-spacing:0;"); Element tableBody = this.htmlDocumentFacade.createTableBody(); final CellRangeAddress[][] mergedRanges = ExcelToHtmlUtils.buildMergedRangesMap(sheet); final List emptyRowElements = new ArrayList(physicalNumberOfRows); int maxSheetColumns = 1; for (int r = sheet.getFirstRowNum(); r <= sheet.getLastRowNum(); r++) { HSSFRow row = sheet.getRow(r); if (row == null) { continue; } if (!this.isOutputHiddenRows() && row.getZeroHeight()) { continue; } Element tableRowElement = this.htmlDocumentFacade.createTableRow(); this.htmlDocumentFacade.addStyleClass(tableRowElement, this.cssClassPrefixRow, "height:" + (row.getHeight() / 20f) + "pt;"); int maxRowColumnNumber = this.processRow(mergedRanges, row, tableRowElement); if (maxRowColumnNumber == 0) { emptyRowElements.add(tableRowElement); } else { if (!emptyRowElements.isEmpty()) { for (Iterator iterator = emptyRowElements.iterator(); iterator.hasNext();) { Element emptyRowElement = (Element) iterator.next(); tableBody.appendChild(emptyRowElement); } emptyRowElements.clear(); } tableBody.appendChild(tableRowElement); } maxSheetColumns = Math.max(maxSheetColumns, maxRowColumnNumber); } this.processColumnWidths(sheet, maxSheetColumns, table); if (this.isOutputColumnHeaders()) { this.processColumnHeaders(sheet, maxSheetColumns, table); } table.appendChild(tableBody); this.htmlDocumentFacade.getBody().appendChild(table); if (null != this.getExcelImageManager()) { table = this.htmlDocumentFacade.createTable(); this.htmlDocumentFacade.addStyleClass(table, this.cssClassPrefixTable, "border-collapse:collapse;border-spacing:0;"); tableBody = this.htmlDocumentFacade.createTableBody(); List<String> urlPaths = this.getExcelImageManager() .getImagePath(sheet.getDrawingPatriarch().getChildren()); if ((urlPaths != null) && (urlPaths.size() != 0)) { Document document = this.htmlDocumentFacade.getDocument(); for (int i = 0, size = urlPaths.size(); i < size; i++) { Element tableRowElement = this.htmlDocumentFacade.createTableRow(); String[] urlPathArr = urlPaths.get(i).split("@"); Element result = document.createElement("img"); result.setAttribute("src", urlPathArr[0]); String imageWidth = urlPathArr[1]; String imageHeight = urlPathArr[2]; result.setAttribute("style", "width:" + imageWidth + "in;height:" + imageHeight + "in;vertical-align:text-bottom;"); Element tableCellElement = this.htmlDocumentFacade.createTableCell(); tableCellElement.appendChild(result); tableRowElement.appendChild(tableCellElement); tableBody.appendChild(tableRowElement); } table.appendChild(tableBody); this.htmlDocumentFacade.getBody().appendChild(table); } } }
From source file:Creator.TaskManagerPanel.java
/** * Reads a file and returns a list of strings which contain all the variable * names// w w w. j a v a 2 s .c o m * * @param filename */ public void readXFile(String filename) { try { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename)); 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 = 0; int idCol = -1, idName = -1; // This trick ensures that we get the data properly even if it doesn't start from first few rows for (int i = 0; i < 1; i++) { row = sheet.getRow(i); if (row != null) { tmp = sheet.getRow(i).getPhysicalNumberOfCells(); if (tmp > cols) { cols = tmp; } } if (!sheet.getRow(i).getCell(0).toString().equals("io_id")) { for (int c = 1; c < cols; c++) { if (sheet.getRow(i).getCell(c).equals("io_id")) { idCol = c; break; } } } else { idCol = 0; } if (!sheet.getRow(i).getCell(1).toString().equals("io_name")) { for (int c = 0; c < cols; c++) { if (sheet.getRow(i).getCell(c).equals("io_name")) { idName = c; break; } } } else { idName = 1; } if (!sheet.getRow(i).getCell(2).toString().equals("io_station_id")) { for (int c = 0; c < cols; c++) { if (sheet.getRow(i).getCell(c).equals("io_station_id")) { stationID = (int) sheet.getRow(1).getCell(c).getNumericCellValue(); break; } } } else { stationID = (int) sheet.getRow(1).getCell(2).getNumericCellValue(); } } if (idName == -1 || idCol == -1) { System.out.println("Could not locate io_name or io_id in excel header"); return; } if (stationID == -1) { System.out.println("Couldnt locate station id"); return; } importedIOVariables = new TreeMap<>(); int io_id; String io_name; for (int r = 1; r < rows; r++) { row = sheet.getRow(r); if (row != null) { cell = row.getCell(idCol); if (cell != null) { io_id = (int) cell.getNumericCellValue(); cell = row.getCell(idName); if (cell != null) { io_name = cell.toString().replace("\"", ""); // Read both name and id importedIOVariables.put(io_name, io_id); } } } } fs.close(); mf.loadImportedIos(importedIOVariables, 2, stationID); } catch (Exception e) { System.out.println("Error reading excel file " + e.getMessage()); } }
From source file:Creator.WidgetPanel.java
/** * Reads a file and returns a list of strings which contain all the variable * names/*w w w . java 2 s. co m*/ * * @param filename */ public void readXFile(String filename) { try { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename)); 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 = 0; int idCol = -1, idName = -1; // This trick ensures that we get the data properly even if it doesn't start from first few rows for (int i = 0; i < 1; i++) { row = sheet.getRow(i); if (row != null) { tmp = sheet.getRow(i).getPhysicalNumberOfCells(); if (tmp > cols) { cols = tmp; } } if (!sheet.getRow(i).getCell(0).toString().equals("io_id")) { for (int c = 1; c < cols; c++) { if (sheet.getRow(i).getCell(c).equals("io_id")) { idCol = c; break; } } } else { idCol = 0; } if (!sheet.getRow(i).getCell(1).toString().equals("io_name")) { for (int c = 1; c < cols; c++) { if (sheet.getRow(i).getCell(c).equals("io_name")) { idName = c; break; } } } else { idName = 1; } if (!sheet.getRow(i).getCell(2).toString().equals("io_station_id")) { for (int c = 0; c < cols; c++) { if (sheet.getRow(i).getCell(c).equals("io_station_id")) { stationID = (int) sheet.getRow(1).getCell(c).getNumericCellValue(); break; } } } else { stationID = (int) sheet.getRow(1).getCell(2).getNumericCellValue(); } } if (idName == -1 || idCol == -1) { System.out.println("Could not locate io_name or io_id in excel header"); return; } if (stationID == -1) { System.out.println("Couldnt locate station id"); return; } importedIOVariables = new TreeMap<>(); int io_id; String io_name; for (int r = 1; r < rows; r++) { row = sheet.getRow(r); if (row != null) { cell = row.getCell(idCol); if (cell != null) { io_id = (int) cell.getNumericCellValue(); cell = row.getCell(idName); if (cell != null) { io_name = cell.toString().replace("\"", ""); // Read both name and id importedIOVariables.put(io_name, io_id); } } } } fs.close(); mf.loadImportedIos(importedIOVariables, 1, stationID); } catch (Exception e) { System.out.println("Error reading excel file " + e.getMessage()); } }