导出Excel的demo（HSSFWorkbook和EasyExcel）
1、HSSFWorkbook
controller
@PostMapping("/exportAllProposal/{proposalNos}")
@ResponseBody
public Result exportAllProposal(@PathVariable(name = "proposalNos") String proposalNos) throws Exception {
logger.info("请求入参：[" + proposalNos + "]");
proposalService.exportAllProposal(proposalNos);
Result result = Result.success(200);// 成功返回
return result;
}server
@Override
public void exportAllProposal(String proposalNos) throws Exception {
//1.表头
String[] contentColumnHeaders = {"投保单号 ","投保人","被保险人 ","险种",
"起保日期" ,"终保日期","操作员","操作时间","投保单状态" };
String path = ClassUtils.getDefaultClassLoader().getResource("").getPath();
path = path+ "proposal"+ System.currentTimeMillis() +".xls";//下载路径
HSSFWorkbook workbook = null;
HSSFSheet sheet = null;
FileInputStream fis = null;
workbook = new HSSFWorkbook();
//2.创建工作表
sheet = workbook.createSheet("proposal");
//3.1、创建头标题行； 并且设置头标题
HSSFRow row = sheet.createRow(0);
for(int i=0; i < contentColumnHeaders.length; i++){
HSSFCell cell2 = row.createCell(i);
cell2.setCellValue(contentColumnHeaders[i]);
}
String[] proposalNos_ = proposalNos.split(",");
SimpleDateFormat df = new SimpleDateFormat("yyyy/MM/dd");
//填写数据
for(int i = 0; i<proposalNos_.length; i++){
Map map = new HashMap();
map.put("proposalNo",proposalNos_[i]);
List<Prptmain> prptmainList = prptmainMapper.selectByMap(map);
Prptmain prptmain = prptmainList.get(0);
HSSFRow rowContent = sheet.createRow(i + 1 );
HSSFCell Cell0 = rowContent.createCell(0);
Cell0.setCellValue(prptmain.getProposalno());
HSSFCell Cell1= rowContent.createCell(1);
Cell1.setCellValue(prptmain.getAppliname());
HSSFCell Cell2 = rowContent.createCell(2);
Cell2.setCellValue(prptmain.getInsuredname());
HSSFCell Cell3 = rowContent.createCell(3);
Cell3.setCellValue(prptmain.getRiskcode());
HSSFCell Cell4 = rowContent.createCell(4);
Cell4.setCellValue(df.format(prptmain.getStartdate()));
HSSFCell Cell5 = rowContent.createCell(5);
Cell5.setCellValue(df.format(prptmain.getEnddate()));
HSSFCell Cell6 = rowContent.createCell(6);
Cell6.setCellValue(prptmain.getOperatorcode());
HSSFCell Cell7 = rowContent.createCell(7);
Cell7.setCellValue(df.format(prptmain.getOperatedate()));
HSSFCell Cell8 = rowContent.createCell(8);
Cell8.setCellValue(prptmain.getUnderwriteflag());
}
FileOutputStream fos = new FileOutputStream(new File(path));
//5、输出
workbook.write(fos);
fos.close();
workbook.close();
}2、EasyExcel
controller
@GetMapping("/exportAllPolicy")
@ResponseBody
public void exportAllProposal(HttpServletResponse response, @RequestParam("endorseNos") String endorseNos) throws Exception {
logger.info("请求入参：[" + endorseNos + "]");
OutputStream os = response.getOutputStream();// 取得输出流
response.reset();// 清空输出流
String name = new String("pheadlitExcel".getBytes("GB2312"), "8859_1");
response.setHeader("Content-disposition", "attachment; filename=" + name + ".xls");// 设定输出文件头
response.setContentType("application/msexcel");// 定义输出类型
try {
ByteArrayInputStream inputStream = endorPolicyService.exportAllEndorse(endorseNos);
byte[] buffer = new byte[inputStream.available()];
inputStream.read(buffer);
inputStream.close();
OutputStream output = new BufferedOutputStream(os);
output.write(buffer);
os.flush();
os.close();
response.setStatus(200);
}catch (Exception e){
e.printStackTrace();
response.setStatus(500);
}
}service
@Override
public ByteArrayInputStream exportAllEndorse(String endorseNos) throws IOException {
String input = "template/prppheadlist.xlsx";//excel模板
File rf;
InputStream in = null;
try {
Resource[] resources = new PathMatchingResourcePatternResolver()
.getResources(ResourceUtils.CLASSPATH_URL_PREFIX + input);
for (Resource resource : resources) {
rf = resource.getFile();
if (null == rf || !rf.exists()) continue;
in = com.sinosig.saab.util.FileUtils.openInputStream(rf);
break;
}
} catch (Exception e) {
in = new ClassPathResource(input).getInputStream();
}
ExcelReader excelReader = null;
ByteArrayOutputStream out = new ByteArrayOutputStream();
//生成excel
ExcelWriter excelWriter = EasyExcel.write(out).withTemplate(in).build();
WriteSheet writeSheet = EasyExcel.writerSheet().build();
List<Prpphead> pheadlists = prppheadDao.queryByParam(endorseNos);
//填写数据
excelWriter.fill(pheadlists, writeSheet);
excelWriter.finish();
return new ByteArrayInputStream(out.toByteArray());
}前端（vue）：
通过sxios发送请求
axios(
{
url: process.env.VUE_APP_BASE_API + '/nvsearch/queryEndorsPolicy/exportAllPolicy',
headers: { 'Content-Type': 'application/vnd.ms-excel' },
method: 'get',
responseType: 'blob',
params: obj
}
).then((res) => {
if (res.status === 200) {
const fileName = (res.headers['content-disposition'] && res.headers['content-disposition'].indexOf('filename=') > -1)
? decodeURIComponent(res.headers['content-disposition'].split('filename=')[1])
: '导出信息.xlsx'
console.log('res', res)
const blob = new Blob([res.data])
if ('download' in document.createElement('a')) { // 非IE下载
console.log('非IE')
const elink = document.createElement('a')
elink.download = fileName
elink.style.display = 'none'
elink.href = URL.createObjectURL(blob)
document.body.appendChild(elink)
elink.click()
URL.revokeObjectURL(elink.href) // 释放URL 对象
document.body.removeChild(elink)
} else { // IE10+下载
navigator.msSaveBlob(blob, fileName)
}
}
}).catch((err) => {
this.$notify({
title: '提示',
message: err,
type: 'error',
})
})此方法可以达到用户自己选择下载地址的目的，而HSSFWork是直接生成excel，下载地址已写死。
注：以代码是个demo，有些类或方法是公司特有的，只是给您一个模板参考使用。
追加：
以上EasyExcel方法，excel文件可能在编译时破坏了格式，导致下载下来的文件无法打开，需要在pom.xml中添加：
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-resources-plugin</artifactId>
<configuration>
<nonFilteredFileExtensions>
<nonFilteredFileExtension>xls</nonFilteredFileExtension>
<nonFilteredFileExtension>xlsx</nonFilteredFileExtension>
</nonFilteredFileExtensions>
</configuration>
</plugin>这样excel文件不参编码就可以了。