poi报表导出

思路：
1.获取报表数据
2.构造Excel
3.完成下载

步骤分析：
1.构造表格数据
2.创建爱你工作簿
3.创建sheet
4.创建行对象
5.创建单元格对象
6.填充数据，设置样式
7.下载

下载模板图：

代码实现：
controller层

@RequestMapping(value = "/export/{month}", method = RequestMethod.GET)
public void export(@PathVariable(name = "month") String month) throws Exception {
//1.构造数据
List<EmployeeReportResult> list =
userCompanyPersonalService.findByReport(companyId,month+"%");
//2.创建工作簿
XSSFWorkbook workbook = new XSSFWorkbook();
//3.构造sheet
String[] titles = {"编号", "姓名", "手机","最高学历", "国家地区", "护照号", "籍贯",
"生日", "属相","入职时间","离职类型","离职原因","离职时间"};
Sheet sheet = workbook.createSheet();
Row row = sheet.createRow(0);
AtomicInteger headersAi = new AtomicInteger();
for (String title : titles) {
Cell cell = row.createCell(headersAi.getAndIncrement());
cell.setCellValue(title);
}
AtomicInteger datasAi = new AtomicInteger(1);
Cell cell = null;
for (EmployeeReportResult report : list) {
Row dataRow = sheet.createRow(datasAi.getAndIncrement());
//编号
cell = dataRow.createCell(0);
cell.setCellValue(report.getUserId());
//姓名
cell = dataRow.createCell(1);
cell.setCellValue(report.getUsername());
//手机
cell = dataRow.createCell(2);
cell.setCellValue(report.getMobile());
//最高学历
cell = dataRow.createCell(3);
cell.setCellValue(report.getTheHighestDegreeOfEducation());
//国家地区
cell = dataRow.createCell(4);
cell.setCellValue(report.getNationalArea());
//护照号
cell = dataRow.createCell(5);
cell.setCellValue(report.getPassportNo());
//籍贯
cell = dataRow.createCell(6);
cell.setCellValue(report.getNativePlace());
//生日
cell = dataRow.createCell(7);
cell.setCellValue(report.getBirthday());
//属相
cell = dataRow.createCell(8);
cell.setCellValue(report.getZodiac());
//入职时间
cell = dataRow.createCell(9);
cell.setCellValue(report.getTimeOfEntry());
//离职类型
cell = dataRow.createCell(10);
cell.setCellValue(report.getTypeOfTurnover());
//离职原因
cell = dataRow.createCell(11);
cell.setCellValue(report.getReasonsForLeaving());
//离职时间
cell = dataRow.createCell(12);
cell.setCellValue(report.getResignationTime());
}
String fileName = URLEncoder.encode(month+"人员信息.xlsx", "UTF-8");
response.setContentType("application/octet-stream");
response.setHeader("content-disposition", "attachment;filename=" + new
String(fileName.getBytes("ISO8859-1")));
response.setHeader("filename", fileName);
workbook.write(response.getOutputStream());
}
//

service层

//根据企业id和年月查询
public List<EmployeeReportResult> findByReport(String companyId, String month) {
return userCompanyPersonalDao.findByReport(companyId,month);
}

dao层实现，这里使用的是spring JPA做dao层数据增删改查的。

@Query(value = "select new
com.abc.domain.employee.response.EmployeeReportResult(a,b) " +
"FROM UserCompanyPersonal a LEFT JOIN EmployeeResignation b ON
a.userId=b.userId WHERE a.companyId = ?1 AND a.timeOfEntry LIKE ?2 OR
(b.resignationTime LIKE ?2)")
List<EmployeeReportResult> findByReport(String companyId, String month);

最后，附下载代码工具类，如下：

import org.apache.poi.ss.usermodel.Workbook;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;

public class DownloadUtils {
    public void download(ByteArrayOutputStream byteArrayOutputStream, HttpServletResponse response, String returnName) throws IOException {
        response.setContentType("application/octet-stream");
        returnName = response.encodeURL(new String(returnName.getBytes(),"iso8859-1"));			//保存的文件名,必须和页面编码一致,否则乱码
        response.addHeader("Content-Disposition","attachment;filename=total.xls");
        response.setContentLength(byteArrayOutputStream.size());
        response.addHeader("Content-Length", "" + byteArrayOutputStream.size());
        ServletOutputStream outputstream = response.getOutputStream();	//取得输出流
        byteArrayOutputStream.writeTo(outputstream);					//写到输出流
        byteArrayOutputStream.close();									//关闭
        outputstream.flush();											//刷数据
    }
}