poi导出数据库数据到excel

poi导出数据库数据到excel

1. 导入poi依赖

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

2. demo代码

@RestController
@RequestMapping("/demo")
public class DemoController {


    @PostMapping("/querySql")
    public void querySql(@RequestBody String sql , @RequestParam("table_name") String tableName) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://172.19.208.1:3316/test", "root", "root");
        Statement stat = conn.createStatement();
        ResultSet rs = stat.executeQuery(sql);
        ArrayList<String> list = new ArrayList<>();
        Arrays.stream(((ResultSetImpl) rs).getMetadata().getFields()).forEach(item -> list.add(item.getName()));
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet(tableName);
        XSSFRow row = sheet.createRow(0);
        for (int i = 0; i < list.size(); i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellValue(list.get(i));
            int num = 1;
            while (rs.next()) {
                XSSFRow rows;
                XSSFCell cells;
                rows = sheet.createRow(num);
                for (int j = 0; j < list.size(); j++) {
                    cells = rows.createCell(j);
                    cells.setCellValue(rs.getString(j +1   ));
                }
                num++;
            }
        }
        File file = new File(tableName + ".xlsx");
        FileOutputStream stream = new FileOutputStream(file);
        workbook.write(stream);
        //关闭资源
        stream.close();
        conn.close();
        stat.close();
        conn.close();
    }


}