使用hutool库，对excel进行一些导出、导入操作
package com.example.demo;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.IoUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.domain.MagBackAmountInfo;
import com.domain.ReturnResult;
import com.example.service.impl.MagBackAmountInfoServiceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@RestController
//允许跨域访问
@CrossOrigin
@RequestMapping("/member")
public class ExcelController {
@Autowired
MagBackAmountInfoServiceImpl backAmountInfoService;
@GetMapping("/testExport")
public void test(HttpServletRequest request, HttpServletResponse response){
int pageStart = Integer.parseInt(request.getParameter("Pagenumber"));
int pageSize = Integer.parseInt(request.getParameter("number"));
/** 这里写你自己获取数据的方法*/
List<MagBackAmountInfo> magBackAmountInfos = backAmountInfoService.selectByPrimarybyid(null);
ExcelWriter writer = ExcelUtil.getWriter(true);
/** 这里的作用是将你的字段与excel展示名称做匹配 */
Map<String,String> map = new HashMap<>();
map.put("backAmountId","VIN");
map.put("makeInvocId","ICCID");
map.put("customerName","客户名称");
map.put("vehiclesName","车系名称");
map.put("vehicleModel","车型名称");
map.put("vehicleConf","配置名称");
map.put("customerType","客户类型");
map.put("activedTime","实销通过日期");
map.put("realStatus","实名状态");
writer.setHeaderAlias(map);
/** 这里的作用时，写出之后不展示你的字段名，只展示你设置的别名，客户类型，实销通过日期。这些 */
writer.setOnlyAlias(true);
writer.write(magBackAmountInfos,true);
response.setContentType("application/;charset=utf-8");
response.setHeader("Content-Disposition","attachment;filename=");
ServletOutputStream out= null;
try {
out = response.getOutputStream();
writer.flush(out, true);
// 关闭writer，释放内存
writer.close();
//此处记得关闭输出Servlet流
IoUtil.close(out);
} catch (IOException e) {
e.printStackTrace();
}
}
@ResponseBody
@RequestMapping(value = "/moban", method = RequestMethod.GET)
public void moban(HttpServletRequest request, HttpServletResponse response){
ExcelWriter writer = ExcelUtil.getWriter(true);
List<String> row = CollUtil.newArrayList("backAmountId", "makeInvocId", "customerName", "vehiclesName","vehicleModel","vehicleConf","customerType","activedTime","realStatus");
List<String> secRow = CollUtil.newArrayList("VIN", "ICCID", "客户名称", "车系名称","车型名称","配置名称","客户类型","实销通过日期-(yyyy-MM-dd HH:mm:ss)","实名状态");
writer.writeHeadRow(row);
writer.writeRow(secRow);
/** 这里说是隐藏，但是其实就是将高度置为0，有点鸡肋 */
writer.getSheet().getRow(0).setZeroHeight(true);
response.setContentType("application/;charset=utf-8");
response.setHeader("Content-Disposition","attachment;filename=");
ServletOutputStream out= null;
try {
out = response.getOutputStream();
writer.flush(out, true);
// 关闭writer，释放内存
writer.close();
//此处记得关闭输出Servlet流
IoUtil.close(out);
} catch (IOException e) {
e.printStackTrace();
}
}
//导入excel
@PostMapping("/import")
public ReturnResult importExcel(@RequestParam("file") MultipartFile file) throws IOException {
String originalFilename = file.getOriginalFilename();
String suffix = originalFilename.substring(originalFilename.lastIndexOf(".") + 1);
if (file==null|| file.isEmpty()){
return ReturnResult.failed(HttpStatus.BAD_REQUEST.value(), "上传文件为空，请重新上传");
}
else if (!suffix.equals("xls")&&!suffix.equals("xlsx")){
return ReturnResult.failed(HttpStatus.BAD_REQUEST.value(), "文件格式错误，请上传excel文件");
}
ExcelReader reader = ExcelUtil.getReader(file.getInputStream());
/** 因为模板中有两行为提示信息，所以这儿设置从0，2之后读取 */
List<MagBackAmountInfo> blackDtos = reader.read(0, 2, MagBackAmountInfo.class);
if (blackDtos.size() == 0) {
return ReturnResult.failed(HttpStatus.BAD_REQUEST.value(), "文件数据为空，请重新上传");
} else {
/** 可以直接批量插入，也可以在service层做一些关键值的判断 */
backAmountInfoService.batchInsert(blackDtos);
return ReturnResult.ok();
}
}
}