Example usage for org.apache.poi.ss.usermodel Row getSheet

List of usage examples for org.apache.poi.ss.usermodel Row getSheet

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. getSheet

Introduction

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

Prototype

Sheet getSheet();

Source Link

Document

Returns the Sheet this row belongs to

Usage

From source file:com.dawg6.web.dhcalc.server.ExportExcel.java

License:Open Source License

@SuppressWarnings("unused")
private Cell addTableCell(Row row, int col, Boolean value) {
    Cell cell = row.createCell(col);//from  w w w  .  j a v  a 2s. co  m
    cell.setCellValue(String.valueOf(value));
    CellUtil.setAlignment(cell, row.getSheet().getWorkbook(), CellStyle.ALIGN_CENTER);

    return cell;
}

From source file:com.dawg6.web.dhcalc.server.ExportExcel.java

License:Open Source License

private Cell addTableCell(Row row, int col, int value) {
    Cell cell = row.createCell(col);//from  ww w. ja v a  2s  . c o  m
    cell.setCellValue(String.valueOf(value));
    cell.setCellStyle(intStyle);
    CellUtil.setAlignment(cell, row.getSheet().getWorkbook(), CellStyle.ALIGN_RIGHT);

    return cell;
}

From source file:com.dawg6.web.dhcalc.server.ExportExcel.java

License:Open Source License

private Cell addTableCell(Row row, int col, double value, HSSFCellStyle style) {
    Cell cell = row.createCell(col);/*from w  w w  .j  a  v  a  2  s.c  om*/
    cell.setCellValue(value);
    cell.setCellStyle(style);
    CellUtil.setAlignment(cell, row.getSheet().getWorkbook(), CellStyle.ALIGN_RIGHT);

    return cell;
}

From source file:com.hust.zsuper.DealWithPatent.ExcelToMySQL.java

License:Open Source License

private String createInsert(final String tableName, final List<Entry<String, ExcelType>> types, final Row row) {
    //Iterate/*from   w w w  . j a  v  a2  s.  c o  m*/
    final FormulaEvaluator evaluator = row.getSheet().getWorkbook().getCreationHelper()
            .createFormulaEvaluator();
    final Map<String, String> colVals = new HashMap<String, String>();

    int columnCount = 0;
    for (Entry<String, ExcelType> sourceType : types) {
        if (isSet(sourceType)) {
            Cell cell = row.getCell(columnCount);
            if (cell != null) {
                cell = evaluator.evaluateInCell(cell);
                try {
                    final String value;
                    switch (sourceType.getValue()) {
                    case DATE:
                        value = "'" + new SimpleDateFormat("yyyy-MM-dd HH:mm").format(getCellValue(cell)) + "'";
                        colVals.put(sourceType.getKey(), value);
                        break;
                    case NUMERIC:
                        value = String.valueOf(getCellValue(cell));
                        colVals.put(sourceType.getKey(), value);
                        break;
                    case BOOLEAN:
                        value = String.valueOf(cell.getBooleanCellValue());
                        colVals.put(sourceType.getKey(), value);
                        break;
                    case STRING:
                        value = String.valueOf(getCellValue(cell)).replaceAll("'", "\\\\'");
                        if (!value.isEmpty()) {
                            colVals.put(sourceType.getKey(), "'" + value + "'");
                        }
                        break;
                    }
                } catch (Exception ex) {
                    if (strict) {
                        throw new RuntimeException("Failed to process cell value: " + getCellValue(cell)
                                + ", of column:row " + columnCount + ":" + row.getRowNum()
                                + ", expecting type: " + sourceType.getValue().toString(), ex);
                    }
                }
            }
        }
        columnCount++;
    }
    return createInsertStatement(colVals, tableName);
}

From source file:com.hust.zsuper.DealWithPatent.WorkhseetToMySQL.java

License:Open Source License

private String createInsertStatement(final Row row) {
    //Iterate/*from   w  w  w . jav  a2 s  . c o m*/
    final StringBuilder columns = new StringBuilder();
    final StringBuilder values = new StringBuilder();
    final FormulaEvaluator evaluator = row.getSheet().getWorkbook().getCreationHelper()
            .createFormulaEvaluator();

    int nullCount = 0;
    int columnCount = 0;
    for (Entry<String, ExcelType> sourceType : types) {
        if (isSet(sourceType)) {
            columns.append("`").append(sourceType.getKey()).append("`").append(",");
            Cell cell = row.getCell(columnCount);

            if (cell == null) {
                values.append("null").append(",");
            } else {
                cell = evaluator.evaluateInCell(cell);

                final String stringValue = getStringValue(sourceType.getValue(), cell);
                if (stringValue == null) {
                    nullCount++;
                }
                values.append(stringValue).append(",");
            }

        }
        columnCount++;
    }
    columns.deleteCharAt(columns.length() - 1);
    values.deleteCharAt(values.length() - 1);

    if (nullCount >= columnCount) {
        return null;
    }
    return "INSERT INTO `" + tableName + "` (" + columns + ") VALUES (" + values + ");";
}

From source file:com.jkoolcloud.tnt4j.streams.parsers.ActivityExcelRowParser.java

License:Apache License

/**
 * Gets field raw data value resolved by locator.
 *
 * @param locator//from ww  w.  j av a  2  s.  c om
 *            activity field locator
 * @param cData
 *            MS Excel document row representing activity object data fields
 * @param formattingNeeded
 *            flag to set if value formatting is not needed
 * @return raw value resolved by locator, or {@code null} if value is not resolved
 *
 * @throws ParseException
 *             if exception occurs while resolving raw data value
 */
@Override
protected Object resolveLocatorValue(ActivityFieldLocator locator, ActivityContext cData,
        AtomicBoolean formattingNeeded) throws ParseException {
    Object val = null;
    String locStr = locator.getLocator();
    Row row = cData.getData();

    if (StringUtils.isNotEmpty(locStr)) {
        int cellIndex = CellReference.convertColStringToIndex(locStr);
        if (cellIndex < 0) {
            throw new ParseException(
                    StreamsResources.getStringFormatted(MsOfficeStreamConstants.RESOURCE_BUNDLE_NAME,
                            "ActivityExcelRowParser.unresolved.cell.reference", locStr),
                    row.getRowNum());
        }
        Cell cell = row.getCell(cellIndex);
        boolean cellFound = false;
        if (cell != null) {
            val = getCellValue(cell);
            cellFound = true;
        }

        logger().log(OpLevel.TRACE,
                StreamsResources.getString(MsOfficeStreamConstants.RESOURCE_BUNDLE_NAME,
                        "ActivityExcelRowParser.resolved.cell.value"),
                locStr, row.getSheet().getWorkbook().getMissingCellPolicy(), toString(val));
    }

    return val;
}

From source file:com.qihang.winter.poi.excel.export.base.ExcelExportBase.java

License:Apache License

/**
 * Cell/*from www.ja  va  2 s  .c o  m*/
 * 
 * @param patriarch
 * @param entity
 * @param row
 * @param i
 * @param imagePath
 * @param obj
 * @throws Exception
 */
public void createImageCell(Drawing patriarch,
        com.qihang.winter.poi.excel.entity.params.ExcelExportEntity entity, Row row, int i, String imagePath,
        Object obj) throws Exception {
    row.setHeight((short) (50 * entity.getHeight()));
    row.createCell(i);
    ClientAnchor anchor;
    if (type.equals(com.qihang.winter.poi.excel.entity.enmus.ExcelType.HSSF)) {
        anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) i, row.getRowNum(), (short) (i + 1),
                row.getRowNum() + 1);
    } else {
        anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) i, row.getRowNum(), (short) (i + 1),
                row.getRowNum() + 1);
    }

    if (StringUtils.isEmpty(imagePath)) {
        return;
    }
    if (entity.getExportImageType() == 1) {
        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
        BufferedImage bufferImg;
        try {
            String path = PoiPublicUtil.getWebRootPath(imagePath);
            path = path.replace("WEB-INF/classes/", "");
            path = path.replace("file:/", "");
            bufferImg = ImageIO.read(new File(path));
            ImageIO.write(bufferImg, imagePath.substring(imagePath.indexOf(".") + 1, imagePath.length()),
                    byteArrayOut);
            byte[] value = byteArrayOut.toByteArray();
            patriarch.createPicture(anchor,
                    row.getSheet().getWorkbook().addPicture(value, getImageType(value)));
        } catch (IOException e) {
            LOGGER.error(e.getMessage(), e);
        }
    } else {
        byte[] value = (byte[]) (entity.getMethods() != null ? getFieldBySomeMethod(entity.getMethods(), obj)
                : entity.getMethod().invoke(obj, new Object[] {}));
        if (value != null) {
            patriarch.createPicture(anchor,
                    row.getSheet().getWorkbook().addPicture(value, getImageType(value)));
        }
    }

}

From source file:com.runwaysdk.dataaccess.io.excel.ErrorSheet.java

License:Open Source License

public void addRow(Row _row) {
    Row row = this.errorSheet.createRow(count++);
    row.setZeroHeight(_row.getZeroHeight());
    row.setHeight(_row.getHeight());//from  w w  w.  j av a2  s. com

    CellStyle style = _row.getRowStyle();

    if (style != null) {
        Workbook workbook = row.getSheet().getWorkbook();

        CellStyle clone = workbook.createCellStyle();
        clone.cloneStyleFrom(style);

        row.setRowStyle(clone);
    }

    Iterator<Cell> cellIterator = _row.cellIterator();
    while (cellIterator.hasNext()) {
        Cell oldCell = cellIterator.next();
        Cell newCell = row.createCell(oldCell.getColumnIndex());

        int cellType = oldCell.getCellType();

        if (cellType == Cell.CELL_TYPE_FORMULA) {
            cellType = oldCell.getCachedFormulaResultType();
        }

        switch (cellType) {
        case Cell.CELL_TYPE_BOOLEAN:
            newCell.setCellValue(oldCell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            newCell.setCellValue(oldCell.getRichStringCellValue());
            break;
        }
    }
}

From source file:com.streamsets.pipeline.lib.parser.excel.Offsets.java

License:Apache License

public static String offsetOf(Row row) {
    String sheetName = row.getSheet().getSheetName();
    int rowNum = row.getRowNum();
    return String.format("%s::%d", sheetName, rowNum);
}

From source file:com.streamsets.pipeline.lib.parser.excel.WorkbookParser.java

License:Apache License

public WorkbookParser(WorkbookParserSettings settings, Context context, Workbook workbook, String offsetId)
        throws DataParserException {
    this.settings = requireNonNull(settings);
    this.context = requireNonNull(context);
    this.workbook = requireNonNull(workbook);
    this.rowIterator = iterate(this.workbook);
    this.offset = requireNonNull(offsetId);
    this.evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    this.currentSheet = null; // default to blank.   Used to figure out when sheet changes and get new field names from header row

    if (!rowIterator.hasNext()) {
        throw new DataParserException(Errors.EXCEL_PARSER_04);
    }//from ww  w .  java2 s . c  om

    headers = new HashMap<>();

    // If Headers are expected, go through and get them from each sheet
    if (settings.getHeader() == ExcelHeader.WITH_HEADER) {
        Sheet sheet;
        String sheetName;
        Row hdrRow;
        for (int s = 0; s < workbook.getNumberOfSheets(); s++) {
            sheet = workbook.getSheetAt(s);
            sheetName = sheet.getSheetName();
            hdrRow = sheet.rowIterator().next();
            List<Field> sheetHeaders = new ArrayList<>();
            // if the table happens to have blank columns in front of it, loop through and artificially add those as headers
            // This helps in the matching of headers to data later as the indexes will line up properly.
            for (int columnNum = 0; columnNum < hdrRow.getFirstCellNum(); columnNum++) {
                sheetHeaders.add(Field.create(""));
            }
            for (int columnNum = hdrRow.getFirstCellNum(); columnNum < hdrRow.getLastCellNum(); columnNum++) {
                Cell cell = hdrRow.getCell(columnNum);
                try {
                    sheetHeaders.add(Cells.parseCell(cell, this.evaluator));
                } catch (ExcelUnsupportedCellTypeException e) {
                    throw new DataParserException(Errors.EXCEL_PARSER_05, cell.getCellTypeEnum());
                }
            }
            headers.put(sheetName, sheetHeaders);
        }
    }

    Offsets.parse(offsetId).ifPresent(offset -> {
        String startSheetName = offset.getSheetName();
        int startRowNum = offset.getRowNum();

        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            int rowNum = row.getRowNum();
            String sheetName = row.getSheet().getSheetName();
            // if a sheet has blank rows at the top then the starting row number may be higher than a default offset of zero or one, thus the >= compare
            if (startSheetName.equals(sheetName) && rowNum >= startRowNum) {
                if (rowIterator.hasPrevious()) {
                    row = rowIterator.previous();
                    this.currentSheet = row.getRowNum() == row.getSheet().getFirstRowNum() ? null
                            : row.getSheet().getSheetName(); // used in comparison later to see if we've moved to new sheet
                } else {
                    this.currentSheet = null;
                }
                break;
            }
        }
    });
}