Excel Export Common Component design

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;