1, EasyExcel
EasyExcel is one of Alibaba's open source POI plug-ins. It mainly solves the problems of complex use of POI framework, difficult operation of sax parsing mode, and easy OOM when the amount of data is large, and solves the error reporting caused by POI concurrency.
Main solutions: load by decompressing files, line by line, and discard unimportant data such as styles and fonts to reduce the occupation of memory.
EasyExcel advantages
-
Annotated custom operation.
-
Simple input and output, providing the interface of input and output process
-
Support a certain degree of flexibility operations such as cell merging
I would like to recommend my own Spring Boot project:
https://github.com/YunaiV/ruoyi-vue-pro
2, Common notes
@ExcelProperty
Specify the column in excel corresponding to the current field. You can match by name or index. Of course, it can not be written. By default, the first field is index=0, and so on. Be careful, either don't write it all, or use index or name to match. Don't mix the three unless you know how to sort them in the source code.
@ExcelIgnore
By default, all fields will be matched with excel. If this annotation is added, this field will be ignored
@DateTimeFormat
For date conversion, using String to receive data in excel date format will call this annotation. The value inside refers to java.text.SimpleDateFormat
@NumberFormat
For digital conversion, this annotation will be called when String is used to receive data in excel digital format. The value inside refers to java.text.DecimalFormat
@ExcelIgnoreUnannotated
By default, those annotated without ExcelProperty will participate in reading and writing, and those annotated with ExcelProperty will not participate
Recommend your own actual Spring Cloud project:
https://github.com/YunaiV/onemall
3, Rely on
<!-- easyexcel Main dependence This one is basically enough--> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.1.4</version> </dependency> <!-- servlet-api --> <dependency> <groupId>javax.servlet</groupId> <artifactId>javax.servlet-api</artifactId> <version>4.0.1</version> <scope>provided</scope> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.47</version> </dependency>
4, Monitor
/**
* EasyExcel Import listening
*/
public class ExcelListener extends AnalysisEventListener {
//You can get this value through an instance
private List<Object> datas = new ArrayList<Object>();
@Override
public void invoke(Object o, AnalysisContext analysisContext) {
datas.add(o);//The data is stored in the list for batch processing or subsequent business logic processing.
doSomething(o);//Handle according to your own business
}
private void doSomething(Object object) {
//1. Warehousing call interface
}
public List<Object> getDatas() {
return datas;
}
public void setDatas(List<Object> datas) {
this.datas = datas;
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
// datas.clear();// Destroy unused resources after parsing
}
}
5, Interface import Excel
try {
//Get file name
String filename = file.getOriginalFilename();
//Get file stream
InputStream inputStream = file.getInputStream();
//Instantiate a class that implements the AnalysisEventListener interface
ExcelListener listener = new ExcelListener();
//Incoming parameters
ExcelReader excelReader = new ExcelReader(inputStream, ExcelTypeEnum.XLS, null, listener);
//Read information
excelReader.read(new Sheet(1, 0, Test.class));
//get data
List<Object> list = listener.getDatas();
if (list.size() > 1) {
for (int i = 0; i < list.size(); i++) {
Testobj = (Test) list.get(i);
JSONObject jo = new JSONObject();
}
}
} catch (Exception e) {
System.out.println(e.getMessage());
}
6, Interface export Excel
HttpServletResponse response, HttpServletRequest request
try {
String filenames = "111111";
String userAgent = request.getHeader("User-Agent");
if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {
filenames = URLEncoder.encode(filenames, "UTF-8");
} else {
filenames = new String(filenames.getBytes("UTF-8"), "ISO-8859-1");
}
response.setContentType("application/vnd.ms-exce");
response.setCharacterEncoding("utf-8");
response.addHeader("Content-Disposition", "filename=" + filenames + ".xlsx");
EasyExcel.write(response.getOutputStream(), Test.class).sheet("sheet").doWrite(testList);
} catch (Exception e) {
}
7, Local import, local export
List<Test> testList = new ArrayList<>();
try {
String strUrl = "C:\\Users\\Administrator\\Desktop\\json.xlsx";
File multipartFile = new File(strUrl);
InputStream inputStream = new FileInputStream(multipartFile);
//Instantiate a class that implements the AnalysisEventListener interface
ExcelListener listener = new ExcelListener();
//Incoming parameters
ExcelReader excelReader = new ExcelReader(inputStream, ExcelTypeEnum.XLS, null, listener);
//Read information
excelReader.read(new Sheet(1, 0, Test.class));
//get data
List<Object> list = listener.getDatas();
if (list.size() > 1) {
for (int i = 0; i < list.size(); i++) {
Testobj = (Test) list.get(i);
}
}
} catch (Exception e) {
System.out.println(e.getMessage());
}
try {
String strUrl = "C:\\Users\\Administrator\\Desktop\\json"+System.currentTimeMillis()+".xlsx";
EasyExcel.write(strUrl,Test.class).sheet("sheet").doWrite(testList);
} catch (Exception e) {
}