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