EXCEL

  

REQUIRMENTS

APACHE POI

We can download apache poi from poi.apache.org

INADTALLATION OF POI

•  Download the Apache poi from the official site
• Click on downloads section
•  Go to binary distribution download the zip file
•  Once the zip file is download extract the zip file
• Configure the build path in eclipse and add all the poi external jars

Once all the jar files are added the user is now ready to read or write the date from the excel files

XSSF = used ro read or write data to Microsoft excel file in XSL OR XLSX format

HSSF= used ro read or write data to Microsoft excel file in XSL format

HOW TO READ DATA FROM EXCEL

• Obtain the excel workbook based upon it location on the computer

Syntax: FileInputStream file=new FileInputStream(“file location”);

•  You can create an object of the workbook by refering to the fileinputstream object that point to the excel file

Syntax: XSSFWorkbook workbook=new XSSFWorkbook(file);

• The next step is to create a sheet in the workbook additionally we can do it as below using he name sheet in the getsheet()

Syntax: XSSFSheet sheet=workbook.getSheet("sheet name");

• We have to obtain the Row in the sheet

Syntax: XSSFRow row=sheet.getRow(index);

• Wee can get the Cell of the row

Syntax: XSSFCell cell=row.getCell(index);

• After the addition in the cell the contains the data you can Read the date in different formats

STRING:

   String text=cell.getStringCellValue();

       System.out.println(text);

NUMBER:

   Int num=(Int)cell.getNumericCellValue();

       System.out.println(num);

DATE:

   Date dob=cell.getdateCellValue();

       System.out.println(dob);

NOTE: Index starts fron Zero for both the Row And Cell.

 

HOW TO READ SPECIFIC CELL VALUE

Package mypackage;

 

import java.io.FileInputStream;

import java.io.IOException;

 

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 Excelsheet {

 

public static void main(String[] args) throws IOException {

 

FileInputStream file=new FileInputStream("C:\\Users\\ramak\\3D Objects\\nani.xlsx");

XSSFWorkbook workbook=new XSSFWorkbook(file);

                   

XSSFSheet sheet=workbook.getSheet("sheet1");

XSSFRow row=sheet.getRow(1);

XSSFCell cell=row.getCell(1);

String text=cell.getStringCellValue();

System.out.println(text);

}

} 

HOW TO GET SPECIFIC ROW

Package mypackage;

 

import java.io.FileInputStream;

import java.io.IOException;

 

import org.apache.poi.xssf.usermodel.XSSFRow;

import  org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

 

public class Excelsheet{

 

public static void main(String[] args) throws IOException {

 

FileInputStream file=new FileInputStream("C:\\Users\\ramak\\3D Objects\\nani.xlsx");

XSSFWorkbook workbook=new XSSFWorkbook(file);

XSSFSheet sheet=workbook.getSheet("sheet1");

           

int lastrow=sheet.getLastRowNum();

int lastcell=sheet.getRow(0).getLastCellNum();

                   

for (int i = 0; i <=lastrow; i++) {

XSSFRow row=sheet.getRow(i);

String allrow=row.getCell(1).toString();

System.out.println(allrow);

}

}

}

HOW TO GET ENTIRE EXCEL SHEET

Package mypackage;

 

import java.io.FileInputStream;

import java.io.IOException;

 

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

 

public class myclass {

 

public static void main(String[] args) throws IOException {

 

FileInputStream file=new FileInputStream("C:\\Users\\ramak\\3D Objects\\nani.xlsx");

XSSFWorkbook workbook=new XSSFWorkbook(file);

XSSFSheet sheet=workbook.getSheet("sheet1");

           

int lastrow=sheet.getLastRowNum();

int lastcell=sheet.getRow(0).getLastCellNum();

                   

for (int i = 0; i <=lastrow; i++) {

XSSFRow row=sheet.getRow(i);

for (int j = 0; j < lastcell; j++) {

String data=row.getCell(j).toString();

System.out.println(data);        

}

}

}

}