Excel Plus

https://hellokaton.github.io/excel-plus/
Admin
Excel Plus is a Java library that simplifies reading and writing Excel files by providing a clean,...
Tokens:4,009
Snippets:47
Trust Score:9.7
Update:5 months ago
Show doc for...
Tokens:
Raw
### Start Writing from a Specific Row

Source: https://hellokaton.github.io/excel-plus

Use the `start` method to specify the row index from which writing should begin. This is an alternative to the default behavior which calculates the starting row based on whether a title is set.

```java
Writer.create().start(4);
```

--------------------------------

### Start Writing from a Specific Row

Source: https://hellokaton.github.io/excel-plus

This option allows you to specify the starting row index for writing data to an Excel sheet. It's generally recommended to let the library determine this based on whether a title is set, but this provides manual control.

```APIDOC
## From a Specified Row
This option is not recommended for general use. By default, it's determined by whether a title is set. If you wish to leave some rows blank in the middle before writing, you can set this.

```java
Writer.create().start(4);
```

This will start writing from the row with index 4.
```

--------------------------------

### Set Starting Row for Reading

Source: https://hellokaton.github.io/excel-plus

Configures the reader to start reading data from a specific row index. Useful when the Excel file has headers or titles.

```java
Reader.create(Sample.class)
      .from(new File("SampleData.xlsx"))
      .start(0)
```

```java
Reader.create(Sample.class)
      .from(new File("SampleData.xlsx"))
      .start(1)
```

--------------------------------

### Read Excel Data into Java Objects

Source: https://hellokaton.github.io/excel-plus

Reads data from an Excel file into a list of Java objects. Specify the target class, file path, sheet name, and starting row.

```java
List<Sample> samples = Reader.create(Sample.class)
                .from(new File("SampleData.xlsx"))
                .sheet("SalesOrders")
                .start(1)
                .asList();
```

--------------------------------

### Download Excel via Servlet

Source: https://hellokaton.github.io/excel-plus

Easily output query data directly to the browser for download using a Servlet. Pass the `HttpServletResponse` object and the desired filename to the `ResponseWrapper.createXLSX` method.

```java
Writer.create()
      ...
      .to(ResponseWrapper.createXLSX(servletResponse, "xxx表格.xlsx"))
```

--------------------------------

### Download via Servlet

Source: https://hellokaton.github.io/excel-plus

Easily output query data directly to the browser for download with a single line of code. This is convenient for servlet-based applications.

```APIDOC
## Download via Servlet
For convenience, we've added a feature to easily output query data directly to the browser for download with just one line of code. If you are using a servlet-based application, you can use the following method.

```java
Writer.create()
      ...
      .to(ResponseWrapper.createXLSX(servletResponse, "xxxTable.xlsx"))
```

Simply pass the `HttpServletResponse` object and the desired file name for the export. The rest is handled automatically.
```

--------------------------------

### Export Using a Template

Source: https://hellokaton.github.io/excel-plus

Utilize a pre-designed Excel template for complex layouts. The program fills in data into the template, ensuring a polished and consistent appearance. The template file should be located in the classpath.

```java
Writer.create()
        .withTemplate(classPath() + "/template.xls")
        .withRows(buildData())
        .to(new File(fileName));
```

--------------------------------

### Export Using a Template

Source: https://hellokaton.github.io/excel-plus

Create complex Excel layouts by using a pre-designed template. The program fills in data into this template, ensuring a polished and consistent look for your exported files.

```APIDOC
## Export Using a Template
Sometimes, the Excel sheet layout needs to be complex. You can pre-design a template Excel file, leave the data fields empty, and then have the program populate it with data before exporting. This satisfies aesthetic requirements.

```java
Writer.create()
        .withTemplate(classPath() + "/template.xls")
        .withRows(buildData())
        .to(new File(fileName));
```

> Note: The `template.xls` file should be located in the `classpath`.
```

--------------------------------

### Create Excel Writer

Source: https://hellokaton.github.io/excel-plus

Creates a default Excel writer instance for writing XLSX files.

```java
Writer.create();
```

--------------------------------

### Add Maven Snapshot Dependency

Source: https://hellokaton.github.io/excel-plus

Use this configuration to include snapshot versions of the Excel Plus library for the latest updates. Remember to check the GitHub README for the most current version.

```xml
<repositories>
    <repository>
        <id>snapshots-repo</id>
        <url>https://oss.sonatype.org/content/repositories/snapshots</url>
        <releases>
            <enabled>false</enabled>
        </releases>
        <snapshots>
            <enabled>true</enabled>
        </snapshots>
    </repository>
</repositories>

<dependency>
    <groupId>io.github.biezhi</groupId>
    <artifactId>excel-plus</artifactId>
    <version>1.0.8-SNAPSHOT</version>
</dependency>
```

--------------------------------

### Create Excel Reader with Factory Method

Source: https://hellokaton.github.io/excel-plus

Instantiates an Excel reader using a factory method, specifying the Java class that maps to Excel rows.

```java
Reader.create(Sample.class);
```

--------------------------------

### Add Maven Dependency for Excel Plus

Source: https://hellokaton.github.io/excel-plus

Include this Maven dependency in your pom.xml to use the Excel Plus library. Ensure compatibility with your project's POI version.

```xml
<dependency>
    <groupId>io.github.biezhi</groupId>
    <artifactId>excel-plus</artifactId>
    <version>1.0.8</version>
</dependency>
```

--------------------------------

### @ExcelColumn Annotation

Source: https://hellokaton.github.io/excel-plus

Configuration for reading and writing Excel documents using annotations.

```APIDOC
## Annotation Usage
Use annotations to configure how Excel documents are read and written.
**@ExcelColumn Annotation**
| Option        | Default Value | Description                                                                 |
|---------------|---------------|-----------------------------------------------------------------------------|
| `index`       | `-1`          | Column index in Excel, starting from 0. Applicable for reading or writing. |
| `title`       | `""`          | Column name when exporting Excel, e.g., Status, Name, Phone Number.         |
| `datePattern` | `""`          | Date formatting `pattern`. Effective for `Date`, `LocalDate`, `LocalDateTime`. |
| `converter`   | `NullConverter.class` | Data type conversion class. Implements the `Converter` interface. Requires a no-argument constructor. |
| `width`       | `-1`          | Column width when exporting to Excel. Recommended to set based on `character count * 256`. |
```

--------------------------------

### Write Java Objects to Excel File

Source: https://hellokaton.github.io/excel-plus

Writes a list of Java objects to a new Excel file. Includes an optional header title that spans the first row.

```java
List<Sample> samples = new ArrayList<>();
// 这里的数据需自行准备
Writer.create()
        .headerTitle("一份简单的Excel表格")
        .withRows(samples)
        .to(new File("sample_test.xlsx"));
```

--------------------------------

### Converter API

Source: https://hellokaton.github.io/excel-plus

Interface for data type conversion.

```APIDOC
## Converter
* `Converter`: Top-level interface for data type conversion, handling custom read and write rules.
```

--------------------------------

### Create Writer for Specific Excel Type

Source: https://hellokaton.github.io/excel-plus

Creates a writer instance for a specific Excel file type (XLSX, XLS, or CSV).

```java
Writer.create(ExcelType.XLS);
```

```java
Writer.create(ExcelType.CSV);
```

--------------------------------

### Writer API

Source: https://hellokaton.github.io/excel-plus

API for writing Excel documents.

```APIDOC
## Writer
* `Writer(ExcelType)`: Constructor to specify the Excel file type (supports XLSX, XLS, CSV).
* `withRows(Collection)`: Provides the data to be written as a collection.
* `sheet(String)`: Sets the name of the sheet to write to (defaults to `Sheet0`).
* `startRow(int)`: Sets the starting row index for writing (0-indexed, defaults to calculated, recommended not to set).
* `headerTitle(String)`: Optional sheet title.
* `titleStyle(BiConsumer)`: Customizes the title style.
* `headerStyle(BiConsumer)`: Customizes the header style.
* `cellStyle(BiConsumer)`: Customizes the style for cells within rows.
* `withTemplate(File)`: Creates an Excel file based on a template file.
* `bufferSize(int)`: Buffer size for writing XLSX files (defaults to 100, recommended not to change).
* `withRaw()`: Enables custom row writing; when enabled, Excel is not written based on collection data.
* `createRow(int)`: Use this API when `withRaw` is enabled to customize the creation of `Row` and `Cell` objects.
* `isAppend(boolean)`: Defaults to false. Set to true to append to the file instead of overwriting.
* `to(File)`: Writes the Excel document to a file.
* `to(OutputStream)`: Writes the Excel document to an OutputStream.
```

--------------------------------

### Define Java Model for Excel Rows

Source: https://hellokaton.github.io/excel-plus

Use the @ExcelColumn annotation to map Java fields to Excel columns by index. Supports various data types and date formatting.

```java
public class Sample {

    @ExcelColumn(index = 0, datePattern = "M/d/yy")
    private LocalDate date;

    @ExcelColumn(index = 1)
    private String location;

    @ExcelColumn(index = 4)
    private int proportion;

    @ExcelColumn(index = 5)
    private double ss;

    @ExcelColumn(index = 6)
    private BigDecimal amount;
    
    // getter setter 省略
}
```

--------------------------------

### Set Header Title for Excel

Source: https://hellokaton.github.io/excel-plus

Adds a custom header title to the Excel document, which will be displayed in the first row and automatically merged.

```java
Writer.create().headerTitle("2018 年 5 月 书籍 TOP 10");
```

--------------------------------

### Customize Excel Styles

Source: https://hellokaton.github.io/excel-plus

Apply custom styles to the header title, column headers, and cells. This allows for fine-grained control over font size, color, and alignment. Refer to POI documentation for advanced styling.

```java
Writer.create()
        .headerTitle("一份自定义样式的Excel表格")
        .withRows(buildData())
        .titleStyle((wb, style) -> {
            Font font = wb.createFont();
            font.setFontHeightInPoints((short) 40);
            font.setColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
            style.setFont(font);
        })
        .headerStyle((wb, style) -> {
            Font font = wb.createFont();
            font.setFontHeightInPoints((short) 20);
            font.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
            style.setFont(font);
        })
        .cellStyle((wb, style) -> {
            Font font = wb.createFont();
            font.setFontHeightInPoints((short) 20);
            font.setColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
            style.setFont(font);
        })
        .to(new File(fileName));
```

--------------------------------

### Read Specific Sheet by Name

Source: https://hellokaton.github.io/excel-plus

Reads data from a specific sheet in an Excel file by providing its name.

```java
Reader.create(Sample.class)
      .from(new File("SampleData.xlsx"))
      .sheet("sheetName")
```

--------------------------------

### Customizing Write Styles

Source: https://hellokaton.github.io/excel-plus

Customize the style of the header and cells in your Excel sheet. This is useful for setting font size, color, alignment, and other visual aspects.

```APIDOC
## Custom Write Styles
In most cases, you won't need to set styles. `excel-plus` provides APIs for setting header and column styles. For specific needs like font size, color, or centering, you can use the following code. If you are unfamiliar with style manipulation, refer to the POI column setting documentation.

```java
Writer.create()
        .headerTitle("A Custom Styled Excel Sheet")
        .withRows(buildData())
        .titleStyle((wb, style) -> {
            Font font = wb.createFont();
            font.setFontHeightInPoints((short) 40);
            font.setColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
            style.setFont(font);
        })
        .headerStyle((wb, style) -> {
            Font font = wb.createFont();
            font.setFontHeightInPoints((short) 20);
            font.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
            style.setFont(font);
        })
        .cellStyle((wb, style) -> {
            Font font = wb.createFont();
            font.setFontHeightInPoints((short) 20);
            font.setColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
            style.setFont(font);
        })
        .to(new File(fileName));
```
```

--------------------------------

### Read Specific Sheet by Index

Source: https://hellokaton.github.io/excel-plus

Reads data from a specific sheet in an Excel file using its index. Defaults to the first sheet (index 0) if not specified.

```java
Reader.create(Sample.class)
      .from(new File("SampleData.xlsx"))
      .sheet(1)
```

--------------------------------

### Reader API

Source: https://hellokaton.github.io/excel-plus

API for reading Excel documents.

```APIDOC
## Reader
* `create(Class)`: Creates a Reader for a specified type.
* `from(File)`: Reads from a file.
* `from(InputStream)`: Reads from an InputStream.
* `startRow(int)`: Sets the starting row for reading (0-indexed).
* `sheet(int)`: Specifies the sheet index to read (defaults to 0).
* `sheet(String)`: Specifies the sheet name to read (overrides sheet index).
* `asStream()`: Returns the read results as a Stream.
* `asList()`: Returns the read results as a List.
```

--------------------------------

### Read CSV Document

Source: https://hellokaton.github.io/excel-plus

Reads data from a CSV file using the same Reader API as for Excel files.

```java
Reader.create(Sample.class)
      .from(new File("sample.csv"))
      .asList();
```

--------------------------------

### Read Excel Data from InputStream

Source: https://hellokaton.github.io/excel-plus

Reads Excel data from an InputStream. Note that reading from a File is generally more efficient.

```java
Reader.create(Sample.class)
      .from(YOU_INPUT_STREAM)
```

--------------------------------

### Set Custom Sheet Name for Writer

Source: https://hellokaton.github.io/excel-plus

Sets a custom name for the sheet when writing an Excel file. Defaults to 'Sheet0' if not specified.

```java
Writer.create().sheet("my_sheet");
```

--------------------------------

### Filter Read Excel Data

Source: https://hellokaton.github.io/excel-plus

Filters rows from an Excel document based on a condition applied to the data. Collects the filtered results into a List.

```java
List<Sample> samples = Reader.create(Sample.class)
                .from(new File(classPath() + "/SampleData.xlsx"))
                .sheet("SalesOrders")
                .startRow(1)
                .asStream()
                .filter(sample -> sample.getAmount().intValue() > 1000)
                .collect(toList());
```
Tokens:
Raw