POI操作Excel

1、创建新Excel

    Workbook wb = new HSSFWorkbook();
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();

    Workbook wb = new XSSFWorkbook();
    FileOutputStream fileOut = new FileOutputStream("workbook.xlsx");
    wb.write(fileOut);
    fileOut.close();

2、读取新的Excel，并更改Excel内容

    InputStream inp = new FileInputStream("workbook.xls");
    //InputStream inp = new FileInputStream("workbook.xlsx");

    Workbook wb = WorkbookFactory.create(inp);
    Sheet sheet = wb.getSheetAt(0);
    Row row = sheet.getRow(2);
    Cell cell = row.getCell(3);
    if (cell == null)
        cell = row.createCell(3);
    cell.setCellType(CellType.STRING);
    cell.setCellValue("a test");

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

3、更改某个Sheet的名字

Workbook wb = WorkbookFactory.create(inp);
wb.setSheetName(sheet的索引, "名字");

4、得到该Sheet

Sheet sheet = wb.getSheetAt(0);

5、cell位置从（0,0）开始的

// 第一个sheet的第6行，第2个单元格放入值’1’
Sheet sheet = wb.getSheetAt(0);
Row row = sheet.createRow((short)5);
// Create a cell and put a value in it.
row.createCell(1).setCellValue("1");

6、下拉列表

String[] datas = [“-“,”10”,”20”];
DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(datas);
CellRangeAddressList addressList1 = new CellRangeAddressList(No, No, 4, 4);
HSSFDataValidation validation1 = null;
validation1 = new HSSFDataValidation(addressList1, dvConstraint);
validation1.setShowErrorBox(false);// 取消弹出错误框
sheet.addValidationData(validation1);

7、替换 车辆设备信息一览(DF11-0123) —》车辆设备信息一览(123)

String biaoTi = cell.getStringCellValue().replaceAll("\\(.*?\\)|\\{.*?}|\\[.*?]|（.*?）","(123)");

8、单元格样式

public static void setCellStyle(Workbook wb,Cell cell){
        Font font = wb.createFont();
        font.setFontHeightInPoints((short)10);
        font.setFontName("微软雅黑");
        // 创建样式
        CellStyle style = wb.createCellStyle();
        // 设置背景色
        style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 设置边框:
        style.setBorderBottom(BorderStyle.THIN); //下边框
        style.setBorderLeft(BorderStyle.THIN);//左边框
        style.setBorderTop(BorderStyle.THIN);//上边框
        style.setBorderRight(BorderStyle.THIN);//右边框
        // 设置居中:
        style.setAlignment(HorizontalAlignment.LEFT); // 水平靠左
        style.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中

        style.setFont(font);
        cell.setCellStyle(style);
    }

9、日期正则表达式

// yyyy-mm 2014-09
String regEx = "^(^(\\d{4}|\\d{2})(\\-|\\/|\\.)\\d{1,2}$)";

10、复制sheet

// 将index为0 的sheet复制给 sheet
Sheet sheet = wb.cloneSheet(0);

11、隐藏行，列

sheet.setColumnHidden(0,true);//隐藏列
row.setZeroHeight(true);//隐藏行
12、移动sheet，设置默认焦点
workbook.setSheetOrder("Setting",1);
workbook.setActiveSheet(0);