Results 1 to 10 of 10
  1. #1
    sashaxiv is offline Member
    Join Date
    Feb 2011
    Location
    Spain
    Posts
    26
    Rep Power
    0

    Default Problem getting cell Color (XSSF)

    Hi all,
    I would like to read de backgroung color of cells, but i am always getting null while trying my code.

    Surfing the net people always use HSF for this task but i need to use XSSF instead.

    Java Code:
    public class ExcelSheetReader
    {
    	/**
    	 * This method is used to read the data's from an excel file.
    	 * @param fileName - Name of the excel file.
    	 */
    	private void readExcelFile(String fileName)
    	{
    		/**
    		 * Create a new instance for cellDataList
    		 */
    		List cellDataList = new ArrayList();
    		try
    		{
    			/**
    			 * Create a new instance for FileInputStream class
    			 */
    			FileInputStream fileInputStream = new FileInputStream(fileName);
    
    			/**
    			 * Create a new instance for HSSFWorkBook Class
    			 */
    
    			XSSFWorkbook workBook = new XSSFWorkbook(fileInputStream);
    			XSSFSheet xssfSheet = workBook.getSheetAt(0);
    			/**
    			 * Iterate the rows and cells of the spreadsheet
    			 * to get all the datas.
    			 */
    			Iterator rowIterator = xssfSheet.rowIterator();
    			rowIterator.next();
    			rowIterator.next();
    			rowIterator.next();
    			while (rowIterator.hasNext())
    			{
    				XSSFRow xssfRow = (XSSFRow) rowIterator.next();
    				Iterator iterator = xssfRow.cellIterator();
    				while (iterator.hasNext())
    				{
    					XSSFCell hssfCell =  (XSSFCell) iterator.next();
    					XSSFColor  cellColor = (hssfCell.getCellStyle().getFillBackgroundColorColor());
    					XSSFCellStyle style = hssfCell.getCellStyle();
    					
    					System.out.println ("****");
    					System.out.println (hssfCell.toString());
    					//XSSFColor xsfColor = style.getFillBackgroundColorColor();
    					XSSFColor xsfColor = style.getFillBackgroundXSSFColor();
    					if ( (xsfColor) != null ){
    						System.out.println (cellColor.toString());
    					}
    					System.out.println ("****");
    
    				}
    
    			}
    		}
    		catch (Exception e)
    		{
    			e.printStackTrace();
    		}
    
    	}
    Some help would be appreciated!
    Thanks in advance

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,086
    Rep Power
    20

    Default Re: Problem getting cell Color (XSSF)

    Does the cell have a background colour?
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  3. #3
    sashaxiv is offline Member
    Join Date
    Feb 2011
    Location
    Spain
    Posts
    26
    Rep Power
    0

    Default Re: Problem getting cell Color (XSSF)

    Yes for sure.
    The code works using HSSF and if I also save the original file as Excel 1997-2003 (xls), but I need it works with a xlsx (Excel 2010) file.

  4. #4
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,086
    Rep Power
    20

    Default Re: Problem getting cell Color (XSSF)

    HSSF and XSSF store things very differently, so I wouldn't use that as a guarantee.
    Are you sure, when you open your xlsx file in Excel, that the cell(s) in question have a background colour?
    If it's the default then I would not like to bet that the value is populated.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  5. #5
    sashaxiv is offline Member
    Join Date
    Feb 2011
    Location
    Spain
    Posts
    26
    Rep Power
    0

    Default Re: Problem getting cell Color (XSSF)

    Yes, I am completely sure that the rows have background colors :p

    The problem is that the original Excel is generated by another java aplication. If I modify this file with a new backgrounded row, the code above recognize this color, but the other rows are not recognized.

    Any ideas?
    Thank

  6. #6
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,086
    Rep Power
    20

    Default Re: Problem getting cell Color (XSSF)

    Have you looked at the XSSFCellStyle returned from the row, rather than the cell?
    Is that null as well?
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  7. #7
    sashaxiv is offline Member
    Join Date
    Feb 2011
    Location
    Spain
    Posts
    26
    Rep Power
    0

    Default Re: Problem getting cell Color (XSSF)

    Ok, I've checked it.

    The XSSFCellStyle returned by the row is not null, but it has no values for the atributtes fint and cellAligment.
    Thank you very much Tolls!

  8. #8
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,086
    Rep Power
    20

    Default Re: Problem getting cell Color (XSSF)

    And a quick read of the setRowStyle() method on XSSFRow:
    "
    If the value is null then the style information is removed, causing the cell to used the default workbook style.
    "
    Which makes me wonder if this is what you're seeing.
    You can get a list of styles held by a workbook.
    I'd look in those and see what's in there.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  9. #9
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,086
    Rep Power
    20

    Default Re: Problem getting cell Color (XSSF)

    Ah, good-oh.
    We Crossposted!
    :)
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  10. #10
    sashaxiv is offline Member
    Join Date
    Feb 2011
    Location
    Spain
    Posts
    26
    Rep Power
    0

    Default Re: Problem getting cell Color (XSSF)

    I think I will firstly save this file as Excel 2003 in order to work!

Similar Threads

  1. Replies: 1
    Last Post: 06-29-2012, 09:26 AM
  2. JTable Cell Color Follow Drag Event
    By joeyvitoro in forum AWT / Swing
    Replies: 6
    Last Post: 11-30-2011, 06:36 PM
  3. Replies: 2
    Last Post: 12-20-2010, 09:37 PM
  4. Replies: 5
    Last Post: 12-20-2010, 08:31 PM
  5. Color cell in JTable
    By ippacciani in forum AWT / Swing
    Replies: 3
    Last Post: 03-25-2009, 11:53 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •