poi导出

1. 引入pom

        <!-- easypoi相关jar包 -->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-spring-boot-starter</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>4.1.0</version>
        </dependency>

　　

2.  EasypoiUtil.java

package com.diit.dwyxbd.utils;

import cn.afterturn.easypoi.entity.ImageEntity;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.export.ExcelExportService;
import com.alibaba.druid.util.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Base64;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.UUID;
import java.util.stream.Collectors;


@Component
public class EasypoiUtil {

    //模板生成路径
    @Value("${at.file-upload}")
    private String paths;


    /**
     * 构建向word中插入的图片信息
     *
     * @param width
     * @param height
     * @param imagePath
     * @return
     */
    public ImageEntity structureImageDate(Integer width, Integer height, String imagePath) {
        ImageEntity image = new ImageEntity();
        image.setWidth(width);
        image.setHeight(height);
        image.setUrl(imagePath);
        image.setType(ImageEntity.URL);
        return image;
    }


    /**
     * 根据base64生成图片
     *
     * @param base64Info
     * @param filePath
     * @return
     */
    public String decodeBase64(String base64Info, File filePath) {
        File dir = new File(filePath + File.separator + "homeImage");
        if (!dir.exists()) {
            dir.mkdirs();
        }
        if (StringUtils.isEmpty(base64Info)) {
            return null;
        }
        String[] arr = base64Info.split("base64,");
        //要保存的路径
        String picPath = "/homeImage/" + UUID.randomUUID().toString() + ".png";
        String save = filePath + picPath;
        try {
            byte[] buffer = Base64.getDecoder().decode(arr[0]);
            OutputStream os = new FileOutputStream(save);
            os.write(buffer);
            os.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return picPath;
    }

    /**
     * 根据base64生成专题图片
     *
     * @param base64Info
     * @param
     * @return 路径
     */
    public String specialDecodeBase64(String base64Info, String imagename, String tid) {
        File dir = new File(paths + File.separator + "AssessmentImage" + File.separator + tid);
        if (!dir.exists()) {
            dir.mkdirs();
        }
        if (StringUtils.isEmpty(base64Info)) {
            return null;
        }
        String[] arr = base64Info.split("base64,");

        String save = dir.getPath() + File.separator + imagename;
        try {
            byte[] buffer = Base64.getDecoder().decode(arr[0]);
            OutputStream os = new FileOutputStream(save);
            os.write(buffer);
            os.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return save;
    }

    /**
     * 导出多个sheet
     * @param excelName
     * @param head
     * @param title
     * @param data
     * @param exportFolder
     * @return
     */
    public Map<String, Object> exportExcel(String excelName, String head, List<Map<String, Object>> title, List<Map<String, Object>> data, File exportFolder, String key) {
        //构建标题
        List<ExcelExportEntity> titleList = new ArrayList<>();//存放标题
        for (Map<String, Object> map : title) {
            if (map.get("alias") != null) {
                ExcelExportEntity colEntity = new ExcelExportEntity(map.get("alias").toString(), map.get("field").toString());
                colEntity.setNeedMerge(true);
                titleList.add(colEntity);
            }
        }

        Map<String, List<Map<String, Object>>> province = data.stream().collect(Collectors.groupingBy(e -> (String) e.get("province")));
        Set<String> province_key = province.keySet();
        List<Map<String, Object>> sheetList = new ArrayList<>();
        for (String pro : province_key) {
            List<Map<String, Object>> maps1 = province.get(pro); // 每个省份数据
            // 创建sheet放入sheet集合
            Map<String, Object> sheet = new HashMap<>();
            sheetList.add(sheet);
            ExportParams ep1 = new ExportParams(head, pro);
            ep1.setStyle(ExcelStyleUtil.class);//设置样式
            sheet.put("title", ep1);
            sheet.put("entity", titleList);
            sheet.put("data", maps1);
        }
        Workbook workbook = new HSSFWorkbook();// excelExportUtils.getWorkbook(ExcelType.HSSF, 0);
        for (int i = 0; i < sheetList.size(); i++) {
            Map<String, Object> map = sheetList.get(i);
            ExportParams exportParams = (ExportParams) map.get("title");
            List<ExcelExportEntity> entity = (List<ExcelExportEntity>) map.get("entity");
            List<Map<String, Object>> listDataMap = (List<Map<String, Object>>) map.get("data");
            ExcelExportService service = new ExcelExportService();
            service.createSheetForMap(workbook, exportParams, entity, listDataMap);
            //获取tab页的表格设置单元格自适应宽度
            Sheet sheet1 = workbook.getSheetAt((short) i);
            for (int j = 0; j < titleList.size(); j++) {
                sheet1.autoSizeColumn((short) j);
            }
        }
        //下载生成的excel
        if (!exportFolder.exists()) {
            exportFolder.mkdirs();
        }
        Map<String, Object> result = new HashMap<>();
        /* 这中方式是直接生成excel保存到服务器某个地址*/
        try {
            FileOutputStream fos = new FileOutputStream(exportFolder.getPath() + "/" + excelName);
            workbook.write(fos);
            result.put("path", exportFolder.getPath());
            result.put("fileName", excelName);
            System.out.println(result);
            fos.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 单sheet
     * @param excelName
     * @param head
     * @param title
     * @param data
     * @param exportFolder
     * @param key
     * @return
     */
    public Map<String, Object> exportExcel2(String excelName, String head, List<Map<String, Object>> title, List<Map<String, Object>> data, File exportFolder, String key) {
        //构建标题
        List<ExcelExportEntity> titleList = new ArrayList<>();//存放标题
        for (Map<String, Object> map : title) {
            if (map.get("alias") != null) {
                ExcelExportEntity colEntity = new ExcelExportEntity(map.get("alias").toString(), map.get("field").toString());
                colEntity.setNeedMerge(true);
                titleList.add(colEntity);
            }
        }
        List<Map<String, Object>> sheetList = new ArrayList<>();
        Map<String, Object> sheet = new HashMap<>();
        sheetList.add(sheet);
        ExportParams ep1 = new ExportParams(head, key);
        ep1.setStyle(ExcelStyleUtil.class);//设置样式
        sheet.put("title", ep1);
        sheet.put("entity", titleList);
        sheet.put("data", data);
        Workbook workbook = new HSSFWorkbook();// excelExportUtils.getWorkbook(ExcelType.HSSF, 0);
        for (int i = 0; i < sheetList.size(); i++) {
            Map<String, Object> map = sheetList.get(i);
            ExportParams exportParams = (ExportParams) map.get("title");
            List<ExcelExportEntity> entity = (List<ExcelExportEntity>) map.get("entity");
            List<Map<String, Object>> listDataMap = (List<Map<String, Object>>) map.get("data");
            ExcelExportService service = new ExcelExportService();
            service.createSheetForMap(workbook, exportParams, entity, listDataMap);
            //获取tab页的表格设置单元格自适应宽度
            Sheet sheet1 = workbook.getSheetAt((short) i);
            for (int j = 0; j < titleList.size(); j++) {
                sheet1.autoSizeColumn((short) j);
            }
        }
        //下载生成的excel
        if (!exportFolder.exists()) {
            exportFolder.mkdirs();
        }
        Map<String, Object> result = new HashMap<>();
        /* 这中方式是直接生成excel保存到服务器某个地址*/
        try {
            FileOutputStream fos = new FileOutputStream(exportFolder.getPath() + "/" + excelName);
            workbook.write(fos);
            result.put("path", exportFolder.getPath());
            result.put("fileName", excelName);
            System.out.println(result);
            fos.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

}

　

3. ExcelStyleUtil.java

package com.diit.dwyxbd.utils;

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.Cell;
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.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.entity.params.ExcelForEachParams;
import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;

public class ExcelStyleUtil implements IExcelExportStyler{

	 private static final short STRING_FORMAT = (short) BuiltinFormats.getBuiltinFormat("TEXT");
	    private static final short FONT_SIZE_TEN = 10;
	    private static final short FONT_SIZE_ELEVEN = 11;
	    private static final short FONT_SIZE_TWELVE = 12;
	    /**
	     * 大标题样式
	     */
	    private CellStyle headerStyle;
	    /**
	     * 每列标题样式
	     */
	    private CellStyle titleStyle;
	    /**
	     * 数据行样式
	     */
	    private CellStyle styles;

	    public ExcelStyleUtil(Workbook workbook) {
	        this.init(workbook);
	    }

	    /**
	     * 初始化样式
	     *
	     * @param workbook
	     */
	    private void init(Workbook workbook) {
	        this.headerStyle = initHeaderStyle(workbook);
	        this.titleStyle = initTitleStyle(workbook);
	        this.styles = initStyles(workbook);
	    }

	    /**
	     * 大标题样式
	     *
	     * @param color
	     * @return
	     */
	    @Override
	    public CellStyle getHeaderStyle(short color) {
	        return headerStyle;
	    }

	    /**
	     * 每列标题样式
	     *
	     * @param color
	     * @return
	     */
	    @Override
	    public CellStyle getTitleStyle(short color) {
	        return titleStyle;
	    }

	    /**
	     * 数据行样式
	     *
	     * @param parity 可以用来表示奇偶行
	     * @param entity 数据内容
	     * @return 样式
	     */
	    @Override
	    public CellStyle getStyles(boolean parity, ExcelExportEntity entity) {
	        return styles;
	    }

	    /**
	     * 获取样式方法
	     *
	     * @param dataRow 数据行
	     * @param obj     对象
	     * @param data    数据
	     */
	    @Override
	    public CellStyle getStyles(Cell cell, int dataRow, ExcelExportEntity entity, Object obj, Object data) {
	        return getStyles(true, entity);
	    }

	    /**
	     * 模板使用的样式设置
	     */
	    @Override
	    public CellStyle getTemplateStyles(boolean isSingle, ExcelForEachParams excelForEachParams) {
	        return null;
	    }

	    /**
	     * 初始化--大标题样式
	     *
	     * @param workbook
	     * @return
	     */
	    private CellStyle initHeaderStyle(Workbook workbook) {
	        CellStyle style = getBaseCellStyle(workbook);
	        style.setFont(getFont(workbook, FONT_SIZE_TWELVE, true));
	        return style;
	    }

	    /**
	     * 初始化--每列标题样式
	     *
	     * @param workbook
	     * @return
	     */
	    private CellStyle initTitleStyle(Workbook workbook) {
	        CellStyle style = getBaseCellStyle(workbook);
	        style.setFont(getFont(workbook, FONT_SIZE_ELEVEN, false));
	        //背景色
	        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
	        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
	        return style;
	    }

	    /**
	     * 初始化--数据行样式
	     *
	     * @param workbook
	     * @return
	     */
	    private CellStyle initStyles(Workbook workbook) {
	        CellStyle style = getBaseCellStyle(workbook);
	        style.setFont(getFont(workbook, FONT_SIZE_TEN, false));
	        style.setDataFormat(STRING_FORMAT);
	        return style;
	    }

	    /**
	     * 基础样式
	     *
	     * @return
	     */
	    private CellStyle getBaseCellStyle(Workbook workbook) {
	        CellStyle style = workbook.createCellStyle();
	        //下边框
	        style.setBorderBottom(BorderStyle.THIN);
	        //左边框
	        style.setBorderLeft(BorderStyle.THIN);
	        //上边框
	        style.setBorderTop(BorderStyle.THIN);
	        //右边框
	        style.setBorderRight(BorderStyle.THIN);
	        //水平居中
	        style.setAlignment(HorizontalAlignment.CENTER);
	        //上下居中
	        style.setVerticalAlignment(VerticalAlignment.CENTER);
	        //设置自动换行
	        style.setWrapText(true);
	        return style;
	    }

	    /**
	     * 字体样式
	     *
	     * @param size   字体大小
	     * @param isBold 是否加粗
	     * @return
	     */
	    private Font getFont(Workbook workbook, short size, boolean isBold) {
	        Font font = workbook.createFont();
	        //字体样式
	        font.setFontName("宋体");
	        //是否加粗
	        font.setBold(isBold);
	        //字体大小
	        font.setFontHeightInPoints(size);
	        return font;
	    }
}

　　

4. 使用

easypoiUtil.exportExcel2("test.xls", null, fieldd, data, filePath, sheetName);