2019-01-08

POI-应用

一.HSSF

1.新建表格

File path = new File(filePath);
path.mkdirs();
File file = new File(filePath, fileName + ".xls");

HSSFWorkbook wb=new HSSFWorkbook();
//新建sheet页
HSSFSheet sheet = wb.createSheet(DateUtil.formatDate(date, "yyyy年MM月dd日"));

FileOutputStream fos = new FileOutputStream(file);
wb.write(fos);
fos.close();

2.设置样式

HSSFCellStyle cellStyle = wb.createCellStyle();  

//设置背景色RGB(253,233,217)
HSSFPalette palette = wb.getCustomPalette();
palette.setColorAtIndex(HSSFColor.ROSE.index, (byte) 253, (byte) 233, (byte) 217);
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyle.setFillForegroundColor(HSSFColor.ROSE.index);
//设置边框
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); 
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

//设置对齐方式
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

DataFormat format = wb.createDataFormat();
cellStyle.setDataFormat(format.getFormat("#,##0.00"));

//设置字体
HSSFFont font = wb.createFont();  
font.setFontName("宋体");
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontHeightInPoints((short) 10);  
cellStyle.setFont(font);

3.合并单元格

//合并单元格，参数1：起始行 参数2：终止行 参数3：起始列 参数 4：终止列
CellRangeAddress region = new CellRangeAddress(0, 0, 0,10); 
sheet.addMergedRegion(region);

4.设置单元格

public static void setHSSFValue(Sheet sheet, int row, int cell, Object value, CellStyle cellStyle) {
    Row rowIn = sheet.getRow(row);
    if (rowIn == null) {
        rowIn = sheet.createRow(row);
    }
    Cell cellIn = rowIn.getCell(cell);
    if (cellIn == null) {
        cellIn = rowIn.createCell(cell);
    }
    if (cellStyle != null) {
        cellIn.setCellStyle(cellStyle);
    }
    // 对时间格式进行单独处理
    if (value == null) {
        cellIn.setCellValue("");
    } else {
        if (isCellDateFormatted(cellStyle)) {
            cellIn.setCellValue((Date) value);
        }if(value instanceof BigDecimal) {
            cellIn.setCellValue(((BigDecimal) value).doubleValue());
        } else {
            cellIn.setCellValue(new HSSFRichTextString(value.toString()));
        }
    }
}
/**
* 设置公式
* @param sheet
* @param row
* @param cell
* @param formula
* @param cellStyle
*/
public static void setHSSFCellFormula(Sheet sheet, int row, int cell, String formula, CellStyle cellStyle) {
    Row rowIn = sheet.getRow(row);
    if (rowIn == null) {
        rowIn = sheet.createRow(row);
    }
    Cell cellIn = rowIn.getCell(cell);
    if (cellIn == null) {
        cellIn = rowIn.createCell(cell);
    }
    if (cellStyle != null) {
        cellIn.setCellStyle(cellStyle);
    }
    cellIn.setCellFormula(formula);
    cellIn.setCellType(Cell.CELL_TYPE_FORMULA);
}


/**
 * 根据表格样式判断是否为日期格式
 * 
 * @param cellStyle
 * @return
 */
public static boolean isCellDateFormatted(CellStyle cellStyle) {
    if (cellStyle == null) {
        return false;
    }
    int i = cellStyle.getDataFormat();
    String f = cellStyle.getDataFormatString();
    return org.apache.poi.ss.usermodel.DateUtil.isADateFormat(i, f);
}

二.XSSF