Data Validation Produces Corrupted File in MicroSoft Excel, I have spreadsheet version 1.20 installed

[prakharDg21]

This is:

- [X] a bug report
- [ ] a feature request
- [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

I know read threads that it is resolved still facing same. when I did not upgrade to version 1.20 , My data was also not loading in sheet but after upgrade showing error but on repair at least showing data

What is the expected behavior?

Expected behaviour is on exporting the Excel file should open without repair and with validation in my case I am using dropdown list

What is the current behavior?

Currently excel shows error , that file needs repair , when we repair the data is available but dropdowns are not working

What are the steps to reproduce?

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

php <?php

require DIR . '/vendor/autoload.php';

//drop down validation for room types $roomTypeValidation = $mainWorksheet->getCell('B'. $rowIndexInteger)->getDataValidation(); $roomTypeValidation->setType( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST); $roomTypeValidation->setErrorStyle( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_INFORMATION ); $roomTypeValidation->setShowInputMessage(true); $roomTypeValidation->setShowErrorMessage(true); $roomTypeValidation->setShowDropDown(true); $roomTypeValidation->setErrorTitle('Input error'); $roomTypeValidation->setError('Value is not in list.'); $roomTypeValidation->setPromptTitle('Pick from list'); $roomTypeValidation->setPrompt('Please pick a value from the drop-down list.');

                        $roomTypes = $this->mRoomType->roomTypeCRUDConsumer('select', array('select' => 'name'));
                        $roomTypesList = '';
                        if($roomTypes['status']) {
                            foreach($roomTypes['data'] as $index => $room) {
                                $roomTypesList .= '"'.$room['name'].'",';
                            }
                        }

                        log_message('debug', logDataPreparer(__METHOD__, __FILE__, __LINE__, array('room type list' => $roomTypesList)));

                        $roomTypeValidation->setFormula1(rtrim($roomTypesList, ','));

// Create new Spreadsheet object $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet(); $excel->setActiveSheetIndexByName('main_networking'); $excelFilePath = 'NetworkingExcel.xlsx';

                    $writer = IOFactory::createWriter($excel, 'Xlsx');
                    $writer->save($excelFilePath);
                    $fileContent = file_get_contents($excelFilePath);
                    $base64Content = base64_encode($fileContent);
                    if ($base64Content) {
                        $responseData['data'] = $base64Content;
                        $responseData['status'] = 200;
                        $responseData['message'] = "Exported Successfully";
                    } else {
                        $responseData['data'] = '';
                        $responseData['status'] = FALSE;
                        $responseData['message'] = "There is some error please contact administrator";
                    }

                    echo json_encode($responseData);
                    unlink($excelFilePath);

// add code that show the issue here...

If this is an issue with reading a specific spreadsheet file, then it may be appropriate to provide a sample file that demonstrates the problem; but please keep it as small as possible, and sanitize any confidential information before uploading.

### What features do you think are causing the issue

- [ ] Reader
- [] Writer
- [ ] Styles
- [ X] Data Validations
- [ ] Formula Calculations
- [ ] Charts
- [ ] AutoFilter
- [ ] Form Elements

### Does an issue affect all spreadsheet file formats? If not, which formats are affected?
xlsx

### Which versions of PhpSpreadsheet and PHP are affected?
Php - 7.3 
phpspreadsheet - 1.20

[prakharDg21]

alright , i did not except everything to get in one line sorry for not formatting , please co operate , if still unreadable i can try formatting. getCell('B'. $rowIndexInteger)->getDataValidation(); $roomTypeValidation->setType( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST); $roomTypeValidation->setErrorStyle( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_INFORMATION ); $roomTypeValidation->setShowInputMessage(true); $roomTypeValidation->setShowErrorMessage(true); $roomTypeValidation->setShowDropDown(true); $roomTypeValidation->setErrorTitle('Input error'); $roomTypeValidation->setError('Value is not in list.'); $roomTypeValidation->setPromptTitle('Pick from list'); $roomTypeValidation->setPrompt('Please pick a value from the drop-down list.'); $roomTypes = $this->mRoomType->roomTypeCRUDConsumer('select', array('select' => 'name')); $roomTypesList = ''; if($roomTypes['status']) { foreach($roomTypes['data'] as $index => $room) { $roomTypesList .= '"'.$room['name'].'",'; } } log_message('debug', logDataPreparer(__METHOD__, __FILE__, __LINE__, array('room type list' => $roomTypesList))); $roomTypeValidation->setFormula1(rtrim($roomTypesList, ','));

// Create new Spreadsheet object $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet(); $excel->setActiveSheetIndexByName('main_networking'); $excelFilePath = 'NetworkingExcel.xlsx'; $writer = IOFactory::createWriter($excel, 'Xlsx'); $writer->save($excelFilePath); $fileContent = file_get_contents($excelFilePath); $base64Content = base64_encode($fileContent); if ($base64Content) { $responseData['data'] = $base64Content; $responseData['status'] = 200; $responseData['message'] = "Exported Successfully"; } else { $responseData['data'] = ''; $responseData['status'] = FALSE; $responseData['message'] = "There is some error please contact administrator"; } echo json_encode($responseData); unlink($excelFilePath);

[prakharDg21]

Please help.

[oleibman]

To properly format your code, put it between a line consisting of 3 backticks followed by the 3 characters php at the start, and 3 backticks at the end.

You should not be using Php7.3 any longer. You should upgrade Php, and then upgrade PhpSpreadsheet to a supported branch (possibly 1.29.4).

I can't really debug your problem without more information. In particular, I have no idea what is in $roomTypesList when you use it in setFormula. I am guessing that that is the source of your problem. At any rate, I have done my best to follow your code and simulate a value for that variable. The result produces an uncorrupted spreadsheet for me (using a more modern Php and PhpSpreadsheet). Cell B1 in the result uses Data Validation, and appears correct to me.

use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

$excel = new Spreadsheet();
$excelFilePath = 'issue.4236.xlsx';
$sheet = $excel->getActiveSheet();

$rowIndexInteger = 1; // simulated value
$roomTypeValidation = $sheet->getCell('B'. $rowIndexInteger)
    ->getDataValidation();
$roomTypeValidation->setType(DataValidation::TYPE_LIST);
$roomTypeValidation->setErrorStyle(DataValidation::STYLE_INFORMATION );
$roomTypeValidation->setShowInputMessage(true);
$roomTypeValidation->setShowErrorMessage(true);
$roomTypeValidation->setShowDropDown(true); 
$roomTypeValidation->setErrorTitle('Input error');
$roomTypeValidation->setError('Value is not in list.');
$roomTypeValidation->setPromptTitle('Pick from list');
$roomTypeValidation->setPrompt('Please pick a value from the drop-down list.');

// Use a simulated value for roomTypesList
$roomTypesList = '"Room1,Room2,Room3"';

$roomTypeValidation->setFormula1(rtrim($roomTypesList, ',')); 

$writer = IOFactory::createWriter($excel, 'Xlsx');
$writer->save($excelFilePath);
echo "saved $excelFilePath\n";

[prakharDg21]

Cant upgrade my PHP right away , PHP 8 has a lot of changes, It would require lot of planning before upgrade.Let me check with some hardcode values if it helps. Thanks for the help.

[prakharDg21]

HII , Thanks for the help it worked. @oleibman