123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131 |
- package com.boot.security.server.controller;
- import java.util.ArrayList;
- import java.util.Collections;
- import java.util.List;
- import java.util.Map;
- import javax.servlet.http.HttpServletResponse;
- import org.apache.commons.codec.digest.DigestUtils;
- import org.slf4j.Logger;
- import org.slf4j.LoggerFactory;
- import org.springframework.beans.factory.annotation.Autowired;
- import org.springframework.jdbc.core.JdbcTemplate;
- import org.springframework.security.access.prepost.PreAuthorize;
- import org.springframework.util.CollectionUtils;
- import org.springframework.web.bind.annotation.PostMapping;
- import org.springframework.web.bind.annotation.RequestMapping;
- import org.springframework.web.bind.annotation.RestController;
- import com.boot.security.server.annotation.LogAnnotation;
- import com.boot.security.server.utils.ExcelUtil;
- import io.swagger.annotations.Api;
- import io.swagger.annotations.ApiOperation;
- @Api(tags = "excel下载")
- @RestController
- @RequestMapping("/excels")
- public class ExcelController {
-
- private static final Logger log = LoggerFactory.getLogger("adminLogger");
- @Autowired
- private JdbcTemplate jdbcTemplate;
- @ApiOperation("校验sql,并返回sql返回的数量")
- @PostMapping("/sql-count")
- public Integer checkSql(String sql) {
- log.info(sql);
- sql = getAndCheckSql(sql);
- Integer count = 0;
- try {
- count = jdbcTemplate.queryForObject("select count(1) from (" + sql + ") t", Integer.class);
- } catch (Exception e) {
- throw new IllegalArgumentException(e.getMessage());
- }
- return count;
- }
- private String getAndCheckSql(String sql) {
- sql = sql.trim().toLowerCase();
- if (sql.endsWith(";") || sql.endsWith(";")) {
- sql = sql.substring(0, sql.length() - 1);
- }
- if (!sql.startsWith("select")) {
- throw new IllegalArgumentException("仅支持select语句");
- }
- return sql;
- }
- @LogAnnotation
- @ApiOperation("根据sql导出excel")
- @PostMapping
- @PreAuthorize("hasAuthority('excel:down')")
- public void downloadExcel(String sql, String fileName, HttpServletResponse response) {
- sql = getAndCheckSql(sql);
- List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
- if (!CollectionUtils.isEmpty(list)) {
- Map<String, Object> map = list.get(0);
- String[] headers = new String[map.size()];
- int i = 0;
- for (String key : map.keySet()) {
- headers[i++] = key;
- }
- List<Object[]> datas = new ArrayList<>(list.size());
- for (Map<String, Object> m : list) {
- Object[] objects = new Object[headers.length];
- for (int j = 0; j < headers.length; j++) {
- objects[j] = m.get(headers[j]);
- }
- datas.add(objects);
- }
- ExcelUtil.excelExport(
- fileName == null || fileName.trim().length() <= 0 ? DigestUtils.md5Hex(sql) : fileName, headers,
- datas, response);
- }
- }
- @LogAnnotation
- @ApiOperation("根据sql在页面显示结果")
- @PostMapping("/show-datas")
- @PreAuthorize("hasAuthority('excel:show:datas')")
- public List<Object[]> showData(String sql) {
- sql = getAndCheckSql(sql);
- List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
- if (!CollectionUtils.isEmpty(list)) {
- Map<String, Object> map = list.get(0);
- String[] headers = new String[map.size()];
- int i = 0;
- for (String key : map.keySet()) {
- headers[i++] = key;
- }
- List<Object[]> datas = new ArrayList<>(list.size());
- datas.add(headers);
- for (Map<String, Object> m : list) {
- Object[] objects = new Object[headers.length];
- for (int j = 0; j < headers.length; j++) {
- objects[j] = m.get(headers[j]);
- }
- datas.add(objects);
- }
- return datas;
- }
- return Collections.emptyList();
- }
- }
|