PHP Excel

Dear Expert,

I am using PHPExcel (http://phpexcel.codeplex.com/) and I want to fill color for pattern but I cannot find how to do it. Please tell what should to do?

below is the pattern:
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_PATTERN_DARKGRID);

Thanks,
Rada
arkam chouAsked:
Who is Participating?
 
Eddie ShipmanAll-around developerCommented:
I actually found this one, too:

$objPHPExcel->getActiveSheet()->getStyle('C1')->getNumberFormat()->setFormatCode('_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)');

Open in new window

0
 
Eddie ShipmanAll-around developerCommented:
Did you try this from the 05-featuredemo example?

$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_PATTERN_DARKGRID);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('FF808080');

Open in new window

0
 
arkam chouAuthor Commented:
Dear EddieShipman,

Thanks you so much, I got the result as below. one more thing I want to format currency as in attachment. could you help me on this?

// pattern
$typeArray = PHPExcel_Style_Fill::FILL_PATTERN_GRAY125;
// color for pattern
$rowBkgStyle = array(
    'fill' => array(
        'type' => $typeArray,
        'startcolor' => array('rgb' => 'C6E0B4'), // for the pattern color
        'endcolor' => array('rgb' => 'FFFFFF')  // for the pattern background color
    )
);
//apply pattern
$objPHPExcel->getActiveSheet()
        ->getStyle("B1:J1")   // apply pattern for cell
        ->applyFromArray($rowBkgStyle);

Open in new window

currency.xlsx
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Eddie ShipmanAll-around developerCommented:
Do you not have any of the examples? I'm sure there is one that shows how to format data to Currency.
0
 
arkam chouAuthor Commented:
Please find format in attachment.
currency.xlsx
0
 
Eddie ShipmanAll-around developerCommented:
I meant the PHP Excel examples. I believe there is one there that shows how.

This is from their 05featuredemo and I changed the formatting to USD:
$objPHPExcel->getActiveSheet()->getStyle('E4:E13')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);

Open in new window

0
 
arkam chouAuthor Commented:
I had do but it cannot show like in column D in attachment.
0
 
Eddie ShipmanAll-around developerCommented:
Oh, I didn't see that it was Accounting format. Let me look...

From what I can see, there is no Accounting format in the PHPExcel formatters. However, I found the "format" for it on the web. Try this:

const FORMAT_ACCOUNTING_USD	= '_($* #,##0_);_($* (#,##0);_($* "-"_);_(@_)';


$objPHPExcel->getActiveSheet()->getStyle('E4:E13')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_ACCOUNTING_USD	);

Open in new window

0
 
Eddie ShipmanAll-around developerCommented:
Also found this very easily:
http://www.darkstarmedia.net/cms/phpexcel-adding-accounting-format-review/#.VYrBcPlVirM

Says essentially the same thing I said in my post #40848744 above.
0
 
Eddie ShipmanAll-around developerCommented:
So archam_chou, has my solution worked for you?
0
 
arkam chouAuthor Commented:
oh, I am sorry.
It work with your answer
$objPHPExcel->getActiveSheet()->getStyle('C1')->getNumberFormat()->setFormatCode('_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)');

Open in new window


How can I change number format in your answer from "$1234.00" to "$1234". I mean I want to pull out ".00".
0
 
Eddie ShipmanAll-around developerCommented:
Take out the .00 in these parts
("$"* #,##0.00_);_("$"* \(#,##0.00\)

Open in new window


Man, 3 questions for one, you're getting a great deal! I sure hope you accept it before the end of the month, this will take me over 3000 to keep my status.
0
 
arkam chouAuthor Commented:
Yes. it work.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.