1
2
3
4
5

<dependency>
    <groupId>com.github.xiaoymin</groupId>
    <artifactId>knife4j-spring-boot-starter</artifactId>
    <version>3.0.3</version>
</dependency>

1
2
3
4

<dependency>
      <groupId>com.github.xiaoymin</groupId>
      <artifactId>knife4j-spring-boot-starter</artifactId>
  </dependency>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

@Configuration
public class Knife4jConfiguration {

    @Value("${swagger.enable:true}")
    private boolean enableSwagger;

    @Bean(value = "defaultApi2")
    public Docket createRestApi() {
        return new Docket(DocumentationType.SWAGGER_2)
                .apiInfo(new ApiInfoBuilder()
                            .title("provider服务")
                            .version("1.0")
                            .build())
                .enable(enableSwagger)
                .select()
                .apis(RequestHandlerSelectors.basePackage("com.winterchen.nacos.rest"))
                .paths(PathSelectors.any())
                .build();
    }

}

1
2

swagger:
    enable: true

1
2
3
4

<dependency>
    <groupId>com.github.xiaoymin</groupId>
    <artifactId>knife4j-spring-boot-starter</artifactId>
</dependency>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

@Component
@Primary
public class SwaggerResourceConfig implements SwaggerResourcesProvider {

    private final RouteLocator routeLocator;
    private final GatewayProperties gatewayProperties;

    public SwaggerResourceConfig(RouteLocator routeLocator, GatewayProperties gatewayProperties) {
        this.routeLocator = routeLocator;
        this.gatewayProperties = gatewayProperties;
    }

    @Override
    public List<SwaggerResource> get() {
        List<SwaggerResource> resources = new ArrayList<>();
        List<String> routes = new ArrayList<>();
        //获取所有路由的ID
        routeLocator.getRoutes().subscribe(route -> routes.add(route.getId()));
        //过滤出配置文件中定义的路由->过滤出Path Route Predicate->根据路径拼接成api-docs路径->生成SwaggerResource
        gatewayProperties.getRoutes().stream().filter(routeDefinition -> routes.contains(routeDefinition.getId())).forEach(route -> {
            route.getPredicates().stream()
                    .filter(predicateDefinition -> ("Path").equalsIgnoreCase(predicateDefinition.getName()))
                    .forEach(predicateDefinition -> resources.add(swaggerResource(route.getId(),
                            predicateDefinition.getArgs().get(NameUtils.GENERATED_NAME_PREFIX + "0")
                                    .replace("**", "v2/api-docs"))));
        });

        return resources;
    }

    private SwaggerResource swaggerResource(String name, String location) {
        SwaggerResource swaggerResource = new SwaggerResource();
        swaggerResource.setName(name);
        swaggerResource.setLocation(location);
        swaggerResource.setSwaggerVersion("2.0");
        return swaggerResource;
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

@RestController
public class SwaggerHandler {

    @Autowired(required = false)
    private SecurityConfiguration securityConfiguration;

    @Autowired(required = false)
    private UiConfiguration uiConfiguration;

    private final SwaggerResourcesProvider swaggerResources;

    @Autowired
    public SwaggerHandler(SwaggerResourcesProvider swaggerResources) {
        this.swaggerResources = swaggerResources;
    }

    /**
     * Swagger安全配置，支持oauth和apiKey设置
     */
    @GetMapping("/swagger-resources/configuration/security")
    public Mono<ResponseEntity<SecurityConfiguration>> securityConfiguration() {
        return Mono.just(new ResponseEntity<>(
                Optional.ofNullable(securityConfiguration).orElse(SecurityConfigurationBuilder.builder().build()), HttpStatus.OK));
    }

    /**
     * Swagger UI配置
     */
    @GetMapping("/swagger-resources/configuration/ui")
    public Mono<ResponseEntity<UiConfiguration>> uiConfiguration() {
        return Mono.just(new ResponseEntity<>(
                Optional.ofNullable(uiConfiguration).orElse(UiConfigurationBuilder.builder().build()), HttpStatus.OK));
    }

    /**
     * Swagger资源配置，微服务中这各个服务的api-docs信息
     */
    @GetMapping("/swagger-resources")
    public Mono<ResponseEntity> swaggerResources() {
        return Mono.just((new ResponseEntity<>(swaggerResources.get(), HttpStatus.OK)));
    }
}-----------------

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132

@Slf4j
@Component
public class AuthorizeFilter implements GlobalFilter, Ordered {

    @Autowired
    UserRedisCollection userRedisCollection;

// (1)
    private boolean checkWhiteList(String uri) {
        boolean access = false;
        if (uri.contains("/login") || uri.contains("/v2/api-docs")) {
            access = true;
            if (uri.contains("logout")) {
                access = false;
            }
        }
        if (uri.contains("/open-api")) {
            access = true;
        }
        return access;
    }

    @Override
    public Mono<Void> filter(ServerWebExchange exchange, GatewayFilterChain chain) {
  // (2)
        ServerHttpRequest request = exchange.getRequest();
        ServerHttpResponse response = exchange.getResponse();
        String uri = request.getURI().getPath();

// (3)
        //前端访问不到header问题
        response.getHeaders().add("Access-Control-Allow-Headers","X-PINGOTHER, Origin, X-Requested-With, Content-Type, Accept, token");
        response.getHeaders().add("Access-Control-Expose-Headers", "token");
        ServerHttpRequest mutableReq = request.mutate()
                .header(DefaultConstants.IP_ADDRESS, getIpAddress(request))
                .build();
        ServerWebExchange mutableExchange = exchange.mutate().request(mutableReq).build();
        // (4)
//检查白名单
        if (checkWhiteList(uri)) {
            return chain.filter(mutableExchange);
        }

// (5)
        //从request获取token
        String accessToken = request.getHeaders().getFirst(DefaultConstants.TOKEN);
        log.info("AccessToken: [{}]", accessToken);

// (6)
        if (StringUtils.isBlank(accessToken)) {
            response.setStatusCode(HttpStatus.UNAUTHORIZED);
            return getVoidMono(response, ResultCodeEnum.UNAUTHORIZED, "未登录");
        }
        Claims claims = null;
        try {
// (7)
            claims = JwtUtil.parseJWT(accessToken, DefaultConstants.SECRET_KEY);

            if (claims == null) {
                response.setStatusCode(HttpStatus.UNAUTHORIZED);
                return getVoidMono(response, ResultCodeEnum.UNAUTHORIZED, "未登录");
            }
            log.info("claims is:{}", claims);
            if (claims.getSubject().equals(DefaultConstants.USER)){
                if(claims.get(DefaultConstants.USERID)!=null) {
                    Long userId = Long.parseLong(claims.get(DefaultConstants.USERID).toString());

                    log.info("userId:{}", userId);
                    Map<String, Object> map = Maps.newHashMapWithExpectedSize(1);
                    map.put(DefaultConstants.USERID,userId.toString());

// (8)
                    UserInfoEntity userInfo = userRedisCollection.getAuthUserInfoAndCache(userId);
                    //判断是否
                    if (userInfo == null) {
                        response.setStatusCode(HttpStatus.UNAUTHORIZED);
                        return getVoidMono(response, ResultCodeEnum.UNAUTHORIZED, "未登录");
                    }

// (9)
                    String token = JwtUtil.createToken(DefaultConstants.USER, map, DefaultConstants.SECRET_KEY);
                    response.getHeaders().add(DefaultConstants.TOKEN, token);

                    mutableReq = request.mutate().header(DefaultConstants.USER_ID, String.valueOf(userId))
                            .header(DefaultConstants.IP_ADDRESS, getIpAddress(request))
                            .build();
                    mutableExchange = exchange.mutate().request(mutableReq).build();
                    return chain.filter(mutableExchange);

                }

            }
        } catch (Exception e) {
            response.setStatusCode(HttpStatus.UNAUTHORIZED);
            return getVoidMono(response, ResultCodeEnum.UNAUTHORIZED, "未登录");
        }

        return getVoidMono(response, ResultCodeEnum.UNAUTHORIZED, "未登录");
    }

    private Mono<Void> getVoidMono(ServerHttpResponse serverHttpResponse, ResultCodeEnum resultCode, String responseText) {
        serverHttpResponse.getHeaders().add("Content-Type", "application/json;charset=UTF-8");
        CommonResult<?> result = CommonResult.failed(resultCode.getCode(), responseText);
        DataBuffer dataBuffer = serverHttpResponse.bufferFactory().wrap(JSON.toJSONString(result).getBytes());
        return serverHttpResponse.writeWith(Flux.just(dataBuffer));
    }

    public String getIpAddress(ServerHttpRequest request) {
        String ip = request.getHeaders().getFirst("x-forwarded-for");
        if (ip == null || ip.length() == 0 || "unknown".equalsIgnoreCase(ip)) {
            ip = request.getHeaders().getFirst("Proxy-Client-IP");
        }
        if (ip == null || ip.length() == 0 || "unknown".equalsIgnoreCase(ip)) {
            ip = request.getHeaders().getFirst("WL-Proxy-Client-IP");
        }
        if (ip == null || ip.length() == 0 || "unknown".equalsIgnoreCase(ip)) {
            ip = request.getHeaders().getFirst("HTTP_CLIENT_IP");
        }
        if (ip == null || ip.length() == 0 || "unknown".equalsIgnoreCase(ip)) {
            ip = request.getHeaders().getFirst("HTTP_X_FORWARDED_FOR");
        }
        if (ip == null || ip.length() == 0 || "unknown".equalsIgnoreCase(ip)) {
            ip = request.getRemoteAddress().getHostString();
        }
        return ip;
    }

    @Override
    public int getOrder() {
        return -100;
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14

@Autowired
    private RedisTemplate redisTemplate;

    public UserInfoEntity getAuthUserInfoAndCache(Long userId) {
        CommonAssert.meetCondition(userId == null, "未获取到userId");
        String key = DefaultConstants.USER_INFO_REDIS + userId;
        UserInfoEntity entity = (UserInfoEntity) redisTemplate.opsForValue().get(key);
        if (null != entity) {
            redisTemplate.opsForValue().set(key, entity, 60 * 24 * 60 * 60 * 1000, TimeUnit.MILLISECONDS);
            return entity;
        }
        CommonAssert.meetCondition(true, "当前用户未登陆，未获取到登陆信息");
        return null;
    }

1
2
3
4
5
6
7
8
9
10
11

public static String getUserIdByCurrent() {
        ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder
                .getRequestAttributes();
        if (attributes != null) {
            HttpServletRequest request = attributes.getRequest();
            return request.getHeader(ConstantsUtil.USER_ID);
        }else{
            return "";
        }

    }

1
2
3
4
5

<!-- springCloud-feign -->
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-starter-openfeign</artifactId>
        </dependency>

1
2
3
4
5
6
7

@FeignClient(value = "winter-nacos-provider")
public interface NacosProviderClient {

    @GetMapping("/nacos/feign-test/{string}")
    String echo2(@PathVariable String string);

}

1
2
3
4

@GetMapping("feign-test/{string}")
public String feignTest(@PathVariable String string) {
    return "Hello feign " + string;
}

1
2
3
4
5
6
7
8
9
10
11

<!-- springCloud-feign -->
<dependency>
    <groupId>org.springframework.cloud</groupId>
    <artifactId>spring-cloud-starter-openfeign</artifactId>
</dependency>
<!-- 对于服务provider的依赖 -->
<dependency>
    <groupId>com.winterchen</groupId>
    <artifactId>spring-cloud-nacos-provider</artifactId>
    <version>0.0.1-SNAPSHOT</version>
</dependency>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

@EnableDiscoveryClient
@EnableFeignClients
@SpringBootApplication
public class NacosConsumerApplication {

    @LoadBalanced
    @Bean
    public RestTemplate restTemplate() {
        return new RestTemplate();
    }

    public static void main(String[] args) {
        SpringApplication.run(NacosConsumerApplication.class, args);
    }

}

1
2
3
4
5
6
7
8

@Autowired
private NacosProviderClient nacosProviderClient;

@GetMapping("/feign-test/{str}")
public String feignTest(@PathVariable String str) {
    return nacosProviderClient.echo2(str);
}

1
2
3
4
5
6
7

@Value("${server.port}")
  String port;

@GetMapping("/ribbon-test")
  public String ribbonTest() {
      return "Hello ribbon , my port: " + port;
  }

1
2

@GetMapping("/nacos/ribbon-test")
  String ribbonTest();

1
2
3
4
5
6
7
8
9
10
11
12

server:
  port: 16012

spring:
  cloud:
    nacos:
      discovery:
        server-addr: 118.25.36.41:8848

test:
  config:
    refresh: false

1
2
3
4

@GetMapping("/ribbon-test")
    public String ribbonTest1() {
        return nacosProviderClient.ribbonTest();
    }

1
2

server:
  port: 16013

1
2

Hello ribbon , my port: 16012
Hello ribbon , my port: 16013

1
2
3
4
5
6
7
8
9
10

feign:
  client:
    config:
      winter-nacos-consumer:
        connectTimeout: 12000000
        readTimeout: 12000000
        NFLoadBalancerRuleClassName: com.netflix.loadbalancer.RandomRule
        OkToRetryOnAllOperations: true
        MaxAutoRetriesNextServer: 2
        MaxAutoRetries: 1

1
2
3
4
5
6

    ConnectTimeout: #单位ms,请求连接超时时间
    ReadTimeout:  #单位ms,请求处理的超时时间
    OkToRetryOnAllOperations: #对所有操作请求都进行重试
    MaxAutoRetriesNextServer: #切换实例的重试次数
    MaxAutoRetries: #对当前实例的重试次数
NFLoadBalancerRuleClassName #配置Ribbon负载均衡规则:IRule

1
2
3
4

@GetMapping("/hystrix-test")
  public String hystrixTest() {
      throw new RuntimeException("ex");
  }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

@Component
public class NacosProviderClientFallback implements NacosProviderClient{
    @Override
    public String echo2(String string) {
        return "error";
    }

    @Override
    public String ribbonTest() {
        return "error";
    }

    @GetMapping("/hystrix-test")
    public String hystrixTest() {
        return "hystrix error";
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13

@FeignClient(value = "winter-nacos-provider", fallback = NacosProviderClientFallback.class)
public interface NacosProviderClient {

    @GetMapping("/nacos/feign-test/{string}")
    String echo2(@PathVariable String string);

    @GetMapping("/nacos/ribbon-test")
    String ribbonTest();

    @GetMapping("/nacos/hystrix-test")
    String hystrixTest();

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

feign:
  client:
    config:
      winter-nacos-consumer:
        connectTimeout: 12000000
        readTimeout: 12000000
        NFLoadBalancerRuleClassName: com.netflix.loadbalancer.RandomRule
        OkToRetryOnAllOperations: true
        MaxAutoRetriesNextServer: 2
        MaxAutoRetries: 1
  hystrix:
    enabled: true #启用hystrix

hystrix:
  command:
    default:
      execution:
        isolation:
          thread:
            timeoutInMilliseconds: 20000 #默认的超时时间
    winter-nacos-consumer:
      execution:
        isolation:
          thread:
            timeoutInMilliseconds: 20000 #当前服务的超时时间，可以不写

1
2
3
4
5

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-mongodb</artifactId>
    <version>2.1.13.RELEASE</version>
</dependency>

1
2
3
4
5
6
7
8

spring:
  data:
    mongodb:
      uri: mongodb://192.168.150.154:17017
      database: ewell-label
    mongodb-target:
      uri: mongodb://192.168.150.154:17017
      database: ewell-label-target

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81

package com.winterchen.label.service.configuration;

import com.mongodb.MongoClient;
import com.mongodb.MongoClientURI;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.mongodb.MongoDbFactory;
import org.springframework.data.mongodb.MongoTransactionManager;
import org.springframework.data.mongodb.config.AbstractMongoConfiguration;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.SimpleMongoDbFactory;
import org.springframework.data.mongodb.core.convert.DefaultDbRefResolver;
import org.springframework.data.mongodb.core.convert.DefaultMongoTypeMapper;
import org.springframework.data.mongodb.core.convert.MappingMongoConverter;
import org.springframework.data.mongodb.core.mapping.MongoMappingContext;

/**
 * @author winterchen
 * @version 1.0
 * @date 2020/12/2 3:51 下午
 * @description 业务mongo数据源
 **/
@Configuration
public class BusinessMongoConfig extends AbstractMongoConfiguration {

    @Value("${spring.data.mongodb.uri}")
    private String uri;

    @Value("${spring.data.mongodb.database}")
    private String database;

    @Override
    protected String getDatabaseName() {
        return database;
    }

    @Override
    public MongoClient mongoClient() {
        MongoClientURI mongoClientURI = new MongoClientURI(uri);
        return new MongoClient(mongoClientURI);
    }

    @Primary
    @Bean("mongoMappingContext")
    public MongoMappingContext mongoMappingContext() {
        MongoMappingContext mappingContext = new MongoMappingContext();
        return mappingContext;
    }

    @Primary
    @Bean
    public MongoTransactionManager transactionManager(@Qualifier("mongoDbFactory") MongoDbFactory mongoDbFactory) throws Exception {
        return new MongoTransactionManager(mongoDbFactory);
    }

    @Primary
    @Bean("mongoDbFactory")
    public MongoDbFactory mongoDbFactory() {
        return new SimpleMongoDbFactory(mongoClient(), getDatabaseName());
    }

    @Primary
    @Bean("mappingMongoConverter") //使用自定义的typeMapper去除写入mongodb时的“_class”字段
    public MappingMongoConverter mappingMongoConverter(@Qualifier("mongoDbFactory") MongoDbFactory mongoDbFactory,
                                                       @Qualifier("mongoMappingContext") MongoMappingContext mongoMappingContext) throws Exception {
        DefaultDbRefResolver dbRefResolver = new DefaultDbRefResolver(mongoDbFactory);
        MappingMongoConverter converter = new MappingMongoConverter(dbRefResolver, mongoMappingContext);
        converter.setTypeMapper(new DefaultMongoTypeMapper(null));
        return converter;
    }

    @Primary
    @Bean(name = "mongoTemplate")
    public MongoTemplate getMongoTemplate(@Qualifier("mongoDbFactory") MongoDbFactory mongoDbFactory,
                                          @Qualifier("mappingMongoConverter") MappingMongoConverter mappingMongoConverter) throws Exception {
        return new MongoTemplate(mongoDbFactory, mappingMongoConverter);
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79

package com.winterchen.label.service.configuration;

import com.mongodb.MongoClient;
import com.mongodb.MongoClientURI;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.mongodb.MongoDbFactory;
import org.springframework.data.mongodb.MongoTransactionManager;
import org.springframework.data.mongodb.config.AbstractMongoConfiguration;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.SimpleMongoDbFactory;
import org.springframework.data.mongodb.core.convert.DefaultDbRefResolver;
import org.springframework.data.mongodb.core.convert.DefaultMongoTypeMapper;
import org.springframework.data.mongodb.core.convert.MappingMongoConverter;
import org.springframework.data.mongodb.core.mapping.MongoMappingContext;

/**
 * @author winterchen
 * @version 1.0
 * @date 2020/12/2 3:53 下午
 * @description TODO
 **/
@Configuration
public class TargetMongoConfig extends AbstractMongoConfiguration {

    @Value("${spring.data.mongodb-target.uri}")
    private String uri;

    @Value("${spring.data.mongodb-target.database}")
    private String database;

    @Override
    protected String getDatabaseName() {
        return database;
    }

    @Override
    public MongoClient mongoClient() {
        MongoClientURI mongoClientURI = new MongoClientURI(uri);
        return new MongoClient(mongoClientURI);
    }

    @Bean("targetMongoMappingContext")
    public MongoMappingContext mongoMappingContext() {
        MongoMappingContext mappingContext = new MongoMappingContext();
        return mappingContext;
    }

    @Bean("TARGET_MONGO_TRANSACTION_MANAGER")
    public MongoTransactionManager transactionManager(@Qualifier("targetMongoDbFactory") MongoDbFactory mongoDbFactory) throws Exception {
        return new MongoTransactionManager(mongoDbFactory);
    }

    @Bean("targetMongoDbFactory")
    public MongoDbFactory mongoDbFactory() {
        return new SimpleMongoDbFactory(mongoClient(), getDatabaseName());
    }

    @Bean("targetMappingMongoConverter") //使用自定义的typeMapper去除写入mongodb时的“_class”字段
    public MappingMongoConverter mappingMongoConverter(@Qualifier("targetMongoDbFactory") MongoDbFactory mongoDbFactory,
                                                       @Qualifier("targetMongoMappingContext") MongoMappingContext mongoMappingContext) throws Exception {
        DefaultDbRefResolver dbRefResolver = new DefaultDbRefResolver(mongoDbFactory);
        MappingMongoConverter converter = new MappingMongoConverter(dbRefResolver, mongoMappingContext);
        converter.setTypeMapper(new DefaultMongoTypeMapper(null));
        return converter;
    }

    /**
     * MongoTemplate实现
     */
    @Bean(name = "targetMongoTemplate")
    public MongoTemplate getMongoTemplate(@Qualifier("targetMongoDbFactory") MongoDbFactory mongoDbFactory,
                                          @Qualifier("mappingMongoConverter") MappingMongoConverter mappingMongoConverter) throws Exception {
        return new MongoTemplate(mongoDbFactory, mappingMongoConverter);
    }

}

1
2

@Autowired
  private MongoTemplate mongoTemplate;

1
2
3

@Autowired
  @Qualifier("targetMongoTemplate")
  private MongoTemplate targetMongoTemplate;

1

@Transactional(rollbackFor = Throwable.class)

1

@Transactional(rollbackFor = Throwable.class, transactionManager = "TARGET_MONGO_TRANSACTION_MANAGER")

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

public boolean addReportExcelToCloud(ReportResult rr) {

OutputStream out = new ByteArrayOutputStream();
ExcelProjectUtils eu = new ExcelProjectUtils();
eu.exportExcel(rr, out);   //<1>
ConvertUtil cu = new ConvertUtil();
try {
ByteArrayInputStream byteInput = cu.parse(out);
String rs = PicUploadToYun.uploadExcel(SysContent.getFileRename("案场数据报.xls"), byteInput);  //<2>
addReportExcelToDB(rr, rs);  //<3>
return true;
} catch (Exception e) {
e.printStackTrace();
}

return false;
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392

/**
 * 周报年报生成excel
 * 
 * @param report
 * @param out
 */
public void exportExcel(ReportResult report, OutputStream out) {

// 判断传入的时间间隔
String dateStr = "";
String reportName = "";
List<String> dateCount = DateUtil.getTwoDateEveryDay(report.getStartTime(), report.getEndTime());
if (dateCount.size() <= 7) {
dateStr += "本周";
reportName += "案场周报";
} else if (dateCount.size() >= 28 && dateCount.size() <= 31) {
dateStr += "本月";
reportName += "案场月报";
} else if (dateCount.size() >= 85 && dateCount.size() <= 100) {
dateStr += "本季度";
reportName += "案场季报";
} else if (dateCount.size() >= 180 && dateCount.size() <= 185) {
dateStr += "本半年度";
reportName += "案场半年报";
} else if (dateCount.size() >= 360 && dateCount.size() <= 367) {
dateStr += "本年度";
reportName += "案场年报";
} else {
dateStr += "时间段内";
reportName += "案场阶段报";
}
report.setReportName(reportName);
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(report.getReportName() + report.getStartTime() + " - " + report.getEndTime());
// 设置表格默认列宽度为100个字节
sheet.setDefaultColumnWidth((short) 100);
/** ----------样式一：标题 ------------ **/
HSSFCellStyle style = workbook.createCellStyle();
// 设置这些样式
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
//style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 生成一个字体
HSSFFont font = workbook.createFont();
font.setFontName("宋体");
//font.setColor(HSSFColor.VIOLET.index);
font.setFontHeightInPoints((short) 14);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);
/***---------样式二：小标题---------***/
HSSFCellStyle style2 = workbook.createCellStyle();
style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
//style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
style2.setAlignment(HSSFCellStyle.ALIGN_LEFT);

//style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 生成另一个字体
HSSFFont font2 = workbook.createFont();
//font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
font2.setFontName("宋体");
font2.setFontHeightInPoints((short) 11);
font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style2.setFont(font2);

/***    样式三：右侧日期       ***/
HSSFCellStyle style3 = workbook.createCellStyle();
//样式
style3.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style3.setBorderRight(HSSFCellStyle.BORDER_THIN);
style3.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
style3.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//字体
HSSFFont font3 = workbook.createFont();
font3.setFontName("宋体");
font3.setFontHeightInPoints((short) 11);
style3.setFont(font3);

/**       样式四：主内容        ***/
HSSFCellStyle style4 = workbook.createCellStyle();
//样式
style4.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style4.setBorderRight(HSSFCellStyle.BORDER_THIN);
style4.setAlignment(HSSFCellStyle.ALIGN_LEFT);
//字体
HSSFFont font4 = workbook.createFont();
font4.setFontName("宋体");
font4.setFontHeightInPoints((short) 11);
style4.setFont(font4);

/**       样式五：底侧空内容       ***/
HSSFCellStyle style5 = workbook.createCellStyle();
//样式
style5.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style5.setBorderRight(HSSFCellStyle.BORDER_THIN);
style5.setAlignment(HSSFCellStyle.ALIGN_LEFT);
style5.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//字体
HSSFFont font5 = workbook.createFont();
font5.setFontName("宋体");
font5.setFontHeightInPoints((short) 11);
style5.setFont(font5);


// 声明一个画图的顶级管理器
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
// 定义注释的大小和位置,详见文档
HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5));
// 设置注释内容
comment.setString(new HSSFRichTextString("数据报"));
// 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
comment.setAuthor("saas");

// 产生表格标题行 -- 项目名称
HSSFRow row = sheet.createRow(0);
createCellAndRow(style4, report.getProjectName(), row);

// 产生表格标题行 -- 周报名称
row = sheet.createRow(1);
createCellAndRow(style, report.getReportName(), row);

// 产生表格标题行 -- 起始时间-终止时间
row = sheet.createRow(2);
String startTime = DateUtil.format(DateUtil.parse(report.getStartTime(), DateUtil.PATTERN_CLASSICAL_SIMPLE),
DateUtil.PATTERN_CLASSICAL_SIMPLE_YMD);
String endTime = DateUtil.format(DateUtil.parse(report.getEndTime(), DateUtil.PATTERN_CLASSICAL_SIMPLE),
DateUtil.PATTERN_CLASSICAL_SIMPLE_YMD);
String date = "日期：" + startTime + " - " + endTime;
createCellAndRow(style3, date, row);

// 接访情况标题
row = sheet.createRow(3);
createCellAndRow(style2, "·接访情况", row);

// 接访客户组数
row = sheet.createRow(4);
Integer visitCount = report.getVisitCount();
String visitNum = "1、" + dateStr + "共计接访客户" + visitCount + "组，来访量";
if (visitCount < 40) {
visitNum += "较少，有待提升";
} else if (visitCount >= 41 && visitCount <= 99) {
visitNum += "尚可，还有提高空间";
} else if (visitCount >= 100 && visitCount <= 139) {
visitNum += "很多";
} else if (visitCount > 140) {
visitNum += "火爆";
}
createCellAndRow(style4, visitNum, row);

// 有效接访率
row = sheet.createRow(5);
Double visitRate = new Double(report.getValidVisitRate());
String visitRateStr = "2、有效接访率为" + visitRate + "%，接访成效";
if (visitRate < 50) {
visitRateStr += "较低，有待提升";
} else if (visitRate >= 50 && visitRate <= 65) {
visitRateStr += "尚可，还有提高空间";
} else if (visitRate >= 65 && visitRate <= 80) {
visitRateStr += "很高";
} else if (visitRate > 80) {
visitRateStr += "极高";
}
createCellAndRow(style4, visitRateStr, row);

// 首访有效率
row = sheet.createRow(6);
Double newVisitRate = new Double(report.getValidNewCuVisitRate());
String newVisitStr = "3、首访有效率为" + newVisitRate + "%，来访转储客的概率";
if (newVisitRate < 40) {
newVisitStr += "较差，有待提升";
} else if (newVisitRate >= 40 && newVisitRate <= 60) {
newVisitStr += "尚可，还有提高空间";
} else if (newVisitRate >= 60 && newVisitRate <= 75) {
newVisitStr += "很高";
} else if (newVisitRate > 75) {
newVisitStr += "极高";
}
createCellAndRow(style4, newVisitStr, row);

// 老客户接访占比
row = sheet.createRow(7);
Double oldVisitRate = new Double(report.getOldCuVisitRate());
String oldVisitStr = "4、老客户接访比为" + oldVisitRate + "%，老客户接访的占比";
if (oldVisitRate < 20) {
oldVisitStr += "较低";
} else if (oldVisitRate >= 20 && oldVisitRate <= 40) {
oldVisitStr += "尚可";
} else if (oldVisitRate >= 40 && oldVisitRate <= 60) {
oldVisitStr += "很高";
} else if (oldVisitRate > 60) {
oldVisitStr += "极高";
}
createCellAndRow(style4, oldVisitStr, row);

//空行
row = sheet.createRow(8);
createCellAndRow(style4, "", row);

// 储客情况
row = sheet.createRow(9);
createCellAndRow(style2, "·储客情况", row);

// 新增储客
row = sheet.createRow(10);
Integer newCuCount = report.getNewCuCount();
String newCuStr = "1、" + dateStr + "新增储客" + newCuCount + "组，新增量";
if (newCuCount < 30) {
newCuStr += "较少，有待提升";
} else if (newCuCount >= 31 && newCuCount <= 60) {
newCuStr += "尚可，还有提高空间";
} else if (newCuCount >= 61 && newCuCount <= 79) {
newCuStr += "很多";
} else if (newCuCount > 80) {
newCuStr += "爆满";
}
createCellAndRow(style4, newCuStr, row);

// 累计老客户
row = sheet.createRow(11);
Integer oldCuCount = report.getTotalOldCuCount();
Integer totalCuCount = report.getTotalCuCount();
Double oldCuRate = new Double(SysContent.getTwoNumberForValue(oldCuCount, totalCuCount));
String oldCuStr = "2、累计老客户总量为" + oldCuCount + "组，老客户占比为" + oldCuRate + "%，显示老客户关注度";
if (oldCuRate < 15) {
oldCuStr += "较低，有待提升";
} else if (oldCuRate >= 15 && oldCuRate <= 25) {
oldCuStr += "尚可，还有提高空间";
} else if (oldCuRate >= 25 && oldCuRate <= 40) {
oldCuStr += "很高";
} else if (oldCuRate > 40) {
oldCuStr += "极高";
}
createCellAndRow(style4, oldCuStr, row);

// 累计总储客
row = sheet.createRow(12);
String totalOldCuStr = "3、累计总储客" + totalCuCount + "组";
createCellAndRow(style4, totalOldCuStr, row);

// 成交情况(周报没有，其他有)
if (report.getSubscribeHouseCount() != null) {

//空行
row = sheet.createRow(13);
createCellAndRow(style4, "", row);

row = sheet.createRow(14);
createCellAndRow(style2, "·成交情况", row);

// 新增认购套数
row = sheet.createRow(15);
Integer subscribeHouseCount = report.getSubscribeHouseCount();
Double subscribeHouseRate = new Double(report.getSubscribeHouseRate());
String subscribeHouseStr = "1、" + dateStr + "新增认购套数" + subscribeHouseCount + "套，较" + dateStr + "同期";
if (subscribeHouseRate < 0) {
subscribeHouseStr += "减少";
} else {
subscribeHouseStr += "增长";
}
subscribeHouseStr += Math.abs(subscribeHouseRate) + "%";
createCellAndRow(style4, subscribeHouseStr, row);

// 新增认购金额
row = sheet.createRow(16);
Long subscribeMoney = report.getSubscribeMoney();
Double subscribeMoneyRate = new Double(report.getSubscribeMoneyRate());
String subscribeMoneyStr = "   新增认购金额" + subscribeMoney + "万元，较" + dateStr + "同期";
if (subscribeHouseRate < 0) {
subscribeMoneyStr += "减少";
} else {
subscribeMoneyStr += "增长";
}
subscribeMoneyStr += Math.abs(subscribeMoneyRate) + "%";
createCellAndRow(style4, subscribeMoneyStr, row);

// 新增签约套数
row = sheet.createRow(17);
Integer signCount = report.getSignCount();
Double signRate = new Double(report.getSignRate());
String signStr = "2、新增签约套数" + signCount + "套,较" + dateStr + "同期";
if (signRate < 0) {
signStr += "减少";
} else {
signStr += "增长";
}
signStr += Math.abs(signRate) + "%";
createCellAndRow(style4, signStr, row);

// 新增签约金额
row = sheet.createRow(18);
Long signHouseMoney = report.getSignHouseMoney();
Double signHouseMoneyRate = new Double(report.getSignHouseMoneyRate());
String signHouseMoneyStr = "   新增签约金额" + signHouseMoney + "万元，较" + dateStr + "同期";
if (signHouseMoneyRate < 0) {
signHouseMoneyStr += "减少";
} else {
signHouseMoneyStr += "增长";
}
signHouseMoneyStr += Math.abs(signHouseMoneyRate) + "%";
createCellAndRow(style4, signHouseMoneyStr, row);

// 新接访签约率
row = sheet.createRow(19);
Double newCustomerSignedRate = new Double(report.getNewCustomerSignedRate());
String newCustomerSignedStr = "3、" + dateStr + "新客户接访签约率" + newCustomerSignedRate + "%，接访签约概率";
if (newCustomerSignedRate < 4) {
newCustomerSignedStr += "较低，与理想值差距大";
} else if (newCustomerSignedRate >= 4 && newCustomerSignedRate <= 6) {
newCustomerSignedStr += "尚可，还有提高空间";
} else if (newCustomerSignedRate >= 6 && newCustomerSignedRate <= 7) {
newCustomerSignedStr += "很高";
} else if (newCustomerSignedRate > 7) {
newCustomerSignedStr += "非常高";
}
createCellAndRow(style4, newCustomerSignedStr, row);

// 储客签约率
row = sheet.createRow(20);
Double momeryCustomerSignedRate = new Double(report.getMomeryCustomerSignedRate());
String momeryCustomerSignedStr = "4、储客签约率" + momeryCustomerSignedRate + "%，储备客户签约概率";
if (momeryCustomerSignedRate < 7) {
momeryCustomerSignedStr += "较低，与理想值差距大";
} else if (momeryCustomerSignedRate >= 7 && momeryCustomerSignedRate <= 12) {
momeryCustomerSignedStr += "尚可，还有提高空间";
} else if (momeryCustomerSignedRate >= 12 && momeryCustomerSignedRate <= 15) {
momeryCustomerSignedStr += "很高";
} else if (momeryCustomerSignedRate > 15) {
momeryCustomerSignedStr += "非常高";
}
createCellAndRow(style4, momeryCustomerSignedStr, row);

// 老客户签约率
row = sheet.createRow(21);
Double oldCustomerSignedRate = new Double(report.getOldCustomerSignedRate());
String oldCustomerSignedStr = "5、老客户签约率为23.2%，高意向客户签约概率";
if (oldCustomerSignedRate < 25) {
oldCustomerSignedStr += "较低，与理想值差距大";
} else if (oldCustomerSignedRate >= 25 && oldCustomerSignedRate <= 35) {
oldCustomerSignedStr += "尚可，还有提高空间";
} else if (oldCustomerSignedRate >= 35 && oldCustomerSignedRate <= 50) {
oldCustomerSignedStr += "很高";
} else if (oldCustomerSignedRate > 50) {
oldCustomerSignedStr += "非常高";
}
createCellAndRow(style4, oldCustomerSignedStr, row);

// 认购客户签约率
row = sheet.createRow(22);
Double contratCuSignedRate = new Double(report.getContratCuSignedRate());
String contratCuSignedStr = "6、认购客户签约率为92%，已认购客户签约率";
if (contratCuSignedRate < 95) {
contratCuSignedStr += "不高，较多退订或拒签";
} else if (contratCuSignedRate >= 95 && contratCuSignedRate <= 97) {
contratCuSignedStr += "尚可，一定数量退订或拒签";
} else if (contratCuSignedRate >= 97 && contratCuSignedRate <= 99) {
contratCuSignedStr += "很高";
} else if (contratCuSignedRate > 99) {
contratCuSignedStr += "非常高";
}
createCellAndRow(style4, contratCuSignedStr, row);

//空行
row = sheet.createRow(23);
createCellAndRow(style4, "", row);

//底侧
row = sheet.createRow(24);
createCellAndRow(style5, "", row);

}else{
row = sheet.createRow(13);
createCellAndRow(style5, "", row);
}

try {
workbook.write(out);
} catch (IOException e) {
e.printStackTrace();
}
}

private void createCellAndRow(HSSFCellStyle style, String text, HSSFRow row) {
HSSFCell cell = row.createCell(0);
cell.setCellStyle(style);
HSSFRichTextString rs = new HSSFRichTextString(text);
cell.setCellValue(rs);
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61

/**
 * 上传Excel
 * @param fileNewName
 * @param uploadFile
 * @return
 */
public static String uploadExcel(String fileNewName,ByteArrayInputStream uploadFile){
// 设置用户属性, 包括appid, secretId和SecretKey
// 这些属性可以通过cos控制台获取(https://console.qcloud.com/cos)
String version = PropertiesUtil.getValue("version");
 long appId = "你的appId";
                 String secretId = "你的secretId ";
                 String secretKey = "你的secretKey ";

// 设置要操作的bucket
String bucketName = "root";
// 初始化客户端配置
ClientConfig clientConfig = new ClientConfig();
// 设置bucket所在的区域，比如广州(gz), 天津(tj)
clientConfig.setRegion("sh");
// 初始化秘钥信息
Credentials cred = new Credentials(appId, secretId, secretKey);
// 初始化cosClient
COSClient cosClient = new COSClient(clientConfig, cred);
// 文件操作 //
// 1. 上传文件(默认不覆盖)
// 将本地的local_file_1.txt上传到bucket下的根分区下,并命名为sample_file.txt
// 默认不覆盖, 如果cos上已有文件, 则返回错误
String cosFilePath = "/report/" + fileNewName;

byte[] localFilePath1 = null;
try {
localFilePath1 = ConvertUtil.toByteArray(uploadFile);
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}

UploadFileRequest uploadFileRequest = new UploadFileRequest(bucketName, cosFilePath, localFilePath1);
uploadFileRequest.setEnableShaDigest(false);
String uploadFileRet = cosClient.uploadFile(uploadFileRequest);
System.out.println("upload file ret:" + uploadFileRet);
//获取保存路径
ObjectMapper om = new ObjectMapper();
HashMap map = new HashMap<>();
try {
map = om.readValue(uploadFileRet, HashMap.class);
} catch (JsonParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (JsonMappingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
HashMap<String, String> value = (HashMap<String, String>) map.get("data");
return value.get("source_url");

}