How to read excel in Java

hello and welcome I'm Raja and today we are going to learn how to read or how to get data from excel in Java and we will create a project and create the functions to read data from Excel files and we are going to use both xlsx and the older formats of XLS to get the data so we are going to learn how to add Excel libraries in the project how to create functions to get data we will also see how we can create a utility and then call the functions from a different class and then we will work with both the old and the new formats of Excel files so this is going to be very easy and very interesting and I will start from scratch from the very basics step one will be I will create a Java maven project and I'm using Eclipse you can use any Java ID so I have Eclipse here I will go to file new and go to others and select maven project and in Eclipse this in all the recent versions of Eclipse maven is already present just in case you are getting any issue you can get the maven plug-in you can also go to my website automation step by step calm and under the programming section you will get Java and you can also get all the videos on how to get started with eclipse and the basics of java so you can see Java installation and then eclipse videos here this is just in case you are completely new and you do not have Eclipse as well you can get that there so I will create a maven project I will say next and I will say create a simple project select next and I will say Excel you you can omit anything and here I will use the same name for artifact ID as well and say finish so you can see the project is created here and it has a form dot XML file where I can add the dependencies so here I have to create a tag for dependencies and if I say dependency press ctrl spacebar on my keyboard it is Auto completing I will select this and now within these dependencies tags I can add the dependency so for reading and writing excel files let me go to the maven central repository from where we can add the maven dependencies I will go here so here I will search for a path JPY Apache py is the library which is open source and is widely used for excel reading writing in Java and here you can see we have Apache py then we have from XML so I'm going to use this OpenOffice XML I will click here and then go to the latest version you can get whatever is the latest version at your time and copy the maven dependency from here so copy this and go to your palm toward XML and paste the dependency here I will remove the comment I will press control D to remove this line and to correct the identity I will select ctrl a and then press ctrl I so this will collect the adenosine so now I will also save the project so that it will download the dependencies and now you can see we have got a maven dependencies folder here if I expand this you can see all the Apache Pui and required libraries are added and this is the advantage of having a maven project now I do not have to do anything for management it will care of all this file management and library management in case a new version comes up I will just have to change the version here and I will save the project again and everything will be taken care of so we have created a java maven project and add Apache Pui maven dependencies in pom dot XML now step number three will be create a package and create class so I'm going to go to the associate tests Java folder do a right click new and I will create a package and I will name this as utils I will keep all the excel reading writing classes in this utils package so it is good to have a separate package for this and this can be common you can use it anywhere in any of your Java projects and inside the utils package I will create a new class and I will name this as Excel utils you can name it anything and save and finish so the class is created here now let me increase the font I will press ctrl + + on my keyboard so now I can start writing functions here so here step number four is create a function to get row count and actually before this I should be having a excel file created so I will say create a excel file and add some data and then this is step number five so I will go to my project itself and here I will go to the project folder do a right-click and say new and first create a new folder and I will name the folder as data and say finish so let me just close this and say finish yes this is done and you can see that data folder is created here now inside the folder I will be creating a excel file so I will go to the location of this folder I will do a right-click properties and this is the location and I will go inside data folder and now I will create a new excel file so if I do a right click new I have got an option for creating XLS and xlsx worksheet now in case you are having Microsoft Office on your system you can create an excel file in Microsoft Office just in case you do not have Microsoft Office there is a free option you can go to google and search for WPS office this is a very good software and works exactly same like Microsoft Office and actually the file extensions are also same so you can just download it for your operating system or Windows Mac Android it works on everything and then it is exactly like Microsoft Office the extensions are also same and you can actually use your existing Microsoft Office files like excel PDF Word everything with WPS office in my case as well I have WPS office I will do right-click and new I am using the latest format that is xlsx worksheet and let me see I will name this as test data and save and open and Here I am going to add some data let us say I will say I will add the data for name place and let us say age and here I say Henry Ford and age here and I will save and now come back to my project and here I will refresh the project so that the file is shown here I will do a right click and refresh and now you can see the excel file is here the excel file is here the reason I am getting two entries is because the excel file is already open and therefore it is shown this here once I close the file this will go away so I have created an excel file with some data and now I will create a function to get the row count so here I will say I'll create a function public void get row count and now because we are using the latest format of Excel I will say X SSF workbook if I press ctrl spacebar on my keyboard I'm getting this option from bajji Apache a B Y I will use this and you can also see it has imported the required classes here so everything is fine and I will give a variable let us say workbook equals new x x SF workbook now here I also have to provide the location of the workbook that is the file path so for that I will create a string variable and say excel path and now I will give the location of the excel or the path of the excel now here in our case because I have created a folder within the project folder and I have created my excel inside this so I can directly say dot forward slash and folder name and the file name just in case you have kept your file at some different location you will have to give the entire path here and that is not recommended because in case you change the location of this project or take this project to any other system the path will break now to handle that one of the ways you can do is you can get a the location of so the best way is to put the file within the folder inside this particular project now there are two ways to get this folder one is this way I can just say dot and then the relative path or if this does not work for you you can first get the path of the project by using system dot get property and you can use the property user dot dir and let me save this into a variable I will say project dir is this and then let me also print it I will say sys oh and control spacebar to autocomplete the print statement and I am printing this project directory here so let me run this and check to run this from the same class I will have to use a main method so I will create a main method I will say main control spacebar to autocomplete and this is the main method and to call this function I will also have to make it static because main is static and I will call this get row count and do a right click run as java application and you can see it is giving us the project path and now I can just say here project dir plus and I will remove the dot so this will also work but if the dot works for you that is better I do not have to do this extra I will not have to add these extra statements here so for us this should work fine so I will remove this as well so now we have got of a workbook this is workbook and now to get the sheet I will say XSS F sheet and again if you press ctrl spacebar you can get it from this Apache Pui and I will name this as sheet equals now here I will use this workbook variable and I will say dot get sheet now you can see we have multiple options I can get the sheet by sheet name or can also get by sheet index I am going to get my sheet name so if I check here the sheet name is sheet 1 so I can use this if you want to get my index you can use index 0 because index position starts with 0 so I will just go and say I want to get a sheet by name sheet 1 and now to get the row count I will say she taught I can say get last row num or i can also say get physical number of rows and this should give me the row count i will store it into a variable called row count and then i can print to verify I will say number of rows and I will print the row count here and to check I will run this and some exception let me check let me save this I will see this and now if I run it again so there is some exception let me quickly check this so here okay I think we have to add our try-catch block here I will add a try and then close the try block and add a catch block and catch the exception here I will say exception exp and then I will say here now just in case you do not know what I am doing this is I am doing exception handling you can again go to my java tutorial here and you will find our video on exception handling so you can see exception handling is here so I am just uh catching the exception here and I will say here let me check real quick yes I will say here I will print out exp dot get caused exp dot get message and then I will print the stack trace exp dot print stack trace so if I run it now let us see so it is showing us null if I see this is fine Excel path is data test data dot xlsx okay so I got the issue the reason is we have not provided the path in the workbook so we have created the axel path here but we did not provide it here so I have to provide the path here and now I will run this again and yes now we are getting the number of rows is too so this is working fine now I will also need a function to get the cell data so step number six is create a function to get cell data so as of now we have only got the row count we actually need to get the data from the excel so for that I will create a function public static void get cell data and here these three statements will remain same because I again have to get the workbook and the sheet so I will just copy and paste these three and here I will also have to surround by try-catch otherwise I will get an exception or I can also use throws keyword so if I do that let me just show you I can say here so if I hover over this you can see it is saying add throws declaration horse around with try catch I will say head throws declaration so this will also work and then I will say here I have got the sheet I will say sheet dot get row and I will give the rule number let us say rule number is 1 and if you go to your excel the index position starts with 0 so this is Rho 0 and this is Rho 1 this is column 0 and this is column 1 so I will say get row 1 and then to get the column I will say get cell and the column number let us say 0 and then I will say because I am I can say get boolean get cached so many functions are there because it is a string value I can say get string value I will also show you how you can get any value whether string or integer or any data type so because it is a string value I will show in a string variable I will say string value equals this and I will print out the value so I can just print out the value like this and now to call this function I will have to call from the main method so I will call it from the main method and now here okay the error is because I am throwing the exception here I will also have to throw the exception in the main method as well horse around by trycatch so let me just throw it from here as well now I will run this and you can see it is printing the data here now just in case I want to get a numeric data which is let us say this age here so if I try to get this I will say get cell 2 and run it so here you can see I am getting in getting an exception and it says cannot get a string value from a numeric cell so for that I will have to say here instead of get string value I can say get numeric cell value and then I will have to store it into a integer or I think it will accept a double so if I store in a double data type and I run it again you can see now it is running fine however it is converting into a decimal and I do not want this I can do some typecasting but a better way will be so that I do not have to use different functions for different data types I can use a data format and this is again coming from Apache Pui I will use the data for matter I can give any variable name let us say format ax equals new data for matter and then I can say format a dot get or I will say format cell value and I have to give the location so I will say she taught get true and dot get cell here and that's it now this will get any data type whether a string or an integer or a boolean or a decimal so I will have to store it into a object data type I am using object data type so that it will store any kind of data and then I will try to print it so let me run this again and you can see now it is printing file now even if I have a string data let me check and if I run this now it is running fine in fact now I can get any data suppose I have something like this a decimal data and I try to get this I will say gets l3 and if I run this you can see I am able to get this so now this will work for every type of data now I have got a function for getting the row count and getting the cell data now I will create a constructor I will say create a constructor and I will show you what it is and how to do it and get pixel path and she as arguments so what I'm going to do is a constructor is a function which has the same name as that last name and does not have a return type so if I say public and without a return type I give the function name same as class name and create this function this becomes a constructor for this class and a constructor is always called whenever you create an object of the class using the new keyword and I will show you in a moment why we need this so I'm creating a constructor and I'm getting two arguments here I will say string Excel path and string sheet name so the reason I am doing this is we want to force the user whenever you create an object of this class you will always have to provide n the excel path in the sheet name and I will store and create the workbooks here so all these things that I am doing inside the function I will move it inside the constructor so here I am using the excel path and the sheet name as well instead of hard-coding I will get it from the calling function or the whenever an object is created so I am doing this again I will have to surround with a try-catch block or add a throws declaration let me just surround with a try-catch here okay so now I am setting the workbook and sheet here and to make it because these are local variables to this function to make it global I will put them at a class level I will copy it from here and go to the class level and provide it here and also make it static so that it can be refer referenced from any function and the same thing I will do for sheet I will put it here and again make it static and I can remove this from here so you can see now it is become a class variable and it changes color to blue so we can know it is coming from that last variable and now I can remove from the functions I do not need all this because all this will be set in the constructor and we can refer directly you can see the sheet is now getting referred directly here I do not need all this so you can see the get cell data function is reduced only to these three lines and the same thing for get row count I do not need all this and in fact I do not even need a try-catch it is good practice to keep the try-catch block so that in case of exception you can handle it properly but for now I am just removing the try catch as well because we are having the try catch at the constructor also so here the get row count function is just reduced to these two lines and I do not need the main method as well I use the main method just for demoing and checking everything is working fine now we are anyways going to call it from a different class so this is now our complete class let me just show you this is our complete class we have the constructor here where we are getting Excel path and sheet name and we are setting workbook and sheet here and then in the functions we are just referring the workbook and the sheet and then we are getting the row count here and then getting the cell data here so now this is fine I will now be creating a new class create a new class and call the Excel utils functions so this is what we will be doing in real world we will be having this as a separate utility package and then from other classes we will be calling this so let me create a new class within utils package you can create it anywhere I will create a new class I will name it let's say Excel utils test and say finish and now in this class I will create a main method I will say main control space bar and select main method and here I will create our object of the Excel utils class so I will say I will say Excel utils and give any variable name Excel equals new Excel utils and now you can see it is forcing us to provide excel bath and sheet name and I cannot instantiate or create an object without giving these two arguments and that is the reason we created the constructor so that whenever our object is created using the new keyword we can force the user to provide these two arguments so let me create a string variable for excel path and I will say this is equals to the path of our excel file which is dot /data /index and the sheet name equals to sheet 1 so this is fine now I can use this excel object or object reference to call any functions of the Excel utils class so I will say excel dot and you can see I am getting the option I can call get row count and I can also call get cell data and one thing I forgot is I have to remove the hard-coding of row number and column number in the get cell data function so you can see this row number and column number are actually hard-coded so I will say here in the brackets of this function declare I will say get the rune um and get the column nom I am getting these two arguments and this I will refer in place of the hard-coded values for row number and column number and I will save it and now if I go here you can see this is getting an error because now I will have to provide the arguments as well so I will say row number 1 column number 0 and now I can reuse it any number of times so let me see what is the ok so let me just add the throws declaration ok and I can use it any number of times I can actually also put this in the loop so let me just try it out here I will get the data from all these columns and check so I will run this run as java application and now you can see I am getting all the data here so I can now use it now let us see step number nine is how to work with dot XLS format so just in case you have an older format of excel file that is dot XLS so let me just go and save this file as a older format I will say save as and I will select the this format dot XLS and save so if I go to the data folder now you can see we have two files one is in the new format and the other one in the older format and they have the same data so for that what I will have to do is I will go back to my excel utils and instead of X s SF workbook I will be using SSF workbook and HS SF worksheet and I will have to import them again from Apache py so I will import this and I believe bought this and you can see it is imported I will no longer need these imports and then also here I will have to use the HSM workbook and in this older format we cannot directly give the excel path like this so for that what I will have to do is I will have to use a input stream I will say input stream from Java IO and I can give some variable like file and I can say new file input stream and here I will provide the location of the file which is Excel path here and then inside the workbook statement here I will say new we have this py EFS file system class I will use this and I will pass the file variable which is this one so this is how it will work in the older format now if I check again I will change the format here or I will use the XLS file here and then try to run this again and you can see now it is working for the old format as well so this is how you can use the Java functions and libraries to read data from excel in any java project i hope this session was useful if you liked the session hit the like button and also share with others and do share your knowledge with everyone you can also subscribe to this channel to receive new videos I hope all this was very useful to you thank you for watching

Channel: Automation Step by Step

Views: 30,030

Keywords: how to read excel in java, java how to get data from excel

Id: B4G2tMDYjRQ

Length: 33min 37sec (2017 seconds)

Published: Thu Mar 26 2020