Example usage for org.apache.poi.hssf.usermodel HSSFWorkbook getNameAt

List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getNameAt

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFWorkbook
  6. getNameAt

Introduction

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

Prototype

@Override
    public HSSFName getNameAt(int nameIndex)

Source Link

Usage

From source file:at.spardat.xma.mdl.grid.GridPOIAdapter.java

License:Open Source License

/**
 * Transfers the spreadsheet data from the POI <code>HSSFWorkbook</code> into the <code>IGridWMServer</code>.
 * Only the sheet on the given sheetIndex is copied.
        //w w w  . ja  v  a 2 s  .com
 * @param igrid the XMA model where to copy the data
 * @param book the POI represntation of the data
 * @param sheetIndex the index of the sheet to copy
 * @return a list containing all SysExceptions describing problems with individual cell formulas or ranges
 */
public static List poi2xma(IGridWM igrid, HSSFWorkbook book, int sheetIndex) {
    GridWM grid = (GridWM) igrid;
    try {
        List errorList = new ArrayList();
        grid.setSheetName(book.getSheetName(sheetIndex));

        grid.colors.clear();
        grid.initBuildInColors();
        short ic = GridWM.HSSF_FIRST_COLOR_INDEX;
        HSSFPalette palette = book.getCustomPalette();
        for (HSSFColor color = palette.getColor(ic); ic < 64 && color != null; color = palette.getColor(++ic)) {
            grid.colors.add(ic, new GridColor(color.getTriplet()));
        }

        grid.fonts.clear();
        int numFonts = book.getNumberOfFonts();
        if (numFonts > 4) {
            // adjust for "There is no 4" see code of org.apache.poi.hssf.model.Workbook.getFontRecordAt()
            numFonts += 1;
        }
        for (short i = 0; i < numFonts; i++) {
            HSSFFont font = book.getFontAt(i);
            byte fontstyle = GridFont.FONT_NORML;
            if (font.getBoldweight() >= HSSFFont.BOLDWEIGHT_BOLD)
                fontstyle |= GridFont.FONT_BOLD;
            if (font.getItalic())
                fontstyle |= GridFont.FONT_ITALIC;
            grid.fonts.add(i, new GridFont(font.getFontName(), fontstyle, font.getColor()));
        }

        grid.styles.clear();
        for (short i = 0, numStyles = book.getNumCellStyles(); i < numStyles; i++) {
            HSSFCellStyle style = book.getCellStyleAt(i);
            grid.styles.add(i, new GridCellStyle(style.getFontIndex(), style.getFillForegroundColor()));
        }

        grid.namedRanges.clear();
        for (int i = 0, numRanges = book.getNumberOfNames(); i < numRanges; i++) {
            HSSFName name = book.getNameAt(i);
            String rangeName = name.getNameName();
            String rangeRef = null;
            try { // ranges not defined but referenced by formulas have a name but no reference in HSSF
                rangeRef = name.getReference();
            } catch (Exception exc) {
                errorList.add(new SysException(exc, ((GridWM) grid).getMessage("inconsistentRange", rangeName))
                        .setCode(GridWM.CODE_inconsistentRange));
            }
            if (rangeRef != null) {
                try {
                    GridRange range = grid.getJeksDelegate().toRange(rangeRef);
                    range.setKey(rangeName);
                    grid.namedRanges.put(rangeName, range);
                } catch (Exception exc) {
                    errorList.add(new SysException(exc,
                            ((GridWM) grid).getMessage("unsupportedReference", rangeName, rangeRef))
                                    .setCode(GridWM.CODE_unsupportedReference));
                }
            }
        }

        grid.rows.clear();
        grid.cols.clear();
        grid.cells.clear();
        grid.delegate = new GridJeksDelegate(grid);
        HSSFSheet sheet = book.getSheetAt(sheetIndex);
        int firstColNum = Integer.MAX_VALUE;
        int lastColNum = Integer.MIN_VALUE;
        for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
            HSSFRow row = sheet.getRow(i);
            if (row == null)
                continue;
            if (row.getFirstCellNum() >= 0)
                firstColNum = Math.min(firstColNum, row.getFirstCellNum());
            lastColNum = Math.max(lastColNum, row.getLastCellNum());
            if (lastColNum > 255)
                lastColNum = 255;
            for (short j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                HSSFCell hssfcell = row.getCell(j);
                if (hssfcell == null)
                    continue;
                GridCell gridcell = grid.getOrCreateCell(i, j);
                switch (hssfcell.getCellType()) {
                case HSSFCell.CELL_TYPE_BLANK:
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    gridcell.setValue(hssfcell.getBooleanCellValue());
                    break;
                case HSSFCell.CELL_TYPE_ERROR:
                    // TODO: recherche error text
                    byte errorCode = hssfcell.getErrorCellValue();
                    //                    gridcell.setValue(errorCode);
                    gridcell.setValue("#ERROR");
                    errorList.add(new SysException(((GridWM) grid).getMessage("errorRecord",
                            grid.getJeksDelegate().toExcelRef(i, j), Byte.toString(errorCode)))
                                    .setCode(GridWM.CODE_errorRecord));
                    break;
                case HSSFCell.CELL_TYPE_FORMULA:
                    String formula = null;
                    try {
                        formula = hssfcell.getCellFormula();
                        gridcell.setFormula(formula);
                    } catch (SysException e) {
                        if (formula != null)
                            gridcell.setValue("=" + formula); //set it as text without interpretation
                        errorList.add(e);
                    }
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                    if (isDateCell(book, hssfcell)) {
                        gridcell.setValue(hssfcell.getDateCellValue());
                    } else {
                        gridcell.setValue(hssfcell.getNumericCellValue());
                    }
                    break;
                case HSSFCell.CELL_TYPE_STRING:
                    gridcell.setValue(hssfcell.getStringCellValue());
                    break;
                default:
                    throw new SysException("unknown cell type " + hssfcell.getCellType());
                }
                gridcell.setEditable(!hssfcell.getCellStyle().getLocked());
                gridcell.setStyle(hssfcell.getCellStyle().getIndex());
            }
        }

        final int scalefactor = 256 / 7; // empirically testet
        //        int width = sheet.getDefaultColumnWidth();  // returns nonsense
        //        width = width/scalefactor;
        //        grid.setDefaultColumnWidth(width);
        for (short i = (short) firstColNum; i <= lastColNum; i++) {
            int width = sheet.getColumnWidth(i);
            width = width / scalefactor;
            grid.getOrCreateColumn(i).setWidth(width);
        }

        if (firstColNum == Integer.MAX_VALUE)
            firstColNum = 0;
        if (lastColNum == Integer.MIN_VALUE)
            lastColNum = 0;
        grid.setMaxRange(
                new GridRange(grid, sheet.getFirstRowNum(), firstColNum, sheet.getLastRowNum(), lastColNum));
        grid.setVisibleRange(grid.getMaxRange());
        return errorList;
    } finally {
        grid.handle(grid.new GridReloadEvent());
    }
}

From source file:com.bayareasoftware.chartengine.ds.ExcelDataSource.java

License:Apache License

/**
 * work around bug with named cells//from   www.jav  a  2 s.c  o  m
 * 
 */
private static void rewriteFormulas(final HSSFWorkbook workbook) {
    //if (true) return;
    // build up a cache of names
    // this is just an easy way of fetching the HSSFName based on the string
    // representation of the name
    final Map<String, HSSFName> nameCache = new HashMap<String, HSSFName>(workbook.getNumberOfNames());
    for (int i = 0; i < workbook.getNumberOfNames(); i++) {
        final HSSFName name = workbook.getNameAt(i);
        nameCache.put(name.getNameName(), name);
    }
    // remove all the sheet names from the name references, having the sheet
    // names around messes up the formulas
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        nameCache.remove(workbook.getSheetName(i));
    }
    //p("Names: " + nameCache.keySet());

    // loop over all the cells and rewrite the formula ones
    for (int sheetCount = 0; sheetCount < workbook.getNumberOfSheets(); sheetCount++) {
        final HSSFSheet sheet = workbook.getSheetAt(sheetCount);
        for (final Iterator rowIterator = sheet.rowIterator(); rowIterator.hasNext();) {
            final HSSFRow row = (HSSFRow) rowIterator.next();
            for (final Iterator cellIterator = row.cellIterator(); cellIterator.hasNext();) {
                final HSSFCell cell = (HSSFCell) cellIterator.next();
                if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
                    String formula = cell.getCellFormula();
                    for (final String name : nameCache.keySet()) {
                        final Pattern pattern = Pattern.compile("(\\W|^)" + name + "(\\W|$)",
                                Pattern.CASE_INSENSITIVE);
                        final HSSFName hssfName = nameCache.get(name);
                        formula = pattern.matcher(formula)
                                .replaceAll("$1" + hssfName.getReference().replace("$", "\\$") + "$2");
                    }
                    //p("Resetting Cell (" + cell.toString()
                    //      + ") Formula:" + formula);
                    cell.setCellFormula(formula);
                } // end if
            } // end for
        } // end for
    } // end for
}

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.xls.HSSFRangeHelper.java

License:Apache License

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

    HSSFName aNamedRange = workbook.getNameAt(rangeNameIdx);
    String sheetName = aNamedRange.getSheetName();
    return workbook.getSheet(sheetName);
}

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

License:Open Source License

private HSSFName getName(HSSFWorkbook wb, HSSFSheet sheet, String nameString) {
    int i;//from w w w.j a v  a2s  .c  om
    HSSFName name;

    for (i = 0; i < wb.getNumberOfNames(); i++) {
        name = wb.getNameAt(i);
        if (name.getNameName().equals(nameString) && name.getSheetName().equals(sheet.getSheetName()))
            return name;
    }

    return null;
}

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 w w. j a  v  a2s  .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;/*from w w w  .  j a  v a2 s  .  c  om*/
    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;
}