In the recent project, I met a demand to import database information into an Excel file, and also to provide an interface to download the Excel file. After that, I made a summary, hoping to bring help to everyone

Source: github.com/HowieYuan/E…

Rely on

<! -- https://mvnrepository.com/artifact/net.sourceforge.jexcelapi/jxl --> <dependency> < the groupId >.net. Sourceforge. Jexcelapi < / groupId > < artifactId > JXL < / artifactId > < version > 2.6.12 < / version > < / dependency >Copy the code

We need to use the JXL package class, and jxl.jar is the utility class library for manipulating Excel tables, in addition to JXL, poI package is also a class library for manipulating Excel. Compared with the two packages, JXL is more suitable for a large amount of data, while POI is more efficient when the amount of data is not high (within 5000 or so), but occupies a large amount of memory and is more prone to memory overflow.

The test data

private int id;
private String name;
private int age;
private String gender;
Copy the code

public List<Person> getPersonList() {
    List<Person> list = new ArrayList<>();
    list.add(new Person(1, "Howie", 20, "female"));
    list.add(new Person(2, "Wade", 25, "male"));
    list.add(new Person(3, "Duncan", 30, "male"));
    list.add(new Person(4, "Kobe", 35, "male"));
    list.add(new Person(5, "James", 40, "male"));
    return list;
}
Copy the code

1. Import all the data into a table

// Create files local files // Create files directly in the project directory String filePath ="Personnel data.xlsx"; File dbfFile = new File(filePath); WritableWorkbook WWB = workbook.createWorkbook (dbfFile); // Create a WritableWorkbook object with the Workbook factory method. // If the file does not exist, create a new fileif(! dbfFile.exists() || dbfFile.isDirectory()) { dbfFile.createNewFile(); } / / personnel information list (PersonFactroy class has been the dependency injection) list < Person > list = PersonFactroy. GetPersonList (); // create a WritableSheet WritableSheet ws = wwb.createsheet (List of "1", 0); Ws. addCell(new Label(0, 0,"Serial number"));
ws.addCell(new Label(1, 0, "Name"));
ws.addCell(new Label(2, 0, "Age"));
ws.addCell(new Label(3, 0, "Gender"));
int index = 0;
for(Person person : List) {// Add the generated cells to the worksheet // (Note here that in Excel, the first argument represents the column, Ws.addcell (new Label(0, index + 1, string.valueof (person.getid ()))); ws.addCell(new Label(1, index + 1, person.getName())); ws.addCell(new Label(2, index + 1, String.valueOf(person.getAge()))); ws.addCell(new Label(3, index + 1, person.getGender())); index++; }Copy the code

2. Import data into multiple tables

Int mus = 2; int mus = 2; Int totle = list.size(); Int avg = totle/mus + 1;for(int i = 0; i < avg; I++) {// create a WritableSheet WritableSheet ws = wwb.createsheet ("A list"+ (i + 1), i); Ws. addCell(new Label(0, 0,"Serial number"));
    ws.addCell(new Label(1, 0, "Name"));
    ws.addCell(new Label(2, 0, "Age"));
    ws.addCell(new Label(3, 0, "Gender"));
    int num = i * mus;
    int index = 0;
    for(int m = num; m < list.size(); M++) {// index == musforcycleif (index == mus) {
            break; } Person person = list.get(m); // Add the generated cells to the worksheet // (Note that in Excel, the first argument represents the column, Ws.addcell (new Label(0, index + 1, string.valueof (person.getid ()))); ws.addCell(new Label(1, index + 1, person.getName())); ws.addCell(new Label(2, index + 1, String.valueOf(person.getAge()))); ws.addCell(new Label(3, index + 1, person.getGender())); index++; }}Copy the code

You can divide each table by the amount of data it has, or you can divide each table by one of the attributes and so on

Provides a file download interface

This method uses a file stream to write to a file. The method type is void and no return is required. In addition, HttpServletResponse must be added to the interface parameters

    @RequestMapping(value = "/getExcel". method = RequestMethod.GET) public void createBoxListExcel(HttpServletResponse response) throws Exception { String filePath ="Personnel data.xlsx"; */ String fileName = new String()"Personnel data.xlsx".getBytes(), "ISO-8859-1"); // Set the file name response.addheader ("Content-Disposition"."filename=" + fileName);
        OutputStream outputStream = response.getOutputStream();
        FileInputStream fileInputStream = new FileInputStream(filePath);
        byte[] b = new byte[1024];
        int j;
        while ((j = fileInputStream.read(b)) > 0) {
            outputStream.write(b, 0, j);
        }
        fileInputStream.close();
        outputStream.flush();
        outputStream.close();
    }
Copy the code

Then, we can download your file immediately by typing localhost:8080/getExcel directly into the address bar

Note: String fileName = new String(” staff information.xlsx “.getBytes(), “ISO-8859-1”); I used the ISO-8859-1 encoding because the ISO-8859-1 encoding is single-byte encoding and backwards compatible with ASCII, whereas only ASCII is supported in the Header, and the file name for transmission must be ASCII