easypoi---Excel文件导出

一.pom文件引入jar

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

二.创建工具类

public class ExcelUtils {
	
	public static void exportExcel(List list, Class pojoClass, String fileName, ExportParams exportParams,
			HttpServletResponse response) throws IOException {
		Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
		downLoadExcel(fileName, response, workbook);
	}

	public static void exportExcel(ExportParams exportParams, List colList, List list,HttpServletResponse response)
			throws IOException {
		Workbook workbook = ExcelExportUtil.exportExcel(exportParams, colList, list);
		downLoadExcel(exportParams.getSheetName(), response, workbook);
	}

	
	private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook)
			throws IOException {
		try {
			response.setHeader("Access-Control-Allow-Origin","*");
			response.setCharacterEncoding("UTF-8");
			response.setHeader("content-Type", "application/vnd.ms-excel");
			response.setHeader("Content-Disposition",
					"attachment;filename=" + URLEncoder.encode(fileName + ".xls", "UTF-8"));
			OutputStream output=response.getOutputStream();
			workbook.write(output);
			output.flush();
			workbook.close();
			output.close();
		} catch (Exception e) {
			throw new IOException(e.getMessage());
		}
	}
}

三.创建实体类

public class ElePfvEnergyDTO implements Serializable{
	
	private static final long serialVersionUID = 1L;

	@Excel(name = "工序名称", width = 20)
    private String name;

    @Excel(name = "电量", groupName = "尖", width = 20, orderNum = "0")
    private Double top;

    @Excel(name = "电费", groupName = "尖", width = 20, orderNum = "1")
    private Double topCost;

    @Excel(name = "电量", groupName = "高", width = 20, orderNum = "0")
    private Double high;
    
    @Excel(name = "电费", groupName = "高", width = 20, orderNum = "1")
    private Double highCost;
    
    @Excel(name = "电量", groupName = "平", width = 20, orderNum = "0")
    private Double flat;
    
    @Excel(name = "电费", groupName = "平", width = 20, orderNum = "1")
    private Double flatCost;
    
    @Excel(name = "电量", groupName = "谷", width = 20, orderNum = "0")
    private Double valley;
    
    @Excel(name = "电费", groupName = "谷", width = 20, orderNum = "1")
    private Double valleyCost;
    
    @Excel(name = "电量", groupName = "总计", width = 20, orderNum = "0")
    private Double allEle;
    
    @Excel(name = "电费", groupName = "总计", width = 20, orderNum = "1")
    private Double allCost;
}

四.在ExportParams类中，找到ExcelExportStylerDefaultImpl类，复制内容并生成新的类（用来设置excel表头背景，字体，位置等）

public class ExcelExportStylerImpl extends AbstractExcelExportStyler
                                          implements IExcelExportStyler {

    public ExcelExportStylerImpl(Workbook workbook) {
        super.createStyles(workbook);
    }

    @Override
    public CellStyle getTitleStyle(short color) {
        CellStyle titleStyle = workbook.createCellStyle();
        Font font = this.workbook.createFont();
        font.setBold(true);
        titleStyle.setFont(font);
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        titleStyle.setWrapText(true);
        titleStyle.setBorderRight(BorderStyle.THIN);
        titleStyle.setBorderTop(BorderStyle.THIN);
        titleStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return titleStyle;
    }

    @Override
    public CellStyle stringSeptailStyle(Workbook workbook, boolean isWarp) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setDataFormat(STRING_FORMAT);
        if (isWarp) {
            style.setWrapText(true);
        }
        return style;
    }

    @Override
    public CellStyle getHeaderStyle(short color) {
        CellStyle titleStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);
        titleStyle.setFont(font);
        return titleStyle;
    }

    @Override
    public CellStyle stringNoneStyle(Workbook workbook, boolean isWarp) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setDataFormat(STRING_FORMAT);
        if (isWarp) {
            style.setWrapText(true);
        }
        return style;
    }

}

五.controller层调用

@RequestMapping(value = "/exportPFYXls")
	public void exportXls(HttpServletResponse response) {

		List list = service.getAreaCodeList();
    	String fileName = "明细表";
        String sheetName = "明细表";
        ExportParams exportParams = new ExportParams();
        exportParams.setSheetName(sheetName);
        exportParams.setStyle(ExcelExportStylerImpl.class);
        exportParams.setCreateHeadRows(true);
        exportParams.setHeaderHeight(500D);
        try {
        	ExcelUtils.exportExcel(list,ElePfvEnergyDTO.class,fileName,exportParams,response);
        } catch (Exception e) {
            e.printStackTrace();
        }
	}

六.展示：

七:纵向单元格合并

excel注解里面有两个属性 NeedMerge：是否合并； MergeVertical：纵向合并

@Excel(name = "电量", groupName = "尖", width = 20, orderNum = "0" ,mergeVertical = true, needMerge = true)

八.动态表头:

1.手动拼接表头:

public List getHeadList(EnParamsVo vo){
    	List dateList = new ArrayList();
		for (int i = 0; i < vo.getSelCount(); i++) {
			String thisDay = DateUtil.getSpecifiedDayBefore(vo.getSelDate(), i);
			dateList.add(thisDay);
		}
		Collections.reverse(dateList);
    	List colList = new ArrayList();
        //第一列
        ExcelExportEntity colEntity1 = new ExcelExportEntity("名称", "name");
        colEntity1.setNeedMerge(true);
        colEntity1.setMergeVertical(true);
        colList.add(colEntity1);
        //第二列
        ExcelExportEntity colEntity2 = new ExcelExportEntity("类别", "type");
        colEntity2.setNeedMerge(true);
        colList.add(colEntity2);
        for(String localDate:dateList){
            ExcelExportEntity dateColGroup = new ExcelExportEntity(localDate, localDate);
            List dateColList = new ArrayList();
            ExcelExportEntity tempExcelExportEntity = new ExcelExportEntity(DateUtil.getWeekOfDate(localDate), "dayOfWeek"+localDate);
            dateColList.add(tempExcelExportEntity);
            dateColGroup.setList(dateColList);
            colList.add(dateColGroup);
        }
        return colList;
    }

2.手动拼接值:

public List> getExcelList(Map map, String name) {
		List dateList = (List) map.get("xList");  
		List outputList = (List) map.get("outputList");
		List electricList = (List) map.get("electricList");
    	List> list = new ArrayList>();
    	List typeList = Arrays.asList("test1","test2","test3","test4","test5","test6");
        for(int i=0;i < typeList.size();i++){
            Map valMap = new HashMap<>();
            valMap.put("name", name);
            valMap.put("type",typeList.get(i));
            	for(int j=0;j < dateList.size();j++){
                    List> dayOfWeekList = new ArrayList>();
                    Map dayOfWeekMap = new HashMap();
                    if (i == 1) {
                    	dayOfWeekMap.put("dayOfWeek"+ dateList.get(j), outputList.get(j));
					}else if(i == 2) {
						dayOfWeekMap.put("dayOfWeek"+ dateList.get(j), electricList.get(j));
					}
					else {
						dayOfWeekMap.put("dayOfWeek"+ dateList.get(j), 0);
					}	
                    dayOfWeekList.add(dayOfWeekMap);
                    valMap.put(dateList.get(j), dayOfWeekList);
                }
 
            list.add(valMap);
        }
        return list;
    }

3.导出:

public void exportMCUOfDay(EnParamsVo vo, HttpServletResponse response){
        List colList = getHeadList(vo);
        List idList = eStruService.getNodeListByDeptIdAndLevel(vo.getSelCode(), "3");
        List> list = new ArrayList>();
        if (CollectionUtils.isEmpty(idList)) {
			return;
		}
        idList.forEach(r->{
        	vo.setSelCode(r);
        	String name = eStruService.getOne(new QueryWrapper().eq("id", r)).getName();
    		QueryWrapper queryWrapper = new QueryWrapper();
    		if (CollectionUtils.isNotEmpty(idList)) {
    			queryWrapper.eq("parent_id", r);
    		} else {
    			queryWrapper.eq("id", "");
    		}
    		queryWrapper.isNotNull("code");
    		List centerNameList = eStruService.list(queryWrapper).stream().map(x ->x.getCode())
    				.collect(Collectors.toList());
    		if (CollectionUtils.isEmpty(centerNameList)) {
    			centerNameList = Arrays.asList("1");
    		}
    		Map map =  queryMCUOfDay(vo, centerNameList);
    		list.addAll(getExcelList(map,name));
        });
        String sheetName = "用电明细";
        ExportParams exportParams = new ExportParams();
        exportParams.setSheetName(sheetName);
        exportParams.setStyle(ExcelExportStylerImpl.class);
        exportParams.setCreateHeadRows(true);
        exportParams.setHeaderHeight(500D);
        try {
        	ExcelUtils.exportExcel(exportParams, colList,list, response);
        } catch (Exception e) {
            e.printStackTrace();
        }
        
    }

展示:

easypoi---Excel文件导出

Java相关栏目本月热门文章