[Wheel creation] to create a simple and omnipotent Excel read and write tool, wheel to create excel read and write

[Wheel creation] to create a simple and omnipotent Excel read and write tool, wheel to create excel read and write

Do you often have to read and write some simple Excel files at work or in normal times?

Shit !~ It's a big headache. It was a very practical task to do this because I had to brag about it. I 'd like to share it with you.

Tired of reading and writing simple types of Excel every time? Not afraid ~ Come, like stream operations? Lai ~, I like to use lambda (although java is quite cool ~), Lai ~ Look at this ~

Haha, if you are not using Java 8 ~~ No problem. You can use the old Anonymous class to implement these functions without interrupting your use of these functions ~~~

Update multiple times ~ I hope you can brainstorm ~

Github address: https://github.com/MatrixSeven/ExcelReads

Welcome to fork, welcome to Star, more comments or suggestions, and provide better ideas ~~

What is ExcelReads?

• This is a general and simple Excel reader.
• Supports custom an object reading and automatic HashMap reading.
• Support for custom extensions
• Allows you to customize the Sheet range and start the number of data rows.
• Supports direct export of Excel (Map/Object) through database queries)
• Supports regular expression filtering.
• The JavaBean object supports regular rule validation using annotations, And the HashMap supports array rule validation.
• Dependency on POI, built using Maven

Updated record

• Fixed the issue of recursive stack-over-stack Export (Object) for database queries.
• Add a new xxx. Class Definition type to export, making the operation easier
• Export annotation support (you can use the seven. savewapper. anno. ExcelAnno annotation)

Update

• Fixed the problem of export (Map) NULL pointer and subscript out-of-bounds for database queries.
• Fixed the problem of empty rows after the data is filtered.
• The operation after the Result parameter is dropped is the same as the operation of Map/Object.

Update

• Added ResExprotDBMap & ResExprotDBObj to support Database Export.
• Allows you to export data based on database queries (Map) and directly add the Result object.
• Supports database-Based Query-based export (Object), which can be directly placed into the Result Object.

Update

• Added simple data types to generate xls/xlsx
• Write directly to the an type and name the Annotation
• Supports write of Map key-value Type
• Supports filtering, processing, sorting, and other writing methods like reading.

Update

• Add CreateMap By Key
• Remove useless generics
• Added xlsx support

Update

• Supports data filtering and processing as conversion, based on the Event Mode
• Update to Build mode to create an instance
• Added data filtering, data conversion, and data sorting callback interfaces.
• Using the chain set method

Others

• Custom read supports a simple standard data format, that is, a typical Header Format.
• Extends WapperMap and WapperObj.
• Use ExcelFactory. getBeans directly for retrieval, and add the generic type to WapperObj.
• Annotation Value corresponds to the column title, and Required corresponds to the regular expression. You can write the regular expression yourself or directly use RegHelper
• Entity bean data is slower than hashMap, 800 ms is slower than million data records, and regular expressions are added to slow down (regular expressions are added to the test)

Usage instructions

Title 1	Title 2	Title 3
Foo	Foo	Foo
Bar	Bar	Bar
Baz	Baz	Baz

Write the custom Bean type exported from the database (xxx. Class type)

1 ExcelFactory.saveExcel(2         UNPOOLED_DATA_SOURCE.getConnection().3                 prepareStatement("select * FROM  users_info limit 1000").executeQuery(),4         "\u5317\u4eac__Excel.xlsx",5          AS.class)6     .FilterCol(() -> new String[]{"updatetime"})7     .Filter((AS o) ->o.getA().length() > 3)8     .Save();

 

Writing custom Bean types exported from databases (self-packaging)

ExcelFactory.saveExcel(        UNPOOLED_DATA_SOURCE.getConnection().                prepareStatement("select * FROM  users_info limit 1000").executeQuery(),        "\u5317\u4eac__Excel.xlsx",            res -> {                AS a = new AS();                a.setA(res.getString("name"));                return a;            })    .FilterCol(() -> new String[]{"updatetime"})    .Filter((AS o) ->o.getA().length() > 3)    .Save();

 

Example of exporting a database directly to Excel

ExcelFactory. saveExcel (UNPOOLED_DATA_SOURCE.getConnection (). prepareStatement ("select * FROM users_info limit 10000 "). executeQuery (), "excel.xlsx") // filter fields. filterCol ()-> new String [] {"updatetime"}) // filter data conditions. filter (HashMap <String, String> o)-> o. get ("address "). equals ("\ u5317 \ u4eac ")). save ();

 

Example of exporting custom types to Excel

List <A> aa = new ArrayList <> (); aa. add (new A ("a", "B"); aa. add (new A ("aa", "bb"); ExcelFactory. saveExcel (aa, System. getProperty ("user. dir "). concat ("\ Save.xlsx") // You can process each row of data. process (A a)->. setA ("xxxxxxx") // filter columns. filterCol ()-> new String [] {"B"}) // discarded when processing data based on a field. filter (A a)->. getA (). length ()> 1) // sort. sort (A o1, A o2)-> o1.getAge ()> o2.getAge ()? 1: o1.getAge () = o2.getAge ()? 0:-1). Save ();

 

Example of reading Excel to Map

1 List <Map <String, String> data = ExcelFactory. getBeans (System. getProperty ("user. dir "). concat ("\ test .xls"), 2 new ResWrapperMap () {3 @ Override // configure the Excel attribute 4 protected void LoadConfig (Config config) {5 config. setContent_row_start (3); 6 config. setTitle_row (2); 7} 8 }). // Process each row of Data 9 Process (HashMap <String, String> o)-> System. out. println (o + "\ n") 10 // filter a column 11 during processing ). filterCol ()-> new String [] { } 12 // You can select this row based on the content of a column in a row. 13 ). filter (HashMap <String, String> o)-> o. get ("creator ")! = Null & o. get ("creator "). length ()> 514 // sorting 15 ). sort (o1, o2)-> o1.hashCode ()> o2.hashCode ()? 1: hashCode () = o2.hashCode ()? 0:-1). Create (); 16 17 // use. CreateMap (key_v) to generate Map <Key, Map> type data

 

Example of reading Excel to custom type

1 Map <String, Seven> map = new ResWrapperObj (Seven) {2 @ Override 3 protected void LoadConfig (Config config) {4 config. setContent_row_start (3); 5 config. setTitle_row (2); 6} 7 }). 8 Process (HashMap <String, String> o)->{} 9 ). filterCol ()-> new String [] {} 10 ). filter (HashMap <String, String> o)-> o. get ("creator ")! = 11 null & o. get ("creator"). length ()> 4). <Map> CreateMap ("creator "));

 

 

• Mail (hacker.kill07@gmail.com)
• QQ: 985390927
• Weibo: @ Alden _ emotion control
• Blog: http://sweets.cf