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{ List codeList(ExcelExportParam exportParam); }
ServiceImpl:
@Service @Transactional public class ExcelExportImpl extends BaseServiceimplements 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 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文件,写上需要查询的字段即可。
创作不易,感谢支持,感谢各位大佬批评指正!



