java——POI学习——2

边框（border）

Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("new sheet");

Row row = sheet.createRow(1);

Cell cell = row.createCell(1);
cell.setCellValue(4);

// 设置边框样式
CellStyle style = wb.createCellStyle();
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.GREEN.getIndex());
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLUE.getIndex());
style.setBorderTop(CellStyle.BORDER_MEDIUM_DASHED);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
cell.setCellStyle(style);

// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();

遍历行和单元格
workbook.sheetIterator()
sheet.rowIterator()
row.cellIterator()

Workbook wb = WorkbookFactory.create(new File("workbook.xls"));
for (Sheet sheet : wb) {
    for (Row row : sheet) {
        for (Cell cell : row) {
            System.out.println(cell.getNumericCellValue());
        }
    }
}
wb.close();

遍历单元格，包括空的单元格（Iterate over cells, with control of missing / blank cells）

Workbook wb = WorkbookFactory.create(new File("workbook.xls"));
Sheet sheet = wb.getSheetAt(0);
int rowStart = Math.min(15, sheet.getFirstRowNum());
int rowEnd = Math.max(1400, sheet.getLastRowNum());
for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
    Row r = sheet.getRow(rowNum);
    if (null == r) {
        continue;
    }
    int lastColumn = r.getLastCellNum();
    for (int cn = 0; cn < lastColumn; cn++) {
        Cell c = r.getCell(cn, Row.RETURN_BLANK_AS_NULL);
        if (null == c) {
            System.out.println(rowNum+":"+cn+" the spreadsheet is empty in this cell");
        } else {
            System.out.println(rowNum+":"+cn+"do something userful with the cell`s contents");
        }
    }
}
wb.close();

获取单元格内容

// 获取单元格内容，首先要知道单元格内容是那种类型，才能用对应的get方法获取。
Workbook wb = WorkbookFactory.create(new File("workbook.xls"));
DataFormatter formatter = new DataFormatter();
Sheet sheet = wb.getSheetAt(0);
for (Row row : sheet) {
    for (Cell cell : row) {
        // 获取单元格的位置
        CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
        System.out.print(cellRef.formatAsString());
        System.out.print("-");

        // 单元格内容的字符串格式值
        String text = formatter.formatCellValue(cell);
        System.out.println(text);

        // Alternatively,get the value and format it yourself.
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
            System.out.println(cell.getRichStringCellValue());
            break;

        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                System.out.println(cell.getDateCellValue());
            } else {
                System.out.println(cell.getNumericCellValue());
            }
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            System.out.println(cell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_FORMULA:
            System.out.println(cell.getCellFormula());
            break;
        default:
            System.out.println();
        }
    }
}

Text Extraction

For most text extraction requirements, the standard ExcelExtractor class should provide all you need.
    InputStream inp = new FileInputStream("workbook.xls");
    HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp));
    ExcelExtractor extractor = new ExcelExtractor(wb);

    extractor.setFormulasNotResults(true);
    extractor.setIncludeSheetNames(false);
    String text = extractor.getText();


For very fancy text extraction, XLS to CSV etc, take a look at /src/examples/src/org/apache/poi/hssf/eventusermodel/examples/XLS2CSVmra.java 

填充颜色

Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("new sheet");

// Create a row and put some cells in it. Rows are 0 based.
Row row = sheet.createRow((short) 1);

// 浅绿色 背景色
CellStyle style = wb.createCellStyle();
style.setFillBackgroundColor(IndexedColors.AQUA.getIndex());
style.setFillPattern(CellStyle.BIG_SPOTS);
Cell cell = row.createCell((short) 1);
cell.setCellValue("X");
cell.setCellStyle(style);

// 橙色 前景色
style = wb.createCellStyle();
style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row.createCell((short) 2);
cell.setCellValue("X");
cell.setCellStyle(style);

// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();

合并单元格

Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("new sheet");

Row row = sheet.createRow((short) 1);
Cell cell = row.createCell((short) 1);
cell.setCellValue("This is a test of merging");

sheet.addMergedRegion(new CellRangeAddress(
        1, // first row (0-based)
        1, // last row (0-based)
        1, // first column (0-based)
        2 // last column (0-based)
));

// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();