This is the first day of my participation in the August More text Challenge. For details, see: August More Text Challenge

Ps: The main content of this article is to obtain data from the back end, and multiple data stored in Excel form, and realize the upload and download based on OSS

The main knowledge

OSS object storage, EasyExcel both from ali cloud, please in advance to the basic knowledge of learning EasyExcel official documentation (must-see) : www.yuque.com/easyexcel/d… OSS official documents: please visit Aliyun or Tencent Cloud

The essence of this article, in fact, the database data is encapsulated as a collection, with EasyExcel custom packaging into Excel files, and uploaded to the OSS and generated on the OSS file download link in a stream, which takes into account the data too much, Excel file generation (including database search data) need to be asynchronous processing. In addition, in the local test, multi-thread transfer of millions of data will cause too much pressure on the computer, resulting in OOM

The main content

1. The entity class

The entity class inherits from the BaseRowModel class

@Data
public class User extends BaseRowModel {
    @ ExcelProperty (" number ")
    private int id;
    @ ExcelProperty (" name ")
    private String name;
    @ ExcelProperty (" date ")
    private Date date;
    @ ExcelProperty (" content ")
    private String content;

}
Copy the code

You can use annotations to set the header of an Excel spreadsheet, and the website has more customization and complex header Settings

2. Excel tools

@Component
public class EasyExcelUtil<T extends BaseRowModel>{


    /** Generate an Excel byte file generated by reading database data * the most common EasyExcel usage * returns excel file data stream */
    public ByteArrayOutputStream easyOut(List<T> list, Class<T> clazz) throws Exception{
        // The name of the file to be uploaded is not written
        String extension = ExcelTypeEnum.XLSX.getValue();

        // Create a byte array output stream to store the contents of the Excel file
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        /** * specifies which class to write to, and then writes to the first sheet, which is called the template, and then the stream closes. * You can define the various styles of the table. * Out is a byte stream * clazz is a reflection of the entity class */
        EasyExcel.write(out, clazz).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).sheet("Template").doWrite(list);
       
        returnout; }}Copy the code

Here is a very basic tool method, the main core code is actually a sentence

EasyExcel.write(out, clazz).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).sheet("Template").doWrite(list);
Copy the code

EasyExcel: one of the most commonly used tool classes, mainly provides the Write() method Write() : parameters, the first for the Excel table to Write to the destination, the summary of two ways to fill (more fill in please refer to the documentation)

• String: Stored in idea directly with the string content as the name
• IO stream: Usually an output stream, you can choose to store it locally or remotely as an input stream, such as OSS or local responde.getOutputStream.

RegisterWriteHandler: Table width adaptive, this imprecise sheet: This method also has many parameters, mainly providing the table page number and page name



DoWrite: Something that is written, such as a list that stores multiple pieces of data as objects

3. The OSS tools

The OSS tool class uses aliyun official Demo, which can be downloaded directly from the website and used by changing the domain name and access key

@Component
public class OSSClientUtil {

    // Endpoint is the domain name used to access the OSS. If you have created a Bucket on the OSS console, check the domain name on the console.
    // If you have not created a Bucket, see developer's Guide > Basic Concepts > Access domain name in the documentation center to select an endpoint.
    // The url is: https://help.aliyun.com/document_detail/oss/user_guide/oss_concept/endpoint.html?spm=5176.docoss/user_guide/endpoint_reg ion
    The format of the // endpoint is http://oss-cn-hangzhou.aliyuncs.com/. Note that there is no bucket name after http://.
    // For example, http://bucket-name.oss-cn-hangzhou.aliyuncs.com is an incorrect endpoint. Please delete bucket-name.
    private static String endpoint = "";

    AccessKeyId and accessKeySecret are OSS access keys that you can create and view on the console.
    // The link to create and view the access key is https://ak-console.aliyun.com/#/.
    AccessKeyId: accessKeySecret: accessKeyId: accessKeyId: accessKeySecret
    private static String accessKeyId = "";// Aliyun registration is available
    private static String accessKeySecret = "";// Aliyun registration is available

    // Bucket is used to manage the storage space of stored objects. For details, see Developer guide > Basic Concepts > Basic Concepts of OSS.
    // Bucket naming rules are as follows: The Bucket name can contain only lowercase letters, digits, and hyphens (-). It must start with a lowercase letter or digit, and must be 3 to 63 bytes in length.
    private static String bucketName = "mytesto";

    // Generate OSSClient, you can specify some parameters, see SDK Manual > Java-SDK > Initialization,
    / / link address is: https://help.aliyun.com/document_detail/oss/sdk/java-sdk/init.html?spm=5176.docoss/sdk/java-sdk/get-start
    OSS ossClient = null;
    public void fileUpload(ByteArrayOutputStream out){
        ossClient = new OSSClientBuilder().build(endpoint, accessKeyId, accessKeySecret);
        // Check whether the bucket name exists. If not, create a new bucket name
        //
        if (ossClient.doesBucketExist(bucketName)) {
            System.out.println("You have created a Bucket:" + bucketName + "。");
        } else {
            System.out.println("Your Bucket does not exist, create a Bucket:" + bucketName + "。");
            // Create a Bucket. For details, see SDK Manual > Java-SDK > Managing Bucket.
            / / link address is: https://help.aliyun.com/document_detail/oss/sdk/java-sdk/manage_bucket.html?spm=5176.docoss/sdk/java-sdk/init
            ossClient.createBucket(bucketName);
        }

        // Check the Bucket information. For details, see SDK Manual > Java-SDK > Managing Bucket.
        BucketInfo info = ossClient.getBucketInfo(bucketName);
        System.out.println("Bucket " + bucketName + The following is the information for");
        System.out.println("\t data center:" + info.getBucket().getLocation());
        System.out.println("\t creation time:" + info.getBucket().getCreationDate());
        System.out.println("\ T User logo:" + info.getBucket().getOwner());

        // Upload the specific file to obtain the data of the Excel file
        InputStream is = new ByteArrayInputStream(out.toByteArray());

        ossClient.putObject(bucketName, "456.xlsx", is);
        System.out.println("Object：" + "456.xlsx" + "Deposit OSS successfully!!");
        ossClient.shutdown();
    }


    // Get the URL that can be downloaded
    public String getDownURL(String fileName){

        ossClient = new OSSClientBuilder().build(endpoint, accessKeyId, accessKeySecret);
        Date expiration = new Date(new Date().getTime() + 3600l * 1000);// Set the expiration time to 1 hour
        / / generated URL
        URL url = ossClient.generatePresignedUrl(bucketName, fileName, expiration);
        if(url ! =null) {
            return url.toString();
        }
        return null;

    }

Copy the code

4. Interface execution

@RestController
public class UserController {

    @Autowired
    private UserService userService;

    @Autowired
    private EasyExcelUtil easyExcelUtil;

    @Autowired
    private OSSClientUtil ossClient;

    @GetMapping("/test")
    public void test(a) throws Exception {


        List<User> list = userService.findAll();

        // Upload files to OSS
        ossClient.fileUpload(easyExcelUtil.easyOut(list,User.class));
        // Get the download link based on the name of the file. The link is valid for one hour
        String s = ossClient.getDownURL("456.xlsx");


        System.out.println("File uploaded successfully!!");
        System.out.println(s);
        //return s;}}Copy the code

Here’s a simple local download example

 public void easyOutHttp(HttpServletResponse response, List<T> data, Class<T> clazz) throws Exception{
        try {
            String fileName = URLEncoder.encode("Application for Payment"."UTF-8");
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition"."attachment; filename=" + fileName + ".xlsx");
            // Convert to data
            EasyExcel.write(response.getOutputStream(), clazz).autoCloseStream(Boolean.FALSE)
                    .sheet("sheet").doWrite(data);
        } catch (Exception e){
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, String> map = new HashMap<String, String>();
            map.put("status"."failure");
            map.put("message"."Download file failed" + e.getMessage());
            //response.getWriter().println(JSON.toJSONString(map));}}Copy the code

The only difference between this method and the Excel method above is that the generated Excel table data is written to the response in the form of output stream. When accessing the page, the download window can be directly jumped out, and it can also be designed to open online. The specific setting properties are as follows: response.setHeader(“Content-disposition”, “attachment; filename=” + fileName + “.xlsx”); The Content – disposition: this property can be set when you write the Content of the is as part of a web page or as attachments to download and save the local local download specific Content can be reference book Jane blogger article www.jianshu.com/p/d4a85d025…

Two additional points:

1. This is a single Excel file generation, in the actual business, there may be multiple Excel files generated, and then packaged as a compressed package to download, but EasyExcel in the use of WorkBook or ExcelWriter when the design of the stream processing more, may occasionally appear the stream is not closed, The method of flow compression is used directly here. In a for loop, we iterate over the data set of each Excel file, adding the stream of each Excel file to the compressed stream one by one in the loop

 // Use EasyExcel to generate excel files and place them in an internal byte stream
 ByteArrayOutputStream byteOutputStream = new ByteArrayOutputStream();
 // Write data using the XLSX file format
 EasyExcel.write(byteOutputStream, clazz).excelType(ExcelTypeEnum.XLSX).sheet("Template").doWrite(batchlist);
 // Write a file to a stream
 ZipEntry zipEntry = new ZipEntry(s + ".xlsx");
 zipOutputStream.putNextEntry(zipEntry);
 // Write to the compressed stream
 byteOutputStream.writeTo(zipOutputStream);
Copy the code

2. The use of OSS is essentially to reduce the pressure of local download, but reading data and generating Excel files will also take a lot of time, so it is recommended that the step of generating Excel can be done asynchronously.

Article content is relatively rough, such as error welcome correction!

So that’s it.