List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook close
@Override
public void close() throws IOException
From source file:ru.inkontext.poi.CreateCustomPivotTable.java
License:Apache License
public static void main(String[] args) throws IOException, InvalidFormatException { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet();//from w w w.j av a 2 s .c o m //Create some data to build the pivot table on setCellData(sheet); new CustomPivotTable(sheet, "A1:D6", "F3").addRowLabel(0) // set first column as 1-th level of rows .excludeSubTotal(0) // excude subtotal .addRowLabel(1) // set second column of source as 2-th level of rows .addColLabel(3).setFormatPivotField(3, 9).addColumnLabel(DataConsolidateFunction.SUM, 2) // Sum up the second column .setFormatDataField(2, 4); //# ##0.00 FileOutputStream fileOut = new FileOutputStream("custom-pivottable.xlsx"); wb.write(fileOut); fileOut.close(); wb.close(); }
From source file:ru.inkontext.poi.CreatePivotTableSimple.java
License:Apache License
public static void main(String[] args) throws IOException, InvalidFormatException { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet();/*from w w w. ja v a 2 s.co m*/ //Create some data to build the pivot table on setCellData(sheet); XSSFPivotTable pivotTable = sheet.createPivotTable(new AreaReference("A1:C6", SpreadsheetVersion.EXCEL2007), new CellReference("E3")); pivotTable.addRowLabel(1); // set second column as 1-th level of rows setFormatPivotField(pivotTable, 1, 9); //set format numFmtId=9 0% pivotTable.addRowLabel(0); // set first column as 2-th level of rows pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 2); // Sum up the second column setFormatDataField(pivotTable, 2, 3); //numFmtId=3 # ##0 FileOutputStream fileOut = new FileOutputStream("stackoverflow-pivottable.xlsx"); wb.write(fileOut); fileOut.close(); wb.close(); }
From source file:scoretracker.beans.EJB.UploadService.java
public Boolean upload(Part file) throws IOException { Boolean upload = true;/* ww w . j a va 2 s . c o m*/ InputStream inputStream = file.getInputStream(); Query q; em = emf.createEntityManager(); XSSFWorkbook workbook = new XSSFWorkbook(inputStream); XSSFSheet firstSheet = workbook.getSheetAt(0); Iterator<Row> iterator = firstSheet.iterator(); List<Teststudent> studenten = new ArrayList<Teststudent>(); Test test = new Test(); //Klas ophalen uit Excel Row nextRow = iterator.next(); q = em.createNamedQuery("Klas.findByName"); List<Klas> klassen = new ArrayList<>(); q.setParameter("name", nextRow.getCell(1).getStringCellValue()); klassen = q.getResultList(); if (klassen.size() != 1) { //vak ophalen uit Excel nextRow = iterator.next(); q = em.createNamedQuery("Course.findByName"); List<Course> courses = new ArrayList<>(); q.setParameter("name", nextRow.getCell(1).getStringCellValue()); courses = q.getResultList(); if (courses.size() != 1) { //Test object aanmaken nextRow = iterator.next(); test.setClassId(klassen.get(0)); test.setCourseId(courses.get(0)); test.setName(nextRow.getCell(1).getStringCellValue()); nextRow = iterator.next(); Double maxScore = nextRow.getCell(1).getNumericCellValue(); test.setMaxScore(maxScore.intValue()); nextRow = firstSheet.getRow(5); while (iterator.hasNext()) { nextRow = iterator.next(); List<Student> students = new ArrayList<>(); q = em.createNamedQuery("Student.findByRNr"); q.setParameter("rNr", nextRow.getCell(0).getStringCellValue()); students = q.getResultList(); if (students.size() != 1) { Student student = students.get(0); Teststudent testStudent = new Teststudent(); testStudent.setTestId(test); testStudent.setStudentId(student); Double score = nextRow.getCell(2).getNumericCellValue(); testStudent.setScore(score.intValue()); studenten.add(testStudent); } else { upload = false; } } } else { upload = false; } } else { upload = false; } workbook.close(); inputStream.close(); //fouthandeling en toevoegen if (upload) { em.persist(test); for (Teststudent t : studenten) { em.persist(t); } } else { upload = false; } return upload; }
From source file:steffen.haertlein.file.FileObject.java
License:Apache License
private void readExcelDocument() { try {/*from w ww. ja va2s . c o m*/ FileInputStream fs = new FileInputStream(f); XSSFWorkbook wb = new XSSFWorkbook(fs); XSSFSheet sh; String text = ""; for (int i = 0; i < wb.getNumberOfSheets(); i++) { sh = wb.getSheetAt(i); for (int j = sh.getFirstRowNum(); j <= sh.getLastRowNum(); j++) { XSSFRow currRow = sh.getRow(j); if (currRow == null || currRow.getFirstCellNum() == -1) { continue; } else { for (int k = currRow.getFirstCellNum(); k < currRow.getLastCellNum(); k++) { if (currRow.getCell(k, Row.RETURN_BLANK_AS_NULL) == null) { continue; } else { text += currRow.getCell(k) + "; "; } } text += System.lineSeparator(); } } } fs.close(); wb.close(); String[] xlsxLines = text.split(System.lineSeparator()); for (String line : xlsxLines) { lines.add(line); } } catch (IOException e) { JOptionPane.showMessageDialog(null, "Fehler in readExcelDocument", "Fehler", JOptionPane.ERROR_MESSAGE); e.printStackTrace(); } }
From source file:Test.LeerExcelXlsx.java
public static void main(String args[]) throws IOException { FileInputStream file = new FileInputStream( new File("C:\\Users\\f2 a55m-s1\\Documents\\baseSqlLite\\Libro1.xlsx")); // Crear el objeto que tendra el libro de Excel XSSFWorkbook workbook = new XSSFWorkbook(file); /*/*from w w w. j av a 2 s . co m*/ * 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 while (rowIterator.hasNext()) { row = rowIterator.next(); // Obtenemos el iterator que permite recorres todas las celdas de una fila Iterator<Cell> cellIterator = row.cellIterator(); Cell celda; while (cellIterator.hasNext()) { celda = cellIterator.next(); // 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)) { System.out.print(celda.getDateCellValue()); } else { System.out.print(celda.getNumericCellValue()); } break; case Cell.CELL_TYPE_STRING: System.out.print(celda.getStringCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: System.out.print(celda.getBooleanCellValue()); break; } } System.out.println(""); } // cerramos el libro excel workbook.close(); }