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

String filePath = "staff data.xls "; String filePath =" staff data.xls "; 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 file if (! 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 (" listing 1", 0); Ws. addCell(new Label(0, 0, "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 (" list "+ (I + 1), I); Ws. addCell(new Label(0, 0, "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++) {// if (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 = "staff data.xls "; */ String fileName = new String(" people data.xls ".getbytes (), "ISo-8859-1 "); 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