php 使用 PHPExcel 以文本格式读取数字

Reading numbers as text format with PHPExcel

提问by sergioviniciuss

I have an array that can store numbers in such as 01, 01A, 01B, 02, 2, and When I get this value using PHPExcel, it's being removed the '0's in case of 01, 02, for example. To solve this problem, I tried to format the row where these values will be stored in excel and set it as text type, just like in the following code:

我有一个可以存储数字的数组，例如 01、01A、01B、02、2，并且当我使用 PHPExcel 获取此值时，例如在 01、02 的情况下，它会被删除“0”。为了解决这个问题，我尝试格式化这些值将存储在excel中的行并将其设置为文本类型，就像在下面的代码中一样：

    $objPHPExcel->setActiveSheetIndexByName('BlocksList');
    $objPHPExcel->getActiveSheet()->fromArray($blockNames, null, 'A2');
    $latestBLColumn = $objPHPExcel->getActiveSheet()->getHighestDataColumn();
    $column = 'A';
    $row = 1;
    for ($column = 'A'; $column != $latestBLColumn; $column++) {
        $objPHPExcel->getActiveSheet()->getStyle($column.$row)->getNumberFormat()->setFormatCode( PHPExcel_Style_NumberFormat::FORMAT_TEXT );
    }
    $objPHPExcel->getActiveSheet()->fromArray($blockNames, null, 'A1');

So, by doing this, I get the array with numbers like 01, 01A, 02, 02B... and I store it in the Row A2. I get the highest Column to use this value in the condition For. In this condition, I set for the Row 1 in the range A until the highest column, to be formated as text.

所以，通过这样做，我得到了像 01、01A、02、02B 之类的数字的数组，并将它存储在 A2 行中。我得到了在条件 For 中使用这个值的最高列。在这种情况下，我将范围 A 中的第 1 行设置为最高列，以格式化为文本。

My template is generated, and all the numbers are in text format, but the problem is that I think when I use the "fromArray()" method, it transforms the numbers of the array before I can get it right in excel. Do you have any idea of how can I solve this problem??

我的模板生成了，所有的数字都是文本格式，但问题是我觉得当我使用“ fromArray()”方法时，它在我可以在excel中正确使用之前转换了数组的数字。你知道我该如何解决这个问题吗？？

回答by Mark Baker

Formatting using a number format affects the way a number is displayed, not the way it is stored.

使用数字格式进行格式化会影响数字的显示方式，而不是它的存储方式。

You'll have to storethe numbers explicitly as strings, so you can't use fromArray(). Use setCellValueExplicit() or setCellValueExplicitByColumnAndRow() instead, passing a $pDataType argument of PHPExcel_Cell_DataType::TYPE_STRING.

您必须将数字显式存储为字符串，因此不能使用 fromArray()。使用 setCellValueExplicit() 或 setCellValueExplicitByColumnAndRow() 代替，传递 PHPExcel_Cell_DataType::TYPE_STRING 的 $pDataType 参数。

EDIT

编辑

Note that you can also set styles for a range of cells, so there's no need to add the overhead of the for loop:

请注意，您还可以为一系列单元格设置样式，因此无需添加 for 循环的开销：

$range = 'A'.$row.':'.$latestBLColumn.$row;
$objPHPExcel->getActiveSheet()
    ->getStyle($range)
    ->getNumberFormat()
    ->setFormatCode( PHPExcel_Style_NumberFormat::FORMAT_TEXT );

EDIT #2Using a cell binder

编辑 #2使用细胞粘合剂

Create a customised cell value binder:

创建自定义的单元格值绑定器：

class PHPExcel_Cell_MyValueBinder extends PHPExcel_Cell_DefaultValueBinder
    implements PHPExcel_Cell_IValueBinder 
{ 
    public function bindValue(PHPExcel_Cell $cell, $value = null) 
    { 
        // sanitize UTF-8 strings 
        if (is_string($value)) { 
            $value = PHPExcel_Shared_String::SanitizeUTF8($value); 
        } 

        // Implement your own override logic 
        if (is_string($value) && $value[0] == '0') { 
            $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING); 
            return true; 
        } 

        // Not bound yet? Use default value parent... 
        return parent::bindValue($cell, $value); 
    } 
} 

To avoid any problems with the autoloader, create this in the /Classes/PHPExcel/Cell directory. Otherwise, give the class your own non-PHPExcel name, and ensure that it's loaded independently.

为避免自动加载器出现任何问题，请在 /Classes/PHPExcel/Cell 目录中创建它。否则，为该类指定您自己的非 PHPExcel 名称，并确保它是独立加载的。

Then, before using your fromArray() call, tell PHPExcel to use your value binder instead of the default binder:

然后，在使用 fromArray() 调用之前，告诉 PHPExcel 使用您的值绑定器而不是默认绑定器：

PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_MyValueBinder() );

回答by Arkitecht

I know this was all posted a while ago, but wanted to share something that worked for me, so you can still use ->fromArrayand not have to iterate over the whole spreadsheet.

我知道这都是前一段时间发布的，但想分享一些对我有用的东西，所以你仍然可以使用->fromArray，而不必遍历整个电子表格。

If you wrap your values in ="VALUE"it will come through as text, and not convert it, and it will not have quotes around it in your spreadsheet

如果您将您的值包装在="VALUE"其中，它将作为文本出现，而不是转换它，并且在您的电子表格中它不会有引号

回答by Jimmy Adaro

In case you need to convert the whole sheet numbers to text, you can use calculateWorksheetDimension()to get the sheet's dimensions (example: 'A1:B200' or 'A1:C150') and then use it in getStyle(), like so:

如果您需要将整个工作表编号转换为 text，您可以使用calculateWorksheetDimension()来获取工作表的尺寸（例如：' A1:B200' 或' A1:C150'），然后在 中使用它getStyle()，如下所示：

// Get sheet dimension
$sheet_dimension = $spreadsheet->getActiveSheet()->calculateWorksheetDimension();

// Apply text format to numbers
$spreadsheet->getActiveSheet()->getStyle($sheet_dimension)->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_TEXT);

Note:This example uses PhpSpreadsheetsince it is the next version of PHPExcel.

注：本例使用PhpSpreadsheet因为它的下一个版本PHPExcel。

回答by H?ng

$objPHPExcel
   ->getActiveSheet()
   ->getCellByColumnAndRow($col, $row)
   ->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING);

回答by user3313585

You can format value to text with add charater before or after value. Example add after value charater ";"

您可以通过在值之前或之后添加字符将值格式化为文本。示例在值字符后添加“;”

$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $row,$user->msisdn.';' );