Easy-POI
Tool link
Github: https://github.com/programmeres/easy-poi
Code cloud: https://gitee.com/nw1992/easy-poi
Easy POI is a lightweight open source component of Excel import and export solution.
(if you like or are willing to use it, please star and Watch the project. If you are an enterprise, please let me know the name of the enterprise by modifying the list of enterprises in this document, so as to encourage and motivate us to maintain the project continuously.)
Find and solve the bug and have self tested. After pullRequest, you can inform us by email (Magic app @ 126. Com), merge and release the latest version in the first time
Use enterprise list:
Function introduction
Browser export Excel file (support single / multi sheet)
Browser export Excel template file
Specify the path to generate Excel file (support single / multiple sheet s)
Returns the OutputStream of an excel file (supporting single / multiple sheet s), which is generally used to upload Excel files to a remote location, such as FTP
Import Excel file (support single / multiple sheet s)
Problems solved
1. Solve the problem of memory overflow caused by exporting a large amount of data (support paging query database, adopt the official poi recommended api (SXSSFWorkbook) to refresh the specified number of rows to disk)
2. Solve the problem of memory overflow caused by importing a large amount of data (insert the database in pages, use the official api recommended by poi (XSSF and SAX), and use the SAX mode to read one line into memory)
3. Solve the problem of null value reading caused by null false lines with placeholders
4. Solve the problem of inaccurate precision of Long type or BigDecimal
Component features
1. Import can customize the processing logic of successful or failed parsing
2. The export supports paging query and full query, and customizes the processing logic of each data
3. Built in cache, 30000 pieces of 11 columns of data, about 2.2s for the first export and 1.4s for the second export; about 3.5s for the first import and about 2.5s for the second import
4. Annotation operation, light and convenient
5. RegexConst (ID card number, mobile phone number, amount, email) is a built-in regular expression class
6. Cell width (the maximum length of cell content shall not exceed 20 Chinese characters)
7. If there is an exception, the Sheet, row and column positions will also be printed
8. The user-defined string information and Excel information in the annotation are all trim, so you don't need to worry about the risk of space before and after
9.Excel style is simple, generous and beautiful
Components need to know
Only Excel files with xlsx suffix are supported for import and export. The attribute order of annotation, i.e. the arrangement order of Excel columns, and the dateformat are "yyyy MM DD HH: mm: SS" by default
Import
1. When importing Excel and reading a blank row, stop reading the data row after the current Sheet
2. Import Excel file, use text or general cell format to prevent unpredictable exception
3. The import field types support: Date, short, int eger, double, long, float, BigDecimal, String
4. The precision of the imported BigDecimal field is 2 by default, and the routingmode is bigdecimal.round'half'even by default
5. The first row of valid cells must contain content and the number of columns imported into Excel file must be equal to the number of annotated attributes based on the first row
6. The format of date type field in Excel should be consistent with that of dateformat (counterexample: December 31, 2018 and "yyyy MM DD") and the length should be consistent or longer (counterexample: December 31, 2018 and yyyy MM DD HH: mm: SS "). Otherwise, SimpleDateFormat will fail to parse and report" Unparseable date: "
export
1. The exported BigDecimal field is not precision formatted by default
2. Paging query starts from the first page by default, with 3000 entries per page
3.Excel will refresh the data in memory to disk every 2000 pieces of data
4. Use the separate Sheet export method to separate sheets for every 80000 rows of data
5. When using the (exportResponse, exportStream, generateExcelStream) method, when a single Sheet is more than 1 million, it will be divided into sheets
6. The annotation attribute type should be consistent with the database type
extend
Inheriting the EasyPoi class, you can use the subclass constructor to override the following default parameters
//Number of Excel auto refresh to disk
public static final int DEFAULT_ROW_ACCESS_WINDOW_SIZE = 2000;
//Pagination number
public static final int DEFAULT_PAGE_SIZE = 3000;
//Number of sheets
public static final int DEFAULT_RECORD_COUNT_PEER_SHEET = 80000;User manual
1. Introduce Maven dependency
2. Annotate the entity attribute to be exported or imported with @ ExportField or @ ImportField annotation
3. Directly call the export or import API
/**
-
Introduction to export annotation function
/**
*/
public @interface ExportField {- excel column name
*/
String columnName();
- Default cell value
*/
String defaultCellValue() default "";
- Date format default yyyy MM DD HH: mm: SS
*/
String dateFormat() default "yyyy-MM-dd HH:mm:ss";
- BigDecimal precision default: - 1 (BigDecimal format is not enabled by default)
*/
int scale() default -1;
- BigDecimal rounding rule default: bigdecimal.round ABCD half ABCD even
*/
int roundingMode() default BigDecimal.ROUND_HALF_EVEN;
}
java
/**
- excel column name
Export annotation Demo
*/
public class ExportFielddemo {
@ExportField(columnName = "ID", defaultCellValue = "1")
private Integer id;
@ExportField(columnName = name, defaultCellValue = Zhang San)
private String name;
@ExportField(columnName = revenue amount, defaultcellvalue = 100, scale = 2, roundingmode = bigdecimal.round_half_even)
private BigDecimal money;
@ExportField(columnName = "creation time", dateformat = "yyyy MM DD", defaultcellvalue = "2019-01-01")
private Date createTime;
}
```
@ImportField
/**
* Introduction to import annotation function
*/
public @interface ImportField {
/**
* @return Is it necessary to fill in?
*/
boolean required() default false;
/**
* Date format default yyyy MM DD HH: mm: SS
*/
String dateFormat() default "yyyy-MM-dd HH:mm:ss";
/**
* Regular expression validation
*/
String regex() default "";
/**
* The error information returned from the failure of regular expression verification. It takes effect after regex configuration
*/
String regexMessage() default "Regular expression validation failed";
/**
* BigDecimal Precision default: 2
*/
int scale() default 2;
/**
* BigDecimal Rounding rule default: bigdecimal.round ABCD half ABCD even
*/
int roundingMode() default BigDecimal.ROUND_HALF_EVEN;
}/**
* Import annotation Demo
*/
public class ImportField {
@ImportField(required = true)
private Integer id;
@ImportField(regex = IDCARD_REGEX, regexMessage="ID card verification failed")
private String idCard;
@ImportField(scale = 2, roundingMode=BigDecimal.ROUND_HALF_EVEN)
private BigDecimal money;
@ImportField(dateFormat="yyyy-MM-dd")
private Date createTime;
}Export Demo
/**
* Export Demo
*/
public class ExportDemo {
/**
* Browser export Excel
*
* @param httpServletResponse
*/
public void exportResponse(HttpServletResponse httpServletResponse) {
ParamEntity queryQaram = new ParamEntity();
EasyPoi.ExportBuilder(httpServletResponse, "Excel file name", AnnotationEntity.class).exportResponse(queryQaram,
new ExportFunction<ParamEntity, ResultEntity>() {
/**
* @param queryQaram Query criteria object
* @param pageNum Current number of pages, starting from 1
* @param pageSize Number of pieces per page, 3000 by default
* @return
*/
@Override
public List<ResultEntity> pageQuery(ParamEntity queryQaram, int pageNum, int pageSize) {
//Paging query operation
return new ArrayList<>();
}
/**
* Transform every data found
*
* @param o
*/
@Override
public void convert(ResultEntity o) {
//Conversion operation
}
});
}
/**
* Browser multi sheet export Excel
*
* @param httpServletResponse
*/
public void exportMultiSheetResponse(HttpServletResponse httpServletResponse) {
ParamEntity queryQaram = new ParamEntity();
EasyPoi.ExportBuilder(httpServletResponse, "Excel file name", AnnotationEntity.class).exportMultiSheetStream(queryQaram,
new ExportFunction<ParamEntity, ResultEntity>() {
/**
* @param queryQaram Query criteria object
* @param pageNum Current number of pages, starting from 1
* @param pageSize Number of pieces per page, 3000 by default
* @return
*/
@Override
public List<ResultEntity> pageQuery(ParamEntity queryQaram, int pageNum, int pageSize) {
//Paging query operation
return new ArrayList<ResultEntity>();
}
/**
* Transform every data found
*
* @param o
*/
@Override
public void convert(ResultEntity o) {
//Conversion operation
}
});
}
/**
* Export Excel to specified path
*/
public void exportStream() throws FileNotFoundException {
ParamEntity queryQaram = new ParamEntity();
EasyPoi.ExportBuilder(new FileOutputStream(new File("C:\\Users\\Excel file.xlsx")), "Sheet name", AnnotationEntity.class)
.exportStream(queryQaram, new ExportFunction<ParamEntity, ResultEntity>() {
/**
* @param queryQaram Query criteria object
* @param pageNum Current number of pages, starting from 1
* @param pageSize Number of pieces per page, 3000 by default
* @return
*/
@Override
public List<ResultEntity> pageQuery(ParamEntity queryQaram, int pageNum, int pageSize) {
//Paging query operation
return new ArrayList<>();
}
/**
* Transform every data found
*
* @param o
*/
@Override
public void convert(ResultEntity o) {
//Conversion operation
}
});
}
/**
* Export multiple sheet Excel to the specified path
*/
@RequestMapping(value = "exportResponse")
public void exportMultiSheetStream() throws FileNotFoundException {
ParamEntity queryQaram = new ParamEntity();
EasyPoi.ExportBuilder(new FileOutputStream(new File("C:\\Users\\Excel file.xlsx")), "Sheet name", AnnotationEntity.class)
.exportMultiSheetStream(queryQaram, new ExportFunction<ParamEntity, ResultEntity>() {
/**
* @param queryQaram Query criteria object
* @param pageNum Current number of pages, starting from 1
* @param pageSize Number of pieces per page, 3000 by default
* @return
*/
@Override
public List<ResultEntity> pageQuery(ParamEntity queryQaram, int pageNum, int pageSize) {
//Paging query operation
return new ArrayList<>();
}
/**
* Transform every data found
*
* @param o
*/
@Override
public void convert(ResultEntity o) {
//Conversion operation
}
});
}
/**
* Generate Excel OutputStream object
*/
public void generateExcelStream() throws FileNotFoundException {
ParamEntity queryQaram = new ParamEntity();
OutputStream outputStream = EasyPoi.ExportBuilder(new FileOutputStream(new File("C:\\Users\\Excel file.xlsx")), "Sheet name", AnnotationEntity.class)
.generateExcelStream(queryQaram, new ExportFunction<ParamEntity, ResultEntity>() {
/**
* @param queryQaram Query criteria object
* @param pageNum Current number of pages, starting from 1
* @param pageSize Number of pieces per page, 3000 by default
* @return
*/
@Override
public List<ResultEntity> pageQuery(ParamEntity queryQaram, int pageNum, int pageSize) {
//Paging query operation
return new ArrayList<>();
}
/**
* Transform every data found
*
* @param o
*/
@Override
public void convert(ResultEntity o) {
//Conversion operation
}
});
}
/**
* Generate multiple Sheet Excel OutputStream objects
*/
public void generateMultiSheetExcelStream() throws FileNotFoundException {
ParamEntity queryQaram = new ParamEntity();
OutputStream outputStream = EasyPoi.ExportBuilder(new FileOutputStream(new File("C:\\Users\\Excel file.xlsx")), "Sheet name", AnnotationEntity.class)
.generateMultiSheetExcelStream(queryQaram, new ExportFunction<ParamEntity, ResultEntity>() {
/**
* @param queryQaram Query criteria object
* @param pageNum Current number of pages, starting from 1
* @param pageSize Number of pieces per page, 3000 by default
* @return
*/
@Override
public List<ResultEntity> pageQuery(ParamEntity queryQaram, int pageNum, int pageSize) {
//Paging query operation
return new ArrayList<>();
}
/**
* Transform every data found
*
* @param o
*/
@Override
public void convert(ResultEntity o) {
//Conversion operation
}
});
}
/**
* Export Excel template
*/
public void exportTemplate(HttpServletResponse httpServletResponse) {
EasyPoi.ExportBuilder(httpServletResponse, "Excel Template name", AnnotationEntity.class).exportTemplate();
}
}Import Demo
/**
* Import Demo
*/
public class ImportDemo {
public void importExcel() throws IOException {
EasyPoi.ImportBuilder(new FileInputStream(new File("C:\\Users\\Import Excel file.xlsx")), AnnotationEntity.class)
.importExcel(new ExcelImportFunction<ResultEntity>() {
/**
* @param sheetIndex Index of the currently executed Sheet, starting at 1
* @param rowIndex Number of currently executed rows, starting at 1
* @param resultEntity Excel Entity of row data
*/
@Override
public void onProcess(int sheetIndex, int rowIndex, ResultEntity resultEntity) {
//Customize verification and operation for each data
//Paging insert: when the number of read rows reaches the number of user-defined rows, insert the database
}
/**
* @param errorEntity Error message entity
*/
@Override
public void onError(ErrorEntity errorEntity) {
//Operation error information after non null and regular verification of each data
}
});
}
}