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

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

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

Introduction

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

Prototype

Sheet getSheet(String name);

Source Link

Document

Get sheet with the given name

Usage

From source file:com.ncc.excel.test.ExcelUtil.java

License:Apache License

/** 
 * ?Excel //from www .  j a va2  s  .  c  o m
 *  
 * @Title: readExcel 
 * @Date : 2014-9-11 ?11:26:53 
 * @param wb 
 * @return 
 */
private List<Row> readExcel(Workbook wb) {
    List<Row> rowList = new ArrayList<Row>();

    int sheetCount = 1;//??sheet?  

    Sheet sheet = null;
    if (onlyReadOneSheet) { //??sheet  
        // ??sheet(?????)  
        sheet = selectedSheetName.equals("") ? wb.getSheetAt(selectedSheetIdx) : wb.getSheet(selectedSheetName);
    } else { //?sheet  
        sheetCount = wb.getNumberOfSheets();//????  
    }

    // ?sheet  
    for (int t = startSheetIdx; t < sheetCount + endSheetIdx; t++) {
        // ??sheet  
        if (!onlyReadOneSheet) {
            sheet = wb.getSheetAt(t);
        }

        //???  
        int lastRowNum = sheet.getLastRowNum();

        if (lastRowNum > 0) { //>0?  
            out("\n????" + sheet.getSheetName() + "");
        }

        Row row = null;
        // ?  
        for (int i = startReadPos; i <= lastRowNum + endReadPos; i++) {
            row = sheet.getRow(i);
            if (row != null) {
                rowList.add(row);
                out("" + (i + 1) + "", false);
                // ???  
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    String value = getCellValue(row.getCell(j));
                    if (!value.equals("")) {
                        out(value + " | ", false);
                    }
                }
                out("");
            }
        }
    }
    return rowList;
}

From source file:com.oleke.facebookcrawler.ExcelAPI.java

License:Apache License

/**
 * This method initializes an Excel Workbook and an Excel Sheet
 *
 * @param filename The output/input filename
 * @param sheetname the name of the sheet
 * @return Returns a Sheet//from  w  ww  .j  a v a2  s .  com
 */
public Sheet initExcel(String filename, String sheetname) {
    Workbook wbk;
    if (!new File(filename + ".xls").exists()) {
        wbk = createExcel(filename, sheetname);
    } else {
        wbk = loadExcel(filename);
    }
    Sheet sh;
    sh = wbk.getSheet(sheetname);
    return sh;
}

From source file:com.plugin.excel.util.ExcelFileHelper.java

License:Apache License

/**
 * It helps to update cell and format the excell based on the formatting defined in ExcelCell.{@link ExcelFormat}
 * /*from   w  w w.  jav a2  s . c  o m*/
 * @param cell
 * @param excell
 * @param style
 * @param font
 */
private static void updateCell(Cell cell, ExcelCell excell, Map<IndexedColors, CellStyle> s_cellStyle,
        Workbook workbook, Font font, Font invisibleFont) {
    if (excell != null) {

        // [1] format cell
        formatCell(workbook, cell, excell, s_cellStyle, font, invisibleFont);

        // [2] set enum
        if (!excell.isConsiderEnum()) {
            if (StringUtils.isNotBlank(excell.getDisplayText())) {
                cell.setCellValue(excell.getDisplayText());
            }
            if (!excell.isMultiSelect() && excell.isNumberValidation()) {
                addNumberValidation(cell);
            }
        } else {
            String[] list = (String[]) excell.getRestriction().getEnumValues()
                    .toArray(new String[excell.getRestriction().getEnumValues().size()]);

            SXSSFSheet sheet = (SXSSFSheet) cell.getSheet();

            DataValidationHelper dvHelper = sheet.getDataValidationHelper();
            DataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
                    .createExplicitListConstraint(list);
            CellRangeAddressList regions = new CellRangeAddressList(cell.getRowIndex(), cell.getRowIndex(),
                    cell.getColumnIndex(), cell.getColumnIndex());
            DataValidation dataValidation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint,
                    regions);
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.createErrorBox("Not Applicable", "Can't change the value");
            dataValidation.setShowErrorBox(true);

            try {
                if (isValidEnumList(list)) {
                    sheet.addValidationData(dataValidation);
                } else {
                    Sheet hidden = null;
                    String hiddenName = "hidden" + getHiddenIndex(excell.getReferenceText());
                    Workbook wBook = cell.getSheet().getWorkbook();
                    if (cell.getSheet().getWorkbook().getSheet(hiddenName) != null) {
                        hidden = wBook.getSheet(hiddenName);
                    } else {
                        hidden = wBook.createSheet(hiddenName);

                        for (int i = 0, length = list.length; i < length; i++) {
                            String name = list[i];
                            Row row = hidden.createRow(i);
                            Cell cell1 = row.createCell(0);
                            cell1.setCellValue(name);
                        }
                        Name namedCell = hidden.getWorkbook().getName(hiddenName);
                        namedCell = namedCell != null ? namedCell : hidden.getWorkbook().createName();
                        namedCell.setNameName(hiddenName);
                        namedCell.setRefersToFormula(hiddenName + "!$A$1:$A$" + list.length);
                    }

                    dvConstraint = (XSSFDataValidationConstraint) dvHelper
                            .createFormulaListConstraint(hiddenName);
                    dataValidation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
                    dataValidation.createErrorBox("Not Applicable", "Can't change the value");
                    dataValidation.setShowErrorBox(true);

                    cell.getSheet().addValidationData(dataValidation);
                    wBook.setSheetHidden(wBook.getSheetIndex(hidden), true);

                }

            } catch (Exception e) {
                String msg = "Excel creation failed while building cell: " + excell.getDisplayText();
                throw new IllegalStateException(msg, e);
            }

            // cell.setCellValue(excelConfig.getDropDownMsg());
        }

    }

}

From source file:com.projectswg.tools.ExcelToIff.java

License:Open Source License

private static void convertSheet(String path, String sheetStr) {
    File file = new File(path);
    if (!file.exists()) {
        System.err.println(String.format("Could not convert %s as it doesn't exist!", path));
        return;/*  w  w  w .j ava2  s.  co  m*/
    }

    try {
        Workbook workbook = WorkbookFactory.create(file);
        Sheet sheet = workbook.getSheet(sheetStr);
        if (sheet == null)
            sheet = workbook.getSheetAt(Integer.valueOf(sheetStr));
        if (sheet == null) {
            System.err.println(String.format("Could not convert %s as there is no sheet name or id that is %s",
                    path, sheetStr));
        }
        System.out
                .println("Converting sheet " + sheet.getSheetName() + " in workbook " + file.getAbsolutePath());
        convertSheet(new File(file.getAbsolutePath().split("\\.")[0] + "_" + sheet.getSheetName() + ".iff"),
                sheet);
        System.out.println("Conversion for sheet " + sheet.getSheetName() + " in workbook "
                + file.getAbsolutePath() + " completed.");
    } catch (IOException | InvalidFormatException e) {
        e.printStackTrace();
    }
}

From source file:com.quanticate.opensource.spreadsheetexcerpt.excerpt.POIExcerpterAndMerger.java

License:Apache License

protected List<Sheet> identifySheets(String[] sheetNames, Workbook workbook) {
    List<Sheet> sheets = new ArrayList<Sheet>(sheetNames.length);
    for (String sn : sheetNames) {
        Sheet s = workbook.getSheet(sn);
        if (s == null)
            throw new IllegalArgumentException("Sheet not found with name '" + sn + "'");

        sheets.add(s);/*from w  w  w. j  a  va 2s .c o m*/
    }
    return sheets;
}

From source file:com.sonicle.webtop.core.io.input.MemoryExcelFileReader.java

License:Open Source License

protected Sheet getSheet(Workbook wb) {
    if (StringUtils.isBlank(sheet)) {
        return wb.getSheetAt(0);
    } else {/*from   w  w w  .j a  v  a2  s .co m*/
        return wb.getSheet(sheet);
    }
}

From source file:com.swordlord.gozer.components.csv.GCsvList.java

License:Open Source License

@Override
public void renderToWorkbook(Workbook wb) {
    Sheet sheet = null;/*w  ww  .  j  a v a 2  s  . c om*/
    String caption = _obList.getCaption();

    if (caption == null) {
        sheet = wb.createSheet();
    } else {
        if (wb.getSheet(caption) == null) {
            sheet = wb.createSheet(caption);
        } else {
            sheet = wb.createSheet(MessageFormat.format("{0} {1}", caption, wb.getNumberOfSheets()));
        }
    }

    createTableColumn(sheet);
    createRows(sheet);
}

From source file:com.vaadin.addon.spreadsheet.SpreadsheetFactory.java

License:Open Source License

private static Sheet createNewSheet(Workbook workbook) {
    int idx = workbook.getNumberOfSheets() + 1;
    String sheetname = "Sheet" + idx;
    while (workbook.getSheet(sheetname) != null) {
        idx++;//from w  w w .j a v  a  2s  .  c  om
        sheetname = "Sheet" + idx;
    }
    return workbook.createSheet(sheetname);
}

From source file:com.vermeg.convertisseur.service.ConvServiceImpl.java

/**
 *
 * @param file//w  w w.  j ava  2s  . c  o  m
 * @return
 * @throws FileNotFoundException
 * @throws InvalidFormatException
 * @throws IOException
 */
/*this method convert a multipart file to json object */
@Override
public JSONObject convert(MultipartFile file, String name)
        throws FileNotFoundException, InvalidFormatException, IOException {

    // File file = new File("C:\\Users\\Ramzi\\Documents\\PFE\\developpement\\avancement.xlsx");

    File filez = File.createTempFile("fichier", "xslx");
    file.transferTo(filez);
    FileInputStream inp = new FileInputStream(filez);
    Workbook workbook = WorkbookFactory.create(inp);
    //Sheet sheet = workbook.getSheetAt( 0 );
    Sheet sheet = workbook.getSheet(name);
    // Start constructing JSON.
    JSONObject json = new JSONObject();

    // Iterate through the rows.
    JSONArray rows = new JSONArray();
    for (Iterator<Row> rowsIT = sheet.rowIterator(); rowsIT.hasNext();) {
        Row row = rowsIT.next();
        JSONObject jRow = new JSONObject();

        // Iterate through the cells.
        JSONArray cells = new JSONArray();
        for (Iterator<Cell> cellsIT = row.cellIterator(); cellsIT.hasNext();) {
            Cell cell = cellsIT.next();
            // System.out.println(cell.getCellType());
            //           cells.put(cell.getDateCellValue());
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                cells.put(cell.getRichStringCellValue().getString());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    cells.put(cell.getDateCellValue());
                } else {
                    cells.put(cell.getNumericCellValue());
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                cells.put(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                cells.put(cell.getCellFormula());
                break;
            default:
                System.out.println();
            }
        }
        jRow.put("cell", cells);
        rows.put(cells);
        //rows.put( jRow );
    }

    // Create the JSON.
    json.put("rows", rows);
    System.out.println(json.toString());
    return json;
}

From source file:com.vermeg.convertisseur.service.ConvServiceImpl.java

@Override
public JSONObject convert(String file, String name)
        throws FileNotFoundException, InvalidFormatException, IOException {

    // File file = new File("C:\\Users\\Ramzi\\Documents\\PFE\\developpement\\avancement.xlsx");

    File filez = File.createTempFile("fichier", "xslx");
    byte[] data = Base64.decodeBase64(file);
    FileOutputStream fos = new FileOutputStream(filez);
    fos.write(data);/*w w  w  .j av a2  s  . co m*/
    fos.close();
    //file.transferTo(filez);
    FileInputStream inp = new FileInputStream(filez);
    Workbook workbook = WorkbookFactory.create(inp);
    //Sheet sheet = workbook.getSheetAt( 0 );
    Sheet sheet = workbook.getSheet(name);
    // Start constructing JSON.
    JSONObject json = new JSONObject();

    // Iterate through the rows.
    JSONArray rows = new JSONArray();
    for (Iterator<Row> rowsIT = sheet.rowIterator(); rowsIT.hasNext();) {
        Row row = rowsIT.next();
        JSONObject jRow = new JSONObject();

        // Iterate through the cells.
        JSONArray cells = new JSONArray();
        for (Iterator<Cell> cellsIT = row.cellIterator(); cellsIT.hasNext();) {
            Cell cell = cellsIT.next();

            if (cell.getCellType() == CELL_TYPE_NUMERIC) {
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    cells.put(cell.getDateCellValue());
                } else
                    cells.put(cell.getNumericCellValue());
            } else
                cells.put(cell.getStringCellValue());
        }
        jRow.put("cell", cells);
        rows.put(cells);
        //rows.put( jRow );
    }

    // Create the JSON.
    json.put("rows", rows);
    System.out.println(json.toString());
    return json;
}