What should I do if PHPSpreadsheet reports an error when exporting Excel with more than 26 columns?

What should I do if PHPSpreadsheet reports an error when exporting Excel with more than 26 columns? The following article will introduce to you the solution for PhpSpreadsheet to export Excel with more than 26 columns. I hope it will be helpful to everyone.

When I used PhpSpreadsheet to export an excel file, I found that an error was reported. After querying the problem, I found that the number of columns exceeded 26. Let’s take a look at the solution.

excel column representation method

• xexcel column representation rules are from A, B, C to Z, When it exceeds 26 letters, it is represented by two letters: AA, AB, AC...AZ, BA, BB, BC...BZ..., when it exceeds 702, it is represented by another method.

• The rows represent 1, 2, 3, 4, 5, 6, 7... and so on. To set the value of a cell in phpexcel, just use the setCellValue method. The first parameter represents the spliced ​​value of the column and row, such as: A1, B1, AA1, BA1.

After knowing this, just calculate the letter representing the column based on the integer part and modulo part of $i/26.

The following is the solution

<?php
namespace AppLibrariesExcel;
use PhpOfficePhpSpreadsheetCellDataType;
use PhpOfficePhpSpreadsheetSpreadsheet;
use PhpOfficePhpSpreadsheetWriterXlsx;
class Export
{
    protected $data_type;
    protected $spread_sheet;
    protected $x_lsx;
    public function __construct(DataType$data_type, Spreadsheet$spread_sheet, Xlsx$x_lsx)
    {
        $this->data_type= $data_type;
        $this->spread_sheet= $spread_sheet;
        $this->x_lsx= $x_lsx;
    }
    /**
    * @ description 文件导出
    * @ date 2019-05-06
    * @ array $field_name 字段名称 汉字(索引数组) [&#39;产品&#39;,&#39;姓名&#39;]
    * @ array $data 数据 [&#39;a&#39; => data, &#39;b&#39; => data]
    * @ array $field_column 数据中的下标名称 字段数据 (索引数组) [&#39;a&#39;,&#39;b&#39;]
    * @ string $file_name 文件名称
    * @ array $arr 需要转换为数字的$field_column中的key(索引数组)
    * @ return  file
    */
    public function export($field_name,$data,$field_column,$file_name,$field_numeric_keys= [])
    {
        @ob_clean();
        if (empty($field_name)|| empty($field_column)|| empty($data)|| empty($file_name))return false;
        $sheet= $this->spread_sheet->getActiveSheet();
        //设置header
        $i= 0;
        foreach ($field_name as $value) {
            $cellName= self::stringFromColumnIndex($i). "1";
            $sheet->setCellValue($cellName, $value)->calculateColumnWidths();
            $sheet->getColumnDimension(self::stringFromColumnIndex($i))->setWidth(15);
            $i++;
        }
        //设置value
        $len= count($field_column);
        foreach ($data as $key=> $item) {
            $row= 2 + ($key* 1);
            for ($i= 0; $i< $len; $i++) {
                $sheet->setCellValueExplicit(self::stringFromColumnIndex($i). $row, $item[$field_column[$i]]??"", DataType::TYPE_STRING);
                if (isset($item[$field_column[$i]])&& !empty($field_numeric_keys)&& in_array($field_column[$i],$field_numeric_keys)) {
                $sheet->setCellValueExplicit(self::stringFromColumnIndex($i). $row, $item[$field_column[$i]]??"", DataType::TYPE_NUMERIC);
                }
            }
        }
        $writer= new Xlsx($this->spread_sheet);
        header(&#39;Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet&#39;);//告诉浏览器输出07Excel文件
        header(&#39;Content-Disposition: attachment;filename="&#39; . $file_name . &#39;.xlsx"&#39;);//告诉浏览器输出浏览器名称
        header(&#39;Cache-Control: max-age=0&#39;);
        $writer->save(&#39;php://output&#39;);
        @ob_flush();
        @flush();
        exit;
    }
    /**
    * @ description excel导出突破只能26个字段
    * @ date 2019-05-07
    * @ return  string 字段
    */
    public static function stringFromColumnIndex($pColumnIndex = 0)
    {
        static $_indexCache= array();
        if (!isset($_indexCache[$pColumnIndex])) {
            if ($pColumnIndex < 26) {
                $_indexCache[$pColumnIndex]= chr(65 + $pColumnIndex);
            }elseif ($pColumnIndex < 702) {
                $_indexCache[$pColumnIndex]= chr(64 + ($pColumnIndex / 26)). chr(65 + $pColumnIndex % 26);
            }else {
                $_indexCache[$pColumnIndex]= chr(64 + (($pColumnIndex - 26)/ 676)). chr(65 + ((($pColumnIndex - 26)% 676)/ 26)). chr(65 + $pColumnIndex % 26);
            }
        }
        return $_indexCache[$pColumnIndex];
    }
}

If you are interested, you can click on "PHP Video Tutorial" to learn more about PHP knowledge.

The above is the detailed content of What should I do if PHPSpreadsheet reports an error when exporting Excel with more than 26 columns?. For more information, please follow other related articles on the PHP Chinese website!