In the past two days, when I was working on the SSM project, I encountered the issue of exporting the query results to the table. The other processes are relatively simple, but it is too troublesome to use the queried data to generate files using POI.
We use POI to generate a HSSFWorkbook file object, and then use the HSSFWorkbook object to generate the HSSFSheet table object
Then use the table object to generate the HSSFRow object, and then the cell object.
We need to set the field value of the database in each cell of the first row, and then loop through our data collection to generate one by one. At this time, we can obtain the class object of this data through the reflection mechanism, and then go to Generate this table object.
Steps for exporting data by reflection mechanism:
First, we first create an HSSFWorkbook object through POI, simulate the creation of a collection of data class objects, and then create our table header
The creation of the table header needs to use the reflection mechanism to traverse the properties of our javabean class. The property name corresponds to the field name in the database table, and the data class is directly received with parameters.
public class HSSFUtil {
public static void createExcelByObjectListToPath(List list){
//Create HSSFWorkbook class object
HSSFWorkbook workbook = new HSSFWorkbook();
//Create a table object through the workbook object, the parameter is the table name
HSSFSheet sheet = workbook.createSheet("The name of the table");
//Start to create the table header, create the first row through the table
HSSFRow row = sheet. createRow(0);
//Create an object of the Class to operate the class
Class<activity> clazz = Data. class;
/ / Get the array collection of attribute classes in the class object
Field[] fields = clazz. getDeclaredFields();
//Loop through the array collection of attribute classes, and generate corresponding cells, and assign the traversed attribute names to the cells
for (int i = 0; i < fields. length; i ++ ) {
row.createCell(i).setCellValue(fields[i].getName());
}
}
Two: traverse our collection of data objects, and pass their attribute values into the created cells
public class HSSFUtil {
public static void createExcelByObjectListToPath(List list){
//Create HSSFWorkbook class object
HSSFWorkbook workbook = new HSSFWorkbook();
//Create a table object through the workbook object, the parameter is the table name
HSSFSheet sheet = workbook.createSheet("The name of the table");
//Start to create the table header, create the first row through the table
HSSFRow row = sheet. createRow(0);
//Create an object of the Class to operate the class
Class<activity> clazz = Data. class;
/ / Get the array collection of attribute classes in the class object
Field[] fields = clazz. getDeclaredFields();
//Loop through the array collection of attribute classes, and generate corresponding cells, and assign the traversed attribute names to the cells
for (int i = 0; i < fields. length; i ++ ) {
row.createCell(i).setCellValue(fields[i].getName());
}
//Start assigning data
for (int i = 0; i < list. size(); i ++ ) {
//Traverse the collection of data objects, creating a row object for each traversal
row = sheet. createRow(i + 1);
//Start traversing the attribute class object after creating the row
for (int j = 0; j < fields. length; j ++ ) {
//Because the attribute is private, you need to use the reflection mechanism to set the access permission and set it to allow access
fields[j].setAccessible(true);
//Create a cell object with a row object, and the corresponding cell is the subscript of the traversal property.
//Then set the value of the cell, the acquisition of the data class object attribute is through the field.get(Object) method,
//This method can get the value of this property of this object of the passed in obj
row.createCell(j).setCellValue((String)fields[j].get(list.get(i)));
}
}
//Two-level traversal will write all the attributes of all data objects in the collection into the file object, and finally only need to generate
workbook.write(new FileOutputStream("The absolute path of the file"));
//The path in the absolute path of the file must exist, the specified file may not exist, and the suffix of the EXCEL file is .xls
}
3. Encapsulate these steps into a tool class
To really encapsulate it into the tool class, in addition to the normal operation of the code, we also need to consider adding more functions to make the code more applicable
Take Tomcat as an example, if you want to send the file to the browser, then you need to create a stream to read it if you write the file locally, and then send the stream obtained by Response.getOutPutStream() to the browser. Then we can directly pass the output stream of the server into workbook.write(), and directly output the file to the browser, so the final package class should be
public class HSSFUtil {
private HSSFUtil(){}
public static void createExcelByObjectListToLocal(List list,String path,String sheetname) throws IOException, IllegalAccessException {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook. createSheet(sheetname);
HSSFRow row = sheet. createRow(0);
Class<activity> clazz = activity. class;
Field[] fields = clazz. getDeclaredFields();
for (int i = 0; i < fields. length; i ++ ) {
row.createCell(i).setCellValue(fields[i].getName());
}
for (int i = 0; i < list. size(); i ++ ) {
row = sheet. createRow(i + 1);
for (int j = 0; j < fields. length; j ++ ) {
fields[j].setAccessible(true);
row.createCell(j).setCellValue((String)fields[j].get(list.get(i)));
}
}
workbook.write(new FileOutputStream(path));
}
public static void createExcelByObjectListToBrowser(List list, OutputStream out,String sheetname) throws IOException, IllegalAccessException{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook. createSheet(sheetname);
HSSFRow row = sheet. createRow(0);
Class<activity> clazz = activity. class;
Field[] fields = clazz. getDeclaredFields();
for (int i = 0; i < fields. length; i ++ ) {
row.createCell(i).setCellValue(fields[i].getName());
}
for (int i = 0; i < list. size(); i ++ ) {
row = sheet. createRow(i + 1);
for (int j = 0; j < fields. length; j ++ ) {
fields[j].setAccessible(true);
row.createCell(j).setCellValue((String)fields[j].get(list.get(i)));
}
}
workbook. write(out);
}
}
We directly provide two different methods for local output and browser output
Eventually the code in our project becomes
public void download(HttpServletResponse resp) throws IOException {
List<activity> acts = activityService. queryAllActivity();
resp.setContentType("application/octet-stream;charset=UTF-8");
resp.setHeader("Content-Disposition","attachment;filename=activities.xls");
try {
HSSFUtil.createExcelByObjectListToBrowser(acts,resp.getOutputStream(),"market activity information table");
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
Just one call to the tool class executes all the codes for data transfer to the file