POI excel处理

1、excel标题样式

 /**
       * @desc 标题样式：边框 + 字体上下左右居中 + 背景颜色
       * @param workbook
       * @param color 前景颜色
       * @return
       */
      public static CellStyle getCenterTitleStyle(Workbook workbook,short color) {
          CellStyle style = workbook.createCellStyle();
          Font font = workbook.createFont();
          font.setFontHeightInPoints((short) 9);
          style.setVerticalAlignment(VerticalAlignment.CENTER);
          style.setAlignment(HorizontalAlignment.CENTER);
          style.setFont(font);
          style.setBorderBottom(BorderStyle.THIN);
          style.setBorderLeft(BorderStyle.THIN);
          style.setBorderRight(BorderStyle.THIN);
          style.setBorderTop(BorderStyle.THIN);
          //style.setFillForegroundColor(IndexedColors.RED.getIndex());
          style.setFillForegroundColor(IndexedColors.RED.getIndex());
          style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
          style.setWrapText(true);
          return style;
      }

 2、excel表头处理。使用枚举类型构造表头。当增加一列或删除一列维护枚举类就可以。

   2.1、简单一级表头。

  

/**
 * excel表头构造类型
 *  key：查询数据库返回key如果匹配不是需要程序做对应映射，value： 表头名称。
 *  例如： select name as goodsName,quantity as quantity,price as price,total as amount from table
 */
public enum  ExcelHeader {

    //序号
    SN("sn","序号"),

    //商品名称
    GOODSNAME("goodsName","名称"),

    //数量
    QUANTITY("quantity","数量"),

    //单价
    PRICE("price","单价"),

    //金额
    AMOUNT("amount","金额");

    private String key;

    private String value;

    public String getKey() {
        return key;
    }

    public void setKey(String key) {
        this.key = key;
    }

    public String getValue() {
        return value;
    }

    public void setValue(String value) {
        this.value = value;
    }

    ExcelHeader(String key,String value){
        this.key = key;
        this.value = value;
    }
}

/*
* excel工具类
*/
class ExcleUtil{

    @Autowired
    ShoppingMapper shoppingMapper;

   /**
    *  导出用户清单
    * @param userId
    * @return
    */
   public String exportShoppingListByUserId(String userId){
       Workbook workbook = new HSSFWorkbook();

       Sheet sheet = workbook.createSheet();

       //表头
       ExcelHeader[] headers = ExcelHeader.values();


       //导出表头begin...........

       //创建表头行
       Row headerRow = sheet.createRow(0);

       for(int i = 0;i<headers.length;i++){
           ExcelHeader header = headers[0];
           String titel = header.getValue();
           Cell cell = headerRow.createCell(i);
           cell.setCellValue(titel);
       }


       //导出表头end...........


       //导出数据begin...........

       //从数据库获取购物清单
       List<Map<String,String>> dataList = shoppingMapper.getShoppingList(userId);

       for(Map<String,String> map : dataList){
           //写数据从几行开始
           int startRow = 1;

           //创建行
           Row row = sheet.createRow(startRow++);

           for(int i = 0;i<headers.length;i++){
               ExcelHeader header = headers[0];
               String dataKey = header.getKey();
               //通过key获取数据值
               String dateValue = map.get(dataKey);
               //创建列
               Cell cell = row.createCell(i);
               //给excel列赋值
               cell.setCellValue(dateValue);
           }
       }

       //导出数据end...........
       
       //todo:导出excel

       return "";
   }

 2.1、二级表头或者多级表头。需要构造更复杂的结构。下面使用二级表头做演示。下面仅仅给出表头构造结构。

 

package com.example.demo;

/**
 *  excel表头构造类型。一级表头
 *  key：唯一键，value： 表头名称，children：下级表头
 */
public enum  ExcelHeaderParent {

    BASE("SN","序号",new ExcelHeader[]{ExcelHeader.SN}),

    //第一季度:下级有4列
    FIRSTQUARTER("firstQuarter","第一季度",new ExcelHeader[]{ExcelHeader.GOODSNAME,
            ExcelHeader.QUANTITY,ExcelHeader.PRICE,ExcelHeader.AMOUNT}),

    //第二季度:下级有4列
    SECONDQUARTER("secondQuarter","第二季度",new ExcelHeader[]{ExcelHeader.GOODSNAMEOFSECOND,
            ExcelHeader.QUANTITYOFSECOND,ExcelHeader.PRICEOFSECOND,ExcelHeader.PRICEOFSECOND,ExcelHeader.AMOUNTOFSECOND})

    //结束符号
    ;

    private String key;

    private String value;

    private ExcelHeader[] children;

    public ExcelHeader[] getChildren() {
        return children;
    }

    public void setChildren(ExcelHeader[] children) {
        this.children = children;
    }

    public String getKey() {
        return key;
    }

    public void setKey(String key) {
        this.key = key;
    }

    public String getValue() {
        return value;
    }

    public void setValue(String value) {
        this.value = value;
    }

    ExcelHeaderParent(String key,String value,ExcelHeader[] children){
        this.key = key;
        this.value = value;
    }
}

package com.example.demo;

/**
 * excel表头构造类型：二级表头 
 *  key：查询数据库返回key如果匹配不是需要程序做对应映射，value： 表头名称。
 *  例如： select name as goodsName,quantity as quantity,price as price,total as amount from table
 */
public enum  ExcelHeader {

    //序号
    SN("sn","序号"),

    //商品名称
    GOODSNAME("goodsName","名称"),

    //数量
    QUANTITY("quantity","数量"),

    //单价
    PRICE("price","单价"),

    //金额
    AMOUNT("amount","金额"),

    //第二季度-商品名称
    GOODSNAMEOFSECOND("goodsNameOfSecond","名称"),

    //第二季度-数量
    QUANTITYOFSECOND("quantityOfSecond","数量"),

    //第二季度-单价
    PRICEOFSECOND("priceOfSecond","单价"),

    //第二季度-金额
    AMOUNTOFSECOND("amountOfSecond","金额");

    private String key;

    private String value;

    public String getKey() {
        return key;
    }

    public void setKey(String key) {
        this.key = key;
    }

    public String getValue() {
        return value;
    }

    public void setValue(String value) {
        this.value = value;
    }

    ExcelHeader(String key,String value){
        this.key = key;
        this.value = value;
    }
}

 3.判读是否空行

public static boolean isRowEmpty(Row row){
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            Cell cell = row.getCell(i);
            if (cell != null && cell.getCellType() != CellType.BLANK){
                return false;
            }
        }
        return true;
    }