在列级别的php Excel中设置数据类型 [英] Set data type in the php excel in column level

问题描述

我正在使用PHPExcel，并使用fromArray将值添加到了单元格中.现在，我想将某些单元格的数据类型设置为字符串.

I am using PHPExcel and using fromArray I have added the vales to the cells. Now I want to set the data type of certain cells as string.

某些列具有特定类型的值(例如，数字，日期或文本).为此，使用字符串数据类型绰绰有余，否则显示长度大于12的数字将显示为指数E，并且前导零将被截断.

Some columns are having specific type of values (e.g numeric or dates or text). Using string data type is more than enough for this purpose otherwise digits with length more than 12 are showing with exponential E and leading zeros are truncated .

我发现了在单元格中设置值时如何以编程方式设置每个特定单元格的数据类型.但是，如何在最终电子表格中的列级别设置数据类型(因此，用户在Excel中输入的新值会自动采用该类型，例如字符串，因此前导零不会被截断).我也想在功能中知道

I found how to set the data type per specific cell programmatically when setting a value in a cell. However how is it possible to set a data-type in column level in the final spreadsheet (so new values entered by a user in Excel, are automatically in that type, e.g string so leading zeros are not truncated). Also I want to know in the function

$objPHPExcel->getActiveSheet()->setCellValueExplicit('A1', '1234567890', PHPExcel_Cell_DataType::TYPE_STRING);

先使用fromArray插入值，然后再在函数中提供单元格ID和数据类型，

after inserting the value first using fromArray and giving cell id and data type later in the function is possible

或者像设置单元格格式一样，是否有任何方法可以将数据类型设置为字符串

Or is there any method available to set data type as string like we format the cell

$objPHPExcel->getActiveSheet()
->getStyle('A3:A123')
->getNumberFormat()
->setFormatCode('0000');

我已经尝试过代码

$worksheet->getCell('A1:A50')->setDataType(PHPExcel_Cell_DataType::TYPE_STRING);

但是它不起作用

推荐答案

设置数据后设置数据类型不会更改数据；它的实际类型是在您首次调用setCellValue()或setCellValueExplicit()时定义的.

Setting the datatype after setting the data won't change the data; it's type in real terms is defined when you call setCellValue() or setCellValueExplicit() in the first place.

如果使用的是setCellValueExplicit()，则明确告诉PHPExcel使用哪种数据类型.如果使用setCellValue()，则是在告诉PHPExcel确定应使用的数据类型.解决此问题的规则在. fromArray()方法使用setCellValue()，因此它使用值绑定器"来标识数据类型，并相应地设置单元格值. PHPExcel文档中的"Excel数据类型"部分.

If you are using setCellValueExplicit(), you're explicitly telling PHPExcel what datatype to use. If you use setCellValue(), you're telling PHPExcel to work out what datatype it should use. The rules for working this out are defined in a "Value Binder". The fromArray() method uses setCellValue(), so it uses the "Value binder" to identify datatypes, and sets the cell values accordingly. How this works is explained in the PHPExcel Documentation in the section on "Excel DataTypes".

除非另行指定，否则PHPExcel将使用 AdvancedValueBinder ，其中包含更多内容复杂的规则，例如将像5%这样的字符串转换为0.05的浮点值，并为该单元格设置格式掩码，以便仍将其显示为5%，这与MS Excel的方式几乎相同.您也可以使用自己的规则创建自己的"Value Binder"，并使用它代替"Default Value Binder".

Unless you have specified otherwise, PHPExcel uses rules in the the Default Value Binder, which contains some very basic and simple rules. The Library also provides an AdvancedValueBinder with more sophisticated rules, such as converting a string like 5% to a float value of 0.05 and setting a format mask for the cell so that it will still be displayed as 5%, in much the same way that MS Excel does. You can also create your own "Value Binder" with your own rules, and use that instead of the "Default Value Binder".

这篇关于在列级别的php Excel中设置数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！