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

This article mainly introduces "how to export Excel quickly". In daily operation, I believe many people have doubts about how to export Excel quickly. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts of "how to export Excel quickly"! Next, please follow the editor to study!

Using third-party libraries to export Excel

There is a saying in the industry: do not repeat the wheel. The use of tool classes can reduce daily tedious coding, reduce repeated coding time, and improve development efficiency. As programmers, they should be more good at using tools to reduce code redundancy and beautify their own code.

Use the Hutool toollibrary to export Excel:1 and first add dependencies

Add dependencies on pom.xml:

Cn.hutool hutool-all 5.1.0 org.apache.poi poi-ooxml 4.1.0

In addition to the introduction of hutool dependency, poi-ooxml dependency is also introduced, which automatically associates the introduction of poi packages and supports the document format of Office2007+ well.

2. Then use the utility class to export Excelimport cn.hutool.core.collection.CollUtil;import cn.hutool.poi.excel.ExcelUtil;import cn.hutool.poi.excel.ExcelWriter;import cn.hutool.poi.excel.StyleSet;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.Font;import java.util.ArrayList;import java.util.Date;import java.util.LinkedHashMap;import java.util.List;import java.util.Map / * @ PACKAGE_NAME: com.lyl.excel * @ ClassName: HutoolExcelUtils * @ Description: use the utility class in Hutool to export Excel * @ Date: 2021-02-18 16:24 * @ Author: [Muzilei] official account * / public class HutoolExcelUtils {/ * Export Excel * * @ param args * / public static void main (String [] args) {ArrayList rows = CollUtil.newArrayList (data ()) ExcelWriter writer = null; try {String path = "E:/QQPCmgr/Desktop/"; String excelName = "Hutool" + System.currentTimeMillis () + ".xlsx"; / / create writer through utility class, fixed file output path writer = ExcelUtil.getWriter (path + excelName) / / define the first line merge cell style CellStyle headCellStyle = writer.getHeadCellStyle (); / / set the content font Font font = writer.createFont (); / / bold font.setBold (true); / / Font color font.setColor (Font.COLOR_RED) HeadCellStyle.setFont (font); / / sets the width of the cells in column 0, and the number of columns is calculated from zero to writer.setColumnWidth (0,20); writer.setColumnWidth (1,20); writer.setColumnWidth (2,20); / / defines the style of the data row StyleSet style = writer.getStyleSet () / / set the cell text content to automatically wrap style.setWrapText (); / / merge the header line after the cell (the first line), using the default header style writer.merge (rows.get (0). Size ()-1, "Export Test: TEST") / / write out the content at once and use the default style to force the output of the title writer.write (rows, true);} catch (Exception e) {e.printStackTrace ();} finally {if (writer! = null) {/ / remember to close writer and free memory writer.close () } / * construct exported data * * @ return * / public static List data () {/ / exported data ArrayList rows = new ArrayList (); for (int I = 0; I

< 10; i++) { Map row = new LinkedHashMap(); row.put("字符串标题", "字符串" + i); row.put("日期标题", new Date()); row.put("数字标题", 0.56); rows.add(row); } return rows; }} 注意： 记得修改代码中导出Excel的路径 path 导出Excel的样式是可以灵活变化的，可以自行进行设置 3、导出Excel的样式如下

4. Points for attention

Memory overflow may occur when exporting large amounts of data. Don't worry, Hutool also provides us with BigExcelWriter to avoid memory overflow problems that may occur when a large amount of data is exported.

In the above example, only part of the styling is implemented, and Hutool provides many other styles that you can try on your own; and Hutool also supports writing to the Web client for download.

Official document address: Hutool operation Excel

Use the EasyExcel tool library to export Excel:

EasyExcel is an Java-based simple, provincial memory read and write Excel Ali open source project; as it is introduced in the project code in GitHub: a fast, easy to avoid OOM java processing Excel tool.

Tests show that 64m of memory reads 75m (46W rows and 25 columns) of Excel in 1 minute; in addition, there is a fast mode that can be faster, but the memory footprint will be a little more than 100m.

1. Add dependency com.alibaba easyexcel 2.2.6 first

Note: this dependency cannot be used with poi-ooxml dependencies, otherwise the runtime will report exceptions that the class cannot find.

2. Then use the tool class to export Excel ① and export the data template class: import com.alibaba.excel.annotation.ExcelIgnore;import com.alibaba.excel.annotation.ExcelProperty;import com.alibaba.excel.annotation.write.style.ColumnWidth;import com.alibaba.excel.annotation.write.style.ContentFontStyle;import com.alibaba.excel.annotation.write.style.ContentRowHeight;import com.alibaba.excel.annotation.write.style.ContentStyle;import com.alibaba.excel.annotation.write.style.HeadFontStyle Import com.alibaba.excel.annotation.write.style.HeadRowHeight;import com.alibaba.excel.annotation.write.style.HeadStyle;import org.apache.poi.ss.usermodel.FillPatternType;import java.util.Date / * @ PACKAGE_NAME: com.lyl.excel * @ ClassName: DemoData * @ Description: data template when exporting data using EasyExcel * @ Date: 2021-01-27 17:46 * @ Author: [Muzilei] official account * / / the background of the title line is set to red IndexedColors.RED.getIndex () @ HeadStyle (fillPatternType = FillPatternType.SOLID_FOREGROUND) FillForegroundColor = 10) / / the header line font is set to 20@HeadFontStyle (fontHeightInPoints = 20) @ ContentRowHeight (25) / / the height of the text content line @ HeadRowHeight (30) / / the height of the header line @ ColumnWidth (20) / / the global column width public class DemoData {/ / the column content background of the string is set to sky blue IndexedColors.SKY_BLUE.getIndex () @ ContentStyle (fillPatternType = FillPatternType.SOLID_FOREGROUND FillForegroundColor = 40) / / the column content font of the string is set to 20 @ ContentFontStyle (fontHeightInPoints = 20) @ ExcelProperty ({"Export Test: TEST", "string title"}) private String string @ ColumnWidth (30) @ ExcelProperty ({"Export Test: TEST", "date title"}) private Date date; @ ExcelProperty ({"Export Test: TEST", "number title"}) private Double doubleData; / * ignore this field * / @ ExcelIgnore private String ignore; public String getString () {return string } public void setString (String string) {this.string = string;} public Date getDate () {return date;} public void setDate (Date date) {this.date = date;} public Double getDoubleData () {return doubleData;} public void setDoubleData (Double doubleData) {this.doubleData = doubleData;} public String getIgnore () {return ignore } public void setIgnore (String ignore) {this.ignore = ignore;}}

Note: a large number of custom annotations are used in this data template class, which can make the code more elegant and concise.

About the actual use of custom annotations in the project, please refer to: do you understand the charm of custom annotations?

②, implement data export to Excel:import com.alibaba.excel.EasyExcel;import java.util.ArrayList;import java.util.Date;import java.util.List / * @ PACKAGE_NAME: com.lyl.excel * @ ClassName: EasyExcelUtils * @ Description: Alibaba's open source easyexcel tool * @ Date: 2021-01-20 16:58 * @ Author: [Muzilei] official account * / public class EasyExcelUtils {public static void main (String [] args) {/ / the path to export Excel String path = "E:/QQPCmgr/Desktop/" / / Export Excel path + file name String filename = path + "EasyExcel" + System.currentTimeMillis () + ".xlsx" / * Export excel * filename: export excel full path * DemoData.class: data template when exporting excel * template: refers to the sheet page of the exported excel * data (): the data collection of the constructed DemoData.class data template * / EasyExcel.write (filename, DemoData.class) .sheet ("template") .doWrite (data ()) } / * construct exported data * * @ return * / private static 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; }} 注意： 记得修改代码中导出Excel的路径 path 导出Excel的样式是可以灵活变化的，可以自行进行设置 3、导出Excel的样式如下

4. EasyExcel exports Excel extension:

In the example in which Excel is exported above, only some of these functions are implemented, and there are many features that cannot be shown one by one due to space. Here are some other features that are supported:

Export only the specified column data in the template data by setting

Export the column data in the template data to the column specified in Excel by setting

The exported data can be imported into the same Excel in multiple batches to avoid memory overflow when there is a large amount of data.

Custom format conversion of exported data, such as log, digital format conversion, etc.

Support for exporting pictures to Excel

Support for exporting data to Excel based on existing Excel template styles

Supports cell merging, table export, automatic column widths, setting cell drop-down, hyperlinks, etc., and inserting annotations

In addition to the above export Excel function, EasyExcel also supports reading Excel data, uploading and downloading Web clients, etc.

Official document address: Ali Open Source EasyExcel

Project code address: alibaba/easyexcel

After the EasyExcel project code is pulled down, you can go directly to the unit test package to view the functional test usage provided:

At this point, the study on "how to export Excel quickly" 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.