Java CellRangeAddress.valueOf方法代码示例

import org.apache.poi.ss.util.CellRangeAddress; //导入方法依赖的package包/类
protected void formatCellStatus(Sheet sheet, Cell cell) {
	cell.setCellStyle(styles.get("status"));
	SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
	ConditionalFormattingRule ruleGreen = sheetCF.createConditionalFormattingRule(ComparisonOperator.EQUAL, "1");
	PatternFormatting fill1 = ruleGreen.createPatternFormatting();
	fill1.setFillBackgroundColor(IndexedColors.GREEN.index);
	fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
	//
	ConditionalFormattingRule ruleRed = sheetCF.createConditionalFormattingRule(ComparisonOperator.EQUAL, "0");
	PatternFormatting fill2 = ruleRed.createPatternFormatting();
	fill2.setFillBackgroundColor(IndexedColors.RED.index);
	fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
	//
	ConditionalFormattingRule ruleOrange = sheetCF.createConditionalFormattingRule(ComparisonOperator.EQUAL, "2");
	PatternFormatting fill3 = ruleOrange.createPatternFormatting();
	fill3.setFillBackgroundColor(IndexedColors.ORANGE.index);
	fill3.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
	//
	String name = CellReference.convertNumToColString(cell.getColumnIndex());
	String location = "$" + name + "$" + cell.getRowIndex() + ":$" + name + "$" + (cell.getRowIndex() + 1);

	CellRangeAddress[] regions = { CellRangeAddress.valueOf(location) };
	ConditionalFormattingRule[] cfRules = new ConditionalFormattingRule[] { ruleGreen, ruleRed, ruleOrange };
	sheetCF.addConditionalFormatting(regions, cfRules);
}
 

import org.apache.poi.ss.util.CellRangeAddress; //导入方法依赖的package包/类
protected A1RangeAddress(String a1address) {
    this.addresses = new LinkedList<>();
    
    if (!a1address.contains(RANGE_DELIMITER)) { this.addresses.add(A1Address.fromA1Address(a1address)); return; }
    
    CellRangeAddress addrs = CellRangeAddress.valueOf(a1address);
    int fromR = addrs.getFirstRow();
    int fromC = addrs.getFirstColumn();
    int toR = addrs.getLastRow();
    int toC = addrs.getLastColumn();

    for (int row = fromR; row <= toR; row++) {
        for (int col = fromC; col <= toC; col++) {
            this.addresses.add(A1Address.fromRowColumn(row, col));
        }
    }
}
 

import org.apache.poi.ss.util.CellRangeAddress; //导入方法依赖的package包/类
/**
 * Prepare the Comment Sheet within the Excel document
 * Sets up row titles and formatting.
 */
private void prepareCommentSheet()
{
    this.commentSheet = workbook.createSheet("Comments");

    Row row1 = commentSheet.createRow(0);
    Row row2 = commentSheet.createRow(1);

    Cell cell;

    cell = row2.createCell(COL_CMT_PLANTID);
    cell.setCellValue("#");
    cell.setCellStyle(styleCentered);

    cell = row1.createCell(COL_CMT_COMMONNAME);
    cell.setCellValue("Common");
    cell.setCellStyle(styleCentered);
    cell = row2.createCell(COL_CMT_COMMONNAME);
    cell.setCellValue("Name");
    cell.setCellStyle(styleCentered);

    cell = row2.createCell(COL_CMT_CULTIVAR);
    cell.setCellValue("Cultivar");
    cell.setCellStyle(styleCentered);

    cell = row1.createCell(COL_CMT_GRDNLOC);
    cell.setCellValue("Garden");
    cell.setCellStyle(styleCentered);
    cell = row2.createCell(COL_CMT_GRDNLOC);
    cell.setCellValue("Location");
    cell.setCellStyle(styleCentered);

    cell = row2.createCell(COL_CMT_COMMENT);
    cell.setCellValue("Comment");
    cell.setCellStyle(styleCentered);

    cell = row2.createCell(COL_CMT_DATE);
    cell.setCellValue("Time of Comment");
    cell.setCellStyle(styleCentered);

    commentSheet.setColumnWidth(COL_CMT_PLANTID,1600);
    commentSheet.setColumnWidth(COL_CMT_GRDNLOC,1900);
    commentSheet.setColumnWidth(COL_CMT_COMMONNAME,3200);
    commentSheet.setColumnWidth(COL_CMT_CULTIVAR,4800);
    commentSheet.setColumnWidth(COL_CMT_COMMENT,8400);
    commentSheet.setColumnWidth(COL_CMT_DATE,6800);
    commentSheet.createFreezePane(0, 2);

    //AutoFilter is the thing that makes that Sorting line on the spreadsheet
    //This range defines what columns are used as keys and how many rows to care about.
    CellRangeAddress rangeAddress = CellRangeAddress.valueOf("A2:F1200");
    commentSheet.setAutoFilter(rangeAddress);

    commentRowCount = 2;
}
 

import org.apache.poi.ss.util.CellRangeAddress; //导入方法依赖的package包/类
/**
 * Prepare the Plant Metadata Sheet within the Excel document
 * Sets up row titles and formatting.
 */
private void preparePlantMetadata()
{
    this.metadataSheet = workbook.createSheet("Plant Metadata");

    Row row1 = metadataSheet.createRow(0);
    Row row2 = metadataSheet.createRow(1);

    Cell cell;

    cell = row2.createCell(COL_PLANT_PLANTID);
    cell.setCellValue("#");
    cell.setCellStyle(styleCentered);

    cell = row1.createCell(COL_PLANT_COMMONNAME);
    cell.setCellValue("Common");
    cell.setCellStyle(styleCentered);
    cell = row2.createCell(COL_PLANT_COMMONNAME);
    cell.setCellValue("Name");
    cell.setCellStyle(styleCentered);

    cell = row2.createCell(COL_PLANT_CULTIVAR);
    cell.setCellValue("Cultivar");
    cell.setCellStyle(styleCentered);

    cell = row1.createCell(COL_PLANT_GRDNLOC);
    cell.setCellValue("Garden");
    cell.setCellStyle(styleCentered);
    cell = row2.createCell(COL_PLANT_GRDNLOC);
    cell.setCellValue("Location");
    cell.setCellStyle(styleCentered);

    cell = row2.createCell(COL_PLANT_LIKES);
    cell.setCellValue("Likes");
    cell.setCellStyle(styleCentered);

    cell = row2.createCell(COL_PLANT_DISLIKES);
    cell.setCellValue("Dislikes");
    cell.setCellStyle(styleCentered);

    cell = row2.createCell(COL_PLANT_COMMENTS);
    cell.setCellValue("Comments");
    cell.setCellStyle(styleCentered);

    cell = row1.createCell(COL_PLANT_PAGEVIEWS);
    cell.setCellValue("Page");
    cell.setCellStyle(styleCentered);
    cell = row2.createCell(COL_PLANT_PAGEVIEWS);
    cell.setCellValue("Views");
    cell.setCellStyle(styleCentered);

    metadataSheet.setColumnWidth(COL_PLANT_PLANTID,1600);
    metadataSheet.setColumnWidth(COL_PLANT_GRDNLOC,1900);
    metadataSheet.setColumnWidth(COL_PLANT_COMMONNAME,3200);
    metadataSheet.setColumnWidth(COL_PLANT_CULTIVAR,7400);
    metadataSheet.createFreezePane(0, 2);


    CellRangeAddress rangeAddress = CellRangeAddress.valueOf("A2:H1200");
    metadataSheet.setAutoFilter(rangeAddress);
    metadataRowCount = 2;
}
 

import org.apache.poi.ss.util.CellRangeAddress; //导入方法依赖的package包/类
/**
 * Prepare the Bed Metadata Sheet within the Excel document
 * Sets up row titles and formatting.
 */
private void prepareBedMetadataSheet()
{
    this.bedmetadataSheet = workbook.createSheet("Bed Metadata");

    Row row1 = bedmetadataSheet.createRow(0);
    Row row2 = bedmetadataSheet.createRow(1);

    Cell cell;

    cell = row1.createCell(COL_BED_GRDNLOC);
    cell.setCellValue("Garden");
    cell.setCellStyle(styleCentered);
    cell = row2.createCell(COL_BED_GRDNLOC);
    cell.setCellValue("Location");
    cell.setCellStyle(styleCentered);

    cell = row1.createCell(COL_BED_PAGEVIEWS);
    cell.setCellValue("Page");
    cell.setCellStyle(styleCentered);
    cell = row2.createCell(COL_BED_PAGEVIEWS);
    cell.setCellValue("Views");
    cell.setCellStyle(styleCentered);

    cell = row1.createCell(COL_BED_QRSCANS);
    cell.setCellValue("QR");
    cell.setCellStyle(styleCentered);
    cell = row2.createCell(COL_BED_QRSCANS);
    cell.setCellValue("Scans");
    cell.setCellStyle(styleCentered);


    bedmetadataSheet.setColumnWidth(COL_BED_GRDNLOC,7400);
    bedmetadataSheet.setColumnWidth(COL_BED_PAGEVIEWS,3200);
    bedmetadataSheet.setColumnWidth(COL_BED_QRSCANS,3200);
    bedmetadataSheet.createFreezePane(0, 2);


    CellRangeAddress rangeAddress = CellRangeAddress.valueOf("A2:C1200");
    bedmetadataSheet.setAutoFilter(rangeAddress);
    bedmetadataRowCount = 2;
}
 

import org.apache.poi.ss.util.CellRangeAddress; //导入方法依赖的package包/类
/**
 * @param range either the range of the entire block to be read, or just the
 *              top row of the cells, in which case the method will stop when
 *              the first empty cell is reached in the first column
 * @param columnTypes An array of data types expected at each column.
 *                 If this array is shorter than the number of column, then the last
 *                 data type is used until the end. So if only one value is given,
 *                 then that is used for the entire block.
 */
public Object[][] readBlock(String range, Class... columnTypes) {

    if (columnTypes == null || columnTypes.length == 0) {
        throw new RuntimeException("columnTypes cannot be null / empty");
    }

    CellRangeAddress cra = CellRangeAddress.valueOf(range);
    AreaReference ar = new AreaReference(range);
    Sheet sheet = workbook.getSheet(ar.getFirstCell().getSheetName());

    int firstColumn = cra.getFirstColumn();
    int firstRow = cra.getFirstRow();
    int lastRow = cra.getLastRow();
    int height = lastRow - firstRow + 1;
    int width = cra.getLastColumn() - firstColumn + 1;

    List<Object> result;
    if (height == 1) {
        result = new LinkedList<Object>();
    } else {
        result = new ArrayList<Object>(height);
    }

    for (int rowNum = 0; moreDataToRead(sheet, firstColumn, firstRow, lastRow, rowNum); rowNum++) {
        Row row = sheet.getRow(firstRow + rowNum);
        Object[] resultRow = new Object[width];
        result.add(resultRow);
        for (int colNum = 0; colNum < width; colNum++) {

            Class colType;
            if (colNum < columnTypes.length - 1) {
                colType = columnTypes[colNum];
            } else {
                colType = columnTypes[columnTypes.length - 1];
            }

            Cell cell = row.getCell(firstColumn + colNum);
            resultRow[colNum] = readCell(cell, colType);
        }

    }

    return result.toArray(new Object[][] {});
}