Cells background turn black

Topics: Developer Forum, User Forum
May 24, 2010 at 3:59 PM

I'm on

phpExcel 1.7.3

Mysql 5.0.90

Php 5.2.10

Apache 2.0

 

I'm opening an xlsx spreadsheet which is "nicely" coloured. After closing the file and downloading it. Every sheet comes out black and the fixed cell coordinates apear elsewhere.

 

I'd appreciate If someone can help me with this.

 

Coordinator
May 26, 2010 at 9:19 AM

What version of Excel are you using to open the xlsx file? If you're using Excel2003 with the compatibility pack, but the style is using too many "custom colours", then they may appear black

May 27, 2010 at 3:52 AM

The file was created on office 2007 and saved as extension xlsx.

Coordinator
May 27, 2010 at 8:38 AM

So after you've downloaded it, you're opening it again using Excel2007.

I've never come across that before: do the test scripts in the /Tests directory work, or do they appear black as well?

May 27, 2010 at 4:08 PM
Tests scripts came out fine.

Do you want me to send you the file?


Coordinator
May 27, 2010 at 10:59 PM
DiegoG wrote:
Do you want me to send you the file?

 If you can... I'll run a few tests to see if I can identify what's happening.

May 28, 2010 at 12:39 PM
the file is uploaded here
http://phpexcel.codeplex.com/Project/Download/AttachmentDownload.ashx?ProjectName=PHPExcel&WorkItemId=13374&FileAttachmentId=4484

under the name ventas.xlsx

regards
Diego G./
Nov 13, 2010 at 6:28 AM

Any resolution to this?  I am suffering the same problem.

Thanks,

Mark

Coordinator
Nov 13, 2010 at 12:25 PM

At this point, I have no solution. I can't consistently replicate the problem. Most times it seems to work perfectly correctly, at other times I do get the black background (but only very rarely) -- which makes diagnosis very hard.

Nov 20, 2010 at 5:31 PM
Edited Nov 20, 2010 at 5:31 PM

Hi,

If I may add my thoughts - I can also replicate this consistently (happens with all my files created in Excel 2007 before loading them into PHPExcel).

Are any of the files in /Tests created using Excel proper or are they all generated from scratch?

I believe that is what the problem is, the files are created by the Excel application rather than PHPExcel.

Thanks,
-Will 

Coordinator
Nov 20, 2010 at 7:52 PM
seuj wrote:

Hi,

Are any of the files in /Tests created using Excel proper or are they all generated from scratch?

I believe that is what the problem is, the files are created by the Excel application rather than PHPExcel.

The files in /Tests are all generated by PHPExcel itself, but I use other test files on a regular basis that are generated in various versions of Excel (95, 97, 2003, 2007 and 2010) that sometimes display this issue, other times not.

Nov 20, 2010 at 11:13 PM
Edited Nov 20, 2010 at 11:16 PM

Thanks for the clarification Mark. Is there anything we can help you with to diagnose? 

E.g. perhaps the version of Windows/Office in our environment as my setup using XP/Excel 2007 and Windows 7/Excel 2010 creates this issue without fail every time.

Anything we can do to help nail this down - let me know!

 

Edit: and I forgot to mention that I've tried explicitly setting a background on the cells I modify, e.g. white. When I do this, these filled cells no longer exhibit the problem, but those I haven't explicitly filled do, i.e. on editing the cells they appear black but on editing the cell with a white background, the background remains white.

Is there a command that PHPExcel can issue to reset a background to 'no fill' (or whatever the parlance is in Excel terminology) once it has been filled?

Jan 14, 2011 at 10:33 PM

Hello,

I also have this problem. For all cells with the background color defined on "no fill", there is no problem. But when I fill a cell, it turns to black after the generation.

Coordinator
Jan 14, 2011 at 10:42 PM

@Zick

Is this when you're reading a file and then saving it again, or simply when creating a workbook from a new PHPExcel object.

If when reading a file, then could you try this with the latest code from the SVN repository, accessible from the "Source Code" tab here on phpexcel.net. This should have been fixed since the 1.7.5 release.

If when creating a new workbook, then a solidfill cell only uses the StartColor. If this is still a problem, can you show me the code that you're using?

Mar 3, 2011 at 11:48 AM

Hello, I'm having this same problem, I think. Here's what happens:

  • I open a template.xlsx (some cells have background colors).
  • I insert some data using setCellValueByColumnAndRow()
  • I write it as a new .xlsx file.

Result:

On the new xslx file all background colors get changed to black.

 

I'm on

phpExcel 1.7.5

Php 5.3.5

 

Any idea why?

Mar 3, 2011 at 11:54 AM
This issue is solved in next version or on the repository
From: "agucho" <notifications@codeplex.com>
Date: 3 Mar 2011 03:48:19 -0800
To: <diegoguidobono@gmail.com>
ReplyTo: PHPExcel@discussions.codeplex.com
Subject: Re: Cells background turn black [PHPExcel:213643]

From: agucho

Hello, I'm having this same problem, I think. Here's what happens:

  • I open a template.xlsx (some cells have background colors).
  • I insert some data using setCellValueByColumnAndRow()
  • I write it as a new .xlsx file.

Result:

On the new xslx file all background colors get changed to black.

I'm on

phpExcel 1.7.5

Php 5.3.5

Any idea why?

Mar 3, 2011 at 12:31 PM

Yes, that worked. Thanks.

Mar 3, 2011 at 3:58 PM

Good to hear! Can you clarify - since the OP was using 1.7.5 does "solved in the next version" mean 1.7.6 or just the current repository?

Many thanks.

Mar 3, 2011 at 4:01 PM
It was fixed for version 1.7.6. (Current one)
From: "seuj" <notifications@codeplex.com>
Date: 3 Mar 2011 07:58:49 -0800
To: <diegoguidobono@gmail.com>
ReplyTo: PHPExcel@discussions.codeplex.com
Subject: Re: Cells background turn black [PHPExcel:213643]

From: seuj

Good to hear! Can you clarify - since the OP was using 1.7.5 does "solved in the next version" mean 1.7.6 or just the current repository?

Many thanks.

Mar 3, 2011 at 4:02 PM

For me, repository version solved the issue. Checked out 2hs ago.

Agustin.

Mar 3, 2011 at 4:14 PM
Edited Mar 3, 2011 at 4:21 PM

Thanks for the confirmation guys. 1.7.6 doesn't fix my issue; I might be seeing something different then - can others confirm?

My cells aren't coloured black but rather when you go to edit the contents of the cells in Excel, the entire edit block is black and the text unreadable as it is also black.

For an example please see: files.seuj.net/browse/screens/phpexcel.png - top is before and bottom is during a cell edit in Excel. If it matters, the gridlines are off in my template spreadsheet but the cells have no background fill.

If this is the same issue I will fiddle with the repository copy to check.

Thanks all.

Mar 4, 2011 at 3:01 PM

same problem here - in both 1.7.4 and 1.7.6

 

and seuj yes that is similar

Mar 4, 2011 at 6:44 PM

Can confirm that this unfortunately hasn't fixed my issue - either 1.7.6 or repository checkout as of 5 minute ago.

In my template (pre-PHPExcel) if I look at the formatting properties of my cell (right click, 'Format cells...'), the 'Background color' is specifically set to 'No color' and 'Pattern color' is 'Automatic'.

After my file has run through PHPExcel however, the same formatting properties of the cell don't seem to be applied any more. Specifically, the cell has no 'Background color' set at all but 'Pattern colour' is still set as 'Automatic'.

Choosing 'No colour' as the 'Background color' in Excel then restores the intended behaviour for the selected cells.

It's worth noting that the 'Automatic' 'Pattern colour' is black, which is most likely the problem, given that the black is showing through as the cell has lost its 'Background color' setting.

Mar 7, 2011 at 11:41 AM

as a way round this bug, i'm now at the end of the file re-formatting my cells with a background colour of white. which seems to work

Mar 7, 2011 at 11:46 AM

I've been using a similar method all along until this is fixed. If it helps (as I am using a template), I pre-fill my entire worksheet with white backgrounds in the template before running it through PHPExcel - saves memory and time on every file creation. I'm also not using gridlines so this looks quite natural anyway.

Mar 17, 2011 at 4:32 PM

oh and btw....not using templates and creating excel from scratch

Sep 8, 2011 at 4:30 PM

I have got the same problem with PHPExcel 1.7.6. I use Office 2007. Then I open a Excel 2007 file :

 

        $vReader = new PHPExcel_Reader_Excel2007();
        if(file_exists($pFileSourcePath)) {
            if($vReader->canRead($pFileSourcePath)) {
                $objPHPExcel = $vReader->load($pFileSourcePath);
            }
        }


        $objPHPExcel->setActiveSheetIndex(0);
        $pFileName = 'test.xlsx';
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$pFileName.'"');
        header('Cache-Control: max-age=0');
   
        $objWriter = PHPExcel_IOFactory::createWriter($pObjPHPExcel, 'Excel2007');
        $objWriter->save('php://output');

The cells background color turns to black while I type caracters in it. Once the cell loses the focus, the back ground turns to the original color.

 

Sep 19, 2011 at 4:08 PM

Same problem here (1.7.6)

Happens every time i load a xlsx template and write in it.


I made a Workaround with fake excel grid while a wait for a fix:

$highestRow = $objPHPExcel->getActiveSheet()->getHighestRow();
$highestColumn = $objPHPExcel->getActiveSheet()->getHighestColumn();
$starting_row = 6;

 $objPHPExcel->getActiveSheet()->getStyle("A".$starting_row.":".$highestColumn.$highestRow)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
$objPHPExcel->getActiveSheet()->getStyle("A".$starting_row.":".$highestColumn.$highestRow)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle("A".$starting_row.":".$highestColumn.$highestRow)->getBorders()->getAllBorders()->getColor()->setARGB("FFd0d7e5");

 

Oct 4, 2011 at 11:41 AM

Same problem (1.7.6)

I've got a Fix :

in PHPExcel/Reader/Excel2007.php, just comment the line 1609 =>

if ($style->fill->patternFill->fgColor) {
	$docStyle->getFill()->getStartColor()->setARGB(self::_readColor($style->fill->patternFill->fgColor,true));
} else {
	//Comment line 1609  $docStyle->getFill()->getStartColor()->setARGB('FF000000');
}

Oct 7, 2011 at 8:52 AM

Thanks quaki.

Another solution is to fill the background color in white :       

$objPHPExcel->getActiveSheet()->getStyle($cellRange)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_NONE);      
$objPHPExcel->getActiveSheet()->getStyle($cellRange)->getFill()->getStartColor()->setARGB('FFFFFFFF');
Jan 20, 2012 at 7:43 AM
quaki wrote:  //Comment line 1609 $docStyle->getFill()->getStartColor()->setARGB('FF000000'); }

 

Thanks Quaki. That fixed it for me.  Will this fix be put into the next revision or does it break something else?

Apr 19, 2014 at 2:58 AM
I'm using version 1.7.7 and I'm facing this issue. @quaki's fix still works, though now it's on line 1813. I second the request to put this change into a future release.