Write Data with formatting information

We can pass the formatting information to Excel by overloading the constructor which takes an additional object containing the cell format information.

Formatting is required when ever you want to distinguish cell content with other cell contents. Generally, when we update the test cases in excel sheet, we keep all "PASS" in green color and Failed in Red color just to highlight the content.

The below syntax and example program helps to write data into excel sheet using formatting styles

// Create a cell format for specified font 
WritableFont cellFont= new WritableFont(WritableFont.TIMES, 12);
WritableCellFormat cellFormat = new WritableCellFormat(cellFont);

and In order to define the font color, we use the below statement.

cellFont.setColour(Colour.RED);
// Create the label, specifying content and format 
Label lab1 = new Label(iColumnNumber, iRowNumber, strData, cellFormat);
sheetTemp.addCell(lab);

We can pass the same format to different cells. We need to share the same object that is created in the above.

Label lab2 = new Label(iColumnNumber, iRowNumber, strData, cellFormat);
sheetTemp.addCell(lab);

Please find the below example program:

import java.io.File;
import jxl.Workbook;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WriteException;

public class dataSheet {

	static Workbook wbook;
	static WritableWorkbook wwbCopy;
	static String ExecutedTestCasesSheet;
	static WritableSheet shSheet;
	
	public void readExcel()
	{
	try{
	wbook = Workbook.getWorkbook(new File("D:\\Dev\\SampleTest\\datasheets\\testSampleData.xls"));
	wwbCopy = Workbook.createWorkbook(new File("D:\\Dev\\SampleTest\\datasheets\\testSampleDataCopy.xls"), wbook);
	shSheet = wwbCopy.getSheet(0);
	}
	catch(Exception e)
	{
		e.printStackTrace();
	}
	
	}	

	public void setValueIntoCell(String strSheetName,int iColumnNumber, int iRowNumber,String strData) throws WriteException
	{
		WritableSheet wshTemp = wwbCopy.getSheet(strSheetName);
		WritableFont cellFont = null;
		WritableCellFormat cellFormat = null;
		
		if(strData.equalsIgnoreCase("PASS"))
		{
			cellFont = new WritableFont(WritableFont.TIMES, 12);
			cellFont.setColour(Colour.GREEN);
			cellFont.setBoldStyle(WritableFont.BOLD);
			
			cellFormat = new WritableCellFormat(cellFont);
			cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);			
		}
		
		else if(strData.equalsIgnoreCase("FAIL"))
		{
			cellFont = new WritableFont(WritableFont.TIMES, 12);
			cellFont.setColour(Colour.RED);
			cellFont.setBoldStyle(WritableFont.BOLD);
				
			cellFormat = new WritableCellFormat(cellFont);
			cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
		}
		
		else
		{
			cellFont = new WritableFont(WritableFont.TIMES, 12);
			cellFont.setColour(Colour.BLACK);
				
			cellFormat = new WritableCellFormat(cellFont);
			cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
			cellFormat.setWrap(true);		
		}
		
		Label labTemp = new Label(iColumnNumber, iRowNumber, strData, cellFormat);	
		try {
			wshTemp.addCell(labTemp);
			
			} 
			catch (Exception e) 
			{
				e.printStackTrace();
			}
	}
	
	public void closeFile()
	{
		try {
			// Closing the writable work book
			wwbCopy.write();
			wwbCopy.close();

			// Closing the original work book
			wbook.close();
		} catch (Exception e)
		{
			e.printStackTrace();

		}
	 }

	public void main(String[] args) throws WriteException
	{
		dataSheet ds = new dataSheet();
		ds.readExcel();
		ds.setValueIntoCell("Sheet1", 5, 1, "PASS");
		ds.setValueIntoCell("Sheet1", 5, 2, "FAIL");
		ds.setValueIntoCell("Sheet1", 5, 3, "N/A");

	}	
}

The Above program will copy the original sheet. Now in the sheet which is copied will be updated with 'PASS', 'FAIL' and 'N/A' with styles defined.

Excel API: 

Add new comment