List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt
@Override public XSSFSheet getSheetAt(int index)
From source file:com.sandstone_tech.lendfastselenium2.util.PoiFileReader.java
/** * Extracts the content of the excel file to be converted as * {@link TestDataModel} object usable by the data provider * //w w w . ja v a 2 s .c om * @param fileName * @return */ protected Object[][] readExcelFileAsTDM(String fileName) { try { XSSFWorkbook wb = new XSSFWorkbook(FileUtils.openInputStream(new File(fileName))); // CONFIGURATION // XSSFSheet sheet = wb.getSheetAt(0); // DATA XSSFSheet dataSheet = wb.getSheetAt(1); if (dataSheet != null && dataSheet.getPhysicalNumberOfRows() > 1) { Object[][] testData = new Object[dataSheet.getPhysicalNumberOfRows() - 2][1]; for (int rowCount = 2; rowCount < dataSheet.getPhysicalNumberOfRows(); rowCount++) { XSSFRow row = dataSheet.getRow(rowCount); TestDataModel testDataModel = new TestDataModel(); testDataModel.setUsername(getStringValue(row.getCell(0))); testDataModel.setPassword(getStringValue(row.getCell(1))); testDataModel.setApplicationType(getStringValue(row.getCell(2))); testDataModel.setOrginationFormModel(this.getOrigination(row)); testDataModel.setPersonalDetailsModel(this.getPersonalDetail(row)); testDataModel.setSignedDeclaration(this.getSignedDeclaration(row)); testDataModel.setCurrentAddress(this.getCurrentAddress(row)); testDataModel.setEmploymentDetails(this.getEmploymentDetails(row)); testDataModel.setPreviousEmploymentDetails(this.getPreviousEmploymentDetails(row)); testDataModel.setPurchaseProperty( new PurchaseProperty(this.getPropertyContract(row), this.getPropertyPurpose(row))); testData[rowCount - 2][0] = testDataModel; } wb.close(); return testData; } wb.close(); } catch (IOException e) { e.printStackTrace(); } return null; }
From source file:com.seer.datacruncher.fileupload.Excel_2007_FileReadObject.java
License:Open Source License
@Override public String parseStream(long schemaId, InputStream ios) { List<SchemaFieldEntity> listSchemaFields = schemaFieldsDao.listSchemaFields(schemaId); StringBuffer sb = new StringBuffer(); try {/*from www . j a v a 2 s .co m*/ XSSFWorkbook myWorkBook = new XSSFWorkbook(ios); XSSFSheet mySheet = myWorkBook.getSheetAt(0); Iterator<Row> rowIter = mySheet.rowIterator(); sb.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>"); while (rowIter.hasNext()) { int j = 0; XSSFRow myRow = (XSSFRow) rowIter.next(); Iterator<Cell> cellIter = myRow.cellIterator(); sb.append("<" + Tag.TAG_ROOT + ">"); while (cellIter.hasNext()) { XSSFCell myCell = (XSSFCell) cellIter.next(); String fieldValue = myCell.toString().trim(); if (!listSchemaFields.get(j).getNillable() || fieldValue.length() > 0) { sb.append("<" + listSchemaFields.get(j).getName() + ">") .append(myCell.toString().replaceAll("&", "&")) .append("</" + listSchemaFields.get(j).getName() + ">"); } j++; if (j == listSchemaFields.size() && cellIter.hasNext()) { return I18n.getMessage("error.numberFieldsNoMatch"); } else if (!cellIter.hasNext() && j != listSchemaFields.size()) { return I18n.getMessage("error.numberFieldsNoMatch"); } } sb.append("</" + Tag.TAG_ROOT + ">\n"); } } catch (IOException e) { logger.error("Error occured during fetch records from excel file.", e); return "Could not able to parse Excel file. " + e.getMessage(); } return sb.toString(); }
From source file:com.siacra.beans.GrupoBean.java
public void archivoXlsx(String path, FileUploadEvent archivo) { excelResponse = new ArrayList<>(); List<Horario> horas = new ArrayList<>(); try {/*from ww w . j a v a 2 s. c o m*/ FileInputStream file = new FileInputStream(new File(path + "\\" + archivo.getFile().getFileName())); // Crear el objeto que tendra el libro de Excel XSSFWorkbook workbook = new XSSFWorkbook(file); /* * Obtenemos la primera pestaa a la que se quiera procesar indicando el indice. * Una vez obtenida la hoja excel con las filas que se quieren leer obtenemos el iterator * que nos permite recorrer cada una de las filas que contiene. */ XSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); Row row; // Recorremos todas las filas para mostrar el contenido de cada celda int cantidad = 0; int cantidad2 = 0; while (rowIterator.hasNext()) { row = rowIterator.next(); if (cantidad2 != 0) { Horario h = new Horario(); UpploadGrupos grupo = new UpploadGrupos(); // Obtenemos el iterator que permite recorres todas las celdas de una fila Iterator<Cell> cellIterator = row.cellIterator(); Cell celda; cantidad = 1; while (cellIterator.hasNext()) { celda = cellIterator.next(); // if((cantidad%10)==0) // { // System.out.print(grupo.toString()); // excelResponse.add(grupo); // grupo = new UpploadGrupos(); // cantidad=1; // } // Dependiendo del formato de la celda el valor se debe mostrar como String, Fecha, boolean, entero... switch (celda.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(celda)) { SimpleDateFormat f = new SimpleDateFormat("HH:mm:ss"); String fecha = f.format(celda.getDateCellValue()); System.out.print(":::::::: " + fecha); Date dos = f.parse(fecha); if (cantidad == 4) { h.setHinicio1(dos); grupo.setInicio1(dos); cantidad++; } else if (cantidad == 5) { h.setHfin1(dos); grupo.setFin1(dos); cantidad++; } else if (cantidad == 7) { h.setHinicio2(dos); grupo.setInicio2(dos); cantidad++; } else if (cantidad == 8) { h.setHfin2(dos); grupo.setFin2(dos); cantidad++; } System.out.println(dos); } // else // { // double numero = celda.getNumericCellValue(); // System.out.println(celda.getNumericCellValue()); // } break; case Cell.CELL_TYPE_STRING: if (cantidad == 1) { grupo.setAsignatura(celda.getStringCellValue()); cantidad++; } else if (cantidad == 2) { grupo.setTipoGrupo(celda.getStringCellValue()); cantidad++; } else if (cantidad == 3) { h.setDia1(celda.getStringCellValue()); grupo.setDia1(celda.getStringCellValue()); cantidad++; } else if (cantidad == 6) { h.setDia2(celda.getStringCellValue()); grupo.setDia2(celda.getStringCellValue()); cantidad++; } else if (cantidad == 9) { grupo.setNumeroGrupo(celda.getStringCellValue()); cantidad++; } else if (cantidad == 10) { grupo.setCupos(celda.getStringCellValue()); cantidad++; } String texto = celda.getStringCellValue(); System.out.println(celda.getStringCellValue()); break; // case Cell.CELL_TYPE_BOOLEAN: // System.out.println(celda.getBooleanCellValue()); // break; }//fin if que obtiene valor de celda } //fin while que recorre celdas System.out.print("objeto:::" + grupo.toString()); System.out.print("objeto:::" + h.toString()); horas.add(h); excelResponse.add(grupo); } // fin if primera iteracion cantidad2++; } // fin while que recorre filas // cerramos el libro excel workbook.close(); } catch (Exception e) { e.printStackTrace(); } procesarListaCargada(); }
From source file:com.smanempat.controller.ControllerClassification.java
private void showXLSX(JTextField txtFileDirectory, JTable tablePreview) throws FileNotFoundException, IOException { DefaultTableModel tableModel = new DefaultTableModel(); File fileName = new File(txtFileDirectory.getText()); FileInputStream inputStream = new FileInputStream(fileName); XSSFWorkbook workbook = new XSSFWorkbook(inputStream); XSSFSheet sheet = workbook.getSheetAt(0); int rowValue = sheet.getLastRowNum() + 1; int colValue = sheet.getRow(0).getLastCellNum(); String[][] data = new String[rowValue][colValue]; String[] colName = new String[colValue]; for (int i = 0; i < rowValue; i++) { XSSFRow row = sheet.getRow(i);// w w w .j a v a 2 s.c om for (int j = 0; j < colValue; j++) { XSSFCell cell = row.getCell(j); int type = cell.getCellType(); Object returnCellValue = null; if (type == 0) { returnCellValue = cell.getNumericCellValue(); } else if (type == 1) { returnCellValue = cell.getStringCellValue(); } data[i][j] = returnCellValue.toString(); } } for (int i = 0; i < colValue; i++) { colName[i] = data[0][i]; } tableModel = new DefaultTableModel(data, colName); tablePreview.setModel(tableModel); tableModel.removeRow(0); }
From source file:com.smanempat.view.ReadWorkbook.java
private void ekstensiXLSXTraining() { try {/* w w w. ja va2s.c o m*/ FileInputStream FIS = new FileInputStream(new File(txtNamaFile.getText())); XSSFWorkbook wb = new XSSFWorkbook(FIS); XSSFSheet ws = wb.getSheetAt(0); int baris = ws.getLastRowNum() + 1; System.out.println(baris); int kolom = ws.getRow(0).getLastCellNum(); System.out.println(kolom); String[][] data = new String[baris][kolom]; String[] nKolom = new String[kolom]; for (int i = 0; i < baris; i++) { XSSFRow row = ws.getRow(i); for (int j = 0; j < kolom; j++) { XSSFCell cell = row.getCell(j); System.out.println("Isi Cell Adalah =" + cell); String value = nilaiCell(cell); data[i][j] = value; System.out.println("data['" + i + "']['" + j + "'] " + data[i][j]); } } //ambil nama kolom pada index data baris ke 0 for (int a = 0; a < kolom; a++) { nKolom[a] = data[0][a]; } //Isi ke database String nim = null; String nama = null; String nilai = null; for (int b = 1; b < baris; b++) { int c = 0; while (c < kolom) { nim = data[b][c]; nama = data[b][c + 1]; nilai = data[b][c + 2]; break; } insert(nim, nama, nilai); } DefaultTableModel DTM = new DefaultTableModel(data, nKolom); jTable1.setModel(DTM); DTM.removeRow(0); } catch (Exception er1) { System.out.print("Error : \n" + er1.toString()); } }
From source file:com.svi.main.logic.ExtractAndPrint.java
private void writeLogFile(List<Nodes> nodesHolder) { DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd HHmm"); Date date = new Date(); Sheet mainSheet;//w ww.ja v a2 s .c o m Sheet dataSheet; Sheet elementSheet; Row dataSheetRow; Row elementSheetRow; InputStream fis; XSSFWorkbook workbook; File outputPath = new File(logPath + "\\Logs"); File outputFile = new File(logPath + "\\Logs\\BPO KPI Report " + dateFormat.format(date) + ".xlsx"); // File name int dataSheetRowCount = 1; int elementSheetRowCount = 1; int totalElementException = 0; try { if (!Files.exists(outputPath.toPath())) { Files.createDirectories(outputPath.toPath()); } fis = ExtractAndPrint.class.getResourceAsStream("bpo_template.xlsx"); workbook = new XSSFWorkbook(fis); //Style for exception sheet XSSFCellStyle style = workbook.createCellStyle(); style.setFillBackgroundColor(IndexedColors.YELLOW.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); //Get data Sheet mainSheet = workbook.getSheetAt(0); writeProjectDetails(mainSheet); dataSheet = workbook.getSheetAt(4); dataSheetRow = dataSheet.createRow(0); elementSheet = workbook.getSheetAt(1); elementSheetRow = elementSheet.createRow(0); //Write excel headers writeDataSheetHeaders(dataSheetRow); writeElementSheetHeaders(elementSheetRow); //Set progress bar values progress = new AtomicInteger(0); total = new AtomicInteger(nodesHolder.size()); mf.setJprogressValues(total, progress); // Sort the nodes per Node ID Collections.sort(nodesHolder, new Comparator<Nodes>() { public int compare(Nodes o1, Nodes o2) { return o1.getNodeId().compareTo(o2.getNodeId()); } }); //Write Data Sheet for (Nodes node : nodesHolder) { mf.loader(); dataSheetRow = dataSheet.createRow(dataSheetRowCount++); writeDataSheet(node, dataSheetRow); } for (Nodes node : nodesHolder) { for (Elements e : node) { if ((e.getStatus().equalsIgnoreCase("COMPLETE") || e.getStatus().equalsIgnoreCase("PROCESSING")) && e.getTotalProcTime() > MAX_MINUTES_ELEMENT) { totalElementException++; } } } progress = new AtomicInteger(0); total = new AtomicInteger(totalElementException); mf.setJprogressValues(total, progress); //Write exception sheet for (Nodes node : nodesHolder) { for (Elements e : node) { if ((e.getStatus().equalsIgnoreCase("COMPLETE") || e.getStatus().equalsIgnoreCase("PROCESSING")) && e.getTotalProcTime() > MAX_MINUTES_ELEMENT) { elementSheetRow = elementSheet.createRow(elementSheetRowCount++); writeElementSheet(e, elementSheetRow); mf.elementLoader(); } } } XSSFFormulaEvaluator.evaluateAllFormulaCells((XSSFWorkbook) workbook); try (FileOutputStream outputStream = new FileOutputStream(outputFile)) { workbook.write(outputStream);//Write the Excel File outputStream.close(); } workbook.close(); fis.close(); mf.generateMessage(); } catch (Exception ex) { Logger.getLogger(ExtractAndPrint.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:com.ts.control.UploadFile.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html;charset=UTF-8"); PrintWriter out = response.getWriter(); String campo1 = ""; String campo2 = ""; String campo3 = ""; String campo4 = ""; String campo5 = ""; String campo6 = ""; String campo7 = ""; int c = 1;//from www . ja v a 2s . c om try { boolean ismultipart = ServletFileUpload.isMultipartContent(request); if (!ismultipart) { } else { FileItemFactory factory = new DiskFileItemFactory(); ServletFileUpload upload = new ServletFileUpload(factory); List items = null; try { items = upload.parseRequest(request); } catch (Exception e) { } Iterator itr = items.iterator(); while (itr.hasNext()) { FileItem item = (FileItem) itr.next(); if (item.isFormField()) { } else { String itemname = item.getName(); if ((itemname == null || itemname.equals(""))) { continue; } String filename = FilenameUtils.getName(itemname); System.out.println("ruta---------------" + saveFile + filename); File f = checkExist(filename); item.write(f); //////// List cellDataList = new ArrayList(); try { FileInputStream fileInputStream = new FileInputStream(f); XSSFWorkbook workBook = new XSSFWorkbook(fileInputStream); XSSFSheet hssfSheet = workBook.getSheetAt(0); Iterator rowIterator = hssfSheet.rowIterator(); while (rowIterator.hasNext()) { XSSFRow hssfRow = (XSSFRow) rowIterator.next(); Iterator iterator = hssfRow.cellIterator(); List cellTempList = new ArrayList(); while (iterator.hasNext()) { XSSFCell hssfCell = (XSSFCell) iterator.next(); cellTempList.add(hssfCell); } cellDataList.add(cellTempList); } } catch (Exception e) { e.printStackTrace(); } out.println("<table border='3' class='table table-bordered table-striped table-hover'>"); for (int i = 1; i < cellDataList.size(); i++) { List cellTempList = (List) cellDataList.get(i); out.println("<tr>"); for (int j = 0; j < cellTempList.size(); j++) { XSSFCell hssfCell = (XSSFCell) cellTempList.get(j); String dato = hssfCell.toString(); out.print("<td>" + dato + "</td>"); switch (c) { case 1: campo1 = dato; c++; break; case 2: campo2 = dato; c++; break; case 3: campo3 = dato; c++; break; case 4: campo4 = dato; c++; break; case 5: campo5 = dato; c++; break; case 6: campo6 = dato; c++; break; case 7: campo7 = dato; c = 1; break; } } //model.Consulta.addRegistros(campo1,campo2,campo3,campo4,campo5,campo6,campo7); } out.println("</table><br>"); ///////// } } } } catch (Exception e) { } finally { out.close(); } }
From source file:com.tutorial.excelreadwrite.excelFunctions.java
excelFunctions(XSSFWorkbook workbook, int sheet) { this.workbook = workbook; this.sheet = workbook.getSheetAt(sheet); //Define the color of mark -- This will be called in future functions mark = workbook.createCellStyle(); mark.setFillForegroundColor(new XSSFColor(java.awt.Color.BLACK)); mark.setFillPattern(CellStyle.SOLID_FOREGROUND); }//from w w w . j ava 2 s. c o m
From source file:com.unicomer.oer.harvester.reader.ExcelBrokerRemoteReader.java
public List<Set<Entity>> read() throws Exception { List<Set<Entity>> list = new ArrayList<Set<Entity>>(); HashMap<String, Entity> servicesMap = new HashMap<String, Entity>(); String defEnvironment = prop.getProperty("default.environment"); String defaultAssetType = prop.getProperty("broker.service.asset-type"); String resourceEnvironmentXPath = prop.getProperty("broker.custom-data.environment"); String resourceNameXPath = prop.getProperty("broker.custom-data.endpoint"); // String defServiceToAppRelation = prop.getProperty("broker.service-to-app-relation"); String harvestType = prop.getProperty("broker.harvest-type"); String transportProtocolXpath = prop.getProperty("broker.custom-data.transport-protocol"); String authenticationMethod = prop.getProperty("broker.custom-data.authentication"); String authorizationMethod = prop.getProperty("broker.custom-data.authentication"); boolean hasHeader = Boolean.valueOf(prop.getProperty("broker.has-header")); try {//from www .ja v a 2s. co m FileInputStream file = new FileInputStream(new File(templateFile)); XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); if (hasHeader) { hasHeader = false; } else { Iterator<Cell> cellIterator = row.cellIterator(); String name = cellIterator.next().getStringCellValue(); String description = cellIterator.next().getStringCellValue(); String originProtocol = cellIterator.next().getStringCellValue(); String resourceName = cellIterator.next().getStringCellValue(); // String location = cellIterator.next().getStringCellValue(); // String originSystem = cellIterator.next().getStringCellValue(); // String targetProtocol = cellIterator.next().getStringCellValue(); // String targetSystem = cellIterator.next().getStringCellValue(); String version = getVersion(name); logger.info("Asset: " + name + " - " + version); UnicomerEntity entity = new UnicomerEntity(defaultAssetType, name, name, description, version, ArtifactAlgorithm.DEFAULT); entity.addCategorization("LineOfBusiness", prop.getProperty("broker.line-of-business")); entity.addCategorization("AssetLifecycleStage", prop.getProperty("default.asset-lifecycle-stage")); entity.addCategorization("Technology", prop.getProperty("broker.technology")); entity.addCategorization("Region", prop.getProperty("default.region")); entity.addHarvesterProperty("Modulo", harvestType); entity.addHarvesterProperty("Harvester Description", prop.getProperty("default.harvester-description")); entity.addCustomData("acquisition-method", prop.getProperty("default.acquisition-method")); entity.addCustomData(resourceEnvironmentXPath, defEnvironment); entity.addCustomData(resourceNameXPath, resourceName); entity.addCustomData(transportProtocolXpath, originProtocol); entity.addCustomData("authentication-method", authenticationMethod); entity.addCustomData("authorization-method", authorizationMethod); entity.addCustomData("has-test-scripts", "true"); entity.addCustomData("needs-performance-testing", "false"); entity.addCustomData("has-automated-testing", "false"); entity.addCustomData("consistent-with-business-mission", "true"); entity.addCustomData("passes-legal-review", "true"); entity.addCustomData("approved-for-internal-use", "true"); entity.addCustomData("approved-for-external-use", "false"); entity.addCustomData("passes-technical-review", "true"); entity.addCustomData("downtime-impact", prop.getProperty("broker.downtime-impact")); entity.addCustomData("license-terms", prop.getProperty("broker.license-terms")); servicesMap.put(name, entity); } } workbook.close(); file.close(); list.add(new HashSet<Entity>(servicesMap.values())); for (Set<Entity> entitySet : list) { if (entitySet != null && entitySet.size() > 0) { YamlWriter.writeToYaml(entitySet, harvestType); } } } catch (Exception e) { e.printStackTrace(); } return list; }
From source file:com.vertec.daoimpl.AttendanceDAOImpl.java
public List<Object> readexcel(String path) { // String data = ""; List<Object> table = new ArrayList<Object>(); try {/* www . ja v a 2 s. c o m*/ // Get the workbook instance for XLSX file XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(path)); // Get first sheet from the workbook XSSFSheet sheet = wb.getSheetAt(0); Row row; Cell cell; // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { row = rowIterator.next(); // For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); List<String> rows = new ArrayList<String>(); while (cellIterator.hasNext()) { cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: rows.add(cell.getRichStringCellValue().getString()); // data+=cell.getRichStringCellValue().getString(); // System.out.print(cell.getRichStringCellValue().getString()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { rows.add(cell.getDateCellValue() + ""); // data+=cell.getDateCellValue(); // System.out.print(cell.getDateCellValue()); } else { rows.add(cell.getNumericCellValue() + ""); // data+=cell.getNumericCellValue(); // System.out.print(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: rows.add(cell.getBooleanCellValue() + ""); // data+=cell.getBooleanCellValue(); // System.out.print(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: rows.add(cell.getCellFormula() + ""); // data+=cell.getCellFormula(); // System.out.print(cell.getCellFormula()); break; default: // System.out.print(""); } // data += "-"; // System.out.print(" - "); } table.add(rows); // data += ";;;"; // System.out.println(";;;"); } } catch (Exception e) { System.err.println("Exception :" + e.getMessage()); } return table; }