前言

做OA管理系统的时候，刚好有个需求：根据指定条件从MYSQL数据库获取列表，将列表生成Excel，并直接下载到本地
工具及插件
项目框架：SpringBoot + Mybatis + MySQL
第三方插件：Lombok
项目引用的是Apache poi生成EXCEL文件

最终导出结果会是这样的，如图：

前端调用

1
2
3
4
$("#button").click(function(){
    let params = let params =  "?city = '北京'&street = '西长安街'";
    window.location.href = "http://127.0.0.1:8080/excel/exportExcel" + params;
});

后端接口

代码中引用了lombok插件，直接使用注解的方式
Result类 是我自己封装的一个返回结果类

引入Maven依赖

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<!-- poi -->
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>3.17</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.17</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-scratchpad</artifactId>
	<version>3.17</version>
</dependency>

控制层Controller

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
import javax.servlet.http.HttpServletResponse;
import java.util.Map;
 
@RestController
@RequestMapping("excel")
public class ExcelController {
 
	@Autowired
    private ExcelService excelService;
 
	/**
	 * 批量导出
	 */
	@RequestMapping("/exportExcel")
	public Result<?> exportExcel(@RequestParam Map<String, Object> params, HttpServletResponse response) {
		try{
			excelService.exportExcel(params, response);
			return Result.succeed("导出成功");
		}catch (Exception e) {
			e.printStackTrace();
			return Result.failed("导出异常");
		}
	}
}

业务逻辑层

1
2
3
4
5
6
/**
 * 业务逻辑 接口
 */
public interface ExcelService {
	public void exportExcel(Map<String, Object> params, HttpServletResponse response);
}

数据库数据处理的时候，逻辑上可能会比较繁琐，最终都是以字符串的形式存入Excel

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
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import javax.servlet.http.HttpServletResponse;
import java.text.SimpleDateFormat;
import java.util.*;
 
/**
 * 业务逻辑 实现类
 */
@Service
public class excelServiceImpl implements HrInterviewService {
	
	// 数据库调用接口
	@Autowired
    private PersonDao personDao;
 
	@Override
    public void exportExcel(Map<String, Object> params,HttpServletResponse response) {
        // 列名，表格第一行
        List<String> rowNameList = Arrays.asList("日期", "城市", "街道", "姓名", "性别", "年龄", "联系方式");
 
        // 获取数据库数据，查询XXX城市的XXX街道人口信息列表
        List<Person> dataList = personDao.getPersonList(params);
 
        /** 数据库数据整合 */
        // 列名 数据
        List<Map<String, String>> list = new ArrayList<>();
		// SimpleDateFormat时间格式
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
		// 循环填表
        for (Person item : dataList) {
            Map<String, String> map = new HashMap<>();
			// 日期
            if (item.getNowDate() != null){
                map.put("日期", sdf.format(item.getNowDate()));
            }else {
                map.put("日期", "");
            }
			// 城市
			map.put("城市", item.getCity() + "");
			// 街道
			map.put("街道", item.getStreet() + "");
			// 姓名
            map.put("姓名", item.getUserName() + "");
			// 性别，数据库用01区分男女
            if (item.getSex() != null && !"".equals(item.getSex())){
                if (item.getSex() == 0){
                    map.put("性别", "女");
                }else {
                    map.put("性别", "男");
                }
            }else {
                map.put("性别", "");
            }
			// 年龄
            if (item.getAge() != null){
                map.put("年龄", item.getAge()+"");
            }else {
                map.put("年龄", "");
            }
			// 联系方式
            map.put("联系方式", item.getTelephone());
            list.add(map);
        }
        // 将需要写入Excel的数据传入
        ExcelUtils.excelPort("人口信息记录表", rowNameList, list, response);
    }
}

数据持久层

1
2
3
4
5
6
@Repository
@Mapper
public interface PersonDao {
	@Select("SELECT * FROM person WHERE city = #{params.city} AND street = #{params.street}")
    List<Person> getPersonList(@Param("params") Map<String, Object> params);
}

实体类层

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
import com.fasterxml.jackson.annotation.JsonFormat;
import org.springframework.format.annotation.DateTimeFormat;
import lombok.Data;
import java.io.Serializable;
import java.util.Date;
 
@Data
public class Person implements Serializable{
    private static final long serialVersionUID = 1L;
    // id
    private Integer id;
	// 日期
    @DateTimeFormat(pattern = "yyyy-MM-dd")
    @JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd")
    private Date nowDate;
	// 城市
    private String city;
	// 街道
    private String street;
	// 姓名
    private String userName;
	// 性别
    private Integer sex;
	// 年龄
    private Integer age;
	// 联系方式
    private String telephone;
}

工具类

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
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.prefs.BackingStoreException;
 
/**
 * Excel工具类
 */
public class ExcelUtils {
 
    /**
     * @param sheetName     工作表名，文件名，头部信息
     * @param rowNameList   列名
     * @param list          需要写入的数据
     * @param response      返回
     */
    public static void excelPort(String sheetName, List<String> rowNameList, List<Map<String, String>> list, HttpServletResponse response) {
        try {
            if (list.size() == 0) {
                throw new BackingStoreException("数据为空");
            }
            // 声明一个工作簿
            XSSFWorkbook wb = new XSSFWorkbook();
            // 创建sheet页
            XSSFSheet sheet = wb.createSheet(sheetName);
            sheet.setDefaultColumnWidth(19);
 
            // 全局加线样式
            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setBorderBottom(BorderStyle.THIN);//下边框
            cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
            cellStyle.setBorderTop(BorderStyle.THIN);//上边框
            cellStyle.setBorderRight(BorderStyle.THIN);//右边框
            cellStyle.setAlignment(HorizontalAlignment.CENTER);//居中
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中
 
            // 记录标题信息
            TreeMap<String, Integer> headMap = new TreeMap<>();
 
            // 标题写入
            XSSFRow row = sheet.createRow(0);
            for (int i = 0; i < rowNameList.size(); i++) {
                row.setHeight((short) 450);
                XSSFCell cell = row.createCell(i);
                String headName = rowNameList.get(i);
                cell.setCellValue(headName); //写入列名
                headMap.put(headName, i);
                cell.setCellStyle(cellStyle);
            }
 
            // 写入内容数据
            int ind = 1;
            for (Map<String, String> map : list) {
                XSSFRow r = sheet.createRow(ind++);
                for (Map.Entry<String, Integer> m : headMap.entrySet()) {
                    String name = m.getKey(); // 列名
                    String value = map.get(name); // value 不一定存在
                    XSSFCell cell2 = r.createCell(m.getValue());
                    if (value != null) {
                        cell2.setCellValue(value);
                    }
                    cell2.setCellStyle(cellStyle);
                }
            }
 
            // 输出Excel文件
            OutputStream output = response.getOutputStream();
            response.reset();
            // 设置文件头
            response.setHeader("Content-Disposition",
                    "attchement;filename=" + new String((sheetName + ".xls").getBytes("gb2312"), "ISO8859-1"));
            response.setContentType("application/msexcel");
            wb.write(output);
            wb.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}