Cell fill pattern color

Topics: User Forum
Jan 19, 2011 at 10:02 PM

Hi, I'm using PHPExcel 1.7.5 with PHP 5.3.3 on a linux Synology diskstation. I have just learned of PHPExcel and this is a great project!

I am able to update cell values in an existing Excel 2010 xlsx sheet with no problem. I noticed however the PHPExcel Writer is modifying the cell pattern fill color for the entire sheet. In Excel's cell formatting option it shows the fill pattern color as solid black. Prior to PHPExcel saving the sheet, this was set to the default "Automatic". In order to fix this I have to manually re-select "Automatic" for all cells in the sheet. Please let me know if there's a work-around for this or if I'm doing something wrong.

Here's my code:

$inputFileType = 'Excel2007';
$inputFileName = 'file.xlsx';
$sheetname = 'current';
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setLoadSheetsOnly($sheetname);
$objPHPExcel = $objReader->load($inputFileName);
$sheet = $objPHPExcel->getActiveSheet();
$sheet->setCellValue('D' . $row->getRowIndex (), "45");
...
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, $inputFileType);
$objWriter->save($inputFileName);

Any help would be appreciated.

Thank you kindly

Jason.

Coordinator
Jan 19, 2011 at 10:12 PM

This problem should be resolved in the latest SVN code. This can be accessed from the "Source Code" tab here on phpexcel.net

Feb 6, 2011 at 6:18 PM

Hi Mark, PHPExcel is really great project. I use it for my latest project.

I have same problem with jasonrushton

I have download latest SVN but it still not change about Cell Fill Pattern

Could you ponting me which class files to update/replace 

 

Thanks

Coordinator
Feb 6, 2011 at 6:24 PM
Edited Feb 6, 2011 at 6:27 PM

@sibiru

It's not really practical to list the set of files that were modified for this fix, mainly because I'd have to work back through all the changes I've made this year to identify them (and some of those files have certainly been modified subsequently as part of other fixes/changes, which may require further files). It's easiest just to download the entire daily build.

If this still isn't working correctly for you, can you create a workbook that demonstrates the specifics of the problem that you can upload.

Feb 7, 2011 at 6:16 AM

OK Mark, thanks for helping out. Keep PHPExcel live....

Feb 7, 2011 at 9:15 PM

Hi Mark, I am tried PHPExcel build 67536 and the cell fill pattern issue seems to still occur. I can upload the xlsx file, just let me know where.

 

 

Coordinator
Feb 7, 2011 at 10:06 PM
Edited Feb 8, 2011 at 8:54 AM

Files can be uploaded to this Work Item

Feb 7, 2011 at 10:56 PM

That link causes this error:

The server experienced an error.

We're sorry, but an error has occurred. We have been notified about the error.

Most likely this was an intermittent issue. Please try your task again. If you continue to experience issues or would like to send us more information regarding this error, please contact us here.

Coordinator
Feb 8, 2011 at 8:55 AM

Spurious dot (.) at the end of the link... please try again.

Feb 8, 2011 at 9:37 AM

Hi Mark, the file has been uploaded as follows:

http://phpexcel.codeplex.com/Project/Download/AttachmentDownload.ashx?ProjectName=phpexcel&WorkItemId=10749&FileAttachmentId=5358

When you double click a cell to edit, notice the cell's fill color changes to black and you can no longer see the text because its black on black.

Mar 11, 2011 at 11:13 PM

I have the same problem, how do I change Pattern Color to 'Automatic' with PHPExcel?

Mar 22, 2011 at 8:41 AM

Hi jasonrushton

Did you fix the cell back color turn black?

If you fix, could you share me the solution.

 

Thanks,

thenndral.

Mar 23, 2011 at 10:39 AM

Hi thenndral, I have to use Excel manually to change the fill color back to no fill for the entire sheet. I have not found a method to do this in PHPExcel.

Mar 24, 2011 at 1:32 AM

Hi jasonrushton,

Thanks for your reply.
I fix it.Here is the code. I hope it works for you also, try and let me know the code is  working or not.
$highestColumn = "XFD";
$highestRow ="10000";
$objPHPExcel->getActiveSheet()->getStyle('a1:' .$highestColumn.$highestRow)->getFill()->getFillType(PHPExcel_Style_Fill::FILL_NONE);
$objPHPExcel->getActiveSheet()->getStyle('a1:' .$highestColumn.$highestRow)->getFill()->getStartColor()->setRGB('FFFFFF');

I have one question, I give maximum highest column and highest row manually. If I want to set for the complete cell ie. active sheet. how to set? please reply me.

thanks again,
thenndral
Mar 24, 2011 at 10:45 AM

that's the easy part ;)

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

Mike

 

 

Mar 25, 2011 at 1:23 AM

Hi,

Thanks for your reply.

getHighestColumn() and getHighestRow returns, the cell contain data column or row.

for example, In my excel contain the maximum data in the column "Q" and in row "1050" it returns

 Q and 1050 only. so cell back color change only this part, the remaining part is remains black the user suppose to edit the cell.

suppose if the user change some data on "R1500" the cell background goes black.

so I need to change the cell color entire sheet. please tell me how to get the entire column or just give my manually like.

$highestColumn = "XFD"; // this is the maximum column name
$highestRow ="1048576"; // this is the maximum row number, i think so.
I think you don't confuse my question. If so I'm sorry let me explain after your reply.
Thanks for being with me. :)
thenndral



 

Mar 25, 2011 at 8:32 AM

omg...again!

Its Terrible :( everything going fine...I just update and my testing team found an error regarding cell back color.

$highestColumn = "XFD";
$highestRow ="10000";
$objPHPExcel->getActiveSheet()->getStyle('a1:' .$highestColumn.$highestRow)->getFill()->getFillType(PHPExcel_Style_Fill::FILL_NONE);
$objPHPExcel->getActiveSheet()->getStyle('a1:' .$highestColumn.$highestRow)->getFill()->getStartColor()->setRGB('FFFFFF');

the above code is not working for merged cells, so sad: :(

Any Ideas to solve....

 

thanks again,

thenndral

Mar 28, 2011 at 1:58 AM

Thanks. I fix it.

 

thanks,

thenndral

Mar 31, 2011 at 12:31 PM

It looks like the PHPExcel_Style_Fill's functionality is not sufficient anymore for all Excel2007+ features regarding backgrounds and patterns. As a side effect the Excel2007 writer outputs styles information that is not conform how Excel2007 it does, here is a patch that fixes the issue in most of the cases:

Index: PHPExcel/Writer/Excel2007/Style.php
===================================================================
--- PHPExcel/Writer/Excel2007/Style.php	(revision 71191)
+++ PHPExcel/Writer/Excel2007/Style.php	(working copy)
@@ -245,15 +245,21 @@
 			$objWriter->startElement('patternFill');
 				$objWriter->writeAttribute('patternType', $pFill->getFillType());
 
-				// fgColor
-				$objWriter->startElement('fgColor');
-				$objWriter->writeAttribute('rgb', $pFill->getStartColor()->getARGB());
-				$objWriter->endElement();
+        if ($pFill->getFillType() != PHPExcel_Style_Fill::FILL_NONE)
+        {
+					// fgColor
+					$objWriter->startElement('fgColor');
+					$objWriter->writeAttribute('rgb', $pFill->getStartColor()->getARGB());
+					$objWriter->endElement();
 
-				// bgColor
-				$objWriter->startElement('bgColor');
-				$objWriter->writeAttribute('rgb', $pFill->getEndColor()->getARGB());
-				$objWriter->endElement();
+          if ($pFill->getFillType() != PHPExcel_Style_Fill::FILL_SOLID)
+          {
+						// bgColor
+						$objWriter->startElement('bgColor');
+						$objWriter->writeAttribute('rgb', $pFill->getEndColor()->getARGB());
+						$objWriter->endElement();
+					}
+				}
 
 			$objWriter->endElement();
Apr 19, 2012 at 1:56 PM

I tried the fix from dverspui, but didnt worked.

 

I modified it a bit:

	/**
	 * Write Pattern Fill
	 *
	 * @param 	PHPExcel_Shared_XMLWriter			$objWriter 		XML Writer
	 * @param 	PHPExcel_Style_Fill					$pFill			Fill style
	 * @throws 	Exception
	 */
	private function _writePatternFill(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Style_Fill $pFill = null)
	{
		// fill
		$objWriter->startElement('fill');

			// patternFill
			$objWriter->startElement('patternFill');
				$objWriter->writeAttribute('patternType', $pFill->getFillType());

				if ($pFill->getFillType() != PHPExcel_Style_Fill::FILL_NONE){
					// fgColor
					$objWriter->startElement('fgColor');
					$objWriter->writeAttribute('rgb', $pFill->getStartColor()->getARGB());
					$objWriter->endElement();

					// bgColor
					$objWriter->startElement('bgColor');
					$objWriter->writeAttribute('rgb', $pFill->getEndColor()->getARGB());
					$objWriter->endElement();
				}
				
				// fgColor
// 				$objWriter->startElement('fgColor');
// 				$objWriter->writeAttribute('rgb', $pFill->getStartColor()->getARGB());
// 				$objWriter->endElement();

// 				// bgColor
// 				$objWriter->startElement('bgColor');
// 				$objWriter->writeAttribute('rgb', $pFill->getEndColor()->getARGB());
// 				$objWriter->endElement();

			$objWriter->endElement();

		$objWriter->endElement();
	}
Aug 21, 2012 at 1:02 PM

Hi Mark,

Is there any way I can change the default color or bars in bar chart or color of lines in line chart? Currently I am not able to find any way to change the default color of data series while exporting charts in Excel.

I would also like to know the way to show data labels for each bar or line in the chart and how to change the orientation of axis labels?