A list,
Today I recommend you a better performance of Excel import and export tool: EasyExcel, I hope to help you!
Easyexcel is ali open source an Excel import and export tool, with fast processing speed, small memory occupation, easy to use characteristics, the underlying logic is based on Apache POI for secondary development, the current application is also very wide!
Second, the practice
Integrating EasyExcel into a SpringBoot project is as simple as a single dependency.
</groupId> com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>3.0.5</version> </dependency>Copy the codeEasyExcel exports and imports support two ways of processing
- The first is to map generated file and parse file data to objects via entity-class annotations
- The second is to generate files and parse file data through dynamic parameterization
The following uses the export and import of user information as an example to introduce the two processing methods.
Simple export
To start, we just need to create a UserEntity UserEntity class and add the corresponding annotation field, as shown in the sample code
@excelProperty (value = "name ") private String Name; @excelProperty (value = "age ") private int age; @dateTimeformat (" YYYY-MM-DD HH: MM: SS ") @excelProperty (value = "operation time ") private DateTime; / / set, get... }Copy the codeThen, the EasyExcel tool class provided by EasyExcel can realize the export of the file.
List<UserWriteEntity> dataList = new ArrayList<>(); for (int i = 0; i < 10; i++) { UserWriteEntity userEntity = new UserWriteEntity(); Userentity. setName(" zhang SAN "+ I); userEntity.setAge(20 + i); userEntity.setTime(new Date(System.currentTimeMillis() + i)); dataList.add(userEntity); FileOutputStream outputStream = new FileOutputStream(new) File("/Users/panzhi/Documents/easyexcel-export-user1.xlsx")); EasyExcel. Write(outputStream, userWriteEntity.class).sheet(" user info ").dowrite (dataList); }Copy the codeRun the program, open the file content results!
Simple import
This simple, fixed header Excel file makes it easy to read file data if you want to.
The above export file as an example, using EasyExcel tool class provided by EasyExcel, can realize the fast reading of file content data, the example code is as follows:
First create the read entity class
Public class UserReadEntity {@excelProperty (value = "name ") private String Name; */ @excelProperty (index = 1) private int age; @dateTimeformat (" YYYY-MM-DD HH: MM: SS ") @excelProperty (value = "operation time ") private DateTime; / / set, get... }Copy the codeIt then reads the file data and encapsulates it into an object
/ / synchronous read from the File content FileInputStream inputStream = new FileInputStream (new File ("/Users/panzhi/Documents/easyexcel - user1. XLS ")); List<UserReadEntity> list = EasyExcel.read(inputStream).head(UserReadEntity.class).sheet().doReadSync(); System.out.println(JSONArray.toJSONString(list)); }Copy the codeRun the program and the output is as follows:
Copy the codeDynamic free export import
In actual use and development, we cannot write an entity class for every Excel import and export requirement. Many business requirements need to be dynamically imported and exported according to different fields, and there is no way to read or write files based on the annotation of entity class.
Therefore, based on dynamic parameters provided by the EasyExcel im into files and dynamic monitor reading method, we can encapsulate a set of dynamic export tool derived class alone, in the province of every time we need to write a lot of repeated work, the following is a small I in the actual use process, packaging tools, here share with you!
- First, we can write a dynamically exported utility class
private static final Logger log = LoggerFactory.getLogger(DynamicEasyExcelExportUtils.class);
private static final String DEFAULT_SHEET_NAME = "sheet1";
/**
* 动态生成导出模版(单表头)
* @param headColumns 列名称
* @return excel文件流
*/
public static byte[] exportTemplateExcelFile(List<String> headColumns){
List<List<String>> excelHead = Lists.newArrayList();
headColumns.forEach(columnName -> { excelHead.add(Lists.newArrayList(columnName)); });
byte[] stream = createExcelFile(excelHead, new ArrayList<>());
return stream;
}
/**
* 动态生成模版(复杂表头)
* @param excelHead 列名称
* @return
*/
public static byte[] exportTemplateExcelFileCustomHead(List<List<String>> excelHead){
byte[] stream = createExcelFile(excelHead, new ArrayList<>());
return stream;
}
/**
* 动态导出文件（通过map方式计算）
* @param headColumnMap 有序列头部
* @param dataList 数据体
* @return
*/
public static byte[] exportExcelFile(LinkedHashMap<String, String> headColumnMap, List<Map<String, Object>> dataList){
//获取列名称
List<List<String>> excelHead = new ArrayList<>();
if(MapUtils.isNotEmpty(headColumnMap)){
//key为匹配符，value为列名，如果多级列名用逗号隔开
headColumnMap.entrySet().forEach(entry -> {
excelHead.add(Lists.newArrayList(entry.getValue().split(",")));
});
}
List<List<Object>> excelRows = new ArrayList<>();
if(MapUtils.isNotEmpty(headColumnMap) && CollectionUtils.isNotEmpty(dataList)){
for (Map<String, Object> dataMap : dataList) {
List<Object> rows = new ArrayList<>();
headColumnMap.entrySet().forEach(headColumnEntry -> {
if(dataMap.containsKey(headColumnEntry.getKey())){
Object data = dataMap.get(headColumnEntry.getKey());
rows.add(data);
}
});
excelRows.add(rows);
}
}
byte[] stream = createExcelFile(excelHead, excelRows);
return stream;
}
/**
* 生成文件（自定义头部排列）
* @param rowHeads
* @param excelRows
* @return
*/
public static byte[] customerExportExcelFile(List<List<String>> rowHeads, List<List<Object>> excelRows){
//将行头部转成easyexcel能识别的部分
List<List<String>> excelHead = transferHead(rowHeads);
return createExcelFile(excelHead, excelRows);
}
/**
* 生成文件
* @param excelHead
* @param excelRows
* @return
*/
private static byte[] createExcelFile(List<List<String>> excelHead, List<List<Object>> excelRows){
try {
if(CollectionUtils.isNotEmpty(excelHead)){
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
EasyExcel.write(outputStream).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.head(excelHead)
.sheet(DEFAULT_SHEET_NAME)
.doWrite(excelRows);
return outputStream.toByteArray();
}
} catch (Exception e) {
log.error("动态生成excel文件失败，headColumns：" + JSONArray.toJSONString(excelHead) + "，excelRows：" + JSONArray.toJSONString(excelRows), e);
}
return null;
}
/**
* 将行头部转成easyexcel能识别的部分
* @param rowHeads
* @return
*/
public static List<List<String>> transferHead(List<List<String>> rowHeads){
//将头部列进行反转
List<List<String>> realHead = new ArrayList<>();
if(CollectionUtils.isNotEmpty(rowHeads)){
Map<Integer, List<String>> cellMap = new LinkedHashMap<>();
//遍历行
for (List<String> cells : rowHeads) {
//遍历列
for (int i = 0; i < cells.size(); i++) {
if(cellMap.containsKey(i)){
cellMap.get(i).add(cells.get(i));
} else {
cellMap.put(i, Lists.newArrayList(cells.get(i)));
}
}
}
//将列一行一行加入realHead
cellMap.entrySet().forEach(item -> realHead.add(item.getValue()));
}
return realHead;
}
/**
* 导出文件测试
* @param args
* @throws IOException
*/
public static void main(String[] args) throws IOException {
//导出包含数据内容的文件（方式一）
LinkedHashMap<String, String> headColumnMap = Maps.newLinkedHashMap();
headColumnMap.put("className","班级");
headColumnMap.put("name","学生信息,姓名");
headColumnMap.put("sex","学生信息,性别");
List<Map<String, Object>> dataList = new ArrayList<>();
for (int i = 0; i < 5; i++) {
Map<String, Object> dataMap = Maps.newHashMap();
dataMap.put("className", "一年级");
dataMap.put("name", "张三" + i);
dataMap.put("sex", "男");
dataList.add(dataMap);
}
byte[] stream1 = exportExcelFile(headColumnMap, dataList);
FileOutputStream outputStream1 = new FileOutputStream(new File("/Users/panzhi/Documents/easyexcel-export-user5.xlsx"));
outputStream1.write(stream1);
outputStream1.close();
//导出包含数据内容的文件（方式二）
//头部，第一层
List<String> head1 = new ArrayList<>();
head1.add("第一行头部列1");
head1.add("第一行头部列1");
head1.add("第一行头部列1");
head1.add("第一行头部列1");
//头部，第二层
List<String> head2 = new ArrayList<>();
head2.add("第二行头部列1");
head2.add("第二行头部列1");
head2.add("第二行头部列2");
head2.add("第二行头部列2");
//头部，第三层
List<String> head3 = new ArrayList<>();
head3.add("第三行头部列1");
head3.add("第三行头部列2");
head3.add("第三行头部列3");
head3.add("第三行头部列4");
//封装头部
List<List<String>> allHead = new ArrayList<>();
allHead.add(head1);
allHead.add(head2);
allHead.add(head3);
//封装数据体
//第一行数据
List<Object> data1 = Lists.newArrayList(1,1,1,1);
//第二行数据
List<Object> data2 = Lists.newArrayList(2,2,2,2);
List<List<Object>> allData = Lists.newArrayList(data1, data2);
byte[] stream2 = customerExportExcelFile(allHead, allData);
FileOutputStream outputStream2 = new FileOutputStream(new File("/Users/panzhi/Documents/easyexcel-export-user6.xlsx"));
outputStream2.write(stream2);
outputStream2.close();
}
}
Copy the codeThen, write a dynamic import utility class
Public class DynamicEasyExcelListener extends AnalysisEventListener<Map<Integer, String>> { private static final Logger LOGGER = LoggerFactory.getLogger(UserDataListener.class); Private List<Map<Integer, String>> headList = new ArrayList<>(); private List<Map<Integer, String>> headList = new ArrayList<>(); Private List<Map<Integer, String>> dataList = new ArrayList<>(); private List<Map<Integer, String>> dataList = new ArrayList<>(); Public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {logger. info(" Parse to a header :{}", json.tojsonString (headMap)); Headlist.add (headMap); ** @param data * one row value. Is the same as {@link AnalysisContext#readRowHolder()} * @param Context */ @override public void invoke(Map<Integer, String> data, AnalysisContext context) {logger. info(" parsed to a data :{}", JSON.toJSONString(data)); dataList.add(data); } @override public void doAfterAllAnalysed(AnalysisContext context) {// Save the data here too, and make sure the last remaining data is also stored in the database logger. info(" All data parsed! "). ); } public List<Map<Integer, String>> getHeadList() { return headList; } public List<Map<Integer, String>> getDataList() { return dataList; }}Copy the code- Dynamically import utility classes
* write import tool class * / public class DynamicEasyExcelImportUtils {/ * * * dynamic access to all the columns and the data volume, * @param stream * @return */ public static List<Map<String,String>> parseExcelToView(byte[] stream) { return parseExcelToView(stream, 1); } @param stream excel file stream @param parseRowNumber specifies to read rows * @return */ public static List<Map<String,String>> parseExcelToView(byte[] stream, Integer parseRowNumber) { DynamicEasyExcelListener readListener = new DynamicEasyExcelListener(); EasyExcelFactory.read(new ByteArrayInputStream(stream)).registerReadListener(readListener).headRowNumber(parseRowNumber).sheet(0).doRead(); List<Map<Integer, String>> headList = readListener.getHeadList(); If (collectionUtils.isempty (headList)){throw new RuntimeException("Excel does not contain header "); } List<Map<Integer, String>> dataList = readListener.getDataList(); If (collectionutils.isempty (dataList)){throw new RuntimeException("Excel does not contain data "); Map<Integer, String> excelHeadIdxNameMap = headList.get(headlist.size () -1); List<Map<String,String>> excelDataList = Lists. NewArrayList (); for (Map<Integer, String> dataRow : dataList) { Map<String,String> rowData = new LinkedHashMap<>(); excelHeadIdxNameMap.entrySet().forEach(columnHead -> { rowData.put(columnHead.getValue(), dataRow.get(columnHead.getKey())); }); excelDataList.add(rowData); } return excelDataList; } /** * file import test * @param args * @throws IOException */ public static void main(String[] args) throws IOException { FileInputStream inputStream = new FileInputStream(new File("/Users/panzhi/Documents/easyexcel-export-user5.xlsx")); byte[] stream = IoUtils.toByteArray(inputStream); List<Map<String,String>> dataList = parseExcelToView(stream, 2); System.out.println(JSONArray.toJSONString(dataList)); inputStream.close(); }}Copy the codeTo facilitate subsequent operations, the column name is used as the key when parsing the data.
Third, summary
In the actual business development process, according to the parameters of dynamic implementation of Excel export import is very wide.
Of course, EasyExcel function is not only introduced above those contents, and based on the template for Excel filling, Web restful export export, use methods are roughly the same, more functions, will be introduced again in the subsequent article, if there is a description of the wrong place, welcome criticism and ridicule!
** For more information about Java technology, please follow me on wechat: docs.qq.com/doc/DQ2Z0eE… Remarks [666] I have prepared a first-line program necessary computer books, JAVA courseware, source code, installation package and other information I hope to help you improve technology and ability **