rely on
<!-- https://mvnrepository.com/artifact/cn.afterturn/easypoi-spring-boot-starter --> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-spring-boot-starter</artifactId> <version>4.3.0</version> </dependency>
Notes usage
- @Excel is used on fields
- @ExcelCollection represents the list in the root object
- @ExcelEntity represents an entity in the root object
- @ExcelIgnore ignore export
- @ExcelTarget binding Id
@ExcelTarget binding Id
The same entity is mapped to Excel, but the header names are different. Different names are mapped according to different classes
The @ ExcelTarget here indicates that different processing can be done for different fields by using the X object
Similarly, both ExcelEntity and ExcelCollection support this method
@Excel is used on fields
attribute | type | Default value | function |
---|---|---|---|
name | String | null | Column name, support ExcelTarget |
needMerge | boolean | fasle | Vertically merge cells (used to merge multiple rows created by a list with a single cell in the list) |
orderNum | String | "0" | Column sorting, support ExcelTarget |
replace | String[] | {} | Character replacement export is {"replacement value source", "replacement value source"} |
savePath | String | "upload" | The path to save the imported file can be filled in if it is a picture. The default is upload/className/ IconEntity. This class corresponds to upload/Icon/ |
type | int | 1 | Export type 1 is text, 2 is picture, 3 is function, 10 is number, and the default is text |
width | double | 10 | Column width |
height | double | 10 | Column height. We plan to use @ ExcelTarget height uniformly in the future. This will be discarded. Please pay attention |
isStatistics | boolean | fasle | Automatic statistics: add a row of statistics and output all data [this processing will swallow exceptions, please pay attention to this] |
isHyperlink | boolean | FALSE | Hyperlink, if it is necessary to implement the interface, return the object |
isImportField | boolean | TRUE | Check the field to see if it is in the imported Excel. If it is not indicated that it is an error, the reading fails and ExcelTarget is supported |
exportFormat | String | "" | The exported time format determines whether the date needs to be formatted by whether it is empty |
importFormat | String | "" | The imported time format determines whether the date needs to be formatted by whether it is empty |
format | String | "" | The time format is equivalent to setting both exportFormat and importFormat |
databaseFormat | String | "yyyyMMddHHmmss" | Export time settings. If the field is of Date type, you do not need to set the database. If it is of string type, you need to set the database format to convert the time format output |
numFormat | String | "" | Number format, the parameter is Pattern, and the object used is DecimalFormat |
imageType | int | 1 | Export type 1 reads from file 2 reads from the database. The default is file. The same is true for import |
suffix | String | "" | Text suffix, e.g. 90 suffix = "%" becomes 90% |
isWrap | boolean | TRUE | Whether line feed is supported \ n |
mergeRely | int[] | {} | Merge cell dependencies. For example, if the second column is merged based on the first column, {0} is OK |
mergeVertical | boolean | fasle | Vertically merge cells with the same content |
fixedIndex | int | -1 | Column corresponding to excel, ignoring name |
isColumnHidden | boolean | FALSE | Export hidden columns |
See how to use it. There is complete code below to copy
@Data @ExcelTarget("user1") @Accessors(fluent = true) public class TestBean implements Serializable { @Excel(name = "A Class number_user1,B Class number_user2" ,//Header name width = 15.0, //width orderNum = "0", //Sorting can also be used consecutively in @ ExcelEntity. Excel in ExcelCollection cannot be used consecutively needMerge = true) private Integer id; @Excel(name = "full name",width = 15.0,orderNum = "1",needMerge = true) private String name; @Excel(name = "Gender", replace = {"male_1","female_2"}, //Character replacement source real 1 for male and 2 for female suffix = "living",//Add Suffix orderNum = "2",needMerge = true) private int sex; @Excel(name = "birthday", databaseFormat = "yyyyMMddHHmmss",//If it is a string > > synchronize the database format format = "yyyy-MM-dd HH:mm:ss",//Set import / export format orderNum = "3", needMerge = true) private Date bir; @ExcelIgnore //Do not generate data @Excel(name = "Birthday 2", databaseFormat = "yyyy-MM-dd HH:mm:ss",format = "yyyy-MM-dd HH:mm:ss",orderNum = "4") private Date bir2; @Excel(name = "Photo",type = 2,//Set data type 2 as picture imageType = 1,//Picture usage orderNum = "8",width = 30,height = 30) private String img; @Excel(name = "Photo 2",type = 2,imageType = 2,orderNum = "8",width = 30,height = 30) private byte[] img1; @ExcelEntity //It means that this is an entity, which is actually equivalent to adding other fields to the root entity private Card card; @ExcelCollection(name = "goods",orderNum = "7")//This collection will loop through the object.. the previous field data is only displayed in the first row private List<Order> orders;
@ExcelCollection
attribute | type | Default value | function |
---|---|---|---|
id | String | null | Definition ID |
name | String | null | Define collection column names and support ExcelTarget |
orderNum | int | 0 | Sorting, support ExcelTarget |
type | Class<?> | ArrayList.class | Create objects on import using |
Entity code
3 entities
@Data @ExcelTarget("user1") @Accessors(fluent = true) public class TestBean implements Serializable { public static String imgPath="imgs/company/baidu.png"; @Excel(name = "A Class number_user1,B Class number_user2" ,//Header name width = 15.0, //width orderNum = "0", //Sorting can also be used consecutively in @ ExcelEntity. Excel in ExcelCollection cannot be used consecutively needMerge = true) private Integer id; @Excel(name = "full name",width = 15.0,orderNum = "1",needMerge = true) private String name; @Excel(name = "Gender", replace = {"male_1","female_2"}, //Character replacement source real 1 for male and 2 for female suffix = "living",//Add Suffix orderNum = "2",needMerge = true) private int sex; @Excel(name = "birthday", databaseFormat = "yyyyMMddHHmmss",//If it is a string > > synchronize the database format format = "yyyy-MM-dd HH:mm:ss",//Set import / export format orderNum = "3", needMerge = true) private Date bir; @ExcelIgnore //Do not generate data @Excel(name = "Birthday 2", databaseFormat = "yyyy-MM-dd HH:mm:ss",format = "yyyy-MM-dd HH:mm:ss",orderNum = "4") private Date bir2; @Excel(name = "Photo",type = 2,//Set data type 2 as picture imageType = 1,//Picture usage orderNum = "8",width = 30,height = 30) private String img; @Excel(name = "Photo 2",type = 2,imageType = 2,orderNum = "8",width = 30,height = 30) private byte[] img1; @ExcelEntity //It means that this is an entity, which is actually equivalent to adding other fields to the root entity private Card card; @ExcelCollection(name = "goods",orderNum = "7")//This collection will loop through the object.. the previous field data is only displayed in the first row private List<Order> orders; //Get 10 complete data public static List<TestBean> getListUser(){ List<TestBean> list=new ArrayList<>(); for (int i = 1; i <= 10; i++) { list.add(new TestBean().id(i).name("full name"+i).bir(new Date()).sex(i%2==0?1:2).card(new Card()) .orders(getOrder()) .img(imgPath)); } return list; } //Only get the root object data, no pictures, list s, entities public static List<TestBean> getBigListFor10000(int page){ List<TestBean> list=new ArrayList<>(); for (int i = page; i <= page*10000; i++) { list.add(new TestBean().id(i).name("full name"+i).bir(new Date()).sex(i%2==0?1:2)); } return list; } public static List<Order> getOrder(){ List<Order> list=new ArrayList<>(); for (int i = 1; i <=3 ; i++) { list.add(new Order().id(i).name("goods"+i)); } return list; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public int getSex() { return sex; } public void setSex(int sex) { this.sex = sex; } public Date getBir() { return bir; } public void setBir(Date bir) { this.bir = bir; } public Date getBir2() { return bir2; } public void setBir2(Date bir2) { this.bir2 = bir2; } public String getImg() { return img; } public void setImg(String img) { this.img = img; } public byte[] getImg1() { return img1; } public void setImg1(byte[] img1) { this.img1 = img1; } public Card getCard() { return card; } public void setCard(Card card) { this.card = card; } public List<Order> getOrders() { return orders; } public void setOrders(List<Order> orders) { this.orders = orders; } //Verification file directory public static String existsDir(){ String dir="D:/eazyPoi/"; File savefile = new File(dir); if (!savefile.exists()) { savefile.mkdirs(); } return dir; } }
@Accessors(fluent = true) @ExcelTarget("card1") @Data public class Card{ @Excel(name = "ID",orderNum = "5") private String id ; @Excel(name = "address",orderNum = "6") private String addr; public Card() { this.id = "123212321";this.addr = "Chaoyang District of Beijing City"; } public String getId() { return id; } public void setId(String id) { this.id = id; } public String getAddr() { return addr; } public void setAddr(String addr) { this.addr = addr; } }
@Accessors(fluent = true) @ExcelTarget("order1") @Data public class Order{ @Excel(name = "Serial number",orderNum = "1") public int id ; @Excel(name = "goods",orderNum = "2") private String name; 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; } }
Excel export
Normal quantity export (1-2W pieces)
package com.ruoyi.web.eazyPoi; import cn.afterturn.easypoi.excel.ExcelExportUtil; import cn.afterturn.easypoi.excel.entity.ExportParams; import cn.afterturn.easypoi.handler.inter.IExcelExportServer; import cn.afterturn.easypoi.handler.inter.IWriter; import org.apache.poi.hssf.record.DVALRecord; import org.apache.poi.ss.usermodel.Workbook; import java.io.File; import java.io.FileOutputStream; import java.util.ArrayList; import java.util.Date; import java.util.List; public class Test { public static void main(String[] args) throws Exception { Test.exportExcel(); } //Methods below 10000 public static void exportExcel()throws Exception{ ExportParams exportParams=new ExportParams("User information table", "User information"); List<TestBean> listUser = TestBean.getListUser(); Workbook sheets = ExcelExportUtil.exportExcel(exportParams, TestBean.class, listUser); sheets.write(new FileOutputStream(TestBean.existsDir()+System.currentTimeMillis() +".xls")); sheets.close(); } }
Large data export
The @ Override function here is a circular function. Obj is the queryparams (parameter 4) passed from the outer layer, but the page will cycle to > obj. For example, our 10 page data page will cycle more than 10 times
- After the sql condition is paged to the last page, the query is
public class Test { public static void main(String[] args) throws Exception { Test.exportExcel(); } public static void exportBigExcel()throws Exception{ Workbook workbook = null; Date start = new Date(); ExportParams exportParams=new ExportParams("User information table", "User information"); workbook = ExcelExportUtil.exportBigExcel(exportParams, TestBean.class, new IExcelExportServer() { /** * @param obj PageCount * @param page Current number of pages * @return */ @Override public List<Object> selectListForExcelExport(Object obj, int page) { //page is incremented from 1 * * (it should be made to end when the number is transmitted outside, but it is not added back directly), so it is manually limited if((int)obj==page){return null;}; //Last returned table object List<Object> listUser = new ArrayList<>(); //Simulated service paging query listUser.addAll(TestBean.getBigListFor10000(page)); return listUser; } },10); workbook.write(new FileOutputStream(TestBean.existsDir()+System.currentTimeMillis() +".xlsx")); workbook.close(); } }
Multi sheet export
public void moveSheet(){ //sheet collection information List<Map<String,Object>> sheetList=new ArrayList<Map<String,Object>>(); //Create 2 sheet s in a circular manner for (int i = 0; i <2 ; i++) { //get data List<TestBean> listUser = TestBean.getListUser(); //Encapsulate sheet information Map<String,Object> mapData=new HashMap<String, Object>(); ExportParams params = new ExportParams(); //sheet name params.setSheetName("Student information sheet"+i); mapData.put("title",params);//Table information mapData.put("entity",TestBean.class);//Entity class mapData.put("data",listUser);//List set sheetList.add(mapData);//sheet set } //export Workbook workbook = ExcelExportUtil.exportExcel(sheetList, ExcelType.XSSF); FileOutputStream fos = null; try { fos = new FileOutputStream("D:/home/excel/Test more Sheet.xls"); workbook.write(fos); fos.close(); } catch (Exception e) { e.printStackTrace(); } }
Excel import
ImportParams parameter
value | type | Default value | function |
---|---|---|---|
titleRows | int | 0 | Number of table title rows, default 0 |
headRows | int | 1 | Number of header rows, 1 by default |
startRows | int | 0 | Set the position to start reading, that is, read the data after the first row from 0 under the title to all 1 |
keyIndex | int | 0 | How to set the primary key? If the cell has no value, skip or think it is the value below the list. This column must have a value, otherwise it is considered as invalid data |
startSheetIndex | int | 0 | Used in combination with sheetNum. There are 10 sheets when there are multiple sheets. We want to read 1-4 sheets |
sheetNum | int | 1 | The number of sheet s to be read when uploading a table. The default value is 1 |
needSave | boolean | FALSE | Do you want to save the uploaded Excel |
needVerfiy | boolean | FALSE | Need to verify the uploaded Excel |
saveUrl | String | "upload/excelUpload" | Save the uploaded excel directory. The default is TestEntity. The save path is upload / Excel upload / test / yyyymmddhhmss_ Save name upload time_ Five digit random number |
verifyHanlder | IExcelVerifyHandler | null | Verification processing interface, custom verification |
lastOfInvalidRow | int | 0 | Last invalid rows, unread rows |
readRows | int | 0 | Manually control the number of rows read. 2 = the first row of data after head is set |
importFields | String[] | null | There must be some fields in excel file, and there must be these data |
keyMark | String | ":" | Key Value reads the tag. Take this as the key, the next Cell as the Value, and multiple cells as ArrayList |
readSingleCell | boolean | FALSE | Read the global scan Excel according to the key value rule, but skip the List reading range to improve performance. Only titleRows + headRows + startRows and lastOfInvalidRow are supported |
dataHanlder | IExcelDataHandler | null | The data processing interface is mainly based on this. Replace and format are behind it |
Import importExcel normally
You need to comment out the Excel fields of the picture in the entity
//Normal table public void importExcel1() { ImportParams params = new ImportParams(); params.setTitleRows(1); params.setHeadRows(1); params.setStartRows(0);//Set the position to start reading, that is, read the data after the first row from 0 under the title to all 1 // params.setKeyIndex(1);// // params.setStartSheetIndex(0); params.setSheetNum(1);// When two sheets are used together, there are 10 sheets. We want to read 1-4 sheets // params.setReadRows(3);// 2 = the first row of data after the measured setting of head params.setImportFields(new String[]{"full name","Gender"});//There must be some fields in excel file, and there must be these data // params.setReadRows(4); File file = new File(getWebRootPath("import/B.xlsx")); List<TestBean> list = ExcelImportUtil.importExcel(file,TestBean.class, params); list.stream().forEach(i-> System.out.println(i.toString())); }
Large data importExcelBySax
//Import large amounts of data public void importExcelBySax() throws FileNotFoundException { /** You need to add a jar package that relies on xml parsing <dependency> <groupId>xerces</groupId> <artifactId>xercesImpl</artifactId> <version>2.9.1</version> </dependency> */ ImportParams params = new ImportParams(); params.setTitleRows(1); List<TestBean> list=new ArrayList<>(); ExcelImportUtil.importExcelBySax( new FileInputStream( new File(getWebRootPath("import/ExcelExportMsgClient.xlsx"))), TestBean.class, params, new IReadHandler<TestBean>() { @Override//Each row read public void handler(TestBean o) { list.add(o); System.out.println(ReflectionToStringBuilder.toString(o)); } @Override public void doAfterAll() { System.out.println("Processing after data execution"); } }); }
asynchronous
I can see from the official demo that it's not going anywhere soon
public void asyncTask(){ ImportParams params =new ImportParams(); params.setHeadRows(1); params.setTitleRows(1); params.setConcurrentTask(true);//Turn on asynchronous params.setCritical(500); List<TestBean> objects = ExcelImportUtil.importExcel( new File(getWebRootPath("import/B.xlsx")), TestBean.class, params ); objects.stream().forEach(i-> System.out.println(i.toString())); }
Read merged header
//Normal table public void importExcel1() { ImportParams params = new ImportParams(); params.setTitleRows(1);//Title 1 line params.setHeadRows(2);//There are 2 rows in the header File file = new File(getWebRootPath("import/B.xlsx")); List<TestBean> list = ExcelImportUtil.importExcel(file,TestBean.class, params); list.stream().forEach(i-> System.out.println(i.toString())); }
Verify the import (screen out the conforming and nonconforming data)
The official gives a lot of demo s, which is a little cumbersome. I think this is more practical
Add in entity class hibernate field validation annotation
This does not require the introduction of additional dependencies
Mainly verify these three locations
public void validFieldImport() throws IOException { ImportParams params=new ImportParams(); params.setTitleRows(1); params.setHeadRows(2); params.setNeedVerify(true);//Settings need validation params.setVerifyGroup(new Class[]{ValidGroup.class});//Set authentication group ExcelImportResult<TestBean> importResult = ExcelImportUtil.importExcelMore(new File(getWebRootPath("import/B.xlsx")), TestBean.class, params); //Compliance verification List<TestBean> list = importResult.getList(); //Non conformance verification List<TestBean> failList = importResult.getFailList(); //Get map Map<String, Object> map = importResult.getMap(); //Export validated data importResult.getWorkbook().write(new FileOutputStream("D:/eazyPoi/validExcel.xlsx")); //Non conforming data importResult.getFailWorkbook().write(new FileOutputStream("D:/eazyPoi/failValidExcel.xlsx")); System.out.println("Verification failed"+importResult.isVerifyFail()); }
The bir field does not have a validation group, so it does not participate in validation
excel that fails to export will be prompted
groupName of header
import picture
@Excel(name = "company LOGO", type = 2 ,width = 40 , height = 30,imageType = 1, savePath="D:\\javaProject\\") private String companyLogo;
CSV import
New C.csv file
id,name,sex,bir 1,Xiao Ming,1,1991-08-08 2,Xiao Zhang,1,1991-09-08 3,Xiao Hong,2,1991-09-08
public void importCSV()throws Exception{ Date start = new Date(); CsvImportParams params = new CsvImportParams(CsvImportParams.UTF8); CsvImportUtil.importCsv(new FileInputStream( new File(getWebRootPath("import/C.csv"))), Map.class, params, new IReadHandler<Map>() { @Override public void handler(Map o) { System.out.println(JSON.toJSONString(o)); } @Override public void doAfterAll() { } }); }
excel to csv
Excel
public void excel2Csv()throws Exception{ //Exported CSV FileOutputStream fos = new FileOutputStream("D:\\eazyPoi\\aa.csv"); ImportParams params = new ImportParams(); params.setTitleRows(1); params.setHeadRows(1); CsvExportParams csvExportParams = new CsvExportParams(); csvExportParams.setEncoding(CsvExportParams.GBK);//UTF8 can be used IWriter ce = CsvExportUtil.exportCsv(csvExportParams, TestBean.class, fos); ExcelImportUtil.importExcelBySax( new FileInputStream( new File(getWebRootPath("import/B.xlsx"))), TestBean.class, params, new IReadHandler<TestBean>() { private List<TestBean> list = new ArrayList<>(); @Override public void handler(TestBean o) { //Add to list after reading list.add(o); } @Override public void doAfterAll() { //Convert output after reading ce.write(list); list.clear(); System.out.println("succcess--------------------------------"); } }); }
The CSV red box is the entity class annotation field
Excel to html
public void testToAllHtmlWorkbookAndImage()throws Exception{ Workbook wb = new XSSFWorkbook(new FileInputStream(new File(getWebRootPath("import/B.xlsx")))); String html = ExcelXorHtmlUtil.excelToHtml(new ExcelToHtmlParams(wb, true, "D:\\eazyPoi\\")); FileWriter fw = new FileWriter("D:/eazyPoi/B.html"); fw.write(html); fw.close(); }