Hutool - 导出复杂Excel - 《Marie_Zhang学习笔记》

Pojo：

package com.rit.demo.pojo;
import java.math.BigDecimal;
import java.util.Date;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.extension.activerecord.Model;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
@Data
public class User extends Model<User>{
    /**  
      * @Fields serialVersionUID :            TODO
      */  
    private static final long serialVersionUID = -4847514461834601881L;
    //設置id自增，再能插入后返回id，設置type才能正常顯示自增id，不設置type插入id會很大
    @TableId(value="idx",type = IdType.AUTO)
    private Integer id;
    private String workid;
     private String name;
    private Integer age;
    private String gerden;
     @JsonFormat(locale="zh", timezone="GMT+8", pattern="yyyy-MM-dd")
    private Date creatTime;
    private BigDecimal money;
}

UserDao：

package com.rit.demo.dao;
import java.util.List;
import com.rit.demo.pojo.User;
public interface UserDao {
    //查询全部
    List<User> queryAll();
}

UserMapper.xml：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.rit.demo.dao.UserDao" >
  <resultMap id="BaseResultMap" type="com.rit.demo.pojo.User" >
    <id column="id" property="id" jdbcType="INTEGER" />
     <result column="workid" property="workid" jdbcType="VARCHAR" />
    <result column="name" property="name" jdbcType="VARCHAR" />
    <result column="age" property="age" jdbcType="INTEGER" />
    <result column="gerden" property="gerden" jdbcType="VARCHAR" />
    <result column="creat_time" property="creatTime" jdbcType="DATE" />
    <result column="money" property="money" jdbcType="DECIMAL" />
  </resultMap>
 <!-- 表字段 -->
    <sql id="baseColumns">
        u.id
        , u.workid
        , u.name
        , u.age
        , u.gerden
        , u.creat_time
        , u.money
    </sql>
  <!--查询所有数据-->
  <select id="queryAll"  resultMap="BaseResultMap">
    SELECT <include refid="baseColumns" /> from user u
  </select>
</mapper>

UserController：

package com.rit.demo.controller;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.CacheEvict;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import com.rit.demo.pojo.User;
import com.rit.demo.service.RedisService;
import com.rit.demo.service.UserService;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
@RestController
@RequestMapping("/userCon")
public class UserController {
    @Autowired
    private UserService us;
    @GetMapping("user_export_excel")
    @ResponseBody
    public void exportExcel(HttpServletResponse response) throws UnsupportedEncodingException{
        //設置響應類型
        response.setContentType("application/vnd.ms-excel");
        //設置字符編碼
        response.setCharacterEncoding("utf-8");
        //設置響應頭信息
        response.setHeader("Content-dispositon", "attachment:filename*=urf-8''"+URLEncoder.encode("用户数据","UTF-8")+".xlsx");
        List<User> list=us.queryAll();
        //写入文件
        ExcelWriter writer=ExcelUtil.getWriter();
        CellStyle cellStyle=writer.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setBorderLeft(writer.getCellStyle().getBorderLeft());
        cellStyle.setBorderRight(writer.getCellStyle().getBorderRight());
        cellStyle.setBorderBottom(writer.getCellStyle().getBorderBottom());
        Font font=writer.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short)12);
        font.setFontName("微软雅黑");
        cellStyle.setFont(font);
        //设置总价单元格样式
        CellStyle cs=writer.createCellStyle();
        cs.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GOLD.getIndex());
        cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //设置垂直居中和水平居中
        cs.setVerticalAlignment(VerticalAlignment.CENTER);
        cs.setAlignment(HorizontalAlignment.LEFT);
        //设置边框
        cs.setBorderRight(writer.getCellStyle().getBorderRight());
        cs.setBorderBottom(writer.getCellStyle().getBorderBottom());
        //merge 数字从0开始算 前面两个数字是第几行到第几行合并 后面两个数字是第几列到第几列合并
        //标题一
        writer.merge(0,0,0,11,"BOM报价单",cellStyle);
        //标题二
        writer.merge(1,1,0,3,"您上传的BOM",cellStyle);
        writer.merge(1,1,4,9,"商城的报价单",cellStyle);
        writer.merge(1,1,10,11,"总价（含税）:99.99",cellStyle);
        //跳过前面两行（为了设置标题样式）原始的设置标题方法，不可以，例：merge(3,"标题名",false);
        writer.passRows(2);
        //设置高度（行row）
        writer.setRowHeight(0, 50);
        writer.setRowHeight(1, 30);
        //设置导出信息的表头
        writer.addHeaderAlias("workid", "工号");
        writer.addHeaderAlias("name", "姓名");
        writer.addHeaderAlias("age", "年龄");
        writer.addHeaderAlias("gerden", "性别");
        writer.addHeaderAlias("creatTime", "入职时间");
        writer.addHeaderAlias("money", "基本工资");
        //设置列宽（Colum）
        writer.setColumnWidth(5, 20);
        writer.setColumnWidth(10, 10);
        writer.setColumnWidth(11, 10);
        //设置导出表头样式，但不包括合并单元格
        CellStyle headCellStyle=writer.getHeadCellStyle();
        headCellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIME.getIndex());
        headCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //写入
        writer.write(list,true);
        //主要针对自定义表头设置 行高（必须要写入完成后才可以设置）
        for (int i = 2; i < list.size(); i++) {
            writer.setRowHeight(i, 20);
        }
        try {
            writer.flush(response.getOutputStream(),true);
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            writer.close();
        }
    }
}