Example usage for org.apache.poi.hssf.usermodel HSSFSheet getSheetName

List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getSheetName

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFSheet
  6. getSheetName

Introduction

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

Prototype

@SuppressWarnings("resource")
@Override
public String getSheetName()

Source Link

Document

Returns the name of this sheet

Usage

From source file:com.asakusafw.testtools.excel.ExcelUtils.java

License:Apache License

/**
 * ???????//www . j  a  v a  2s .  com
 * @param sheet ?
 * @param row ?
 * @param col ??
 * @return ??
 */
private HSSFCell getCell(HSSFSheet sheet, HSSFRow row, int col) {
    HSSFCell cell = row.getCell(col);
    if (cell == null) {
        String fmt = "Excel???(), file = %s, sheet = %s, row = %d, col = %d";
        String msg = String.format(fmt, filename, sheet.getSheetName(), row.getRowNum() + 1, col + 1);
        throw new InvalidExcelBookException(msg);
    }
    return cell;
}

From source file:com.asakusafw.testtools.excel.ExcelUtils.java

License:Apache License

/**
 * ????????//  w  w  w. j a va2 s  .  c  om
 * @param sheet ?
 * @param rownum ??
 * @param col ??
 * @return ??
 */
private HSSFCell getCell(HSSFSheet sheet, int rownum, int col) {
    HSSFRow row = sheet.getRow(rownum);
    if (isEmpty(row)) {
        String fmt = "Excel???(), file = %s, sheet = %s, row = %d";
        String msg = String.format(fmt, filename, sheet.getSheetName(), rownum);
        throw new InvalidExcelBookException(msg);
    }
    HSSFCell cell = getCell(sheet, row, col);
    return cell;
}

From source file:com.asakusafw.testtools.excel.ExcelUtils.java

License:Apache License

/**
 * ?????????()??/*from w  w w  .  j av  a2  s  .  c  o  m*/
 * @param sheet ?
 * @param item ??
 * @param row ?
 * @return ??
 */
private String getStringCellValue(HSSFSheet sheet, ConditionSheetItem item, HSSFRow row) {
    HSSFCell cell = getCell(item, row);
    String ret;
    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
        double dval = cell.getNumericCellValue();
        ret = Double.toString(dval);
        ret = ret.replaceAll("\\.0*$", "");
    } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
        ret = "";
    } else if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
        String fmt = "Excel???????????? file = %s, sheet = %s, row = %d, col = %d";
        int rownum = row.getRowNum() + 1;
        int col = item.getCol() + 1;
        String msg = String.format(fmt, filename, sheet.getSheetName(), rownum, col);
        throw new InvalidExcelBookException(msg);
    } else {
        ret = cell.getStringCellValue();
    }
    return ret;
}

From source file:com.asakusafw.testtools.excel.ExcelUtils.java

License:Apache License

private Double getDubleCellValue(HSSFSheet sheet, ConditionSheetItem item, HSSFRow row) {
    HSSFCell cell = getCell(item, row);/*from   ww  w .j  av a  2 s. com*/
    Double ret;
    if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
        String str = cell.getStringCellValue();
        if (str == null || str.length() == 0) {
            ret = null;
        } else {
            try {
                ret = Double.parseDouble(str);
            } catch (NumberFormatException e) {
                String fmt = "Excel???????????? file = %s, sheet = %s, row = %d, col = %d";
                int rownum = row.getRowNum() + 1;
                int col = item.getCol() + 1;
                String msg = String.format(fmt, filename, sheet.getSheetName(), rownum, col);
                throw new InvalidExcelBookException(msg);
            }
        }
    } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
        ret = null;
    } else if (cell.getCellType() != Cell.CELL_TYPE_NUMERIC) {
        String fmt = "Excel???????????? file = %s, sheet = %s, row = %d, col = %d";
        int rownum = row.getRowNum() + 1;
        int col = item.getCol() + 1;
        String msg = String.format(fmt, filename, sheet.getSheetName(), rownum, col);
        throw new InvalidExcelBookException(msg);
    } else {
        ret = cell.getNumericCellValue();
    }
    return ret;
}

From source file:com.elbeesee.poink.transreptor.HSSFSheetToXML.java

License:Open Source License

public void onTransrept(INKFRequestContext aContext) throws Exception {
    IHSSFSheetRepresentation aIHSSFSheetRepresentation = (IHSSFSheetRepresentation) aContext
            .sourcePrimary(IHSSFSheetRepresentation.class);
    HSSFSheet vSheet = aIHSSFSheetRepresentation.getSheetReadOnly();
    String vSheetName = vSheet.getSheetName();

    StringBuilder vSheetXML = new StringBuilder();

    vSheetXML.append("<sheet sheetName=\"");
    vSheetXML.append(XMLUtils.escape(vSheetName));
    vSheetXML.append("\" sheetIndex=\"");
    vSheetXML.append(vSheet.getWorkbook().getSheetIndex(vSheetName));
    vSheetXML.append("\" numRows=\"");
    vSheetXML.append(vSheet.getPhysicalNumberOfRows());
    vSheetXML.append("\">");

    // do the rows
    int i = 0;//w  w w .j ava 2  s  .  co  m
    for (Iterator<Row> vRowIterator = vSheet.rowIterator(); vRowIterator.hasNext();) {
        HSSFRow vHSSFRow = (HSSFRow) vRowIterator.next();
        IHSSFRowRepresentation vHSSFRowRepresentation = new HSSFRowImplementation(vHSSFRow);
        String vRowXML = aContext.transrept(vHSSFRowRepresentation, String.class);
        vSheetXML.append(vRowXML);
        i = i + 1;
    }
    //

    vSheetXML.append("</sheet>");

    INKFResponse vResponse = aContext.createResponseFrom(vSheetXML.toString());
    vResponse.setExpiry(INKFResponse.EXPIRY_DEPENDENT);
}

From source file:com.hack23.cia.service.external.esv.impl.EsvApiImpl.java

License:Apache License

/**
 * Adds the ministry per year to map.//from   w  w  w . j av  a 2s. c  o  m
 *
 * @param name
 *            the name
 * @param map
 *            the map
 * @param mySheet
 *            the my sheet
 */
private static void addMinistryPerYearToMap(final String name,
        final Map<Integer, List<GovernmentBodyAnnualSummary>> map, final HSSFSheet mySheet) {
    if (mySheet.getSheetName().chars().allMatch(Character::isDigit)) {

        final int year = Integer.valueOf(mySheet.getSheetName());

        final List<GovernmentBodyAnnualSummary> yearList = new ArrayList<>();
        final Iterator<Row> rowIterator = mySheet.iterator();

        rowIterator.next();

        while (rowIterator.hasNext()) {
            final Row row = rowIterator.next();
            final short maxColIx = row.getLastCellNum();

            if (maxColIx == 10) {
                final GovernmentBodyAnnualSummary governmentBodyAnnualSummary = new GovernmentBodyAnnualSummary(
                        year, row.getCell(0).toString(), getInteger(row.getCell(1).toString()),
                        row.getCell(2).toString(), row.getCell(3).toString(), row.getCell(4).toString(),
                        row.getCell(5).toString(), getInteger(row.getCell(6).toString()),
                        getInteger(row.getCell(7).toString()), row.getCell(8).toString(),
                        row.getCell(9).toString());
                row.getCell(9).toString();

                if (name == null || name.equalsIgnoreCase(governmentBodyAnnualSummary.getMinistry())) {
                    yearList.add(governmentBodyAnnualSummary);
                }

            }

        }
        map.put(year, yearList);
    }
}

From source file:com.hack23.cia.service.external.esv.impl.EsvApiImpl.java

License:Apache License

/**
 * Adds the data for year to map./*from w w  w  . ja v  a 2  s .c  o  m*/
 *
 * @param name
 *            the name
 * @param map
 *            the map
 * @param mySheet
 *            the my sheet
 */
private static void addDataForYearToMap(String name, final Map<Integer, GovernmentBodyAnnualSummary> map,
        final HSSFSheet mySheet) {
    if (mySheet.getSheetName().chars().allMatch(Character::isDigit)) {

        final int year = Integer.valueOf(mySheet.getSheetName());

        final Iterator<Row> rowIterator = mySheet.iterator();

        rowIterator.next();

        while (rowIterator.hasNext()) {
            final Row row = rowIterator.next();
            final short maxColIx = row.getLastCellNum();

            if (maxColIx == 10) {
                final GovernmentBodyAnnualSummary governmentBodyAnnualSummary = new GovernmentBodyAnnualSummary(
                        year, row.getCell(0).toString(), getInteger(row.getCell(1).toString()),
                        row.getCell(2).toString(), row.getCell(3).toString(), row.getCell(4).toString(),
                        row.getCell(5).toString(), getInteger(row.getCell(6).toString()),
                        getInteger(row.getCell(7).toString()), row.getCell(8).toString(),
                        row.getCell(9).toString());
                row.getCell(9).toString();

                if (name == null || name.equalsIgnoreCase(governmentBodyAnnualSummary.getName())) {
                    map.put(year, governmentBodyAnnualSummary);
                }
            }
        }
    }
}

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

License:Apache License

/**
 * Method writes horizontal band//from   ww  w  .j a v  a  2  s .c  om
 * Note: Only one band for row is supported. Now we think that many bands for row aren't usable.
 *
 * @param band          - band to write
 * @param templateSheet - template sheet
 * @param resultSheet   - result sheet
 */
protected void writeHorizontalBand(BandData band, HSSFSheet templateSheet, HSSFSheet resultSheet) {
    String rangeName = band.getName();
    AreaReference templateRange = getAreaForRange(templateWorkbook, rangeName);
    if (templateRange == null) {
        throw wrapWithReportingException(String.format("No such named range in xls file: %s", rangeName));
    }
    CellReference[] crefs = templateRange.getAllReferencedCells();

    CellReference topLeft, bottomRight;
    AreaReference resultRange;

    int rowsAddedByHorizontalBandBackup = rowsAddedByHorizontalBand;
    int rownumBackup = rownum;

    if (crefs != null) {
        addRangeBounds(band, crefs);

        ArrayList<HSSFRow> resultRows = new ArrayList<HSSFRow>();

        int currentRowNum = -1;
        int currentRowCount = -1;
        int currentColumnCount = 0;
        int offset = 0;

        topLeft = new CellReference(rownum + rowsAddedByHorizontalBand, 0);
        // no child bands - merge regions now
        if (band.getChildrenList().isEmpty()) {
            copyMergeRegions(resultSheet, rangeName, rownum + rowsAddedByHorizontalBand,
                    getCellFromReference(crefs[0], templateSheet).getColumnIndex());
        }

        for (CellReference cellRef : crefs) {
            HSSFCell templateCell = getCellFromReference(cellRef, templateSheet);
            HSSFRow resultRow;
            if (templateCell.getRowIndex() != currentRowNum) { //create new row
                resultRow = resultSheet.createRow(rownum + rowsAddedByHorizontalBand);
                copyPageBreaks(templateSheet, resultSheet, templateCell.getRowIndex(), resultRow.getRowNum());
                rowsAddedByHorizontalBand += 1;

                //todo move to options
                if (templateCell.getCellStyle().getParentStyle() != null
                        && templateCell.getCellStyle().getParentStyle().getUserStyleName() != null
                        && templateCell.getCellStyle().getParentStyle().getUserStyleName()
                                .equals(DYNAMIC_HEIGHT_STYLE)) {
                    //resultRow.setHeight(templateCell.getRow().getHeight());
                } else {
                    resultRow.setHeight(templateCell.getRow().getHeight());
                }
                resultRows.add(resultRow);

                currentRowNum = templateCell.getRowIndex();
                currentRowCount++;
                currentColumnCount = 0;
                offset = templateCell.getColumnIndex();
            } else { // or write cell to current row
                resultRow = resultRows.get(currentRowCount);
                currentColumnCount++;
            }

            copyCellFromTemplate(templateCell, resultRow, offset + currentColumnCount, band);
        }

        bottomRight = new CellReference(rownum + rowsAddedByHorizontalBand - 1, offset + currentColumnCount);
        resultRange = new AreaReference(topLeft, bottomRight);

        areaDependencyManager.addDependency(new Area(band.getName(), Area.AreaAlign.HORIZONTAL, templateRange),
                new Area(band.getName(), Area.AreaAlign.HORIZONTAL, resultRange));
        bandsToResultRanges.put(band,
                new Range(resultSheet.getSheetName(), resultRange.getFirstCell().getCol() + 1,
                        resultRange.getFirstCell().getRow() + 1, resultRange.getLastCell().getCol() + 1,
                        resultRange.getLastCell().getRow() + 1));
    }

    for (BandData child : band.getChildrenList()) {
        writeBand(child);
    }

    // scheduled merge regions
    if (!band.getChildrenList().isEmpty() && crefs != null) {
        copyMergeRegions(resultSheet, rangeName, rownumBackup + rowsAddedByHorizontalBandBackup,
                getCellFromReference(crefs[0], templateSheet).getColumnIndex());
    }

    rownum += rowsAddedByHorizontalBand;
    rowsAddedByHorizontalBand = 0;
    rownum += rowsAddedByVerticalBand;
    rowsAddedByVerticalBand = 0;
}

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

License:Apache License

/**
 * Method writes vertical band//from w  ww . j a v  a 2s .  c  o m
 * Note: no child support for vertical band ;)
 *
 * @param band          - band to write
 * @param templateSheet - template sheet
 * @param resultSheet   - result sheet
 */
protected void writeVerticalBand(BandData band, HSSFSheet templateSheet, HSSFSheet resultSheet) {
    String rangeName = band.getName();
    CellReference[] crefs = getRangeContent(templateWorkbook, rangeName);

    Set<Integer> addedRowNumbers = new HashSet<Integer>();

    if (crefs != null) {
        addRangeBounds(band, crefs);

        Bounds thisBounds = templateBounds.get(band.getName());
        Bounds parentBounds = templateBounds.get(band.getParentBand().getName());
        Range parentRange = bandsToResultRanges.get(band.getParentBand());

        int localRowNum = parentBounds != null && parentRange != null
                ? parentRange.getFirstRow() - 1 + thisBounds.row0 - parentBounds.row0
                : rownum;

        colnum = colnum == 0 ? getCellFromReference(crefs[0], templateSheet).getColumnIndex() : colnum;
        copyMergeRegions(resultSheet, rangeName, localRowNum, colnum);

        int firstRow = crefs[0].getRow();
        int firstColumn = crefs[0].getCol();

        for (CellReference cref : crefs) {//create necessary rows
            int currentRow = cref.getRow();
            final int rowOffset = currentRow - firstRow;
            if (!rowExists(resultSheet, localRowNum + rowOffset)) {
                HSSFRow resultRow = resultSheet.createRow(localRowNum + rowOffset);
                copyPageBreaks(templateSheet, resultSheet, cref.getRow(), resultRow.getRowNum());
            }
            addedRowNumbers.add(cref.getRow());
        }

        CellReference topLeft = null;
        CellReference bottomRight = null;
        for (CellReference cref : crefs) {
            int currentRow = cref.getRow();
            int currentColumn = cref.getCol();
            final int rowOffset = currentRow - firstRow;
            final int columnOffset = currentColumn - firstColumn;

            HSSFCell templateCell = getCellFromReference(cref, templateSheet);
            resultSheet.setColumnWidth(colnum + columnOffset,
                    templateSheet.getColumnWidth(templateCell.getColumnIndex()));
            HSSFCell resultCell = copyCellFromTemplate(templateCell,
                    resultSheet.getRow(localRowNum + rowOffset), colnum + columnOffset, band);
            if (topLeft == null) {
                topLeft = new CellReference(resultCell);
            }
            bottomRight = new CellReference(resultCell);
        }

        colnum += crefs[crefs.length - 1].getCol() - firstColumn + 1;

        AreaReference templateRange = getAreaForRange(templateWorkbook, rangeName);
        AreaReference resultRange = new AreaReference(topLeft, bottomRight);
        areaDependencyManager.addDependency(new Area(band.getName(), Area.AreaAlign.VERTICAL, templateRange),
                new Area(band.getName(), Area.AreaAlign.VERTICAL, resultRange));
        bandsToResultRanges.put(band,
                new Range(resultSheet.getSheetName(), resultRange.getFirstCell().getCol() + 1,
                        resultRange.getFirstCell().getRow() + 1, resultRange.getLastCell().getCol() + 1,
                        resultRange.getLastCell().getRow() + 1));
    }

    //for first level vertical bands we should increase rownum by number of rows added by vertical band
    //nested vertical bands do not add rows, they use parent space
    if (BandData.ROOT_BAND_NAME.equals(band.getParentBand().getName())) {
        List<BandData> sameBands = band.getParentBand().getChildrenByName(band.getName());
        if (sameBands.size() > 0 && sameBands.get(sameBands.size() - 1) == band) {//check if this vertical band is last vertical band with same name
            rownum += addedRowNumbers.size();
            //      rowsAddedByVerticalBand = 0;
        }
    }
}

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

License:Apache License

/**
 * Method creates mapping [rangeName : List&lt;CellRangeAddress&gt;].
 * 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  w ww  .ja v  a  2s.  co  m*/
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);
                    }
                }
            }
        }
    }
}