Example usage for org.apache.poi.ss.usermodel Workbook getSheetAt

List of usage examples for org.apache.poi.ss.usermodel Workbook getSheetAt

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. getSheetAt

Introduction

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

Prototype

Sheet getSheetAt(int index);

Source Link

Document

Get the Sheet object at the given index.

Usage

From source file:com.dataart.spreadsheetanalytics.engine.DataSetConverters.java

License:Apache License

/**
 * Converts a {@link Workbook} to new {@link IDataSet}.
 * Ignores empty rows./*  ww w  .  ja v a 2s .c om*/
 * 
 * @throws {@link CalculationEngineException} if {@link Workbook} contains formulas or Cell references.
 */
static IDataSet toDataSet(final Workbook workbook) {
    Sheet sheet = workbook.getSheetAt(0); //TODO: this works only for single sheet documents
    DataSet dataSet = new DataSet(sheet.getSheetName());

    for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
        IDsRow dsRow = dataSet.addRow();
        Row row = sheet.getRow(i);
        for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
            Cell wbCell = row.getCell(j);
            if (wbCell != null && wbCell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                throw new CalculationEngineException("DataSet should not contain formulas");
            }
            IDsCell cell = dsRow.addCell();
            cell.setValue(ConverterUtils.resolveCellValue(wbCell));
        }
    }
    return dataSet;
}

From source file:com.dataart.spreadsheetanalytics.engine.DependencyExtractors.java

License:Apache License

/**
 * Extracts {@link IDataModel} from {@link Workbook} at given {@link ICellAddress}.
 * Useful for formula. If given {@link ICellAddress} contains formula it can be parsed.
 * Based on this parsed information a new {@link IDataModel} might be created.
 *///  w w  w  . j ava  2 s. c  o m
static IDataModel toDataModel(final Workbook book, final ICellAddress address) {
    if (book == null || address == null) {
        return null;
    }
    if (address instanceof A1RangeAddress) {
        throw new CalculationEngineException(
                "A1RangeAddress is not supported, only one cell can be converted to DataModel.");
    }

    Sheet s = book.getSheetAt(0); /* TODO: only one sheet is supported */
    Row r = s.getRow(address.a1Address().row());
    if (r == null) {
        return null;
    }
    Cell c = r.getCell(address.a1Address().column());
    if (c == null || CELL_TYPE_FORMULA != c.getCellType()) {
        return null;
    }

    return createDataModelFromCell(s, create((XSSFWorkbook) book),
            fromRowColumn(c.getRowIndex(), c.getColumnIndex()));
}

From source file:com.dataart.spreadsheetanalytics.engine.DependencyExtractors.java

License:Apache License

/**
 * Extracts {@link IDataModel} from {@link Workbook} for given function name.
 * Useful for formula with particular functions. Does scan IDataModel for exact formula use and create new 
 * {@link IDataModel} for every formula found.
 *///from  w  ww  .  ja  v  a  2  s  .  com
static List<IDataModel> toDataModels(final Workbook book, final String function) {
    if (book == null || function == null) {
        return emptyList();
    }
    List<IDataModel> list = new LinkedList<>();

    final FormulaParsingWorkbook parsingBook = create((XSSFWorkbook) book);

    Sheet s = book.getSheetAt(0); /* TODO: only one sheet is supported */
    for (Row r : s) {
        for (Cell c : r) {
            if (c == null || CELL_TYPE_FORMULA != c.getCellType()) {
                continue;
            }

            try {
                if (ConverterUtils.isFunctionInFormula(c.getCellFormula(), function)) {
                    list.add(createDataModelFromCell(s, parsingBook,
                            fromRowColumn(c.getRowIndex(), c.getColumnIndex())));
                }
            } catch (FormulaParseException e) {
                log.warn("Warning while parsing excel formula. Probably this is OK.", e);
            }
        }
    }

    return list;
}

From source file:com.dataart.spreadsheetanalytics.engine.DependencyExtractors.java

License:Apache License

/**
 * Does the same logic as {@link #toDataModels(Workbook, String)}, but for each new {@link IDataModel} created
 * also created an instance of given {@link FunctionMeta}.
 *///from w ww.  j a  v  a 2  s.c  om
static <T extends FunctionMeta> Map<T, IDataModel> toMetaFunctions(Workbook book, Class<T> metaClass) {
    Map<T, IDataModel> map = new HashMap<>();

    book.addToolPack(Functions.getUdfFinder());
    final FormulaParsingWorkbook parsingBook = create((XSSFWorkbook) book);

    Sheet s = book.getSheetAt(0); /* TODO: only one sheet is supported */
    for (Row r : s) {
        for (Cell c : r) {
            if (c == null || CELL_TYPE_FORMULA != c.getCellType()) {
                continue;
            }

            try {
                String formula = c.getCellFormula();
                String keyword = metaClass.getAnnotation(FunctionMeta.MetaFunctionKeyword.class).value();

                if (!formula.startsWith(keyword)) {
                    continue;
                }

                IDataModel dataModel = createDataModelFromCell(s, parsingBook,
                        fromRowColumn(c.getRowIndex(), c.getColumnIndex()));
                T meta = createAttributeFunctionMeta(metaClass, formula, dataModel);

                map.put(meta, dataModel);
            } catch (Exception e) {
                log.debug("Warning while parsing custom excel formula. It is OK.", e);
            }
        }
    }

    return map;
}

From source file:com.dataart.spreadsheetanalytics.engine.PoiWorkbookConverters.java

License:Apache License

private PoiProxySheet makeSheet(Workbook wb, FormulaParsingWorkbook ewb) {
    Sheet wbSheet = wb.getSheetAt(0);
    PoiProxySheet sheet = new PoiProxySheet(wbSheet.getSheetName());

    for (int r = 0; r <= wbSheet.getLastRowNum(); r++) {
        Row wbSheetRow = wbSheet.getRow(r);
        if (wbSheetRow == null) {
            continue;
        }/*from   w  ww.  j  a va  2s .  c  o  m*/

        for (int c = 0; c <= wbSheetRow.getLastCellNum(); c++) {
            Cell wbSheetRowCell = wbSheetRow.getCell(c);
            if (wbSheetRowCell == null) {
                continue;
            }

            final Ptg[] tokens = CELL_TYPE_FORMULA == wbSheetRowCell.getCellType()
                    ? FormulaParser.parse(wbSheetRowCell.getCellFormula(), ewb, FormulaType.CELL, 0)
                    : null;
            sheet.setCell(PoiProxyCell.makeCell(sheet, wbSheetRowCell, tokens));
        }
    }

    return sheet;
}

From source file:com.dataart.spreadsheetanalytics.functions.poi.data.FuncexecFunction.java

License:Apache License

@Override
public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {

    log.debug("In evaluate() of FUNCEXEC function. Args = {}", Arrays.toString(args));

    if (!(args[0] instanceof StringEval) && !(args[0] instanceof RefEval)) {
        log.warn(/*from  www. j  a  v a  2  s.c om*/
                "The first argument of FUNCEXEC function must be a string (or a reference to a cell) - name of DEFINE function.");
        return ErrorEval.VALUE_INVALID;
    }

    String defineFunctionName;
    try {
        defineFunctionName = (String) coerceValueTo(
                getSingleValue(args[0], ec.getRowIndex(), ec.getColumnIndex()));
    } catch (EvaluationException e) {
        log.error(String.format("Cannot get the value of DEFINE functuion name: %s", args[0]), e);
        return ErrorEval.VALUE_INVALID;
    }
    defineFunctionName = defineFunctionName.toUpperCase(Locale.getDefault());

    MetaFunctionAccessor defines = (MetaFunctionAccessor) ec.getCustomEvaluationContext()
            .get(MetaFunctionAccessor.class);
    if (defines == null) {
        defines = this.external.getMetaFunctionAccessor();
    }

    if (defines.get(defineFunctionName) == null) {
        log.warn("No DEFINE function with name {} is found.", defineFunctionName);
        return ErrorEval.NAME_INVALID;
    }

    List<ValueEval> inputValues = new LinkedList<>();
    List<ValueEval> inArgs = new ArrayList(Arrays.asList(args));
    inArgs.remove(0); //remove define function name

    try {
        for (ValueEval v : ICustomFunction.prepareQueryArgs(inArgs)) {
            inputValues.add(getSingleValue(v, ec.getRowIndex(), ec.getColumnIndex()));
        }
    } catch (EvaluationException e) {
        log.error("Cannot resolve input values for FUNCEXEC function", e);
        return ErrorEval.VALUE_INVALID;
    }

    final DefineFunctionMeta meta = (DefineFunctionMeta) defines.get(defineFunctionName);
    log.info("Found DEFINE function to invoke. Name = {}.", defineFunctionName);

    if (meta.getInputs().size() != inputValues.size()) {
        log.warn("Wrong number of input arguments for FUNCEXEC+DEFINE. Expected: {}, Actual: {}.",
                meta.getInputs().size(), args.length - 1);
        return ErrorEval.VALUE_INVALID;
    }

    List<IA1Address> inputAddresses = meta.getInputs();
    log.debug("Input Addresses for DEFINE: {}, Input Values for DEFINE: {}.", inputAddresses, inputValues);

    if (inputAddresses.size() != inputValues.size()) {
        log.warn("Wrong number of input arguments for {} function.", defineFunctionName);
        return ErrorEval.VALUE_INVALID;
    }

    DataModelAccessor dataModels = (DataModelAccessor) ec.getCustomEvaluationContext()
            .get(DataModelAccessor.class);
    if (dataModels == null) {
        dataModels = this.external.getDataModelAccessor();
    }

    IDataModel dmWithDefine = dataModels.get(meta.getDataModelId());

    Workbook book = toWorkbook(dmWithDefine);
    EvaluationWorkbook defineBook = toEvaluationWorkbook(book);

    Sheet s = book.getSheetAt(0); //TODO one sheet support
    for (int i = 0; i < inputAddresses.size(); i++) {

        Row defineRow = s.getRow(inputAddresses.get(i).row());
        if (defineRow == null) {
            defineRow = s.createRow(inputAddresses.get(i).row());
        }
        Cell defineCell = defineRow.getCell(inputAddresses.get(i).column());
        if (defineCell == null) {
            defineCell = defineRow.createCell(inputAddresses.get(i).column());
        }

        populateCellValue(defineCell, inputValues.get(i));
        updateCell(defineBook, defineCell);
    }

    WorkbookEvaluator defineEvaluator = new WorkbookEvaluator(defineBook,
            IStabilityClassifier.TOTALLY_IMMUTABLE, null);
    List<ValueEval> outputValues = meta.getOutputs().stream().map(
            a -> defineEvaluator.evaluate(getEvaluationCell(defineBook, a), ec.getCustomEvaluationContext()))
            .collect(Collectors.<ValueEval>toList());

    log.debug("Output Values of DEFINE execution: {}.", outputValues);

    return outputValues.size() == 1 ? outputValues.get(0) : toArrayEval(outputValues);
}

From source file:com.delpac.bean.CargarSellosBean.java

public List<Sellos> importData(Workbook workbook, int tabNumber) throws IOException {
    List<Sellos> lista = new ArrayList<>();
    String[][] data;//w  ww  . j  a  v a 2  s .co m
    Sheet sheet = workbook.getSheetAt(tabNumber);
    data = new String[sheet.getLastRowNum() + 1][];
    Row[] row = new Row[sheet.getLastRowNum() + 1];
    Cell[][] cell = new Cell[row.length][];

    for (int i = 1; i < row.length; i++) {
        row[i] = sheet.getRow(i);
        cell[i] = new Cell[row[i].getLastCellNum()];
        data[i] = new String[row[i].getLastCellNum()];
        Sellos sellos = new Sellos();
        for (int j = 0; j < cell[i].length; j++) {
            cell[i][j] = row[i].getCell(j);
            if (cell[i][j] != null) {
                switch (j) {
                case 0:
                    sellos.setInv_seguridad(getDataFromCell(cell, i, j));
                    break;
                case 1:
                    sellos.setInv_sello(getDataFromCell(cell, i, j));
                    break;
                }
            } else {
                switch (j) {
                case 0:
                    sellos.setInv_seguridad("");
                    break;
                case 1:
                    sellos.setInv_sello("");
                    break;
                }
            }
        }
        if (!"".equals(sellos.getInv_sello())) {
            lista.add(sellos);
        }
    }

    return lista;
}

From source file:com.delpac.bean.PreDescargaBean.java

public List<PreDescarga> importData(Workbook workbook, int tabNumber) throws IOException {
    List<PreDescarga> lista = new ArrayList<>();
    String[][] data;/*  w w  w  .ja va2s .com*/
    DataFormatter df = new DataFormatter();
    Sheet sheet = workbook.getSheetAt(tabNumber);
    data = new String[sheet.getLastRowNum() + 1][];
    Row[] row = new Row[sheet.getLastRowNum() + 1];
    Cell[][] cell = new Cell[row.length][];

    for (int i = 1; i < row.length; i++) {
        row[i] = sheet.getRow(i);
        cell[i] = new Cell[row[i].getLastCellNum()];
        data[i] = new String[row[i].getLastCellNum()];
        PreDescarga preDescarga = new PreDescarga();
        for (int j = 0; j < cell[i].length; j++) {
            cell[i][j] = row[i].getCell(j);
            if (cell[i][j] != null) {
                switch (j) {
                case 0:
                    preDescarga.setBl(getDataFromCell(cell, i, j));
                    break;
                case 1:
                    preDescarga.setConsignatario(getDataFromCell(cell, i, j));
                    break;
                case 2:
                    preDescarga.setPto_origen(getDataFromCell(cell, i, j));
                    break;
                case 3:
                    preDescarga.setPto_destino(getDataFromCell(cell, i, j));
                    break;
                case 4:
                    preDescarga.setFec_embarque(getDataFromCell(cell, i, j));
                    break;
                case 5:
                    preDescarga.setPeso(Double.parseDouble(getDataFromCell(cell, i, j)));
                    break;
                case 6:
                    preDescarga.setBulto(Double.parseDouble(getDataFromCell(cell, i, j)));
                    break;
                case 7:
                    preDescarga.setEmbalaje(getDataFromCell(cell, i, j));
                    break;
                case 8:
                    preDescarga.setCarga(getDataFromCell(cell, i, j));
                    break;
                case 9:
                    preDescarga.setCon_codigo(getDataFromCell(cell, i, j));
                    break;
                case 10:
                    preDescarga.setTemperatura(getDataFromCell(cell, i, j));
                    break;
                case 11:
                    preDescarga.setVentilacion(getDataFromCell(cell, i, j));
                    break;
                case 12:
                    preDescarga.setSello(getDataFromCell(cell, i, j));
                    break;
                case 13:
                    preDescarga.setSown(getDataFromCell(cell, i, j));
                    break;
                case 14:
                    preDescarga.setClassimo(getDataFromCell(cell, i, j));
                    break;
                case 15:
                    preDescarga.setUnnro(getDataFromCell(cell, i, j));
                    break;
                case 16:
                    preDescarga.setTip_cont(getDataFromCell(cell, i, j));
                    break;
                case 17:
                    preDescarga.setCondicion(getDataFromCell(cell, i, j));
                    break;
                case 18:
                    preDescarga.setAlm_codigo(getDataFromCell(cell, i, j));
                    break;
                }
            } else {
                switch (j) {
                case 1:
                    preDescarga.setBl("");
                    break;
                case 2:
                    preDescarga.setConsignatario("");
                    break;
                case 3:
                    preDescarga.setPto_origen("");
                    break;
                case 4:
                    preDescarga.setPto_destino("");
                    break;
                case 5:
                    preDescarga.setFec_embarque("");
                    break;
                case 6:
                    preDescarga.setPeso(0);
                    break;
                case 7:
                    preDescarga.setBulto(0);
                    break;
                case 8:
                    preDescarga.setEmbalaje("");
                    break;
                case 9:
                    preDescarga.setCarga("");
                    break;
                case 10:
                    preDescarga.setCon_codigo("");
                    break;
                case 11:
                    preDescarga.setTemperatura("");
                    break;
                case 12:
                    preDescarga.setVentilacion("");
                    break;
                case 13:
                    preDescarga.setSello("");
                    break;
                case 14:
                    preDescarga.setSown("");
                    break;
                case 15:
                    preDescarga.setClassimo("");
                    break;
                case 16:
                    preDescarga.setUnnro("");
                    break;
                case 17:
                    preDescarga.setTip_cont("");
                    break;
                case 18:
                    preDescarga.setCondicion("");
                    break;
                case 19:
                    preDescarga.setAlm_codigo("");
                    break;
                }
            }
        }
        lista.add(preDescarga);
    }
    return lista;
}

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  www  .j a  v  a  2  s. co  m

        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  ww .ja v  a 2s  .co m*/
        model.setViewName("/admin/group-registration");
    }
    return model;
}