This tutorial will show you how to write large data to an Excel file using Apache POI using SXSSF.

The theoretical text and concept have been borrowed from http://poi.apache.org/spreadsheet/how-to.html#sxssf

SXSSF (package: org.apache.poi.xssf.streaming) is an API-compatible streaming extension of XSSF to be used when very large spreadsheets have to be produced, and heap space is limited. SXSSF achieves its low memory footprint by limiting access to the rows that are within a sliding window, while XSSF gives access to all rows in the document. Older rows that are no longer in the window become inaccessible, as they are written to the disk.

You can specify the window size at workbook construction time via new SXSSFWorkbook(int windowSize) or you can set it per-sheet via SXSSFSheet#setRandomAccessWindowSize(int windowSize)

When a new row is created via createRow() and the total number of unflushed records would exceed the specified window size, then the row with the lowest index value is flushed and cannot be accessed via getRow() anymore.

The default window size is 100 and defined by SXSSFWorkbook.DEFAULT_WINDOW_SIZE.

A windowSize of -1 indicates unlimited access. In this case all records that have not been flushed by a call to flushRows() are available for random access.

Note that SXSSF allocates temporary files that you must always clean up explicitly, by calling the dispose method.

SXSSFWorkbook defaults to using inline strings instead of a shared strings table. This is very efficient, since no document content needs to be kept in memory, but is also known to produce documents that are incompatible with some clients. With shared strings enabled all unique strings in the document has to be kept in memory. Depending on your document content this could use a lot more resources than with shared strings disabled.

Please note that there are still things that still may consume a large amount of memory based on which features you are using, e.g. merged regions, comments, … are still only stored in memory and thus may require a lot of memory if used extensively.

Carefully review your memory budget and compatibility needs before deciding whether to enable shared strings or not.

Prerequisites

The following configurations are required in order to run the application

Eclipse Mars
JDK 1.8

Apache POI dependency in pm.xml
Have maven installed and configured

Now we will see the below steps how to create a maven based spring project in Eclipse.

Step 1. Create a standalone maven project in Eclipse

Go to File -> New -> Other. On popup window under Maven select Maven Project. Then click on Next. Select the workspace location – either default or browse the location. Click on Next. Now in next window select the row as highlighted from the below list of archtypes and click on Next button.

maven-arctype-quickstart

Now enter the required fields (Group Id, Artifact Id) as shown below

Group Id : com.roytuts
Artifact Id : java

Step 2. Modify the pom.xml file as shown below.

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>com.roytuts</groupId>
	<artifactId>java</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>

	<name>java</name>
	<url>http://maven.apache.org</url>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<poi.version>3.12</poi.version>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>
		<!-- apache poi for xlsx, docx etc reading/writing -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>${poi.version}</version>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<configuration>
					<source>${java.version}</source>
					<target>${java.version}</target>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>

Step 3. If you see JRE System Library[J2SE-1.5] then change the version by below process

Do right-click on the project and go to Build -> Configure build path, under Libraries tab click on JRE System Library[J2SE-1.5], click on Edit button and select the appropriate jdk 1.8 from the next window. Click on Finish then Ok.

Step 4. Create model class

package com.roytuts.excel;

public class ExcelModel {

	private String column1;
	private String column2;
	private String column3;
	private String column4;
	private String column5;
	private String column6;
	private String column7;
	private String column8;
	private String column9;
	private String column10;
	private String column11;
	private String column12;
	private String column13;
	private String column14;
	private String column15;
	private String column16;
	private String column17;
	private String column18;
	private String column19;
	private String column20;
	private String column21;
	private String column22;
	private String column23;
	private String column24;
	private String column25;
	private String column26;
	private String column27;
	private String column28;
	private String column29;
	private String column30;
	private String column31;
	private String column32;
	private String column33;
	private String column34;
	private String column35;
	private String column36;
	private String column37;
	private String column38;
	private String column39;
	private String column40;
	private String column41;
	private String column42;
	private String column43;
	private String column44;
	private String column45;
	private String column46;
	private String column47;
	private String column48;
	private String column49;
	private String column50;
	private String column51;
	private String column52;
	private String column53;
	private String column54;
	private String column55;
	private String column56;
	private String column57;
	private String column58;
	private String column59;
	private String column60;
	private String column61;
	private String column62;
	private String column63;
	private String column64;
	private String column65;
	private String column66;
	private String column67;
	private String column68;
	private String column69;
	private String column70;
	private String column71;
	private String column72;
	private String column73;
	private String column74;
	private String column75;
	private String column76;
	private String column77;
	private String column78;
	private String column79;
	private String column80;
	private String column81;
	private String column82;
	private String column83;
	private String column84;
	private String column85;
	private String column86;
	private String column87;
	private String column88;
	private String column89;
	private String column90;
	private String column91;
	private String column92;
	private String column93;
	private String column94;
	private String column95;
	private String column96;
	private String column97;
	private String column98;
	private String column99;
	private String column100;

	public String getColumn1() {
		return column1;
	}

	public void setColumn1(String column1) {
		this.column1 = column1;
	}

	public String getColumn2() {
		return column2;
	}

	public void setColumn2(String column2) {
		this.column2 = column2;
	}

	public String getColumn3() {
		return column3;
	}

	public void setColumn3(String column3) {
		this.column3 = column3;
	}

	public String getColumn4() {
		return column4;
	}

	public void setColumn4(String column4) {
		this.column4 = column4;
	}

	public String getColumn5() {
		return column5;
	}

	public void setColumn5(String column5) {
		this.column5 = column5;
	}

	public String getColumn6() {
		return column6;
	}

	public void setColumn6(String column6) {
		this.column6 = column6;
	}

	public String getColumn7() {
		return column7;
	}

	public void setColumn7(String column7) {
		this.column7 = column7;
	}

	public String getColumn8() {
		return column8;
	}

	public void setColumn8(String column8) {
		this.column8 = column8;
	}

	public String getColumn9() {
		return column9;
	}

	public void setColumn9(String column9) {
		this.column9 = column9;
	}

	public String getColumn10() {
		return column10;
	}

	public void setColumn10(String column10) {
		this.column10 = column10;
	}

	public String getColumn11() {
		return column11;
	}

	public void setColumn11(String column11) {
		this.column11 = column11;
	}

	public String getColumn12() {
		return column12;
	}

	public void setColumn12(String column12) {
		this.column12 = column12;
	}

	public String getColumn13() {
		return column13;
	}

	public void setColumn13(String column13) {
		this.column13 = column13;
	}

	public String getColumn14() {
		return column14;
	}

	public void setColumn14(String column14) {
		this.column14 = column14;
	}

	public String getColumn15() {
		return column15;
	}

	public void setColumn15(String column15) {
		this.column15 = column15;
	}

	public String getColumn16() {
		return column16;
	}

	public void setColumn16(String column16) {
		this.column16 = column16;
	}

	public String getColumn17() {
		return column17;
	}

	public void setColumn17(String column17) {
		this.column17 = column17;
	}

	public String getColumn18() {
		return column18;
	}

	public void setColumn18(String column18) {
		this.column18 = column18;
	}

	public String getColumn19() {
		return column19;
	}

	public void setColumn19(String column19) {
		this.column19 = column19;
	}

	public String getColumn20() {
		return column20;
	}

	public void setColumn20(String column20) {
		this.column20 = column20;
	}

	public String getColumn21() {
		return column21;
	}

	public void setColumn21(String column21) {
		this.column21 = column21;
	}

	public String getColumn22() {
		return column22;
	}

	public void setColumn22(String column22) {
		this.column22 = column22;
	}

	public String getColumn23() {
		return column23;
	}

	public void setColumn23(String column23) {
		this.column23 = column23;
	}

	public String getColumn24() {
		return column24;
	}

	public void setColumn24(String column24) {
		this.column24 = column24;
	}

	public String getColumn25() {
		return column25;
	}

	public void setColumn25(String column25) {
		this.column25 = column25;
	}

	public String getColumn26() {
		return column26;
	}

	public void setColumn26(String column26) {
		this.column26 = column26;
	}

	public String getColumn27() {
		return column27;
	}

	public void setColumn27(String column27) {
		this.column27 = column27;
	}

	public String getColumn28() {
		return column28;
	}

	public void setColumn28(String column28) {
		this.column28 = column28;
	}

	public String getColumn29() {
		return column29;
	}

	public void setColumn29(String column29) {
		this.column29 = column29;
	}

	public String getColumn30() {
		return column30;
	}

	public void setColumn30(String column30) {
		this.column30 = column30;
	}

	public String getColumn31() {
		return column31;
	}

	public void setColumn31(String column31) {
		this.column31 = column31;
	}

	public String getColumn32() {
		return column32;
	}

	public void setColumn32(String column32) {
		this.column32 = column32;
	}

	public String getColumn33() {
		return column33;
	}

	public void setColumn33(String column33) {
		this.column33 = column33;
	}

	public String getColumn34() {
		return column34;
	}

	public void setColumn34(String column34) {
		this.column34 = column34;
	}

	public String getColumn35() {
		return column35;
	}

	public void setColumn35(String column35) {
		this.column35 = column35;
	}

	public String getColumn36() {
		return column36;
	}

	public void setColumn36(String column36) {
		this.column36 = column36;
	}

	public String getColumn37() {
		return column37;
	}

	public void setColumn37(String column37) {
		this.column37 = column37;
	}

	public String getColumn38() {
		return column38;
	}

	public void setColumn38(String column38) {
		this.column38 = column38;
	}

	public String getColumn39() {
		return column39;
	}

	public void setColumn39(String column39) {
		this.column39 = column39;
	}

	public String getColumn40() {
		return column40;
	}

	public void setColumn40(String column40) {
		this.column40 = column40;
	}

	public String getColumn41() {
		return column41;
	}

	public void setColumn41(String column41) {
		this.column41 = column41;
	}

	public String getColumn42() {
		return column42;
	}

	public void setColumn42(String column42) {
		this.column42 = column42;
	}

	public String getColumn43() {
		return column43;
	}

	public void setColumn43(String column43) {
		this.column43 = column43;
	}

	public String getColumn44() {
		return column44;
	}

	public void setColumn44(String column44) {
		this.column44 = column44;
	}

	public String getColumn45() {
		return column45;
	}

	public void setColumn45(String column45) {
		this.column45 = column45;
	}

	public String getColumn46() {
		return column46;
	}

	public void setColumn46(String column46) {
		this.column46 = column46;
	}

	public String getColumn47() {
		return column47;
	}

	public void setColumn47(String column47) {
		this.column47 = column47;
	}

	public String getColumn48() {
		return column48;
	}

	public void setColumn48(String column48) {
		this.column48 = column48;
	}

	public String getColumn49() {
		return column49;
	}

	public void setColumn49(String column49) {
		this.column49 = column49;
	}

	public String getColumn50() {
		return column50;
	}

	public void setColumn50(String column50) {
		this.column50 = column50;
	}

	public String getColumn51() {
		return column51;
	}

	public void setColumn51(String column51) {
		this.column51 = column51;
	}

	public String getColumn52() {
		return column52;
	}

	public void setColumn52(String column52) {
		this.column52 = column52;
	}

	public String getColumn53() {
		return column53;
	}

	public void setColumn53(String column53) {
		this.column53 = column53;
	}

	public String getColumn54() {
		return column54;
	}

	public void setColumn54(String column54) {
		this.column54 = column54;
	}

	public String getColumn55() {
		return column55;
	}

	public void setColumn55(String column55) {
		this.column55 = column55;
	}

	public String getColumn56() {
		return column56;
	}

	public void setColumn56(String column56) {
		this.column56 = column56;
	}

	public String getColumn57() {
		return column57;
	}

	public void setColumn57(String column57) {
		this.column57 = column57;
	}

	public String getColumn58() {
		return column58;
	}

	public void setColumn58(String column58) {
		this.column58 = column58;
	}

	public String getColumn59() {
		return column59;
	}

	public void setColumn59(String column59) {
		this.column59 = column59;
	}

	public String getColumn60() {
		return column60;
	}

	public void setColumn60(String column60) {
		this.column60 = column60;
	}

	public String getColumn61() {
		return column61;
	}

	public void setColumn61(String column61) {
		this.column61 = column61;
	}

	public String getColumn62() {
		return column62;
	}

	public void setColumn62(String column62) {
		this.column62 = column62;
	}

	public String getColumn63() {
		return column63;
	}

	public void setColumn63(String column63) {
		this.column63 = column63;
	}

	public String getColumn64() {
		return column64;
	}

	public void setColumn64(String column64) {
		this.column64 = column64;
	}

	public String getColumn65() {
		return column65;
	}

	public void setColumn65(String column65) {
		this.column65 = column65;
	}

	public String getColumn66() {
		return column66;
	}

	public void setColumn66(String column66) {
		this.column66 = column66;
	}

	public String getColumn67() {
		return column67;
	}

	public void setColumn67(String column67) {
		this.column67 = column67;
	}

	public String getColumn68() {
		return column68;
	}

	public void setColumn68(String column68) {
		this.column68 = column68;
	}

	public String getColumn69() {
		return column69;
	}

	public void setColumn69(String column69) {
		this.column69 = column69;
	}

	public String getColumn70() {
		return column70;
	}

	public void setColumn70(String column70) {
		this.column70 = column70;
	}

	public String getColumn71() {
		return column71;
	}

	public void setColumn71(String column71) {
		this.column71 = column71;
	}

	public String getColumn72() {
		return column72;
	}

	public void setColumn72(String column72) {
		this.column72 = column72;
	}

	public String getColumn73() {
		return column73;
	}

	public void setColumn73(String column73) {
		this.column73 = column73;
	}

	public String getColumn74() {
		return column74;
	}

	public void setColumn74(String column74) {
		this.column74 = column74;
	}

	public String getColumn75() {
		return column75;
	}

	public void setColumn75(String column75) {
		this.column75 = column75;
	}

	public String getColumn76() {
		return column76;
	}

	public void setColumn76(String column76) {
		this.column76 = column76;
	}

	public String getColumn77() {
		return column77;
	}

	public void setColumn77(String column77) {
		this.column77 = column77;
	}

	public String getColumn78() {
		return column78;
	}

	public void setColumn78(String column78) {
		this.column78 = column78;
	}

	public String getColumn79() {
		return column79;
	}

	public void setColumn79(String column79) {
		this.column79 = column79;
	}

	public String getColumn80() {
		return column80;
	}

	public void setColumn80(String column80) {
		this.column80 = column80;
	}

	public String getColumn81() {
		return column81;
	}

	public void setColumn81(String column81) {
		this.column81 = column81;
	}

	public String getColumn82() {
		return column82;
	}

	public void setColumn82(String column82) {
		this.column82 = column82;
	}

	public String getColumn83() {
		return column83;
	}

	public void setColumn83(String column83) {
		this.column83 = column83;
	}

	public String getColumn84() {
		return column84;
	}

	public void setColumn84(String column84) {
		this.column84 = column84;
	}

	public String getColumn85() {
		return column85;
	}

	public void setColumn85(String column85) {
		this.column85 = column85;
	}

	public String getColumn86() {
		return column86;
	}

	public void setColumn86(String column86) {
		this.column86 = column86;
	}

	public String getColumn87() {
		return column87;
	}

	public void setColumn87(String column87) {
		this.column87 = column87;
	}

	public String getColumn88() {
		return column88;
	}

	public void setColumn88(String column88) {
		this.column88 = column88;
	}

	public String getColumn89() {
		return column89;
	}

	public void setColumn89(String column89) {
		this.column89 = column89;
	}

	public String getColumn90() {
		return column90;
	}

	public void setColumn90(String column90) {
		this.column90 = column90;
	}

	public String getColumn91() {
		return column91;
	}

	public void setColumn91(String column91) {
		this.column91 = column91;
	}

	public String getColumn92() {
		return column92;
	}

	public void setColumn92(String column92) {
		this.column92 = column92;
	}

	public String getColumn93() {
		return column93;
	}

	public void setColumn93(String column93) {
		this.column93 = column93;
	}

	public String getColumn94() {
		return column94;
	}

	public void setColumn94(String column94) {
		this.column94 = column94;
	}

	public String getColumn95() {
		return column95;
	}

	public void setColumn95(String column95) {
		this.column95 = column95;
	}

	public String getColumn96() {
		return column96;
	}

	public void setColumn96(String column96) {
		this.column96 = column96;
	}

	public String getColumn97() {
		return column97;
	}

	public void setColumn97(String column97) {
		this.column97 = column97;
	}

	public String getColumn98() {
		return column98;
	}

	public void setColumn98(String column98) {
		this.column98 = column98;
	}

	public String getColumn99() {
		return column99;
	}

	public void setColumn99(String column99) {
		this.column99 = column99;
	}

	public String getColumn100() {
		return column100;
	}

	public void setColumn100(String column100) {
		this.column100 = column100;
	}

}

Step 5. Create the Excel writer class

package com.roytuts.excel;

import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.List;

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.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

public class ExcelWriter {

	// using auto flush and default window size 100
	public void writeToExcelAutoFlush(List<ExcelModel> excelModels) {
		SXSSFWorkbook wb = null;
		FileOutputStream fos = null;
		try {
			// keep 100 rows in memory, exceeding rows will be flushed to disk
			wb = new SXSSFWorkbook(SXSSFWorkbook.DEFAULT_WINDOW_SIZE/* 100 */);
			Sheet sh = wb.createSheet();

			@SuppressWarnings("unchecked")
			Class<ExcelModel> classz = (Class<ExcelModel>) excelModels.get(0).getClass();

			Field[] fields = classz.getDeclaredFields();
			int noOfFields = fields.length;

			int rownum = 0;
			Row row = sh.createRow(rownum);
			for (int i = 0; i < noOfFields; i++) {
				Cell cell = row.createCell(i);
				cell.setCellValue(fields[i].getName());
			}

			for (ExcelModel excelModel : excelModels) {
				row = sh.createRow(rownum + 1);
				int colnum = 0;
				for (Field field : fields) {
					String fieldName = field.getName();
					Cell cell = row.createCell(colnum);
					Method method = null;
					try {
						method = classz.getMethod("get" + ExcelUtils.capitalizeInitialLetter(fieldName));
					} catch (NoSuchMethodException nme) {
						method = classz.getMethod("get" + fieldName);
					}
					Object value = method.invoke(excelModel, (Object[]) null);
					cell.setCellValue((String) value);
					colnum++;
				}
				rownum++;
			}
			fos = new FileOutputStream("sxssf.xlsx");
			wb.write(fos);
		} catch (Exception ex) {

		} finally {
			try {
				if (fos != null) {
					fos.close();
				}
			} catch (IOException e) {
			}
			try {
				if (wb != null) {
					wb.close();
				}
			} catch (IOException e) {
			}
		}
	}

	// using manual flush and default window size 100
	public void writeToExcelManualFlush(List<ExcelModel> excelModels) {
		SXSSFWorkbook wb = null;
		FileOutputStream fos = null;
		try {
			// turn off auto-flushing and accumulate all rows in memory
			wb = new SXSSFWorkbook(-1);
			Sheet sh = wb.createSheet();

			@SuppressWarnings("unchecked")
			Class<ExcelModel> classz = (Class<ExcelModel>) excelModels.get(0).getClass();

			Field[] fields = classz.getDeclaredFields();
			int noOfFields = fields.length;

			int rownum = 0;
			Row row = sh.createRow(rownum);
			for (int i = 0; i < noOfFields; i++) {
				Cell cell = row.createCell(i);
				cell.setCellValue(fields[i].getName());
			}

			for (ExcelModel excelModel : excelModels) {
				row = sh.createRow(rownum + 1);
				int colnum = 0;
				for (Field field : fields) {
					String fieldName = field.getName();
					Cell cell = row.createCell(colnum);
					Method method = null;
					try {
						method = classz.getMethod("get" + ExcelUtils.capitalizeInitialLetter(fieldName));
					} catch (NoSuchMethodException nme) {
						method = classz.getMethod("get" + fieldName);
					}
					Object value = method.invoke(excelModel, (Object[]) null);
					cell.setCellValue((String) value);
					colnum++;
				}
				// manually control how rows are flushed to disk
				if (rownum % 100 == 0) {
					// retain 100 last rows and flush all others
					((SXSSFSheet) sh).flushRows(100);
				}
				rownum++;
			}
			fos = new FileOutputStream("sxssf.xlsx");
			wb.write(fos);
		} catch (Exception ex) {

		} finally {
			try {
				if (fos != null) {
					fos.close();
				}
			} catch (IOException e) {
			}
			try {
				if (wb != null) {
					wb.close();
				}
			} catch (IOException e) {
			}
		}
	}

}

In the above class the first method writes a sheet with a window of 100 rows. When the row count reaches 101, the row with rownum=0 is flushed to disk and removed from memory, when rownum reaches 102 then the row with rownum=1 is flushed, etc.

The second method turns off auto-flushing (windowSize=-1) and the code manually controls how portions of data are written to disk

SXSSF flushes sheet data in temporary files (a temp file per sheet) and the size of these temporary files can grow to a very large value. For example, for a 20 MB csv data the size of the temp xml becomes more than a gigabyte.

Step 6. Create mock Excel data using below class

package com.roytuts.excel;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigInteger;
import java.security.SecureRandom;
import java.util.ArrayList;
import java.util.List;

public class ExcelMockData {

	private List<ExcelModel> excelData;

	public ExcelMockData() {
	}

	public List<ExcelModel> getExcelData() {
		if (excelData == null) {
			populateExcelData();
		}
		return excelData;
	}

	public void setExcelData(List<ExcelModel> excelData) {
		this.excelData = excelData;
	}

	private void populateExcelData() {
		excelData = new ArrayList<>();
		Class<ExcelModel> classz = (Class<ExcelModel>) ExcelModel.class;
		Method[] methods = classz.getMethods();
		for (int i = 0; i < 20000; i++) {
			ExcelModel model = new ExcelModel();
			for (Method method : methods) {
				String methodName = method.getName();
				if (methodName.startsWith("set")) {
					try {
						method.invoke(model, getRandomString());
					} catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
						e.printStackTrace();
					}
				}
			}
			excelData.add(model);
		}
	}

	private String getRandomString() {
		SecureRandom random = new SecureRandom();
		return new BigInteger(130, random).toString(32);
	}

}

Step 7. Create a main class to test the excel writing

package com.roytuts.excel;

import java.util.List;

public class ExcelWritingTest {

	public static void main(String[] args) {

		ExcelMockData mockData = new ExcelMockData();
		List<ExcelModel> excelData = mockData.getExcelData();

		ExcelWriter writer = new ExcelWriter();
		
		//using auto flush mode
		// writer.writeToExcelAutoFlush(excelData);
		
		//using manual flush mode
		writer.writeToExcelManualFlush(excelData);

	}

}

Step 8. Run the main class, you will see an excel file sxssf.xlsx will be generated under project directory java.

Thanks for reading.

Tags:

I am a professional Web developer, Enterprise Application developer, Software Engineer and Blogger. Connect me on Roy Tutorials | TwitterFacebook Google PlusLinkedin | Reddit

Leave a Reply

Your email address will not be published. Required fields are marked *