Excel rounding value 7,256E + 18

2

That old doubt with Excel cell formatting is causing me problems now. I use laravel framework and to export the data I use Laravel Excel .

I am formatting the cell for text type and even then Excel continues rounding the values and changing the ending to 0000. Follow my example.

Passing the cell to text type.

$sheet->setColumnFormat(array(
'B' => '@'));

Result in the worksheet.

7,256E+18 => 7256001273850860000 // valor que sai na célula no excel
             7256001273850860168 // valor que era para ser gerado

If anyone can help, I am very grateful. Thanks ..

    
asked by anonymous 28.09.2017 / 13:35

3 answers

2

This occurs because Excel rounds up after 15 digits, because it uses the Floating-point arithmetic . Then the data should be shown as Text (String)

Option 1

Format the Text Cells Manually by selecting the cells, then enter the number with the cell already formatted, as in the image:

Option2

ExcelVBAcodetotransformtheformattingoftheentireworksheetintotext

WithSheets(1).Cells'Paraprimeiraplanilha.NumberFormat="@"
End With

Or with the name of the worksheet

With Sheets("Planilha1").Cells
.NumberFormat = "@"
End With

Option 3

Reading about Lavaravel, it can export to CSV files.

Then you can export the files to CSV and use a code in Excel-VBA to import them as String to the worksheet.

I suggest contacting Laravel and request some change in the code that creates the Excel spreadsheets, to make the formatting of the cells in Text. Or open issue on Github

I suggest reading this issue of Laravel's Github: Laravel 4.2 / Laravel-Excel 1.3: Numbers formatted as text still appearing as number # 613

    
28.09.2017 / 14:16
0

Put the value with this form="7256001273850860168", as if it were a function, then yes it will identify as a string.

Orformatthefieldfirstastexttheninsertthevalue,becauseassoonasyouenterthevalueinexcelitputszeroattheend,butifyouformatitastextandthenassignthevalueitworks.

    
28.09.2017 / 13:54
0

Well, to "solve" the problem, when I'm going to play the value in the worksheet, I concatenate a blank space, so excel does not break the format.

$sheet->row($cont, array($val.' ')); 
    
28.09.2017 / 16:14