post请求实现excel导出下载功能

欢迎大家关注我的公众号【老周聊架构】，Java后端主流技术栈的原理、源码分析、架构以及各种互联网高并发、高性能、高可用的解决方案。

主要思路：

一、pom依赖

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.14</version>
</dependency>

二、前台传给后台的数据。笔者这里传的可能和你的不一样，具体看自己想实现什么需求哈。

{
	"fileName" : "riemann的Excel导出功能测试",
	"templateType":0,
	"sheetData":[{
		"sheetName":"exportExcelTest",
		"tabularData":[{
			"tabularContent":"工号",
			"firstRow":1,
			"lastRow":1,
			"firstCol":1,
			"lastCol":1
		},
		{
			"tabularContent":"姓名",
			"firstRow":1,
			"lastRow":1,
			"firstCol":2,
			"lastCol":2
		},
		{
			"tabularContent":"性别",
			"firstRow":1,
			"lastRow":1,
			"firstCol":3,
			"lastCol":3
		},
		{
			"tabularContent":"年龄",
			"firstRow":1,
			"lastRow":1,
			"firstCol":4,
			"lastCol":4
		},
		{
			"tabularContent":"电话",
			"firstRow":1,
			"lastRow":1,
			"firstCol":5,
			"lastCol":5
		},
		{
			"tabularContent":"0001",
			"firstRow":2,
			"lastRow":2,
			"firstCol":1,
			"lastCol":1
		},
		{
			"tabularContent":"riemann",
			"firstRow":2,
			"lastRow":2,
			"firstCol":2,
			"lastCol":2
		},
		{
			"tabularContent":"男",
			"firstRow":2,
			"lastRow":2,
			"firstCol":3,
			"lastCol":3
		},
		{
			"tabularContent":"27",
			"firstRow":2,
			"lastRow":2,
			"firstCol":4,
			"lastCol":4
		},
		{
			"tabularContent":"13158085808",
			"firstRow":2,
			"lastRow":2,
			"firstCol":5,
			"lastCol":5
		}]
	}]
}

三、model 实体类

ExportExcelData.java

package com.riemann.springbootdemo.model;

import java.util.List;

/**
 * @author riemann
 * @date 2019/04/28 21:28
 */
public class ExportExcelData {

    //导出Excel的文件名
    private String fileName;

    //模板类型
    private Integer templateType;

    //每个sheet页的数据
    private List<SheetData> sheetData;

    public String getFileName() {
        return fileName;
    }

    public void setFileName(String fileName) {
        this.fileName = fileName;
    }

    public Integer getTemplateType() {
        return templateType;
    }

    public void setTemplateType(Integer templateType) {
        this.templateType = templateType;
    }

    public List<SheetData> getSheetData() {
        return sheetData;
    }

    public void setSheetData(List<SheetData> sheetData) {
        this.sheetData = sheetData;
    }

}

SheetData.java

package com.riemann.springbootdemo.model;

import java.util.List;

/**
 * @author riemann
 * @date 2019/04/28 21:31
 */
public class SheetData {

    //每个sheet页的名字
    private String sheetName;

    //单元格的数据
    private List<TabularData> tabularData;

    public String getSheetName() {
        return sheetName;
    }

    public void setSheetName(String sheetName) {
        this.sheetName = sheetName;
    }

    public List<TabularData> getTabularData() {
        return tabularData;
    }

    public void setTabularData(List<TabularData> tabularData) {
        this.tabularData = tabularData;
    }

}

TabularData.java

package com.riemann.springbootdemo.model;

/**
 * @author riemann
 * @date 2019/04/28 21:33
 */
public class TabularData {

    //每个单元格的内容
    private String tabularContent;

    //开始行
    private Integer firstRow;

    //结束行
    private Integer lastRow;

    //开始列
    private Integer firstCol;

    //结束列
    private Integer lastCol;

    public String getTabularContent() {
        return tabularContent;
    }

    public void setTabularContent(String tabularContent) {
        this.tabularContent = tabularContent;
    }

    public Integer getFirstRow() {
        return firstRow;
    }

    public void setFirstRow(Integer firstRow) {
        this.firstRow = firstRow;
    }

    public Integer getLastRow() {
        return lastRow;
    }

    public void setLastRow(Integer lastRow) {
        this.lastRow = lastRow;
    }

    public Integer getFirstCol() {
        return firstCol;
    }

    public void setFirstCol(Integer firstCol) {
        this.firstCol = firstCol;
    }

    public Integer getLastCol() {
        return lastCol;
    }

    public void setLastCol(Integer lastCol) {
        this.lastCol = lastCol;
    }

}

四、Controller类

ExportExcelController.java

package com.riemann.springbootdemo.controller;

import com.riemann.springbootdemo.model.ApiResponse;
import com.riemann.springbootdemo.model.ExportExcelData;
import com.riemann.springbootdemo.service.ExportExcelService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;


/**
 * Excel导出 Controller
 *
 * @author riemann
 * @date 2019/04/28 21:37
 */
@RestController
@RequestMapping(value = "/export")
public class ExportExcelController {

    @Autowired
    private ExportExcelService exportExcelService;

    @RequestMapping(value = "/exportExcel", method= RequestMethod.POST)
    public ApiResponse exportExcel(@RequestBody ExportExcelData eeData) {
        ApiResponse apiResponse = new ApiResponse();
        if (eeData == null) {
            return null;
        } else {
            apiResponse = exportExcelService.exportExcel(eeData);
        }
        return apiResponse;
    }

}

五、service以及impl实现类

ExportExcelService.java

package com.riemann.springbootdemo.service;

import com.riemann.springbootdemo.model.ApiResponse;
import com.riemann.springbootdemo.model.ExportExcelData;
import org.springframework.stereotype.Service;

/**
 * @author riemann
 * @date 2019/04/28 21:58
 */

public interface ExportExcelService {

    ApiResponse exportExcel(ExportExcelData exportExcelData);

}

ExportExcelServiceImpl.java

package com.riemann.springbootdemo.service.impl;

import com.riemann.springbootdemo.model.ApiResponse;
import com.riemann.springbootdemo.model.ExportExcelData;
import com.riemann.springbootdemo.model.SheetData;
import com.riemann.springbootdemo.model.TabularData;
import com.riemann.springbootdemo.service.ExportExcelService;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 * @author riemann
 * @date 2019/04/28 22:05
 */
@Service
public class ExportExcelServiceImpl implements ExportExcelService {

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

    @Override
    public ApiResponse exportExcel(ExportExcelData exportExcelData) {

        ApiResponse apiResponse = new ApiResponse();
        String fileName = exportExcelData.getFileName();
        int templateType = exportExcelData.getTemplateType();
        List<SheetData> sheetDataList = exportExcelData.getSheetData();

        HSSFWorkbook wb = new HSSFWorkbook();//创建工作薄
        //创建字体样式
        HSSFFont font = wb.createFont();
        font.setFontName("宋体");//使用宋体
        font.setFontHeightInPoints((short) 12);//字体大小
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗
        //创建单元格样式style
        HSSFCellStyle style = wb.createCellStyle();
        style.setFont(font);//将字体注入
        style.setWrapText(true);//自动换行
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
        style.setBorderTop((short) 1);//设置边框大小
        style.setBorderBottom((short) 1);
        style.setBorderLeft((short) 1);
        style.setBorderRight((short) 1);

        //循环遍历有多少个sheeet页
        for (int i = 0; i < sheetDataList.size(); i++) {
            SheetData sheetData = sheetDataList.get(i);
            String sheetName = sheetData.getSheetName();
            List<TabularData> tabularData = sheetData.getTabularData();
            HSSFSheet sheet = wb.createSheet();//创建工作表
            wb.setSheetName(i, sheetName);

            for(TabularData tab : tabularData) {
                HSSFRow row = sheet.getRow(tab.getLastRow() - 1);
                if (row == null) {
                    row = sheet.createRow(tab.getLastRow() - 1);//创建所需的行数
                }
                HSSFCell cell = row.getCell(tab.getLastCol() - 1);//设置单元格的数据
                if (cell == null) {
                    cell = row.createCell(tab.getLastCol() - 1);
                    cell.setCellValue(tab.getTabularContent());
                }
            }
        }

        if (downloadExcel(wb)){
            apiResponse.setStatusCode("200");
            apiResponse.setMessage("export excel success");
        } else {
            apiResponse.setStatusCode("400");
            apiResponse.setMessage("export excel failed");
        }
        return apiResponse;
    }

    public boolean downloadExcel(HSSFWorkbook wb) {
        boolean flag = true;
        Date date = new Date();
        String sdf = "";
        sdf = new SimpleDateFormat("yyyy-MM-dd").format(date.getTime());
        String filePath = "D:\\excel\\excel导出测试表" + sdf + ".xls";
        File file = new File(filePath);

        FileOutputStream fos = null;
        try {

            fos = new FileOutputStream(file);
            wb.write(fos);
        } catch (IOException e) {
            e.printStackTrace();
            flag = false;
        } finally {
            if (fos != null) {
                try {
                    fos.close();
                }catch (IOException e) {
                    e.printStackTrace();
                    flag = false;
                }
            }
        }
        return flag;
    }
}

六、运行结果