Springboot 3 ways to download Excel

Let’s summarize the three ways that browser downloads and code native downloads can be implemented.

(In fact, it is generally in the code to generate Excel, and then upload to OSS, and then pass the link to the front desk, but I seem to have not implemented a direct click can be downloaded in the browser function, so this time together to summary the three implementation methods.)

EasyExcel– Browser download

1. The Maven environment
2. Complete code implementation

3. Achieve results

2.EasyExcel– local download

1. Complete code implementation
2. Realize the effect

3.Poi– Browser implementation download

1. The Maven environment
2. Code implementation
3. Achieve results

4. To summarize

🔥1.EasyExcel– browser download

1. The Maven environment

There are a lot of Maven easyExcel versions on the Internet, or recommend Alibaba easyExcel, simple operation, code is not redundant

        <! -- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.10</version>
        </dependency>
Copy the code

2. Complete code implementation

Control layer: Set response format and download directly

package com.empirefree.springboot.controller;

import com.alibaba.excel.EasyExcel;
import com.empirefree.springboot.pojo.User;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

/ * * *@program: springboot
 * @description:
 * @author: huyuqiao
 * @create: 2021/07/04 15:01 * /

@RestController
public class UserController {

    /** * Author: HuYuQiao * Description: Browser download --excel */
    @GetMapping("/testRespExcel")
    public void testRespExcel(HttpServletResponse response){
        response.addHeader("Content-Disposition"."attachment; filename=" + "huyuqiao.xlsx");
        response.setContentType("application/vnd.ms-excel; charset=gb2312");
        try {
// Get the OutputStream output from HttpServletResponse
            ServletOutputStream outputStream = response.getOutputStream();
            /* * EasyExcel has several different read methods, The write method in EasyExcel that outputs Excel through OutputStream is called * it returns a value of type ExcelWriterBuilder * ExcelWriterBuilde has a doWrite method that outputs data to the set Sheet */
            EasyExcel.write(outputStream, User.class).sheet("Test data").doWrite(getAllUser());
        } catch(IOException e) { e.printStackTrace(); }}public List<User> getAllUser(a){
        List<User> userList = new ArrayList<>();
        for (int i=0; i<100; i++){ User user = User.builder().name(Joe Hu+ i).password("huyuqiao").age(i).build();
            userList.add(user);
        }
        returnuserList; }}Copy the code

Entity class: Set the corresponding Excel attribute for User,value stands for the name in Excel, index stands for the column

package com.empirefree.springboot.pojo;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Builder;
import lombok.Data;

/ * * *@program: springboot
 * @description: user
 * @author: huyuqiao
 * @create: 2021/07/04 14:53 * /
@Data
@Builder
public class User  extends BaseRowModel{

    @excelProperty (value = "name ",index = 0)
    private String name;

    @excelProperty (value = "password ",index = 1)
    private String password;

    @excelProperty (value = "age ",index = 2)
    private Integer age;
}
Copy the code

3. Achieve results

🔥2.EasyExcel– local download

1. Complete code implementation

Maven is the same as above, just set the file output stream

package com.empirefree.springboot.controller;

import com.alibaba.excel.EasyExcel;
import com.empirefree.springboot.pojo.User;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

/ * * *@program: springboot
 * @description:
 * @author: huyuqiao
 * @create: 2021/07/04 15:01 * /

@RestController
public class UserController {
    /** * Author: HuYuQiao * Description: local generated --excel */
    @GetMapping("/testLocalExcel")
    public void testLocalExcel(a){
        // File output location
        OutputStream out = null;
        try {
            out = new FileOutputStream("C:\\Users\\EDY\\Desktop\\empirefree.xlsx");
            EasyExcel.write(out, User.class).sheet("Test data").doWrite(getAllUser());
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }finally {
            try {
                / / close the flow
                out.close();
            } catch(IOException e) { e.printStackTrace(); }}}public List<User> getAllUser(a){
        List<User> userList = new ArrayList<>();
        for (int i=0; i<100; i++){ User user = User.builder().name("Zhang"+ i).password("1234").age(i).build();
            userList.add(user);
        }
        returnuserList; }}Copy the code

2. Realize the effect

🔥3.Poi– Browser implementation download

1. The Maven environment

		<! Excel export tool -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>RELEASE</version>
		</dependency>
Copy the code

2. Code implementation

Control layer

       /** * Author: HuYuQiao * Description: excle-export */
    @GetMapping("/export")
    public String exportExcel(HttpServletResponse response) {
        System.out.println("Successfully reached everywhere excel....");
        String fileName = "test.xls";
        if (fileName == null || "".equals(fileName)) {
            return "File name cannot be empty!";
        } else {
            if (fileName.endsWith("xls")) {
                Boolean isOk = excelService.exportExcel(response, fileName, 1.10);
                if (isOk) {
                    return "Export successful!";
                } else {
                    return "Export failed!"; }}return "Wrong file format!"; }}Copy the code

Serviceimpl layer

  /** * Author: HuYuQiao * Description: excel-impl */
    @Override
    public Boolean exportExcel(HttpServletResponse response, String fileName, Integer pageNum, Integer pageSize) {
        log.info("Start exporting data...");
        // Query the data and assign it to ExcelData
        List<User> userList = userMapper.find();
        System.out.println(userList.size() + "size");
        List<String[]> list = new ArrayList<String[]>();
        for (User user : userList) {
            String[] arrs = new String[4];
            arrs[0] = String.valueOf(user.getId());
            arrs[1] = user.getUsername();
            arrs[2] = user.getPassword();
            arrs[3] = String.valueOf(user.getEnable());
            list.add(arrs);
        }
        // Table header assignment
        String[] head = {"Sequence"."Username"."Password"."State"};
        ExcelData data = new ExcelData();
        data.setHead(head);
        data.setData(list);
        data.setFileName(fileName);
        // Implement export
        try {
            ExcelUtil.exportExcel(response, data);
            log.info("End of exporting data...");
            return true;
        } catch (Exception e) {
            log.info("Failed to export data...);
            return false; }}Copy the code

Utility class

package com.example.demo.utils;

import com.example.demo.entity.ExcelData;
import com.example.demo.entity.User;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import static org.apache.poi.ss.usermodel.CellType.*;

/** * Author: HuYuQiao * Description: excelUtil */
@Slf4j
public class ExcelUtil {

    /** * Author: HuYuQiao * Description: excelUtil-export */
    public static void exportExcel(HttpServletResponse response, ExcelData data) {
        log.info(Export parsing begins, fileName:{},data.getFileName());
        try {
            // Instantiate HSSFWorkbook
            HSSFWorkbook workbook = new HSSFWorkbook();
            // Create an Excel form with the name of sheet
            HSSFSheet sheet = workbook.createSheet("sheet");
            // Set the table header
            setTitle(workbook, sheet, data.getHead());
            // Set cells and assign values
            setData(sheet, data.getData());
            // Set the browser download
            setBrowser(response, workbook, data.getFileName());
            log.info("Export parsing successful!");
        } catch (Exception e) {
            log.info("Export parsing failed!"); e.printStackTrace(); }}/** * Author: HuYuQiao * Description: excelUtil-settitle */
    private static void setTitle(HSSFWorkbook workbook, HSSFSheet sheet, String[] str) {
        try {
            HSSFRow row = sheet.createRow(0);
            // To set the column width, the second argument to setColumnWidth is multiplied by 256, in units of 1/256 character width
            for (int i = 0; i <= str.length; i++) {
                sheet.setColumnWidth(i, 15 * 256);
            }
            // Set it to center bold and format the time format
            HSSFCellStyle style = workbook.createCellStyle();
            HSSFFont font = workbook.createFont();
            font.setBold(true);
            style.setFont(font);
            style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
            // Create the header name
            HSSFCell cell;
            for (int j = 0; j < str.length; j++) { cell = row.createCell(j); cell.setCellValue(str[j]); cell.setCellStyle(style); }}catch (Exception e) {
            log.info(Failed to set table header while exporting!); e.printStackTrace(); }}/** * Author: HuYuQiao * Description: excelUtil-setData */
    private static void setData(HSSFSheet sheet, List<String[]> data) {
        try{
            int rowNum = 1;
            for (int i = 0; i < data.size(); i++) {
                HSSFRow row = sheet.createRow(rowNum);
                for (int j = 0; j < data.get(i).length; j++) {
                    row.createCell(j).setCellValue(data.get(i)[j]);
                }
                rowNum++;
            }
            log.info("Table assignment successful!");
        }catch (Exception e){
            log.info("Table assignment failed!"); e.printStackTrace(); }}/** * Author: HuYuQiao * Description: excelUtil-setBrowser */
    private static void setBrowser(HttpServletResponse response, HSSFWorkbook workbook, String fileName) {
        try {
            / / empty response
            response.reset();
            // Set the Header for response
            response.addHeader("Content-Disposition"."attachment; filename=" + fileName);
            OutputStream os = new BufferedOutputStream(response.getOutputStream());
            response.setContentType("application/vnd.ms-excel; charset=gb2312");
            // Write Excel to the output stream
            workbook.write(os);
            os.flush();
            os.close();
            log.info("Setup browser download successful!");
        } catch (Exception e) {
            log.info("Setup browser download failed!"); e.printStackTrace(); }}/** * Author: HuYuQiao * Description: excelUtil--importExcel */
    public static List<Object[]> importExcel(String fileName) {
        log.info(Import parsing begins, fileName:{},fileName);
        try {
            List<Object[]> list = new ArrayList<>();
            InputStream inputStream = new FileInputStream(fileName);
            Workbook workbook = WorkbookFactory.create(inputStream);
            Sheet sheet = workbook.getSheetAt(0);
            // Get the number of rows of the sheet
            int rows = sheet.getPhysicalNumberOfRows();
            for (int i = 0; i < rows; i++) {
                // Filter the header row
                if (i == 0) {
                    continue;
                }
                // Get data for the current row
                Row row = sheet.getRow(i);
                Object[] objects = new Object[row.getPhysicalNumberOfCells()];
                int index = 0;
                for (Cell cell : row) {
                    if (cell.getCellType().equals(NUMERIC)) {
                        objects[index] = (int) cell.getNumericCellValue();
                    }
                    if (cell.getCellType().equals(STRING)) {
                        objects[index] = cell.getStringCellValue();
                    }
                    if (cell.getCellType().equals(BOOLEAN)) {
                        objects[index] = cell.getBooleanCellValue();
                    }
                    if (cell.getCellType().equals(ERROR)) {
                        objects[index] = cell.getErrorCellValue();
                    }
                    index++;
                }
                list.add(objects);
            }
            log.info("Import file resolved successfully!");
            return list;
        }catch (Exception e){
            log.info("Import file parsing failed!");
            e.printStackTrace();
        }
        return null;
    }

    // Test the import
    public static void main(String[] args) {
        try {
            String fileName = "E:/test.xlsx";
            List<Object[]> list = importExcel(fileName);
            for (int i = 0; i < list.size(); i++) {
                User user = new User();
                user.setId((Integer) list.get(i)[0]);
                user.setUsername((String) list.get(i)[1]);
                user.setPassword((String) list.get(i)[2]);
                user.setEnable((Integer) list.get(i)[3]); System.out.println(user.toString()); }}catch(Exception e) { e.printStackTrace(); }}}Copy the code

3. Achieve results

4 🔥 summary

In general, when Excel needs to be downloaded in the browser, easyExcel of Alibaba is the fastest and most convenient, and note that response format needs to be set

Springboot 3 ways to download Excel

🔥1.EasyExcel– browser download

1. The Maven environment

2. Complete code implementation

3. Achieve results

🔥2.EasyExcel– local download

1. Complete code implementation

2. Realize the effect

🔥3.Poi– Browser implementation download

1. The Maven environment

2. Code implementation

3. Achieve results

4 🔥 summary

Related Posts

Mysql Database optimization

InnoDB second level snapshot works with current read

Hand tear redis distributed lock, next door Zhang xiaoshuai all understand!