Changing cell color using apache poi

Question

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 lt 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 lt String gt  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

User · Answer

For apache POI 3 9 you can use the code bellow   HSSFCellStyle style   workbook createCellStyle   style setFillForegroundColor HSSFColor YELLOW index  style setFillPattern  short  FillPatternType SOLID FOREGROUND ordinal      The methods for 3 9 version accept short and you should pay attention to the inputs

User · Answer

I believe it is because cell getCellStyle initially returns the default cell style which you then change   Create styles like this and apply them to cells   cellStyle    XSSFCellStyle  cell getSheet   getWorkbook   createCellStyle      Although as the previous poster noted try and create styles and reuse them   There is also some utility class in the XSSF library that will avoid the code I have provided and automatically try and reuse styles  Can t remember the class 0ff hand

User · Answer

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 lt String  CellStyle gt  styles   private static Map lt String  CellStyle gt  createStyles Workbook wb           Map lt String  CellStyle gt  styles   new HashMap lt String  CellStyle gt             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

User · Answer

To create your cell styles see  http   poi apache org spreadsheet quick-guide html CustomColors   Custom colors  HSSF   HSSFWorkbook wb   new HSSFWorkbook    HSSFSheet sheet   wb createSheet    HSSFRow row   sheet createRow  short  0   HSSFCell cell   row createCell  short  0   cell setCellValue  Default Palette       apply some colors from the standard palette     as in the previous examples    we ll use red text on a lime background  HSSFCellStyle style   wb createCellStyle    style setFillForegroundColor HSSFColor LIME index   style setFillPattern HSSFCellStyle SOLID FOREGROUND    HSSFFont font   wb createFont    font setColor HSSFColor RED index   style setFont font    cell setCellStyle style      save with the default palette FileOutputStream out   new FileOutputStream  default palette xls    wb write out   out close       now  let s replace RED and LIME in the palette    with a more attractive combination     lovingly borrowed from freebsd org   cell setCellValue  Modified Palette       creating a custom palette for the workbook HSSFPalette palette   wb getCustomPalette       replacing the standard red with freebsd org red palette setColorAtIndex HSSFColor RED index           byte  153     RGB red  0-255           byte  0       RGB green          byte  0       RGB blue      replacing lime with freebsd org gold palette setColorAtIndex HSSFColor LIME index   byte  255   byte  204   byte  102      save with the modified palette    note that wherever we have previously used RED or LIME  the    new colors magically appear out   new FileOutputStream  modified palette xls    wb write out   out close      XSSF    XSSFWorkbook wb   new XSSFWorkbook        XSSFSheet sheet   wb createSheet        XSSFRow row   sheet createRow 0       XSSFCell cell   row createCell  0       cell setCellValue  custom XSSF colors         XSSFCellStyle style1   wb createCellStyle        style1 setFillForegroundColor new XSSFColor new java awt Color 128  0  128         style1 setFillPattern CellStyle SOLID FOREGROUND

User · Answer

checkout the example here   http   svn apache org repos asf poi trunk src examples src org apache poi ss examples BusinessPlan java  style setFillForegroundColor IndexedColors LIGHT CORNFLOWER BLUE getIndex

[java] Changing cell color using apache poi

Examples related to java

Examples related to excel

Examples related to apache-poi