MainMenu

Home Java Overview Maven Tutorials

Saturday, 14 January 2017

Write data in excel using java, Read data from excel



Hello friends,
Here we will discuss that how to read write notepad, excel & docs


Java Code to write Notepad
We will use PrintWriter class to write the notepad
import java.io.FileNotFoundException;
import java.io.PrintWriter;
import java.io.UnsupportedEncodingException;
public class DataReader
{
public static void main(String args[]) throws FileNotFoundException, UnsupportedEncodingException
{
PrintWriter writer;
writer = new PrintWriter("D:\\Selenium\\Complete selenium\\eclipse mars\\csc2.txt");
writer.println("Chandan 31");
writer.println("Adhiraj 30");
writer.close();
}
}

We will use BufferedReader class & FileReader class to Read the notepad


Selenium code to read notepad
import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
public class ReadNotepad
{
public static void main(String args[]) throws IOException
{
BufferedReader br = null;
FileReader fr = null;
fr = new FileReader("D:\\Selenium\\Complete selenium\\eclipse mars\\csc2.txt");
br = new BufferedReader(fr);
String notepaddata = "";
while((notepaddata = br.readLine() ) != null)
{
System.out.println(notepaddata);
}
}
}



So this way we will read write notepad



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