java poi批量导出excel表格

1.前言

现在提供了很多方便的框架操作excel进行导入导出。
框架的底层还是对poi框架进行了封装。

2.项目环境

前端使用的是layui框架，后台是springboot框架，部署在阿里云linux服务器上。

3.maven依赖

导入maven 依赖

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-examples -->
	<dependency>
	    <groupId>org.apache.poi</groupId>
	    <artifactId>poi-examples</artifactId>
	    <version>4.1.2</version>
	</dependency>
	
	<dependency>
	    <groupId>org.apache.poi</groupId>
	    <artifactId>poi</artifactId>
	    <version>4.1.2</version>
	</dependency>
	
	<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
	<dependency>
	    <groupId>org.apache.poi</groupId>
	    <artifactId>poi-ooxml</artifactId>
	    <version>4.1.2</version>
	</dependency>
	
	<dependency>
	    <groupId>org.apache.poi</groupId>
	    <artifactId>poi-ooxml-schemas</artifactId>
	    <version>4.1.2</version>
	</dependency>
	
	<dependency>
	    <groupId>org.apache.poi</groupId>
	    <artifactId>poi-excelant</artifactId>
	    <version>4.1.2</version>
	</dependency>
	
	<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-scratchpad -->
	<dependency>
	    <groupId>org.apache.poi</groupId>
	    <artifactId>poi-scratchpad</artifactId>
	    <version>4.1.2</version>
	</dependency>
	
	<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-math3 -->
	<dependency>
	    <groupId>org.apache.commons</groupId>
	    <artifactId>commons-math3</artifactId>
	    <version>3.6.1</version>
	</dependency>

4. 代码演示

1.首先创建工作簿，相当于整体excel文件,是整个文件的骨架

	private HSSFWorkbook workbook = new HSSFWorkbook();

2.再通过workbook 对象 创建sheet对象

 private HSSFSheet sheet = workbook.createSheet();

对应于excel表格的工作空间

3.接下来就到数据单元的绘制
可以分为两个部分：
表头：数据是固定的
列数据：是动态数据
每个数据都由一个单元格组成，一行可以有多个单元格

3.1.创建表头

	private static final String[] HEAD_NAME = {"id","姓名","公司","职位","电话","邮箱","更多信息"};

	.....省略
	
		//===========表头操作=====================
        for (int i = 0; i < HEAD_NAME.length; i++) {
        	//设置宽度
            sheet.setColumnWidth(i,4000);
            if (i == 0) {
                //创建一行表头
                headRow = sheet.createRow(i);
                headRow.setHeight((short) 800);
            }
            //创建单元格数据
            HSSFCell cell = headRow.createCell(i);
            //给单元格赋值
            cell.setCellValue(HEAD_NAME[i]);
        }

3.2 绘制数据

			//===========数据操作=====================
        for (int i = 0; i < appointment.size(); i++) {
        	//从i+1行开始绘制
            HSSFRow row = sheet.createRow(i+1);
            
            //jackson反序列化
            Appointment value = objectMapper.convertValue(appointment.get(i), Appointment.class);
            
			//为每个数据创建一个单元格
            HSSFCell cell = row.createCell(0);
            cell.setCellValue(value.getAId());

            HSSFCell cell2 = row.createCell(1);
            cell2.setCellValue(value.getName());

            HSSFCell cell3 = row.createCell(2);
            cell3.setCellValue(value.getCompany());

            HSSFCell cell4 = row.createCell(3);
            cell4.setCellValue(value.getPost());

            HSSFCell cell5 = row.createCell(4);
            cell5.setCellValue(value.getPhone());

            HSSFCell cell6 = row.createCell(5);
            cell6.setCellValue(value.getEmail());

            HSSFCell cell7 = row.createCell(6);
            cell7.setCellValue(value.getMessage());
        }

5.整体代码

controller层代码

	@PostMapping("/export")
    public Object export(@RequestParam String appointment){
        HashMap result = new HashMap();
        try {
            ObjectMapper mapper = new ObjectMapper();
            List list = mapper.readValue(appointment,List.class);
            String fileName = new ExcelUtil().export(list, mapper);
            if (!fileName.isEmpty()) {
            	//必须将路径返回给前端，才能配合前端下载到本地
                result.put("msg","excel/"+fileName);
                result.put("code", 200);
                result.put("desc", "导出成功");
                return result;
            }
        } catch (Exception e) {
            logger.error(e.toString());
        }
        result.put("code", 500);
        result.put("desc", "导出失败");
        return result;
    }

业务层代码

package com.hniu.zs_manage.utils;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.hniu.zs_manage.entity.appointment.Appointment;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.slf4j.Logger;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Component;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;

/**
 * @Description: excel 导出工具类
 * @Author: songbiao
 */
@Component
public class ExcelUtil {

    private static final String[] HEAD_NAME = {"id","姓名","公司","职位","电话","邮箱","更多信息"};

    private static FileOutputStream FILE_OUTPUT_STREAM = null;

    private static Logger logger = LogUtil.getLOG(ExcelUtil.class);

    private static final File FILE_URL = new File("/cbim/ManagerSystem/excel");

    private static String fileName = "";

    private HSSFWorkbook workbook = new HSSFWorkbook();

    private HSSFSheet sheet = workbook.createSheet();

    private static HSSFFont headFont = null;
    private static HSSFCellStyle headStyle = null;
    private static HSSFFont dataFont = null;
    private static HSSFCellStyle dataStyle = null;

    private static HSSFRow headRow = null;

    public ExcelUtil() {
        //头字体样式
        HSSFFont headFont = workbook.createFont();
        headFont.setFontName("宋体");
        headFont.setFontHeightInPoints((short) 18);

        //列头
        HSSFCellStyle headStyle = workbook.createCellStyle();
        headStyle.setFont(headFont);
//        headStyle.setFillBackgroundColor(IndexedColors.YELLOW.getIndex());
        //新版本FillPatternType
        headStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
        headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headStyle.setAlignment(HorizontalAlignment.CENTER);
        headStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        //数字字体样式
        HSSFFont dataFont = workbook.createFont();
        dataFont.setFontName("宋体");
        dataFont.setFontHeightInPoints((short) 16);

        //数据样式
        HSSFCellStyle dataStyle = workbook.createCellStyle();
        dataStyle.setFont(dataFont);
        dataStyle.setAlignment(HorizontalAlignment.LEFT);
        dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);

       //===========表头操作=====================
        for (int i = 0; i < HEAD_NAME.length; i++) {
        	//设置宽度
            sheet.setColumnWidth(i,4000);
            if (i == 0) {
                //创建一行表头
                headRow = sheet.createRow(i);
                headRow.setHeight((short) 800);
            }
            //创建单元格数据
            HSSFCell cell = headRow.createCell(i);
            //给单元格赋值
            cell.setCellValue(HEAD_NAME[i]);
        }
    }

    public String export(List appointment,ObjectMapper objectMapper) throws IOException {

        FILE_OUTPUT_STREAM = new FileOutputStream(fileCheck(FILE_URL));

        //===========数据操作=====================
        for (int i = 0; i < appointment.size(); i++) {
            HSSFRow row = sheet.createRow(i+1);
            //为每个数据创建一个单元格
            Appointment value = objectMapper.convertValue(appointment.get(i), Appointment.class);
            HSSFCell cell = row.createCell(0);
            cell.setCellStyle(dataStyle);
            cell.setCellValue(value.getAId());

            HSSFCell cell2 = row.createCell(1);
            cell2.setCellStyle(dataStyle);
            cell2.setCellValue(value.getName());

            HSSFCell cell3 = row.createCell(2);
            cell3.setCellStyle(dataStyle);
            cell3.setCellValue(value.getCompany());

            HSSFCell cell4 = row.createCell(3);
            cell4.setCellStyle(dataStyle);
            cell4.setCellValue(value.getPost());

            HSSFCell cell5 = row.createCell(4);
            cell5.setCellStyle(dataStyle);
            cell5.setCellValue(value.getPhone());

            HSSFCell cell6 = row.createCell(5);
            cell6.setCellStyle(dataStyle);
            cell6.setCellValue(value.getEmail());

            HSSFCell cell7 = row.createCell(6);
            cell7.setCellStyle(dataStyle);
            cell7.setCellValue(value.getMessage());
        }
        workbook.write(FILE_OUTPUT_STREAM);
        FILE_OUTPUT_STREAM.flush();
        FILE_OUTPUT_STREAM.close();
        workbook.close();
        logger.debug("文件流读写关闭成功....");
        return fileName;
    }

    public static File fileCheck(File file) throws IOException {
        if (!file.getPath().endsWith(".xls")) {
            if (!file.exists()) {
                file.mkdirs();
            }
            fileName = "cbim_" + System.currentTimeMillis() + ".xls";
            file = new File(file.getPath(), fileName);
            file.createNewFile();
        } else if (!file.exists()) {
            //是文件且路径不对时直接返回异常信息
            throw new IllegalStateException("路径异常");
        }
        return file;
    }

}

6. 导出到本地配置

可以通过response流将文件返回给客户端，也可以用nginx搭建文件服务器。
将文件写在服务器上，通过nginx 搭建文件服务器来实现下载到本地

6.1nginx配置

在nginx.conf下配置（找到自己对应的nginx配置文件）

location /myfiles {
    alias /export/share/test/;   # 文件存放目录，注意要以 '/' 结尾；
    autoindex on;               # 自动列出目录下的文件；
    autoindex_exact_size off;   # 文件大小按 G、M 的格式显示，而不是 Bytes；
}

配置成功如下: 点击文件即可下载，或者通过http请求在myfiles后加上该文件名

6.2前端代码

发送ajax请求controller得到的文件路径，再进行http请求

success:function (res) {
    if (res.code == 200) {
        //下载到本地
        window.location.href(res.msg);
    }
    alert(res.desc);
    layer.close(layer.index);
}

最终结果演示: