I transform a string called sCurrentLine to an Excel's row. Depending if the line is starting with X, Y or Z I color the row.
row = sheet.createRow(lines);
String[] parts = sCurrentLine.split("\\|");
if (sCurrentLine.contains("X")) {
bgColorIndex = HSSFColor.RED.index;
} else if (sCurrentLine.contains("Y")){
bgColorIndex = HSSFColor.LIGHT_BLUE.index;
} else if (sCurrentLine.contains("Z")) {
bgColorIndex = HSSFColor.YELLOW.index;
} else {
bgColorIndex = HSSFColor.BROWN.index;
}
So I am having this variable called bgColorIndex and use it to set the color of all the cells of the row
for (short i = 0; i < parts.length; i++) {
row.createCell(i).setCellValue(parts[i]);
HSSFCell curCell = row.getCell(i);
HSSFCellStyle curStyle = curCell.getCellStyle();
curStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
curStyle.setFillForegroundColor(bgColorIndex);
curStyle.setFillBackgroundColor(bgColorIndex);
System.out.println("Color is: " + bgColorIndex);
curCell.setCellStyle(curStyle);
}
lines++;
}
The issue I am having is setCellStyle is applying the style for all the rows and sheets, so the last color I detect is applied to whole document.
How can I color independently every cell?
EDITED: Whole code:
private void generateCSVFile() {
String filename = "excel.xls" ;
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("FirstSheet");
HSSFRow rowhead = sheet.createRow((short)0);
// header
rowhead.createCell((short)0).setCellValue("AXIS");
rowhead.createCell((short)1).setCellValue("INIT");
rowhead.createCell((short)2).setCellValue("MID");
rowhead.createCell((short)3).setCellValue("END");
// Set columns width
for (short i = 0; i < 3; i++) {
sheet.setColumnWidth(i, (short)(20*300));
}
BufferedReader br = null;
FileReader fr = null;
try {
fr = new FileReader("data.txt");
br = new BufferedReader(fr);
String sCurrentLine;
br = new BufferedReader(new FileReader("data.txt"));
short lines = 1;
HSSFRow row = null;
while ((sCurrentLine = br.readLine()) != null) {
if (sCurrentLine.contains("AXIS")) {
lines = 0;
sheet = workbook.createSheet("SecondSheet");
// Set columns width
for (short i = 0; i < 7; i++) {
sheet.setColumnWidth(i, (short)(20*256));
}
row = sheet.createRow(lines);
}
row = sheet.createRow(lines);
String[] parts = sCurrentLine.split("\\|");
short bgColorIndex = 0;
// Check the first cell to set color for X, Y or Z
if (sCurrentLine.contains("X")) {
bgColorIndex = HSSFColor.RED.index;
} else if (sCurrentLine.contains("Y")){
bgColorIndex = HSSFColor.LIGHT_BLUE.index;
} else if (sCurrentLine.contains("Z")) {
bgColorIndex = HSSFColor.YELLOW.index;
} else {
bgColorIndex = HSSFColor.BROWN.index;
}
for (short i = 0; i < parts.length; i++) {
row.createCell(i).setCellValue(parts[i]);
HSSFCell curCell = row.getCell(i);
HSSFCellStyle curStyle = curCell.getCellStyle();
curStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
curStyle.setFillForegroundColor(bgColorIndex);
curStyle.setFillBackgroundColor(bgColorIndex);
System.out.println("Color is: " + bgColorIndex);
curCell.setCellStyle(curStyle);
curStyle = null;
}
lines++;
}
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
if (br != null)
br.close();
if (fr != null)
fr.close();
} catch (IOException ex) {
ex.printStackTrace();
}
}
}
You have to create four different CellStyle in the workbook:
CellStyle styleX = wb.createCellStyle();
styleX.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
styleX.setFillForegroundColor(HSSFColor.RED.index);
styleX.setFillBackgroundColor(HSSFColor.RED.index);
CellStyle styleY = wb.createCellStyle();
styleY.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
styleY.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
styleY.setFillBackgroundColor(HSSFColor.LIGHT_BLUE.index);
CellStyle styleZ = wb.createCellStyle();
styleZ.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
styleZ.setFillForegroundColor(HSSFColor.YELLOW.index);
styleZ.setFillBackgroundColor(HSSFColor.YELLOW.index);
CellStyle styleOther = wb.createCellStyle();
styleOther .setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
styleOther .setFillForegroundColor(HSSFColor.BROWN.index);
styleOther .setFillBackgroundColor(HSSFColor.BROWN.index);
And, for each row:
CellStyle style = null;
row = sheet.createRow(lines);
String[] parts = sCurrentLine.split("\\|");
if (sCurrentLine.contains("X")) {
style = styleX;
} else if (sCurrentLine.contains("Y")){
style = styleY;
} else if (sCurrentLine.contains("Z")) {
style = styleZ;
} else {
style = styleOther;
}
for (short i = 0; i < parts.length; i++) {
row.createCell(i).setCellValue(parts[i]);
HSSFCell curCell = row.getCell(i);
currCell.setCellStyle(style);
}