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

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

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

Introduction

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

Prototype

int getNumberOfSheets();

Source Link

Document

Get the number of spreadsheets in the workbook

Usage

From source file:br.ufal.cideei.util.count.SummaryBuilder.java

License:Open Source License

public static void buildSummary(String splShortName)
        throws InvalidFormatException, FileNotFoundException, IOException {

    // final String userHomeFolder = System.getProperty("user.home").substring(3);
    String userHomeFolder = "C:\\tst";
    final String output = userHomeFolder + File.separator + "summ.xls";
    File outputFile = new File(output);
    Workbook outputWorkbook;// w  ww .  j a  va 2  s  .c  om
    if (!outputFile.exists()) {
        outputFile.createNewFile();
        outputWorkbook = new HSSFWorkbook();
    } else {
        FileInputStream inputFileStream = new FileInputStream(outputFile);
        outputWorkbook = WorkbookFactory.create(inputFileStream);
    }

    {
        List<String> referencesForRDA3 = new ArrayList<String>();
        List<String> referencesForUVA3 = new ArrayList<String>();
        List<String> referencesForRDA2 = new ArrayList<String>();
        List<String> referencesForUVA2 = new ArrayList<String>();
        String fileName = "fs-" + splShortName + ".xls";
        String filePath = userHomeFolder + File.separator;
        String fullFileName = filePath + File.separator + "fs-" + splShortName + ".xls";
        Workbook workbook = WorkbookFactory.create(new FileInputStream(new File(fullFileName)));
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            Sheet sheet = workbook.getSheetAt(i);
            Row headerRow = sheet.getRow(0);
            for (Cell cell : headerRow) {
                String stringCellValue = cell.getStringCellValue();
                if (stringCellValue.equals("rd")) {
                    Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1);
                    Cell sumCell = sumRow.getCell(i);
                    CellReference sumCellRef = new CellReference(sumCell);
                    String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!"
                            + sumCellRef.formatAsString();
                    referencesForRDA2.add(cellRefForAnotherSheet);
                } else if (stringCellValue.equals("uv")) {
                    Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1);
                    Cell sumCell = sumRow.getCell(i);
                    CellReference sumCellRef = new CellReference(sumCell);
                    String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!"
                            + sumCellRef.formatAsString();
                    referencesForUVA2.add(cellRefForAnotherSheet);
                } else if (stringCellValue.equals("rd (a3)")) {
                    Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1);
                    Cell sumCell = sumRow.getCell(i);
                    CellReference sumCellRef = new CellReference(sumCell);
                    String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!"
                            + sumCellRef.formatAsString();
                    referencesForRDA3.add(cellRefForAnotherSheet);
                } else if (stringCellValue.equals("uv (a3)")) {
                    Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1);
                    Cell sumCell = sumRow.getCell(i);
                    CellReference sumCellRef = new CellReference(sumCell);
                    String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!"
                            + sumCellRef.formatAsString();
                    referencesForUVA3.add(cellRefForAnotherSheet);
                }
            }
        }
        if (outputWorkbook.getSheet(splShortName) != null) {
            outputWorkbook.removeSheetAt(outputWorkbook.getSheetIndex(splShortName));
        }
        Sheet outputSheet = outputWorkbook.createSheet(splShortName);
        Row RDA2Row = outputSheet.createRow(0);
        RDA2Row.createCell(0).setCellValue("RD A2");
        for (int i = 0; i < referencesForRDA2.size(); i++) {
            Cell createdCell = RDA2Row.createCell(i + 1);
            System.out.println(referencesForRDA2.get(i));
            createdCell.setCellType(Cell.CELL_TYPE_FORMULA);
            createdCell.setCellValue(referencesForRDA2.get(i));
        }
        Row UVA2Row = outputSheet.createRow(1);
        UVA2Row.createCell(0).setCellValue("UV A2");
        for (int i = 0; i < referencesForUVA2.size(); i++) {
            Cell createdCell = UVA2Row.createCell(i + 1);
            createdCell.setCellFormula(referencesForUVA2.get(i));
        }
        Row RDA3Row = outputSheet.createRow(2);
        RDA3Row.createCell(0).setCellValue("RD A3");
        for (int i = 0; i < referencesForRDA3.size(); i++) {
            Cell createdCell = RDA3Row.createCell(i + 1);
            createdCell.setCellFormula(referencesForRDA3.get(i));
        }
        Row UVA3Row = outputSheet.createRow(3);
        UVA3Row.createCell(0).setCellValue("UV A3");
        for (int i = 0; i < referencesForUVA3.size(); i++) {
            Cell createdCell = UVA3Row.createCell(i + 1);
            createdCell.setCellFormula(referencesForUVA3.get(i));
        }
    }
    FileOutputStream fileOutputStream = new FileOutputStream(outputFile);
    outputWorkbook.write(fileOutputStream);
    fileOutputStream.close();
}

From source file:cherry.parser.worksheet.RowBasedParser.java

License:Apache License

@Override
public List<TypeDef> parse(Workbook workbook) {
    List<TypeDef> list = new LinkedList<TypeDef>();
    int numOfSheets = workbook.getNumberOfSheets();
    for (int i = 0; i < numOfSheets; i++) {
        Sheet sheet = workbook.getSheetAt(i);
        list.addAll(parseSheet(sheet));// w w w  .  jav a2  s  .  com
    }
    return list;
}

From source file:cherry.parser.worksheet.SheetBasedParser.java

License:Apache License

@Override
public List<TypeDef> parse(Workbook workbook) {
    List<TypeDef> list = new LinkedList<TypeDef>();
    int numOfSheets = workbook.getNumberOfSheets();
    for (int i = 0; i < numOfSheets; i++) {
        Sheet sheet = workbook.getSheetAt(i);
        TypeDef typeDef = parseSheet(sheet);
        if (typeDef != null) {
            list.add(typeDef);//ww w.j  a v  a  2  s. com
        }
    }
    return list;
}

From source file:cn.afterturn.easypoi.cache.ExcelCache.java

License:Apache License

public static Workbook getWorkbook(String url, Integer[] sheetNums, boolean needAll) {
    InputStream is = null;//from   ww w .  ja v  a  2 s  .c  o  m
    List<Integer> sheetList = Arrays.asList(sheetNums);
    try {
        is = POICacheManager.getFile(url);
        Workbook wb = WorkbookFactory.create(is);
        // sheet
        if (!needAll) {
            for (int i = wb.getNumberOfSheets() - 1; i >= 0; i--) {
                if (!sheetList.contains(i)) {
                    wb.removeSheetAt(i);
                }
            }
        }
        return wb;
    } catch (Exception e) {
        LOGGER.error(e.getMessage(), e);
    } finally {
        try {
            is.close();
        } catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
        }
    }
    return null;
}

From source file:cn.afterturn.easypoi.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

public Workbook createExcelCloneByTemplate(TemplateExportParams params,
        Map<Integer, List<Map<String, Object>>> map) {
    // step 1. ??
    if (params == null || map == null || StringUtils.isEmpty(params.getTemplateUrl())) {
        throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR);
    }//from  ww w  . jav a2s . c  om
    Workbook wb = null;
    // step 2. ?Excel,??
    try {
        this.teplateParams = params;
        wb = ExcelCache.getWorkbook(teplateParams.getTemplateUrl(), teplateParams.getSheetNum(), true);
        int oldSheetNum = wb.getNumberOfSheets();
        List<String> oldSheetName = new ArrayList<>();
        for (int i = 0; i < oldSheetNum; i++) {
            oldSheetName.add(wb.getSheetName(i));
        }
        // KEY?
        List<Map<String, Object>> mapList;
        List<Integer> sheetNumList = new ArrayList<>();
        sheetNumList.addAll(map.keySet());
        Collections.sort(sheetNumList);

        //???
        for (Integer sheetNum : sheetNumList) {
            mapList = map.get(sheetNum);
            for (int i = mapList.size(); i > 0; i--) {
                wb.cloneSheet(sheetNum);
            }
        }
        for (int i = 0; i < oldSheetName.size(); i++) {
            wb.removeSheetAt(wb.getSheetIndex(oldSheetName.get(i)));
        }
        // ?
        setExcelExportStyler(
                (IExcelExportStyler) teplateParams.getStyle().getConstructor(Workbook.class).newInstance(wb));
        // step 3. ??
        int sheetIndex = 0;
        for (Integer sheetNum : sheetNumList) {
            mapList = map.get(sheetNum);
            for (int i = mapList.size() - 1; i >= 0; i--) {
                tempCreateCellSet.clear();
                if (mapList.get(i).containsKey(SHEET_NAME)) {
                    wb.setSheetName(sheetIndex, mapList.get(i).get(SHEET_NAME).toString());
                }
                parseTemplate(wb.getSheetAt(sheetIndex), mapList.get(i), params.isColForEach());
                sheetIndex++;
            }
        }
    } catch (Exception e) {
        LOGGER.error(e.getMessage(), e);
        return null;
    }
    return wb;
}

From source file:cn.afterturn.easypoi.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

public Workbook createExcelByTemplate(TemplateExportParams params, Map<Integer, Map<String, Object>> map) {
    // step 1. ??
    if (params == null || map == null || StringUtils.isEmpty(params.getTemplateUrl())) {
        throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR);
    }//from  w ww  .j  a  va  2 s . com
    Workbook wb = null;
    // step 2. ?Excel,??
    try {
        this.teplateParams = params;
        wb = getCloneWorkBook();
        // ?
        setExcelExportStyler(
                (IExcelExportStyler) teplateParams.getStyle().getConstructor(Workbook.class).newInstance(wb));
        // step 3. ??
        for (int i = 0, le = params.isScanAllsheet() ? wb.getNumberOfSheets()
                : params.getSheetNum().length; i < le; i++) {
            if (params.getSheetName() != null && params.getSheetName().length > i
                    && StringUtils.isNotEmpty(params.getSheetName()[i])) {
                wb.setSheetName(i, params.getSheetName()[i]);
            }
            tempCreateCellSet.clear();
            parseTemplate(wb.getSheetAt(i), map.get(i), params.isColForEach());
        }
    } catch (Exception e) {
        LOGGER.error(e.getMessage(), e);
        return null;
    }
    return wb;
}

From source file:cn.afterturn.easypoi.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

public Workbook createExcelByTemplate(TemplateExportParams params, Class<?> pojoClass, Collection<?> dataSet,
        Map<String, Object> map) {
    // step 1. ??
    if (params == null || map == null
            || (StringUtils.isEmpty(params.getTemplateUrl()) && params.getTemplateWb() == null)) {
        throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR);
    }//w w  w . java2s .  com
    Workbook wb = null;
    // step 2. ?Excel,??
    try {
        this.teplateParams = params;
        if (params.getTemplateWb() != null) {
            wb = params.getTemplateWb();
        } else {
            wb = getCloneWorkBook();
        }
        // ?
        setExcelExportStyler(
                (IExcelExportStyler) teplateParams.getStyle().getConstructor(Workbook.class).newInstance(wb));
        // step 3. ??
        for (int i = 0, le = params.isScanAllsheet() ? wb.getNumberOfSheets()
                : params.getSheetNum().length; i < le; i++) {
            if (params.getSheetName() != null && params.getSheetName().length > i
                    && StringUtils.isNotEmpty(params.getSheetName()[i])) {
                wb.setSheetName(i, params.getSheetName()[i]);
            }
            tempCreateCellSet.clear();
            parseTemplate(wb.getSheetAt(i), map, params.isColForEach());
        }
        if (dataSet != null) {
            // step 4. ?
            dataHandler = params.getDataHandler();
            if (dataHandler != null) {
                needHandlerList = Arrays.asList(dataHandler.getNeedHandlerFields());
            }
            addDataToSheet(pojoClass, dataSet, wb.getSheetAt(params.getDataSheetNum()), wb);
        }
    } catch (Exception e) {
        LOGGER.error(e.getMessage(), e);
        return null;
    }
    return wb;
}

From source file:cn.bzvs.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

public Workbook createExcleByTemplate(TemplateExportParams params, Class<?> pojoClass, Collection<?> dataSet,
        Map<String, Object> map) {
    // step 1. ??
    if (params == null || map == null || StringUtils.isEmpty(params.getTemplateUrl())) {
        throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR);
    }//ww  w.jav a  2s  .c  om
    Workbook wb = null;
    // step 2. ?Excel,??
    try {
        this.teplateParams = params;
        wb = getCloneWorkBook();
        // ?
        setExcelExportStyler(
                (IExcelExportStyler) teplateParams.getStyle().getConstructor(Workbook.class).newInstance(wb));
        // step 3. ??
        for (int i = 0, le = params.isScanAllsheet() ? wb.getNumberOfSheets()
                : params.getSheetNum().length; i < le; i++) {
            if (params.getSheetName() != null && params.getSheetName().length > i
                    && StringUtils.isNotEmpty(params.getSheetName()[i])) {
                wb.setSheetName(i, params.getSheetName()[i]);
            }
            tempCreateCellSet.clear();
            parseTemplate(wb.getSheetAt(i), map, params.isColForEach());
        }
        if (dataSet != null) {
            // step 4. ?
            dataHanlder = params.getDataHanlder();
            if (dataHanlder != null) {
                needHanlderList = Arrays.asList(dataHanlder.getNeedHandlerFields());
            }
            addDataToSheet(pojoClass, dataSet, wb.getSheetAt(params.getDataSheetNum()), wb);
        }
    } catch (Exception e) {
        e.getMessage();
        return null;
    }
    return wb;
}

From source file:cn.bzvs.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

public Workbook createExcleByTemplate(TemplateExportParams params, Map<Integer, Map<String, Object>> map) {
    // step 1. ??
    if (params == null || map == null || StringUtils.isEmpty(params.getTemplateUrl())) {
        throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR);
    }/*from   w  w w.  ja  v a 2 s  .  co m*/
    Workbook wb = null;
    // step 2. ?Excel,??
    try {
        this.teplateParams = params;
        wb = getCloneWorkBook();
        // step 3. ??
        for (int i = 0, le = params.isScanAllsheet() ? wb.getNumberOfSheets()
                : params.getSheetNum().length; i < le; i++) {
            if (params.getSheetName() != null && params.getSheetName().length > i
                    && StringUtils.isNotEmpty(params.getSheetName()[i])) {
                wb.setSheetName(i, params.getSheetName()[i]);
            }
            tempCreateCellSet.clear();
            parseTemplate(wb.getSheetAt(i), map.get(i), params.isColForEach());
        }
    } catch (Exception e) {
        e.getMessage();
        return null;
    }
    return wb;
}

From source file:com.actelion.research.spiritapp.report.AbstractReport.java

License:Open Source License

/**
 * Creates a new sheet, ensuring that the name is safe and unique
 * @param workbook//w ww. j ava 2s  . c  o  m
 * @param sheetName
 * @return
 */
public Sheet createSheet(Workbook workbook, String sheetName) {
    Set<String> names = new HashSet<String>();
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        names.add(workbook.getSheetName(i));
    }

    String safe = WorkbookUtil.createSafeSheetName(sheetName);

    String name;
    for (int i = 0;; i++) {
        name = safe + (i == 0 ? "" : " (" + i + ")");
        if (!names.contains(name))
            break;
    }
    Sheet sheet = wb.createSheet(name);
    sheet.setAutobreaks(true);
    sheet.setMargin(Sheet.LeftMargin, 1);
    sheet.setMargin(Sheet.RightMargin, 1);
    sheet.setMargin(Sheet.BottomMargin, .5);
    sheet.setMargin(Sheet.TopMargin, .5);

    sheet.setFitToPage(true);
    sheet.getPrintSetup().setLandscape(true);
    sheet.getPrintSetup().setFitWidth((short) 1);
    sheet.getPrintSetup().setFitHeight((short) 99);

    //      Footer footer = sheet.getFooter();
    //       footer.setRight( "Page " + HeaderFooter.page() + " of " + HeaderFooter.numPages() );

    return sheet;
}