java generated excel sorting color mess.

Question

I am using jxl api for generating a excel sheet, in which the alternate rows are highlighted, when I sort the contents of a excel and sort manually, the cell background colors messup, usually this is because I am writing the color cell by cell, is there anyway through which i color the alternate rows of the excel while generating it, in such a way that it doesnt effect the sorting of the contents.

Answer 1

Have you tried using the RowView:

Sheet s = ...
Colour[] colorings = new Colour[]{Colour.GOLD, Colour.OCEAN_BLUE};
for(int i=0;i<s.getRows();i++){
  CellView rowView = s.getRowView(i);
  WritableCellFormat newFormat = new WritableCellFormat(rowView.getFormat());
  newFormat.setBackground(colorings[i%2]);
  rowView.setFormat(newFormat());
}

I think this should accomplish the effect you are looking for. Note I have copied the existing format above, but if you don't already have formats applied to the rows, you could create two formats and reuse them. The only other caveat is I believe any format applied to a specific cell will override the view format, so you may need to make sure the individual cells are formatted to have color DEFAULT or AUTOMATIC (I'm not entirely sure about this, as I haven't tried it myself).