EasyExcel的导入导出和EasyExcel手动输入表头和表内容_easyexcel导入跳过表头

作者：pnstu | 2024-02-05 15:45:55

踩

easyexcel导入跳过表头

EasyExcel

1.导入(数据为json)

1.导入头

public void saveHeadData(PageData pd, MultipartFile file)  throws Exception{
            //easyExcel 进行保存excel的头数据
            EasyExcelHeadListener easyExcelListener = new EasyExcelHeadListener(dynamicExcelMapper, pd);
            EasyExcel.read(file.getInputStream(), easyExcelListener).sheet(0).doRead();
    }
1
2
3
4
5

package com.ruoyi.project.common.easyexcel;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.serializer.SerializerFeature;
import com.ruoyi.common.utils.PageData;
import com.ruoyi.project.business.mapper.DynamicExcelMapper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Create on 2021/8/2.
 *
 * @author Jaime
 * @Description:
 */
public class EasyExcelHeadListener extends AnalysisEventListener {

    private static final Logger logger = LoggerFactory.getLogger(EasyExcelHeadListener.class);

    //用list集合保存解析到的结果
    private List<Map<Integer, Map<Integer, String>>> list;

    private PageData pd;

    //不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
    private DynamicExcelMapper dynamicExcelMapper;

    //初始化
    public EasyExcelHeadListener(DynamicExcelMapper dynamicExcelMapper, PageData pd) {
        this.dynamicExcelMapper = dynamicExcelMapper;
        this.pd = pd;
        this.list = new ArrayList<>();
    }

    @Override
    public void invokeHeadMap(Map headMap, AnalysisContext context) {
        logger.info("解析到一条头数据：{}, ", JSON.toJSONString(headMap));
        try{
            //保存头数据到数据库( SerializerFeature.QuoteFieldNames:输出key默认带双引号)
            pd.put("head", JSON.toJSONString(headMap, SerializerFeature.QuoteFieldNames));
            saveHeadData(pd);
        }catch(Exception e){
            logger.info(e.getMessage());
             e.printStackTrace();
        }
    }

    @Override
    public void invoke(Object data, AnalysisContext context) {
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
    }

    /**
     * 保存头数据
     */
    private void saveHeadData(PageData pd) throws Exception {
        //判断数据库中是否有
        Integer num = dynamicExcelMapper.getDynamicExcelId(pd);
        if (num != null) {
            dynamicExcelMapper.updateHeadData(pd);
        }else {
            dynamicExcelMapper.saveHeadData(pd);
        }
    }
}

    <insert id="saveHeadData" parameterType="pd" useGeneratedKeys="true" keyProperty="bid">
        insert into dynamic_excel(
        <if test="head != null">head,</if>
        <if test="proj_id != null">bus_id,</if>
        creater,createtime
        )values
        (
        <if test="head != null and head != ''">#{head},</if>
        <if test="proj_id != null and head != ''">#{dynamic_excelid},</if>
        #{creater},now()
        )
    </insert>
        
    <update id="updateHeadData" parameterType="pd">
        update dynamic_excel
        <trim prefix="SET" suffixOverrides=",">
            <if test="head != null">head = #{head},</if>
        </trim>
        where bus_id = #{dynamic_excelid}
    </update>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95

2.导入内容

    public void saveContentData(PageData pd, MultipartFile file) throws Exception{
        //easyExcel 进行保存excel的内容
        EasyExcelBodyListener easyExcelListener = new EasyExcelBodyListener(dynamicExcelContentMapper, pd);
        EasyExcel.read(file.getInputStream(), easyExcelListener).sheet(0).doRead();
    }
1
2
3
4
5

package com.ruoyi.project.common.easyexcel;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.serializer.SerializerFeature;
import com.ruoyi.common.utils.PageData;
import com.ruoyi.project.business.mapper.DynamicExcelContentMapper;
import lombok.SneakyThrows;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Create on 2021/8/2.
 *
 * @author Jaime
 * @Description:
 */
public class EasyExcelBodyListener extends AnalysisEventListener {

    private static final Logger logger = LoggerFactory.getLogger(EasyExcelBodyListener.class);

    //保存数据内容
    private List<Map<String, String>> contentList;

    //每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
    private static final int BATCH_COUNT = 50;

    private PageData pd;

    //不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
    private DynamicExcelContentMapper dynamicExcelContentMapper;

    //初始化
    public EasyExcelBodyListener(DynamicExcelContentMapper dynamicExcelContentMapper, PageData pd) {
        this.dynamicExcelContentMapper = dynamicExcelContentMapper;
        this.pd = pd;
        this.contentList = new ArrayList<>();
    }

    @Override
    @SneakyThrows
    public void invoke(Object data, AnalysisContext context) {
        Map<String, String> content = new HashMap<>();
        content.put("bid", pd.get("bid") + "");
        content.put("content", JSON.toJSONString(data, SerializerFeature.QuoteFieldNames));
        contentList.add(content);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (contentList.size() > BATCH_COUNT) {
            saveContentData();
            contentList.clear();
        }
    }

    /**
     * 解析到最后会进入这个方法，需要重写这个doAfterAllAnalysed方法，然后里面调用自己定义好保存方法
     * @param context
     */
    @SneakyThrows
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
//         这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveContentData();
        logger.info("所有数据解析完成！");
    }

    /**
     * 加上存储数据库
     */
    private void saveContentData() throws Exception{
        dynamicExcelContentMapper.saveContentData(contentList);
    }
}

    <insert id="saveContentData" parameterType="List">
        INSERT INTO `dynamic_excel_content`(bid, content) VALUES
         <foreach collection="list" index="index" item="item" separator=",">
             <foreach collection="item" index="key" item="value" open="(" close=")" separator=",">
                 #{value}
             </foreach>
         </foreach>
     </insert>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87

3.添加头({“0”:“年龄”,“1”:“姓名”})

JSONObject head = (JSONObject) pd.get("head");
pd.put("head", head.toString());
dynamicExcelService.saveHeadData(pd);
1
2
3

4.添加内容({“0”:“18”,“1”:“大概”})

	@Transactional(rollbackFor = Exception.class)
    public void saveContentData(PageData pd) throws Exception{
        //保存数据内容
        List<Map<String, String>> contentList = new ArrayList<>();
        Map<String, String> content = null;
        //获取数据(JSONArray里是JSONObject)
        JSONArray contentArray = (JSONArray) pd.get("content");
        //把Json转为String
        for (Object data : contentArray) {
            content = new HashMap<>();
            content.put("bid", pd.get("bid") + "");
            content.put("content", JSON.toJSONString(data, SerializerFeature.QuoteFieldNames));
            contentList.add(content);
        }
        //判断数据库中是否有表单内容
        int num = dynamicExcelContentMapper.getDynamicExcelContentIdCount(pd);
        if (num > 0) {
            //先删再重新添加
            dynamicExcelContentMapper.deleteDynamicExcelContent(pd);
            dynamicExcelContentMapper.saveContentData(contentList);
        }else {
            dynamicExcelContentMapper.saveContentData(contentList);
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

2.导出(和web导出)

1.导出excel

//导出excel
public String DownloadServer(String path, String fileName, HorizontalCellStyleStrategy customStyle, List<List<String>> head, List<List<String>> data) {
		File newFile = new File(path + File.separator + fileName);

		if (!newFile.getParentFile().exists())//若父路径不存在，则创建
			newFile.getParentFile().mkdirs();

			//导出excel
    EasyExcel.write(path + File.separator + fileName)
        .registerWriteHandler(new CustomCellWriteHandler())
        .registerWriteHandler(customStyle)
        .head(head)
        .sheet()
        .doWrite(data);
	}
	
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

2.web导出excel

	public void ExcelDownload(String wordFilePath, String name, HorizontalCellStyleStrategy customStyle, List<List<String>> head, List<List<String>> data, HttpServletResponse response) {
		try{
			File newFilePath = new File(wordFilePath);
			newFilePath.mkdirs();

			//导出exceld
			response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
			response.setCharacterEncoding("utf-8");
			// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
			String fileName = URLEncoder.encode(name, "UTF-8").replaceAll("\\+", "%20");
			response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
			EasyExcel.write(response.getOutputStream())
					.head(head)
					.registerWriteHandler(new CustomCellWriteHandler())
					.registerWriteHandler(customStyle)
					.sheet()
					.doWrite(data);
		}catch(Exception e){
		     e.printStackTrace();
		}
	}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

head/data(head和data是一对多的关系)

 private List<List<String>> head(List<PageData> list) {
        List<List<String>> returnList = new ArrayList<>();
        JSONObject head = JSON.parseObject(list.get(0).getString("head"));

        List<String> headData;
        for (int i = 0, len = head.size(); i < len; i++) {
            headData = new ArrayList<>();
            headData.add(head.getString(String.valueOf(i)));
            returnList.add(headData);
        }
        return returnList;
    }

    private  List<List<String>> data(List<PageData> list) {
        List<List<String>> returnList = new ArrayList<>();
        List<PageData> contentList = (List)list.get(0).get("contentList");

        for (int i = 0, len = contentList.size(); i < len; i++) {
            JSONObject content = JSON.parseObject(contentList.get(i).getString("content"));
            List<String> dataList = new ArrayList<>();
            for (int j = 0, leng = content.size(); j < leng; j++) {
                dataList.add(content.getString(String.valueOf(j)));
            }
            returnList.add(dataList);
        }
        return returnList;
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

customStyle(表头和内容风格)

 private HorizontalCellStyleStrategy getCustomStyle() {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景色设置
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short)14);
        headWriteFont.setFontName("宋体");
        headWriteCellStyle.setWriteFont(headWriteFont);
        //自动换行
        headWriteCellStyle.setWrapped(false);
        // 水平对齐方式
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 垂直对齐方式
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontName("宋体");
        contentWriteFont.setFontHeightInPoints((short)13);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        return horizontalCellStyleStrategy;
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

CustomCellWriteHandler(自动设置列宽)

package com.ruoyi.project.common.easyexcel;

import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.CollectionUtils;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class CustomCellWriteHandler extends AbstractColumnWidthStyleStrategy {

//    @Override
//    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head,
//                                  Integer relativeRowIndex, Boolean isHead) {
//        // 简单设置
//        Sheet sheet = writeSheetHolder.getSheet();
//        sheet.setColumnWidth(cell.getColumnIndex(), 5000);
//    }

    private static final int MAX_COLUMN_WIDTH = 255;
    //因为在自动列宽的过程中，有些设置地方让列宽显得紧凑，所以做出了个判断
    private static final int COLUMN_WIDTH = 20;
    private  Map<Integer, Map<Integer, Integer>> CACHE = new HashMap(8);

    public CustomCellWriteHandler() {
    }

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
        if (needSetWidth) {
            Map<Integer, Integer> maxColumnWidthMap = (Map)CACHE.get(writeSheetHolder.getSheetNo());
            if (maxColumnWidthMap == null) {
                maxColumnWidthMap = new HashMap(16);
                CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
            }

            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
            if (columnWidth >= 0) {
                if (columnWidth > MAX_COLUMN_WIDTH) {
                    columnWidth = MAX_COLUMN_WIDTH;
                }else {
                    if(columnWidth<COLUMN_WIDTH){
                        columnWidth =columnWidth*2;
                    }
                }

                Integer maxColumnWidth = (Integer)((Map)maxColumnWidthMap).get(cell.getColumnIndex());
                if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                    ((Map)maxColumnWidthMap).put(cell.getColumnIndex(), columnWidth);
                    writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(),  columnWidth* 256);
                }
            }
        }
    }

    private  Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            return cell.getStringCellValue().getBytes().length;
        } else {
            CellData cellData = (CellData)cellDataList.get(0);
            CellDataTypeEnum type = cellData.getType();
            if (type == null) {
                return -1;
            } else {
                switch(type) {
                    case STRING:
                        return cellData.getStringValue().getBytes().length;
                    case BOOLEAN:
                        return cellData.getBooleanValue().toString().getBytes().length;
                    case NUMBER:
                        return cellData.getNumberValue().toString().getBytes().length;
                    default:
                        return -1;
                }
            }
        }
    }

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85

1.//创建一个流，等待写入excel文件内容
				ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
				ExcelWriter excelWriter = EasyExcel.write(byteArrayOutputStream).build();
				WriteSheet writeFail = EasyExcel.writerSheet(0, "Sheet1").head(CustomerReturnsVO.class).build();
				excelWriter.write(list, writeFail);
				excelWriter.finish();
				InputStream inputStream = new ByteArrayInputStream(byteArrayOutputStream.toByteArray());
				try {
					remoteFileService.save(inputStream, CommonUtil.getFileNodeUrl(filenodePath));
				} catch (IOException e) {
					log.error(message + "上传文件失败,原因: " + e.getMessage() , e);
				}
1
2
3
4
5
6
7
8
9
10
11
12

声明：本文内容由网友自发贡献，转载请注明出处：【wpsshop】