different colors for cells in excel sheet using jxl

Question

I have been learning how to use jXL API as I'm new to it. I have an excel sheet, where I want to color the cells' data based on an true/false condition. For ex, if the condition is true, it has to be green and if the condition fails, red.

I'm trying to achieve this while writing data into excel sheet using jxl api.

The snippets of the code I have been trying to complete is as follows.

Code snippet for writing into excel sheet. I have created a method to define format properties for each cell and wcellFormat1 is a variable for the same, which is of type WritableCellFormat.

for(int i=1; i11; i++){
            String srnum = String.valueOf(rnum);
            wsheet.addCell(new jxl.write.Label(1, rc, srnum, wcellFormat1));
            wsheet.addCell(new jxl.write.Label(2, rc, "b", wcellFormat1));
            wsheet.addCell(new jxl.write.Label(3, rc, "c", wcellFormat1));
            wsheet.addCell(new jxl.write.Label(4, rc, "d", wcellFormat1));
            wsheet.addCell(new jxl.write.Label(5, rc, "e", wcellFormat1));
            wsheet.addCell(new jxl.write.Label(6, rc, "f", wcellFormat1));  

            rnum++;
            rc++;   
            System.out.println(""+rnum+"\n"+rc);
        }
        wbook.write();
        wbook.close();

This code snippet is for applying the conditions which I have mentioned before. wfontStatus is of type WritableFont and fCellstatus is of type WritableCellFormat which i have used for specifying formats.

public void formatCellStatus(Boolean b) throws WriteException{

        if(b == true){
            wfontStatus = new WritableFont(WritableFont.createFont("Arial"), WritableFont.DEFAULT_POINT_SIZE, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.GREEN);
        }else{
            wfontStatus = new WritableFont(WritableFont.createFont("Arial"), WritableFont.DEFAULT_POINT_SIZE, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.RED);
        }

        fCellstatus = new WritableCellFormat(wfontStatus);
        fCellstatus.setWrap(true);
        fCellstatus.setAlignment(jxl.format.Alignment.CENTRE);
        fCellstatus.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
        fCellstatus.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.MEDIUM, jxl.format.Colour.BLUE2);
    }

The problem I'm facing is that I'm not understanding how to use the above method to apply the conditions necessary while writing into sheet.

Please help me out with this. Thank you.

Solution 1

The method should look something like

public WritableCellFormat createFormatCellStatus(boolean b) throws WriteException{
    Colour colour = (b == true) ? Colour.GREEN : Colour.RED;
    WritableFont wfontStatus = new WritableFont(WritableFont.createFont("Arial"), WritableFont.DEFAULT_POINT_SIZE, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, colour);
    WritableCellFormat fCellstatus = new WritableCellFormat(wfontStatus);

    fCellstatus.setWrap(true);
    fCellstatus.setAlignment(jxl.format.Alignment.CENTRE);
    fCellstatus.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
    fCellstatus.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.MEDIUM, jxl.format.Colour.BLUE2);
    return fCellstatus;
}

and inside the loop that creates labels

for(int i=1; i11; i++){
    String srnum = String.valueOf(rnum);
    wsheet.addCell(new jxl.write.Label(1, rc, srnum, createFormatCellStatus(true)));    //will create green cell
    wsheet.addCell(new jxl.write.Label(2, rc, "b", createFormatCellStatus(false))); //will create red cell
    wsheet.addCell(new jxl.write.Label(3, rc, "c", createFormatCellStatus(false)));
    wsheet.addCell(new jxl.write.Label(4, rc, "d", createFormatCellStatus(true)));
    wsheet.addCell(new jxl.write.Label(5, rc, "e", createFormatCellStatus(false)));
    wsheet.addCell(new jxl.write.Label(6, rc, "f", createFormatCellStatus(true)));  

    rnum++;
    rc++;   
    System.out.println(""+rnum+"\n"+rc);
}
wbook.write();
wbook.close();

Solution 2

This method just updates fCellstatus variable. So it can be used in the following way:

formatCellStatus(condition);
wsheet.addCell(new jxl.write.Label(columnNumber, rowNumber, "cellvalue", fCellstatus));

I think that it's not a good idea to involve fields into interactions like this. I would suggest to re-implement this method in the following way:

public WritableCellFormat getCellFormatByCondition(boolean condition) {
    if(b == true){
        wfontStatus = new WritableFont(WritableFont.createFont("Arial"), WritableFont.DEFAULT_POINT_SIZE, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.GREEN);
    }else{
        wfontStatus = new WritableFont(WritableFont.createFont("Arial"), WritableFont.DEFAULT_POINT_SIZE, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.RED);
    }

    WritableCellFormat result = new WritableCellFormat(wfontStatus);
    result .setWrap(true);
    result .setAlignment(jxl.format.Alignment.CENTRE);
    result .setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
    result .setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.MEDIUM, jxl.format.Colour.BLUE2);
    return result;
}

This way usage is a bit cleaner:

 wsheet.addCell(new jxl.write.Label(columnNumber, rowNumber, "cellvalue", getCellFormat(condition)));

I have to say that creating new WritableCellFormat object for every cell is a waste of resources. Also jxl has a limitations on the number of formats used in a single workbook, so you will face incorrect formatting problems on larger sheets.

I would suggest to reuse format objects:

private WritableCellFormat GREEN_CELL_FORMAT;
private WritableCellFormat RED_CELL_FORMAT;

private void createFormats() {
    //you'll need to call this before writing workbook
    //JXL has problems with using one format across several workbooks
    WritableFont greenFont = new WritableFont(WritableFont.createFont("Arial"), WritableFont.DEFAULT_POINT_SIZE, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.GREEN);
    WritableFont redFont= new WritableFont(WritableFont.createFont("Arial"), WritableFont.DEFAULT_POINT_SIZE, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.RED);
    GREEN_CELL_FORMAT = getCellFormat(greenFont);
    RED_CELL_FORMAT = getCellFormat(redFont);
}

private WritableCellFormat getCellFormat(WritableFont font) {
    WritableCellFormat result = new WritableCellFormat(font);
    result .setWrap(true);
    result .setAlignment(jxl.format.Alignment.CENTRE);
    result .setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
    result .setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.MEDIUM, jxl.format.Colour.BLUE2);
    return result;
}

private WritableCellFormat getCellFormatByCondition(boolean condition) {
    return condition ? GREEN_CELL_FORMAT : RED_CELL_FORMAT;
}

So, you can use only two CellFormat objects for each workbook.