这是indexloc提供的服务，不要输入任何密码
Skip to content

BAEL-8684: Comparison with HSSFWorkbook, XSSFWorkbook, and SXSSFWorkbook in Apache POI #17812

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
merged 1 commit into from
Nov 2, 2024
Merged

BAEL-8684: Comparison with HSSFWorkbook, XSSFWorkbook, and SXSSFWorkbook in Apache POI #17812

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
1 commit
Select commit
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
15 changes: 13 additions & 2 deletions apache-poi-3/pom.xml
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -78,7 +78,17 @@
<groupId>ch.qos.logback</groupId>
<artifactId>logback-core</artifactId>
<version>${logback-core.version}</version>
</dependency>
</dependency>
<dependency>
<groupId>org.openjdk.jmh</groupId>
<artifactId>jmh-core</artifactId>
<version>${jmh.version}</version>
</dependency>
<dependency>
<groupId>org.openjdk.jmh</groupId>
<artifactId>jmh-generator-annprocess</artifactId>
<version>${jmh.version}</version>
</dependency>
</dependencies>

<properties>
Expand All @@ -90,7 +100,8 @@
<jxl.version>2.6.12</jxl.version>
<poitl.version>1.12.2</poitl.version>
<logback-classic.version>1.5.6</logback-classic.version>
<logback-core.version>1.5.6</logback-core.version>
<logback-core.version>1.5.6</logback-core.version>
<jmh.version>1.37</jmh.version>
</properties>

</project>
133 changes: 133 additions & 0 deletions apache-poi-3/src/main/java/com/baeldung/poi/benchmark/WorkbookBenchmark.java
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,133 @@
package com.baeldung.poi.benchmark;

import java.io.IOException;
import java.util.concurrent.TimeUnit;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openjdk.jmh.annotations.*;
import org.openjdk.jmh.infra.Blackhole;
import org.openjdk.jmh.profile.MemPoolProfiler;
import org.openjdk.jmh.runner.Runner;
import org.openjdk.jmh.runner.options.Options;
import org.openjdk.jmh.runner.options.OptionsBuilder;

@State(Scope.Benchmark)
@BenchmarkMode(Mode.AverageTime)
@Warmup(iterations = 1, time = 2)
@Measurement(iterations = 3, time = 1)
@OutputTimeUnit(TimeUnit.MILLISECONDS)
@Fork(value = 1)
public class WorkbookBenchmark
{

@Benchmark
public static void write2500RowsToHSSFWorkbook(Blackhole blackhole) throws IOException {
writeRowsToWorkbook(new HSSFWorkbook(), 2500, blackhole);
}

@Benchmark
public static void write5000RowsToHSSFWorkbook(Blackhole blackhole) throws IOException {
writeRowsToWorkbook(new HSSFWorkbook(), 5000, blackhole);
}

@Benchmark
public static void write10000RowsToHSSFWorkbook(Blackhole blackhole) throws IOException {
writeRowsToWorkbook(new HSSFWorkbook(), 10000, blackhole);
}

@Benchmark
public static void write20000RowsToHSSFWorkbook(Blackhole blackhole) throws IOException {
writeRowsToWorkbook(new HSSFWorkbook(), 20000, blackhole);
}

@Benchmark
public static void write40000RowsToHSSFWorkbook(Blackhole blackhole) throws IOException {
writeRowsToWorkbook(new HSSFWorkbook(), 40000, blackhole);
}

@Benchmark
public static void write2500RowsToXSSFWorkbook(Blackhole blackhole) throws IOException {
writeRowsToWorkbook(new XSSFWorkbook(), 2500, blackhole);
}

@Benchmark
public static void write5000RowsToXSSFWorkbook(Blackhole blackhole) throws IOException {
writeRowsToWorkbook(new XSSFWorkbook(), 5000, blackhole);
}

@Benchmark
public static void write10000RowsToXSSFWorkbook(Blackhole blackhole) throws IOException {
writeRowsToWorkbook(new XSSFWorkbook(), 10000, blackhole);
}

@Benchmark
public static void write20000RowsToXSSFWorkbook(Blackhole blackhole) throws IOException {
writeRowsToWorkbook(new XSSFWorkbook(), 20000, blackhole);
}

@Benchmark
public static void write40000RowsToXSSFWorkbook(Blackhole blackhole) throws IOException {
writeRowsToWorkbook(new XSSFWorkbook(), 40000, blackhole);
}

@Benchmark
public static void write2500RowsToSXSSFWorkbook(Blackhole blackhole) throws IOException {
writeRowsToWorkbook(new SXSSFWorkbook(), 2500, blackhole);
}

@Benchmark
public static void write5000RowsToSXSSFWorkbook(Blackhole blackhole) throws IOException {
writeRowsToWorkbook(new SXSSFWorkbook(), 5000, blackhole);
}

@Benchmark
public static void write10000RowsToSXSSFWorkbook(Blackhole blackhole) throws IOException {
writeRowsToWorkbook(getSXSSFWorkbook(), 10000, blackhole);
}

@Benchmark
public static void write20000RowsToSXSSFWorkbook(Blackhole blackhole) throws IOException {
writeRowsToWorkbook(getSXSSFWorkbook(), 20000, blackhole);
}

@Benchmark
public static void write40000RowsToSXSSFWorkbook(Blackhole blackhole) throws IOException {
writeRowsToWorkbook(getSXSSFWorkbook(), 40000, blackhole);
}

private static SXSSFWorkbook getSXSSFWorkbook() {
SXSSFWorkbook workbook = new SXSSFWorkbook();
workbook.setCompressTempFiles(true);
return workbook;
}

public static void writeRowsToWorkbook(Workbook workbook, int iterations, Blackhole blackhole) throws IOException {
Sheet sheet = workbook.createSheet();
for (int n=0;n<iterations;n++) {
Row row = sheet.createRow(sheet.getLastRowNum()+1);
for (int c=0;c<256;c++) {
Cell cell = row.createCell(c);
cell.setCellValue("abcdefghijklmnopqrstuvwxyz");
}
}
workbook.close();
blackhole.consume(workbook);
}

public static void main(String[] args) throws Exception {
Options options = new OptionsBuilder()
.include(WorkbookBenchmark.class.getSimpleName()).threads(1)
.shouldFailOnError(true)
.shouldDoGC(true)
.addProfiler(MemPoolProfiler.class)
.jvmArgs("-server").build();
new Runner(options).run();
}

}
67 changes: 67 additions & 0 deletions apache-poi-3/src/test/java/com/baeldung/poi/benchmark/SXSSFWorkbookFunctionTest.java
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,67 @@
package com.baeldung.poi.benchmark;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFFormulaEvaluator;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;

import java.io.*;

import static org.assertj.core.api.Assertions.assertThat;
import static org.junit.jupiter.api.Assertions.*;

class SXSSFWorkbookFunctionTest {

private Workbook workbook;
private Sheet sheet;

@BeforeEach
void setup() {
workbook = new SXSSFWorkbook(1);
sheet = workbook.createSheet("Test Sheet");
sheet.createRow(0).createCell(0).setCellValue(5);
sheet.createRow(1).createCell(0).setCellValue(15);
}

@Test
void whenAutoSizeColumnOnSXSSFWorkbook_thenThrowsIllegalStateException() {
assertThrows(IllegalStateException.class, () -> sheet.autoSizeColumn(0));
}

@Test
void whenCloneSheetOnSXSSFWorkbook_thenThrowsIllegalStateException() {
assertThrows(IllegalStateException.class, () -> workbook.cloneSheet(0));
}

@Test
void whenEvaluateFormulaCellOnSXSSFWorkbook_thenThrowsIllegalStateException() {
Cell formulaCell = sheet.createRow(sheet.getLastRowNum()).createCell(0);
formulaCell.setCellFormula("SUM(A1:B1)");

FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
assertThrows(SXSSFFormulaEvaluator.RowFlushedException.class, () -> evaluator.evaluateFormulaCell(formulaCell));
}

@Test
void whenGetRowOnSXSSFWorkbook_thenReturnNull() {
Row row = sheet.getRow(0);
assertThat(row).isNull();
}

@Test
void whenShiftColumnsOnSXSSFWorkbook_thenThrowsUnsupportedOperationException() {
assertThrows(UnsupportedOperationException.class, () -> sheet.shiftColumns(0, 2, 1));
}

@AfterEach
void cleanUp() throws IOException {
workbook.close();
}

}