springboot实现根据查询条件导出excel

最新推荐文章于 2024-04-30 15:35:00 发布

原创最新推荐文章于 2024-04-30 15:35:00 发布 · 6.4k 阅读

22 ·

CC 4.0 BY-SA版权

文章标签：

#springboot #excel

记录专栏收录该内容

9 篇文章

订阅专栏

首先添加pom文件

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

controller层

@RestController
@RequestMapping("/adminApi/excel")
public class ExcelController {

    @Resource
    private ExcelService excelService;

    /*
     *导出用户信息
     * 2018/10/30+
     * dayue
     */
    @RequestMapping(value = "/export")
    public void downloadAllClassmate(@RequestParam(value = "status") String status,
                                     @RequestParam(value = "sex") String sex, @RequestParam(value = "type") String type,
                                     @RequestParam(value = "firstTime") String firstTime, @RequestParam(value = "lastTime") String lastTime,
                                     HttpServletResponse response) throws IOException {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("用户信息表");
        
        //查询出的数据
        List<ExcelUser> classmateList = excelService.selectUser(status,sex,type,firstTime,lastTime);
         
        //文件名称  必须用英文
        String fileName = "User"  + ".xls";//设置要导出的文件的名字
        //新增数据行，并且设置单元格数据

        int rowNum = 1;
        
        //根据实际情况编写列表名（顺序必须一一对应）
        String[] headers = {"姓氏", "名字"};
        //headers表示excel表中第一行的表头

        HSSFRow row = sheet.createRow(0);
        //在excel表中添加表头

        for(int i=0;i<headers.length;i++){
            HSSFCell cell = row.createCell(i);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
         
        //查询出来的数据可能为null这种看着很不舒服  将null变为空
         String lastName = null;
         String firstName = null;

        //在表中存放查询到的数据放入对应的列
        for (ExcelUser excelUser : classmateList) {
            HSSFRow row1 = sheet.createRow(rowNum);
            //判断lastName是否为null  若为null 给空  若不为null 传相应的值
            if (excelUser.getLastName() == null){
                lastName = "";
            }else {
                lastName = excelUser.getLastName();
            }
            row1.createCell(0).setCellValue(lastName);
            //同上
            if (excelUser.getFirstName() == null){
                firstName = "";
            }else {
                firstName = excelUser.getFirstName();
            }
            row1.createCell(1).setCellValue(firstName);
            rowNum++;
        }

        response.setContentType("application/octet-stream");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);
        response.flushBuffer();
        workbook.write(response.getOutputStream());
    }

Service层

List<ExcelUser> selectUser(String status,String sex, String type, String firstTime, String lastTime);

ServiceImp层

@Override
    public List<ExcelUser> selectUser(String status,String sex, String type, String firstTime, String lastTime){
        return  bpUserMapper.findUser(status,sex,type,firstTime,lastTime);
    }

sql就是根据你查询条件写一个sql语句就不写了

最后重点不能使post请求用get或者链接请求否则会返回乱码