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.ocs.dynamo.ui.composite.table.export.TableExportActionHandlerTest.java

License:Apache License

@Test
public void testExportWithCustomCellStyle() throws IOException {

    List<EntityModel<?>> models = new ArrayList<>();
    models.add(entityModelFactory.getModel(Person.class));

    handler = new TableExportActionHandler(ui, entityModelFactory, models, messageService, REPORT_TITLE,
            columnIds, true, new CustomCellStyleGenerator() {

                private CellStyle cellStyle;

                private CellStyle bdStyle;

                @Override/*  w  w  w  .  j a  v a2 s . c  om*/
                public CellStyle getCustomCellStyle(Workbook workbook, Object propId, Object value,
                        AttributeModel attributeModel) {
                    if (cellStyle == null) {
                        cellStyle = workbook.createCellStyle();
                        cellStyle.setAlignment(CellStyle.ALIGN_RIGHT);
                        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
                        cellStyle.setBorderTop(CellStyle.BORDER_THIN);
                        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
                        cellStyle.setBorderRight(CellStyle.BORDER_THIN);
                        Font font = workbook.createFont();
                        font.setColor(IndexedColors.BLUE.getIndex());
                        cellStyle.setFont(font);
                    }

                    if (bdStyle == null) {
                        DataFormat format = workbook.createDataFormat();
                        bdStyle = workbook.createCellStyle();
                        bdStyle.setAlignment(CellStyle.ALIGN_RIGHT);
                        bdStyle.setBorderBottom(CellStyle.BORDER_THIN);
                        bdStyle.setBorderTop(CellStyle.BORDER_THIN);
                        bdStyle.setBorderLeft(CellStyle.BORDER_THIN);
                        bdStyle.setBorderRight(CellStyle.BORDER_THIN);
                        bdStyle.setDataFormat(format.getFormat("#,##0.00"));

                        Font font = workbook.createFont();
                        font.setColor(IndexedColors.BLUE.getIndex());
                        bdStyle.setFont(font);
                    }

                    if ("name".equals(propId)) {
                        return cellStyle;
                    } else if ("percentage".equals(propId)) {
                        return bdStyle;
                    }
                    return null;
                }
            });

    handler.handleAction(handler.getActions(null, null)[0], getTable(), null);

    byte[] bytes = captureSave();
    Workbook wb = importer.createWorkbook(bytes);

    Assert.assertEquals("Bas, Bob", wb.getSheetAt(0).getRow(2).getCell(0).getStringCellValue());
    Font font = wb.getFontAt(wb.getSheetAt(0).getRow(2).getCell(0).getCellStyle().getFontIndex());
    Assert.assertEquals(IndexedColors.BLUE.getIndex(), font.getColor());

    Assert.assertEquals("Patrick", wb.getSheetAt(0).getRow(3).getCell(0).getStringCellValue());

    Assert.assertEquals(35, wb.getSheetAt(0).getRow(2).getCell(1).getNumericCellValue(), 0.001);
    Assert.assertEquals(44, wb.getSheetAt(0).getRow(3).getCell(1).getNumericCellValue(), 0.001);

    // totals must be summed up
    Assert.assertEquals(79, wb.getSheetAt(0).getRow(4).getCell(1).getNumericCellValue(), 0.001);

    // percentage
    Assert.assertEquals(0.12, wb.getSheetAt(0).getRow(2).getCell(3).getNumericCellValue(), 0.001);
    Assert.assertEquals(0.15, wb.getSheetAt(0).getRow(3).getCell(3).getNumericCellValue(), 0.001);

}

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;
    try {// w w w.j  a v  a2 s .  c  o m
        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.opendoorlogistics.studio.LoadedDatastore.java

License:Open Source License

private void updateWorkbookWithModifications(Workbook wb, ExecutionReport report) {
    // parse the original tables; these will be held in the datastore with the same index as the sheet
    int nbOriginal = originalLoadedDs.getTableCount();
    if (nbOriginal != wb.getNumberOfSheets()) {
        throw new RuntimeException();
    }//from w  w  w  .ja  va 2s  .c o m

    ArrayList<ODLTableReadOnly> oldOnesToReadd = new ArrayList<>();
    for (int i = nbOriginal - 1; i >= 0; i--) {
        ODLTableReadOnly originalTable = originalLoadedDs.getTableAt(i);
        ODLTableReadOnly newTable = ds.getTableByImmutableId(originalTable.getImmutableId());

        if (newTable == null) {
            // table was deleted
            wb.removeSheetAt(i);
        } else if (DatastoreComparer.isSame(originalTable, newTable, DatastoreComparer.CHECK_ALL) == false) {
            Sheet sheet = wb.getSheetAt(i);

            boolean sameStructure = DatastoreComparer.isSameStructure(originalTable, newTable,
                    DatastoreComparer.CHECK_ALL);
            if (sameStructure) {
                // re-write all values but skip the header row
                int nbOversized = 0;
                for (int iRow = 0; iRow < newTable.getRowCount(); iRow++) {
                    int iTargetRow = iRow + 1;
                    Row row = sheet.getRow(iTargetRow);
                    if (row == null) {
                        row = sheet.createRow(iTargetRow);
                    }

                    int nc = newTable.getColumnCount();
                    for (int col = 0; col < nc; col++) {
                        Cell cell = row.getCell(col);
                        if (cell != null && cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                            // don't set the value of formula cells...
                            continue;
                        }
                        if (cell == null) {
                            cell = row.createCell(col);
                        }

                        String sval = TableUtils.getValueAsString(newTable, iRow, col);
                        if (sval != null && sval.length() > PoiIO.MAX_CHAR_COUNT_IN_EXCEL_CELL) {
                            nbOversized++;
                        }
                        cell.setCellValue(sval);
                    }
                }

                // delete any rows after the last row (including 1 for the header)
                int lastOKRow = newTable.getRowCount();
                while (sheet.getLastRowNum() > lastOKRow) {
                    sheet.removeRow(sheet.getRow(sheet.getLastRowNum()));
                }

                if (nbOversized > 0 && report != null) {
                    report.log(PoiIO.getOversizedWarningMessage(nbOversized, newTable.getName()));
                    ;
                }

            } else {
                // delete and replace. replace after parsing all original tables as we can get table name conflicts
                wb.removeSheetAt(i);
                oldOnesToReadd.add(newTable);
            }

        }

    }

    // re-add any totally replaced tables
    for (ODLTableReadOnly table : oldOnesToReadd) {
        Sheet sheet = wb.createSheet(table.getName());
        if (sheet != null) {
            PoiIO.exportTable(sheet, table, report);
        }
    }

    // add new tables at the end
    for (int i = 0; i < ds.getTableCount(); i++) {
        ODLTableReadOnly newTable = ds.getTableAt(i);
        if (originalLoadedDs.getTableByImmutableId(newTable.getImmutableId()) == null) {
            // new table...
            Sheet sheet = wb.createSheet(newTable.getName());
            if (sheet != null) {
                PoiIO.exportTable(sheet, newTable, report);
            }
        }

    }
}

From source file:com.opengamma.analytics.financial.credit.cds.ISDATestGridManager.java

License:Open Source License

public ISDATestGrid loadTestGrid(final String category, final String fileName) throws Exception {

    InputStream is = null;/*from   ww  w .  jav a  2s  . com*/

    try {
        final String path = RESOURCE_DIR + File.separator + TEST_GRID_DIR + File.separator + category
                + File.separator + fileName;
        is = getClass().getClassLoader().getResourceAsStream(path);
        final Workbook wb = new HSSFWorkbook(is);
        final Sheet sheet = wb.getSheetAt(0);

        final ISDATestGrid testGrid = new ISDATestGrid();
        testGrid.process(sheet);
        return testGrid;
    } finally {
        if (is != null) {
            is.close();
        }
    }
}

From source file:com.opengamma.financial.security.equity.GICSCodeDescription.java

License:Open Source License

/**
 *  Load S&P GICS code mappings from an Apace POI HSSFWorkbook 
 * @param workbook HSSFWorkbook to parse S&P GCIS Excel
 * @param gicsMap Map to add mappings to
 *///from  w  w  w  .  j  a v  a2 s. c o m
static void processGICSExcelWorkbook(Workbook workbook, Map<String, String> gicsMap) {

    //Assume 1 sheet
    Sheet sheet = workbook.getSheetAt(0);
    if (sheet == null) {
        return;
    }
    for (int rowNum = sheet.getFirstRowNum(); rowNum <= sheet.getLastRowNum(); rowNum++) {
        Row row = sheet.getRow(rowNum);
        if (row == null) {
            continue;
        }
        for (int cellNum = 0; cellNum < row.getPhysicalNumberOfCells(); cellNum++) {
            Cell cell = row.getCell(cellNum, Row.CREATE_NULL_AS_BLANK);
            if (isNumeric(cell)) {
                //worst case if the Excel file is in an  incorrect (or updated) format
                // is that number -> random or empty string mappings will be created
                gicsMap.put(getGICSCellValue(cell), getGICSCellValue(row, cellNum + 1));
            }
        }
    }
}

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;
    }/*from   ww w . ja  v  a  2 s .co 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.ostrichemulators.semtool.poi.main.POIReader.java

License:Open Source License

public static ImportData readNonloadingSheet(Workbook workbook) {
    ImportData id = new ImportData();

    int sheets = workbook.getNumberOfSheets();
    for (int sheetnum = 0; sheetnum < sheets; sheetnum++) {
        Sheet sheet = workbook.getSheetAt(sheetnum);
        String sheetname = workbook.getSheetName(sheetnum);

        // we need to shoehorn the arbitrary data from a spreadsheet into our
        // ImportData class, which has restrictions on the data...we're going
        // to do it by figuring out the row with the most columns, and then
        // naming all the columns with A, B, C...AA, AB...
        // then load everything as if it was plain data
        // first, figure out our max number of columns
        int rows = sheet.getLastRowNum();
        int maxcols = Integer.MIN_VALUE;
        for (int r = 0; r <= rows; r++) {
            Row row = sheet.getRow(r);//from w  ww . j  a v a2 s. com
            if (null != row) {
                int cols = (int) row.getLastCellNum();
                if (cols > maxcols) {
                    maxcols = cols;
                }
            }
        }

        // second, make "properties" for each column
        LoadingSheetData nlsd = new LoadingSheetData(sheetname, "A");
        for (int c = 1; c < maxcols; c++) {
            nlsd.addProperty(Integer.toString(c));
        }

        // lastly, fill the sheets
        for (int r = 0; r <= rows; r++) {
            Row row = sheet.getRow(r);
            if (null != row) {
                Map<String, Value> propmap = new HashMap<>();

                int lastpropcol = row.getLastCellNum();
                for (int c = 1; c <= lastpropcol; c++) {
                    String val = getString(row.getCell(c));
                    if (!val.isEmpty()) {
                        propmap.put(Integer.toString(c), VF.createLiteral(val));
                    }
                }

                nlsd.add(getString(row.getCell(0)), propmap);
            }
        }

        if (!nlsd.isEmpty()) {
            id.add(nlsd);
        }
    }

    return id;
}

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

public FileInputStream generateAllEmployeeReport(File file) throws ExceptionWrapper {
    try {/*from   www. j  a  va  2 s.com*/
        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 generateLicenseReport(File file) throws ExceptionWrapper {
    try {//from w w  w .  java2  s . 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);
    }

}

From source file:com.projectswg.tools.controllers.MainController.java

License:Open Source License

private void handlePopulateList(File source) throws IOException, InvalidFormatException {
    Workbook workbook = WorkbookFactory.create(source);
    outputSelectionList.clear();//from  ww  w .j  a  v a  2 s. c om
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        outputSelectionList.add(getFileItem(source.getAbsolutePath(), workbook.getSheetAt(i)));
    }
    workbook.close();
}