Implement an Excel export public component yourself .... The article is mainly divided into the following sections to explain:
1. Clear target
2. Technical Advance Research
3, technical scheme selection and project design
4. Technology implementation
The following is the goal of implementing an Excel Export module:
Resolve Excel formats that need to be exported through custom annotations
Support Custom Date display formats
Support for displaying self-added columns
Enumeration conversions that support the agreed properties (for example, Ywg-> completed)
A small amount of data is exported directly (5,000 article)
Support paging export for large data volumes (full data export support, millions)
Ultra-large data export (Tens data volume)
After the goal is clear, can begin to carry on the technical advance research, compared with the current more popular three kinds of open source Excel export technology characteristic:
Jxl
Low efficiency and simple operation
Ability to modify cell properties, format support not as powerful as poi
Fastexcel
Excel 97-2003 file format supported in Excel file reading and writing components with pure Java development
Low memory consumption
Only character information is read, such as color/font properties are not supported
Poi
High efficiency, relatively complex operation
Support for formulas, macros, images, graphs
Supports cosmetic cell properties; Support for fonts, numbers, date operations
The 3.8 version of the POI came out of the Sxssfworkbook, can support large amount of data operations, but only support xlsx format
Research on Excel Export methods:
An Excel file that directly exports. xls (65532 lines supported on one table) or. xlsx (single table supports 1048576 rows) suffix
Export A. txt file
Export A. csv file
Exporting. xml files
Technology selection:
Integration of the previous open source technology analysis and analysis of export methods, combined with their own business scenarios, direct export Excel is a better choice (users can directly open). In open source technology, JXL can be directly excluded, because the efficiency is low; Fastexcel is actually good, memory consumption is small, but does not support complex operations, poor scalability, and poi not only high efficiency, and support a variety of operations (for future unknown expansion requirements, such as the need to export tables, images.) , but also support the operation of large amount of data, very suitable. Excel2007 has been popularized, there is no barrier to open.
Technical Solution Design:
Using POI as the third party library for manipulating Excel
Custom annotations, with column numbers, column names, date formatting, enumeration types, self-adding sequences
Annotation parsing, parsing Excel exported columns and corresponding fields
Data parsing, parsing the results of the annotation to parse each row of data
Export
If the amount of data is small (<5000), direct total quantity is exported
If the amount of data is large (range [5000,1048576)), you need to parse the data in batches and then export
If the data volume is oversized, exceeds the Excel2007 table row number limit (1048576), uses the export many copies Excel or many Zhang sheet the way
Defines a custom annotation that consists mainly of the following attributes (serial number, column name, date format, enumeration name, and whether it is self added)
Package com.shulin.winter.annotions;
Import java.lang.annotation.*;
/** * Excel Export Note * Created by Shulin on 16/12/25. */@Target ({Elementtype.field, elementtype.type}) @Retention (retentionpolicy.runtime) @Do cumented public @interface exlout { /** * Column serial number, such as 1th column is name, 2nd column is electricity Phone number, 3rd is address * * @return /
int Colseq ();
/** * Column name * * @return
* string colname ();
/** * Date type format * * @return * * &NBsp
string DateFormat () default "Yyyy-mm-dd HH:mm:SS";
/** * Enum name * * @return
* string enumname () default "";
/** * Is there a self-adding sequence * * @return
/ boolean AutoIncrement () default false;