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.jogo.dao.RepositorioDao.java

@Override
public List importarPerguntas(int index) {

    //CRIO UM ARRAY DE PERGUNTAS PARA ARMAZENAR AS PERGUNTAS BUSCAS DO EXCEL.
    List<Perguntas> perguntas = new ArrayList<>();

    try {//from ww w. ja v  a 2s .  c o  m

        //CAPTURANDO O EXCEL PARA MEU WB.    
        wb = WorkbookFactory.create(new FileInputStream(patch));

        //CAPTURO A FOLHA DO EXCEL PASSANDO O INDEX
        Sheet folha = wb.getSheetAt(index);

        //CRIO UM ITERATOR PARA INTERAGIR COM AS LINHAS.
        Iterator filaIterator = folha.rowIterator();

        //ENQUANTO HOUVER INTERAO PEGA UMA LINHA.
        while (filaIterator.hasNext()) {

            //CAPTURO A LINHA DO EXCEL
            Row linha = (Row) filaIterator.next();
            //CRIO UM ITERATOR PARA PEGAR AS COLUNAS
            Iterator colunaIterator = linha.cellIterator();

            //AQUI DIGO QUE MINHAS COLUNAS NO PODE PASSAR DE 6, COMO TA A MINHA ESTRUTURA PARA O EXCEL
            //1 - PERGUNTA, 2- ALTERNATIVA, 3- ALTERNATIVA, 4- ATLTERNATIVA, 5- ALTERNATIVA, 6- RESPOSTA
            //CHEGOU MAIOR QUE 6 SAIU DO LOOP DE COLUNAS.
            if (linha.getLastCellNum() > 6) {
                break;
            }

            //CRIOU A CLASSE DE PERGUNTAS E ADD DENTRO DO MEU ARRAY
            Perguntas per = new Perguntas();
            perguntas.add(per);

            //INTERAGIR COM AS COLUNAS, PEGAR AS COLUNAS DO EXCEL
            while (colunaIterator.hasNext()) {

                //TENDO A LINHA E COLUNA JA POSSO TER UMA CELULA.
                Cell celula = (Cell) colunaIterator.next();

                //APOS CAPTURAR O VALOR NA CELULA, SETO PARA MINHA CLASSE PERGUNTAS QUE CRIEI LOGO ACIMA.
                if (celula != null) {
                    //CAPTURAR O TIPO DA CELULA, NO CASO TODAS AS PERGUNTAS E ALTERNATIVAS SO STRINGS.
                    //OBS: OLHE QUE NESSE CASO S POSSO TRAZER STRING'S CASO CONTRARIO NO IR? FUNCIONAR.:/
                    switch (celula.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        switch (celula.getColumnIndex()) {
                        case 0:
                            per.setPergunta(celula.getStringCellValue());
                            break;
                        case 1:
                            per.setAlt1(celula.getStringCellValue());
                            break;
                        case 2:
                            per.setAlt2(celula.getStringCellValue());
                            break;
                        case 3:
                            per.setAlt3(celula.getStringCellValue());
                            break;
                        case 4:
                            per.setAlt4(celula.getStringCellValue());
                            break;
                        case 5:
                            per.setResposta(celula.getStringCellValue());
                            break;
                        default:
                            break;
                        }
                    }

                }

            }
        }
    } catch (IOException | InvalidFormatException | EncryptedDocumentException e) {
    }
    return perguntas;
}

From source file:com.medicaid.mmis.util.CodeMappingLoader.java

License:Apache License

/**
 * Reads the code mapping xls and inserts any unmapped row to the legacy mapping table.
 * @throws InvalidFormatException if the input file is not recognized 
 *//* ww  w  . j  a va 2  s  .c  o  m*/
@SuppressWarnings("unchecked")
public static void main(String[] args) throws IOException, PortalServiceException, InvalidFormatException {
    PropertyConfigurator.configure("log4j.properties");
    logger = Logger.getLogger(CodeMappingLoader.class);
    EntityManagerFactory emf = Persistence.createEntityManagerFactory("cms-data-load");
    EntityManager em = emf.createEntityManager();

    Workbook workbook = WorkbookFactory.create(new File("mapping/CodeMapping.xlsx"));
    SequenceGeneratorBean sequence = new SequenceGeneratorBean();
    sequence.setEm(em);

    try {
        em.getTransaction().begin();
        List<LegacySystemMapping> rs = em.createQuery("Select l from LegacySystemMapping l").getResultList();
        for (LegacySystemMapping mapping : rs) {
            em.remove(mapping);
        }
        importSheet(em, sequence, workbook, "ENROLLMENT_STATUS");
        importSheet(em, sequence, workbook, "RISK_LEVEL");
        importSheet(em, sequence, workbook, "SPECIALTY_CODE");
        importSheet(em, sequence, workbook, "LICENSE_TYPE");
        importSheet(em, sequence, workbook, "ISSUING_BOARD");
        importSheet(em, sequence, workbook, "PROVIDER_TYPE");
        importSheet(em, sequence, workbook, "COUNTY_CODE");
        importSheet(em, sequence, workbook, "BEN_OWNER_TYPE");
        importSheet(em, sequence, workbook, "LICENSE_STATUS");
        importSheet(em, sequence, workbook, "COS");
        em.getTransaction().commit();
    } catch (Throwable t) {
        em.getTransaction().rollback();
        logger.error("Could not complete import", t);
        throw new PortalServiceException("Error during import", t);
    }
}

From source file:com.mimp.controllers.reporte.java

@RequestMapping("/Reportes/OrganismosAcreditados")
    public void ReporteOrganismo(ModelMap map, HttpSession session, HttpServletResponse response) {
        Personal usuario = (Personal) session.getAttribute("usuario");
        Workbook wb = new XSSFWorkbook();
        try {/*  w ww  . j a va  2  s  . com*/
            //Se llama a la plantilla localizada en la ruta

            //            InputStream inp = new FileInputStream("C:\\Plantillas\\OrgAcred.xlsx");
            InputStream inp = new FileInputStream("/opt/Plantillas/OrgAcred.xlsx");

            wb = WorkbookFactory.create(inp);
            Sheet sheet = wb.getSheetAt(0);

            //Aqu va el query que consigue los datos de la tabla
            //ArrayList<Organismo> listaorg = ServicioPersonal.ListaOrganismos();
            ArrayList<Organismo> listaorg = ServicioReporte.ReporteOrganismo2();

            int i = 1;
            for (Organismo org : listaorg) {
                Row row = sheet.createRow(i);

                Cell cell = row.createCell(0);
                cell.setCellValue(i);
                cell = row.createCell(1);
                cell.setCellValue(org.getEntidad().getNombre());
                cell = row.createCell(2);
                cell.setCellValue(org.getCompetencia());
                cell = row.createCell(3);
                cell.setCellValue(org.getEntidad().getResolAuto());
                cell = row.createCell(4);
                String fechaVenc = "";
                try {
                    fechaVenc = format.dateToString(org.getEntidad().getFechaVenc());
                } catch (Exception ex) {
                }
                cell.setCellValue(fechaVenc);
                cell = row.createCell(5);
                for (Iterator iter = org.getRepresentantes().iterator(); iter.hasNext();) {
                    Representante rep = (Representante) iter.next();
                    cell.setCellValue(rep.getNombre() + " " + rep.getApellidoP());
                }
                cell = row.createCell(6);
                cell.setCellValue(org.getEntidad().getObs());

                i++;
            }
        } catch (Exception e) {
            //e.printStackTrace();
        }

        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Content-Disposition",
                    "attachment; filename=Registro del nmero Organismos Acreditados.xlsx");
            OutputStream fileOut = response.getOutputStream();
            wb.write(fileOut);
            fileOut.flush();
            fileOut.close();
        } catch (Exception ex) {
            //ex.printStackTrace();
        }

        String mensaje_log = "El usuario: " + usuario.getNombre() + " " + usuario.getApellidoP() + " con ID: "
                + usuario.getIdpersonal() + ". Descarg el Reporte 'Organismos Acreditados' ";

        String Tipo_registro = "Personal";

        try {
            String Numero_registro = String.valueOf(usuario.getIdpersonal());

            ServicioPersonal.InsertLog(usuario, Tipo_registro, Numero_registro, mensaje_log);
        } catch (Exception ex) {
        }
    }

From source file:com.miraisolutions.xlconnect.Workbook.java

License:Open Source License

private Workbook(InputStream in) throws IOException, InvalidFormatException {
    this.workbook = WorkbookFactory.create(in);
    this.excelFile = null;
    initDefaultDataFormats();// w  w w  .ja  v a  2 s  . c o m
    initDefaultStyles();
}

From source file:com.murilo.excel.ExcelHandler.java

private Workbook newWorkbook(String filename) throws IOException, InvalidFormatException {

    Workbook wb = null;//from   w ww  .j a  va 2s. c o m

    if (filename.toLowerCase().endsWith("xls")) {
        wb = (HSSFWorkbook) WorkbookFactory.create(new File(filename));
    }
    if (filename.toLowerCase().endsWith("xlsx")) {
        wb = (XSSFWorkbook) WorkbookFactory.create(new File(filename));
    }
    return wb;
}

From source file:com.opendoorlogistics.core.tables.io.PoiIO.java

License:Open Source License

public static ODLDatastoreAlterable<ODLTableAlterable> importExcel(InputStream stream, ExecutionReport report) {
    //tmpFileBugFix();

    ODLDatastoreAlterable<ODLTableAlterable> ds = ODLFactory.createAlterable();

    Workbook wb = null;//from  ww w .jav  a2s . c  o  m
    try {
        wb = WorkbookFactory.create(stream);

        String author = getAuthor(wb);
        if (author != null && Strings.equalsStd(author, AppConstants.ORG_NAME)) {
            ds.setFlags(ds.getFlags() | ODLDatastore.FLAG_FILE_CREATED_BY_ODL);
        }

    } catch (Throwable e) {
        throw new RuntimeException(e);
    }

    // look for the schema; remove it from the workbook to simplify the later workbook updating code
    // (the schema gets held by the datastore structure anyway)
    SchemaSheetInformation info = null;
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        Sheet sheet = wb.getSheetAt(i);
        if (Strings.equalsStd(sheet.getSheetName(), SCHEMA_SHEET_NAME)) {
            info = importSchemaTables(sheet, report);
            wb.removeSheetAt(i);
            break;
        }
    }

    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        Sheet sheet = wb.getSheetAt(i);
        ODLTableAlterable table = ds.createTable(sheet.getSheetName(), -1);
        importSheet(table, sheet, info != null ? info.schema : null, false);
    }

    return ds;
}

From source file:com.openitech.db.model.ExcelDataSource.java

License:Apache License

@Override
public boolean loadData(boolean reload, int oldRow) {
    boolean result = false;

    if (isDataLoaded && !reload) {
        return false;
    }/* ww  w  . java2  s. c  o  m*/
    if (sourceFile != null) {
        try {
            Workbook workBook = WorkbookFactory.create(new FileInputStream(sourceFile));
            //        HSSFWorkbook workBook = new HSSFWorkbook(new FileInputStream(sourceFile));
            Sheet sheet = workBook.getSheetAt(0);
            DataFormatter dataFormatter = new DataFormatter(Locale.GERMANY);
            FormulaEvaluator formulaEvaluator = workBook.getCreationHelper().createFormulaEvaluator();

            int lastRowNum = sheet.getLastRowNum();

            boolean isFirstLineHeader = true;

            //count = sheet. - (isFirstLineHeader ? 1 : 0);
            int tempCount = 0;
            for (int j = 0; j <= lastRowNum; j++) {
                //zane se z 0
                Row row = row = sheet.getRow(j);
                if (row == null) {
                    continue;
                }

                // display row number in the console.
                System.out.println("Row No.: " + row.getRowNum());
                if (isFirstLineHeader && row.getRowNum() == 0) {
                    populateHeaders(row);
                    continue;
                }
                tempCount++;

                Map<String, DataColumn> values;
                if (rowValues.containsKey(row.getRowNum())) {
                    values = rowValues.get(row.getRowNum());
                } else {
                    values = new HashMap<String, DataColumn>();
                    rowValues.put(row.getRowNum(), values);
                }

                // once get a row its time to iterate through cells.
                int lastCellNum = row.getLastCellNum();
                for (int i = 0; i <= lastCellNum; i++) {
                    DataColumn dataColumn = new DataColumn();
                    Cell cell = row.getCell(i);
                    if (cell == null) {
                        continue;
                    }
                    System.out.println("Cell No.: " + cell.getColumnIndex());
                    System.out.println("Value: " + dataFormatter.formatCellValue(cell));
                    if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                        dataColumn = new DataColumn(dataFormatter.formatCellValue(cell, formulaEvaluator));
                    } else {
                        dataColumn = new DataColumn(dataFormatter.formatCellValue(cell));
                    }

                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC: {
                        // cell type numeric.
                        System.out.println("Numeric value: " + cell.getNumericCellValue());
                        dataColumn = new DataColumn(dataFormatter.formatCellValue(cell));
                        break;
                    }
                    case Cell.CELL_TYPE_STRING:
                        // cell type string.
                        System.out.println("String value: " + cell.getStringCellValue());
                        dataColumn = new DataColumn(dataFormatter.formatCellValue(cell));
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        // cell type string.
                        System.out.println("String value: " + cell.getBooleanCellValue());
                        dataColumn.setValue(cell.getBooleanCellValue(), Boolean.class);
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        // cell type string.
                        System.out.println(
                                "Formula value: " + dataFormatter.formatCellValue(cell, formulaEvaluator));
                        dataColumn = new DataColumn(dataFormatter.formatCellValue(cell, formulaEvaluator));
                        break;
                    default:
                        dataColumn.setValue(cell.getStringCellValue(), String.class);
                        break;
                    }

                    values.put(getColumnName(cell.getColumnIndex()).toUpperCase(), dataColumn);

                }
            }

            count = tempCount;

            isDataLoaded = true;
            //se postavim na staro vrstico ali 1
            if (oldRow > 0) {
                absolute(oldRow);
            } else {
                first();
            }

            result = true;
        } catch (Exception ex) {
            Logger.getLogger(ExcelDataSource.class.getName()).log(Level.SEVERE, null, ex);
            result = false;
        }
    }

    return result;
}

From source file:com.perceptive.epm.perkolcentral.bl.ExcelReportBL.java

public FileInputStream generateAllEmployeeReport(File file) throws ExceptionWrapper {
    try {/*w  w w.j ava 2 s  .co m*/
        FileInputStream inp = new FileInputStream(file);
        //InputStream inp = new FileInputStream("workbook.xlsx");

        Workbook wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheetAt(0);

        int iRowCounter = 1;
        for (EmployeeBO employeeBO : employeeBL.getAllEmployees().values()) {
            Row row = sheet.getRow(iRowCounter);
            if (row == null)
                row = sheet.createRow(iRowCounter);
            Cell cell = row.getCell(0);
            if (cell == null)
                cell = row.createCell(0);
            setCellBorder(wb, cell);
            cell.setCellValue(employeeBO.getEmployeeId());

            cell = row.getCell(1);
            if (cell == null)
                cell = row.createCell(1);
            setCellBorder(wb, cell);
            cell.setCellValue(employeeBO.getEmployeeUid());

            cell = row.getCell(2);
            if (cell == null)
                cell = row.createCell(2);
            setCellBorder(wb, cell);
            cell.setCellValue(employeeBO.getEmployeeName());

            cell = row.getCell(3);
            if (cell == null)
                cell = row.createCell(3);
            setCellBorder(wb, cell);
            cell.setCellValue(employeeBO.getEmail());

            cell = row.getCell(4);
            if (cell == null)
                cell = row.createCell(4);
            setCellBorder(wb, cell);
            cell.setCellValue(employeeBO.getManager());

            cell = row.getCell(5);
            if (cell == null)
                cell = row.createCell(5);
            setCellBorder(wb, cell);
            cell.setCellValue(employeeBO.getMobileNumber());

            cell = row.getCell(6);
            if (cell == null)
                cell = row.createCell(6);
            setCellBorder(wb, cell);
            cell.setCellValue(employeeBO.getExtensionNum());

            cell = row.getCell(7);
            if (cell == null)
                cell = row.createCell(7);
            setCellBorder(wb, cell);
            cell.setCellValue(employeeBO.getWorkspace());

            iRowCounter = iRowCounter + 1;
        }
        FileOutputStream fileOut = new FileOutputStream(file);
        wb.write(fileOut);
        fileOut.close();
        return new FileInputStream(file);
    } catch (Exception ex) {
        throw new ExceptionWrapper(ex);
    }

}

From source file:com.perceptive.epm.perkolcentral.bl.ExcelReportBL.java

public FileInputStream generateAllEmployeeTeamWiseReport(File file, Boolean isScrumTeam)
        throws ExceptionWrapper {
    try {/*from w w  w . ja v a2s .  c om*/
        FileInputStream inp = new FileInputStream(file);
        //InputStream inp = new FileInputStream("workbook.xlsx");

        Workbook wb = WorkbookFactory.create(inp);
        int iSheetCounter = 1;
        for (Integer groupID : employeeBL.getAllEmployeesKeyedByGroupId().keySet()) {
            GroupBO groupBO = groupsBL.getAllGroups().get(groupID);
            if (isScrumTeam && !groupBO.getRallyGroup())
                continue;
            if (!isScrumTeam && groupBO.getRallyGroup())
                continue;

            Sheet sheet = wb.cloneSheet(0);
            wb.setSheetName(wb.getSheetIndex(sheet), groupBO.getGroupName());
            //wb.setSheetName(iSheetCounter,groupBO.getGroupName());

            int iRowCounter = 1;
            for (EmployeeBO employeeBO : employeeBL.getAllEmployeesKeyedByGroupId().get(groupID)) {
                Row row = sheet.getRow(iRowCounter);
                if (row == null)
                    row = sheet.createRow(iRowCounter);
                Cell cell = row.getCell(0);
                if (cell == null)
                    cell = row.createCell(0);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getEmployeeId());

                cell = row.getCell(1);
                if (cell == null)
                    cell = row.createCell(1);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getEmployeeUid());

                cell = row.getCell(2);
                if (cell == null)
                    cell = row.createCell(2);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getEmployeeName());

                cell = row.getCell(3);
                if (cell == null)
                    cell = row.createCell(3);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getEmail());

                cell = row.getCell(4);
                if (cell == null)
                    cell = row.createCell(4);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getManager());

                cell = row.getCell(5);
                if (cell == null)
                    cell = row.createCell(5);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getMobileNumber());

                cell = row.getCell(6);
                if (cell == null)
                    cell = row.createCell(6);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getExtensionNum());

                cell = row.getCell(7);
                if (cell == null)
                    cell = row.createCell(7);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getWorkspace());

                if (isScrumTeam) {
                    cell = row.getCell(8);
                    if (cell == null)
                        cell = row.createCell(8);
                    setCellBorder(wb, cell);
                    cell.setCellValue(employeeBO.getSpecificRoleInScrumTeam());
                }

                iRowCounter = iRowCounter + 1;
            }
        }
        iSheetCounter = iSheetCounter + 1;
        wb.removeSheetAt(0);
        FileOutputStream fileOut = new FileOutputStream(file);
        wb.write(fileOut);
        fileOut.close();
        return new FileInputStream(file);
    } catch (Exception ex) {
        throw new ExceptionWrapper(ex);
    }

}

From source file:com.perceptive.epm.perkolcentral.bl.ExcelReportBL.java

public FileInputStream generateLicenseReport(File file) throws ExceptionWrapper {
    try {//from   w  w w.  j  av  a2s.  c om
        FileInputStream inp = new FileInputStream(file);
        //InputStream inp = new FileInputStream("workbook.xlsx");

        Workbook wb = WorkbookFactory.create(inp);
        HashMap<String, ArrayList<String>> licenseInfoKeyedByLicenseName = licensesBL.getLicenseRelatedInfo();
        //Create The Summary Sheet
        Sheet sheetSummary = wb.getSheetAt(1);
        int iSummaryRowCounter = 1;
        for (String licenseType : licenseInfoKeyedByLicenseName.keySet()) {

            Row row = sheetSummary.getRow(iSummaryRowCounter);
            if (row == null)
                row = sheetSummary.createRow(iSummaryRowCounter);
            Cell cell = row.getCell(0);
            if (cell == null)
                cell = row.createCell(0);
            setCellBorder(wb, cell);
            cell.setCellValue(licenseType);

            row = sheetSummary.getRow(iSummaryRowCounter);
            if (row == null)
                row = sheetSummary.createRow(iSummaryRowCounter);
            cell = row.getCell(1);
            if (cell == null)
                cell = row.createCell(1);
            setCellBorder(wb, cell);
            cell.setCellValue(Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(0)));
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);

            row = sheetSummary.getRow(iSummaryRowCounter);
            if (row == null)
                row = sheetSummary.createRow(iSummaryRowCounter);
            cell = row.getCell(2);
            if (cell == null)
                cell = row.createCell(2);
            setCellBorder(wb, cell);
            cell.setCellValue(Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(1)));
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);

            row = sheetSummary.getRow(iSummaryRowCounter);
            if (row == null)
                row = sheetSummary.createRow(iSummaryRowCounter);
            cell = row.getCell(3);
            if (cell == null)
                cell = row.createCell(3);
            setCellBorder(wb, cell);
            cell.setCellValue(Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(0))
                    - Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(1)));
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);

            iSummaryRowCounter = iSummaryRowCounter + 1;
        }
        int iSheetCounter = 1;

        for (String licenseType : licenseInfoKeyedByLicenseName.keySet()) {
            Sheet sheet = wb.cloneSheet(0);
            wb.setSheetName(wb.getSheetIndex(sheet), licenseType);
            CellReference cellReference = new CellReference("B1");
            Row row = sheet.getRow(cellReference.getRow());
            Cell cell = row.getCell(cellReference.getCol());
            if (cell == null)
                cell = row.createCell(cellReference.getCol());
            setCellBorder(wb, cell);
            cell.setCellValue(Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(0)));
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);

            cellReference = new CellReference("B2");
            row = sheet.getRow(cellReference.getRow());
            cell = row.getCell(cellReference.getCol());
            if (cell == null)
                cell = row.createCell(cellReference.getCol());
            setCellBorder(wb, cell);
            cell.setCellValue(Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(1)));
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);

            cellReference = new CellReference("B3");
            row = sheet.getRow(cellReference.getRow());
            cell = row.getCell(cellReference.getCol());
            if (cell == null)
                cell = row.createCell(cellReference.getCol());
            setCellBorder(wb, cell);
            cell.setCellValue(Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(0))
                    - Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(1)));
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);

            ArrayList<EmployeeBO> allEmployees = new ArrayList<EmployeeBO>(
                    employeeBL.getAllEmployees().values());
            final String selectedLicenseTypeName = licenseType;
            CollectionUtils.filter(allEmployees, new Predicate() {
                @Override
                public boolean evaluate(Object o) {
                    EmployeeBO emp = (EmployeeBO) o;
                    if (CollectionUtils.exists(emp.getLicenses(), new Predicate() {
                        @Override
                        public boolean evaluate(Object o) {
                            return ((LicenseBO) o).getLicenseTypeName()
                                    .equalsIgnoreCase(selectedLicenseTypeName); //To change body of implemented methods use File | Settings | File Templates.
                        }
                    }))
                        return true;
                    else
                        return false;
                }
            });

            int iRowCounter = 5;
            for (EmployeeBO employeeBO : allEmployees) {
                row = sheet.getRow(iRowCounter);
                if (row == null)
                    row = sheet.createRow(iRowCounter);
                cell = row.getCell(0);
                if (cell == null)
                    cell = row.createCell(0);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getEmployeeId());

                cell = row.getCell(1);
                if (cell == null)
                    cell = row.createCell(1);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getEmployeeUid());

                cell = row.getCell(2);
                if (cell == null)
                    cell = row.createCell(2);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getEmployeeName());

                cell = row.getCell(3);
                if (cell == null)
                    cell = row.createCell(3);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getEmail());

                cell = row.getCell(4);
                if (cell == null)
                    cell = row.createCell(4);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getManager());

                cell = row.getCell(5);
                if (cell == null)
                    cell = row.createCell(5);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getMobileNumber());

                cell = row.getCell(6);
                if (cell == null)
                    cell = row.createCell(6);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getExtensionNum());

                cell = row.getCell(7);
                if (cell == null)
                    cell = row.createCell(7);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getWorkspace());

                iRowCounter = iRowCounter + 1;
            }
        }
        iSheetCounter = iSheetCounter + 1;
        wb.removeSheetAt(0);
        FileOutputStream fileOut = new FileOutputStream(file);
        wb.write(fileOut);
        fileOut.close();
        return new FileInputStream(file);
    } catch (Exception ex) {
        throw new ExceptionWrapper(ex);
    }

}