Spring boot realizes Excel function of data export in database

Function introduction

A lot of data export codes have been found on the Internet, which may be due to the weakness of their own foundation or other reasons. As a result, they have not run successfully. Even if they run successfully, the results are unsatisfactory. In this summary, I will learn from a demo that has been integrated by others.

Step analysis

       
1. Add data export button on the foreground page.
       
2. Data export function in the background.
2.1 query of database information.
The data in a table in the database is queried, and the queried data is written into the excel file.

2.2 establish an excel table to store and query the data.
To create an excel table, first create a workbook, then create a sheet, and then create a row in the sheet as the header, and put the data queried by the database under the corresponding header.

Code

The front html page code is as follows:

<table border="0" style="margin-top:4px; margin-left: 18px">
            <tr>
                <td><a href="#" class="easyui-linkbutton" onclick="downloadfile();">Data export</a></td>

            </tr>

        </table>

The js code of the foreground page is as follows:

function downloadfile(){
        window.location.href="/UserExcelDownloads";
    }

The service and mapper layer codes are as follows:

//mapper interface code
@Select("select * from teacher")
    public List<Teacher> teacherinfor();

//service layer code
public List<Teacher> teacherinfor(){
        return teachermapper.teacherinfor();

//At the same time, create a Teacher entity class. The attribute name stored in the class should be the same as the attribute name of the table in the exported database

The background Controller layer code is as follows:

@RequestMapping(value = "UserExcelDownloads", method = RequestMethod.GET)
    public void downloadAllClassmate(HttpServletResponse response) throws IOException {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("Information sheet");

        List<Teacher> classmateList = teacherservice.teacherinfor();

        String fileName = "userinf"  + ".xls";//Set the name of the file to export
        //Add data row and set cell data

        int rowNum = 1;

        String[] headers = { "Student ID", "Full name", "Identity type", "Login password"};
        //headers represent the header of the first row in the excel table

        HSSFRow row = sheet.createRow(0);
        //Add header to excel

        for(int i=0;i<headers.length;i++){
            HSSFCell cell = row.createCell(i);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }

        //Put the queried data in the table into the corresponding column
        for (Teacher teacher : classmateList) {
            HSSFRow row1 = sheet.createRow(rowNum);
            row1.createCell(0).setCellValue(teacher.getTno());
            row1.createCell(1).setCellValue(teacher.getTname());
            row1.createCell(2).setCellValue(teacher.getType());
            row1.createCell(3).setCellValue(teacher.getTpassword());
            rowNum++;
        }

        response.setContentType("application/octet-stream");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);
        response.flushBuffer();
        workbook.write(response.getOutputStream());
    }

Demo screenshot


Figure 1 data information in data


Figure 1 data in userinf.xls table

Tags: Excel Database Attribute

Posted on Tue, 05 May 2020 09:17:42 -0400 by climbjm