使用Apache POI更改单元格颜色 [英] Changing cell color using apache poi
问题描述
我使用Apache POI读取零件编号A S preadsheet数据。我仰望的部件号在我们的数据库中，如果我们有i色的部件号小区绿化，如果我们不i色红其零件的CAD图纸。处理完毕后在S preadsheet被保存。我遇到的问题是，该列中的每一个细胞散发出来的绿色。我已经通过了code踩，查找零件号的逻辑工作正常和逻辑来确定电池应该是什么颜色，将颜色和填充出现工作为好。任何想法，我做错了什么？
感谢。
//检查零件
对于（INT R = 1;为r sheet.getPhysicalNumberOfRows（）; R ++）{
字符串部分号码= NULL;
开关（cell.getCellType（））{
案例HSSFCell.CELL_TYPE_NUMERIC：
长PNUM =（长）cell.getNumericCellValue（）;
部分号码=将String.valueOf（PNUM）;
打破;
案例HSSFCell.CELL_TYPE_STRING：
部分号码= cell.getStringCellValue（）;
打破;
默认：
logger.info（在行编号+ R +上表+ partList.getSheetName（S）+是不支持的类型）;
} 尝试{
清单＆LT;串GT; oldMaterialNumbers = getOldMaterialNumbers（零件号）; 布尔gotDrawing = checkPartNumber（oldMaterialNumbers，部分号码）;
//如果有一个图纸，然后上色排绿色的，如果不红。
短bgColorIndex = gotDrawing
？HSSFColor.LIGHT_GREEN.index // 42
：HSSFColor.RED.index; // 10 HSSFCell curCell = row.getCell（partNumberColumn）;
HSSFCellStyle curStyle = curCell.getCellStyle（）; curStyle.setFillPattern（HSSFCellStyle.SOLID_FOREGROUND）;
curStyle.setFillForegroundColor（bgColorIndex）; curCell.setCellStyle（curStyle）; }赶上（例外五）{
Ë扔掉;
}
}
短版：创建样式只有一次，到处使用它们
长版：使用一种方法来创建你需要（在样式量提防的限制）的样式
私有静态地图＆LT;弦乐，CellStyle＆GT;款式;私有静态地图＆LT;弦乐，CellStyle＆GT; createStyles（练习册WB）{
地图＆LT;弦乐，CellStyle＆GT;风格=新的HashMap＆LT;弦乐，CellStyle＆GT;（）;
DATAFORMAT DF = wb.createDataFormat（）; CellStyle风格;
字体headerFont = wb.createFont（）;
headerFont.setBoldweight（Font.BOLDWEIGHT_BOLD）;
headerFont.setFontHeightInPoints（（短）12）;
风格= createBorderedStyle（WB）;
style.setAlignment（CellStyle.ALIGN_CENTER）;
style.setFont（headerFont）;
styles.put（样式1样式）; 风格= createBorderedStyle（WB）;
style.setAlignment（CellStyle.ALIGN_CENTER）;
style.setFillForegroundColor（IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex（））;
style.setFillPattern（CellStyle.SOLID_FOREGROUND）;
style.setFont（headerFont）;
style.setDataFormat（df.getFormat（D-MMM））;
styles.put（date_style，风格）;
...
返回的款式;
}
您也可以使用方法，同时创造风格的HashMap做重复任务
私有静态CellStyle createBorderedStyle（练习册WB）{
CellStyle风格= wb.createCellStyle（）;
style.setBorderRight（CellStyle.BORDER_THIN）;
style.setRightBorderColor（IndexedColors.BLACK.getIndex（））;
style.setBorderBottom（CellStyle.BORDER_THIN）;
style.setBottomBorderColor（IndexedColors.BLACK.getIndex（））;
style.setBorderLeft（CellStyle.BORDER_THIN）;
style.setLeftBorderColor（IndexedColors.BLACK.getIndex（））;
style.setBorderTop（CellStyle.BORDER_THIN）;
style.setTopBorderColor（IndexedColors.BLACK.getIndex（））;
返回风格;
}
那么，在你的主code，从样式映射你设置的样式。
手机电池= xssfCurrentRow.createCell（intCellPosition）;
cell.setCellValue（等等）;
cell.setCellStyle（（CellStyle）styles.get（STYLE1））;
I'm using Apache POI to read data in a spreadsheet of part numbers. I look up the part number in our database, if we have a CAD drawing of the part I color the part number cell green, if we don't I color it red. After the processing is done the spreadsheet is saved. The problem I'm having is that every cell in that column comes out green. I've stepped through the code, the logic to look up the part number is working fine and the logic to determine what color the cell should be and setting the color and fill appears to work as well. Any ideas what I'm doing wrong here?
Thanks.
//Check the parts
for(int r=1;r<sheet.getPhysicalNumberOfRows();r++) {
String partNumber = null;
switch(cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
long pNum = (long) cell.getNumericCellValue();
partNumber = String.valueOf(pNum);
break;
case HSSFCell.CELL_TYPE_STRING:
partNumber = cell.getStringCellValue();
break;
default:
logger.info("Part Number at row " + r + " on sheet " + partList.getSheetName(s) + "is of an unsupported type");
}
try {
List<String> oldMaterialNumbers = getOldMaterialNumbers(partNumber);
boolean gotDrawing = checkPartNumber(oldMaterialNumbers, partNumber);
//If there's a drawing then color the row green, if not red.
short bgColorIndex = gotDrawing
?HSSFColor.LIGHT_GREEN.index //42
:HSSFColor.RED.index; //10
HSSFCell curCell = row.getCell(partNumberColumn);
HSSFCellStyle curStyle = curCell.getCellStyle();
curStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
curStyle.setFillForegroundColor(bgColorIndex);
curCell.setCellStyle(curStyle);
}catch(Exception e) {
throw e;
}
}
Short version: Create styles only once, use them everywhere.
Long version: use a method to create the styles you need (beware of the limit on the amount of styles).
private static Map<String, CellStyle> styles;
private static Map<String, CellStyle> createStyles(Workbook wb){
Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
DataFormat df = wb.createDataFormat();
CellStyle style;
Font headerFont = wb.createFont();
headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
headerFont.setFontHeightInPoints((short) 12);
style = createBorderedStyle(wb);
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setFont(headerFont);
styles.put("style1", style);
style = createBorderedStyle(wb);
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setFont(headerFont);
style.setDataFormat(df.getFormat("d-mmm"));
styles.put("date_style", style);
...
return styles;
}
you can also use methods to do repetitive tasks while creating styles hashmap
private static CellStyle createBorderedStyle(Workbook wb) {
CellStyle style = wb.createCellStyle();
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
return style;
}
then, in your "main" code, set the style from the styles map you have.
Cell cell = xssfCurrentRow.createCell( intCellPosition );
cell.setCellValue( blah );
cell.setCellStyle( (CellStyle) styles.get("style1") );
这篇关于使用Apache POI更改单元格颜色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
