spring boot 将数据库的数据导出到 Exel

引入的jar maven坐标（pom.xml ）

	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi</artifactId>
		<version>3.15</version>
	</dependency>
	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi-ooxml-schemas</artifactId>
		<version>3.15</version>
	</dependency>
	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi-ooxml</artifactId>
		<version>3.15</version>
	</dependency>

实现

import com.synda.smp.log.entity.SyndaLog;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.util.List;


public interface LogService {
    XSSFWorkbook creatExcel(String fileName, List<SyndaLog> ids);
}

import com.synda.smp.log.entity.SyndaLog;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;

@Service
public class LogServiceImpl implements LogService{


    @Override
    public XSSFWorkbook creatExcel(String fileName, List<SyndaLog> list) {
        XSSFWorkbook sheets = new XSSFWorkbook();
        //创建一张表
        Sheet sheet = sheets.createSheet(fileName);
        //创建表头,创建第一行，起始为0
        Row titleRow =sheet.createRow(0);
        Class<SyndaLog> logClass=SyndaLog.class;
        Field[] declaredFields = logClass.getDeclaredFields();
        List<String> methodNames=new ArrayList<>();
        int cells=0;
        for (int i=0;i<declaredFields.length;i++){
            cells++;
            titleRow.createCell(i).setCellValue(declaredFields[i].getName());
            methodNames.add("get"+declaredFields[i].getName().substring(0,1).toUpperCase()+declaredFields[i].getName().substring(1));
        }
        int cell = 1;
        for (SyndaLog syndaLog : list) {
            //从第二行开始保存数据
            Row row = sheet.createRow(cell);
            for (int i=0;i<methodNames.size();i++){
                Method method = null;
                Object invokeValue =null;
                try {
                    method = syndaLog.getClass().getMethod(methodNames.get(i));
                    invokeValue=method.invoke(syndaLog, new Object[0]);
                    String value=invokeValue.toString();
                    System.out.println(methodNames.get(i)+":"+value);
                    //将数据库的数据遍历出来
                    row.createCell(i).setCellValue(value);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            cell++;
        }
        return sheets;
    }
}

import com.synda.smp.log.repository.SyndaLogInterface;
import com.synda.smp.log.service.LogService;
import io.swagger.annotations.ApiParam;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.Assert;
import org.springframework.web.bind.annotation.*;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.OutputStream;
import java.util.List;

import com.synda.smp.log.entity.SyndaLog;
import org.springframework.web.bind.annotation.RestController;

/**
 * <p>
 *  控制器
 * </p>
 *
 * @author gdc
 * @since 2020-07-14
 */

@RestController
@RequestMapping("/log")
public class SyndaLogController {
    @Autowired
    private LogService logService;
    @Resource
    private SyndaLogInterface repository;

    public void export(HttpServletResponse response,
                       @ApiParam(name = "ids", value = "需要导出的id集合", required = true)
                       @RequestParam(name = "ids")
                       String[] ids,
                       @ApiParam(name = "fileName", value = "导出文件名（无后缀）", required = true)
                       @RequestParam(name = "fileName")
                       String fileName){
        List<SyndaLog> syndaLogs = repository.findAllByIdInAndExportTagEquals(ids,0);
        String result=syndaLogs.size()>0?"result":null;
        Assert.notNull(result,"无数据导出");
        XSSFWorkbook sheets = logService.creatExcel(fileName,syndaLogs);
        this.setBrowser(response,sheets,fileName);
    }
    private  void setBrowser(HttpServletResponse response, XSSFWorkbook workbook, String fileName) {
        try {
            //清空response
            response.reset();
            //设置response的Header
            response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
            OutputStream os = new BufferedOutputStream(response.getOutputStream());
            response.setContentType("application/vnd.ms-excel;charset=gb2312");
            //将excel写入到输出流中
            workbook.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
}