Clever Geek Handbook

Different colors for cells in excel sheet using jxl

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

I am trying to achieve this by writing data to an excel sheet using the jxl api.

The snippets of code I have tried to follow are as follows.

Snippet code to write to excel sheet. I created a method to define format properties for each cell and wcellFormat1 is a variable for the same WritableCellFormat type.

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 that I mentioned earlier. wfontStatus is of type WritableFont and fCellstatus is of type WritableCellFormat, which I used to specify 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 am facing is that I do not understand how to use the above method to apply the conditions required when writing to the sheet.

Please help me with this. Thank.

+3


source to share


2 answers


The method should look something like this:

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 a 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();
+3


source


This method only updates the variable fCellstatus

. Therefore, it can be used like this:

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

I think it's not a good idea to include fields in such interactions. I would suggest re-implementing this method like this:

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;
}

In this case, the usage is a little cleaner:



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

I have to say that creating a new object WritableCellFormat

for each cell is a waste of resources. Also, jxl has limitations on the number of formats used in one book, so you are running into incorrect formatting problems on large sheets.

I would suggest reusing 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;
}

Thus, only two CellFormat objects can be used per book.

+1


source






More articles:


All Articles