Set data type in the php excel in column level

Question

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.

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 .

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);

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');

I have tried the code

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

But it is not working

Answer 1

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.

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".

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".

Answer 2

You can read the array as follows

    $excel = new Spreadsheet();

    $columnsNames = array_keys($clientes[0]);
    $sheet = $excel->setActiveSheetIndex(0);
    $sheet->fromArray($columnsNames, NULL, 'A1'); 

    [$startColumn, $startRow] = Coordinate::coordinateFromString('A2');
    $NumeroLinha = 1;

    foreach ($encodeClientes as $rowData) {
        $currentColumn = $startColumn;
        foreach ($rowData as $cellValue) {
                if ($cellValue !== null) {
                    switch ($currentColumn) {
                        case 'A':
                            $sheet->getCell($currentColumn . $startRow)->setValue($NumeroLinha++);
                            break;
                        case 'F':
                            $sheet->getCell($currentColumn . $startRow)->setValueExplicit($cellValue, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                            break;
                        default:
                            $sheet->getCell($currentColumn . $startRow)->setValue($cellValue);
                    }
                }
            ++$currentColumn;
        }
        ++$startRow;
    }