Mybatis 动态传sql可以查询表名，任意表名，导出表中的数据

导出数据我用的是poi导出excel文件在pom文件中引入

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <!-- poi-ooxml XSSF is our port of the Microsoft Excel XML (2007+) file format (OOXML) to pure Java -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.17</version>
        </dependency>

获取任意表名和表中的字段参考文章

Mybatis 动态传sql可以查询表名，任意表名，不固定字段的个数返回未定义的类型以及增删改

之后再controller层引用

 @ApiOperation(value = "导出任意表中的数据", notes = "")
    @GetMapping("/executeTableInfo")
    @SystemControllerLog(title = "导出数据",businessType = BusinessType.EXPORT)
    public void executeTableInfo(@RequestParam("tableName")String tableName,HttpServletRequest request,
                              HttpServletResponse response) throws IOException {
        List<Map<String, String>> maps = managerTableService.executeTableInfo(tableName);
        List<String> tableCloumName = managerTableService.getTableCloumName("'"+tableName+"'");
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet=workbook.createSheet(tableName);

        //设置导出的文件的名字
        String filename=tableName+".xls";
        //创建表头
        HSSFRow tablerow = sheet.createRow(0);
        //循环添加表头数据
        for (int i = 0; i < tableCloumName.size(); i++) {
            HSSFCell cell=tablerow.createCell(i);
            HSSFRichTextString text=new HSSFRichTextString(tableCloumName.get(i));
            cell.setCellValue(text);
        }
        for (int i = 0; i < maps.size(); i++) {

            // 一个List对象是一个Map，一行数据，一个Map对象对应一行里的一条数据
            Map tableMap = maps.get(i);

            //表头是第0行，所以从第一行开始创建
            Row row = sheet.createRow(i + 1);

            //循环创建单元格
            for (int j = 0; j < tableCloumName.size(); j++) {
                //获取指定字段的值，判断是否为空
                Object object=tableMap.get(tableCloumName.get(j));
                String val="";
                if(object!=null){
                    val=object.toString();
                }
                row.createCell(j).setCellValue(val);
            }
        }
        response.setContentType("application/octet-stream");
        response.setHeader("Content-disposition", "attachment;filename="+filename);
        try {
            response.flushBuffer();
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

这么写就可以导出任何表中的数据，亲测可行

 

转载于:https://www.cnblogs.com/blackCatFish/p/11002127.html