Read / Write Excel file (.xls or .xlsx) using Apache POI

ReadWriteExcelFile.java

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadWriteExcelFile {

	public static void readXLSFile() throws IOException
	{
		InputStream ExcelFileToRead = new FileInputStream("C:/Test.xls");
		HSSFWorkbook wb = new HSSFWorkbook(ExcelFileToRead);

		HSSFSheet sheet=wb.getSheetAt(0);
		HSSFRow row; 
		HSSFCell cell;

		Iterator rows = sheet.rowIterator();

		while (rows.hasNext())
		{
			row=(HSSFRow) rows.next();
			Iterator cells = row.cellIterator();
			
			while (cells.hasNext())
			{
				cell=(HSSFCell) cells.next();
		
				if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING)
				{
					System.out.print(cell.getStringCellValue()+" ");
				}
				else if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
				{
					System.out.print(cell.getNumericCellValue()+" ");
				}
				else
				{
					//U Can Handel Boolean, Formula, Errors
				}
			}
			System.out.println();
		}
	
	}
	
	public static void writeXLSFile() throws IOException {
		
		String excelFileName = "C:/Test.xls";//name of excel file

		String sheetName = "Sheet1";//name of sheet

		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet(sheetName) ;

		//iterating r number of rows
		for (int r=0;r < 5; r++ )
		{
			HSSFRow row = sheet.createRow(r);
	
			//iterating c number of columns
			for (int c=0;c < 5; c++ )
			{
				HSSFCell cell = row.createCell(c);
				
				cell.setCellValue("Cell "+r+" "+c);
			}
		}
		
		FileOutputStream fileOut = new FileOutputStream(excelFileName);
		
		//write this workbook to an Outputstream.
		wb.write(fileOut);
		fileOut.flush();
		fileOut.close();
	}
	
	public static void readXLSXFile() throws IOException
	{
		InputStream ExcelFileToRead = new FileInputStream("C:/Test.xlsx");
		XSSFWorkbook  wb = new XSSFWorkbook(ExcelFileToRead);
		
		XSSFWorkbook test = new XSSFWorkbook(); 
		
		XSSFSheet sheet = wb.getSheetAt(0);
		XSSFRow row; 
		XSSFCell cell;

		Iterator rows = sheet.rowIterator();

		while (rows.hasNext())
		{
			row=(XSSFRow) rows.next();
			Iterator cells = row.cellIterator();
			while (cells.hasNext())
			{
				cell=(XSSFCell) cells.next();
		
				if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING)
				{
					System.out.print(cell.getStringCellValue()+" ");
				}
				else if(cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC)
				{
					System.out.print(cell.getNumericCellValue()+" ");
				}
				else
				{
					//U Can Handel Boolean, Formula, Errors
				}
			}
			System.out.println();
		}
	
	}
	
	public static void writeXLSXFile() throws IOException {
		
		String excelFileName = "C:/Test.xlsx";//name of excel file

		String sheetName = "Sheet1";//name of sheet

		XSSFWorkbook wb = new XSSFWorkbook();
		XSSFSheet sheet = wb.createSheet(sheetName) ;

		//iterating r number of rows
		for (int r=0;r < 5; r++ )
		{
			XSSFRow row = sheet.createRow(r);

			//iterating c number of columns
			for (int c=0;c < 5; c++ )
			{
				XSSFCell cell = row.createCell(c);
	
				cell.setCellValue("Cell "+r+" "+c);
			}
		}

		FileOutputStream fileOut = new FileOutputStream(excelFileName);

		//write this workbook to an Outputstream.
		wb.write(fileOut);
		fileOut.flush();
		fileOut.close();
	}

	public static void main(String[] args) throws IOException {
		
		writeXLSFile();
		readXLSFile();
		
		writeXLSXFile();
		readXLSXFile();

	}

}

I am using this apache POI for reading excel sheet data. one of the column has numbers as string and need to remove the space after the string.But trim(), replaceAll() is not working for the data coming from the excel sheet.

Example : currentcell.getStringCellValue().trim() is also not working.It is returning the same string with the white space. Even tried with the replaceAll().

Can anybody please help me with this ?
Thank you in advance.

@bibekkumar005 may be this is you needed:

<!-- POI : Excel library -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>${poi.version}</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>${poi.version}</version>
</dependency>

please write the dependency .....having trouble when including POI jars for hssf and xssf

how to add this in android studio Dependency

How to convert .xls to .xlsx and vice versa?

Try it for Gradle Dependency in Android Studio:

implementation group: 'org.apache.poi', name: 'poi-ooxml', version: '3.9'

Thank you.

After changing the gradle version everything worked fine.

Any luck with android studio? .xls works fine but .xlsx giving me a lots to error.
Any one with working solution for .xlsx generation?

Dependency
implementation 'org.apache.poi:poi:3.9'
implementation 'org.apache.poi:poi-ooxml:3.9'
implementation 'org.apache.xmlbeans:xmlbeans:2.3.0'

Try it for Gradle Dependency in Android Studio:

implementation group: 'org.apache.poi', name: 'poi-ooxml', version: '3.9'

hello I implemnet latest version of but it' throw an error while I run app
Here is the Error
#1.Failed to transform bcprov-jdk15on-1.68.jar
#2.Unsupported class file major version 59

Any luck with android studio? .xls works fine but .xlsx giving me a lots to error.
Any one with working solution for .xlsx generation?

Dependency
implementation 'org.apache.poi:poi:3.9'
implementation 'org.apache.poi:poi-ooxml:3.9'
implementation 'org.apache.xmlbeans:xmlbeans:2.3.0'

Try it for Gradle Dependency in Android Studio:

implementation group: 'org.apache.poi', name: 'poi-ooxml', version: '3.9'

hello I implemnet latest version of but it' throw an error while I run app
Here is the Error
#1.Failed to transform bcprov-jdk15on-1.68.jar
#2.Unsupported class file major version 59

anybody here please share me the right dependancy to implemeant in anroid studio

when the line of the file is big enough，the code running below is very slow！

FileOutputStream outputStream = new FileOutputStream(outFile);
xssfWorkbook.write(outputStream);
outputStream.flush();
outputStream.close();

Hi
My code is running fine on local but on server it gives the given error, I am using apache poi 4.1.2, unable to debug and reproduce this

2021-07-07T14:18:52.477042302ZException in thread "pool-48-thread-1" java.lang.InternalError: java.lang.reflect.InvocationTargetException at java.desktop/sun.font.FontManagerFactory$1.run(FontManagerFactory.java:86) at java.base/java.security.AccessController.doPrivileged(Native Method) at java.desktop/sun.font.FontManagerFactory.getInstance(FontManagerFactory.java:74) at java.desktop/java.awt.Font.getFont2D(Font.java:497) at java.desktop/java.awt.Font.canDisplayUpTo(Font.java:2250) at java.desktop/java.awt.font.TextLayout.singleFont(TextLayout.java:469) at java.desktop/java.awt.font.TextLayout.(TextLayout.java:530) at org.apache.poi.ss.util.SheetUtil.getDefaultCharWidth(SheetUtil.java:273) at org.apache.poi.ss.util.SheetUtil.getColumnWidth(SheetUtil.java:248) at org.apache.poi.ss.util.SheetUtil.getColumnWidth(SheetUtil.java:233) at org.apache.poi.xssf.usermodel.XSSFSheet.autoSizeColumn(XSSFSheet.java:555) at org.apache.poi.xssf.usermodel.XSSFSheet.autoSizeColumn(XSSFSheet.java:537) at com.haulerhero.crm.data.service.ExportService.createCell(ExportService.java:118) at com.haulerhero.crm.data.service.ExportService.writeHeaderLine(ExportService.java:105) at com.haulerhero.crm.data.service.ExportService$1.run(ExportService.java:49) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) at java.base/java.lang.Thread.run(Thread.java:829) Caused by: java.lang.reflect.InvocationTargetException at java.base/jdk.internal.reflect.GeneratedConstructorAccessor151.newInstance(Unknown Source) at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490) at java.desktop/sun.font.FontManagerFactory$1.run(FontManagerFactory.java:84) ... 17 more Caused by: java.lang.NullPointerException at java.desktop/sun.awt.FontConfiguration.getVersion(FontConfiguration.java:1262) at java.desktop/sun.awt.FontConfiguration.readFontConfigFile(FontConfiguration.java:225) at java.desktop/sun.awt.FontConfiguration.init(FontConfiguration.java:107) at java.desktop/sun.awt.X11FontManager.createFontConfiguration(X11FontManager.java:719) at java.desktop/sun.font.SunFontManager$2.run(SunFontManager.java:379) at java.base/java.security.AccessController.doPrivileged(Native Method) at java.desktop/sun.font.SunFontManager.(SunFontManager.java:324) at java.desktop/sun.awt.FcFontManager.(FcFontManager.java:35) at java.desktop/sun.awt.X11FontManager.(X11FontManager.java:56) ... 21 more

If flush() used right after write(f) no data is been updated to the file. Why so?