Implementation of Java Fast Import and Export Using easypoi
- 2021-07-09 08:19:03
- OfStack
Brief introduction
easypoi function is like the name easy, and its main function is easy, so that a person who has never seen poi can easily write Excel import and export, and complete the complicated writing before through simple annotations and template language (familiar expression syntax).
Integration
Dependency can be introduced into pom
<!--easypoi-->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>3.0.3</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>3.0.3</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>3.0.3</version>
</dependency>
Integration tool class EasyPoiUtil
package cn.common.util;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.common.exception.ZXException;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;
/**
* @author huangy
* @date 2019/6/28 14:57
*/
public class EasyPoiUtil {
/**
* Export Excel Including the file name and the table name. Whether to create a header
*
* @param list Exported entity class
* @param title Header name
* @param sheetName sheet Table name
* @param pojoClass Mapped entity class
* @param isCreateHeader Whether to create a header
* @param fileName Filename
* @param response
* @return
*/
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response){
ExportParams exportParams = new ExportParams(title, sheetName);
exportParams.setCreateHeadRows(isCreateHeader);
defaultExport(list, pojoClass, fileName, response, exportParams);
}
/**
* Export Excel Default format Create header by default
*/
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName, HttpServletResponse response){
defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
}
/**
* map More sheet Formal derivation
* @param list
* @param fileName
* @param response
*/
public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){
defaultExport(list, fileName, response);
}
/**
* General default export method
* @param list
* @param pojoClass
* @param fileName
* @param response
* @param exportParams
*/
private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {
Workbook workbook = ExcelExportUtil.exportExcel(exportParams,pojoClass,list);
ExcelExportUtil.closeExportBigExcel();
if (workbook != null);
downLoadExcel(fileName, response, workbook);
}
/**
* More sheet Default export method
* @param list
* @param fileName
* @param response
*/
private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
ExcelExportUtil.closeExportBigExcel();
if (workbook != null);
downLoadExcel(fileName, response, workbook);
}
/**
* Download excel
* @param fileName
* @param response
* @param workbook
*/
private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition",
"attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
workbook.write(response.getOutputStream());
} catch (IOException e) {
throw new ZXException(e.getMessage());
}
}
/**
* Import File path form
* @param filePath
* @param titleRows
* @param headerRows
* @param pojoClass
* @param <T>
* @return
*/
public static <T> List<T> importExcel(String filePath,Integer titleRows,Integer headerRows, Class<T> pojoClass){
if (StringUtils.isBlank(filePath)){
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
List<T> list = null;
try {
list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
}catch (NoSuchElementException e){
throw new ZXException(" Template cannot be empty ");
} catch (Exception e) {
e.printStackTrace();
throw new ZXException(e.getMessage());
}
return list;
}
/**
* Import MultipartFile Form
* @param file
* @param titleRows
* @param headerRows
* @param pojoClass
* @param <T>
* @return
*/
public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass){
if (file == null){
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
List<T> list = null;
try {
list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
}catch (NoSuchElementException e){
throw new ZXException("excel File cannot be empty ");
} catch (Exception e) {
throw new ZXException(e.getMessage());
}
return list;
}
}
Use sample
Entity class
public class BlackListExport {
@Excel(name = " Customer name ", width = 15, orderNum = "2")
private String name;
@Excel(name = " Remarks ", width = 10, orderNum = "1")
private String remark;
@Excel(name = " Mobile phone number ", width = 15, orderNum = "0")
private String phone;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getRemark() {
return remark;
}
public void setRemark(String remark) {
this.remark = remark;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public BlackListExport() {
}
public BlackListExport(String name, String remark, String phone) {
this.name = name;
this.remark = remark;
this.phone = phone;
}
}
Interface
@ApiOperation(value = "easyPoiUtil Export test ")
@GetMapping(value = "/poi/export1")
public void export1(HttpServletResponse response){
List<BlackListExport> list=new ArrayList<>();
for(int i=0;i<10000;i++){
list.add(new BlackListExport(i+"",i+"",i+""));
}
EasyPoiUtil.exportExcel(list,"zx","huangy",BlackListExport.class,"zx.xls",response);
}
/**
* If the padding is different sheet Get data Data lists use the same list Object is populated,
* The number will appear 1 Sub-filled sheet There is data, and other uses are the same in the future list Object data Filled sheet There is no data display.
* @param response
*/
@ApiOperation(value = " More sheet Export test ")
@GetMapping(value = "/poi/export2")
public void export2(HttpServletResponse response){
// Query data , Omitted here
List list = new ArrayList<>();
list.add(new BlackListExport(" Name 1"," Remarks 1"," Mobile phone 1")) ;
list.add(new BlackListExport(" Name 2"," Remarks 2"," Mobile phone 2")) ;
list.add(new BlackListExport(" Name 3"," Remarks 3"," Mobile phone 3")) ;
List list2 = new ArrayList<>();
list2.add(new BlackListExport(" Name -1"," Remarks -1"," Mobile phone -1")) ;
list2.add(new BlackListExport(" Name -2"," Remarks -2"," Mobile phone -2")) ;
list2.add(new BlackListExport(" Name -3"," Remarks -3"," Mobile phone -3")) ;
List<Map<String,Object>> sheetsList = new ArrayList<>();
for(int i=1;i<=4;i++){
// Setting export configuration
// Create a parameter object (used to set the excel Get sheet Content and other information)
ExportParams params = new ExportParams() ;
// Settings sheet Name of
params.setSheetName(" Forms "+i);
// Create sheet Used map
Map<String,Object> dataMap = new HashMap<>();
// title Parameters of are ExportParams Type, currently only in the ExportParams Set in the sheetName
dataMap.put("title",params) ;
// Template exports the corresponding entity type
dataMap.put("entity",BlackListExport.class) ;
// sheet Data to populate in
if(i%2==0){
dataMap.put("data",list) ;
}else {
dataMap.put("data",list2) ;
}
sheetsList.add(dataMap);
}
EasyPoiUtil.exportExcel(sheetsList,"hy.xls",response);
}