Click on the blue font above and select " Star Official Account "
High-quality articles, delivered in the first time
Used in the project:
Features
IMPORT
1. ExcelHandle core processor;
2. ExcelWorkbookManageexcel manages all worksheets;
3. ExcelInitConfig configuration file initialization;
4. AbstractFileParser file conversion class;
What are the advantages of alanpoi import?
1. Users do not need to introduce additional cumbersome jars such as poi;
2. Parse large files in milliseconds, support one-click parsing of multi-sheet tabs, and do not need to cyclically match and parse all data according to a certain format;
3. No matter how your system is Complex, how many imports, alanpoi supports all, and accurately returns the objects you need, reducing the workload of developers;
4. At present, the external business is becoming more and more complex, and the requirements for various functions are becoming more and more strict, of course, imports are no exception , alanpoi supports one-click write-back of errors to excel, corresponding to each row;
5. alanpoi is flexible and extensible, providing the ExcelConsumeInterface interface, which can be inherited, and implement three methods of valid, error, and end to write your own business;
A. valid: The method parameter returns all the data in excel, and the user can perform self-validation
B. error: The import error will call back
C. end: The method parameter returns the data that has been verified successfully, and the data that fails the valid verification will not be returned, and the user can operate the persistence by himself. or other businessHow to use alanpoi to import
A simple sentence: one configuration, one inheritance, one call
a configuration
Create a new excel-config.xml file in the project resources directory, configure your own consumer class path in cosume, and inherit the ExcelConsumeInterface interface. The vo in the sheet is the object path to serialize the current sheet, and the column in the column is of course the attribute in the configuration vo , among which the name is an optional field. Filled in is to match the excel column name according to this. If not filled, it is according to the offset order; if the import contains multiple sheets, configure multiple
an inheritance
The consume class inherits the ExcelConsumeInterface interface and implements the method
/**
* when error will 调用
*
* @param excelError
*/
void error(ExcelError excelError);
/**
* custom valid data
*
* @param workbookId
* @param sheetDataList
*/
void validData(String workbookId, List<ExcelSheetData> sheetDataList, Map<Serializable, Object> excelParam);
/**
* @param sheetDataList return success data
*/
void end(List<ExcelSheetData> sheetDataList, Map<Serializable, Object> excelParam);a call
The user can call the customImportData of the ExcelExportUtil class. The parameter excelId is the id configured in excel-conifg.xml
Export
describe
Can be implemented with one line of code and never need a second line, if one line does not work, then add another line!
model
Annotation mode export
ExcelSheet annotation: used to import classes, you can specify sheet name, column header color, font, height, width
ExcelColum annotation: On the properties of the imported class, you can specify the name of the column header and the style of the cell
DateFormat annotation: For the properties of the imported class, it can be output to excel according to the specified format, the default is "yyyy/MM/dd"
NumFormat Note: For the properties of the imported class, it can be output to excel according to the specified format, the default is "00.00"Example:
@ExcelSheet(name = "测试", backColor = AlanColors.GREEN, font = "宋体", fontSize = 25)
@Data
public class ExportVO {
@ExcelColumn(name = "名称", width = 32, link = "${url}")
private String name;
@ExcelColumn(name = "值")
private String value;
@ExcelColumn(name = "金额")
@NumFormat(value = "0000.00##")
private BigDecimal amount;
@ExcelColumn(name = "时间格式化")
@DateFormat(value = "yyyy-MM-dd hh:mm:ss")
private Date dateTime;
@DateFormat
@ExcelColumn(name = "日期格式化")
private java.sql.Date date;
@ExcelColumn(isExist = false)
private String url;
}use
Method 1. Export directly to the browser
ExcelExportUtil.export(Colletion>,Class,HttpServletRequest,HttpServletResponse,fileName);Method 2. Call getWorkbook to get the worksheet and process the workbook by yourself
ExcelExportUtil.getWorkbook(Collection singleSheetData, Class c)Advanced use
Example 1: Export specified columns (dynamically export columns)
List<ExportVO> list = new ArrayList<>();
for (int i = 0; i < 500; i++) {
ExportVO exportVO = new ExportVO();
exportVO.setName("name" + i);
exportVO.setValue(new BigDecimal(123.11 + i * 0.09));
exportVO.setAmount(new BigDecimal(6666.666 + i * 10));
exportVO.setDate(new Date(132324343 + i * 100));
exportVO.setDateTime(new java.util.Date());
list.add(exportVO);
}
List<String> colList = new ArrayList<>();
//按照顺序仅导出add的列
colList.add("name");
colList.add("value");
//调用获取workbook对象；也可以直接调用exportSpecifyCol方法导出到浏览器
Workbook workbook = ExcelExportUtil.getWorkbookSpecifyCol(list, ExportVO.class, colList);Example 2: Multi-sheet tab export
List<ExportVO> list = new ArrayList<>();
List<Export2VO> list2 = new ArrayList<>();
for (int i = 0; i < 500; i++) {
ExportVO exportVO = new ExportVO();
exportVO.setName("name" + i);
exportVO.setValue(new BigDecimal(123.11 + i * 0.09));
exportVO.setAmount(new BigDecimal(6666.666 + i * 10));
exportVO.setDate(new Date(132324343 + i * 100));
exportVO.setDateTime(new java.util.Date());
list.add(exportVO);
Export2VO export2VO = new Export2VO();
export2VO.setName("name" + i);
export2VO.setValue("value" + i);
export2VO.setAmount(new BigDecimal(6666.666 + i * 10));
export2VO.setDate(new Date(132324343 + i * 100));
export2VO.setDateTime(new java.util.Date());
list2.add(export2VO);
}
Map<Class<?>, Collection<?>> map = new HashMap<>();
map.put(ExportVO.class, list);
map.put(Export2VO.class, list2);
//调用获取workbook对象；也可以直接调用exportByMultiSheet方法导出到浏览器
Workbook workbook = ExcelExportUtil.getWorkbookByMultiSheet(map);The code has been open source, address:
https://github.com/alan-et/alanpoi/tree/develop/alanpoi-analysis
Source: blog.csdn.net/weixin_43225813/
article/details/108995011
