公式错误 [英] Error with the formulas

问题描述

我想将公式设置为单元格，但是我在途中遇到错误.有一个代码:

I want to set formula to cell but im getting error on my way. Theres a code:

  $objReader = PHPExcel_IOFactory::load($path.$filename);
    $objReader->setActiveSheetIndex(0);
    $objReader->getActiveSheet()
                        ->setCellValue('C2','=VLOOKUP(A9;A3:B32;2)');

    $objWriter = PHPExcel_IOFactory::createWriter($objReader, 'Excel5');
    $objWriter->save($path.'plik.xls'); 

配方是从普通图纸复制而来的，所以她很健壮.我正在收到此味精:feuil1!C2 -> Formula Error: An unexpected error occured.

Formula is copied from normal sheet so she's wright. Im getting this msg: feuil1!C2 -> Formula Error: An unexpected error occured.

推荐答案

除非您启用了使用;作为参数分隔符的语言环境，否则必须使用英语(en_us)参数分隔符(即逗号) ,)

Unless you've enabled a locale that uses ; as an argument separator, then you must use an English (en_us) argument separator (ie, a comma ,)

从手册中引用:

Inside the Excel file, formulas are always stored as they would appear in an English version of Microsoft Office Excel, and PHPExcel handles all formulae internally in this format. This means that the following rules hold:
•  Decimal separator is '.' (period)
•  Function argument separator is ',' (comma)
•  Matrix row separator is ';' (semicolon)
•  English function names must be used
This is regardless of which language version of Microsoft Office Excel may have been used to create the Excel file.

如此:

$objReader = PHPExcel_IOFactory::load($path.$filename);
$objReader->setActiveSheetIndex(0);
$objReader->getActiveSheet()
    ->setCellValue('C2','=VLOOKUP(A9,A3:B32,2)');

$objWriter = PHPExcel_IOFactory::createWriter($objReader, 'Excel5');
$objWriter->save($path.'plik.xls'); 

编辑

如果要使用法语公式，则需要启用语言环境，并在法语和英语之间转换公式:

If you want to use French language formulae, then you need to enable the locale, and convert the formulae between French and English:

$objReader = PHPExcel_IOFactory::load($path.$filename);
$objReader->setActiveSheetIndex(0);

$locale = 'fr';
$validLocale = PHPExcel_Settings::setLocale($locale);
if (!$validLocale) {
echo 'Unable to set locale to '.$locale." - reverting to en_us<br />\n";

    $internalFormula = '=VLOOKUP(A9,A3:B32,2)';
} else {
    $formula = '=RECHERCHEV(A9;A3:B32;2)';
    $internalFormula = 
        PHPExcel_Calculation::getInstance()->translateFormulaToEnglish($formula);
}
$objReader->getActiveSheet()
    ->setCellValue('C2', $internalFormula);

$objWriter = PHPExcel_IOFactory::createWriter($objReader, 'Excel5');
$objWriter->save($path.'plik.xls'); 

这篇关于公式错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！