使用 PHPExcel 以文本格式读取数字
我有一个可以存储数字的数组,例如 01、01A、01B、02、2,当我使用 PHPExcel 获取此值时,例如,在 01、02 的情况下,它会被删除0".为了解决这个问题,我尝试将这些值将存储在excel中的行格式化并将其设置为文本类型,就像在下面的代码中一样:
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:
$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');
因此,通过这样做,我得到了具有 01、01A、02、02B 等数字的数组,并将其存储在 A2 行中.我得到了在条件 For 中使用此值的最高列.在这种情况下,我将范围 A 中的第 1 行设置为最高列,以格式化为文本.
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.
我的模板生成了,所有的数字都是文本格式,但问题是我认为当我使用fromArray()
"方法时,它转换了之前数组的数字我可以在excel中做对.你知道我该如何解决这个问题吗??
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??
推荐答案
使用数字格式格式化会影响数字的显示方式,而不是存储方式.
Formatting using a number format affects the way a number is displayed, not the way it is stored.
您必须将数字明确存储为字符串,因此您不能使用 fromArray().改用 setCellValueExplicit() 或 setCellValueExplicitByColumnAndRow(),传递 PHPExcel_Cell_DataType::TYPE_STRING 的 $pDataType 参数.
You'll have to store the 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.
编辑
请注意,您还可以为一系列单元格设置样式,因此无需添加 for 循环的开销:
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:
$range = 'A'.$row.':'.$latestBLColumn.$row;
$objPHPExcel->getActiveSheet()
->getStyle($range)
->getNumberFormat()
->setFormatCode( PHPExcel_Style_NumberFormat::FORMAT_TEXT );
编辑#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);
}
}
为避免自动加载器出现任何问题,请在/Classes/PHPExcel/Cell 目录中创建它.否则,为类指定您自己的非 PHPExcel 名称,并确保它是独立加载的.
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.
然后,在使用 fromArray() 调用之前,告诉 PHPExcel 使用您的值绑定器而不是默认绑定器:
Then, before using your fromArray() call, tell PHPExcel to use your value binder instead of the default binder:
PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_MyValueBinder() );
相关文章