【用java实现Excel表格的导入导出】

easypoi（生成Excel表格）

1.基本注解

@ExcelTarget("user")					//设置表头

@Excel(name = "姓名"，orderNum = "2")		//设置列名

@Excel(name = "编号"，orderNum = "1")		//orderNum 排序，数值越小越靠前，默认为0

@Excel(name = "性别"，orderNum = "3"，replace = {"男_0","女_1"})	//replace 替换 0替换男

@Excel(name = "生日"，orderNum = "4"，width = 100) 		//width 设置宽度	

@Excel(name = "日期"，orderNum = "5"，format = "yyyy年MM月dd日") 	//format 日期格式化

@Excel(name = "百分比"，orderNum = "4"，suffix = "%") 		//suffix 添加尾缀

@ExcelIgnore			//忽略

@ExcelEntity		//表示该属性是一个实体类	
private Card card;				

user,setCard（new Card("001","武汉市")）


@ExcelCollection(name = "订单信息")		//表示该属性是一个集合
private List<Order> orders;

user.setOrder(Arrays.asList(new Order("01","小米")，new Order("02","华为")))

2.导出

1.导入起步依赖

<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-spring-boot-starter</artifactId>
    <version>4.0.0</version>
</dependency>

允许定义相同的bean对象 去覆盖原有的

spring:
  main:
    allow-bean-definition-overriding: true

2.编写实体类 序列化 并 配置注解

User

@Data
@NoArgsConstructor
@AllArgsConstructor
@ExcelTarget("用户列表")
public class User implements Serializable {

    @Excel(name = "编号",orderNum = "1")
    private Integer id;

    @Excel(name = "姓名",orderNum = "2")
    private String name;

    @Excel(name = "年龄",orderNum = "4",suffix = "岁")
    private Integer age;

    @Excel(name="性别",orderNum = "3")
    private String gender;

    @Excel(name="生日",orderNum = "5",format = "yyyy年MM月dd日",width = 40)
    private Date birthday;

    @ExcelEntity
    private Details details;

    @ExcelCollection(name = "订单信息",orderNum = "10")
    private List<Order> orderList;
}

Details

@Data
@NoArgsConstructor
@AllArgsConstructor
@ExcelTarget("用户细节")
public class Details implements Serializable {

    @Excel(name="用户名",orderNum = "6")
    private String username;

    @Excel(name="密码",orderNum = "7")
    private String password;
}

Order

@Data
@NoArgsConstructor
@AllArgsConstructor
@ExcelTarget("订单")
public class Order implements Serializable {

    @Excel(name="订单编号",orderNum = "8")
    private String id;

    @Excel(name="商品名称",orderNum = "9")
    private String name;
}

3.编写配置类

package com.fy.demo;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import org.apache.poi.ss.usermodel.Workbook;
import java.io.File;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

public class ExportDemo {
    public static void main(String[] args) throws Exception{

        //1.导出设置
        ExportParams exportParams = new ExportParams();
        exportParams.setTitle("用户列表");          //设置表格头
        exportParams.setSheetName("用户列表sheet");  //设置表名

        //2.数据准备
        List<User> users = new ArrayList<User>(){{
            add(new User(1,"张三",89,"男",new Date(),new Details("三三","123"),Arrays.asList(new Order("0100","小米"),new Order("0120","小米10"))));
            add(new User(2,"李四",78,"男",new Date(),new Details("ss","123"),Arrays.asList(new Order("0200","华为"),new Order("0220","华为60"))));
            add(new User(3,"王五",67,"男",new Date(),new Details("ww","123"),Arrays.asList(new Order("0300","vivo"),new Order("0330","vivox21"))));
        }};

        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, User.class, users);

        //3.文件输出
        FileOutputStream fileOutputStream = new FileOutputStream(
                new File("C:\\Users\\方子琰\\OneDrive\\桌面\\sprint-vue-kaoshixitong\\user.xls")
        );
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        workbook.close();
    }
}

3.导入

1.编写映射 的实体类

...

2.编写配置类

package com.fy.demo;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import java.io.File;
import java.io.FileInputStream;
import java.util.List;

public class ImportDemo {
    public static void main(String[] args) throws Exception{

        //1.文件输入流
        FileInputStream fileInputStream = new FileInputStream(
                new File("C:\\Users\\方子琰\\OneDrive\\桌面\\sprint-vue-kaoshixitong\\user.xls")
        );

        ImportParams importParams = new ImportParams();
        importParams.setTitleRows(1); //指定标题占用行数
        importParams.setHeadRows(2);  //指定表头占用行数
        importParams.setSheetNum(1);

        List<User> userList = ExcelImportUtil.importExcel(fileInputStream, User.class, importParams);

        userList.forEach(System.out::println);
    }
}

4.试题导出功能

 //导出
    @GetMapping("export")
    public void export(HttpServletResponse response)throws  Exception{
        //获取数据
        List<SysUser> users = userMapper.findAll();

        //导出
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("用户信息", "user_list")
            , SysUser.class, users);

        //设置响应
        response.setHeader("content-disposition","attachment;fileName="+ URLEncoder.encode("用户列表.xls","UTF-8"));
        response.setContentType("application/vnd.ms-excel;charset=utf-8");

        ServletOutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);

        outputStream.close();
        workbook.close();

    }