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

This article introduces the knowledge of "how java manipulates excel tables". Many people will encounter such a dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Catalogue

Working with scen

What is easyExcel?

Write to the table

1. Introduce dependency

2. Create the entity class corresponding to the table first.

3. Generate a table

Read the table

1. Entity classes are the same as above.

2. Analysis

3. Testing

Working with scen

1. Export user information to excel table (export data.)

2. Input the information in the Excel table into the website database (upload exercises.) greatly reduce the amount of website input! Excel processing is often designed in development, such as exporting Excel and importing Excel into the database!

At present, the popular operation of Excel is Apache POI and Alibaba's easyExcel!

Here we mainly use easyExcel for operation.

What is easyExcel?

EasyExcel is a simple, provincial memory read and write Excel open source project based on Java. Support reading and writing Excel of 100 megabytes while saving memory as much as possible.

Official website: EasyExcel Sparrow (yuque.com)

An excel table consists of workbooks, worksheets, rows, and columns.

Write to the table

1 、 Introduce dependency com.alibaba easyexcel 2.2.0-beta2 com.alibaba fastjson 1.2.54 org.projectlombok lombok 1.18.20 Joda-time joda-time 2.10.10 junit junit 4.13.2 2 、 First create the entity class @ Datapublic class DemoData {@ ExcelProperty ("string title") private String string corresponding to the table @ ExcelProperty ("date title") private Date date; @ ExcelProperty ("number title") private Double doubleData; / * ignore this field * / @ ExcelIgnore private String ignore;} 3, generate the table private List data () {List list = new ArrayList (); for (int I = 0; I)

< 10; i++) { DemoData data = new DemoData(); data.setString("字符串" + i); data.setDate(new Date()); data.setDoubleData(0.56); list.add(data); } return list; } //根据list 写入excel @Test public void simpleWrite() { String path = "D:\\qdSystem\\Resources\\下载\\"; // 写法1 String fileName = path + "qiadnu.xlsx"; // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭 EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data()); }读取表格1、实体类 同上。2、解析// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去public class DemoDataListener extends AnalysisEventListener { private static final Logger LOGGER = LoggerFactory.getLogger(DemoDataListener.class); /** * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收 */ private static final int BATCH_COUNT = 5; List list = new ArrayList(); /** * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。 */ private DemoDAO demoDAO; public DemoDataListener() { // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数 demoDAO = new DemoDAO(); } /** * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来 * * @param demoDAO */ public DemoDataListener(DemoDAO demoDAO) { this.demoDAO = demoDAO; } /** * 这个每一条数据解析都会来调用 * @param context */ @Override public void invoke(DemoData data, AnalysisContext context) { LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data)); list.add(data); // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM if (list.size() >

= BATCH_COUNT) {saveData (); / / Storage completes cleaning list list.clear () }} / * all data parsing will be called * * @ param context * / @ Override public void doAfterAllAnalysed (AnalysisContext context) {/ / the data is also saved here to ensure that the final remaining data is also stored in the database saveData (); LOGGER.info ("all data parsing is complete!") ;} / * plus storage database * / private void saveData () {LOGGER.info ("{} pieces of data, start storing the database!" , list.size (); demoDAO.save (list); LOGGER.info ("Storage database successful!") ;}}

Dao layer

/ * assume this is your DAO store. Of course, this class is managed by spring, and of course you don't need storage, and you don't need this class. * * / public class DemoDAO {public void save (List list) {/ / if it is mybatis, try not to call insert directly for many times, write a mapper and add a method batchInsert, insert all data at one time}} 3, test @ Test public void simpleRead () {/ / there is a very important point that DemoDataListener cannot be managed by spring You need to new every time you read excel, and then you can use spring to input / / write 1: String fileName = path + "qiadnu.xlsx" by construction method. / / you need to specify which class to read, and then reading the first sheet file stream will automatically close EasyExcel.read (fileName, DemoData.class, new DemoDataListener ()). Sheet (). DoRead ();} "how java manipulates the excel table" ends here, thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.