List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook XSSFWorkbook
public XSSFWorkbook(PackagePart part) throws IOException
From source file:com.knsi.PerformanceFrame.java
public void createUI() { JDialog pf = new JDialog(this, "Performance", true); pf.setIconImage(Toolkit.getDefaultToolkit().getImage(getClass().getResource("/resources/logo.png"))); JTabbedPane perpanel = new JTabbedPane(); JPanel addp = new JPanel(); JPanel viewp = new JPanel(); perpanel.addTab(" Add New Test Details ", addp); //perpanel.addTab(" View Student Performance ", viewp); addp.setBackground(colorSecondary);/* ww w.j a v a2s.co m*/ viewp.setBackground(colorSecondary); setaddTab(addp); submitBtn.addActionListener(new ActionListener() { @Override public void actionPerformed(ActionEvent e) { try { System.out.println(t4.getSelectedItem()); hasStudentmarks.setVisible(false); if (t4.getSelectedItem().equals("")) { hasStudents.setText( "<html><div style=\"color: red;\">" + "No batch has been selected" + "</html>"); hasStudents.setVisible(true); } else if (tName.getText().equalsIgnoreCase("")) { hasStudents.setText("<html><div style=\"color: red;\">" + "Invalid or No Test Name Entered" + "</html>"); hasStudents.setVisible(true); } else if (!maxMarks.getText().matches("^[1-9]\\d*$")) { hasStudents.setText("<html><div style=\"color: red;\">" + "Marks should only consist of digits" + "</html>"); hasStudents.setVisible(true); } else if (isValid) { hasStudents.setText("<html><div style=\"color: red;\">" + "Cannot simultaneously create two performances" + "</html>"); hasStudents.setVisible(true); } else if (fileHasValues((String) t4.getSelectedItem())) { hasStudents.setText( "<html><div style=\"color: red;\">" + "No Students in this batch" + "</html>"); hasStudents.setVisible(true); System.out.println(""); } else { isValid = true; hasStudents.setText("<html><div style=\"color: green;\">" + "Performance created successfully" + "</html>"); hasStudents.setVisible(true); outof.setText(maxMarks.getText()); try { WritePerformance(); } catch (Exception ee) { ee.printStackTrace(); } } } catch (IOException ex) { Logger.getLogger(PerformanceFrame.class.getName()).log(Level.SEVERE, null, ex); } } private boolean fileHasValues(String string) throws IOException { XSSFWorkbook details = new XSSFWorkbook(new FileInputStream(new File(string + ".xlsx"))); XSSFSheet spreadsheet1 = details.getSheet("details"); return spreadsheet1.getLastRowNum() == 0; } }); NexttBtn.addActionListener(new ActionListener() { @Override public void actionPerformed(ActionEvent e) { System.out.println("the next student is:::"); if (!isValid) { hasStudentmarks.setText( "<html><div style=\"color: red;\">" + "Please Create a Performance" + "</html>"); hasStudentmarks.setVisible(true); } else if (!studentmarks.getText().matches("^[1-9]\\d*$") || Integer.parseInt(studentmarks.getText()) > Integer.parseInt(maxMarks.getText())) { hasStudentmarks.setText("<html><div style=\"color: red;\">" + "Invalid Entry" + "</html>"); hasStudentmarks.setVisible(true); } else if (itercount == nameList.size()) { hasStudentmarks.setText("<html><div style=\"color: green;\">" + "All Student\'s Performance has been updated" + "</html>"); hasStudentmarks.setVisible(true); isValid = false; itercount = 0; } else { NexttBtn.setText("<html><div style=\"color: white;\">" + "Next Student" + "</html>"); studentname.setText(nameList.get(itercount).getCell(0).getStringCellValue()); studentid.setText(nameList.get(itercount).getCell(1).getStringCellValue()); marksList.add(studentmarks.getText()); itercount++; } /* for (XSSFRow row : nameList) { System.out.println(row.getCell(0).getStringCellValue()+" "+row.getCell(1).getStringCellValue()); System.out.println(nameList.get(itercount).getCell(0).getStringCellValue()+""+nameList.get(itercount).getCell(1).getStringCellValue()); }*/ WritePerformanceDB.WriteTo(marksList); } }); pf.add(perpanel, BorderLayout.CENTER); pf.setSize(960, 680); //pf.setBounds(0,0,1060,720); pf.setLocationRelativeTo(null); pf.setVisible(true); }
From source file:com.knsi.PerformanceFrame.java
public void WritePerformance() throws Exception { /*just create a new cell at the end of each row at to that add values of the list*/ XSSFWorkbook details = new XSSFWorkbook( new FileInputStream(new File(t4.getSelectedItem().toString() + ".xlsx"))); XSSFSheet spreadsheet1 = details.getSheet("Performance"); XSSFRow row;/* w ww . ja v a 2s .co m*/ Iterator<Row> rowIterator = spreadsheet1.iterator(); while (rowIterator.hasNext()) { row = (XSSFRow) rowIterator.next(); //Iterator < Cell > cellIterator = row.cellIterator(); //System.out.println("the last column in this sheet is "+row.getLastCellNum()); nameList.add(row); /*for(int i=0;i<2;i++) { Cell cell = cellIterator.next(); if(i==0) { studentid.setText(cell.getStringCellValue()); } else { studentname.setText(cell.getStringCellValue()); } System .out.print(cell.getStringCellValue() + " \t\t " ); }*/ //System .out.println(); } }
From source file:com.knsi.WriteDB.java
public static void writeTo(JTextField labels[], String pof) { try {//from ww w .j a v a 2 s.c o m File db = new File(labels[4].getText() + ".xlsx"); System.out.println(db.getAbsolutePath()); Date d = new Date(); System.out.println(d.toString()); FileInputStream dbStream = new FileInputStream(db); XSSFWorkbook details = new XSSFWorkbook(dbStream); XSSFSheet spreadsheet1 = details.getSheet("details"); XSSFSheet spreadsheet2 = details.getSheet("Fees"); XSSFSheet spreadsheet3 = details.getSheet("Performance"); XSSFSheet spreadsheet4 = details.getSheet("Attendence"); System.out.println(spreadsheet1.getLastRowNum() + "------------" + spreadsheet2.getLastRowNum()); //Create row object XSSFRow row1, row2, row3, row4; //This data needs to be written (Object[]) String obj1[] = new String[14]; int j = 1; for (int i = 0; i <= 12; i++) { if (i == 4) { continue; } if (labels[i].getText().equalsIgnoreCase("")) { obj1[j] = "N/A"; } else { obj1[j] = labels[i].getText(); } j++; } //obj1[0]=Integer.toString(spreadsheet1.getLastRowNum()+1); obj1[0] = getRegId(obj1[0], Integer.toString(spreadsheet1.getLastRowNum() + 1), labels[4].getText(), pof); obj1[13] = d.toString(); System.out.println("The details for the details sheet is:"); for (int i = 0; i < 14; i++) { System.out.println(obj1[i]); } String obj2[] = new String[4]; obj2[0] = obj1[0]; obj2[1] = labels[13].getText(); obj2[2] = labels[14].getText(); obj2[3] = labels[13].getText(); System.out.println("The details for the fees sheet is:"); for (int i = 0; i < 4; i++) { System.out.println(obj2[i]); } String obj3[] = new String[2]; obj3[0] = obj1[0]; obj3[1] = obj1[1]; int rowid1 = spreadsheet1.getLastRowNum() + 1; int rowid2 = spreadsheet2.getLastRowNum() + 1; int rowid3 = spreadsheet3.getLastRowNum() + 1; int rowid4 = spreadsheet4.getLastRowNum() + 1; row1 = spreadsheet1.createRow(rowid1); row2 = spreadsheet2.createRow(rowid2); row3 = spreadsheet3.createRow(rowid3); row4 = spreadsheet4.createRow(rowid4); int cellid = 0; for (String obj : obj1) { Cell cell = row1.createCell(cellid++); cell.setCellValue(obj); } cellid = 0; for (String obj : obj2) { Cell cell = row2.createCell(cellid++); cell.setCellValue(obj); } cellid = 0; for (String obj : obj3) { Cell cell = row3.createCell(cellid); cell.setCellValue(obj); Cell cell2 = row4.createCell(cellid); cell2.setCellValue(obj); cellid++; } //Write the workbook in file system FileOutputStream out = new FileOutputStream(db); details.write(out); out.close(); System.out.println(db + " written successfully"); s = db.getAbsolutePath(); } catch (Exception ee) { s = ee.getMessage(); System.out.println(s); } }
From source file:com.kongwu.insweb.utils.ReadExcel.java
License:Apache License
/** * @param filepath excel//from w w w.j av a 2s . c om * * * * @return ??,:list<usrid,query,biz,semantic> * : * : * @throws IOException */ public static List<List<String>> readTestset(String filepath) throws IOException { FileInputStream fileIn = null; List<List<String>> testsetList = new ArrayList<List<String>>(); /**?excel**/ try { fileIn = new FileInputStream(filepath); /**excel 2007**/ if (filepath.endsWith(".xlsx")) { Workbook wb = new XSSFWorkbook(fileIn); Sheet sheet = wb.getSheetAt(0); Row row = null; Cell cell = null; int rowSize = sheet.getLastRowNum() + 1;// getLastRowNum()1 logger.info("?" + rowSize); if (rowSize < 2) return null; // ?, for (int i = 1; i < rowSize; i++) { row = sheet.getRow(i); if (row == null) continue; int cellSize = row.getLastCellNum(); logger.info("?" + cellSize); /** * ?cell */ List<String> list = new ArrayList<String>(); for (int j = 0; j < cellSize; j++) { cell = row.getCell(i); if (cell != null) { list.add(cell.toString()); } } testsetList.add(list); } } else { POIFSFileSystem fs = new POIFSFileSystem(fileIn); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row = null; HSSFCell cell = null; int rowSize = sheet.getLastRowNum() + 1;// getLastRowNum()1 logger.info("?" + rowSize); if (rowSize < 2)// ??? return null; // ?, for (int i = 1; i < rowSize; i++) { row = sheet.getRow(i); if (row == null) continue; int cellSize = row.getLastCellNum(); logger.info("?" + cellSize); List<String> list = new ArrayList<String>(); for (int j = 0; j < cellSize; j++) { cell = row.getCell(j); if (cell != null) { list.add(cell.toString()); } } testsetList.add(list); } } } finally { if (fileIn != null) fileIn.close(); } return testsetList; }
From source file:com.kplot.web.data.WorkbookFactory.java
License:Apache License
/** * Creates a XSSFWorkbook from the given OOXML Package * * <p>Note that in order to properly release resources the * Workbook should be closed after use.</p> * * @param pkg The {@link OPCPackage} opened for reading data. * * @return The created Workbook/*from w ww . ja va 2s . c o m*/ * * @throws IOException if an error occurs while reading the data */ public static Workbook create(OPCPackage pkg) throws IOException { return new XSSFWorkbook(pkg); }
From source file:com.kplot.web.data.WorkbookFactory.java
License:Apache License
/** * Creates the appropriate HSSFWorkbook / XSSFWorkbook from * the given InputStream, which may be password protected. * <p>Your input stream MUST either support mark/reset, or * be wrapped as a {@link PushbackInputStream}! Note that * using an {@link InputStream} has a higher memory footprint * than using a {@link File}.</p> * * <p>Note that in order to properly release resources the * Workbook should be closed after use. Note also that loading * from an InputStream requires more memory than loading * from a File, so prefer {@link #create(File)} where possible.</p> * * @param inp The {@link InputStream} to read data from. * @param password The password that should be used or null if no password is necessary. * * @return The created Workbook//from w w w . j av a 2s.co m * * @throws IOException if an error occurs while reading the data * @throws InvalidFormatException if the contents of the file cannot be parsed into a {@link Workbook} * @throws EncryptedDocumentException If the wrong password is given for a protected file * @throws EmptyFileException If an empty stream is given */ public static Workbook create(InputStream inp, String password) throws IOException, InvalidFormatException, EncryptedDocumentException { // If clearly doesn't do mark/reset, wrap up if (!inp.markSupported()) { inp = new PushbackInputStream(inp, 8); } // Ensure that there is at least some data there byte[] header8 = IOUtils.peekFirst8Bytes(inp); // Try to create if (NPOIFSFileSystem.hasPOIFSHeader(header8)) { NPOIFSFileSystem fs = new NPOIFSFileSystem(inp); return create(fs, password); } if (POIXMLDocument.hasOOXMLHeader(inp)) { return new XSSFWorkbook(OPCPackage.open(inp)); } throw new InvalidFormatException("Your InputStream was neither an OLE2 stream, nor an OOXML stream"); }
From source file:com.kplot.web.data.WorkbookFactory.java
License:Apache License
/** * Creates the appropriate HSSFWorkbook / XSSFWorkbook from * the given File, which must exist and be readable, and * may be password protected/*from w w w . j a va2s . c o m*/ * <p>Note that in order to properly release resources the * Workbook should be closed after use. * * @param file The file to read data from. * @param password The password that should be used or null if no password is necessary. * @param readOnly If the Workbook should be opened in read-only mode to avoid writing back * changes when the document is closed. * * @return The created Workbook * * @throws IOException if an error occurs while reading the data * @throws InvalidFormatException if the contents of the file cannot be parsed into a {@link Workbook} * @throws EncryptedDocumentException If the wrong password is given for a protected file * @throws EmptyFileException If an empty stream is given */ public static Workbook create(File file, String password, boolean readOnly) throws IOException, InvalidFormatException, EncryptedDocumentException { if (!file.exists()) { throw new FileNotFoundException(file.toString()); } try { System.out.println("NPOIFSFileSystem"); NPOIFSFileSystem fs = new NPOIFSFileSystem(file, readOnly); try { return create(fs, password); } catch (RuntimeException e) { System.out.println("ensure that the file-handle is closed again"); fs.close(); throw e; } } catch (OfficeXmlFileException e) { System.out.println("opening as .xls failed => try opening as .xlsx"); System.out.println("OPCPackage"); OPCPackage pkg = OPCPackage.open(file, readOnly ? PackageAccess.READ : PackageAccess.READ_WRITE); try { return new XSSFWorkbook(pkg); } catch (IOException ioe) { // ensure that file handles are closed (use revert() to not re-write the file) pkg.revert(); //pkg.close(); // rethrow exception throw ioe; } catch (RuntimeException ioe) { // ensure that file handles are closed (use revert() to not re-write the file) pkg.revert(); //pkg.close(); // rethrow exception throw ioe; } } }
From source file:com.krawler.esp.fileparser.excel.XlsxParser.java
License:Open Source License
public String extractText(String filepath) throws FileNotFoundException, IOException { StringBuilder sb = new StringBuilder(); try {/*from w w w . j a va 2 s. c om*/ FileInputStream fis = new FileInputStream(filepath); XSSFWorkbook workbook = new XSSFWorkbook(fis); XSSFExcelExtractor es = new XSSFExcelExtractor(workbook); sb.append(es.getText()); } catch (Exception e) { logger.warn(e.getMessage(), e); } return sb.toString(); }
From source file:com.krawler.spring.importFunctionality.ImportController.java
License:Open Source License
public ModelAndView fileUploadXLSX(HttpServletRequest request, HttpServletResponse response) { String View = "jsonView-ex"; JSONObject jobj = new JSONObject(); try {//from w w w . ja va 2 s .com System.out.println("A(( Upload XLSX start : " + new Date()); jobj.put("success", true); FileItemFactory factory = new DiskFileItemFactory(4096, new File(ConfigReader.getinstance().get("UploadTempDir", "/tmp"))); ServletFileUpload upload = new ServletFileUpload(factory); upload.setSizeMax(10485760); // 10Mb List fileItems = upload.parseRequest(request); Iterator i = fileItems.iterator(); String destinationDirectory = storageHandlerImpl.GetDocStorePath() + "xlsfiles"; String fileName = null; String fileid = UUID.randomUUID().toString(); fileid = fileid.replaceAll("-", ""); // To append UUID without "-" [SK] String Ext = ""; while (i.hasNext()) { java.io.File destDir = new java.io.File(destinationDirectory); if (!destDir.exists()) { //Create xls file's folder if not present destDir.mkdirs(); } FileItem fi = (FileItem) i.next(); if (fi.isFormField()) continue; fileName = fi.getName(); if (fileName.contains(".")) { Ext = fileName.substring(fileName.lastIndexOf(".")); int startIndex = fileName.contains("\\") ? (fileName.lastIndexOf("\\") + 1) : 0; fileName = fileName.substring(startIndex, fileName.lastIndexOf(".")); } if (fileName.length() > 28) { // To fixed Mysql ERROR 1103 (42000): Incorrect table name throw new DataInvalidateException("Filename is too long, use upto 28 characters."); } fi.write(new File(destinationDirectory, fileName + "_" + fileid + Ext)); } FileInputStream fs = new FileInputStream(destinationDirectory + "/" + fileName + "_" + fileid + Ext); XSSFWorkbook wb = new XSSFWorkbook(fs); int count = wb.getNumberOfSheets(); JSONArray jArr = new JSONArray(); for (int x = 0; x < count; x++) { JSONObject obj = new JSONObject(); obj.put("name", wb.getSheetName(x)); obj.put("index", x); jArr.put(obj); } jobj.put("file", destinationDirectory + "/" + fileName + "_" + fileid + Ext); jobj.put("filename", fileName + "_" + fileid + Ext); jobj.put("data", jArr); jobj.put("msg", "Image has been successfully uploaded"); jobj.put("lsuccess", true); jobj.put("valid", true); } catch (FileUploadBase.SizeLimitExceededException ex) { Logger.getLogger(ImportController.class.getName()).log(Level.SEVERE, null, ex); jobj.put("msg", "File exceeds max size limit i.e 10MB."); jobj.put("lsuccess", false); jobj.put("valid", true); } catch (Exception e) { Logger.getLogger(ImportController.class.getName()).log(Level.SEVERE, null, e); try { jobj.put("msg", e.getMessage()); jobj.put("lsuccess", false); jobj.put("valid", true); } catch (Exception ex) { } } finally { System.out.println("A(( Upload XLS end : " + new Date()); return new ModelAndView(View, "model", jobj.toString()); } }
From source file:com.krawler.spring.importFunctionality.ImportUtil.java
License:Open Source License
/** * Generate the preview of the xls grid/* w ww .j a va2s . c o m*/ * @param filename * @param sheetNo * @return * @throws FileNotFoundException * @throws IOException * @throws JSONException */ public static JSONObject parseXLSX(String filename, int sheetNo) throws FileNotFoundException, IOException, JSONException { JSONObject jobj = new JSONObject(); FileInputStream fs = new FileInputStream(filename); XSSFWorkbook wb = new XSSFWorkbook(fs); XSSFSheet sheet = wb.getSheetAt(sheetNo); //DateFormat sdf = new SimpleDateFormat(df); int startRow = 0; int maxRow = sheet.getLastRowNum(); int maxCol = 0; int noOfRowsDisplayforSample = 20; if (noOfRowsDisplayforSample > sheet.getLastRowNum()) { noOfRowsDisplayforSample = sheet.getLastRowNum(); } JSONArray jArr = new JSONArray(); try { for (int i = 0; i <= noOfRowsDisplayforSample; i++) { XSSFRow row = sheet.getRow(i); JSONObject obj = new JSONObject(); JSONObject jtemp1 = new JSONObject(); if (row == null) { continue; } if (i == 0) { maxCol = row.getLastCellNum(); } for (int cellcount = 0; cellcount < maxCol; cellcount++) { XSSFCell cell = row.getCell(cellcount); CellReference cref = new CellReference(i, cellcount); String colHeader = cref.getCellRefParts()[2]; String val = null; if (cell != null) { switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { val = Long.toString(cell.getDateCellValue().getTime()); } else { val = dfmt.format(cell.getNumericCellValue()); } break; case XSSFCell.CELL_TYPE_STRING: val = ImportUtil.cleanHTML(cell.getRichStringCellValue().getString()); break; } } if (i == 0) { // List of Headers (Consider first row as Headers) if (val != null) { jtemp1 = new JSONObject(); jtemp1.put("header", val == null ? "" : val); jtemp1.put("index", cellcount); jobj.append("Header", jtemp1); } } obj.put(colHeader, val); } // if(obj.length()>0){ //Don't show blank row in preview grid[SK] jArr.put(obj); // } } } catch (Exception ex) { Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, ex); } jobj.put("startrow", startRow); jobj.put("maxrow", maxRow); jobj.put("maxcol", maxCol); jobj.put("index", sheetNo); jobj.put("data", jArr); jobj.put("filename", filename); jobj.put("msg", "XLSX has been successfully uploaded"); jobj.put("lsuccess", true); jobj.put("valid", true); return jobj; }