Learn to read or write a huge Excel file using the fastexcel library, which is an excellent alternative for Apache POI. Although POI is the most popular library to work with Excel files, it is not suitable for working with large Excel files due to high memory and processing power consumption.
- Apache POI’s non-streaming API is about ten times slower than fastexcel and uses 12 times more heap memory.
- Apache POI’s streaming API provides almost similar performance.
Overall, the Fastexcel provides a rather easier and simplified API to get started with. It handles the memory much more efficiently and handles large files very well. Note that FastExcel supports a limited set of features – it focuses on speed and memory reduction.
1. Maven
Start with adding the latest version of the following dependencies:
- fastexcel: for writing excel files.
- fastexcel-reader: for reading excel files.
<dependency>
<groupId>org.dhatim</groupId>
<artifactId>fastexcel</artifactId>
<version>0.14.0</version>
</dependency>
<dependency>
<groupId>org.dhatim</groupId>
<artifactId>fastexcel-reader</artifactId>
<version>0.14.0</version>
</dependency>2. Writing a Large Excel File
2.1. API
To write an excel file, start by creating a new org.dhatim.fastexcel.Workbook instance and obtain the org.dhatim.fastexcel.Worksheet reference using newWorksheet() method.
OutputStream os = new FileOutputStream("file.xlsx");
Workbook wb = new Workbook(outputStream, "DemoExcel", "1.0");
Worksheet ws = wb.newWorksheet("Sheet 1");Now we can use various methods in Worksheet to set the values in the cells.
ws.value(0, 0, "Some Value"); //A1
ws.value(0, 1, LocalDateTime.now()); //A2
ws.value(0, 2, 100); //A3
ws.value(1, 0, "Some Value"); //B1
ws.value(1, 1, LocalDateTime.now()); //B2
ws.value(1, 2, 100); //B3We can also apply the formulas in the cells.
ws.formula(10, 0, "SUM(A1:A10)");2.2. Example
The following is an example of writing 1,000,000 rows in the sheet where each row contains 10 cell values. We are also using the StopWatch API to measure the performance.
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.concurrent.TimeUnit;
import org.apache.commons.lang3.time.StopWatch;
import org.dhatim.fastexcel.Workbook;
import org.dhatim.fastexcel.Worksheet;
public class WriteExcel {
public static void main(String[] args) throws IOException {
try (OutputStream os = new FileOutputStream("c:/temp/fastexcel-demo.xlsx")) {
Workbook wb = new Workbook(os, "DemoExcel", "1.0");
Worksheet ws = wb.newWorksheet("Sheet 1");
StopWatch watch = new StopWatch();
watch.start();
ws.value(0, 0, "Column 1");
ws.value(0, 1, "Column 2");
ws.value(0, 2, "Column 3");
ws.value(0, 3, "Column 4");
ws.value(0, 4, "Column 5");
ws.value(0, 5, "Column 6");
ws.value(0, 6, "Column 7");
ws.value(0, 7, "Column 8");
ws.value(0, 8, "Column 9");
ws.value(0, 9, "Column 10");
for (int i = 1; i < 1_000_000; i++) {
String value = "data-" + i;
ws.value(i, 0, i);
ws.value(i, 1, value);
ws.value(i, 2, value);
ws.value(i, 3, value);
ws.value(i, 4, value);
ws.value(i, 5, value);
ws.value(i, 6, value);
ws.value(i, 7, value);
ws.value(i, 8, value);
ws.value(i, 9, value);
}
wb.finish();
watch.stop();
System.out.println("Processing time :: " + watch.getTime(TimeUnit.MILLISECONDS));
} catch (Exception e) {
e.printStackTrace();
}
}
}The whole program is executed in around 5-6 seconds on my machine and writes a 37MB size excel file.
3. Reading a Large Excel File
3.1. API
FastExcel provides a streaming API that iterates over all rows and provides get() methods to read the cell values according to cell value types.
try (Stream<Row> rows = sheet.openStream()) {
rows.forEach(r -> {
BigDecimal num = r.getCellAsNumber(0).orElse(null);
...
...
});
}The following get methods can be used to read the cell values:
getCellAsNumber(): returns Optional<BigDecimal> type.getCellAsString(): returns Optional<String> type.getCellAsDate(): returns Optional<LocalDateTime> type.getCellAsBoolean(): returns Optional<Boolean> type.getCellText(): returns String type.getCellRawValue(): returns Optional<String> type.
3.2. Example
The following program reads the excel file generated in the previous section and writes cell values in the console to keep the code simple.
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.concurrent.TimeUnit;
import java.util.stream.Stream;
import org.apache.commons.lang3.time.StopWatch;
import org.dhatim.fastexcel.reader.ReadableWorkbook;
import org.dhatim.fastexcel.reader.Row;
public class ReadExcel {
public static void main(String[] args) throws IOException {
try (InputStream is = new FileInputStream("c:/temp/fastexcel-demo.xlsx");
ReadableWorkbook wb = new ReadableWorkbook(is)) {
StopWatch watch = new StopWatch();
watch.start();
wb.getSheets().forEach(sheet ->
{
try (Stream<Row> rows = sheet.openStream()) {
rows.skip(1).forEach(r -> {
BigDecimal num = r.getCellAsNumber(0).orElse(null);
String str = r.getCellAsString(1).orElse(null);
System.out.println("Cell str value :: " + num);
System.out.println("Cell str value :: " + str);
});
} catch (Exception e) {
e.printStackTrace();
}
watch.stop();
System.out.println("Processing time :: " + watch.getTime(TimeUnit.MILLISECONDS));
});
}
}
}The whole program is executed in around 7-8 seconds on my machine.
4. Conclusion
In this Java tutorial, we learned to use the FastExcel library to read and write large excel files effectively. We learned the APIs for read and write operations as well as simple examples for the same. You can customize the programs according to your project requirements.
Happy Learning !!
Comments