一、前言
Java SpringBoot+MVC+MybatisPlus,实现Excel文件导出功能
文件导出功能经典案例比较多,代码复用性也很高。
二、代码逻辑
注:实体类自行创建
Controller层代码:
@RestController
@RequestMapping("/api/v1/m-training-record")
@Api(tags = "MTrainingRecord")
public class ExcelExportContoller {
@Autowired
private ExcelExportService excelExportService;
@GetMapping("/excelExport")
@ApiOperation(value = "excelExport", tags = {"F"})
public void list(@Validated ExcelExportParam exportParam, HttpServletResponse response) {
try { //导出Excel
HSSFWorkbook workbook = excelExportService.excelExport(exportParam);
//设置内容类型
response.setContentType("application/vnd.ms-excel;charset=utf-8");
OutputStream outputStream = response.getOutputStream();
response.setHeader("Content-disposition", "attachment;filename=trainingRecord.xls");
workbook.write(outputStream);
outputStream.flush();
outputStream.close();}
catch (Exception e){
e.printStackTrace();
}
}
}
Service层:
public interface ExcelExportService {
HSSFWorkbook excelExport(ExcelExportParam exportParam);
}
Mapper层:
@Mapper
public interface ExcelExportMapper extends MyBaseMapper<ExcelExport>{
List<ExcelExportDto> codeList(ExcelExportParam exportParam);
}
ServiceImpl:
@Service
@Transactional
public class ExcelExportImpl extends BaseService<ExcelExportMapper, ExcelExport> implements ExcelExportService {
@Autowired
private ExcelExportMapper excelExportMapper;
@Override
public HSSFWorkbook excelExport(ExcelExportParam exportParam) {
//新建一个excel
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
//新建一个sheet页
HSSFSheet sheet = hssfWorkbook.createSheet("ExcelExport");
//新建一行,第index行
HSSFRow hssfRow = null;
hssfRow = sheet.createRow(0);
//设置行高
hssfRow.setHeight((short) (22.50 * 20));
//创建表头
hssfRow.createCell(0).setCellValue("CourseId");
hssfRow.createCell(1).setCellValue("CourseName");
hssfRow.createCell(2).setCellValue("DepartmentName");
hssfRow.createCell(3).setCellValue("EmployeeName");
hssfRow.createCell(4).setCellValue("Uid");
hssfRow.createCell(5).setCellValue("Id");
List<ExcelExportDto> dtoList = excelExportMapper.codeList(exportParam);
//设置Excel格式
HSSFCellStyle style = genContextStyle(hssfWorkbook);
//遍历数据并赋值到Excel里
for (int i = 0; i < dtoList.size(); i++) {
hssfRow = sheet.createRow(i + 1);
ExcelExportDto excelExportDto = dtoList.get(i);
hssfRow.createCell(0).setCellValue(excelExportDto.getCourseId());
hssfRow.createCell(1).setCellValue(excelExportDto.getCourseName());
hssfRow.createCell(2).setCellValue(excelExportDto.getDepartmentName());
hssfRow.createCell(3).setCellValue(excelExportDto.getEmployeeName());
hssfRow.createCell(4).setCellValue(excelExportDto.getUid());
hssfRow.createCell(5).setCellValue("11111");
}
//设置默认行高
sheet.setDefaultRowHeight((short) (16.5 * 20));
//列宽自适应
for (int i = 0; i < dtoList.size(); i++) {
sheet.autoSizeColumn(i);
int width = Math.max(15 * 256, Math.min(256 * 256, sheet.getColumnWidth(i) * 12 / 10));
sheet.setColumnWidth(i, width);
}
return hssfWorkbook;
}
//表格字体样式
public static HSSFCellStyle genContextStyle(HSSFWorkbook workbook) {
HSSFCellStyle style1 = workbook.createCellStyle();
//文本水平居中显示
style1.setAlignment(HorizontalAlignment.CENTER);
//文本竖直居中显示
style1.setVerticalAlignment(VerticalAlignment.CENTER);
//文本自动换行
style1.setWrapText(true);
//设置文本边框
style1.setBorderBottom(BorderStyle.THIN);
style1.setBorderLeft(BorderStyle.THIN);
style1.setBorderRight(BorderStyle.THIN);
style1.setBorderTop(BorderStyle.THIN);
return style1;
}
}
再加上自己手写一个.xml文件,写上需要查询的字段即可。
创作不易,感谢支持,感谢各位大佬批评指正!