1

I am trying to create an excel sheet using PHPExcel, modify the data in the excel sheet then download the modified data that I created into a template format i have stored on my server. I noticed that once I downloaded the sheet with data in it, that the error checking/data validation was gone, most of it was drop downs for valid options.

I noticed from a previous question asked here that PHPExcel doesn't support array formulas, are the dropdowns/data validation options in Excel considered array formula?

If not then how can I load the data into a Template Excel Sheet already stored on my server using PHPExcel and maintain the dropdown boxes that are already saved into the Excel Sheet?

edit: To clarify I already have an excel sheet that I am using as a template document, I am simply loading the excel sheet, adding data to it, and then saving it and sending it for download to the user. The formatting and column headers show up fine after I open the newly downloaded sheet, but validation checks/ and drop down boxes are missing.

Here is my code

$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
$sheet = $objPHPExcel->getActiveSheet();

$sheet->getCell('A1')->setValue('new value');
$sheet->getCell('B1')->setValue('y');

$url = 'templateFile.xlsx';
$outputFileType = PHPExcel_IOFactory::identify($url);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, $outputFileType);
$objWriter->save("temp/templateFileWithData.xlsx");

in the above example code, cell B1 would have a warning text and a drop down box on it asking users to select 'Y' or 'N' as the only valid options

Thanks for any help.

Community
  • 1
  • 1
ebellefontaine
  • 140
  • 12
  • Dropdowns/data validations are nothing to do with formulae at all, in MS EXcel or in PHPExcel.... take a look at `/Examples/15datavalidation.php` to see how to create data validation using PHPExcel – Mark Baker Aug 05 '15 at 14:22
  • If you already have a template file (which is unclear from your code and your question) then simply load it, write your new data and save it – Mark Baker Aug 05 '15 at 14:23
  • Yes I have a formatted template file with validation checks/ drop downs already in it, I load it, add data using the code example above and it turns out fine with the exception that the data validation/drop down box is missing – ebellefontaine Aug 05 '15 at 14:43
  • It shouldn't be lost, can you provide a copy of your template? – Mark Baker Aug 05 '15 at 14:44
  • https://drive.google.com/file/d/0B8aE9p7Y-nonckg3M3RmZFdOWG8/view?usp=sharing – ebellefontaine Aug 06 '15 at 15:29

0 Answers0