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

This article mainly introduces "how to use Hutool Java tool class library _ ExcelUtil". In daily operation, I believe many people have doubts about how to use Hutool Java tool class library _ ExcelUtil. Xiaobian consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts about "how to use Hutool Java tool library _ ExcelUtil". Next, please follow the editor to study!

Hutool Java tool class library _ ExcelUtil depends on cn.hutool hutool-all 5.4.5 ExcelUtil

Read Excel from file to ExcelReader

ExcelReader reader = ExcelUtil.getReader (FileUtil.file ("test.xlsx"))

Read Excel from stream to ExcelReader

ExcelReader reader = ExcelUtil.getReader (ResourceUtil.getStream ("aaa.xlsx"))

Read the specified sheet

ExcelReader reader;// gets reader = ExcelUtil.getReader (FileUtil.file ("test.xlsx"), 0) by sheet number; / / gets reader = ExcelUtil.getReader (FileUtil.file ("test.xlsx"), "sheet1") by sheet name; ExcelReader

Read all rows and columns in Excel, represented by a list

ExcelReader reader = ExcelUtil.getReader ("d:/aaa.xlsx"); List readAll = reader.read ()

Read as Map list, default first behavior title row, key in Map is the title, and value is the cell value corresponding to the title

ExcelReader reader = ExcelUtil.getReader ("d:/aaa.xlsx"); List readAll = reader.readAll ()

Read as Bean list, the field in Bean is called title, and the field value is the cell value corresponding to the title

ExcelReader reader = ExcelUtil.getReader ("d:/aaa.xlsx"); List all = reader.readAll (Person.class); ExcelWriter

Hutool encapsulates Excel as ExcelWriter, which wraps the Workbook object. Every time you call the merge (merge cells) or write (write out data) method, you just write the data to Workbook instead of writing out the file. Only when you call the flush or close method will you really write out the file.

Due to mechanism reasons, the ExcelWriter object needs to be closed after writing out, and it can be closed by calling the close method. Only then will the Workbook object resources be released, otherwise the Workbook with data will always be resident in memory.

(1) write out row and column objects to Excel

List row1 = CollUtil.newArrayList ("aa", "bb", "cc", "dd"); List row2 = CollUtil.newArrayList ("aa1", "bb1", "cc1", "dd1"); List row3 = CollUtil.newArrayList ("aa2", "bb2", "cc2", "dd2"); List row4 = CollUtil.newArrayList ("aa3", "bb3", "cc3", "dd3") List row5 = CollUtil.newArrayList ("aa4", "bb4", "cc4", "dd4"); List rows = CollUtil.newArrayList (row1, row2, row3, row4, row5); / / create writerExcelWriter writer = ExcelUtil.getWriter ("d:/writeTest.xlsx") by utility class; / / create writer//ExcelWriter writer = new ExcelWriter ("d:/writeTest.xls") by constructor; / / skip the current line, which is not required, and demonstrate writer.passCurrentRow () here / / merge the header lines after the cell, using the default title style writer.merge (row1.size ()-1, "test title"); / / write out the content at one time and force the output of the title writer.write (rows, true); / / close writer and free memory writer.close ()

(2) write out Map data

Map row1 = new LinkedHashMap (); row1.put (name, Zhang San); row1.put (Age, 23); row1.put (score, 88.32); row1.put (pass, true); row1.put (date of examination, DateUtil.date ()); Map row2 = new LinkedHashMap (); row2.put (name, Li Si); row2.put (Age, 33); row2.put (score, 59.50) Row2.put ("pass", false); row2.put ("examination date", DateUtil.date ()); ArrayList rows = CollUtil.newArrayList (row1, row2); / / create writerExcelWriter writer = ExcelUtil.getWriter ("d:/writeMapTest.xlsx") through the tool class; / / merge the header row after the cell, using the default header style writer.merge (row1.size ()-1, "Class one report Card") / / write out the content at once and use the default style to force the output of the title writer.write (rows, true); / / close writer and free memory writer.close ()

(3) write out Bean data

Public class TestBean {private String name; private int age; private double score; private boolean isPass; private Date examDate; public String getName () {return name;} public void setName (String name) {this.name = name;} public int getAge () {return age;} public void setAge (int age) {this.age = age } public double getScore () {return score;} public void setScore (double score) {this.score = score;} public boolean isPass () {return isPass;} public void setPass (boolean isPass) {this.isPass = isPass;} public Date getExamDate () {return examDate;} public void setExamDate (Date examDate) {this.examDate = examDate } TestBean bean1 = new TestBean (); bean1.setName ("Zhang San"); bean1.setAge (22); bean1.setPass (true); bean1.setScore (66.30); bean1.setExamDate (DateUtil.date ()); TestBean bean2 = new TestBean (); bean2.setName (Li Si); bean2.setAge (28); bean2.setPass (false); bean2.setScore (38.50); bean2.setExamDate (DateUtil.date ()); List rows = CollUtil.newArrayList (bean1, bean2) / / create writerExcelWriter writer = ExcelUtil.getWriter ("d:/writeBeanTest.xlsx") through the tool class; / / merge the header lines after the cell, using the default title style writer.merge (4, "Class one report Card"); / / write out the content at one time, use the default style, and force the output of the title writer.write (rows, true); / / close writer and free memory writer.close ()

(4) Custom key alias for Bean (sort title)

When writing out the Bean, we can call the addHeaderAlias method of the ExcelWriter object to customize the alias of the key in the Bean, so that we can write a custom title

/ create writerExcelWriter writer = ExcelUtil.getWriter ("d:/writeBeanTest.xlsx") through the tool class; / / customize title aliases writer.addHeaderAlias ("name", "name"); writer.addHeaderAlias ("age", "age"); writer.addHeaderAlias ("score", "score"); writer.addHeaderAlias ("isPass", "pass"); writer.addHeaderAlias ("examDate", "exam time") / / merge the header line after the cell, use the default title style writer.merge (4, Class one report Card); / / write out the content at one time, use the default style, and force the output of the title writer.write (rows, true); / / close writer and free memory writer.close (); java parses Excel using the hutool utility class

Hutool package, really easy to use, convenient, highly recommended.

Https://hutool.cn/docs/index.html#/

Then use the tool class of Excel, and if you need to introduce the version of the poi-ooxml package, there is an error in it.

Just make sure for yourself.

Org.apache.poi poi-ooxml 3.17

One line of code is done: the read method is commonly used.

ExcelReader reader = ExcelUtil.getReader (FileUtil.file ("C:\\ Users\\ stack\\ Desktop\\ hanke\\ aaa.xlsx")

At this point, the study on "how to use the Hutool Java tool class library _ ExcelUtil" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.