导出 Excel（动态导入标头）

Excel表导出easypoi

导出Excel
- 1.依赖包
- - 1可能出现的坑，含模版坑（maven版本冲突，自用4.0.0）
- 2.controller
- 3.工具类
- - - excel
    - 时间
- 4.实体类
- - - 进阶注解
Excel模板导出
- 1.模板导入(表达式写正确)
- 2.代码实现
- 3.Excel导出HTML
自定义数据表头导出（进阶）
- 表头为数据库第一行数据
- 下载效果图

导出Excel 1.依赖包

		
        
            cn.afterturn
            easypoi-base
            4.0.0
        
        
        
        
            cn.afterturn
            easypoi-spring-boot-starter
            4.4.0
        
        
         
        
            com.alibaba
            easyexcel
            3.0.2
        
        
            cn.afterturn
            easypoi-annotation
            4.0.0

1可能出现的坑，含模版坑（maven版本冲突，自用4.0.0）

解决后

		
		
			cn.afterturn
			easypoi-base
			4.0.0
		
		
			cn.afterturn
			easypoi-web
			4.0.0
		
		
			cn.afterturn
			easypoi-annotation
			4.0.0
		

		
		
			com.alibaba
			easyexcel
			1.1.2-beat1
			
				
					poi-ooxml
					org.apache.poi
				
				
					poi
					org.apache.poi
				
			
		
		
		
		
			com.alibaba
			easyexcel
			2.2.6
			
				
					org.apache.poi
					poi
				
				
					poi
					org.apache.poi
				
				
					poi-ooxml
					org.apache.poi
				
				
					poi-ooxml-schemas
					org.apache.poi
				
			
		

		
		
		
			com.meditrusthealth
			fast-common-core
			
				
					poi
					org.apache.poi

2.controller

    
    @GetMapping("export/card/list")
    @ApiOperation("导出")
    void exportUserManage(@ApiIgnore @RequestParam("ids") Long[] ids, @ApiIgnore HttpServletResponse response) throws Exception{
    LambdaQueryWrapper lambdaQueryWrapper = new LambdaQueryWrapper<>();
        lambdaQueryWrapper.in(EquityCardBatchnoDetail::getBatchNo,ids)
//                .eq(EquityCardBatchno::getDelFlag,false)
                .orderByDesc(EquityCardBatchnoDetail::getCreateTime)
        ;
        List list = equityCardBatchnoDetailMapper.selectList(lambdaQueryWrapper);
        for (EquityCardBatchnoDetail detail : list) {
            if ("01".equals(detail.getActivationStatus())){
                detail.setActivationStatus("未激活");
            }
            if ("02".equals(detail.getActivationStatus())){
                detail.setActivationStatus("已激活");
            }
        }

        ExcelUtils.exportExcelToTarget(response, null, list, EquityCardExcel.class);
    }

3.工具类 excel

package com.meditrusthealth.mth.equity.service.util;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.BeanUtils;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;


public class ExcelUtils {

    
    public static void exportExcel(HttpServletResponse response, String fileName, Collection list,
                                     Class pojoClass) throws IOException {
        if(StringUtils.isBlank(fileName)){
            //当前日期
            fileName = DateUtils.format(new Date());
        }

        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), pojoClass, list);
        response.setCharacterEncoding("UTF-8");
        response.setHeader("content-Type", "application/vnd.ms-excel");
        response.setHeader("Content-Disposition",
                "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xls");
        ServletOutputStream out = response.getOutputStream();
        workbook.write(out);
        out.flush();
    }

    
    public static void exportExcelToTarget(HttpServletResponse response, String fileName, Collection sourceList,
                                     Class targetClass) throws Exception {
        List targetList = new ArrayList<>(sourceList.size());
        for(Object source : sourceList){
            Object target = targetClass.newInstance();
            BeanUtils.copyProperties(source, target);
            targetList.add(target);
        }

        exportExcel(response, fileName, targetList, targetClass);
    }
}

时间

package com.meditrusthealth.mth.equity.service.util;

import org.apache.commons.lang3.StringUtils;
import org.joda.time.DateTime;
import org.joda.time.LocalDate;
import org.joda.time.format.DateTimeFormat;
import org.joda.time.format.DateTimeFormatter;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;


public class DateUtils {
	
	public final static String DATE_PATTERN = "yyyy-MM-dd";
	
	public final static String DATE_TIME_PATTERN = "yyyy-MM-dd HH:mm:ss";

    
	public static String format(Date date) {
        return format(date, DATE_PATTERN);
    }

    
    public static String format(Date date, String pattern) {
        if(date != null){
            SimpleDateFormat df = new SimpleDateFormat(pattern);
            return df.format(date);
        }
        return null;
    }

    
    public static Date parse(String date, String pattern) {
        try {
            return new SimpleDateFormat(pattern).parse(date);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return null;
    }

    
    public static Date stringToDate(String strDate, String pattern) {
        if (StringUtils.isBlank(strDate)){
            return null;
        }

        DateTimeFormatter fmt = DateTimeFormat.forPattern(pattern);
        return fmt.parseLocalDateTime(strDate).toDate();
    }

    
    public static Date[] getWeekStartAndEnd(int week) {
        DateTime dateTime = new DateTime();
        LocalDate date = new LocalDate(dateTime.plusWeeks(week));

        date = date.dayOfWeek().withMinimumValue();
        Date beginDate = date.toDate();
        Date endDate = date.plusDays(6).toDate();
        return new Date[]{beginDate, endDate};
    }

    
    public static Date addDateSeconds(Date date, int seconds) {
        DateTime dateTime = new DateTime(date);
        return dateTime.plusSeconds(seconds).toDate();
    }

    
    public static Date addDateMinutes(Date date, int minutes) {
        DateTime dateTime = new DateTime(date);
        return dateTime.plusMinutes(minutes).toDate();
    }

    
    public static Date addDateHours(Date date, int hours) {
        DateTime dateTime = new DateTime(date);
        return dateTime.plusHours(hours).toDate();
    }

    
    public static Date addDateDays(Date date, int days) {
        DateTime dateTime = new DateTime(date);
        return dateTime.plusDays(days).toDate();
    }

    
    public static Date addDateWeeks(Date date, int weeks) {
        DateTime dateTime = new DateTime(date);
        return dateTime.plusWeeks(weeks).toDate();
    }

    
    public static Date addDateMonths(Date date, int months) {
        DateTime dateTime = new DateTime(date);
        return dateTime.plusMonths(months).toDate();
    }

    
    public static Date addDateYears(Date date, int years) {
        DateTime dateTime = new DateTime(date);
        return dateTime.plusYears(years).toDate();
    }
}

4.实体类

@Data
public class EquityCardExcel {
    @Excel(name = "卡号")
    private String cardNo;
    @Excel(name = "激活码")
    private String activationCode;
    @Excel(name = "激活状态")
    private String activationStatus;
    @Excel(name = "包编码")
    private String pkgCode;
    @Excel(name = "包名称")
    private String pkgName;
  }

进阶注解

//多种注解使用,needMerge 合并单元格;groupName 双行表头;orderNum 排序规则
//replace 替换值,isimportField true为校验;suffix = "生" ,拼接值
@Excel(name = "产品名称", needMerge = true, groupName = "二级", orderNum = "1")
    private String r1;
    @Excel(name = "申请时间", groupName = "二级", orderNum = "2")
    private String r2;
    @Excel(name = "被保险人姓名", groupName = "二级", orderNum = "3")
    private String r3;
    @Excel(name = "客户类型", groupName = "二级", orderNum = "4")

@Excel(name = "激活状态",replace = { "未激活_01", "激活_02" }, isimportField = "true")
    private String activationStatus;

Excel模板导出 1.模板导入(表达式写正确)

表达式

空格分割
三目运算 {{test ? obj:obj2}}
n: 表示这个cell是数值类型 {{n:}}
le: 代表长度{{le:()}} 在if/else 运用{{le:() > 8 ? obj1 : obj2}}
fd: 格式化时间 {{fd:(obj;yyyy-MM-dd)}}
fn: 格式化数字 {{fn:(obj;###.00)}}
fe: 遍历数据,创建row
!fe: 遍历数据不创建row
$fe: 下移插入,把当前行,下面的行全部下移.size()行,然后插入
#fe: 横向遍历
v_fe: 横向遍历值
!if: 删除当前列 {{!if:(test)}}
单引号表示常量值 ‘’ 比如’1’ 那么输出的就是 1
&NULL& 空格
]] 换行符多行遍历导出
sum：统计数据

整体风格和el表达式类似，大家应该也比较熟悉
采用的写法是{{}}代表表达式，然后根据表达式里面的数据取值
关于样式问题
easypoi不会改变excel原有的样式，如果是遍历，easypoi会根据模板的那一行样式进行复制

模板:

结果:

2.代码实现

{{$fe: maplist lm.r0
maplist为总数据 lm是一个多集合
是数据集合 lm为当前循环集合♻️的一条

 public static void main(String[] args) throws Exception {
 		//引入写好表达式的excel
        TemplateExportParams params = new TemplateExportParams("WEB-INF/doc/专项支出用款申请书_map.xls");
        //本地模版位置，实际项目中打jar包测试服务器读不到问题未解决，换另一种看下方进阶
        
        Map map = new HashMap();
        //k与表达式一一对应,v是显示的值
        map.put("date", "2014-12-25");
        map.put("money", 2000000.00);
        map.put("upperMoney", "贰佰万");
        map.put("company", "执笔潜行科技有限公司");
        map.put("bureau", "财政局");
        map.put("person", "JueYue");
        map.put("phone", "1879740****");
        List> listMap = new ArrayList>();
        for (int i = 0; i < 4; i++) {
            Map lm = new HashMap();
            lm.put("id", i + 1 + "");
            lm.put("zijin", i * 10000 + "");
            lm.put("bianma", "A001");
            lm.put("mingcheng", "设计");
            lm.put("xiangmumingcheng", "EasyPoi " + i + "期");
            lm.put("quancheng", "开源项目");
            lm.put("sqje", i * 10000 + "");
            lm.put("hdje", i * 10000 + "");

            listMap.add(lm);
        }
        map.put("maplist", listMap);

        Workbook workbook = ExcelExportUtil.exportExcel(params, map);
        File savefile = new File("G:/excel/");
        if (!savefile.exists()) {
            savefile.mkdirs();
        }
        //文件导出查看
        FileOutputStream fos = new FileOutputStream("G:/excel/专项支出用款申请书_map.xls");
        workbook.write(fos);
        fos.close();
    }

3.Excel导出HTML

07版 xlsx

        ExcelToHtmlParams params = new ExcelToHtmlParams(WorkbookFactory.create(POICacheManager.getFile("G:/excel/自己的文件.xlsx")));
        response.getOutputStream().write(ExcelXorHtmlUtil.excelToHtml(params).getBytes());

03版 xls

		ExcelToHtmlParams params = new ExcelToHtmlParams(WorkbookFactory.create(POICacheManager.getFile("exceltohtml/exporttemp_img.xls")),true,"yes");
        response.getOutputStream().write(ExcelXorHtmlUtil.excelToHtml(params).getBytes());

更多了解可看官方文档:easypoi官方文档

自定义数据表头导出（进阶）表头为数据库第一行数据

特殊需求用了select * ；
目的：数据库增加字段后不需要改动代码

数据库

	//切换数据源
    @SwitchDs(name = "lpmx")
    @Override
        public void customizeExcel(String startTime, String endTime, HttpServletResponse response) throws Exception {
        
        List> selectAllJxMaps = claimDetailsJxMapper.selectAllJx();
        
        List> maps = claimDetailsJxMapper.selectAllJxTime(startTime, endTime);

        
        List decryptList = new ArrayList<>();
//        decryptList.add("r3");
        decryptList.add("r6");//证件号
//        decryptList.add("r7");

        
        List colList = new ArrayList();
        
        Map title = selectAllJxMaps.remove(0);
        
        for (Map.Entry entry : title.entrySet()) {
            String key = entry.getKey();
            Object value = entry.getValue();
            ExcelExportEntity colEntity = new ExcelExportEntity(String.valueOf(value), key);
            colEntity.setNeedMerge(true);
            colList.add(colEntity);
        }
        
        for (int i = 0; i < maps.size(); i++) {
            Map objectMap = maps.get(i);
            
            for (Map.Entry entry : objectMap.entrySet()) {
                String key = entry.getKey();
                Object value = entry.getValue();
                
                if (decryptList.contains(key)) {
                    String decrypt = FastCodeUtils.decrypt(String.valueOf(value));
                    if (StringUtils.isNotBlank(decrypt)) {
                        entry.setValue(decrypt);
                    }
                }
            }
        }
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(null, "数据"), colList, maps);
        response.setCharacterEncoding("UTF-8");
        response.setHeader("content-Type", "application/vnd.ms-excel");
        response.setHeader("Content-Disposition",
                "attachment;filename=" + URLEncoder.encode("某某某数据", "UTF-8") + ".xls");
        ServletOutputStream out = response.getOutputStream();
        workbook.write(out);
    }

下载效果图

导出 Excel（动态导入标头）

Java相关栏目本月热门文章