DOCSEARCHbyAlgolia
Submit Docs

fastexcel

repository·master·Indexed 21 days ago

https://github.com/dhatim/fastexcel

A high-performance, low-memory Java library for generating (fastexcel-writer) and reading (fastexcel-reader) Excel XLSX files. Designed as a streaming alternative to Apache POI, it is optimized for handling very large worksheets without excessive heap memory usage. Features include support for various data types, cell styling, formulas, hyperlinks, and inline strings for memory optimization.

Tokens
3.7K
Snippets
16
Records
17
Agent score
75%

What's inside fastexcel

  1. Optimize memory with Inline Strings

    master

    By default, ws.value(row, col, string) uses shared strings. This is efficient for files with many duplicate strings but can consume significant heap memory if most strings are unique.

    To reduce memory usage at the cost of larger file size, use ws.inlineString(row, col, string) to write strings directly to the cell without sharing them.

    // Use this if you have many unique strings to save heap memory
ws.inlineString(0, 0, "Inline String");

  2. Install fastexcel-writer

    master

    To use the fastexcel-writer for generating XLSX workbooks, add the following dependency to your Maven pom.xml:

    <dependency>
    <groupId>org.dhatim</groupId>
    <artifactId>fastexcel</artifactId>
    <version>0.20.2</version>
</dependency>

    Prerequisites:

    • Java 8 or higher.

  3. Run benchmarks in fastexcel

    master

    To run the performance benchmarks, ensure your benchmark classes end with the suffix Benchmark and extend BenchmarkLauncher. Use the Maven profile -Pbench to execute them.

    Benchmark results are exported as CSV files to the target directory, named after the benchmark class (for example, ReaderBenchmark.csv).

    mvn clean test -Pbench

  5. Install fastexcel-reader

    master

    To use the fastexcel-reader for streaming XLSX reading, add the following dependency to your Maven pom.xml:

    <dependency>
    <groupId>org.dhatim</groupId>
    <artifactId>fastexcel-reader</artifactId>
    <version>0.18.4</version>
</dependency>

    Prerequisites:

    • Java 8 or higher.

  7. Create a simple workbook with fastexcel-writer

    master

    You can create a workbook by providing an OutputStream to the Workbook constructor. The workbook supports various data types including Strings, Dates, Integers, Longs, and Doubles. Use wb.newWorksheet(name) to create a new sheet and ws.value(row, col, value) to populate cells.

    try (OutputStream os = ...; Workbook wb = new Workbook(os, "MyApplication", "1.0");) {
    Worksheet ws = wb.newWorksheet("Sheet 1");
    ws.value(0, 0, "This is a string in A1");
    ws.value(0, 1, new Date());
    ws.value(0, 2, 1234);
    ws.value(0, 3, 123456L);
    ws.value(0, 4, 1.234);
}

  8. Protect a worksheet from viewing

    master

    You can hide a worksheet and protect the workbook structure using protectWithViewPassword(password). This prevents users from unhiding, moving, renaming, or deleting sheets unless they have the workbook structure password.

    Note: This is distinct from protect(...), which is used to protect a worksheet from editing. protectWithViewPassword(...) is specifically for restricting visibility.

    try (OutputStream os = new FileOutputStream("protected.xlsx");
     Workbook wb = new Workbook(os, "Application", "1.0")) {

    Worksheet ws = wb.newWorksheet("SecretSheet");
    ws.value(0, 0, "Sensitive Data");

    ws.protectWithViewPassword("viewPassword");
}

  9. Manage cell ranges, merging, and shading

    master

    Use ws.range(startRow, startCol, endRow, endCol) to select a block of cells for bulk operations:

    • Merging: ws.range(...).merge()
    • Alignment: ws.range(...).style().horizontalAlignment("center").set()
    • Shading:
      • Alternate rows: ws.range(...).style().shadeAlternateRows(Color.GRAY2).set()
      • Every Nth row: ws.range(...).style().shadeRows(Color.GRAY2, 5).set()
    • Naming: ws.range(...).setName("my_range") (names must only contain letters, numbers, and underscores).
    // Merge and center
ws.range(0, 0, 10, 10).style().horizontalAlignment("center").italic().set();
ws.range(0, 0, 10, 10).merge();

// Shade every 5th row
ws.range(0, 0, 10, 10).style().shadeRows(Color.GRAY2, 5).set();

// Name a range
ws.range(0, 0, 0, 10).setName("header");

  10. Read Excel files with fastexcel-reader

    master

    The reader uses a streaming approach to read cell content. It is designed for high performance and low memory usage, though it discards styles and graphs.

    Streaming rows: Use sheet.openStream() to get a Stream<Row>. Each Row allows you to extract values as BigDecimal, String, or LocalDateTime.

    Reading all rows to a list: Use sheet.read() to load all rows into memory.

    Customizing reading options: By default, formatting is not read. To include cell formatting, pass a ReadingOptions object to the ReadableWorkbook constructor:

    • withCellFormat (boolean): If true, extracts cell formatting.
    • cellInErrorIfParseError (boolean): If true, cell type is ERROR on parse failure; if false, an exception is thrown.
    // Streaming approach
try (InputStream is = ...; ReadableWorkbook wb = new ReadableWorkbook(is)) {
    Sheet sheet = wb.getFirstSheet();
    try (Stream<Row> rows = sheet.openStream()) {
        rows.forEach(r -> {
            BigDecimal num = r.getCellAsNumber(0).orElse(null);
            String str = r.getCellAsString(1).orElse(null);
            LocalDateTime date = r.getCellAsDate(2).orElse(null);
        });
    }
}

// Reading with formatting options
ReadingOptions readingOptions = new ReadingOptions(true, true);
try (ReadableWorkbook wb = new ReadableWorkbook(is, readingOptions)) {
    // ...
}

  11. Apply styles and formatting to cells

    master

    You can apply styles to individual cells or ranges. Supported operations include:

    • Bold/Italic: ws.style(row, col).bold().set() or .italic().set()
    • Fill patterns: ws.style(row, col).fill(Fill.GRAY125).set()
    • Custom formatting: ws.style(row, col).format("yyyy-MM-dd").set()
    • Conditional formatting: ws.style(row, col).fillColor("FF8800").set(new ConditionalFormattingExpressionRule("LENB(A1)>1", true))
    • Rotation: ws.style(row, col).rotation(90).set()
    • Global font: wb.setGlobalDefaultFont("Arial", 15.5)
    // Bold with fill
ws.style(0, 0).bold().fill(Fill.GRAY125).set();

// Date formatting
ws.value(0, 0, LocalDateTime.now());
ws.style(0, 0).format("yyyy-MM-dd H:mm:ss").set();

// Conditional formatting
ws.style(0, 0).fillColor("FF8800").set(new ConditionalFormattingExpressionRule("LENB(A1)>1", true));

  12. Use formulas and hyperlinks

    master

    Cells containing formulas do not have a pre-set value in the generated workbook. You can use ws.range(...).toString() to build formula strings dynamically.

    Formulas: ws.formula(row, col, "FORMULA_STRING")

    Hyperlinks:

    • To a URL: ws.hyperlink(row, col, new HyperLink("https://example.com", "Label"))
    • To a cell/range: ws.range(row, col, row, col).setHyperlink(new HyperLink("path/to/file.pdf", "Label"))
    // Formula using range string
ws.formula(10, 0, "SUM(" + ws.range(0, 0, 9, 0).toString() + ")");

// Hyperlink to URL
ws.hyperlink(0, 0, new HyperLink("https://github.com/dhatim/fastexcel", "Baidu"));