MainMenu

Home Core Java Overview Maven Tutorials

Saturday, 14 January 2017

Write data in excel using java, Read data from excel



Hello friends,
In this example we will create an excel sheet & then we will write the data in that sheet.

Click here to download Apache POI Jar Files




package excel;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
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;
import org.testng.annotations.Test;
public class Insert
{
XSSFWorkbook wb;
XSSFSheet sheet;
public static void main(String args[])
{
public void f() throws IOException
{
try {
File src = new File("D:\\study material\\Selenium Final complete\\Selenium Test data\\outputfile1.xlsx");
wb = new XSSFWorkbook();
sheet = wb.createSheet("mysheet1");
XSSFRow row1 = sheet.createRow((short)0);
XSSFRow row2 = sheet.createRow((short)1); row1.createCell(0).setCellValue("chandan");
row2.createCell(0).setCellValue("singh");
FileOutputStream fos = new FileOutputStream(src);
wb.write(fos);
}
catch (FileNotFoundException e)
{
e.printStackTrace();
}
}
}
}
-----------------------------------------------------------------------------------------------------------------
In below example we read the data from excel sheet.

package readexcel;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class Readexcel
{
public static void main(String args[]) throws Exception
{
File src = new File(“C:\\Selenium Practice\\Readxl\\ExcelData\\TestData.xlsx”);
FileInputStream fis = new FileInputStream(src);
XSSFWorkbook xb = new XSSFWorkbook(fis); //HSSFWorkbook will be used for .xls file
XSSFSheet ws = xb.getSheetAt(0);
String Data1 = ws.getRow(0).getCell(0).getStringCellValue();
System.out.println(Data1);
}
}
----------------------------------------------------------------------------------------------------------------------------------------

How to read the numeric cell in excel using Java


Here is the image of excel file



public class readnumcell
{
public void readexcel() throws IOException
{
File src1 = new File("D:\\Excel_demo\\myexcel.xlsx");
FileInputStream fis = new FileInputStream(src1);
XSSFWorkbook wb = new XSSFWorkbook(fis);
XSSFSheet st = wb.getSheetAt(0);
String mykeyword = st.getRow(1).getCell(2).getStringCellValue();
System.out.println(mykeyword);
Integer mymobileno = (int) st.getRow(1).getCell(4).getNumericCellValue();
System.out.println(mymobileno);
double mymobileno1 = st.getRow(1).getCell(4).getNumericCellValue();
System.out.println(mymobileno1);
DataFormatter formatter = new DataFormatter();
String val = formatter.formatCellValue(st.getRow(1).getCell(4));
System.out.println(val);
writeexcel();
}
}

Output :
Login
2147483647
9.718632008E9
9718632008
dne
----------------------------------------------------------------------------------------------------------------------------------------
Write Data in existing excel


public static void writeexistingExcel(String path, int sheetnumber, int rownumber, int cellnumber, String status ) throws IOException
{
File src = new File(path);
FileInputStream fis= new FileInputStream(src);
sheet = wb.getSheetAt(sheetnumber);
sheet.getRow(rownumber).createCell(cellnumber).setCellValue(status);
FileOutputStream fos =new FileOutputStream(src); //Open FileOutputStream to write updates
wb.write(fos); //write changes
}

----------------------------------------------------------------------------------------------------------------------------------------
Write Data in existing excel in new sheet

public void createexistingsheet() throws IOException, EncryptedDocumentException, InvalidFormatException
{
File source = new File("D:\\Excel_demo\\oldexcel\\myexcel.xlsx");
FileInputStream fileinputstream = new FileInputStream(source);
Workbook workbook = WorkbookFactory.create(fileinputstream);
XSSFSheet sheett = (XSSFSheet) workbook.getSheetAt(1);
XSSFRow rowx = sheett.createRow((short)0);
rowx.createCell(0).setCellValue("chandan");
FileOutputStream fo = new FileOutputStream(source);
workbook.write(fo);
}
----------------------------------------------------------------------------------------------------------------------------------------

Write Data in MS Word Document:


In below code first we create an Class & then a constructor which first create the MS word document then write data in it.

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xwpf.usermodel.XWPFParagraph;
import org.apache.poi.xwpf.usermodel.XWPFRun;
public class CreateD
{
public static void main(String args[]) throws FileNotFoundException, IOException
{
CreateD obj = new CreateD("hello! how are you?");
}
public CreateD(String data)
{
try {
File src = new File("D:\\study material\\Selenium Final complete\\hack data\\Adobereview.docx");
XWPFDocument documents = new XWPFDocument();
XWPFParagraph paragraph = documents.createParagraph();
XWPFRun run = paragraph.createRun();
run.setText(data);
FileOutputStream fos = new FileOutputStream(src);
documents.write(fos);
}
catch (FileNotFoundException e)
{
e.printStackTrace();
}
catch (IOException e)
{ e.printStackTrace();
}
}
}





Tags :

Read and write data in excel

java code to read write excel

No comments:

Post a Comment