auto-excel

A fast import and export tool for Excel.

License

License

Categories

Categories

Net Auto Application Layer Libs Code Generators
GroupId

GroupId

net.fenghaitao
ArtifactId

ArtifactId

auto-excel
Last Version

Last Version

2.0.0
Release Date

Release Date

Type

Type

jar
Description

Description

auto-excel
A fast import and export tool for Excel.
Project URL

Project URL

https://github.com/feng-haitao/auto-excel
Source Code Management

Source Code Management

https://github.com/feng-haitao/auto-excel

Download auto-excel

How to add to project

<!-- https://jarcasting.com/artifacts/net.fenghaitao/auto-excel/ -->
<dependency>
    <groupId>net.fenghaitao</groupId>
    <artifactId>auto-excel</artifactId>
    <version>2.0.0</version>
</dependency>
// https://jarcasting.com/artifacts/net.fenghaitao/auto-excel/
implementation 'net.fenghaitao:auto-excel:2.0.0'
// https://jarcasting.com/artifacts/net.fenghaitao/auto-excel/
implementation ("net.fenghaitao:auto-excel:2.0.0")
'net.fenghaitao:auto-excel:jar:2.0.0'
<dependency org="net.fenghaitao" name="auto-excel" rev="2.0.0">
  <artifact name="auto-excel" type="jar" />
</dependency>
@Grapes(
@Grab(group='net.fenghaitao', module='auto-excel', version='2.0.0')
)
libraryDependencies += "net.fenghaitao" % "auto-excel" % "2.0.0"
[net.fenghaitao/auto-excel "2.0.0"]

Dependencies

compile (2)

Group / Artifact Type Version
org.apache.poi : poi jar 4.1.2
org.apache.poi : poi-ooxml jar 4.1.2

provided (1)

Group / Artifact Type Version
org.projectlombok : lombok jar 1.18.8

test (2)

Group / Artifact Type Version
junit : junit jar 4.13
org.springframework : spring-core jar 5.1.18.RELEASE

Project Modules

There are no modules declared in this project.

中文 | English | Documentation

Why AutoExcel?

Excel import and export is very common in software development, as long as you are a programmer, you have met. I believe that many people will choose to use Apache POI to complete this work like me. While feeling the power of POI, my team also encountered the following problems:

  1. Directly use POI to operate Excel will generate a lot of hard code, you will hardly write row index and column index in the code.
  2. A large number of non-reusable format control codes, such as background color, alignment, cell style, etc.
  3. The implementation consultant clearly provided a ready-made template, but had to develop the code to implement it again, resulting in low development efficiency.
  4. Development resources have to be used when the template is adjusted.
  5. Simple export also requires specific code.

AutoExcel solves the above problems. It is very simple and only requires a small amount of code to complete complex import and export. When using it, programmers have no sense of import and export, that is, there is no need to directly manipulate POI. At the same time, the implementation consultant provides Excel is the import and export template, unless new data sources or fields are added, the template update does not need to use development resources.

AutoExcel does not over-encapsulate the POI, but makes full use of Excel's own feature-the name manager, through some tricks, the cell and the data source are mapped, thereby decoupling the programmer and the POI, and avoid hard code, so that import and export work becomes enjoyable and no longer boring.

Features

  • Export with template
    • Support multiple sheets
    • Support basic object and table data
    • A single sheet supports multiple data sources of variable length
    • Support horizontal filling of data
    • Automatically apply cell style
    • Auto fill in line number
    • Auto fill formula
    • Automatic summary
  • Export directly
    • Support multiple sheets
    • Export with basic style
    • Automatically adjust column width
  • Import
    • Support multiple sheets
    • Automatic data type conversion
  • Support millions of data import and export in seconds

Function preview

Before export After export
image image
image image
image image
image image

To achieve all of the above exports, you only need to write the following small amount of code (you need additional code to prepare the data source, for example, from the database. In the following example, use DataGenerator to generate demo data)

// Set export parameters, such as data source name, data source, etc.
List<TemplateExportPara> paras = new ArrayList<>();
paras.add(new TemplateExportPara("BusinessUnit", DataGenerator.genBusinessUnit()));
paras.add(new TemplateExportPara("Contract", DataGenerator.genContracts()));
paras.add(new TemplateExportPara("Project", DataGenerator.genProjects(1)));

List<Product> products = DataGenerator.genProducts(1);
TemplateExportPara para3 = new TemplateExportPara("Product", products);
// When a single sheet has multiple data sources, the data source above should be set to inserted
para3.setInserted(true);
paras.add(para3);

TemplateExportPara para5 = new TemplateExportPara("Product2", products);
// Horizontal fill
para5.setDataDirection(DataDirection.Right);
paras.add(para5);

// (Optional operation) Remove unnecessary sheets
ExcelSetting excelSetting = new ExcelSetting();
excelSetting.setRemovedSheets(Arrays.asList("will be removed"));

AutoExcel.save(this.getClass().getResource("/template/Export.xlsx").getPath(),
               this.getClass().getResource("/").getPath() + "AutoExcel.xlsx",
               paras,
               excelSetting);

Million data test

Unit: ms

10W rows and 10 columns of data 100W rows and 10 columns of data
Export with template 6,258 23,540
Export directly 5,711 24,952
Import 4,466 21,595
Import + Type conversion 4,823 26,279

Maven

<dependency>
  <groupId>net.fenghaitao</groupId>
  <artifactId>auto-excel</artifactId>
  <version>2.0.0</version>
</dependency>

For more functions, please go to Documentation.

Versions

Version
2.0.0