Excel .xlsx writer make all cells TEXT

[blasto333]

I am and am trying to figure out how to make all cells written in text format and NOT general. It doesn't seem to be possible. The reason being is if a user edits a cell I want it to be exactly as the typed and NOT have excel guess what it is. (Converting 10% --> 10% as a PERCENT; when I read this back it is .1 and not 10%)

in phpExcel I am able to do:

PHPExcel_Cell::setValueBinder(new TextValueBinder());

class TextValueBinder implements PHPExcel_Cell_IValueBinder
{
    public function bindValue(PHPExcel_Cell $cell, $value = null) 
    {
        $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING);
        return true;
    }
}

to force all cells as text.

Is there a way to do this in spout (I couldn't find in docs)? I like spout better because it is faster and uses less memory.

Even if I have to come up with a crazy hack; I am all for it.

[adrilo]

This is not supported yet and won't be until Spout supports per-cell styling (which is planned in v3). As far as I know, there are no workarounds for it unfortunately. You'll have to fork and implement it yourself if you want support for that.

[blasto333]

Can you lead me to which file to fork?

Chris Muench
Sent from my iPhone

On Jul 6, 2016, at 4:51 AM, Adrien Loison notifications@github.com wrote:

This is not supported yet and won't be until Spout supports per-cell
styling (which is planned in v3). As far as I know, there are no
workarounds for it unfortunately. You'll have to fork and implement it
yourself if you want support for that.

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub
#261 (comment), or mute
the thread
https://github.com/notifications/unsubscribe/ACoxro2-pZ6GDnHNpTJ7Ad9vgLdguQjYks5qS2yPgaJpZM4JBJTc
.

[adrilo]

You can look at #209, it does something a bit similar

[bevhost]

I achieved this by accident, as I was reading all the data from a database and the data type was always text. I actually had to cast the numbers from strings to get numbers in Excel.
To cast a string to a number "4" + 0 will return 4.
To cast a number to string eg $a = 4; $b = "$a"; // $b is now a string with 4 in it.

[blasto333]

I solved it by doing the following

+++ b/application/libraries/Spout/Writer/XLSX/Helper/StyleHelper.php
@@ -139,7 +139,7 @@ EOD;
         $content = '<cellXfs count="' . count($registeredStyles) . '">';

         foreach ($registeredStyles as $style) {
-            $content .= '<xf numFmtId="0" fontId="' . $style->getId() . '" fillId="0" borderId="0" xfId="0"';
+            $content .= '<xf numFmtId="49" fontId="' . $style->getId() . '" fillId="0" borderId="0" xfId="0"';

[adrilo]

@bevhost, you can also explicitly cast variables like this:

$string = (string)$foo;
$int = (int)$foo;
...

@blasto333 You may have other problems changing the numFmtId. All cells will now have a number format applied, which is not necessarily what you want.

[adrilo]

This can now be solved thanks to #668. You can style your cell/row using StyleBuilder::setFormat(...) to customize the number format to use.

[razorsharpshady]

Hey @adrilo .. Can we set the format as Text now? if yes can you please share a code snippet?
p.s Thank you for this great library

[razorsharpshady]

Using '@' in format seems to be working fine and doesn't changes the data in excel.
For anybody else facing the same auto-format issue in excel checkout the below snippet, I have set the '@' as the default format for the row, you can also set it on cell level basis as well

<?php 
use Box\Spout\Writer\Common\Creator\Style\StyleBuilder;
use Box\Spout\Writer\Common\Creator\WriterEntityFactory;

$writer = WriterEntityFactory::createXLSXWriter();
$writer->openToFile('/tmp/tmpfile');
//https://docs.microsoft.com/en-us/office/troubleshoot/excel/format-cells-settings
//using @ for converting to text instead of General
$style = (new StyleBuilder())->setFormat('@')->build();
$writer->setDefaultRowStyle($style);
$writer->addRow(WriterEntityFactory::createRowFromArray(["1-10","10-50"]));
$writer->close();
?>