Locale Settings for Formulae

Some localisation elements have been included in PHPExcel. You can set a locale by changing the settings. To set the locale to Russian you would use:

$locale = 'ru';

$validLocale = PHPExcel_Settings::setLocale($locale);

if (!$validLocale) {

echo 'Unable to set locale to '.$locale." - reverting to en_us<br />\n";

}

If Russian language files aren’t available, the setLocale() method will return an error, and English settings will be used throughout.

Once you have set a locale, you can translate a formula from its internal English coding.

$formula = $objPHPExcel->getActiveSheet()->getCell('B8')->getValue();

$translatedFormula =

PHPExcel_Calculation::getInstance()->_translateFormulaToLocale($formula);

You can also create a formula using the function names and argument separators appropriate to the defined locale; then translate it to English before setting the cell value:

$formula = '=ДНЕЙ360(ДАТА(2010;2;5);ДАТА(2010;12;31);ИСТИНА)';

$internalFormula =

PHPExcel_Calculation::getInstance()->translateFormulaToEnglish($formula);

$objPHPExcel->getActiveSheet()->setCellValue('B8',$internalFormula);

Currently, formula translation only translates the function names, the constants TRUE and FALSE, and the function argument separators.

At present, the following locale settings are supported:

4.6.6. Write a newline character "\n" in a cell (ALT+"Enter")

In Microsoft Office Excel you get a line break in a cell by hitting ALT+"Enter". When you do that, it automatically turns on "wrap text" for the cell.

Here is how to achieve this in PHPExcel:

$objPHPExcel->getActiveSheet()->getCell('A1')->setValue("hello\nworld");

$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setWrapText(true);

i Tip

Read more about formatting cells using getStyle() elsewhere.

i Tip

AdvancedValuebinder.php automatically turns on "wrap text" for the cell when it sees a newline character in a string that you are inserting in a cell. Just like Microsoft Office Excel. Try this:

require_once 'PHPExcel/Cell/AdvancedValueBinder.php';

PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() );

$objPHPExcel->getActiveSheet()->getCell('A1')->setValue("hello\nworld");

Read more about AdvancedValueBinder.php elsewhere.

4.6.7. Explicitly set a cell’s datatype

You can set a cell’s datatype explicitly by using the cell’s setValueExplicit method, or the setCellValueExplicit method of a worksheet. Here’s an example:

$objPHPExcel->getActiveSheet()->getCell('A1')->setValueExplicit('25', PHPExcel_Cell_DataType::TYPE_NUMERIC);

Date: 2016-03-03; view: 1212