Example usage for org.apache.poi.ss.usermodel WorkbookFactory create

List of usage examples for org.apache.poi.ss.usermodel WorkbookFactory create

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. WorkbookFactory
  6. create

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel WorkbookFactory create.

Prototype

public static Workbook create(File file) throws IOException, EncryptedDocumentException

Source Link

Document

Creates the appropriate HSSFWorkbook / XSSFWorkbook from the given File, which must exist and be readable.

Usage

From source file:com.crimelab.service.PiServiceImpl.java

@Override
public Workbook create(PiReport piReport, HttpSession session) {
    Workbook wb = null;/*from  www  .j  av a 2  s  .c om*/
    try {
        InputStream inp = session.getServletContext().getResourceAsStream("/WEB-INF/templates/Default.xls");
        wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheetAt(0);

        Cell examType = findCell(sheet, "$examType");
        Cell reportNo = findCell(sheet, "$reportNo");
        Cell caseType = findCell(sheet, "$caseType");
        Cell suspects = findCell(sheet, "$suspects");
        Cell victims = findCell(sheet, "$victims");
        Cell timeDateReceived = findCell(sheet, "$timeDateReceived");
        Cell requestingParty = findCell(sheet, "$requestingParty");
        Cell specimenSubmitted = findCell(sheet, "$specimenSubmitted");
        Cell purposeOfLabExam = findCell(sheet, "$purposeOfLabExam");
        Cell findings = findCell(sheet, "$findings");
        Cell conclusions = findCell(sheet, "$conclusions");
        Cell remarks = findCell(sheet, "$remarks");
        Cell timeDateCompleted = findCell(sheet, "$timeDateCompleted");
        Cell examinerName = findCell(sheet, "$examinerName");
        Cell examinerRank = findCell(sheet, "$examinerRank");
        Cell examinerPosition = findCell(sheet, "$examinerPosition");
        Cell appName = findCell(sheet, "$appName");
        Cell appRank = findCell(sheet, "$appRank");
        Cell appPosition = findCell(sheet, "$appPosition");
        Cell notedName = findCell(sheet, "$notedName");
        Cell notedRank = findCell(sheet, "$notedRank");
        Cell notedPosition = findCell(sheet, "$notedPosition");

        examType.setCellValue(piReport.getExamType());
        reportNo.setCellValue(piReport.getReportNo());
        caseType.setCellValue(piReport.getCaseType());
        suspects.setCellValue(piReport.getSuspects());
        victims.setCellValue(piReport.getVictims());
        timeDateReceived.setCellValue(piReport.getTimeDateReceived());
        requestingParty.setCellValue(piReport.getRequestingParty());
        specimenSubmitted.setCellValue(piReport.getSpecimenSubmitted());
        purposeOfLabExam.setCellValue(piReport.getPurposeOfLabExam());
        findings.setCellValue(piReport.getFindings());
        conclusions.setCellValue(piReport.getConclusions());
        remarks.setCellValue(piReport.getRemarks());
        timeDateCompleted.setCellValue(piReport.getTimeDateCompleted());
        examinerName.setCellValue(piReport.getExaminerName());
        examinerRank.setCellValue(piReport.getExaminerRank());
        examinerPosition.setCellValue(piReport.getExaminerPosition());
        appName.setCellValue(piReport.getAppName());
        appRank.setCellValue(piReport.getAppRank());
        appPosition.setCellValue(piReport.getAppPosition());
        notedName.setCellValue(piReport.getNotedName());
        notedRank.setCellValue(piReport.getNotedRank());
        notedPosition.setCellValue(piReport.getNotedPosition());

        piDAO.piReportInfo(piReport);
    } catch (Exception e) {
        e.printStackTrace();

    }
    return wb;
}

From source file:com.crimelab.service.QdServiceImpl.java

@Override
public Workbook create(Qd qd, HttpSession session) {
    Workbook wb = null;//from   ww w  .j  a  va  2  s .  co m

    try {
        InputStream inp = session.getServletContext().getResourceAsStream("/WEB-INF/templates/Default.xls");
        wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheetAt(0);

        Cell division = findCell(sheet, "$division");
        Cell reportNo = findCell(sheet, "$reportNo");
        Cell caseType = findCell(sheet, "$caseType");
        Cell suspect = findCell(sheet, "$suspect");
        Cell victim = findCell(sheet, "$victim");
        Cell timeDateReceived = findCell(sheet, "$timeDateReceived");
        Cell requestingParty = findCell(sheet, "$requestingParty");
        Cell specimenSubmitted = findCell(sheet, "$specimenSubmitted");
        Cell purposeOfLabExam = findCell(sheet, "$purposeOfLabExam");
        Cell findings = findCell(sheet, "$findings");
        Cell conclusion = findCell(sheet, "$conclusion");
        Cell remarks = findCell(sheet, "$remarks");
        Cell timeDateCompleted = findCell(sheet, "$timeDateCompleted");
        Cell examinerName = findCell(sheet, "$examinerName");
        Cell examinerRank = findCell(sheet, "$examinerRank");
        Cell examinerPosition = findCell(sheet, "$examinerPosition");
        Cell notedName = findCell(sheet, "$notedName");
        Cell notedRank = findCell(sheet, "$notedRank");
        Cell notedPosition = findCell(sheet, "$notedPosition");

        division.setCellValue(qd.getDivision());
        reportNo.setCellValue(qd.getReportNo());
        caseType.setCellValue(qd.getCaseType());
        suspect.setCellValue(qd.getSuspect());
        victim.setCellValue(qd.getVictim());
        timeDateReceived.setCellValue(qd.getTimeDateReceived());
        requestingParty.setCellValue(qd.getRequestingParty());
        specimenSubmitted.setCellValue(qd.getSpecimenSubmitted());
        purposeOfLabExam.setCellValue(qd.getPurposeOfLabExam());
        findings.setCellValue(qd.getFindings());
        conclusion.setCellValue(qd.getConclusion());
        remarks.setCellValue(qd.getRemarks());
        timeDateCompleted.setCellValue(qd.getTimeDateCompleted());
        examinerName.setCellValue(qd.getExaminerName());
        examinerRank.setCellValue(qd.getExaminerRank());
        examinerPosition.setCellValue(qd.getExaminerPosition());
        notedName.setCellValue(qd.getNotedName());
        notedRank.setCellValue(qd.getNotedRank());
        notedPosition.setCellValue(qd.getNotedPosition());

        qdDAO.qdInfo(qd);
    } catch (Exception e) {
        e.printStackTrace();
    }
    return wb;
}

From source file:com.devnexus.ting.web.controller.admin.RegistrationController.java

License:Apache License

@RequestMapping(value = "/s/admin/{eventKey}/uploadRegistration", method = RequestMethod.POST)
public ModelAndView uploadGroupRegistration(ModelAndView model, HttpServletRequest request,
        @PathVariable(value = "eventKey") String eventKey,
        @Valid @ModelAttribute("registerForm") UploadGroupRegistrationForm uploadForm,
        BindingResult bindingResult) throws FileNotFoundException, IOException, InvalidFormatException {

    EventSignup signUp = eventSignupRepository.getByEventKey(eventKey);
    model.getModelMap().addAttribute("event", signUp.getEvent());

    if (bindingResult.hasErrors()) {

        model.getModelMap().addAttribute("registerForm", uploadForm);
        model.setViewName("/admin/upload-registration");
    } else {/*from  w  w  w  . j  a  va2 s.c  om*/

        boolean hasErrors = false;

        try {

            Workbook wb = WorkbookFactory.create(uploadForm.getRegistrationFile().getInputStream());
            Sheet sheet = wb.getSheetAt(0);
            Cell contactNameCell = sheet.getRow(0).getCell(1, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
            Cell contactEmailCell = sheet.getRow(1).getCell(1, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
            Cell contactPhoneCell = sheet.getRow(2).getCell(1, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
            Cell registrationReferenceCell = sheet.getRow(3).getCell(1,
                    Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);

            if (contactNameCell.getStringCellValue().isEmpty()) {
                hasErrors = true;
                bindingResult.addError(new ObjectError("registrationFile", "Contact Name Empty"));
            }

            if (contactEmailCell.getStringCellValue().isEmpty()) {
                hasErrors = true;
                bindingResult.addError(new ObjectError("registrationFile", "Contact Email Empty"));
            }

            if (contactPhoneCell.getStringCellValue().isEmpty()) {
                hasErrors = true;
                bindingResult.addError(new ObjectError("registrationFile", "Contact Phone Empty"));
            }

            if (registrationReferenceCell.getStringCellValue().isEmpty()) {
                hasErrors = true;
                bindingResult.addError(new ObjectError("registrationFile", "Registration Reference Empty"));
            }

            if (!hasErrors) {
                RegistrationDetails details = new RegistrationDetails();
                details.setContactEmailAddress(contactEmailCell.getStringCellValue());
                details.setContactName(contactNameCell.getStringCellValue());
                details.setContactPhoneNumber(contactPhoneCell.getStringCellValue());
                details.setRegistrationFormKey(registrationReferenceCell.getStringCellValue());
                details.setEvent(signUp.getEvent());
                details.setFinalCost(BigDecimal.ZERO);
                details.setInvoice("Invoiced");
                details.setPaymentState(RegistrationDetails.PaymentState.PAID);
                int attendeeRowIndex = 7;

                Row attendeeRow = sheet.getRow(attendeeRowIndex);
                while (attendeeRow != null) {
                    attendeeRow = sheet.getRow(attendeeRowIndex);
                    if (attendeeRow != null) {
                        Cell firstName = attendeeRow.getCell(0, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                        Cell lastName = attendeeRow.getCell(1, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                        Cell emailAddress = attendeeRow.getCell(2, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                        Cell city = attendeeRow.getCell(3, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                        Cell state = attendeeRow.getCell(4, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                        Cell country = attendeeRow.getCell(5, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                        Cell ticketType = attendeeRow.getCell(6, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                        Cell company = attendeeRow.getCell(7, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                        Cell jobTitle = attendeeRow.getCell(8, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                        Cell tshirtSize = attendeeRow.getCell(9, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                        Cell vegetarian = attendeeRow.getCell(10, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                        Cell sponsorMessages = attendeeRow.getCell(11,
                                Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);

                        if (firstName.getStringCellValue().isEmpty()) {
                            break;
                        }

                        if (lastName.getStringCellValue().isEmpty()) {
                            bindingResult.addError(new ObjectError("registrationFile",
                                    " row " + (attendeeRowIndex + 1) + " missing information : lastName"));
                            hasErrors = true;
                            break;
                        }
                        if (emailAddress.getStringCellValue().isEmpty()) {
                            bindingResult.addError(new ObjectError("registrationFile",
                                    " row " + (attendeeRowIndex + 1) + " missing information : emailAddress"));
                            hasErrors = true;
                            break;
                        }
                        if (city.getStringCellValue().isEmpty()) {
                            bindingResult.addError(new ObjectError("registrationFile",
                                    " row " + (attendeeRowIndex + 1) + " missing information : city"));
                            hasErrors = true;
                            break;
                        }
                        if (state.getStringCellValue().isEmpty()) {
                            bindingResult.addError(new ObjectError("registrationFile",
                                    " row " + (attendeeRowIndex + 1) + " missing information : state "));
                            hasErrors = true;
                            break;
                        }
                        if (country.getStringCellValue().isEmpty()) {
                            bindingResult.addError(new ObjectError("registrationFile",
                                    " row " + (attendeeRowIndex + 1) + " missing information : country"));
                            hasErrors = true;
                            break;
                        }
                        if (company.getStringCellValue().isEmpty()) {
                            bindingResult.addError(new ObjectError("registrationFile",
                                    " row " + (attendeeRowIndex + 1) + " missing information : company"));
                            hasErrors = true;
                            break;
                        }
                        if (jobTitle.getStringCellValue().isEmpty()) {
                            bindingResult.addError(new ObjectError("registrationFile",
                                    " row " + (attendeeRowIndex + 1) + " missing information : jobTitle"));
                            hasErrors = true;
                            break;
                        }

                        if (ticketType.getStringCellValue().isEmpty()) {
                            bindingResult.addError(new ObjectError("registrationFile",
                                    " row " + (attendeeRowIndex + 1) + " missing information: ticket type"));
                            hasErrors = true;
                            break;
                        }

                        if (tshirtSize.getStringCellValue().isEmpty()) {
                            bindingResult.addError(new ObjectError("registrationFile",
                                    " row " + (attendeeRowIndex + 1) + " missing information: t shirt "));
                            hasErrors = true;
                            break;
                        }
                        if (vegetarian.getStringCellValue().isEmpty()
                                || !(vegetarian.getStringCellValue().equalsIgnoreCase("no")
                                        || vegetarian.getStringCellValue().equalsIgnoreCase("yes"))) {
                            bindingResult.addError(
                                    new ObjectError("registrationFile", " row " + (attendeeRowIndex + 1)
                                            + " missing information. Vegetarian option should be yes or no "));
                            hasErrors = true;
                            break;
                        }
                        if (sponsorMessages.getStringCellValue().isEmpty()
                                || !(sponsorMessages.getStringCellValue().equalsIgnoreCase("no")
                                        || sponsorMessages.getStringCellValue().equalsIgnoreCase("yes"))) {
                            bindingResult.addError(
                                    new ObjectError("registrationFile", " row " + (attendeeRowIndex + 1)
                                            + " missing information. Sponsor message should be yes or no "));
                            hasErrors = true;
                            break;
                        }

                        TicketOrderDetail detail = new TicketOrderDetail();

                        detail.setCity(city.getStringCellValue());
                        detail.setCompany(company.getStringCellValue());
                        detail.setCouponCode("");
                        detail.setCountry(country.getStringCellValue());
                        detail.setEmailAddress(emailAddress.getStringCellValue());
                        detail.setFirstName(firstName.getStringCellValue());
                        detail.setJobTitle(jobTitle.getStringCellValue());
                        detail.setLastName(lastName.getStringCellValue());
                        detail.setSponsorMayContact(
                                sponsorMessages.getStringCellValue().equalsIgnoreCase("no") ? "false" : "true");
                        detail.setState(state.getStringCellValue());
                        detail.setTicketGroup(
                                Long.parseLong(ticketType.getStringCellValue().split("-\\|-")[1].trim()));

                        detail.setLabel(businessService.getTicketGroup(detail.getTicketGroup()).getLabel());

                        detail.settShirtSize(tshirtSize.getStringCellValue());
                        detail.setVegetarian(
                                vegetarian.getStringCellValue().equalsIgnoreCase("no") ? "false" : "true");
                        detail.setRegistration(details);
                        details.getOrderDetails().add(detail);

                        attendeeRowIndex++;

                    }
                }

                if (uploadForm.getOverrideRegistration()) {
                    try {
                        RegistrationDetails tempRegistration = businessService
                                .getRegistrationForm(registrationReferenceCell.getStringCellValue());
                        tempRegistration.getOrderDetails().forEach((oldDetail) -> {
                            oldDetail.setRegistration(null);
                        });
                        tempRegistration.getOrderDetails().clear();
                        tempRegistration.getOrderDetails().addAll(details.getOrderDetails());

                        tempRegistration.getOrderDetails().forEach((detail) -> {
                            detail.setRegistration(tempRegistration);
                        });
                        details = tempRegistration;

                        businessService.updateRegistration(details, uploadForm.getSendEmail());
                    } catch (EmptyResultDataAccessException ignore) {
                        businessService.updateRegistration(details, uploadForm.getSendEmail());
                    }
                } else {
                    try {
                        RegistrationDetails tempRegistration = businessService
                                .getRegistrationForm(registrationReferenceCell.getStringCellValue());
                        hasErrors = true;
                        bindingResult.addError(new ObjectError("registrationFile",
                                "Registration with this key exists, please check \"Replace Registrations\"."));
                    } catch (EmptyResultDataAccessException ignore) {
                        businessService.updateRegistration(details, uploadForm.getSendEmail());
                    }
                }

            }

        } catch (Exception ex) {
            hasErrors = true;
            Logger.getAnonymousLogger().log(Level.SEVERE, ex.getMessage(), ex);

            bindingResult.addError(new ObjectError("registrationFile", ex.getMessage()));
        }
        if (hasErrors) {
            model.setViewName("/admin/upload-registration");
        } else {
            model.setViewName("/admin/index");
        }
    }

    return model;
}

From source file:com.devnexus.ting.web.controller.admin.RegistrationController.java

License:Apache License

@RequestMapping(value = "/s/admin/{eventKey}/groupRegistration", method = RequestMethod.POST)
public ModelAndView downloadGroupRegistration(ModelAndView model, HttpServletRequest request,
        @PathVariable(value = "eventKey") String eventKey, @Valid RegisterForm form, BindingResult result)
        throws FileNotFoundException, IOException, InvalidFormatException {

    EventSignup signUp = eventSignupRepository.getByEventKey(eventKey);

    model.getModelMap().addAttribute("event", signUp.getEvent());
    model.getModelMap().addAttribute("registerForm", form);

    if (!result.hasErrors()) {
        Workbook workbook = WorkbookFactory
                .create(getClass().getResourceAsStream("/forms/registration_form.xlsx"));
        Sheet formSheet = workbook.getSheetAt(0);
        Sheet ticketTypeSheet = workbook.createSheet("ticket_types");

        Row contactNameRow = formSheet.getRow(0);
        Row contactEmailRow = formSheet.getRow(1);
        Row contactPhoneRow = formSheet.getRow(2);
        Row registrationReferenceRow = formSheet.getRow(3);

        String[] ticketTypes = formatTicketTypes(signUp);
        addTicketTypesToSheet(ticketTypes, ticketTypeSheet);

        contactNameRow.createCell(1).setCellValue(form.getContactName());
        contactEmailRow.createCell(1).setCellValue(form.getContactEmailAddress());
        contactPhoneRow.createCell(1).setCellValue(form.getContactPhoneNumber());
        registrationReferenceRow.createCell(1).setCellValue(UUID.randomUUID().toString());

        createTicketTypeDropDown(formSheet, ticketTypeSheet, ticketTypes);

        model.setView(new BulkRegistrationFormView(workbook,
                form.getContactName().replace(" ", "_") + "RegistrationFile.xlsx"));
    } else {//from   w  w w . j  a va  2 s . com
        model.setViewName("/admin/group-registration");
    }
    return model;
}

From source file:com.dickimawbooks.datatooltk.io.DatatoolExcel.java

License:Open Source License

public String[] getSheetNames(File file) throws IOException {
    if (!file.exists()) {
        throw new IOException(DatatoolTk.getLabelWithValue("error.io.file_not_found", "" + file));
    }/*from  ww w . j a  va2  s . c om*/

    Workbook workBook = null;

    try {
        workBook = WorkbookFactory.create(file);
    } catch (org.apache.poi.openxml4j.exceptions.InvalidFormatException e) {
        throw new IOException(DatatoolTk.getLabelWithValue("error.unknown_file_format", file.getName()), e);
    }

    int numSheets = workBook.getNumberOfSheets();

    String[] names = new String[numSheets];

    for (int i = 0; i < numSheets; i++) {
        names[i] = workBook.getSheetName(i);
    }

    return names;
}

From source file:com.dickimawbooks.datatooltk.io.DatatoolExcel.java

License:Open Source License

public DatatoolDb importData(File file) throws DatatoolImportException {
    DatatoolDb db = new DatatoolDb(settings);

    try {/*from  w w  w.  j  a va2s.c o  m*/
        if (!file.exists()) {
            throw new IOException(DatatoolTk.getLabelWithValue("error.io.file_not_found", "" + file));
        }

        if (file.getName().toLowerCase().endsWith(".xlsx")) {
            throw new IOException(DatatoolTk.getLabel("error.xlsx_not_supported"));
        }

        Workbook workBook = WorkbookFactory.create(file);
        Sheet sheet;

        String sheetRef = settings.getSheetRef();

        int sheetIdx = 0;
        String sheetName = null;

        try {
            sheetIdx = Integer.parseInt(sheetRef);
        } catch (NumberFormatException e) {
            sheetName = sheetRef;
        }

        if (sheetName == null) {
            sheet = workBook.getSheetAt(sheetIdx);
            db.setName(sheet.getSheetName());
        } else {
            sheet = workBook.getSheet(sheetName);
            db.setName(sheetName);
        }

        Iterator<Row> rowIter = sheet.rowIterator();
        int rowIdx = 0;

        if (!rowIter.hasNext()) {
            return db;
        }

        Row row = rowIter.next();

        if (settings.hasCSVHeader()) {
            // First row is header

            boolean empty = true;

            while (empty) {
                for (Cell cell : row) {
                    DatatoolHeader header = new DatatoolHeader(db, cell.toString());
                    db.addColumn(header);

                    empty = false;
                }

                if (empty) {
                    if (!rowIter.hasNext()) {
                        return db;
                    }

                    row = rowIter.next();
                }
            }
        } else {
            // First row of data

            int cellIdx = 0;

            for (Cell cell : row) {
                DatatoolHeader header = new DatatoolHeader(db,
                        DatatoolTk.getLabelWithValue("default.field", (cellIdx + 1)));
                db.addColumn(header);

                db.addCell(rowIdx, cellIdx, getCellValue(cell));

                cellIdx++;
            }

            if (cellIdx > 0) {
                rowIdx++;
            }
        }

        while (rowIter.hasNext()) {
            row = rowIter.next();

            int cellIdx = 0;

            for (Cell cell : row) {
                db.addCell(rowIdx, cellIdx, getCellValue(cell));

                cellIdx++;
            }

            if (cellIdx > 0) {
                rowIdx++;
            }
        }
    } catch (Exception e) {
        throw new DatatoolImportException(DatatoolTk.getLabelWithValue("error.import.failed", file.toString()),
                e);
    }

    return db;
}

From source file:com.efficio.fieldbook.service.FileServiceImpl.java

License:Open Source License

@Override
public Workbook retrieveWorkbook(String currentFilename) throws IOException {
    InputStream is = new FileInputStream(getFilePath(currentFilename));

    try {/*from  w  w  w  . j  a  v a2 s  . c  om*/
        return WorkbookFactory.create(is);
    } catch (InvalidFormatException e) {
        LOG.error(e.getMessage(), e);
        return null;
    } finally {
        IOUtils.closeQuietly(is);
    }
}

From source file:com.efficio.fieldbook.web.nursery.controller.AddOrRemoveTraitsControllerTest.java

License:Open Source License

/**
 * Sets the up.// w  ww.j  a  v a  2 s  .  co m
 */
@Before
public void setUp() {
    try {
        // InputStream inp = new FileInputStream("");

        InputStream inp = getClass().getClassLoader()
                .getResourceAsStream("GermplasmImportTemplate-Advanced-rev4.xls");

        workbookAdvance = (org.apache.poi.ss.usermodel.Workbook) WorkbookFactory.create(inp);

    } catch (Exception e) {
        LOG.error(e.getMessage(), e);
    }
}

From source file:com.efficio.fieldbook.web.nursery.controller.ImportGermplasmListControllerTest.java

License:Open Source License

/**
 * Sets the up.//  ww w  .j  av a  2  s.com
 */
@Before
public void setUp() {

    try {
        // InputStream inp = new FileInputStream("");

        InputStream inp = getClass().getClassLoader()
                .getResourceAsStream("GermplasmImportTemplate-Basic-rev4b-with_data.xls");

        workbookBasic = WorkbookFactory.create(inp);

        inp = getClass().getClassLoader().getResourceAsStream("GermplasmImportTemplate-Advanced-rev4.xls");
        workbookAdvance = WorkbookFactory.create(inp);

        inp = getClass().getClassLoader()
                .getResourceAsStream("GermplasmImportTemplate-Basic-rev4b-with_data.xlsx");

        workbookBasicXlsx = WorkbookFactory.create(inp);

        inp = getClass().getClassLoader().getResourceAsStream("GermplasmImportTemplate-Advanced-rev4.xlsx");
        workbookAdvanceXlsx = WorkbookFactory.create(inp);

        inp = getClass().getClassLoader().getResourceAsStream("Population114_Pheno_FB_1.xls");
        workbookInvalid = WorkbookFactory.create(inp);

    } catch (Exception e) {
        LOG.error(e.getMessage(), e);
    }
}

From source file:com.eleven0eight.xls2json.App.java

License:Open Source License

public String convertXlsToJson(FileInputStream fis) throws Exception {

    Workbook workbook = WorkbookFactory.create(fis);
    Sheet sheet = workbook.getSheetAt(0);
    JSONObject json = new JSONObject();
    JSONArray items = new JSONArray();
    ArrayList cols = new ArrayList();

    for (int i = 0; i <= sheet.getLastRowNum(); i++) {
        Row row = sheet.getRow(i);//from  w  w  w.j  a v  a 2s .co  m
        JSONObject item = new JSONObject();

        for (short colIndex = row.getFirstCellNum(); colIndex <= row.getLastCellNum(); colIndex++) {
            Cell cell = row.getCell(colIndex);
            if (cell == null) {
                continue;
            }
            if (i == 0) { // header
                cols.add(colIndex, cell.getStringCellValue());
            } else {
                item.put((String) cols.get(colIndex), cell.getStringCellValue());
            }
        }
        if (item.length() > 0) {
            items.put(item);
        }
    }
    json.put("items", items);
    return json.toString();

}