Apache POI Tutorial Part1 - How To Read Data From Excel #ApachePOI
Apache POI Tutorial Part1 - How To Read Data From Excel #ApachePOI
Topic : How To Read Data From Excel
#########################
Udemy Courses:
#########################
Manual Testing+Agile with Jira Tool
► https://lnkd.in/gNqhEq9K
Selenium with Java+Cucumber
****************************
► https://lnkd.in/g5YcAQyR
Selenium with Python \u0026 PyTest
► https://lnkd.in/gvrpyYan
Selenium with python using Robot framework
► https://lnkd.in/gxsDf8zR
API Testing(Postman, RestAssured \u0026 SoapUI)
► https://lnkd.in/gHKQ2v2e
Web \u0026 API Automation using Cypress with Javascript
*
► https://lnkd.in/gcZz5Psx
Playwright with Javascript
► https://lnkd.in/g-ePPav6
Jmeter-Performance Testing
► https://lnkd.in/gA3eiZPY
SDET Essencials(Full Stack QA)
*****************
► https://lnkd.in/gmBc4-wD
Appium-Mobile Automation Testing
► https://lnkd.in/gi_UiRTA
Java Collections
► https://lnkd.in/gZftiiEn
Python Programming
► https://lnkd.in/ghP6WHQz
Cucumber BDD Framework
► https://bit.ly/3F7d69r
Protractor with Javascript
► https://lnkd.in/gZnJ2qsS
####################################
Youtube Playlists:
####################################
Manual Testing \u0026 Agile
► https://bit.ly/3ujvaYa
► https://bit.ly/3OYzxQj
SQL
► https://bit.ly/3R6XjeG
► https://bit.ly/3nE0a1s
► https://bit.ly/3IeKuuJ
linux \u0026 Shell Scripting
► https://bit.ly/3up1Z6a
► https://bit.ly/3nzvGxx
Java
► https://bit.ly/3R9QfOs
► https://bit.ly/3AoRMKM
► https://bit.ly/3IbTTDn
► https://bit.ly/3ybXInv
► https://bit.ly/3yCoHdw
Selenium With Java+Cucumber
**********
► https://bit.ly/3P2FMm4
► https://bit.ly/3yhiIt4
► https://bit.ly/3uokzet
► https://bit.ly/3a7XIND
► https://bit.ly/3umN2S9
► https://bit.ly/3aar7GW
► https://bit.ly/3y9kD2S
► https://bit.ly/3uhLskn
Python
► https://bit.ly/3agNgU0
► https://bit.ly/3NJNnoy
► https://bit.ly/2CTQ4mR
► https://bit.ly/3OIYWh1
Selenium With Python,Pytest\u0026Behave
► https://bit.ly/3OHHoC9
► https://bit.ly/3IeNLdv
► https://bit.ly/2J4tPeT
► https://bit.ly/3ydSkAq
Selenium With Python Using Robert Framework
(Web\u0026API Testing)
***********************************************
► https://bit.ly/3nUvlpr
► https://bit.ly/3nUvE3z
API Testing (Postman,SoapUi,\u0026Rest Assured)
► https://bit.ly/3OXacWY
► https://bit.ly/3yh0UhE
► https://bit.ly/3nC9DWQ
► https://bit.ly/3yGfhNS
► https://bit.ly/3OJa11H
► https://bit.ly/3P2slCC
Mobile App Testing Appium
**************************
► https://bit.ly/3Al49HG
Performance Testing Jmeter
► https://bit.ly/3nz4fE7
Maven,Jenkins,Git,Github,CI/CD
*****************************
► https://bit.ly/3P16HPj
► https://bit.ly/3yhlUVA
► https://bit.ly/3afYfwT
► https://bit.ly/3yGzweG
► https://bit.ly/3yF73Ws
► https://bit.ly/3NCOmH7
SQL,DB Testing\u0026ETL,Bigdata
► https://bit.ly/3NKOcNY
► https://bit.ly/3OVpDyI
► https://bit.ly/3NEvUy0
► https://bit.ly/3IryxlT
► https://bit.ly/3RkWBLh
JavaScript Based Automation Tools
****************************
► https://bit.ly/3nUx51X
► https://bit.ly/3AqI6Q0
► https://bit.ly/3yeurbT
► https://bit.ly/3ujzpTK
Selector Hub Tools
► https://lnkd.in/gfqbR5TC
GraphQL
*****
► https://lnkd.in/gZZNCc5q
Cypress API Testing
► https://lnkd.in/gc2STVEm
Cypress Web Testing
► https://lnkd.in/gW8SgMna
Playwright with Javascipt
**********************
► https://lnkd.in/gQZwaaVD
#ApachePOITutorial
#ExcelDataReading
#JavaExcelTutorial
#POIExcelBasics
#ExcelDataManipulation
#ReadExcelInJava
#POIExcelGuide
#ExcelDataReader
#JavaPOIExample
#ExcelParsingTutorial
#POIWorkbookRead
#ExcelDataExtraction
#JavaExcelPOI
#POIExcelDemo
#ExcelFileHandling
#POIExcelFunctions
#ExcelDataAccess
#JavaPOIReadExcel
#POIExcelTips
#ExcelDataVisualization
#POIExcelMethods
#JavaExcelParsing
#POIExcelTricks
#ExcelDataReaderPOI
#POIExcelWalkthrough
#JavaExcelManipulation
#POIExcelTutorials
#ExcelPOIExamples
#POIExcelTechniques
#JavaExcelDataRead
#POIExcelGuide
#ExcelPOIUsage
#POIExcelStepByStep
#JavaPOIWorkbook
#ExcelPOIHandsOn
#POIExcelExplained
#JavaExcelPOITips
#ExcelPOIDataExtraction
#POIExcelCoding
#POIExcelHacks
#JavaPOIExcelParsing
#ExcelPOIWalkthrough
#POIExcelFunctions
#POIExcelDataAccess
#JavaExcelPOIDemo
#POIExcelTricks
#ExcelPOIManipulation
#POIExcelExamples
#JavaPOIExcelTutorials
#ExcelPOIReadSkills
Content
1.04 -> hi everyone welcome you all this is pawan
so from today's session onwards we are going
6.72 -> to see how we can work with apache boy and
first of all let us try to understand what
12.88 -> is apache poi and where exactly we use it and
what is the importance of this in automation
20.24 -> so first let us start with what is apache
poi so apache paw is an api provided by
26.08 -> apache foundation which is a collection of
different java libraries and these libraries
32.24 -> gives the facility to read write and manipulate
different microsoft files such as excel powerpoint
38.72 -> and ms word files and apache po is basically
designed as an api which contains a number of
44.96 -> java libraries and we use it whenever you want
to work with microsoft documents so for example
51.84 -> i want to work with excel file let's say i want
to create an excel file i want to create multiple
56 -> sheets i want to write some data in excel or
similarly i want to read some data from excel
62.24 -> right so these kind of operations if you want
to perform through java so we use apache poi
67.76 -> package and which is basically an api and
which is implemented by apache foundation
73.2 -> and this is completely open source and we can just
download and do configuration and start using it
79.84 -> right so what is the importance of this apache
poi especially in automation so in automation
85.76 -> we have something called data driven testing so
what is data driven testing means so we'll run
90.4 -> the test case or we'll execute this test case with
multiple sets of data and where exactly we specify
96.24 -> the data so data will be specified in the uh
some third-party files like it can be a text file
102.32 -> so it can be csv files or it can be excel file
sometimes we can take the data from the databases
107.52 -> but but among all these sources excel file
will play very very important roles most of
112.08 -> the times we use excel as a data source where we
can put all the data we prepare all the test data
118.08 -> and we use the test data in our application so to
do all these things we need to make use of apache
126.48 -> point and by default selenium doesn't support a
excel file we don't have such classes or methods
132.08 -> in selenium because it is only meant for web
testing but if you still want to automate or if
138.16 -> you want to do some kind of data driven testing so
we need to work with excel file so to achieve this
144.88 -> we use apache poi so this is a third party api
which is provided by apache foundation and which
150.56 -> we can integrate with our selenium then we can
achieve data driven testing so now we'll see what
156.72 -> is apache poi so what it contains what are the
different classes or interfaces provided inside
162.4 -> so apache poi is having mainly four interfaces
especially when you're working with excel file
168.32 -> these are the four interfaces we use
workbook interface sheet interface
173.84 -> a row and cell so when i take in any excel
file right so what excel contains mainly
179.2 -> four components excel file contains a workbook the
workbook contains the multiple sheets every sheet
184.96 -> contains a multiple rows and each row contains
a multiple cells so this is the hierarchy of the
189.92 -> excel so for each component in excel they have
created a separate interfaces like workbook is
196.96 -> one interface sheet is another interface row
is another interface cell is another interface
203.2 -> and these interfaces have implemented by using
different classes suppose if you are working
208.48 -> with microsoft excel before 97 version and in
that time the file extension will be dot xls
216.56 -> so if you have an excel file with this extension
you have to use these classes hssf workbook
221.84 -> hssf sheet ssh of row hssf cell so these are
the different classes which i have implemented
229.68 -> which will support only microsoft until 97 and
the newer versions of microsoft excels we have
238.16 -> a different type of extension that is xls ex and
if your excel file is having this extension xls ex
245.36 -> then you have to use these classes so the
same interfaces have implemented by using
250.4 -> different classes like xssf workbook excesses
of sheet excesses of row and excess of cell
257.12 -> so these are the different classes which have
implemented these interfaces right so these are
263.04 -> the interfaces and classes which are available in
apache point now each class is representing one
268.64 -> component in excel so for example in excel file we
have a workbook so xsf workbook is a class which
274.8 -> is representing workbook and xssf sheet which
is a class which is representing the sheet in a
280.64 -> workbook then xsf row which is another class which
is representing the row in a sheet similarly we
287.6 -> also have excess of cell this is representing a
cell in the particular row so these are different
294 -> classes which are implemented these interfaces now
we'll see practically how we can use these classes
302.8 -> and to work with the excel file okay so most
of the times so what are the different type
308.4 -> of operations we can do in excel so we have some
data in excel and we can read the data from excel
314.4 -> and sometimes we can create our own excel
file and we can create some data in excel
319.12 -> and sometimes we do some validations on the rows
and columns and sometimes we'll apply the some
324.96 -> styles like we'll update some cells with color
and we will increase the font decrease the font
330.48 -> do some calculation so these are the common
operations we do on excel right excel sheet so
335.76 -> all these things we can do by using apache point
now we'll see the basic operations what we can do
343.52 -> which is very helpful in our automation now
let us start with this so normally when i start
350.4 -> doing some operational excel the first
operation which i'm going to show you
353.92 -> is reading data from excel so i'll maintain
one excel file and i specify some data in it
361.68 -> then i'll show you how we can read the data
from excel using apache point now let's go to
368.56 -> eclipse and here i'm going to create a new project
so let's go to file new java project i'm creating
377.44 -> and i'll name it as apache poi and then finish
so now i created a new java project in my eclipse
390.88 -> so now it is creating a new project so once you
created your project this is simple java project
396.4 -> now we need to work with apache pi so for that
we need to download the libraries so from where
402.72 -> we can download the libraries is we need to go
to official website if i just go to apple cpui
408.24 -> and this is a official website apache pipe and if
i just look at this page you can find something
415.12 -> called there are multiple links are there and
home java docs if i just click on the java docs
421.6 -> so they will provide the documentation and if i
just go to the download link and you can see here
427.6 -> available downloads and in this section you can
find the the latest stable releases apache pipe
434.24 -> 4.1.2 so this is a latest official version you
can directly click on this link or if you just
441.12 -> come down to binary distribution you can find the
one more zip file you can download from anywhere
447.76 -> so let me just click on this link and once you
click on this link which will start downloading
454.16 -> apache poi so when i click on this which will
start downloading yeah now it is go to this
459.92 -> section actually so when i click on this zip file
which will start downloading the apache boy so now
464.72 -> it is again navigating to next page so let's click
on the either this one or this one one of the link
471.76 -> so once you click on this which will
download a zip file now which contains
476.24 -> n number of jar files and jar file contains
uh so many libraries and classes and methods
482.88 -> so we have to use those classes
and methods to work with excel file
487.12 -> so let me open this now i got the zip file so once
you've got the zip file just extract this [Music]
495.6 -> so once you extracted this which contains
all the binary files so let it extract
511.44 -> okay so now i have extracted my zip file into
a folder so if i just open this folder you can
517.28 -> see one more folder pi 4.1.2 which is the latest
version now open this you can find multiple jar
523.28 -> files and along with them in the oxml iphone leave
you can see few more jar files then again inside
530.8 -> the lib folder you can see few more jar files
so we need to add all the jar files to your
536.56 -> java project to get all the libraries so meanwhile
what i can do is i'll just copy the folder as it
542.72 -> is i can put my common place where i will maintain
all the drivers so go to my driver folder so i can
548.56 -> just create one more driver folder here and i'll
keep all the drivers in one place so if i just
553.52 -> look at here i copied all the already that folder
so pyi beam 4.1.2 which contains all the jar files
560.96 -> now we need to associate these jar files to your
java project now go to eclipse now right click on
566.96 -> your project whatever it created go to properties
and go to java build path and here we have to add
575.12 -> external jars and go to apache boy so here we
need to add all the jar so first we need to
582.16 -> add these jars open and then go to add external
charts one more time go to oxml and select these
590 -> jar files one more time additional charts go back
go to lib folders select all of them and say open
597.84 -> so now we have added all the jar files to your
java project and then say apply and close now
603.36 -> my project setup is ready with apache poi so if
you have a normal java project you can manually
609.68 -> download the jar files then attach those jar file
in the java build up suppose if you are creating
615.52 -> maven project then you need to add dependencies
in the xml so in those cases you can get the
621.84 -> dependencies from a1 repository so if i just go
to mavenrepository.com and here search for apache
630.56 -> file and when you search for apache pi
let me just give proper syntax apache poi
639.76 -> right so now we can see multiple packages are
there so this is the one package pyo xml and
646 -> only poi so these are the two things which we need
to add in the palm.xml so once you click on this
651.04 -> you will get one dependency so this is our latest
version 4.1.2 and you need to add this dependency
656.8 -> in the palm.xml if you are using maven project
this is a one dependency and there is one more
662.4 -> dependency you need to add called only by apache
pi so if i just click on this again go to the
669.2 -> same version now we need to add this is also so
these are two dependencies you need to which you
673.92 -> need to add if you are creating a maven project
okay all right so now we have done with project
680.64 -> setup so now i'll show you the first operation
in excel that is how to read the data from excel
687.28 -> okay so how to read the data from excel so
for that what i can do is i'll just create
692.48 -> one excel which contains some data then i'll show
you how many ways we can read the data from excel
698.72 -> by using apache poi so inside our project
itself i'm just creating a new folder
705.12 -> and i'll name it as data files and all
excel files i'll create inside the folder
711.12 -> so inside the folder okay so i'm just going to
have one file so i already have some data file
719.52 -> let's say i have countries excel file so i'm
just putting here so this is my countries.xls
725.2 -> file so let me open and show you so once you
open this excel file which contains some data
731.92 -> so it is opening inside the eclipse i
don't want to open inside the eclipse so
736.88 -> what i will do is i can just do some settings
just hold on so instead of directly open what you
743.76 -> can do is just right click open with uh instead
of default editor i can just say system editor
753.84 -> then it will open the new excel file so this is
excel i have some data in it so just look at here
760.72 -> i have some countries and i have some capitals
and then population so now i want to read this
766.48 -> data and i want to print this data in my console
window so how we can read it so now we'll see that
773.52 -> so this is my excel file which i am going to read
and my excel file name is countries.xlsx and name
780.08 -> of the sheet is sheet1 i have it three columns
country capital and population now let us see one
786.08 -> by one so step by step how we can achieve it so
let's go to src and i am creating my own package
792 -> i say excel operations let's say finish
now let me just create a new class
801.68 -> new java class i'll name it as
reading excel let's take a main method
811.2 -> okay so now we'll do step by step how we can read
the data from excel file so there are many ways
817.2 -> are there i'll show you mainly two different
ways so we can use a for loop method which
822.56 -> will read the data from excel and we also can use
iterator method so both approaches i'll show you
828.56 -> so before that first of all uh we need
to first get the location of the file so
834.32 -> let's create one variable called string say uh
excel file path i'm just getting a one variable
842.48 -> excel file path equal to and where exactly my
file is present so my file is currently available
848.72 -> in my project itself in my project data files
inside the data files folder my file is present
856.32 -> so dot slash slash is representing your
current project location inside this i
861.84 -> have a folder called data files inside this you
have a excel file which is countries dot xls
869.68 -> so i'm just putting the name of the file so
this is the location of the excel file okay now
878 -> first thing is we need to we already have this
file so just we need to read the data so first
884.16 -> we need to open this particular file by using file
input stream so we need to create a stream through
891.12 -> that stream we can read the data okay so here we
have to use input stream file input stream class
898.8 -> which will create a stream to this file and we
can read the data so when i open the file in the
904.4 -> reading mode we use file input stream class so let
me open file input let me write file input stream
912.64 -> and i'll name it as input stream equal to new file
input stream new file input name so for which file
923.36 -> we have to open the screen so this particular
file so that we have to pass as an argument
929.76 -> so this will we need to import file input stream
from java.io and this will throw some exception
935.68 -> just add so now i open the file in the stream mode
so now i need to get the workbook from the file
945.2 -> then i need to get the sheet then i need to
get the rows and then finally you can read the
949.28 -> data from the cells now this is step by step
activities let me do that so first we created file
955.76 -> path and then we connected the stream to that file
so input stream is representing the file and then
962.32 -> we need to get the workbook from this file
so by using input stream so let's take this
969.36 -> uh let's create a workbook object we have a class
in apache poi that is xssf workbook so this is a
979.12 -> a class which is available so for that
i am just creating one object variable
983.84 -> let's say workbook now i want to
get the workbook from this file
989.12 -> and refer that workbook by using this variable
so for that what you can do is we have to create
996.16 -> excessive workbook object as a new exssf workbook
and inside this we have a constructor that
1004.32 -> constructor will take the file path input stream
so we need to pass it so what this statement will
1012.08 -> do is first we get the location of the file and we
refer that file by using file input stream so that
1018.4 -> we are going to open the file in the reading
mode then we get the workbook from the file
1025.6 -> so access of workbook have to import from apogee
poi or apache poi so if i just look at here
1032.88 -> xsf xss f workbook okay this is exact class
name now import it so we have to import this
1041.04 -> from orgy apache.poi.xssf dot user model this is
a package and this is a class we have imported
1050.4 -> so all the classes like access of workbook access
of worksheet row xsf row excessive cell all the
1057.44 -> classes are present in the same package so instead
of specifying each and every class i say star
1063.76 -> and then we need to add this exception so
from that file we open the stream and we got
1070.8 -> the workbook from the file so once we get the
workbook from that workbook we have to get the
1075.84 -> sheet so workbook dot there is a method called get
sheet [Applause] workbook dot get sheet so this
1085.04 -> particular method uh will get the sheet so we have
two methods are available so when i say get sheet
1092.08 -> we have to specify the name of the sheet and this
will return the sheet object so we have to create
1099.36 -> uh excesses of sheet i say sheet equal
to and this is again from apache pi
1106.56 -> so from the workbook i extracted the sheet
and referring that sheet with the object so
1112.88 -> if you still want to like if you want to pass an
index we have one more method that is for example
1119.12 -> you can write the same statement like this instead
of get sheet method you can use get sheet enter
1128.32 -> so in that case you have to pass index number
so index is always start from zero so either
1133.68 -> you can use the sheet name or you can use index
name so now we got the sheet from the workbook
1140.8 -> so once we get the sheet now the sheet contains
a rows as well as cells now we have to read them
1147.76 -> so first method am going to show you is using for
loop okay using for loop how we can read the data
1156.4 -> from the sheet all the rows and columns so to do
that we need to find out how many rows we have
1163.28 -> how many columns we have in the excel sheet so
that is a prerequisite so let me take this sheet
1170.08 -> dot there is a method called get a last row num
and this will return the last row number that
1176.56 -> is exactly equal to number of rows so i'm storing
this value in one more variable called inter rows
1186.24 -> and similarly i'll create i want to find out
the cells number of cells in the particular row
1192.24 -> so that is representing the columns so for that
what i can do is i'll use sheet dot get row
1199.76 -> so whatever the object we created for sheet
dot sheet dot there is a method called get row
1207.2 -> and this method will get the row from the sheet so
which row you want to get let us give some number
1213.52 -> let's say i'll give one so first row and inside
that row i want to find how many cells we have
1220.32 -> so i'll use one method called get last cell num
so that is exactly equal to number of columns in
1227.28 -> that particular row and i'll store that value in
one more variable okay so now i got the number of
1235.2 -> rows and number of columns in excel so accordingly
we have to write two different loop statements
1241.52 -> which will read the data from the xr so let
me write one for loop this is one for loop
1246.64 -> and this is another for loop and one for loop is
representing the rows and one another for loop is
1251.6 -> representing the columns now outer for loop is
representing the row so where exactly we have to
1258.48 -> start so enter r i am starting from zero row and
how many times we have to repeat this depends upon
1265.76 -> the number of rows and then increment i r value
every time i need to increment the row value
1273.04 -> so once you start this loop before starting
the inner loop because inner loop is
1278.8 -> uh representing the cells in excel or in sheet so
before reading the cells by using inner for loop
1286.48 -> we have to get the row from the sheet so
we have to get the first row from the sheet
1291.52 -> in that row from that row we have to read all
the cells after completion of all the cells in
1297.04 -> the first row then again we need to go up and
get another row and again read all the cells
1302.96 -> so similarly for each row the inner for loop will
execute multiple times which will read the data
1309.6 -> from the cells so before starting inner follow we
have to get the row from the sheet so first row we
1316.24 -> have to get so how to get the row is same method
so use a sheet object dot get row get row and
1325.28 -> inside the get row we have to pass a row number
that is r now this will return the row so this
1332.96 -> particular method sheet dot get row method will
return the row object so when it returns the row
1338.64 -> object that means we have to store that row object
in a variable now the type of this variable is xss
1347.04 -> f row object so now i got the first row into
this variable now this variable is representing
1355.92 -> the first row and from that particular row i want
to read all the cells now come to inner for loop
1362.64 -> and here i'll start from 0 again and see
less than how many times we have to repeat
1370.56 -> again number of columns and then c plus plus so
here we got the row anyway so from this row we
1379.44 -> have to extract all the cells take this row object
dot dot there is a method called get the cell
1388.4 -> and this particular method will return the
particular cell from the row because one row
1394.56 -> can have multiple cells so this method will
return the cell so which cell it will return
1400.56 -> c so whatever the c value are maintaining for cell
cell number i'm passing here so row dot get a cell
1407.52 -> of c so this method will return the cell object
and we can store that cell object in a variable
1415.12 -> and the type of this variable
is xsscf cell object variable
1422.08 -> right so you can just look at here all the classes
we have used so far so access is a workbook xss of
1428.96 -> sheet and then we have seen excess of row and
excessive cell so all three all four classes we
1435.28 -> have used so excesses of workbook is representing
the workbook sheet representing the sheet excesses
1441.84 -> of your row representing the row object excesses
of cell representing the cell object so here we
1446.8 -> just get the cell object from that cell object
we have to get the data so how to extract the
1453.6 -> data from the cell object so before extracting the
data from the cell object we have to find out what
1460.64 -> kind of data we have in that that's most important
so if you have all the data like string format we
1469.04 -> can use a method called tostring but if you have
a multiple type of data if i just look at this our
1474.32 -> excel file i'm opening this excel file so you can
just look at this i have a different type of data
1480.8 -> so the countries or strings capital or strings
population is number different type of data i
1485.68 -> have you may have a boolean also right so depends
upon the type of data we have to read the cell so
1492.8 -> for that we need to find out what is the type of
the cell so how to know type of the cell heroes
1499.04 -> i can simply say cell dot get cell type cell
dot get cell type so this particular method
1508.8 -> cell type so this method will return the type
of the cell so normally what is the type of the
1513.92 -> cell the type of the cell can be string or it can
be numeric it can be boolean or it can be formula
1519.28 -> or whatever so depends upon the data the type
will be automatically assigned to the cell so
1525.6 -> once you get the cell object here i want to
check the type of the cell and based on the
1532.56 -> type i will read the data if it is a string type
i'll use get string cell value method if it is a
1538.72 -> number type then i'll use get a numeric cell value
method so depends upon the type of the cell we use
1543.92 -> particular method so here i have a multiple
conditions so that i can put in the
1548.96 -> switch case statement so after getting the
cell immediately what is what will do is this
1555.36 -> statement i'll put in the switch statement so i
can create one switch cell dot get type i'm taking
1562.8 -> and if the cell dot get type right so
equal to case let's say if it is a string
1570.8 -> if the cell dot get type is a
string then i have one method called
1576 -> system.out.pintln i can use one method called cell
object dot get a string cell value get the string
1584.64 -> cell value so this method exactly we use if the
type of the column is or type of the cell is
1590 -> string suppose if the type of the cell is number
so as soon as we have done this we can break
1596.56 -> we can come out from switch and suppose
if the type of the cell is a numeric okay
1603.92 -> numeric and then we have to say system.intellin
and from the cell object we have to extract the
1612.88 -> value by using get a numeric cell value and then
break okay similarly suppose if the cell value
1624.08 -> is cell type is boolean when i say cell type
is a boolean in that case we say system dot or
1634.96 -> dot println cell dot get boolean cell value
so this is how we need to use proper method
1643.2 -> while reading the data from excel depends upon
the type of the cell we have to you choose the
1649.2 -> proper method get string cell value get a numeric
cell value get boolean cell value so this is a
1655.2 -> switch command and this will read the data okay
so let me repeat this loop once again so the
1662.88 -> outer loop is representing the rows in a xl and
inner loop is representing the cells in each row
1669.92 -> so first we need to get the row by using sheet dot
get row method we pass the row index and we got
1676.8 -> the row here let us say currently this is my zero
row now this inner loop will repeat multiple times
1684.48 -> to get multiple cells from this particular row
because row contains multiple cells so on this
1690.8 -> particular row this loop will read all the cells
right so now inner for loop is representing the
1698.32 -> cells and here from that particular row object
i am extracting the cell by passing the index
1704.24 -> number and it will return the cell object and i am
checking whether the cell object or cell type is
1710.88 -> string or numeric or boolean so the cell type
is string then i am using get string cell value
1717.76 -> if the cell type is a numeric then i am using
get numeric cell value if the get cell type is
1724.08 -> boolean then i'm using get a boolean cell value
so depends upon the type of the cell we have to
1728.48 -> use proper method done so after completion of
the inner for loop before going to outer for
1735.04 -> loop i just use one system statement so that
all the rows will be printed in the next lines
1741.12 -> then put the semicolon that's it so this is a one
way we can read the data from excel so by using
1748.64 -> for loops now let me just execute this
and i will see how it is going to work
1753.52 -> so we already have an excel file in the same
project under data files maxwell file is present
1760.24 -> so now let us try to execute
run as java application
1768.72 -> okay now you can see here country capital property
this is basically header part this is a one record
1774.56 -> second row and this is a third row
and so on so all the rows we got it
1778.48 -> but i want ln this exactly in the tablet format
1781.84 -> right so what i can do is install println here
i'll remove ln and say only print and then execute
1793.12 -> so now we got exactly table for my country capital
population and all the values of combined you can
1798.48 -> just look at here this is a country capital and
the population so i want to give some space or i
1805.04 -> want to give some slash between those values and
what we can do is here before after completion of
1813.52 -> this switch case after completion of the switch
okay and here i will print something like slash
1823.36 -> okay so then uh it will just add this divider
between the values it's just for formatting
1829.44 -> purpose now when i run as a java application
now exactly will see the data so again it is
1835.52 -> printed multiple lines so this is just a
print not a lm printer it's a just a print
1844.96 -> all right so now exactly you can get the data
so this is header part and rest of the data we
1851.2 -> got it from excel so this is how we need to just
read the data from excel by using loop statements
1859.36 -> now i'll show you one more way to read the
data from excel and this time i am going
1864.56 -> to use iterator approach or iterator method now
we'll see that i'll comment this code till here
1872.64 -> and till here i am commenting this code
now i'll show you how we can work with
1879.52 -> iterator how to read the data from excel using
iterator and this is a most popular approach
1886.32 -> most of the people will use because this is little
simpler than this one because we no need to count
1891.36 -> rows and columns and all those things now we'll
see how we can read the data from the same excel
1897.76 -> by using iterator method so as usual first we need
to get the path of the excel and we open the file
1903.84 -> by using file input stream then we have to get
the workbook we have to get the sheet till here
1909.28 -> everything is same so once we get the sheet we
have to read all the rows and columns so for that
1914.8 -> i am going to create something called iterator
method so we already sheet object is created so
1920.4 -> in the sheet object we have a method called
iterator so what this method exactly will
1926.4 -> do is which will return all the rows and
uh we can we can iterate those rows and i
1933.6 -> can store them in a variable called this will
it will return some iterator object so i say
1942.08 -> iterator is my variable and the type
of the variable should be iterator
1950.72 -> and then import this iterator from java dot util
package so now by using this iterator i can repeat
1959.28 -> all the rows and cells now i will show you how
we can do this so now i'll write one while loop
1965.52 -> in the while loop itself i'll verify iterator is
having another record or not has a next method
1973.2 -> will check that particular object is there or
not in the iterator first object has next yes
1978.72 -> if it is a yes the condition is true and then it
will come to while loop and here from the iterator
1986.48 -> we have to capture the value we have to capture
the value so in iterator we have all the data
1994.8 -> but we need to capture each individual data one
by one so from the iterator the next method will
2001.44 -> return the data means it will return the row
because we have all the rows in excel sheet so
2007.36 -> in the sheet we created iterator so iterator
contains all the rows along with the cells
2011.92 -> now what this command will do is this
command will return the first row
2016.32 -> and i'll store that in a variable okay
so i'll create one variable called a row
2022.96 -> now when i create a row what type of object it
should be the variable should be referred with xss
2029.04 -> f row class object and we need to add typecasting
because this will return the row object so import
2037.76 -> this cast now we are converting that into row
and then storing into row so we got the first
2044.56 -> row into this variable now from this row in this
row we have a multiple cells so to capture all the
2053.44 -> cells we have to apply iterator for the cells also
so for that what you can do is take this row row
2063.68 -> dot cell iterator there is a method called sell
iterator so what this method will do is in this
2071.6 -> particular row this will iterate all the cells or
it will capture all the cells and store them into
2079.12 -> one variable and say i'll name it as cell iterator
just like a row iterator we have a cell iterator
2087.6 -> let me just store this in a variable i'll
name it as cell iterator and what is the
2095.52 -> type of this variable is the type of the
variable should be also iterator type
2102.8 -> ok so here we apply iterator for the sheet we
got all the rows into this iterator type and
2110.96 -> by using this iterator we got the one row
next method will get the row and store
2116.16 -> that into this variable so from that row by
using cell iterator we capture all the cells
2122.64 -> and the return type will be the iterator so
by using this we can read all the cells now
2128.88 -> so now let me write one more while loop to read
all the cells so by using the cell iterator
2136 -> so cell iterator dot has a next again same method
so here from this iterator i am verifying the next
2144.8 -> data or record is there or not if it is
there this condition becomes two and we
2148.96 -> are proceeding further similarly once we get the
cell iterator there are multiple cells we have
2154.72 -> in that particular row so which will check the
cell is present or not if the cell is present
2159.6 -> then it will returns true then we start reading
the cell so immediately what we have to do is the
2165.12 -> condition is true then we have to extract the
cell from the cell iterator so cell iterator
2170.96 -> dot the next method so this particular method will
return the cell and where you have to store it we
2178.48 -> have to store in some variable called cell and the
type of this variable is xss f cell and we need
2186.72 -> to do typecasting so i had cast xsf cell then we
got the cell object here so we got the cell object
2194.64 -> so once you get the cell object then we have
to find out the type of the cell accordingly
2199.2 -> we can read the data so as usual we use the
switch case statement once we get the cell
2203.92 -> object we can use which case statement so i'm just
copying the same switch command switch case block
2210.72 -> till here and from here it is exactly the same
so once you get the cell get the type of the
2218.24 -> cell accordingly we use proper method so after
coming out of this again if you want to specify
2225.92 -> slash between each and every column you can say
system dot out dot print just give some slash
2239.36 -> okay so i can just give one spanish print ln
2247.44 -> okay so over so after coming out of this it will
go to the another row so i'll write one sis out
2254.24 -> here and don't specify anything and
this is only just printer but here it
2260.24 -> is a print element so it is just for
formatting purpose so this is how we
2264.24 -> can use a iterator which will also read the
data from excel so let me repeat once again
2270.08 -> so first we need to add the sheet into iterator
which will capture all the data into iterator
2275.04 -> and checking this iterator is having the
next record then go inside and get the row
2280.88 -> and again in the row there are multiple cells
are there so i'm adding the row to the iterator
2286.32 -> and get the cell iterator and again i'm checking
the cells is present or not if the cell is present
2291.44 -> or not if it is yes then it will come here get the
cell into this variable the cell object i'm just
2297.6 -> checking the cell object type and accordingly i'll
read the data by passing get boolean cell value
2302.96 -> numeric cell value or string cell value methods
so this is how we can read the data from excel
2309.92 -> by using iterator method and now let us try to
execute and we will see how it is going to work
2317.2 -> right click run as java application yes so now
we can see we got all the data from excel file
2326.08 -> right so this is how we can use apache
poi api which will read the data from
2333.04 -> excel file so we have to use four classes we have
used so what are the classes excesses of workbook
2340.4 -> excesses of sheet excess of row and xs cell so by
using these classes and methods we can read the
2347.6 -> data from excel and either we can use for loop
method or we can use iterator approach okay so
2355.84 -> that's all for this video guys in the next video
we will see few more concepts thanks for watching
Source: https://www.youtube.com/watch?v=ipjl49Hgsg8