PHP implements Excel long number string display as scientific notation code sharing-PHP Tutorial-php.cn

PHP implements Excel long number string display as scientific notation code sharing

小云云

Release： 2023-03-20 12:20:01

Original

2212 people have browsed it

In excel, if you enter or copy a very long numeric string in a default grid, it will be displayed as a scientific calculation, such as an ID number. The solution is to format the table as text or add a single word before inputting it. quotation marks.

Using PHPExcel to generate excel will also encounter the same problem. There are three solutions:
1. Set the cell to text

$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setTitle(&#39;Simple&#39;);//设置A3单元格为文本
$objPHPExcel->getActiveSheet()->getStyle(&#39;A3&#39;)->getNumberFormat()
    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);//也可以设置整行或整列的style
/*//E 列为文本
$objPHPExcel->getActiveSheet()->getStyle(&#39;E&#39;)->getNumberFormat()
    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);//第三行为文本
$objPHPExcel->getActiveSheet()->getStyle(&#39;3&#39;)->getNumberFormat()
    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
*/

Copy after login

More formats are available Found in PHPExcel/Style/NumberFormat.php. Note: The above settings still display the results of scientific notation in text mode for long numeric strings. The reason may be that PHP uses scientific notation when processing large numbers.

2. The specified data type displayed when setting the value

$objPHPExcel = new PHPExcel();$objPHPExcel->setActiveSheetIndex(0);$objPHPExcel->getActiveSheet()->setTitle(&#39;Simple&#39;);$objPHPExcel->getActiveSheet()->setCellValueExplicit(&#39;D1&#39;,                                 123456789033, 
                                 PHPExcel_Cell_DataType::TYPE_STRING);

Copy after login

3. Add a space before the numeric string to make it a string

$objPHPExcel = new PHPExcel();$objPHPExcel->setActiveSheetIndex(0);$objPHPExcel->getActiveSheet()->setTitle(&#39;Simple&#39;);$objPHPExcel->getActiveSheet()->setCellValue(&#39;D1&#39;, &#39; &#39; . 123456789033);

Copy after login

It is recommended to use the first Two or three, the first one does not fundamentally solve the problem.

Related recommendations:

Front-end html table generation example of excel table

Solution to js exporting Excel table exceeding 26 English characters ES6

How does PHPExcel merge and split cells

The above is the detailed content of PHP implements Excel long number string display as scientific notation code sharing. For more information, please follow other related articles on the PHP Chinese website!