php excel 数值验证,PHPExcel：数据验证无法以.xls格式运行

我有一个带有两张表的excel文件：

>工作表;

>列表 – 将在工作表工作表中显示为列表项的项目列表.

请看下面的图片：

我想使用PHPExcel库生成它.我试过但没有得到预期的结果.看下面的代码：

$objPHPExcel = new PHPExcel();

// Set document properties

$objPHPExcel->getProperties()->setCreator("Soumya Biswas")

->setLastModifiedBy("Soumya Biswas")

->setTitle("Office 2007 XLSX Test Document")

->setSubject("Office 2007 XLSX Test Document")

->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")

->setKeywords("office 2007 openxml php")

->setCategory("Test result file");

// Create a first sheet

$objPHPExcel->setActiveSheetIndex(0);

$objPHPExcel->getActiveSheet()->setCellValue('A5', "List");

// Set data validation

$objValidation = $objPHPExcel->getActiveSheet()->getCell('B5')->getDataValidation();

$objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_LIST );

$objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_INFORMATION );

$objValidation->setAllowBlank(false);

$objValidation->setShowInputMessage(true);

$objValidation->setShowErrorMessage(true);

$objValidation->setShowDropDown(true);

$objValidation->setErrorTitle('Input error');

$objValidation->setError('Value is not in list.');

$objValidation->setPromptTitle('Pick from list');

$objValidation->setPrompt('Please pick a value from the drop-down list.');

$objValidation->setFormula1('"$List.$A$1:$A$10"'); // Make sure to put the list items between " and " !!!

$objPHPExcel->createSheet();

$objPHPExcel->setActiveSheetIndex(1);

$objPHPExcel->getActiveSheet()->setTitle('List');

for ($i = 1; $i <= 10; $i++) {

$objPHPExcel->getActiveSheet()->setCellValue("A{$i}", "List Item {$i}");

}

// Set active sheet index to the first sheet, so Excel opens this as the first sheet

$objPHPExcel->setActiveSheetIndex(0);

header('Content-Type: application/vnd.ms-excel');

header('Content-Disposition: attachment;filename="data-validation.xls"');

header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

$objWriter->save('php://output');

exit;

解决方法:

我认为在另一个工作表中引用单元格范围的正确语法是：

List!$A$1:$A$10

所以你应该尝试：

$objValidation->setFormula1('List!$A$1:$A$10'); // tested it, worked for me

->setFormula1(“Worksheet!A1:{$endCell}1”);// work….

虽然这家伙使用命名范围还有另一个问题.

背景：我认为：

$objValidation->setFormula1('"$List.$A$1:$A$10"');

你明确地使用引号之间的给定字符串作为列表值,如下所述：here(你可能首先得到这个片段)或here.

但由于您不想使用固定列表项而是动态引用的项,因此应省略双引号.

标签：php,oop,phpexcel

来源： https://codeday.me/bug/20190529/1177511.html