Format of the cell not set to Date in Excel

Topics: Developer Forum, User Forum
Nov 10, 2010 at 5:52 AM

I am writing a date to a cell in an excel sheet. The excel datevalue is correctly written to the cell. But the format of the cell is displayed as General and not as date. Given below is the piece of my code that does this:   

$objPHPExcel->getActiveSheet()->setCellValue('C3', PHPExcel_Shared_Date::PHPToExcel(strtotime($curdate_export)));
$objPHPExcel->getActiveSheet()->getStyle('C3')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY);   

$curdate_export has a value of 2010-11-10. When I open the excel sheet, the cell has a value of 40492 (which is the excel value of 2010-11-10) and the format is general. When I change the format to dd-mm-yyyy in excel, the date is displayed correctly.

How do I make my code to change the format of the cell to date? Is there anything wrong with my setFormatCode? I am using this code within my drupal application. Would that be the cause? But all other data is getting written correcly onto the excel sheet.

I am using PHPExcel 1.7.4 and creating a xls document. Please help me in finding out where I am going wrong.

Thanks in advance

Coordinator
Nov 11, 2010 at 9:42 PM

I can't see where you're going wrong. I've tried running your code myself, cut-and-pasting the lines from your message, and it's generating the xls file correctly. What version of Excel are you using to open the file?

Nov 22, 2010 at 6:55 AM

Thanks MarkBaker. I figured out finally why it was not setting the format of  the cell. I was defining the style of the cell after setting the format to date. Now I moved the style before the statement which sets the format and it is working perfectly fine.

Now I have got an other issue. I am trying to set the following formula for a cell.

=COUNTIFS(E:E,"Major",D:D,"Documentation")

But it gives me an error. When I put the same in the excel sheet, it is accepted properly. Please let me know if PHPExcel is alright with using COUNTIFS for formulas.

Given below is the piece of code (in PHP) that writes the formula to the excel sheet.

    $major_formula = '=COUNTIFS(E:E,"Major",D:D,"Testing")';           
    $objWorksheet->setCellValue('M1', $major_formula);

Looking forward for your help.

Thanks in advance

 

 

Coordinator
Nov 23, 2010 at 8:23 AM
kdeepala wrote:

Now I have got an other issue. I am trying to set the following formula for a cell.

=COUNTIFS(E:E,"Major",D:D,"Documentation")

But it gives me an error. When I put the same in the excel sheet, it is accepted properly. Please let me know if PHPExcel is alright with using COUNTIFS for formulas.

The COUNTIFS() function is not currently supported. There are two files in the /Documentation folder -- FunctionListByName.txt and FunctionListByCategory.txt -- that list all of the Excel functions, and whether or not they are currently implemented.

At present, when saving a file using the Excel2007 or Excel5 Writers, PHPExcel saves both the formula and the calculated result... so calculating the result for a formula that contains a function that isn't implemented will result in an error. You can disable this pre-calculation by setting $objWriter->setPreCalculateFormulas(false); befor eyou save. When the saved file is then opened in Excel, it may automatically recalcuate all formulae, might prompt the user to recalculate, or (worst case) not let the user know that a recalculation is necessary and display invalid values in those cells (that's why we try to precalculate when we can).

Other writers, such as CSV, HTML and PDF require formulas to be calculated within PHPExcel so that the correct result can be rendered; so disabling precalculation is not an option.

Note also that the Excel5 Writer doesn't allow formulae containing functions that aren't part of the Excel5 core set, so Analysis Toolkit functions are not supported.

Nov 23, 2010 at 10:26 AM

Thank you again. I will substitue COUNTIFS with equivalent IF statements. Does phpexcel support & (ampersand) in formulas. For example can I define something like following:

              ->setCellValue('G16', '="Effort (Hrs)"& ROUND(IF(J18=0,0,K17/J18)*100,0)&"%    " &"       Variance     "')

Thanks in advance

 

Coordinator
Nov 27, 2010 at 9:57 PM

The & operator is not supported in the 1.7.4 Excel5 Writer, although it is supported in the Excel2007 Writer, and in the latest SVN code for the Excel5 Writer. See Work Item 7895.