How to Read Excel Files in Java (1).pdf

https://firstcode.school/how-to-read-excel-file-in-java/

1. HowtoReadExcelFilesin
Java?
Intoday’scomputerworld,excelfilesareanintegralpartofeverybusinessranging
fromsmalltolarge.TheusesofExcelfilesincludedatamanagement,analysis,
charting,graphing,etc.Sowhenwecometotheprogrammingside,therewillbea
situationwherewehavetohandleExcelfiles.InJavaprogramming,thereisno
built-infeaturetosupportExcelfiles.YetwecanhandleExcelfileseasilybyusing
thethird-partyAPInamedApachePOI.Intoday’sarticle,wewilllookathowtoread
ExcelfilesinJavausingtheApachePOIAPI.
Excelfiles:
WeallknowthatExcelfilesarespreadsheetsthatmakedatahandlingand
manipulationeasier.
Generally,therearetwofileformatsavailableforExcelfiles.Theyare
1..xls
2..xlsx.
Thexlsformatistheolderonefor2003andolderversions.Andxlsxisanewformat
forexcel2007andlater.
ApachePOI:

2. Generally,ApachePOI(PoorObfuscationImplementation)isanopen-sourcelibrary
forhandlingMicrosoftofficerelatedfiles.Javadoesnothavethebuilt-infeatureto
handleexcelfiles.So,WeuseApachePOI,athird-partyJavaAPIwithclassesand
interfacestodealwithreadingandwritingoperationsinexcelfiles.
ThisAPIsupportsboth.xlsand.xlsxformats.Todealwiththe.xlsformat,weuse
HSSF(HorribleSpreadSheetFormat)implementation.Andtodealwiththe.xlsx
format,weuseXSSF(XMLSpreadSheetFormat)implementation.
Classes:
WeallknowthatExcelfilesconsistofWorkbook,WorkSheets,cells,rows,and
columns.TheAPIcomprisesavarietyofclassestoincorporatethefunctionalitythat
dealswithexcelfiles.Thereareseparateclassesavailabletodealwith.xlsand.xlsx
formats.
HSSFWorkbook,HSSFSheet,HSSFRow,andHSSFCellaretheclassesthat
providethefunctionalityforhandlingworkbooks,sheets,rows,andcellsinxlsformat
files,respectively.
Similarly,theXSSFWorkbook,XSSFSheet,XSSFRow,andXSSFCellarethe
classesthatprovidethefunctionalityforhandlingworkbooks,sheets,rows,andcells
inxlsxformatfiles,respectively.
Interface:
ThisAPIprovidessomecommoninterfacesforalltheclassesdiscussedabove.
Theyare:

3. 1.Workbook–Workbookisacommoninterfaceimplementedbyboth
HSSFWorkbookandXSSFWorkbookclasses.
2.Sheet–TheHSSFSheetandXSSFSheetclassesimplementthisinterface.
3.Row–BothHSSFRowandXSSFRowclassesusethisinterface.
4.Cell–TheHSSFCellandXSSFCellclassesimplementthisinterface.
PrerequisitesforusingApachePOIto
readexcelfileinjava:
TherequirementsforusingApachePOIare:
1.AneclipseIDE
2.poi-bin-5.2.3-20220909.zipfolder
Extractthezipfile.ThereisalistofjarfilesrequiredtouseApachePOI.
Youcandownloadthelatestversionsofthejarifneeded.
3.Anexcelfileinbothformats.
a.Inthisarticle,wewillusetheexcelfilesnamedFirstCode.xlsandFirstCode.xlsx.
b.Thecontentofthefilesare:
Nowfollowthestepsgivenbelowtosetuptheenvironment.
1.OpentheEclipseIDE
2.Createanewjavaproject.

4. 3.Addallthedownloadedjarfilesintheclasspath,asshownbelow.
Right-clicktheprojectname→BuildPath→ConfigureBuildPath→Clickthe
librariestab→AddExternaljarsinClasspath→Selectallthejarsdiscussedabove
→ClickApplyandclose.Ifyouareusingmaven,youshouldincludethefollowing
dependenciesinyourpom.xmlfile.
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.0</version>
</dependency>
Codetoreadthexlsfileinjava:
importjava.io.File;
importjava.io.FileInputStream;
importjava.io.FileNotFoundException ;
importjava.util.Scanner;
importorg.apache.poi.hssf.usermodel.HSSFSheet;
importorg.apache.poi.hssf.usermodel.HSSFWorkbook;

5. importorg.apache.poi.ss.usermodel.Cell;
importorg.apache.poi.ss.usermodel.FormulaEvaluator;
importorg.apache.poi.ss.usermodel.Row;
publicclassReadExcelFile{
publicstaticvoidmain(Stringargs[])
{
try{
//Creatingafileobjectandgettingtheexcelfile.
Filefile=newFile("E:\\FirstCode.xls");
//CreatingFileInputStream objecttoreadthefile.
FileInputStream fisObject=newFileInputStream(file);
//CreatingWorkbookinstancefor.xlsfile.
HSSFWorkbookworkbook=newHSSFWorkbook(fisObject);
//Gettingthesheetnumberfromtheuser.
System.out.println("Enterthesheetnumber:");
Scannerscan=newScanner(System.in);
intsheetnumber=scan.nextInt();
//CreatingaSheetobjecttogettherequiredsheetinthefile.
HSSFSheetsheet=workbook.getSheetAt(sheetnumber);
//Evaluatingcelltype
FormulaEvaluator
formulaEvaluator=workbook. getCreationHelper().createFormulaEvaluator ();
//Iteratingtherowswith,foreachloop.

6. for(Rowrow:sheet)
{
//Iteratingthecellswith,foreachloop.
for(Cellcell:row)
{
switch(formulaEvaluator.evaluateInCell(cell).getCellType())
{
//Numericcelltype.
caseNUMERIC:
//Gettingthecellvalueasanumberandprintingit.
System.out.print(cell.getNumericCellValue()+"\t\t");
break;
//Stringcelltype.
caseSTRING:
//Gettingthecellvalueasastringandprintingit.
System.out.print(cell.getStringCellValue()+"\t\t");
break;
default:
break;
}
}
System.out.println();
}

7. workbook.close();
}
catch(FileNotFoundException exception){
//Ifthereisnoexcelfilewiththegivennamefoundinthespecifiedlocation,it
throwsFileNotFoundException.
System.out.println("FileNotFound.");
}
catch(IllegalArgumentException exception){
//Ifthesheetisempty,itthrowsanIllegalArgumentException.
System.out.println("Sheetisempty");
}
catch(Exceptionexception){
//TodealwithotherExceptions.
exception.printStackTrace();
}
}
}
Output:
■Whentheexcelfilereadingissuccessful,wholefilewillbedisplayedas
output.
■Whenthereisnoexcelfilewiththegivennamefoundinthespecified
location,theoutputwillbe:
FileNotFound
■Whenthereisnodatainthegivensheetindex,theoutputwillbe:

8. Sheetisempty
Codetoreadthexlsxfileinjava:
importjava.io.File;
importjava.io.FileInputStream;
importjava.io.FileNotFoundException ;
importjava.util.Iterator;
importjava.util.Scanner;
importorg.apache.poi.ss.usermodel.Cell;
importorg.apache.poi.ss.usermodel.Row;
importorg.apache.poi.xssf.usermodel.XSSFSheet;
importorg.apache.poi.xssf.usermodel.XSSFWorkbook;
publicclassReadExcelFile
{
publicstaticvoidmain(String[]args)
{
try
{
//Creatingafileobjectandgettingtheexcelfile.
Filefile=newFile("E:\\FirstCode.xlsx" );
//CreatingFileInputStream objecttoreadthefile.
FileInputStream fisObject=newFileInputStream(file);
//CreatingWorkbookinstancefor.xlsxfile.

9. XSSFWorkbookworkbook=newXSSFWorkbook(fisObject);
//Gettingthesheetnumberfromtheuser.
System.out.println("Enterthesheetnumber:");
Scannerscan=newScanner(System.in);
intsheetnumber=scan.nextInt();
//CreatingaSheetobjecttogettherequiredsheetinthefile.
XSSFSheetsheet=workbook.getSheetAt(sheetnumber);
//Usinganiteratortoiteratethesheetobjecttogetrows.
Iterator<Row>itr=sheet.iterator();
while(itr.hasNext())
{
//Gettingtherowvalues.
Rowrow=itr.next();
//Iteratingeachcolumnintherow.
Iterator<Cell>cellIterator=row.cellIterator();
while(cellIterator.hasNext())
{
//Gettingcolumnvalues.
Cellcell=cellIterator.next();
//Gettingcelltype.
switch(cell.getCellType())
{
//Stringcelltype.

10. caseSTRING:
//Gettingthecellvalueasastringandprintingit.
System.out.print(cell.getStringCellValue()+"\t\t\t");
break;
//Numericcelltype.
caseNUMERIC:
//Gettingthecellvalueasanumberandprintingit.
System.out.print(cell.getNumericCellValue()+"\t\t\t");
break;
default:
break;
}
}
System.out.println("");
}
workbook.close();
}
catch(FileNotFoundException exception)
{
//Ifthereisnoexcelfilewiththegivennamefoundinthespecifiedlocation,it
throwsFileNotFoundException.
System.out.println("FileNotFound.");
}

11. catch(IllegalArgumentException exception)
{
//Ifthesheetisempty,itthrowsanIllegalArgumentException.
System.out.println("Sheetisempty");
}
catch(Exceptionexception)
{
//TodealwithotherExceptions.
exception.printStackTrace();
}
}
}
Output:
■Whentheexcelfilereadingissuccessful,theoutputwillbethewholefile.
■Whenthereisnoexcelfilewiththegivennamefoundinthespecified
location,theoutputwillbe:
FileNotFound
■Whenthereisnodatainthegivensheetindex,theoutputwillbe:
SheetisEmpty
Codetoreadaparticularcellvalueinthe
xlsxfileinJava:
importjava.io.FileInputStream;

12. importjava.io.FileNotFoundException ;
importjava.io.IOException;
importjava.util.Scanner;
importorg.apache.poi.ss.usermodel.Cell;
importorg.apache.poi.ss.usermodel.Row;
importorg.apache.poi.ss.usermodel.Sheet;
importorg.apache.poi.ss.usermodel.Workbook;
importorg.apache.poi.xssf.usermodel.XSSFWorkbook;
publicclassReadExcelFile{
publicstaticvoidmain(String[]args)
{
//CreatinganobjectfortheReadExcelFileclass.
ReadExcelFilerc=newReadExcelFile();
System.out.println("Enterthesheet,row,andcolumnnumberofthecell:");
//Gettingtheinputforsheet,row,andcolumnnumber,fromtheuser.
Scannerscan=newScanner(System.in);
intsheetnumber=scan.nextInt();
introw=scan.nextInt();
intcolumn=scan.nextInt();
//ReadCellData functioncall
StringvOutput=rc.ReadCellData(row,column,sheetnumber);
//Checkingwhetherthereturnvalueisnull.Themethodreturnsnullwhenthesheetor
cellisempty.

13. if(vOutput!=null)
//Printingthecellvalue.
System.out.println(vOutput);
}
//Methodforgettingthecellvalue.
publicStringReadCellData(intvRow,intvColumn,intvsheet)
{
//Variabletostorethecellvalue.
Stringvalue=null;
//Initializing Workbookobjectasnull.
Workbookworkbook=null;
try
{
//CreatingFileInputStream objecttoreadthefile.
FileInputStream fis=newFileInputStream("E:\\FirstCode.xlsx" );
//CreatingWorkbookinstancefor.xlsfile.
workbook=newXSSFWorkbook(fis);
//CreatingaSheetobjecttogettherequiredsheetinthefile.
Sheetsheet=workbook.getSheetAt(vsheet);
//Gettingthespecifiedrow.
Rowrow=sheet.getRow(vRow);
//Gettingthespecifiedcolumn.
Cellcell=row.getCell(vColumn);

14. //Gettingthespecifiedcellvalue.
value=cell.getStringCellValue();
}
catch(FileNotFoundException exception)
{
//Ifthereisnoexcelfilewiththegivennamefoundinthespecifiedlocation,it
throwsFileNotFoundException.
System.out.println("FileNotFound.");
}
catch(IOExceptionexception)
{
System.out.println("Invalidinputs.");
}
catch(NullPointerException exception)
{
//Ifthecellisempty,itthrowsaNullPointerException.
System.out.println("Nodatainthespecifiedcell");
}
catch(IllegalArgumentException exception)
{
//Ifthesheetisempty,itthrowsanIllegalArgumentException.
System.out.println("Emptysheet");
}

15. catch(Exceptionexception)
{
//TodealwithotherExceptions.
exception.printStackTrace();
}
//Returnsthecorrespondingcellvalue.
returnvalue;
}
}
Output:
■Whentheexcelfilereadingissuccessful,theoutputwillbethedesired
value.
■Whenthereisnoexcelfilewiththegivennamefoundinthespecified
location,theoutputwillbe:
FileNotFound
■Whenthereisnodatainthegivensheetindex,theoutputwillbe:
EmptySheet
■Whenthereisnodatainthegivencell,theoutputwillbe:
Nodatainthespecifiedcell
Codetoreadxlsandxlsxfilesinjava:
importjava.io.File;
importjava.io.FileNotFoundException ;

16. importjava.util.Scanner;
importorg.apache.poi.ss.usermodel.DataFormatter;
importorg.apache.poi.ss.usermodel.Sheet;
importorg.apache.poi.ss.usermodel.Workbook;
importorg.apache.poi.ss.usermodel.WorkbookFactory;
publicclassReadExcelFile{
publicstaticfinalStringFILE_PATH="E:\\FirstCode.xlsx" ;
publicstaticvoidmain(String[]args){
try{
//CreatingWorkbookinstanceforexcelfileforbothformat.xlsand.xlsxfile.
Workbookworkbook=WorkbookFactory.create(newFile(FILE_PATH));
//GettingthenumberofsheetsintheWorkbook.
System.out.println("Workbookconsistsof"+workbook.getNumberOfSheets()+"Sheets:
");
System.out.println("Enterthesheetindexnumber:");
//Gettingthesheetnumberfromtheuser.
Scannerscan=newScanner(System.in);
intsheetnumber=scan.nextInt();
//CreatingaSheetobjecttogettherequiredsheetinthefile.
Sheetsheet=workbook.getSheetAt(sheetnumber);
//CreateaDataFormattertoformateachcellvalueasString.
DataFormatterdataFormatter=newDataFormatter();
//UsingforEachloopwithlambda(Java8).

17. sheet.forEach(row->{
row.forEach(cell->{
StringcellValue=dataFormatter.formatCellValue(cell);
System.out.print(cellValue+"\t\t");
});
System.out.println();
});
//Closingtheworkbookobject.
workbook.close();
}
catch(FileNotFoundException exception)
{
//Ifthereisnoexcelfilewiththegivennamefoundinthespecifiedlocation,it
throwsFileNotFoundException.
System.out.println("FileNotFound.");
}
catch(IllegalArgumentException exception)
{
//Ifthesheetisempty,itthrowsanIllegalArgumentException.
System.out.println("Sheetisempty");
}
catch(Exceptionexception)
{

18. //TodealwithotherExceptions.
exception.printStackTrace();
}
}
}
Output:
■Whentheexcelfilereadingissuccessful,theoutputwillbeasdesired.
■WhenthereisnoExcelfilewiththegivennamefoundinthespecified
location,theoutputwillbe:
FilenotFound
■Whenthereisnodatainthegivensheetindex,theoutputwillbe:
SheetisEmpty
Additionaltips:
Ifyourexcelfilehasmorethanonesheet,youcangetthenumberofsheetsand
iterateoverthem.Refertothecodesnippetforabetterunderstanding.
Codesnippet:
workbook.forEach(sheet->{
System.out.println("=>"+sheet.getSheetName());
});
Toiterateamongthesheetsorrows,orcells,youcanuseoneofthefollowingways:
1.foreachloopwithalambdaexpression.

19. 2.foreachloop
3.Iterator
Idemonstratedeachiteratingwayinthesamplecodesaboveforyourreference.
Summary
ThereisanotherAPIforhandlingexcelfilesnamedJXLorJEXCEL.ButthisAPI
supportsonlyxlsfilesanddoesnotsupportxlsxfiles.Itslastupdatewasin2009.So
everyoneusesApachePOInow.IhopeyouunderstandhowtouseApachePOIto
readexcelfilesinjava.Thankyouforreading.