Useful Operations in Excel Using POI API

In the previous post, we have learnt how to Read data from Excel file. Apart from usual reading and writing , we will perform some useful operations in Excel using POI API.

In Selenium, if we are creating reporting using excel file then we might need to format excel files according to our need. Let’s see different kind of operations on Excel files.

1. Insert the comment into the desired Cell:

We have Create a reusable function as given below.

//RowNum : Row number where you want to insert the comment
	//CellNum : cell number under which row you want to insert comment
	//comment : The comment which you want to enter
	//author : Comment author
	//FilePath : Excel workbook path
	//sheetname: Sheet under the workbook 
	public static void insertCommentInCell(int RowNum,int CellNum,String comment,String author,String FilePath,String sheetname){
		XSSFWorkbook workbook;
		XSSFSheet sheet;
		XSSFCell cell;
		XSSFRow row;
		FileInputStream fileIS;
		try {
			fileIS = new FileInputStream(new File(FilePath));
		
		//Get sheet access
		workbook = new XSSFWorkbook(fileIS);
		sheet = workbook.getSheet(sheetname);
		row = sheet.getRow(RowNum);
		cell = row.getCell(CellNum);
		
		Drawing drawing = sheet.createDrawingPatriarch();
		CreationHelper factory = workbook.getCreationHelper();
		ClientAnchor  anchor = factory.createClientAnchor();
	    anchor.setCol1(cell.getColumnIndex());  //Comment space
        anchor.setCol2(cell.getColumnIndex()+1);
        anchor.setRow1(row.getRowNum());
        anchor.setRow2(row.getRowNum()+3);
		
		Comment comment1  = drawing.createCellComment(anchor);
		//Give comment String
		RichTextString str1 = factory.createRichTextString(comment);
		comment1.setString(str1);  //Set the comment in the cell
		comment1.setAuthor(author);  //Give the comment author
		cell.setCellComment(comment1);
		fileIS.close();      //Close input stream
		
		//Write to file
        FileOutputStream out = new FileOutputStream(FilePath);
        workbook.write(out);
        out.close();
		}catch(Exception e){
			System.out.println(e.getMessage());
		}
	}

The function will be called in the following way.

public static void main(String[] args) throws Exception {
		
	String FilePath = "C:\\Users\\Shekar Sharma\\Documents\\Sample1.xlsx";
	insertCommentInCell(2,1,"This is a City.", "Shekhar",FilePath, "Data");
}

2. Format the cell (change the foreground color and font type): 

We will show you how easy it is to format the cell like changing foreground color, background color,font type,font color etc. bu using POI API.

Reusable function for cell formatting:

	//RowNum : Row number where you want to insert the comment
	//CellNum : cell number under which row you want to insert comment
	//FGcolor : Foreground color
	//FilePath : Excel workbook path
	//sheetname: Sheet under the workbook
	public static void formatCell(int RowNum,int CellNum,String FGcolor,String FilePath,String sheetname){
		XSSFWorkbook workbook;
		XSSFSheet sheet;
		XSSFCell cell;
		XSSFRow row;
		FileInputStream fileIS;
		try {
			fileIS = new FileInputStream(new File(FilePath));
		
		//Get sheet access
		workbook = new XSSFWorkbook(fileIS);
		sheet = workbook.getSheet(sheetname);
		row = sheet.getRow(RowNum);
		cell = row.getCell(CellNum);
		
        // Set ForeGround color
		CellStyle style = workbook.createCellStyle();		
        style = workbook.createCellStyle();
        style.setFillForegroundColor(IndexedColors.RED.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        cell.setCellStyle(style);
        
        //Set Font Color and font type
        Font font = workbook.createFont();
        font.setColor(IndexedColors.WHITE.getIndex());
        font.setFontName("Times New Roman");
        style.setFont(font);
        cell.setCellStyle(style);
        
		fileIS.close();      //Close input stream
		//Write to file
        FileOutputStream out = new FileOutputStream(FilePath);
        workbook.write(out);
        out.close();
		}catch(Exception e){
			System.out.println(e.getMessage());
		}
	}

Calling above function:

public static void main(String[] args) {
	String FilePath = "C:\\Users\\Shekar Sharma\\Documents\\Sample1.xlsx";
	formatCell(1,1,"Red", FilePath,"Data");

}

Output:

3. Creating Hyperlink in the Excel File:

We can create hyperlinks in the excel files , that makes it very handy. Suppose, you need to add some website links, making reference from one sheet to another sheet etc. The same file can be sent to intended users containing all hyperlinks and details.

Let’s see its 2 types of implementations.

• Creating a hyperlink which points to a Website In out example, we have created the hyperlink in second row which is the name ‘Shekhar’. This will be linked to the website ‘www.testingpool.com’. Once you click on the hyperlink, intended site should open.

	//RowNum : Row number where you want to insert the comment
	//CellNum : cell number under which row you want to insert comment
	//siteAddr : URL of the site which you want to create as hyperlink in the document
	//FilePath : Excel workbook path
	//sheetname: Sheet under the workbook
	public static void setHyperlink(int RowNum,int CellNum,String siteAddr,String FilePath,String sheetname){
		XSSFWorkbook workbook;
		XSSFSheet sheet;
		XSSFCell cell;
		XSSFRow row;
		FileInputStream fileIS;
		try {
			fileIS = new FileInputStream(new File(FilePath));
		
		//Get sheet access
		workbook = new XSSFWorkbook(fileIS);
		sheet = workbook.getSheet(sheetname);
		row = sheet.getRow(RowNum);
		cell = row.getCell(CellNum);
		
		CreationHelper createHelper = workbook.getCreationHelper();
        //Making hyperlinks blue and underlined 
        CellStyle style = workbook.createCellStyle();
        Font hlink_font = workbook.createFont();
        hlink_font.setUnderline(Font.U_SINGLE);
        hlink_font.setColor(IndexedColors.BLUE.getIndex());
        style.setFont(hlink_font);
        
        //Linking to our site URL
        if(!siteAddr.isEmpty()){
	        Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_URL);
	        link.setAddress(siteAddr);
	        cell.setHyperlink(link);
	        cell.setCellStyle(style);
        }
          
		fileIS.close();      //Close input stream
		//Write to file
        FileOutputStream out = new FileOutputStream(FilePath);
        workbook.write(out);
        out.close();
		}catch(Exception e){
			System.out.println(e.getMessage());
		}
	}

Calling to the above function:

public static void main(String[] args) {
	String FilePath = "C:\\Users\\Shekar Sharma\\Documents\\Sample1.xlsx";
	setHyperlink(1,0,"https://testingpool.com", FilePath,"Data");

}

Output:

• Creating hyperlink with reference to another sheet: we will create the Hyperlink to cell ‘Bangalore’, where it will point to another sheet ‘Sheet1′ in the same workbook. When click on ‘Bangalore’, it will link to another sheet named as ‘Sheet1’ and cell named as ‘India’ in that sheet1.

	//RowNum : Row number where you want to insert the comment
	//CellNum : cell number under which row you want to insert comment
	//targetSheet : referenced sheet name
	//FilePath : Excel workbook path
	//sheetname: Sheet under the workbook
	public static void setHyperlink(int RowNum,int CellNum,String targetSheet,String FilePath,String sheetname){
		XSSFWorkbook workbook;
		XSSFSheet sheet;
		XSSFCell cell;
		XSSFRow row;
		FileInputStream fileIS;
		try {
			fileIS = new FileInputStream(new File(FilePath));
		
		//Get sheet access
		workbook = new XSSFWorkbook(fileIS);
		sheet = workbook.getSheet(sheetname);
		row = sheet.getRow(RowNum);
		cell = row.getCell(CellNum);
		
		CreationHelper createHelper = workbook.getCreationHelper();
        //Making hyperlinks blue and underlined 
        CellStyle style = workbook.createCellStyle();
        Font hlink_font = workbook.createFont();
        hlink_font.setUnderline(Font.U_SINGLE);
        hlink_font.setColor(IndexedColors.BLUE.getIndex());
        style.setFont(hlink_font);

        
        //create a target sheet and cell
        
        Sheet sheet2 = workbook.getSheet(targetSheet);
        sheet2.createRow(0).createCell((short)0).setCellValue("India");

        Hyperlink link2 = createHelper.createHyperlink(Hyperlink.LINK_DOCUMENT);
        link2.setAddress("'"+targetSheet+"'!A1");
        cell.setHyperlink(link2);
        cell.setCellStyle(style);
          
		fileIS.close();      //Close input stream
		//Write to file
        FileOutputStream out = new FileOutputStream(FilePath);
        workbook.write(out);
        out.close();
		}catch(Exception e){
			System.out.println(e.getMessage());
		}
	}

Calling the function:

public static void main(String[] args) {
	String FilePath = "C:\\Users\\Shekar Sharma\\Documents\\Sample1.xlsx";
	setHyperlink(1,1,"Sheet1", FilePath,"Data");

}