Example usage for org.apache.poi.hssf.usermodel HSSFName getRefersToFormula

List of usage examples for org.apache.poi.hssf.usermodel HSSFName getRefersToFormula

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFName
  6. getRefersToFormula

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFName getRefersToFormula.

Prototype

public String getRefersToFormula()

Source Link

Usage

From source file:com.haulmont.yarg.formatters.impl.xls.HSSFRangeHelper.java

License:Apache License

public static AreaReference getAreaForRange(HSSFWorkbook workbook, String rangeName) {
    int rangeNameIdx = workbook.getNameIndex(rangeName);
    if (rangeNameIdx == -1)
        return null;

    HSSFName aNamedRange = workbook.getNameAt(rangeNameIdx);
    return new AreaReference(aNamedRange.getRefersToFormula(), SpreadsheetVersion.EXCEL97);
}

From source file:com.haulmont.yarg.formatters.impl.XLSFormatter.java

License:Apache License

/**
 * Method creates mapping [rangeName : List<CellRangeAddress>].
 * List contains all merge regions for this named range.
 * Attention: if merged regions writes wrong - look on methods isMergeRegionInsideNamedRange or isNamedRangeInsideMergeRegion
 * todo: how to recognize if merge region must be copied with named range
 *
 * @param currentSheet Sheet which contains merge regions
 *//*from ww w .ja  v a2s.  c  om*/
protected void initMergeRegions(HSSFSheet currentSheet) {
    int rangeNumber = templateWorkbook.getNumberOfNames();
    for (int i = 0; i < rangeNumber; i++) {
        HSSFName aNamedRange = templateWorkbook.getNameAt(i);

        String refersToFormula = aNamedRange.getRefersToFormula();
        if (!AreaReference.isContiguous(refersToFormula)) {
            continue;
        }

        AreaReference aref = new AreaReference(refersToFormula);
        Integer rangeFirstRow = aref.getFirstCell().getRow();
        Integer rangeFirstColumn = (int) aref.getFirstCell().getCol();
        Integer rangeLastRow = aref.getLastCell().getRow();
        Integer rangeLastColumn = (int) aref.getLastCell().getCol();

        for (int j = 0; j < currentSheet.getNumMergedRegions(); j++) {
            CellRangeAddress mergedRegion = currentSheet.getMergedRegion(j);
            if (mergedRegion != null) {
                Integer regionFirstRow = mergedRegion.getFirstRow();
                Integer regionFirstColumn = mergedRegion.getFirstColumn();
                Integer regionLastRow = mergedRegion.getLastRow();
                Integer regionLastColumn = mergedRegion.getLastColumn();

                boolean mergedInsideNamed = isMergeRegionInsideNamedRange(rangeFirstRow, rangeFirstColumn,
                        rangeLastRow, rangeLastColumn, regionFirstRow, regionFirstColumn, regionLastRow,
                        regionLastColumn);

                boolean namedInsideMerged = isNamedRangeInsideMergeRegion(rangeFirstRow, rangeFirstColumn,
                        rangeLastRow, rangeLastColumn, regionFirstRow, regionFirstColumn, regionLastRow,
                        regionLastColumn);

                if (mergedInsideNamed || namedInsideMerged) {
                    String name = aNamedRange.getNameName();
                    SheetRange sheetRange = new SheetRange(mergedRegion, currentSheet.getSheetName());
                    if (mergeRegionsForRangeNames.get(name) == null) {
                        ArrayList<SheetRange> list = new ArrayList<SheetRange>();
                        list.add(sheetRange);
                        mergeRegionsForRangeNames.put(name, list);
                    } else {
                        mergeRegionsForRangeNames.get(name).add(sheetRange);
                    }
                }
            }
        }
    }
}

From source file:com.haulmont.yarg.formatters.impl.XLSFormatter.java

License:Apache License

/**
 * Create new merge regions in result sheet identically to range's merge regions from template.
 * Not support copy of frames and rules//from w  w w.  jav  a  2  s .c  o  m
 *
 * @param resultSheet            - result sheet
 * @param rangeName              - range name
 * @param firstTargetRangeRow    - first column of target range
 * @param firstTargetRangeColumn - first column of target range
 */
protected void copyMergeRegions(HSSFSheet resultSheet, String rangeName, int firstTargetRangeRow,
        int firstTargetRangeColumn) {
    int rangeNameIdx = templateWorkbook.getNameIndex(rangeName);
    if (rangeNameIdx == -1)
        return;

    HSSFName aNamedRange = templateWorkbook.getNameAt(rangeNameIdx);
    AreaReference aref = new AreaReference(aNamedRange.getRefersToFormula());
    int column = aref.getFirstCell().getCol();
    int row = aref.getFirstCell().getRow();

    List<SheetRange> regionsList = mergeRegionsForRangeNames.get(rangeName);
    if (regionsList != null)
        for (SheetRange sheetRange : regionsList) {
            if (resultSheet.getSheetName().equals(sheetRange.getSheetName())) {
                CellRangeAddress cra = sheetRange.getCellRangeAddress();
                if (cra != null) {
                    int regionHeight = cra.getLastRow() - cra.getFirstRow() + 1;
                    int regionWidth = cra.getLastColumn() - cra.getFirstColumn() + 1;

                    int regionVOffset = cra.getFirstRow() - row;
                    int regionHOffset = cra.getFirstColumn() - column;

                    CellRangeAddress newRegion = cra.copy();
                    newRegion.setFirstColumn(regionHOffset + firstTargetRangeColumn);
                    newRegion.setLastColumn(regionHOffset + regionWidth - 1 + firstTargetRangeColumn);

                    newRegion.setFirstRow(regionVOffset + firstTargetRangeRow);
                    newRegion.setLastRow(regionVOffset + regionHeight - 1 + firstTargetRangeRow);

                    boolean skipRegion = false;

                    for (int mergedIndex = 0; mergedIndex < resultSheet.getNumMergedRegions(); mergedIndex++) {
                        CellRangeAddress mergedRegion = resultSheet.getMergedRegion(mergedIndex);

                        if (!intersects(newRegion, mergedRegion)) {
                            continue;
                        }

                        skipRegion = true;
                    }

                    if (!skipRegion) {
                        resultSheet.addMergedRegion(newRegion);
                    }
                }
            }
        }
}

From source file:net.sf.jasperreports.engine.export.JRXlsExporter.java

License:Open Source License

protected void closeWorkbook(OutputStream os) throws JRException {
    try {//from ww  w  . j ava 2 s  . c om
        for (Object anchorName : anchorNames.keySet()) // the anchorNames map contains no entries for reports with ignore anchors == true;
        {
            HSSFName anchor = anchorNames.get(anchorName);
            List<Hyperlink> linkList = anchorLinks.get(anchorName);
            anchor.setRefersToFormula(
                    "'" + workbook.getSheetName(anchor.getSheetIndex()) + "'!" + anchor.getRefersToFormula());

            if (linkList != null && !linkList.isEmpty()) {
                for (Hyperlink link : linkList) {
                    link.setAddress(anchor.getRefersToFormula());
                }
            }
        }

        int index = 0;
        for (Integer linkPage : pageLinks.keySet()) { // the pageLinks map contains no entries for reports with ignore hyperlinks == true 
            List<Hyperlink> linkList = pageLinks.get(linkPage);
            if (linkList != null && !linkList.isEmpty()) {
                for (Hyperlink link : linkList) {
                    index = onePagePerSheetMap.get(linkPage - 1) != null
                            ? (onePagePerSheetMap.get(linkPage - 1) ? Math.max(0, linkPage - 1)
                                    : Math.max(0, sheetsBeforeCurrentReportMap.get(linkPage)))
                            : 0;
                    link.setAddress("'" + workbook.getSheetName(index) + "'!$A$1");
                }
            }
        }

        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            workbook.getSheetAt(i).setForceFormulaRecalculation(true);
        }

        workbook.write(os);
    } catch (IOException e) {
        throw new JRException("Error generating XLS report : " + jasperPrint.getName(), e);
    }
}

From source file:net.sf.jasperreports.engine.export.JRXlsMetadataExporter.java

License:Open Source License

protected void closeWorkbook(OutputStream os) throws JRException {
    try {//from ww w. j a va2 s. c om
        for (Object anchorName : anchorNames.keySet()) {
            HSSFName anchor = anchorNames.get(anchorName);
            List<Hyperlink> linkList = anchorLinks.get(anchorName);
            anchor.setRefersToFormula(
                    "'" + workbook.getSheetName(anchor.getSheetIndex()) + "'!" + anchor.getRefersToFormula());

            if (linkList != null && !linkList.isEmpty()) {
                for (Hyperlink link : linkList) {
                    link.setAddress(anchor.getRefersToFormula());
                }
            }
        }

        int index = 0;
        for (Integer linkPage : pageLinks.keySet()) {
            List<Hyperlink> linkList = pageLinks.get(linkPage);
            if (linkList != null && !linkList.isEmpty()) {
                for (Hyperlink link : linkList) {
                    index = onePagePerSheetMap.get(linkPage - 1) != null
                            ? (onePagePerSheetMap.get(linkPage - 1) ? Math.max(0, linkPage - 1)
                                    : Math.max(0, sheetsBeforeCurrentReportMap.get(linkPage)))
                            : 0;
                    link.setAddress("'" + workbook.getSheetName(index) + "'!$A$1");
                }
            }
        }

        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            HSSFSheet currentSheet = workbook.getSheetAt(i);
            currentSheet.setForceFormulaRecalculation(true);
            for (String columnName : columnNames) {
                Integer columnWidth = columnWidths.get(columnName);
                if (columnWidth != null && columnWidth < Integer.MAX_VALUE) {
                    currentSheet.setColumnWidth(columnNamesMap.get(columnName),
                            Math.min(43 * columnWidth, 256 * 255));
                } else {
                    currentSheet.autoSizeColumn(columnNamesMap.get(columnName), false);
                }
            }
        }
        workbook.write(os);
    } catch (IOException e) {
        throw new JRException("Error generating XLS metadata report : " + jasperPrint.getName(), e);
    }
}

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

License:Open Source License

private Cell getCellForName(HSSFSheet sheet, HSSFName name) {
    AreaReference aref;//from w ww  .j  a v a 2  s.c  o  m
    Cell cell;
    CellReference cref[];

    cell = null;
    if (name != null && !name.isDeleted()) {
        aref = new AreaReference(name.getRefersToFormula());
        cref = aref.getAllReferencedCells();
        cell = sheet.getRow(cref[0].getRow()).getCell((int) cref[0].getCol());
    }

    return cell;
}

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

License:Open Source License

@TransactionTimeout(600)
public ArrayList<IdNameVO> getColumnNames(Integer formatId) throws Exception {
    int i;//from   w ww  .j  a  v a 2s .  co m
    AreaReference aref;
    ArrayList<IdNameVO> columnNames;
    CellReference cref[];
    DictionaryViewDO formatVDO;
    FileInputStream in;
    HSSFName name;
    HSSFWorkbook wb;

    columnNames = new ArrayList<IdNameVO>();

    try {
        formatVDO = dictionary.fetchById(formatId);
    } catch (NotFoundException nfE) {
        formatVDO = new DictionaryViewDO();
        formatVDO.setEntry("DefaultTotal");
    } catch (Exception anyE) {
        throw new Exception("Error retrieving worksheet format: " + anyE.getMessage());
    }

    try {
        in = new FileInputStream(getWorksheetTemplateFileName(formatVDO));
    } catch (FileNotFoundException fnfE) {
        throw new Exception("Error loading template file: " + fnfE.getMessage());
    }

    try {
        wb = new HSSFWorkbook(in, true);
    } catch (IOException ioE) {
        throw new Exception("Error loading workbook from template file: " + ioE.getMessage());
    }

    for (i = 0; i < wb.getNumberOfNames(); i++) {
        name = wb.getNameAt(i);
        if (name.getRefersToFormula() != null) {
            aref = new AreaReference(name.getRefersToFormula());
            cref = aref.getAllReferencedCells();
            columnNames.add(
                    new IdNameVO(new Integer(Short.valueOf(cref[0].getCol()).intValue()), name.getNameName()));
        }
    }

    return columnNames;
}

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

License:Open Source License

private HashMap<String, String> loadNamesByCellReference(HSSFWorkbook wb) {
    int i;//w  w w.j a  v a  2  s  .c o  m
    HSSFName name;
    HashMap<String, String> names;

    names = new HashMap<String, String>();

    for (i = 0; i < wb.getNumberOfNames(); i++) {
        name = wb.getNameAt(i);
        names.put(name.getRefersToFormula(), name.getNameName());
    }

    return names;
}

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

License:Open Source License

private Cell getCellForName(HSSFSheet sheet, String name) {
    AreaReference aref;/*ww  w .ja  v  a  2 s .  c om*/
    Cell cell;
    CellReference cref[];
    HSSFName cellName;

    cell = null;
    cellName = sheet.getWorkbook().getName(name);
    if (cellName != null && !cellName.isDeleted()) {
        aref = new AreaReference(cellName.getRefersToFormula());
        cref = aref.getAllReferencedCells();
        cell = sheet.getRow(cref[0].getRow()).getCell((int) cref[0].getCol());
    }

    return cell;
}