How to generate an Excel file using SpringMVC?

This article brings you how to use SpringMVC to generate an Excel file? It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

To generate an Excel file or import the data of an Excel file through Java, you need to use a POI package of Apache. Here I will provide this package first.

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.9</version>
</dependency>

Here I am using SpringBoot to create a project. I won’t go into details about the creation process. I just started to import the database data into an Excel file.
Officially started writing this Demo
1. Create an ExcelUtil class. This class operates Excel with two main methods.

1-1.exportFile(): Export the data into an Excel file;
1-2.importFile(): Import the data from the specified file;

The users table structure is as follows:

 id | username  | password  |
+----+-----------+-----------+
|  1 | yx12156ok | yx27787ok |
|  2 | yangxiang | 123456    |
|  3 | zhangsan  | 666666    |
|  4 | wangwu    | 999999    |
|  5 | xiaoming  | xiaoming

Here we will first post the implementation of the ExcelUtil class import, and then explain it in detail below:

public class ExcelUtil {
    
    private final String excel2003 = "xls";
    private final String excel2007 = "xlsx";
    
    private Workbook workbook;
    private Sheet sheet;
    private Row row;
    private Cell cell;
    private CellStyle style;
    
    private File file;
    
    //初始化表结构和生成表头
    public ExcelUtil(String[] titles,File file) {
        this.file = file;
        String fileName = this.file.getName();
        this.workbook = getWorkbook(fileName);
        if(workbook == null) return;
        this.sheet = this.workbook.createSheet();
        this.row = this.sheet.createRow(0);
        this.style = this.workbook.createCellStyle();
        this.style.setAlignment(CellStyle.ALIGN_CENTER);
        for(int i = 0 ; i < titles.length ; i ++) {
            cell = row.createCell(i);    //创建列
            cell.setCellValue(titles[i]);    //赋值
            cell.setCellStyle(style);    //样式
        }
    }
    
    public void genertedExportUsersFile(List<Users> data) throws IOException {    
        //遍历每一行数据
        for(int i = 0; i < data.size() ; i ++) {
            int j = 0;
            Users user = data.get(i);
            row = sheet.createRow(i+1);
            row.setRowStyle(style);
            row.createCell(j++).setCellValue(Optional.of(user.getId()).orElse(null));
            row.createCell(j++).setCellValue(Optional.of(user.getUsername()).orElse(null));
            row.createCell(j++).setCellValue(Optional.of(user.getPassword()).orElse(null));
        }
    }
    
    public void genertedExportStudentFile(List<Student> data) {
        //遍历每一行数据 
        for(int i = 0,j = 0 ; i < data.size() ; i ++) {
            Student student = data.get(i);
            row = sheet.createRow(i+1);
            row.setRowStyle(style);
            row.createCell(j++).setCellValue(Optional.of(student.getId()).orElse(null));
            row.createCell(j++).setCellValue(Optional.of(student.getUsername()).orElse(null));
            row.createCell(j++).setCellValue(Optional.of(student.getPassword()).orElse(null));
            row.createCell(j++).setCellValue(Optional.of(student.getStuname()).orElse(null));
            row.createCell(j++).setCellValue(Optional.of(student.getStusex()).orElse(null));
}
}
    
public void write() throws IOException {
    //把数据写入表格文件
    OutputStream out = new FileOutputStream(this.file);
    this.workbook.write(out);
    out.close();
}
private Workbook getWorkbook(String fileName) {
        //根据文件后缀名来获取Excel文件是2003版的还是2007版的
        String type = checkFileType(fileName);
        //根据版本的不同实例不同的对象
        if(type.equals(this.excel2003))
            return new HSSFWorkbook();
        else if(type.equals(this.excel2007))
            return new XSSFWorkbook();
        return null;
    }

private String checkFileType(String fileName) {
    return fileName.substring(fileName.lastIndexOf(".")+1);
}
}

Now I will talk about the functions of several methods:
Constructor method: initialize the table structure object and produce the table header;
genertedExportUsersFile method: generate the table data, the data should not be actually written at this time;
write method: write the generated table in In the File class, it is the created file;
getWorkbook method: Get the Excel object of the 2003 version or the 2007 version;
checkFileType method: Get the suffix name of the file.

The above is the detailed content of How to generate an Excel file using SpringMVC?. For more information, please follow other related articles on the PHP Chinese website!