This is the 19th day of my participation in the Genwen Challenge

preface

EasyExcel is a sharp tool to generate Excel, this paper wants to solve the problem is when the generation of Excel time is too long, synchronous download file old timeout problem. Since synchronization always times out, we need to change the Excel generation method to asynchronous. Whenever the user clicks the corresponding button, mq is sent to the handler, the corresponding data is put into Excel and uploaded to OSS. There are two steps: generation and shard upload.

The package used

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.210.</version>
        </dependency>
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.72.</version>
        </dependency>
        <dependency>
            <groupId>com.aliyun.oss</groupId>
            <artifactId>aliyun-sdk-oss</artifactId>
            <version>3.0. 0</version>
        </dependency>
Copy the code

Hutool is used to manipulate files.

Use EasyExcel to generate Excel

One thing to note in the first step is that we need to save Excel in a local temporary file because it will consume a lot of memory if the direct stream mode is passed to OSS. See github.com/alibaba/eas…

        ExcelWriter excelWriter = null;
        String fileName = "test.xlsx";
        try {
            // Create a file
            FileUtil.touch(fileName);
            excelWriter = EasyExcel.write(fileName, User.class).build();
            WriteSheet writeSheet = EasyExcel.writerSheet("User List").build();
            List<User> userList = new ArrayList<>();
            userList.add(new User(1."wlz"));
            userList.add(new User(2."lin"));
            userList.add(new User(3."shi"));
            userList.add(new User(4."ying"));
            userList.add(new User(5."di"));
            excelWriter.write(userList, writeSheet);
        } catch (Exception e) {

        } finally {
            if(excelWriter ! =null) { excelWriter.finish(); }}Copy the code

Fragment upload OSS

You are advised to use the FRAGMENT upload function of OSS to facilitate rapid upload of large files.

        InputStream inputStream = null;
        OSS ossClient = null;
        try {
            File file = new File(fileName);
            String fileAddress = "test.xlsx";
            ossClient = new OSSClientBuilder().build("region"."secretId"."secretKey");
            / / create InitiateMultipartuploadRequest object.
            InitiateMultipartUploadRequest request = new InitiateMultipartUploadRequest("bucketName", fileAddress);// Initialize the fragment
            InitiateMultipartUploadResult upresult = ossClient.initiateMultipartUpload(request);
            // Return uploadId, which is the unique identifier of the fragment upload event. You can initiate operations based on this ID, such as canceling the fragment upload and querying the fragment upload.
            String uploadId = upresult.getUploadId();
            // partETags are collections of partETags. PartETag consists of the ETag of the fragment and the fragment number.
            List<PartETag> partETags = new ArrayList<>();
            // Calculate how many shards the file has.
            // the size of a single fragment is 2MB
            long partSize = 2 * 1024 * 1024L;// "
      
        "localFile to be uploaded
      
            long fileLength = file.length();
            int partCount = (int) (fileLength / partSize);
            if(fileLength % partSize ! =0) {
                partCount++;
            }
            // Iterate over the fragment upload.
            for (int i = 0; i < partCount; i++) {
                long startPos = i * partSize;
                long curPartSize = (i + 1 == partCount) ? (fileLength - startPos) : partSize;

                inputStream = new FileInputStream(fileName);

                // Skip the uploaded shards.
                inputStream.skip(startPos);
                UploadPartRequest uploadPartRequest = new UploadPartRequest();
                uploadPartRequest.setBucketName("bucketName");
                uploadPartRequest.setKey(fileAddress);
                uploadPartRequest.setUploadId(uploadId);
                uploadPartRequest.setInputStream(inputStream);

                // Set the fragment size. Except for the last shard, there is no size limit, and all shards must be at least 100KB. uploadPartRequest.setPartsize(curPartSize);
                // Set the shard number. Each fragment uploaded has a fragment number that ranges from 1 to 10000. If the fragment number exceeds this range, oSS returns an InvalidArgument error code.
                uploadPartRequest.setPartNumber(i + 1);

                // Each shard does not need to be uploaded in sequence, and can even be uploaded from different clients. The oSS will sort the shard number to form a complete file.
                UploadPartResult uploadPartResult = ossClient.uploadPart(uploadPartRequest);


                // After each upload shard, the 0SS result will contain a PartETag. PartETags will be saved in partETags
                partETags.add(uploadPartResult.getPartETag());
            }
            / / create CompleteMultipartUploadRequest object.
            // All valid partETags are required to complete the shard upload operation. OSS will verify the validity of each shard after receiving the submitted partETags. When all data shards are validated, OSS will combine them into a complete file.
            CompleteMultipartUploadRequest completeMultipartUploadRequest = new CompleteMultipartUploadRequest("", fileAddress, uploadId, partETags);


            // Upload completed.
            CompleteMultipartUploadResult completeMultipartUploadResult = ossClient.completeMultipartUpload(completeMultipartUploadRequest);


        } catch (Exception e) {
        }
        finally {
            try {
                if(ossClient ! =null) {
                    ossClient.shutdown();
                }
                if(inputStream ! =null) {
                    inputStream.close();
                }
                if(file! =null) {// Use Hutool to delete filesfile.delete(); }}catch (Exception e) {

            }
        }
Copy the code

conclusion

Breaking big tasks into small chunks, breakthroughs, is the way we program.