POI is often used to export excel, but if you have to write your own every time you export, it will be more troublesome, and the code is basically repeated. So recently, I spent a little time and studied. According to the annotation and reflection technology, I wrote a tool class for POI to export excel. It can basically realize the export of the first level and the second level headers.
The example I wrote this time is a simple spring boot project. If you don't know about spring boot, learn it by yourself.
1. Create a SpringBoot project
1.1 import dependency
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>org.example</groupId>
<artifactId>POI_SpringBoot</artifactId>
<version>1.0-SNAPSHOT</version>
<!-- Define public resource version -->
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.0.1.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<dependencies>
<!-- Top introduction parent，So there is no need to specify the version below -->
<!-- Contain mvc,aop etc. jar Resources -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!--spring Operation database jpa A set of classes and methods used to store data into a database-->
<!--<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>-->
<!-- Thermal deployment -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<optional>true</optional>
<scope>true</scope>
</dependency>
<!--spring template engine-->
<!--<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>-->
<!--Database related-->
<!--<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.3.2</version>
</dependency>
<dependency>
<groupId>com.google.collections</groupId>
<artifactId>google-collections</artifactId>
<version>1.0</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<!-- There is no such configuration, devtools Not effective -->
<fork>true</fork>
</configuration>
</plugin>
</plugins>
</build>
</project>1.2 startup
package com.poi;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class PoiApplication {
public static void main(String[] args) {
SpringApplication.run(PoiApplication.class,args);
}
}1.3 configuration file application.yml
My configuration here only configures the port number, and other settings are not made. You can make your own settings if necessary.
server: port: 9000
2. Custom annotation
When exporting excel, we need to set some excel cell styles, such as row height, column width, font, font color and so on. In order to set flexibly, you need to define an annotation first, and set the properties of each column through the annotation.
package com.poi.annotation;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
@Target(ElementType.FIELD)//Indicates that this annotation can only use attributes
@Retention(RetentionPolicy.RUNTIME)//Indicates that this annotation will exist at run time. Custom annotation is generally set to this value
public @interface ExcelColumn {
/**
* @Title: title
* @Description: Header column name is required
*/
public String title();
/**
* The serial number of the column, which means to put the attribute of this annotation into the first column of excel, starting from 1
* It should be noted that this value must be continuous. For example, the sort of each attribute must be: 1, 3, 2, 4, 6, 5 (can be disordered)
But not: 1, 3, 4, 6, 5 (2 less)
*/
public int sort();
/**
* @Title: width
* @Description: Column width default 15
*/
public short width() default 15;
public enum Alignment {
LEFT(0x1), CENTER(0x2), RIGHT(0x3);
private int value;
private Alignment(int value) {
this.value = value;
}
public int getValue() {
return value;
}
};
/**
* @Title: alignment
* @Description: Text style center by default (Alignment.CENTER)
*/
public Alignment alignment() default Alignment.CENTER;
/**
* @Title: boder
* @Description: Whether cells need to be surrounded by borders is true by default
*/
public boolean boder() default true;
public enum StyleColor {
WHITE(0x9), BLACK(0x8), BLUE(0xc), RED(0xa), YELLOW(0xd);
private int value;
private StyleColor(int value) {
this.value = value;
}
public int getValue() {
return value;
}
};
/**
* @Title: styleColor
* @Description: Cell background color default white
*/
public StyleColor styleColor() default StyleColor.WHITE;
public enum FontColor {
BLACK(0x8), BLUE(0xc), RED(0xa), YELLOW(0xd);
private int value;
private FontColor(int value) {
this.value = value;
}
public int getValue() {
return value;
}
};
/**
* @Title: fontColor
* @Description: Text color default black (FontColor.BLACK) temporarily supports black blue red yellow
*/
public FontColor fontColor() default FontColor.BLACK;
/**
* @Title: fontSize
* @Description: Font size default 12
*/
public short fontSize() default 12;
/**
* @Title: fontName
* @Description: Font default Microsoft YaHei
*/
public String fontName() default "Microsoft YaHei";
}3. Tools for exporting excel
In the tool class, reflection technology is used to obtain the attributes and annotations of the class, so we need to have a certain understanding of reflection.
The tool class directly downloads the excel file, and there will be no Chinese code problems in the download, because the corresponding processing is also done in it.
package com.poi.util;
import com.poi.annotation.ExcelColumn;
import org.apache.poi.hssf.usermodel.HSSFBorderFormatting;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Base64;
import java.util.Iterator;
import java.util.List;
/**
* excel Export tool class
*/
@SuppressWarnings("all")
public class ExcelExpUtil<T> {
private Logger log = LoggerFactory.getLogger(ExcelExpUtil.class);//Log record
private Class clazz;//Export the Class object of the target Class
private int colNum;//Total column number
private int startRowIndex;//Line number at the beginning of the export, excluding the header
private Field[] fields;//All are property arrays
private String titleName;//Title Content
private String[] headerArr;//Header array to merge
private String[] headers;// Column name collection
private Short[] columnWidth ;// Column width set
private Integer[] alignList ;// Text style position set, left, center, or right
private Boolean[] boderList ;// Whether a border collection is required
private Integer[] fontColorList;// Font color set
private String[] fontNameList ;// Font collection
private Short[] fontSizeList;// Font size set
private Integer[] styleColorList ;// Cell background color set
private Field[] excelFileds ;//Fields with annotations are excluded, i.e. fields to be exported without annotations
/**
* There is no construction method for merging headers
* @param clazz Exports the CLass's CLass object, through which the corresponding attributes and annotations are obtained by reflection
* @param titleName Title Title
* @param colNum Total number of columns. We should know the total number of columns before exporting
* @param startRowIndex Line number at the beginning of the export, excluding the header
*/
public ExcelExpUtil(Class clazz,String titleName, int colNum, int startRowIndex){
this.clazz=clazz;
this.titleName = titleName;
this.colNum = colNum;
this.startRowIndex = startRowIndex-1;//From 0, so subtract 1
this.fields = clazz.getDeclaredFields();//Get all the properties of the class, including private properties, by emission
this.headers = new String[colNum];
this.columnWidth = new Short[colNum];
this.alignList = new Integer[colNum];
this.boderList = new Boolean[colNum];
this.fontColorList = new Integer[colNum];
this.fontNameList = new String[colNum];
this.fontSizeList = new Short[colNum];
this.styleColorList = new Integer[colNum];
this.excelFileds = new Field[colNum];
}
/**
* A construction method of merging header with one row
* @param clazz CLass object of exported CLass
* @param titleName Title Title
* @param colNum Total column number
* @param startRowIndex Line number at the beginning of the export, excluding the header
* @param headerArr Header array to merge
*/
public ExcelExpUtil(Class clazz,String titleName, int colNum, int startRowIndex,String[] headerArr){
this(clazz,titleName,colNum,startRowIndex);//Call another constructor
this.headerArr = headerArr;
}
/**
* How to export excel
* @param dataList Exported business data
* @param response Browser download stream
* @param fileName Exported file name
* @param ext The extension of the exported file,. XLS. Xlsx
*/
public void excelExp(List<T> dataList,HttpServletRequest request, HttpServletResponse response, String fileName, String ext){
//Get workbook object by suffix
Workbook workbook = getWorkBook(ext);
if(workbook != null){
creatSheet(workbook, dataList);//Create sheet page
//Download File
this.downloadFile(workbook,request,response,fileName,ext);
}else{
log.error("Failed to create Workbook");
}
}
//Create sheet
private void creatSheet(Workbook workbook, List<T> dataList) {
if(dataList==null || dataList.size()<=0){
return;
}
Sheet sheet = workbook.createSheet();//sheet object
Row row =null;//Row object
Cell cell = null;//Column objects
ExcelColumn excelColumn = null;
int j=0;//Equivalent to column number, starting from 0
for(short i = 0; i < fields.length; i++){//Traverse the set of attributes to get the set of annotated attributes
Field field = fields[i];//Get attribute
//Judge whether there is annotation on the attribute: ExcelColumn
if(field.isAnnotationPresent(ExcelColumn.class)){
excelColumn = (ExcelColumn)field.getAnnotation(ExcelColumn.class);//Get the specified annotation information through the attribute
j = excelColumn.sort()-1;//The column number of the annotation on the attribute starts from 1. In fact, the column number in the poi starts from 0, so subtract 1 here
excelFileds[j] = field;//Put into the collection of fields to export
headers[j] = excelColumn.title();//Put the column name into the column name collection
columnWidth[j]=excelColumn.width();//Put width into the width set
alignList[j]=excelColumn.alignment().getValue();
boderList[j]=excelColumn.boder();
fontColorList[j]=excelColumn.fontColor().getValue();
fontNameList[j]=excelColumn.fontName();
fontSizeList[j]=excelColumn.fontSize();
styleColorList[j]=excelColumn.styleColor().getValue();
}
}
// Loop set column width
int length = columnWidth.length;
for (int i = 0; i < length; i++) {
sheet.setColumnWidth(i, columnWidth[i] * 256);
}
// Generate table header row, the first row
this.createTitle(workbook,sheet,row);
// Merge header row settings
if(this.headerArr!=null){
this.createHeBinHeader(workbook,sheet,row,cell);
}
//Generate header row, using data in header
this.createHeader(workbook,sheet,row,cell);
// Traversing set data to generate data rows
this.setDataToSheet(workbook,sheet,row,cell,dataList);
}
//Merge rows based on header array information
private void createHeBinHeader(Workbook workbook, Sheet sheet, Row row, Cell cell) {
row = sheet.createRow(1);//The second row is the row of the consolidated header, with the row number of 1
row.setHeightInPoints(25);//Set row height
CellStyle boderStyle = workbook.createCellStyle();//Create a style
Font font = workbook.createFont();// Generate a font default font Microsoft YaHei
font.setFontHeightInPoints((short)20);// Set font size
font.setBoldweight(Font.BOLDWEIGHT_BOLD);//Bold font
boderStyle.setFont(font);// Apply font to current style
//Set borders
//boderStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
/*boderStyle.setBorderBottom(CellStyle.BORDER_THIN);
boderStyle.setBorderLeft(CellStyle.BORDER_THIN);
boderStyle.setBorderRight(CellStyle.BORDER_THIN);
boderStyle.setBorderTop(CellStyle.BORDER_THIN);*/
//Vertical centering
boderStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
boderStyle.setAlignment(CellStyle.ALIGN_CENTER); // Create a centered format
String[] zuoBiaoArr = null;
int colIndex = 0;//Column start row number
for(int i = 0; i<headerArr.length;i++){
String[] valueArr = headerArr[i].split("#");
String cellValue = valueArr[0];//Cell content
String zuoBiaoStr = valueArr[1];
zuoBiaoArr = zuoBiaoStr.split(",");
colIndex=this.colNum-(this.colNum-Integer.parseInt(zuoBiaoArr[3]))-1;
cell = row.createCell(colIndex);//Get column
cell.setCellStyle(boderStyle);//Set style
cell.setCellValue(cellValue);//Set contents
sheet.addMergedRegion(new CellRangeAddress(Integer.parseInt(zuoBiaoArr[0]),
Integer.parseInt(zuoBiaoArr[1]), Integer.parseInt(zuoBiaoArr[2]), Integer.parseInt(zuoBiaoArr[3])));//Set merge
//colIndex += Integer.parseInt(zuoBiaoArr[3])+1; / / start column number of the second column
}
}
/**
* Loop traversal, the method of setting data into a table
* @param workbook workbook
* @param row Row object
* @param cell Cell object
* @param sheet sheet object
* @param dataList Data set
* @param alignList Alignment set
* @param boderList Is the collection of borders
* @param fontColorList Collection of font colors
* @param fontNameList Collection of font names
* @param fontSizeList Collection of font sizes
* @param styleColorList A collection of background colors
* @param excelFileds Column set to export
*/
private void setDataToSheet(Workbook workbook, Sheet sheet, Row row,Cell cell, List<T> dataList) {
Iterator<T> it = dataList.iterator();
while (it.hasNext()) {
row = sheet.createRow(startRowIndex++);//From what line
T t = (T) it.next();
// Using reflection, according to the order of javabean properties, dynamically call getXxx() method to get the property value
for (int i = 0; i < excelFileds.length; i++) {
cell = row.createCell(i);
Field field = excelFileds[i];//get attribute
field.setAccessible(true);//Ignore the check and write data without this
//Call method to style
this.setCellStyle(workbook, cell, alignList[i], boderList[i],styleColorList[i],
fontColorList[i], fontNameList[i],fontSizeList[i],false);
String fieldName = field.getName();//Get attribute name
//get method name of the property
String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
try {
cell.setCellValue(field.get(t).toString());
//Another way to get value by manual splicing
//Get the corresponding method
/*Method getMethod = this.clazz.getMethod(getMethodName, new Class[] {});
//Execute method to get return value
Object value = getMethod.invoke(t, new Object[] {});
String textValue = value.toString();
if (textValue != null) {
RichTextString richString = new HSSFRichTextString(textValue);
cell.setCellValue(richString);
}*/
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
//Generate header row
private void createHeader(Workbook workbook, Sheet sheet,Row row,Cell cell) {
row = sheet.createRow(this.startRowIndex-1);//The previous row of the content start row is the header row
for (int i = 0; i < headers.length; i++) {
cell = row.createCell(i);
//Call method to style
this.setCellStyle(workbook, cell, alignList[i], boderList[i],styleColorList[i], fontColorList[i], fontNameList[i],fontSizeList[i],true);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);//Get the value of the column
cell.setCellValue(text);//Set values to cells
}
}
/**
* How to create a title row
* @param workbook
* @param sheet
* @param row
*/
private void createTitle(Workbook workbook,Sheet sheet,Row row){
row = sheet.createRow(0);//Heading line
row.setHeightInPoints(50);//Set row height
Cell titleCell = row.createCell(0);//Get title column
CellStyle cellStyle = workbook.createCellStyle();
// Generate a font default font Microsoft YaHei
Font font = workbook.createFont();
// Set font size
font.setFontHeightInPoints((short)30);
// Bold font
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
// Apply font to current style
cellStyle.setFont(font);
//Vertical centering
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
cellStyle.setAlignment(CellStyle.ALIGN_CENTER); // Create a centered format
titleCell.setCellStyle(cellStyle);//Set style
titleCell.setCellValue(this.titleName);//Set contents
sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(),
row.getRowNum(), row.getRowNum(), this.colNum-1));//Set merge
}
/**
* @Title: How to style cells
* @param workbook workbook
* @param cell Cells to configure
* @param alignment Cell text style
* @param needBoder Need surround
* @param sytleColor Cell background color
* @param fontColor Text color
* @param fontName Typeface
* @param fontSize Font size
* @param isBold Bold header
* @Description: Set the overall style of cells and fonts
*/
private void setCellStyle(Workbook workbook, Cell cell, int alignment, Boolean needBoder, int sytleColor,int fontColor,
String fontName,short fontSize,boolean isBold) {
// Generate a generic style with a default background of white
CellStyle style = workbook.createCellStyle();
style.setFillForegroundColor((short)sytleColor);
// Cell content style
style.setAlignment((short) alignment);
// Do cells need borders
if (needBoder) {
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setBorderTop(CellStyle.BORDER_THIN);
}
// Generate a font default font Microsoft YaHei
Font font = workbook.createFont();
font.setFontName(fontName);
font.setColor((short) fontColor);
// Set font size
font.setFontHeightInPoints(fontSize);
// Whether the font is bold
if (isBold) {
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
}
// Apply font to current style
style.setFont(font);
cell.setCellStyle(style);
}
//Get workbook object by suffix
private Workbook getWorkBook(String ext){
if(".xls".equals(ext)){
return new HSSFWorkbook();
}
if(".xlsx".equals(ext)){
return new SXSSFWorkbook();
}
log.error("Extension can only be[.xls]Or [.xlsx]");
return null;
}
/**
* How to download files
* @param workbook workbook
* @param request request
* @param response response
* @param filename File name
* @param ext File extension
*/
private void downloadFile(Workbook workbook,HttpServletRequest request, HttpServletResponse response,String filename,String ext){
filename = filename+ext;//Filename + extension
//Call other download methods
downloadFile(workbook,request,response,filename);
}
private void downloadFile(Workbook workbook,HttpServletRequest request, HttpServletResponse response,String filename){
try {
//Get user agent from request header to determine whether Firefox is currently in use
String agent = request.getHeader("User-Agent");
//Different coding according to different browsers
String realFilename = "";
if (agent.contains("MSIE")) {
// IE browser
realFilename = URLEncoder.encode(filename, "utf-8");
realFilename = realFilename.replace("+", " ");
} else if (agent.contains("Firefox")) {
// Firefox, use java8 here
realFilename = "=?utf-8?B?" + Base64.getEncoder().encodeToString(filename.getBytes("utf-8")) + "?=";
} else {
// Other browsers
realFilename = URLEncoder.encode(filename, "utf-8");
}
//Set the file name to be downloaded
response.setHeader("Content-Disposition","attachment;filename="+realFilename);
response.setContentType("application/octet-stream");
response.setHeader("filename", filename);
workbook.write(response.getOutputStream());
} catch (Exception e) {
e.printStackTrace();
}
}
}4. Test export
4.1 adding annotations to Java classes
package com.poi.pojo;
import com.poi.annotation.ExcelColumn;
import java.util.Date;
public class User {
//If this attribute needs to be exported, add annotation information to it
@ExcelColumn(title = "Full name",width = 20,alignment = ExcelColumn.Alignment.CENTER,sort=2)
private String name;
@ExcelColumn(title = "user ID",width = 10,alignment = ExcelColumn.Alignment.LEFT,styleColor = ExcelColumn.StyleColor.RED,sort=1)
private String id;
@ExcelColumn(title = "Age",width = 10,alignment = ExcelColumn.Alignment.CENTER,sort=4)
private int age;
//@ExcelColumn(title = date of birth, width = 20,alignment = ExcelColumn.Alignment.LEFT,sort=4)
private Date birthday;
@ExcelColumn(title = "Home address",width = 50,alignment = ExcelColumn.Alignment.LEFT,fontColor = ExcelColumn.FontColor.BLUE,sort=5)
private String address;
@ExcelColumn(title = "height",width = 10,alignment = ExcelColumn.Alignment.CENTER,sort=3)
private double high;
public User() {
}
public User(String id, String name, int age, Date birthday, String address, double high) {
this.id = id;
this.name = name;
this.age = age;
this.birthday = birthday;
this.address = address;
this.high = high;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public double getHigh() {
return high;
}
public void setHigh(double high) {
this.high = high;
}
}4.2 create controller
package com.poi.controller;
import com.poi.pojo.User;
import com.poi.util.ExcelExpUtil;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
@Controller
@RequestMapping("/excel")
public class UserExcelController {
@RequestMapping("/userList")
public void expUserList(HttpServletRequest request,HttpServletResponse response){
//Query business data, which should be queried from the database in the actual project
List<User> userList = this.getDataList();
//Call tool class method for export
new ExcelExpUtil(User.class,"User list",5,3)
.excelExp(userList,request,response,"User list",".xlsx");
}
private List<User> getDataList() {
List<User> userList = new ArrayList<>();
User user1 = new User("1001","Lao Pang",38,new Date(System.currentTimeMillis()-1000*60*60*24*365),"Yuelu Academy 1, Yuelu District, Changsha City, Hunan Province",180.5);
User user2 = new User("1002","Lao Wang",38,new Date(System.currentTimeMillis()-1000*60*60*24),"Yuelu Academy 2, Yuelu District, Changsha City, Hunan Province",170.5);
User user3 = new User("1003","Lao Li",38,new Date(System.currentTimeMillis()-1000*60*60*24*365*2),"Yuelu Academy 3, Yuelu District, Changsha City, Hunan Province",160.5);
User user4 = new User("1004","Old week",38,new Date(System.currentTimeMillis()-1000*60*60*24*365*10),"Yuelu Academy 4, Yuelu District, Changsha City, Hunan Province",190.5);
User user5 = new User("1005","Lao Zhao",38,new Date(System.currentTimeMillis()-1000*60*60*24*365*20),"Yuelu Academy 5, Yuelu District, Changsha City, Hunan Province",150.5);
userList.add(user1);
userList.add(user2);
userList.add(user3);
userList.add(user4);
userList.add(user5);
return userList;
}
}4.3 effect
Start the project, visit: http://127.0.0.1:9000/excel/userList You can download the excel file. The effect after downloading and opening is as follows:
5. Test merge level 1 header
5.1. Create a new test java object
package com.poi.pojo;
import com.poi.annotation.ExcelColumn;
public class Dog {
@ExcelColumn(title = "Pets ID",sort = 2,width = 10)
private int id;
@ExcelColumn(title = "Pet name",sort = 1,width = 20)
private String name;
@ExcelColumn(title = "Pet color",sort = 3,width = 20)
private String color;
@ExcelColumn(title = "Pet owners ID",sort = 5,width = 10)
private String userId;
@ExcelColumn(title = "Pet favorite food",sort = 4,width = 30)
private String eat;
@ExcelColumn(title = "Pet owner name",sort = 6,width = 20)
private String userName;
public Dog(int id, String name, String color,String eat, String userId, String userName) {
this.id = id;
this.name = name;
this.color = color;
this.eat = eat;
this.userId = userId;
this.userName = userName;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getColor() {
return color;
}
public void setColor(String color) {
this.color = color;
}
public String getEat() {
return eat;
}
public void setEat(String eat) {
this.eat = eat;
}
public String getUserId() {
return userId;
}
public void setUserId(String userId) {
this.userId = userId;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
}5.2 create controller
package com.poi.controller;
import com.poi.pojo.Dog;
import com.poi.util.ExcelExpUtil;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;
@Controller
@RequestMapping("/excel")
public class DogController {
@RequestMapping("/dogList")
public void excelExport(HttpServletRequest request, HttpServletResponse response){
//Construct merged header row information
String[] headerArr = new String[3];
headerArr[0] = "Pet information 1#1,1,0,1";
headerArr[1] = "Pet information 2#1,1,2,3";
headerArr[2] = "Master information#1,1,4,5";
List<Dog> dogList = new ArrayList<>();
Dog dog1 = new Dog(1,"Little bad cat","Take a good look at the colorful","Just like eating dead mice","1001","Lao Pang");
Dog dog2 = new Dog(1,"Little rabbit","It's pure white. It's beautiful","Just like eating carrots","1001","Lao Pang");
dogList.add(dog1);
dogList.add(dog2);
//The construction method that can merge headers is called here
new ExcelExpUtil(Dog.class,"Pets list",6,4,headerArr)
.excelExp(dogList,request,response,"Pets list",".xlsx");
}
}5.3 view export effect
Run project, request: http://127.0.0.1:9000/excel/dogList , the effect is as follows:
