Shulou(Shulou.com)06/01 Report--

This article mainly shows you "how to use the EasyExcel tool class in Java", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "how to use the EasyExcel tool class in Java" this article.

I. Preface

With regard to EasyExcel, it further encapsulates poi, making the whole writing process more object-oriented. Advantages, I think the process is clearer, easier to understand, better readability, the downside is that the operation is not as flexible as the original way.

II. Import

StudentVo is an entity class. Note that each attribute in the entity should correspond to excel in one-to-one order. It is recommended to use the String type. When it is really inserted, it will be converted.

The ImportExcelListener class is the class that actually handles data.

CommonService is just a service bean of Spring to perform curd operations

Private CommonService commonService; public void importExcel (MultipartFile file) throws IOException {/ / HEAD_ROW_NUMBER is the number of header rows in Excel. The HEAD_ROW_NUMBER parameter in the construction method of ImportExcelListener here can decide whether to pass EasyExcel.read (file.getInputStream (), StudentVo.class, new ImportExcelListener (HEAD_ROW_NUMBER, commonService)). Sheet (). HeadRowNumber (HEAD_ROW_NUMBER). DoRead ();}

The ImportExcelListener class is as follows, and generics can be avoided here according to the actual needs.

Public class ImportExcelListener extends AnalysisEventListener {/ * stores the database every BATCH_COUNT and then cleans up the list to facilitate memory recovery * / private static final int BATCH_COUNT = 500; private int headRowNum; private CommonService commonService; private List list = new ArrayList (); public ImportExcelListener (int headRowNumber, CommonService commonService) {this.headRowNum = headRowNumber This.commonService = commonService;} / * every data parsing will call * / @ Override public void invoke (T data, AnalysisContext context) {/ / here you can also convert data to the corresponding entity, and then do some parameter verification Integer rowNum = context.readRowHolder (). GetRowIndex () Log.info ("currently parsed data is row {}", rowNum); list.add (data); / / batch save to prevent excessive one-time data, memory overflow if (list.size () > = BATCH_COUNT) {/ / save commonService.save (list) List.clear ();}} / / after parsing, also remember to determine whether it is empty. If it is not empty, save @ Override public void doAfterAllAnalysed (AnalysisContext context) {if (CollectionUtils.isEmpty (list)) {return;} commonService.save (list). List.clear ();}}

If there is no special requirement for the imported data and the amount of data is small, you can also use the following method directly:

List data = null; try {data = EasyExcelFactory.read (file.getInputStream ()). Sheet (). DoReadSync ();} catch (IOException e) {log.error (read failed, e); throw new BusinessException (read failed);} / / Save operation / /. III. Export

There is no need to map entity objects. After finding out the data in practical application, you need to traverse and assemble it into List > type, two-dimensional data, representing the rows and columns, and setting the header content List titleList = new ArrayList (); titleList.add ("name", "key", "value"); int sheetNum = 0 WriteSheet writeSheet = EasyExcel.writerSheet (sheetNum, "Export data"). Build (); excelWriter.write (titleList, writeSheet); / / generate exported content; List content = new ArrayList (); / / 10 rows and 3 columns of for (int I = 0; I < 10; iTunes +) {List dataList = new ArrayList (); for (int j = 0; j < 3 DataList.add +) {dataList.add (j);} content.add (dataList);} / / write file excelWriter.write (content, writeSheet); / / close file stream excelWriter.finish ();}

Map the entity to the corresponding column through the comments provided by EasyExcel:

@ Data public class VO {@ ExcelProperty (value = "name", index = 0) int name; @ ExcelProperty (value = "key", index = 1) int key; @ ExcelProperty (value = "value", index = 2) int value;} @ Test public void test () throws FileNotFoundException {File file = new File ("E:\ work-grandview\ test .xlsx") / / create writerBuilder object, automatically adjust column width ExcelWriter excelWriter = EasyExcel.write (new FileOutputStream (file)) .registerWriteHandler (new LongestMatchColumnWidthStyleStrategy ()) .autoCloseStream (Boolean.TRUE) .build (); / / generate export content; List content = new ArrayList (); / / 10 rows and 3 columns for (int I = 0; I < 10) VO vo +) {VO vo = new VO (); vo.setKey (I); vo.setName (I); vo.setValue (I); content.add (vo) } / / create sheet, set table no. 0. In practice, you may have to create multiple sheets, which can traverse data. Every time n entries are satisfied, create table I sheet WriteSheet writeSheet = EasyExcel.writerSheet (0, "export data"). Build (); / / set header writeSheet.setClazz (content.get (0). GetClass ()) / / write to file excelWriter.write (content, writeSheet); excelWriter.finish ();}

After the first creation, there was a strange addition of a this0 column, and then I thought that my physical class was the inner class of the T e s t class, and'c o n t e n t. G e t (0). G e t C l a s s () 'takes an agent class T e s t 0 column, which is treated as an attribute value when the header is set. Resolved after writing the VO class to the outside.

The above is all the content of the article "how to use the EasyExcel tool class in Java". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.