java实现根据sql动态下载数据到excel

需求

由于生产数据库不能直接连接下载数据，所以需要在监控系统上做一个根据sql动态查询并下载数据的功能。

实现思路

写一个接口，传入需要查询的数据库信息和sql，将查询的接口导出到Excel中

实现细节

入参

@Data
public class ExportDataReq {
    /**
     * "jdbc:mysql://localhost:3306/your_database";
     */
    @NotEmpty
    String url;
    @NotEmpty
    String user;
    @NotEmpty
    String password;
    @NotEmpty
    String sql;
    @NotEmpty
    String[] titles;
}

controller

@Slf4j
@Controller
@RequestMapping("/export/")
public class ExportController {
    @Autowired
    ExportService exportService;

    @ApiOperation(value = "下载数据信息", notes = "日期格式：yyyy-MM-dd", httpMethod = "POST")
    @PostMapping(value = "/exportData")
    @ResponseBody
    public BaseResponse exportData(@RequestBody @Valid ExportDataReq req,HttpServletResponse response) {
        BaseResponse baseResponse = exportService.exportData(req, response);
        return baseResponse;
    }
}

接口

public interface ExportService {
    BaseResponse exportData(ExportDataReq req, HttpServletResponse response);
}

服务

@Service
public class ExportServiceImpl implements ExportService {
    @Override
    public BaseResponse exportData(ExportDataReq req, HttpServletResponse response) {
        try (Connection conn = DriverManager.getConnection(req.getUrl(), req.getUser(), req.getPassword());
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(req.getSql())) {
            //创建wb
            HSSFWorkbook wb =  new HSSFWorkbook();
            //创建sheet
            HSSFSheet sheet = wb.createSheet("data");
            //添加标题
            addTitle(sheet,req.getTitles());
            //添加内容
            addRow(sheet,rs);
            //导出
            writeToResponse(response,wb,"data"+dateNow());
            return ResponseUtils.success();
        } catch (Exception ex) {
            ex.printStackTrace();
            return ResponseUtils.fail(ex.getMessage());
        }finally {

        }

    }
    protected void writeToResponse(HttpServletResponse response, HSSFWorkbook wb, String fileName) throws IOException {
        response.addHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(fileName,"utf-8")+".xls");
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setCharacterEncoding("UTF-8");
        OutputStream out = response.getOutputStream();
        wb.write(out);
        out.flush();
        wb.close();
    }
    private void addRow(HSSFSheet sheet, ResultSet rs) throws SQLException {
        ResultSetMetaData rsMetaData = rs.getMetaData();
        int columnCount = rsMetaData.getColumnCount();
        int rowNum = 1;
        while (rs.next()) {
            Row row = sheet.createRow(rowNum++);
            for (int i = 1; i <= columnCount; i++) {
                Cell cell = row.createCell(i - 1);
                String columnName = rsMetaData.getColumnName(i);
                switch (rsMetaData.getColumnType(i)) {
                    case Types.VARCHAR:
                    case Types.CHAR:
                        cell.setCellValue(rs.getString(columnName));
                        break;
                    case Types.INTEGER:
                        cell.setCellValue(rs.getInt(columnName));
                        break;
                    case Types.DOUBLE:
                        cell.setCellValue(rs.getDouble(columnName));
                        break;
                    // Add other types as needed
                    default:
                        cell.setCellValue(rs.getString(columnName));
                }
            }
        }
    }

    private void addTitle(HSSFSheet sheet, String[] titles) {
        HSSFRow row = sheet.createRow((int) 0);
        for (int i=0;i<titles.length;i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(titles[i]);
            sheet.autoSizeColumn(i);//自动设宽
        }
    }
    private String dateNow(){
        SimpleDateFormat sdf=new SimpleDateFormat("yyyyMMddhhmmss");
        return sdf.format(new java.util.Date());
    }
}

然后通过postman即可导出数据，因为入参类型比较复杂，sql可能较长，所以此处使用post请求。

测试

入参

{"url":"jdbc:mysql://XXXX:63306/gac_order",
"user":"XXX",
"password":"xxx",
"sql":"select order_id,create_time,user_id,status  from order_info limit 1000",
"titles":["order_id","create_time","user_id","status"]}

postman send按钮下拉的Send and Download 可以顺利下载下数据