Java Excel导出（文件下载）详细教程

一、前言

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文件，写上需要查询的字段即可。

创作不易，感谢支持，感谢各位大佬批评指正！