Hi,
This is my logic below for creating a cell. I have several overloads: one
for string, one for integer, one for double
The getColorBasedOnStatus returns a color based on the status of a given
row (OK-black, ERROR-RED, WARNING-AQUA)
So, I assume what I need to do is create three styles:Black, Red, AQUA and
give those styles class level scope and just use those class level objects
rather than creating a new one for each cell.
Correct?
private short getColorBasedOnStatus(String rowState){
short result = IndexedColors.BLACK.getIndex();
if(rowState.compareTo("ERROR") != 0) {
result = IndexedColors.RED.getIndex();
}
if(rowState.compareTo("WARNING") != 0) {
result = IndexedColors.AQUA.getIndex();
}
return result;
}
/**
* Creates a cell and aligns it a certain way.
*
* @param wb the workbook
* @param row the row to create the cell in
* @param i the column number to create the cell in
* @param halign the horizontal alignment for the cell.
*/
private void createCell(XSSFWorkbook wb, Row row, int i, short halign,
short valign, String value) {
CreationHelper ch = wb.getCreationHelper();
Cell cell = row.createCell(i);
cell.setCellValue(ch.createRichTextString(value));
CellStyle cellStyle = wb.createCellStyle();
try {
String rowState = RowState.get(row.getRowNum()-1);
if (rowState.compareToIgnoreCase("ok") != 0 ) {
XSSFFont myFont= wb.createFont();
myFont.setFontHeightInPoints((short)10);
myFont.setFontName("Arial");
myFont.setColor(getColorBasedOnStatus(rowState));
myFont.setBold(true);
myFont.setItalic(false);
cellStyle.setFont(myFont);
}
} catch(Exception e) {}
cellStyle.setAlignment(halign);
cellStyle.setVerticalAlignment(valign);
cell.setCellStyle(cellStyle);
}
Christopher Schene
Field Engineer
Global
Fraud and Identity Solutions
Experian
16260 N. 71st Street
Suite #400
Scottsdale
Arizona, USA
85254
+1 602.290.9792 mobile
+1 480.751.3928 office
[email protected]
www.experian.com <http://www.experian.com/>
On 11/4/15, 10:03 AM, "Schene, Chris" <[email protected]> wrote:
>"Make sure you create all of your styles outside of the loop, and re-use
>them.
>³
>
>Ah! Makes sense! I actually create a new style for each and every cell.
>Since I can have up to 50,000 rows that is a total of 2.35 million
>separate styles.
>
>Yikes!
>
>Thanks,
>
>Chris
>
>Christopher Schene
>Field Engineer
>Global
>Fraud and Identity Solutions
>
>Experian
>16260 N. 71st Street
>Suite #400
>Scottsdale
> Arizona, USA
> 85254
>+1 602.290.9792 mobile
>+1 480.751.3928 office
>[email protected]
>www.experian.com <http://www.experian.com/>
>
>
>
>
>
>On 11/4/15, 9:55 AM, "Nick Burch" <[email protected]> wrote:
>
>>On Wed, 4 Nov 2015, Schene, Chris wrote:
>>> I am using the code below to create a very large spread sheet that is
>>>47
>>> rows wide.
>>>
>>> There are a few very large strings in the rows, but for the most part
>>>the
>>> data is fairly small.
>>>
>>> If the .xls file is over about 1000 rows I get an error when I load
>>>the
>>> .xls file in excel saying it needs to repair the file and I lose all
>>>the
>>> row color and column width settings.
>>
>>Cell Styles are workbook scoped, not cell scoped. Make sure you create
>>all
>>of your styles outside of the loop, and re-use them. If you create one
>>style per cell, you'll use too many and Excel will sulk....
>>
>>Nick
>>
>>---------------------------------------------------------------------
>>To unsubscribe, e-mail: [email protected]
>>For additional commands, e-mail: [email protected]
>>
>
