javascript - 如何扩展字符串限制，同时使用 PHP 将其从 MYSQL 数据库转换为下拉列表中的 Excel

Question

我正在尝试将 MySQL 数据作为下拉列表保存到 Excel 文件中，但我遇到了字符串限制问题。如果字符串限制超过 255，Excel 文件就会损坏。这是我的代码:

foreach ($sequence as $sequence_no) {
if ($pattern_record['attribute_' . $sequence_no . '_name'] != "") {
    $cell = $objWorkSheet->getCell($column . $row);
    $cell->setValue($pattern_record['attribute_' . $sequence_no . '_name']);
    if (!empty($pattern_record['attribute_' . $sequence_no . '_variable_value'])) {
        for ($j = 2; $j <= $no_emp; $j++) {
            $excel->setActiveSheetIndex(0);
            $excel->getActiveSheet()
                    ->setCellValue($column . "" . $j, "");
            $configs = $pattern_record['attribute_' . $sequence_no . '_variable_value'];
            $objValidation = $excel->getActiveSheet()->getCell($column . $j)->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('"' . $configs . '"');
        }
    }

    //check and set datatype in excel
    //General format
    else if (in_array($sequence_no, $datatype_varchar)) {
        $excel->getActiveSheet()->getStyle($column)
                ->getNumberFormat()
                ->setFormatCode(
                        PHPExcel_Style_NumberFormat::FORMAT_TEXT
        );
    } else if (in_array($sequence_no, $datatype_text)) {
        //text format
        $excel->getActiveSheet()->getStyle($column)
                ->getNumberFormat()
                ->setFormatCode(
                        '0'
        );
    } else if (in_array($sequence_no, $datatype_int)) {

        // numerical format
        $excel->getActiveSheet()->getStyle($column)
                ->getNumberFormat()
                ->setFormatCode(
                        '0'
        );
    } else if (in_array($sequence_no, $datatype_date)) {

        // date format
        $excel->getActiveSheet()->getStyle($column)->getNumberFormat()
                ->setFormatCode('yyyy-mm-dd');
    }
    $excel->getActiveSheet()->getStyle("$column$row:$column$row")->getFont()->setSize(14);
    $excel->getActiveSheet()->getStyle("$column:$column")->getFont()->setSize(12);

    $excel->getActiveSheet()
            ->getColumnDimension($column)
            ->setAutoSize(true);
    $excel->getActiveSheet()->getStyle($column . $row . ':' . $column . $row)->getFill()
            ->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
            ->getStartColor()->setARGB('FFE8E5E5');


    $col++;
    $column++;
} }

如果数据长度在 255 之间，则下载正常，但如果超过，则会损坏。请提出一些建议。

Answer 1

不要将数据直接放入下拉列表中。将其写入一组单元格，然后将下拉列表设置为单元格范围。这样，它就不受数据验证列表的 MS Excel 字符串限制的限制