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:
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 =newFileOutputStream("file.xlsx");Workbook wb =newWorkbook(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.
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.
The following program reads the excel file generated in the previous section and writes cell values in the console to keep the code simple.
importjava.io.FileInputStream;importjava.io.IOException;importjava.io.InputStream;importjava.math.BigDecimal;importjava.util.concurrent.TimeUnit;importjava.util.stream.Stream;importorg.apache.commons.lang3.time.StopWatch;importorg.dhatim.fastexcel.reader.ReadableWorkbook;importorg.dhatim.fastexcel.reader.Row;publicclassReadExcel{publicstaticvoidmain(String[] args)throwsIOException{try(InputStream is =newFileInputStream("c:/temp/fastexcel-demo.xlsx");ReadableWorkbook wb =newReadableWorkbook(is)){StopWatch watch =newStopWatch();
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.
A fun-loving family man, passionate about computers and problem-solving, with over 15 years of experience in Java and related technologies.
An avid Sci-Fi movie enthusiast and a fan of Christopher Nolan and Quentin Tarantino.
Comments