Example usage for org.apache.poi.ss.usermodel Name setSheetIndex

Introduction

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

Prototype

public void setSheetIndex(int sheetId);

Source Link

Document

Tell Excel that this name applies to the worksheet with the specified index instead of the entire workbook.

Usage

From source file:org.openelis.bean.QcChartReport1Bean.java

License:Open Source License

private void finishSheet(HSSFSheet sheet, HSSFWorkbook wb, String qcName, String qcType, String sheetName) {
    int i, columnIndex;
    ArrayList<DictionaryDO> tempQcColumns;
    DictionaryDO dict;//w w w . j  av a2 s  .  c om
    HashSet<Integer> emptyColumns;
    Name rangeName;
    Row row;
    String rangeFormula;

    if (qcColumns != null && !qcColumns.isEmpty())
        row = sheet.getRow(32);
    else
        row = sheet.getRow(3);
    emptyColumns = new HashSet<Integer>();
    for (i = 0; i < row.getLastCellNum(); i++) {
        if (i >= maxChars.size() || maxChars.get(i) == 0)
            emptyColumns.add(i);
    }

    setHeaderCells(sheet, qcName, qcType, sheetName);

    if (qcColumns != null && !qcColumns.isEmpty()) {
        tempQcColumns = new ArrayList<DictionaryDO>();
        tempQcColumns.addAll(qcColumns);
        for (i = tempQcColumns.size() - 1; i > -1; i--) {
            if (emptyColumns.contains(i + 5)) {
                tempQcColumns.remove(i);
                removeColumn(sheet, i + 5);
                maxChars.remove(i + 5);
            }
        }

        rangeName = getName(wb, sheet, "RowNumber");
        if (rangeName == null) {
            rangeName = wb.createName();
            rangeName.setSheetIndex(wb.getSheetIndex(sheet));
            rangeName.setNameName("RowNumber");
        }
        rangeFormula = sheet.getSheetName() + "!$" + CellReference.convertNumToColString(0) + "$33:" + "$"
                + CellReference.convertNumToColString(0) + "$" + (sheet.getLastRowNum() + 1);
        rangeName.setRefersToFormula(rangeFormula);

        /*
         * Create named ranges for the graph to be able to locate the appropriate
         * data
         */
        columnIndex = 5;
        for (i = 0; i < tempQcColumns.size(); i++) {
            dict = tempQcColumns.get(i);
            if (!DataBaseUtil.isEmpty(dict.getCode())) {
                rangeName = getName(wb, sheet, dict.getCode());
                if (rangeName == null) {
                    rangeName = wb.createName();
                    rangeName.setSheetIndex(wb.getSheetIndex(sheet));
                    rangeName.setNameName(dict.getCode());
                }
                rangeFormula = rangeName.getRefersToFormula();
                if (rangeFormula != null && rangeFormula.length() > 0
                        && !"$A$2".equals(rangeFormula.substring(rangeFormula.indexOf("!") + 1)))
                    rangeFormula += ",";
                else
                    rangeFormula = "";
                rangeFormula += sheet.getSheetName() + "!$" + CellReference.convertNumToColString(columnIndex)
                        + "$33:" + "$" + CellReference.convertNumToColString(columnIndex) + "$"
                        + (sheet.getLastRowNum() + 1);
                rangeName.setRefersToFormula(rangeFormula);
            }
            columnIndex++;
        }
        /*
         * make each column wide enough to show the longest string in it; the
         * width for each column is set as the maximum number of characters in
         * that column multiplied by 256; this is because the default width of
         * one character is 1/256 units in Excel
         */
        for (i = 5; i < maxChars.size(); i++)
            sheet.setColumnWidth(i, maxChars.get(i) * 256);
    } else if (worksheetHeaders != null && worksheetHeaders.size() > 0) {
        /*
         * make each column wide enough to show the longest string in it; the
         * width for each column is set as the maximum number of characters in
         * that column multiplied by 256; this is because the default width of
         * one character is 1/256 units in Excel
         */
        for (i = 0; i < maxChars.size(); i++)
            sheet.setColumnWidth(i, maxChars.get(i) * 256);
    }

    wb.setSheetName(wb.getSheetIndex(sheet), sheetName);
    sheet.setForceFormulaRecalculation(true);
    maxChars.clear();
}

---