某些情况下,我们的需求可能需要把数据库某些字段或者某些数据来写入到Excel并生成文件,而恰好最近我就收到了这样的需求,把签到数据写入Excel并生成,提供接口给前端下载使用.
操作微软的办公套件工具我看了有JXL和POI,最后还是选用POI.
需求
将MySQL字段内存的JSON数据写入到Excel表格中,title标题与content数量为用户自定义,前端传入当前签到活动的ID到数据库查询到对应ID的数据转为list, 转list需要先生成一个实体类映射对象.
对象
@Data
@AllArgsConstructor
@NoArgsConstructor
public class BasicSelcInfo {
private String title;
private String content;
}
坐标
<!--操作Excel的依赖坐标-->
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
代码
@Service
public class ExcelServiceImpl implements ExcelService {
@Autowired
private AttendanceMapper attendanceMapper;
//生成Excel方法
public HSSFWorkbook getHSSFWorkbook(Long id) {
//创建对象
HSSFWorkbook workbook = null;
//查询数据库
List<Attendance> attendances = attendanceMapper.listAttendanceByAid(id);
System.err.println(attendances.size());
System.out.println(attendances);
//非空判断
if (workbook == null) {
workbook = new HSSFWorkbook();
}
//在workbook中添加一个sheet
HSSFSheet sheet = workbook.createSheet(new Date().getTime() + ""); //设置当前时间为sheet名称
//sheet添加标头第0行
HSSFRow row = sheet.createRow(0);
//创建单元格设置,设置标头居中
HSSFCellStyle style = workbook.createCellStyle();
//声明列对象
HSSFCell cell = null;
sheet.setDefaultRowHeightInPoints(10);
//声明list,存放数据库总结果
List<List<BasicSelcInfo>> all = new ArrayList<>();
//声明list,提升作用域
List<BasicSelcInfo> list = null;
//获取字段JSON
for (Attendance attendance : attendances) {
String jsonText = attendance.getBasicSelcInfo();
ObjectMapper objectMapper = new ObjectMapper();
//JSON转集合
try {
list = objectMapper.readValue(jsonText, new TypeReference<List<BasicSelcInfo>>() {
});
} catch (IOException e) {
e.printStackTrace();
}
all.add(list);
System.err.println("数据" + list);
}
//创建标题
for (int i1 = 0; i1 < list.size(); i1++) {
cell = row.createCell(i1);
cell.setCellValue(list.get(i1).getTitle());
cell.setCellStyle(style);
}
//创建内容
int index = 0;
for (List<BasicSelcInfo> basicSelcInfos : all) {
row = sheet.createRow(index + 1);
for (int j = 0; j < basicSelcInfos.size(); j++) {
row.createCell(j).setCellValue(basicSelcInfos.get(j).getContent());
}
index++;
}
/* try {
//文件输出流
OutputStream os = new FileOutputStream(new File("/Users/zhangchanggeng/Desktop/test.xls"));
//写入文件
workbook.write(os);
//切换状态
os.flush();
}catch (Exception e){
e.printStackTrace();
}*/
return workbook;
}
}
Controller
@RestController
public class DownLoadExcelController {
@Autowired
private ExcelService excelService;
//处理Excel
@GetMapping("/exportExcel")
public void exportExcel(@RequestParam("aid") Long aid , HttpServletResponse response){
HSSFWorkbook workbook = excelService.getHSSFWorkbook(aid);
try {
//响应到客户端
this.setResponseHeader(response,new Date().getYear()+"Y"+aid+".xls");
ServletOutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
//发送响应流
public void setResponseHeader(HttpServletResponse response,String fileName){
fileName = new String(fileName.getBytes(), StandardCharsets.UTF_8);
response.setContentType("application/vnd.ms-excel");
try {
response.setHeader("Content-disposition","attachment:filename="+ URLEncoder.encode(fileName,"UTF-8"));
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
}
}