java 使用jxl为Excel工作表中的单元格设置不同颜色

different colors for cells in excel sheet using jxl

提问by kshama hegde

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.

我一直在学习如何使用 jXL API，因为我是新手。我有一个 Excel 工作表，我想根据真/假条件为单元格的数据着色。例如，如果条件为真，它必须是绿色的，如果条件失败，它必须是红色的。

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

我试图在使用 jxl api 将数据写入 excel 表时实现这一点。

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.

用于写入 Excel 工作表的代码片段。我创建了一个方法来为每个单元格定义格式属性，wcellFormat1 是相同的变量，它是 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.

此代码片段用于应用我之前提到的条件。wfontStatus 是 WritableFont 类型，fCellstatus 是我用于指定格式的 WritableCellFormat 类型。

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.

这个你能帮我吗。谢谢你。

采纳答案by Bnrdo

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();

回答by default locale

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

这个方法只是更新fCellstatus变量。所以它可以通过以下方式使用：

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 WritableCellFormatobject 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.

我不得不说，WritableCellFormat为每个单元格创建新对象是一种资源浪费。此外，jxl 对单个工作簿中使用的格式数量有限制，因此您将在较大的工作表上面临不正确的格式问题。

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.

因此，每个工作簿只能使用两个 CellFormat 对象。