List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook close
@Override
public void close() throws IOException
From source file:de.tum.in.socket.server.ReadExcel.java
License:Apache License
/** * Reads the excel// ww w .ja va2 s . c o m */ public static List<RealtimeData> read() throws IOException { System.out.println("Reading dummy data from Excel....."); final List<RealtimeData> data = Lists.newArrayList(); XSSFWorkbook workbook = null; try { final File file = new File("testdata.xlsx"); final FileInputStream fileStream = new FileInputStream(file); // Get the workbook instance for XLS file workbook = new XSSFWorkbook(fileStream); // Get first sheet from the workbook final XSSFSheet sheet = workbook.getSheetAt(0); // load data from excel file final RealtimeData bluetoothData = new RealtimeData(); loadDataToList(sheet, data, bluetoothData); } catch (final Exception e) { e.printStackTrace(); } finally { workbook.close(); } System.out.println("Reading dummy data from Excel.....Done"); return data; }
From source file:FilesHandlers.ExcelHandler.java
/** * used for getting the content of the selected file * * @param file The name of the file to display * @param sheet The sheet number//from w w w. j ava 2 s .co m * @return The content of given sheet * @throws java.io.FileNotFoundException */ public ArrayList<String[]> getFileCtBySheet(String file, int sheet) throws FileNotFoundException, IOException { ArrayList<String[]> list = new ArrayList<String[]>(); ArrayList<String> row = new ArrayList<>(); File selectedFile = new File(this.workingDirectory.concat(file)); FileInputStream inputStream = new FileInputStream(selectedFile); XSSFWorkbook workbook = new XSSFWorkbook(inputStream); Sheet firstSheet = workbook.getSheetAt(sheet); Iterator<Row> iterator = firstSheet.iterator(); while (iterator.hasNext()) { Row nextRow = iterator.next(); Iterator<Cell> cellIterator = nextRow.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: row.add((String) cell.getStringCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: row.add("" + cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: row.add("" + cell.getNumericCellValue()); break; } } list.add(row.toArray(new String[list.size()])); row = new ArrayList<>(); } workbook.close(); inputStream.close(); return list; }
From source file:fr.paris.lutece.plugins.appointment.service.AppointmentUtilities.java
License:Open Source License
/** * Build the excel fil of the list of the appointments found in the manage appointment viw by filter * /*from w w w .j av a2s . c om*/ * @param strIdForm * the form id * @param response * the response * @param locale * the local * @param listAppointmentsDTO * the list of the appointments to input in the excel file * @param stateService * the state service */ public static void buildExcelFileWithAppointments(String strIdForm, HttpServletResponse response, Locale locale, List<AppointmentDTO> listAppointmentsDTO, StateService stateService) { AppointmentFormDTO tmpForm = FormService.buildAppointmentFormLight(Integer.parseInt(strIdForm)); XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet(I18nService.getLocalizedString(KEY_RESOURCE_TYPE, locale)); List<Object[]> tmpObj = new ArrayList<Object[]>(); EntryFilter entryFilter = new EntryFilter(); entryFilter.setIdResource(Integer.valueOf(strIdForm)); List<Entry> listEntry = EntryHome.getEntryList(entryFilter); Map<Integer, String> mapDefaultValueGenAttBackOffice = new HashMap<Integer, String>(); for (Entry e : listEntry) { if (e.isOnlyDisplayInBack()) { e = EntryHome.findByPrimaryKey(e.getIdEntry()); if (e.getFields() != null && e.getFields().size() == 1 && !StringUtils.isEmpty(e.getFields().get(0).getValue())) { mapDefaultValueGenAttBackOffice.put(e.getIdEntry(), e.getFields().get(0).getValue()); } else if (e.getFields() != null) { for (Field field : e.getFields()) { if (field.isDefaultValue()) { mapDefaultValueGenAttBackOffice.put(e.getIdEntry(), field.getValue()); } } } } } int nTaille = 10 + (listEntry.size() + 1); if (tmpForm != null) { int nIndex = 0; Object[] strWriter = new String[1]; strWriter[0] = tmpForm.getTitle(); tmpObj.add(strWriter); Object[] strInfos = new String[nTaille]; strInfos[0] = I18nService.getLocalizedString(KEY_COLUMN_LAST_NAME, locale); strInfos[1] = I18nService.getLocalizedString(KEY_COLUMN_FISRT_NAME, locale); strInfos[2] = I18nService.getLocalizedString(KEY_COLUMN_EMAIL, locale); strInfos[3] = I18nService.getLocalizedString(KEY_COLUMN_DATE_APPOINTMENT, locale); strInfos[4] = I18nService.getLocalizedString(KEY_TIME_START, locale); strInfos[5] = I18nService.getLocalizedString(KEY_TIME_END, locale); strInfos[6] = I18nService.getLocalizedString(KEY_COLUMN_ADMIN, locale); strInfos[7] = I18nService.getLocalizedString(KEY_COLUMN_STATUS, locale); strInfos[8] = I18nService.getLocalizedString(KEY_COLUMN_STATE, locale); strInfos[9] = I18nService.getLocalizedString(KEY_COLUMN_NB_BOOKED_SEATS, locale); nIndex = 1; if (listEntry.size() > 0) { for (Entry e : listEntry) { strInfos[10 + nIndex] = e.getTitle(); nIndex++; } } tmpObj.add(strInfos); } if (listAppointmentsDTO != null) { for (AppointmentDTO appointmentDTO : listAppointmentsDTO) { int nIndex = 0; Object[] strWriter = new String[nTaille]; strWriter[0] = appointmentDTO.getLastName(); strWriter[1] = appointmentDTO.getFirstName(); strWriter[2] = appointmentDTO.getEmail(); strWriter[3] = appointmentDTO.getDateOfTheAppointment(); strWriter[4] = appointmentDTO.getStartingTime().toString(); strWriter[5] = appointmentDTO.getEndingTime().toString(); strWriter[6] = appointmentDTO.getAdminUser(); String status = I18nService.getLocalizedString(AppointmentDTO.PROPERTY_APPOINTMENT_STATUS_RESERVED, locale); if (appointmentDTO.getIsCancelled()) { status = I18nService.getLocalizedString(AppointmentDTO.PROPERTY_APPOINTMENT_STATUS_UNRESERVED, locale); } strWriter[7] = status; State stateAppointment = stateService.findByResource(appointmentDTO.getIdAppointment(), Appointment.APPOINTMENT_RESOURCE_TYPE, tmpForm.getIdWorkflow()); String strState = StringUtils.EMPTY; if (stateAppointment != null) { appointmentDTO.setState(stateAppointment); strState = stateAppointment.getName(); } strWriter[8] = strState; nIndex = 1; strWriter[9] = Integer.toString(appointmentDTO.getNbBookedSeats()); List<Integer> listIdResponse = AppointmentResponseService .findListIdResponse(appointmentDTO.getIdAppointment()); List<Response> listResponses = new ArrayList<Response>(); for (int nIdResponse : listIdResponse) { Response resp = ResponseHome.findByPrimaryKey(nIdResponse); if (resp != null) { listResponses.add(resp); } } for (Entry e : listEntry) { Integer key = e.getIdEntry(); StringBuffer strValue = new StringBuffer(StringUtils.EMPTY); String strPrefix = StringUtils.EMPTY; for (Response resp : listResponses) { String strRes = StringUtils.EMPTY; if (key.equals(resp.getEntry().getIdEntry())) { Field f = resp.getField(); int nfield = 0; if (f != null) { nfield = f.getIdField(); Field field = FieldHome.findByPrimaryKey(nfield); if (field != null) { strRes = field.getTitle(); } } else { strRes = resp.getResponseValue(); } } if ((strRes != null) && !strRes.isEmpty()) { strValue.append(strPrefix + strRes); strPrefix = CONSTANT_COMMA; } } if (strValue.toString().isEmpty() && mapDefaultValueGenAttBackOffice.containsKey(key)) { strValue.append(mapDefaultValueGenAttBackOffice.get(key)); } if (!strValue.toString().isEmpty()) { strWriter[10 + nIndex] = strValue.toString(); } nIndex++; } tmpObj.add(strWriter); } } int nRownum = 0; for (Object[] myObj : tmpObj) { Row row = sheet.createRow(nRownum++); int nCellnum = 0; for (Object strLine : myObj) { Cell cell = row.createCell(nCellnum++); if (strLine instanceof String) { cell.setCellValue((String) strLine); } else if (strLine instanceof Boolean) { cell.setCellValue((Boolean) strLine); } else if (strLine instanceof Date) { cell.setCellValue((Date) strLine); } else if (strLine instanceof Double) { cell.setCellValue((Double) strLine); } } } try { String now = new SimpleDateFormat("yyyyMMdd-hhmm") .format(GregorianCalendar.getInstance(locale).getTime()) + "_" + I18nService.getLocalizedString(KEY_RESOURCE_TYPE, locale) + EXCEL_FILE_EXTENSION; response.setContentType(EXCEL_MIME_TYPE); response.setHeader("Content-Disposition", "attachment; filename=\"" + now + "\";"); response.setHeader("Pragma", "public"); response.setHeader("Expires", "0"); response.setHeader("Cache-Control", "must-revalidate,post-check=0,pre-check=0"); OutputStream os = response.getOutputStream(); workbook.write(os); os.close(); workbook.close(); } catch (IOException e) { AppLogService.error(e); } }
From source file:gov.anl.cue.arcane.engine.matrix.MatrixEngine.java
License:Open Source License
/** * Read parameters.//from ww w .ja v a2s . co m * * @param fileName the file name * @throws IOException Signals that an I/O exception has occurred. * @throws FileNotFoundException the file not found exception */ public void readParameters(String fileName) throws IOException, FileNotFoundException { // Attempt to open the spreadsheet. XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(new File(fileName))); // Scan the sheets. Iterator<XSSFSheet> sheets = workbook.iterator(); // Read the first sheet. XSSFSheet sheet = sheets.next(); // Scan the rows. for (Row row : sheet) { // Get the next row label. String label = row.getCell(0).getStringCellValue(); // Check the label type. if (label.equals("Random Seed")) { // Read the value. this.randomSeed = (int) row.getCell(1).getNumericCellValue(); // Check the next label type. } else if (label.equals("Population Size")) { // Read the value. this.populationSize = (int) row.getCell(1).getNumericCellValue(); // Check the next label type. } else if (label.equals("Kill Fraction")) { // Read the value. this.killFraction = row.getCell(1).getNumericCellValue(); // Check the next label type. } else if (label.equals("Crossover Probability")) { // Read the value. this.crossoverProbability = row.getCell(1).getNumericCellValue(); // Check the next label type. } else if (label.equals("Mutation Probability for Cells")) { // Read the value. this.mutationProbabilityForCells = row.getCell(1).getNumericCellValue(); // Check the next label type. } else if (label.equals("Maximum New Term Count")) { // Read the value. this.maximumNewTermCount = (int) row.getCell(1).getNumericCellValue(); // Check the next label type. } else if (label.equals("Addition Probability")) { // Read the value. this.additionProbability = row.getCell(1).getNumericCellValue(); // Check the next label type. } else if (label.equals("Subtraction Probability")) { // Read the value. this.subtractionProbability = row.getCell(1).getNumericCellValue(); // Check the next label type. } else if (label.equals("Multiplication Probability")) { // Read the value. this.multiplicationProbability = row.getCell(1).getNumericCellValue(); // Check the next label type. } else if (label.equals("Accumulate Add Probability")) { // Read the value. this.accumulateAddProbability = row.getCell(1).getNumericCellValue(); // Check the next label type. } else if (label.equals("Accumulate Subtract Probability")) { // Read the value. this.accumulateSubtractProbability = row.getCell(1).getNumericCellValue(); } } // Close the workbook. workbook.close(); }
From source file:gov.anl.cue.arcane.engine.matrix.MatrixModel.java
License:Open Source License
/** * Imports the dimensions for a matrix model from a * template spreadsheet./*from www.j ava2 s. co m*/ * * @param fileName the file name * @return the matrix dimensions */ public static HashMap<Integer, Integer> importTemplateDimensions(String fileName) { // Create the results holder. HashMap<Integer, Integer> nodeCounts = new HashMap<Integer, Integer>(); // Try to read the spreadsheet. try { // Attempt to open the template spreadsheet. XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(new File(fileName))); // Scan the sheets. Iterator<XSSFSheet> sheets = workbook.iterator(); // Ignore the first sheet. XSSFSheet sheet = sheets.next(); sheet = sheets.next(); // Prepare to scan the node count column. Iterator<Row> rowIterator = sheet.rowIterator(); // Skip the header row. rowIterator.next(); // Find the total number of nodes requested. int rowIndex = 0; Double nextCellValue = Util.getSpreadsheetNumber(sheet, rowIterator.next().getRowNum(), 2); while (!Double.isNaN(nextCellValue)) { // Store the results. nodeCounts.put(rowIndex++, nextCellValue.intValue()); // Get the next node count. nextCellValue = Util.getSpreadsheetNumber(sheet, rowIterator.next().getRowNum(), 2); } // Close the workbook. workbook.close(); // Catch errors. } catch (Exception e) { // Note an error. nodeCounts = null; } // Return the results. return nodeCounts; }
From source file:gov.anl.cue.arcane.engine.matrix.MatrixModel.java
License:Open Source License
/** * Reads the dimensions for a matrix model from a spreadsheet. * This method is necessary because Excel spreadsheets * do not reliably store the row and column dimension * in the meta-information. The values that are stored * there are not guaranteed to be correct in all cases. * * @param fileName the file name * @return the matrix dimensions// w w w .j a v a 2s.c om */ public static MatrixDimensions readDimensions(String fileName) { // Create the results holder. MatrixDimensions matrixDimensions = new MatrixModel.MatrixDimensions(); // Try to read the spreadsheet. try { // Attempt to open the spreadsheet. XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(new File(fileName))); // Scan the sheets. Iterator<XSSFSheet> sheets = workbook.iterator(); // Skip the first sheet. XSSFSheet sheet = sheets.next(); // Move to the sheet for the first variable. sheet = sheets.next(); // Find the number of rows. matrixDimensions.rows = sheet.getLastRowNum(); // Prepare to check the first row. Iterator<Row> rowIterator = sheet.iterator(); // Check the header row length Row row = rowIterator.next(); matrixDimensions.columns = row.getLastCellNum() - 2; // Close the workbook. workbook.close(); // Catch errors. } catch (Exception e) { // Note an error. matrixDimensions = null; } // Return the results. return matrixDimensions; }
From source file:gov.anl.cue.arcane.engine.UtilTest.java
License:Open Source License
/** * Test get spreadsheet number./*w w w. j a v a 2s .co m*/ * * @throws Exception the exception */ @Test public void testGetSpreadsheetNumber() throws Exception { // Attempt to open the spreadsheet. XSSFWorkbook workbook = new XSSFWorkbook( new FileInputStream(new File(Util.INPUT_DIR + "//" + Util.TEST_DIR + "//Test.xlsx"))); // Prepare to access the sheets. Iterator<XSSFSheet> sheets = workbook.iterator(); // Read the first sheet. XSSFSheet sheet = sheets.next(); // Check the results. Assert.assertEquals((Double) Util.getSpreadsheetNumber(sheet, 0, 0), (Double) 1.0); Assert.assertEquals((Double) Util.getSpreadsheetNumber(sheet, 1, 0), (Double) 1.0); // Close the workbook. workbook.close(); }
From source file:gov.anl.cue.arcane.engine.UtilTest.java
License:Open Source License
/** * Test get spreadsheet string./* w w w . jav a2 s .c o m*/ * * @throws Exception the exception */ @Test public void testGetSpreadsheetString() throws Exception { // Attempt to open the spreadsheet. XSSFWorkbook workbook = new XSSFWorkbook( new FileInputStream(new File(Util.INPUT_DIR + "//" + Util.TEST_DIR + "//Test.xlsx"))); // Prepare to access the sheets. Iterator<XSSFSheet> sheets = workbook.iterator(); // Read the first sheet. XSSFSheet sheet = sheets.next(); // Check the results. Assert.assertTrue(Util.getSpreadsheetString(sheet, 2, 0).equals("Test text")); Assert.assertTrue(Util.getSpreadsheetString(sheet, 3, 0).equals("Another string")); // Close the workbook. workbook.close(); }
From source file:gov.anl.cue.arcane.engine.UtilTest.java
License:Open Source License
/** * Test get spreadsheet boolean.//from w w w . j av a2 s. c o m * * @throws Exception the exception */ @Test public void testGetSpreadsheetBoolean() throws Exception { // Attempt to open the spreadsheet. XSSFWorkbook workbook = new XSSFWorkbook( new FileInputStream(new File(Util.INPUT_DIR + "//" + Util.TEST_DIR + "//Test.xlsx"))); // Prepare to access the sheets. Iterator<XSSFSheet> sheets = workbook.iterator(); // Read the first sheet. XSSFSheet sheet = sheets.next(); // Check the results. Assert.assertTrue(Util.getSpreadsheetBoolean(sheet, 4, 0)); Assert.assertFalse(Util.getSpreadsheetBoolean(sheet, 5, 0)); // Close the workbook. workbook.close(); }
From source file:gov.va.isaac.isaacDbProcessingRules.spreadsheet.SpreadsheetReader.java
License:Apache License
public List<RuleDefinition> readSpreadSheet(InputStream is) throws IOException { XSSFWorkbook ss = new XSSFWorkbook(is); Sheet sheet = ss.getSheetAt(1);/*from w w w .j av a2 s . com*/ int i = readHeaders(sheet); for (; i <= sheet.getLastRowNum(); i++) { Row r = sheet.getRow(i); if (r != null) { ArrayList<Cell> values = new ArrayList<>(); for (int col = 0; col < columnHeaders.size(); col++) { values.add(r.getCell(col)); } data.add(values); } } //Have read the entire spreadsheet - now process into our 'rule' format ArrayList<RuleDefinition> result = new ArrayList<>(); for (int rowNum = 0; rowNum <= data.size(); rowNum++) { RuleDefinition rd = new RuleDefinition(); Integer id = readIntColumn(rowNum, "ID"); if (id == null) { //blank row? continue; } rd.id = id; rd.date = readDateColumn(rowNum, version == 1 ? "Date" : "Timestamp"); rd.action = Action.parse(readStringColumn(rowNum, "Action")); rd.sctFSN = readStringColumn(rowNum, version == 1 ? "SCT FSN" : "FSN"); try { rd.sctID = readLongColumn(rowNum, version == 1 ? "SCT ID" : "SCT_ID"); } catch (IllegalStateException e) { String temp = readStringColumn(rowNum, version == 1 ? "SCT ID" : "SCT_ID"); if (temp != null) { rd.sctID = Long.parseLong(temp); } } rd.author = readStringColumn(rowNum, "Author"); if (version == 1) { rd.comments = readStringColumn(rowNum, "Comments"); } ArrayList<SelectionCriteria> criteria = new ArrayList<>(); while (true) { SelectionCriteria sc = new SelectionCriteria(); if (version == 1) { sc.operand = readOperand(rowNum); sc.type = SelectionCriteriaType.parse(readStringColumn(rowNum, "Type")); } else { sc.type = SelectionCriteriaType.RXCUI; } try { //If we read a long, as a string, we get an extra .0 on the end - so read as a long first, if it is one. sc.value = readLongColumn(rowNum, version == 1 ? "Value" : "RXCUI").toString(); } catch (IllegalStateException e) { sc.value = readStringColumn(rowNum, version == 1 ? "Value" : "RXCUI"); } if (version == 1) { sc.valueId = readStringColumn(rowNum, "Value ID"); } criteria.add(sc); //peak at the next row, see if it is an additional criteria, or a new rule Integer nextId = readIntColumn(rowNum + 1, "ID"); //if the next row has an id, its a new rule String nextType = readStringColumn(rowNum + 1, "Type"); //check to see if we hit the end of the rows if (nextId != null || nextType == null) { break; } else //more criteria for this rule { rowNum++; } } rd.criteria = criteria; result.add(rd); } ss.close(); return result; }