使用EasyExcel添加Excel数据

109 阅读 0 评论 72 点赞

我是靠谱客的博主体贴睫毛，这篇文章主要介绍使用EasyExcel添加Excel数据，现在分享给大家，希望可以做个参考。

一、导入excel代码
1、pom文件：

复制代码

1
2
3
4
5
6
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>easyexcel</artifactId>
			<version>1.1.2-beta4</version>
		</dependency>

2、实体类：

复制代码

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
@Data
@EqualsAndHashCode(callSuper = true)
public class UploadChildProject extends BaseRowModel{
	//这里index=0是指定顺序
	@ExcelProperty(value = {"序号","项目名称","物业类型","省","市","区"},index = 0)
	private String serialNumber;

	@ExcelProperty(value = {"序号","项目名称","物业类型","省","市","区"},index = 1)
	private String buildingName;

	@ExcelProperty(value = {"序号","项目名称","物业类型","省","市","区"},index = 2)
	private String propertyTypeName;
	private String propertyTypeCode;
	@ExcelProperty(value = {"序号","项目名称","物业类型","省","市","区"},index = 3)
	private String provinceName;
	/**
	 * 省code
	 */
	private String provinceCode;
	@ExcelProperty(value = {"序号","项目名称","物业类型","省","市","区"},index = 4)
	private String cityName;
	/**
	 * 城市code
	 */
	private String cityCode;
	@ExcelProperty(value = {"序号","项目名称","物业类型","省","市","区"},index = 5)
	private String areaName;
	/**
	 * 区code
	 */
	private String areaCode;
	/**
	 * 导入失败行的备注
	 */
	private String remarks;
	/**
	 * 是否已经标注错误原因；true 已标注  false 未标注
	 */
	private boolean flag;
}

3、导入代码：

复制代码

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
/**
	 * 导入
	 * @param file
	 * @return
	 */
	@PostMapping("/file/upload")
	public R importChildProjects(@Param( "file" ) MultipartFile file){
		if (null == file ) {
			R.fail("文件为空，请核对后重新导入！");
		}else if(file.isEmpty()){
			R.fail("文件为空，请核对后重新导入！");
		}
		String originalFilename = file.getOriginalFilename();
		if ( !StringUtils.endsWithIgnoreCase(originalFilename, ExcelTypeEnum.XLSX.getValue())) {
			R.fail("请使用模板导入！");
		}
		List<UploadChildProject> uploadChildProjectList = null;
		try {
			//需要指定sheetNo
			uploadChildProjectList = EasyExcelUtils.readByModel(file.getInputStream(), UploadChildProject.class, 4, 1);
			System.out.println("sheet4"+ JSON.toJSONString(uploadChildProjectList));
		} catch (Exception e) {
			log.error( "导入子项目失败：",e );
		}
		return null;
	}

遇坑：
excel使用wps打开，在隐藏sheet并加上保护工作簿的密码后，导入时会找不到指定的sheetNo；
excel使用office打开，在隐藏sheet并加上保护工作簿的密码后，导入时正常！
都取消保护工作簿的密码后，导入时正常！
总结：wps是免费产品坑多；建议使用office；

二、在已存在的excel中添加数据：

业务需求：需要在已经存在的excel模板中添加数据，并返回给前端；
代码：

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
@GetMapping("/addExcel2")
	public void downloadFile(HttpServletResponse response) throws IOException {
		OutputStream outputStream = null;
		try {
			response.setContentType("application/x-msdownload");
			String name = "xxxx.xlsx";
			// 设置头消息
			response.setHeader("Content-Disposition", "attachment;filename=" + new String(name.getBytes("utf-8"), "iso-8859-1"));
			outputStream = response.getOutputStream();
			Sheet sheet2 = new Sheet(4,3);
			sheet2.setStartRow( 0 );
			EasyExcelUtils.onlineExcel( getLists(data()),sheet2 ,outputStream,nationalExcelMould);
		}catch (Exception e ){
			log.error( "导入错误：",e );
		}finally {
			if (outputStream != null) {
				outputStream.flush();
				outputStream.close();
			}
		}
	}

工具类：

复制代码

import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URL;
import java.util.ArrayList;
import java.util.List;

/***
 * easyExcel文件操作类
 */
public class EasyExcelUtils {

public static void onlineExcel(List<List<String>> data,Sheet sheet,OutputStream out,String url){
        try {
        	//直接读取线上的excel
            InputStream inputStream =  new URL( url ).openStream();
            ExcelWriter writer = EasyExcelFactory.getWriterWithTemp(inputStream,out,ExcelTypeEnum.XLSX,true);
            writer.write0(data, sheet);
            writer.finish();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    public static void writeWithoutHead() throws IOException{
        InputStream inputStream =  Thread.currentThread().getContextClassLoader().getResourceAsStream(""+"area.xlsx" );
        OutputStream out = new FileOutputStream("area2.xlsx");
        ExcelWriter writer = EasyExcelFactory.getWriterWithTemp(inputStream,out,ExcelTypeEnum.XLSX,true);
        //写第一个sheet, sheet1  数据全是List<String> 无模型映射关系
        Sheet sheet1 = new Sheet(4, 3);
        sheet1.setStartRow(0);
        List<List<String>> data = new ArrayList<>();
        List<String> test1 = new ArrayList<>();
        test1.add("123");
        test1.add("1234");
        test1.add("住宅");
        test1.add("福建省");
        test1.add("厦门市");
        test1.add("海沧区");
        data.add(test1);
        writer.write0(data, sheet1);
        writer.finish();
    }
    /**
     * 导出文件
     */
    public static void writeByModel(List<? extends BaseRowModel> data, Sheet sheet,OutputStream outputStream){
        ExcelWriter excelWriter = new ExcelWriter(outputStream, ExcelTypeEnum.XLSX, true);
        excelWriter.write(data,sheet);
        excelWriter.finish();
    }

/**
     * 读取文件
     * @param inputStream 需解析文件的流
     * @param clazz 继承excel文件解析基础类BaseRowModel 的子类
     * @param sheetNo 工作表编号
     * @param headLineMun 行号，从第几行开始解析
     * @return
     */
    public static List readByModel(InputStream inputStream,Class<? extends BaseRowModel> clazz,int sheetNo, int headLineMun){
        ExcelTypeEnum excelTypeEnum = ExcelTypeEnum.valueOf(inputStream);
        // 解析每行结果在listener中处理
        EasyExcelListener<?> listener = new EasyExcelListener<>();
        ExcelReader excelReader = new ExcelReader(inputStream, excelTypeEnum, null, listener);
        //默认只有一列表头
        excelReader.read(new Sheet(sheetNo,headLineMun,clazz));
        return  listener.getDataList();
    }

/**
     * 多sheet导出文件
     */
    public static void writeMultiSheetByModel(List<? extends BaseRowModel> data, List<Sheet> sheets,OutputStream outputStream){
        ExcelWriter excelWriter = new ExcelWriter(outputStream, ExcelTypeEnum.XLSX, true);
        sheets.forEach(sheet -> {
            excelWriter.write(data,sheet);
        });
        excelWriter.finish();
    }

/**
     * 多sheet读取文件
     * @return
     */
    public static EasyExcelListener readMultiSheetByModel(InputStream inputStream,Class<? extends BaseRowModel> clazz,ExcelTypeEnum excelTypeEnum, int headLineMun){
        // 解析每行结果在listener中处理
        EasyExcelListener<?> listener = new EasyExcelListener<>();
        ExcelReader excelReader = new ExcelReader(inputStream, excelTypeEnum, null, listener);
        List<Sheet> sheets = excelReader.getSheets();
        sheets.forEach(sheet -> {
            sheet.setHeadLineMun(headLineMun);
            sheet.setClazz(clazz);
            excelReader.read(sheet);
        });
        return  listener;
    }

/**
     * 获取ExcelReader
     * @return
     */
    public static ExcelReader getExcelReader(InputStream inputStream, ExcelTypeEnum excelTypeEnum,EasyExcelListener listener){
        // 解析每行结果在listener中处理
        ExcelReader excelReader = new ExcelReader(inputStream, excelTypeEnum, null, listener);
        return excelReader;
    }
}

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
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URL;
import java.util.ArrayList;
import java.util.List;

/***
 * easyExcel文件操作类
 */
public class EasyExcelUtils {

    public static void onlineExcel(List<List<String>> data,Sheet sheet,OutputStream out,String url){
        try {
        	//直接读取线上的excel
            InputStream inputStream =  new URL( url ).openStream();
            ExcelWriter writer = EasyExcelFactory.getWriterWithTemp(inputStream,out,ExcelTypeEnum.XLSX,true);
            writer.write0(data, sheet);
            writer.finish();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    public static void writeWithoutHead() throws IOException{
        InputStream inputStream =  Thread.currentThread().getContextClassLoader().getResourceAsStream(""+"area.xlsx" );
        OutputStream out = new FileOutputStream("area2.xlsx");
        ExcelWriter writer = EasyExcelFactory.getWriterWithTemp(inputStream,out,ExcelTypeEnum.XLSX,true);
        //写第一个sheet, sheet1  数据全是List<String> 无模型映射关系
        Sheet sheet1 = new Sheet(4, 3);
        sheet1.setStartRow(0);
        List<List<String>> data = new ArrayList<>();
        List<String> test1 = new ArrayList<>();
        test1.add("123");
        test1.add("1234");
        test1.add("住宅");
        test1.add("福建省");
        test1.add("厦门市");
        test1.add("海沧区");
        data.add(test1);
        writer.write0(data, sheet1);
        writer.finish();
    }
    /**
     * 导出文件
     */
    public static void writeByModel(List<? extends BaseRowModel> data, Sheet sheet,OutputStream outputStream){
        ExcelWriter excelWriter = new ExcelWriter(outputStream, ExcelTypeEnum.XLSX, true);
        excelWriter.write(data,sheet);
        excelWriter.finish();
    }

    /**
     * 读取文件
     * @param inputStream 需解析文件的流
     * @param clazz 继承excel文件解析基础类BaseRowModel 的子类
     * @param sheetNo 工作表编号
     * @param headLineMun 行号，从第几行开始解析
     * @return
     */
    public static List readByModel(InputStream inputStream,Class<? extends BaseRowModel> clazz,int sheetNo, int headLineMun){
        ExcelTypeEnum excelTypeEnum = ExcelTypeEnum.valueOf(inputStream);
        // 解析每行结果在listener中处理
        EasyExcelListener<?> listener = new EasyExcelListener<>();
        ExcelReader excelReader = new ExcelReader(inputStream, excelTypeEnum, null, listener);
        //默认只有一列表头
        excelReader.read(new Sheet(sheetNo,headLineMun,clazz));
        return  listener.getDataList();
    }

    /**
     * 多sheet导出文件
     */
    public static void writeMultiSheetByModel(List<? extends BaseRowModel> data, List<Sheet> sheets,OutputStream outputStream){
        ExcelWriter excelWriter = new ExcelWriter(outputStream, ExcelTypeEnum.XLSX, true);
        sheets.forEach(sheet -> {
            excelWriter.write(data,sheet);
        });
        excelWriter.finish();
    }

    /**
     * 多sheet读取文件
     * @return
     */
    public static EasyExcelListener readMultiSheetByModel(InputStream inputStream,Class<? extends BaseRowModel> clazz,ExcelTypeEnum excelTypeEnum, int headLineMun){
        // 解析每行结果在listener中处理
        EasyExcelListener<?> listener = new EasyExcelListener<>();
        ExcelReader excelReader = new ExcelReader(inputStream, excelTypeEnum, null, listener);
        List<Sheet> sheets = excelReader.getSheets();
        sheets.forEach(sheet -> {
            sheet.setHeadLineMun(headLineMun);
            sheet.setClazz(clazz);
            excelReader.read(sheet);
        });
        return  listener;
    }

    /**
     * 获取ExcelReader
     * @return
     */
    public static ExcelReader getExcelReader(InputStream inputStream, ExcelTypeEnum excelTypeEnum,EasyExcelListener listener){
        // 解析每行结果在listener中处理
        ExcelReader excelReader = new ExcelReader(inputStream, excelTypeEnum, null, listener);
        return excelReader;
    }
}