yii2tech / spreadsheet Goto Github PK

Hello, I have a question about the format of spreadsheet. I use the package to export a excel, it may look like this:
$exporter = new Spreadsheet([ 'dataProvider' => $dataProvider, 'columns' => [ 'id', 'idCard', ] ]);
The idCard is an 18-digit number。but when I download the excel ,it show like this:

I just want it process the idCard as a string, this format is not I want. now the value is 411627200002085000. in fact, the value of idCard here is "411627200002084831". I think this bug is the number have overflowed。i think if I set the number format to text, it may work well. so I change code to this:
$exporter = new Spreadsheet([ 'dataProvider' => $dataProvider, 'columns' => [ 'id', [ 'attribute' => 'idCard', 'contentOptions' => [ 'numberFormat' => [ 'formatCode' => \PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_TEXT, ], ], ] ] ]);
the result change to this, it changed the format to text, but it still an Incorrect value.

I checked the code, and i think the reason is the number is too big. when we set value to cell ,it has already overflowed. and then ,I set the format to text, the value can't change to the original value, so the text is wrong. the code is in "yii2tech/spreadsheet/src/Spreadsheet.php",
public function renderCell($cell, $content, $style = []) { $sheet = $this->getDocument()->getActiveSheet(); $sheet->setCellValue($cell, $content); $this->applyCellStyle($cell, $style); return $this; }
when we set cellvalue ,the value has already overflowed, and then I can do noting to change the value to original value. I try to use setCellValueExplicit, and it work well.
Could you please change the function setCellValue to setCellValueExplicit, so I can set the value explicit?

String column specification produces format 'text' instead of 'raw'.
For example:

$exporter = new Spreadsheet([
    ///...,
    'columns' => [
        ['attribute' => 'name'], // format detected as 'raw'
        'description', // format detected as 'text'
    ],
]);
$exporter->save('/path/to/file.xls');

Just do not need to send to the @PHPOffice/phpspreadsheet documentation.
I tried to embed setWidth, but I would understand where else.

i have template excel, i want to replace some field or row with data from my table and generate edited templates.
thanks

Question

What steps will reproduce the problem?

$model = [
    'data' => [
        '0.0.0' => [
            'reference_number' => 'DMG/00015/2022'
            'customer' => 'PT. Rapala Indonesia'
            'damage' => 'TEAR IN'
            'description' => 'Tingkat Oval 60% s/d 80 %'
            'files' => [
                0 => 'https://sgp1.digitaloceanspaces.com/files.online/goods/OVR3/.tmb/carbon_18_.png'
                1 => 'https://sgp1.digitaloceanspaces.com/files.online/goods/OVR3/.tmb/teahub.io-cbr250rr-wallpaper-3291320.jpeg'
            ]
        ]
        '0.0.1' => [
            'reference_number' => 'DMG/00015/2022'
            'customer' => 'PT. Rapala Indonesia'
            'damage' => 'TEAR OUT'
            'description' => 'Tingkat Oval 90% s/d 100 % R'
            'files' => [
                0 => 'https://sgp1.digitaloceanspaces.com/files.online/goods/OVR3/.tmb/WhatsApp%20Image%202021-12-22%20at%2012.22.19%20PM.jpeg'
                1 => 'https://sgp1.digitaloceanspaces.com/files.online/goods/OVR3/.tmb/carbon_13_.png'
                2 => 'https://sgp1.digitaloceanspaces.com/files.online/goods/OVR3/.tmb/carbon_14_.png'
                3 => 'https://sgp1.digitaloceanspaces.com/files.online/goods/OVR3/.tmb/Action%20Column.png'
            ]
        ]
    ]
]

What is the expected result?

Render those image files based on url given in one column

What do you get instead?

For now, I just can display it as raw text.

Additional info

This is the code for my implementation

    public function actionExportToExcel($id) {
        $model = DamageReport::find()->dataFormatExcel($id);
        $exporter = new Spreadsheet([
            'dataProvider' => new ArrayDataProvider([
                'allModels' =>$model['data']
            ]),
            'columns' => [
                'customer',
                'reference_number',
                'damage',
                'description',
                [
                    'attribute' => 'files',
                    'format' => 'raw',
                    'value' => function($model){
                        if($model['files']){
                            // for testing 
                            return Html::img($model['files'][0]) ;
                        }

                        return null;
                    }
                ],
            ],
        ]);
        return $exporter->send($model['reference_number'] .'.xls');
    }

After installing "yii2tech/spreadsheet": "^1.0" using composer not showing in the vendor folder
folder missing "use yii2tech\spreadsheet\Spreadsheet";

Here below the following message after install using composer

Using version ^1.0 for yii2tech/spreadsheet
./composer.json has been updated
Loading composer repositories with package information
Updating dependencies (including require-dev)
Package operations: 4 installs, 0 updates, 0 removals

• Installing markbaker/complex (1.4.7): Downloading (100%)
• Installing psr/simple-cache (1.0.1): Downloading (100%)
• Installing phpoffice/phpspreadsheet (1.5.0): Downloading (100%)
• Installing yii2tech/spreadsheet (1.0.2): Downloading (100%)
  phpoffice/phpspreadsheet suggests installing mpdf/mpdf (Option for rendering PDF with PDF Writer)
  phpoffice/phpspreadsheet suggests installing dompdf/dompdf (Option for rendering PDF with PDF Writer)
  phpoffice/phpspreadsheet suggests installing tecnickcom/tcpdf (Option for rendering PDF with PDF Writer)
  phpoffice/phpspreadsheet suggests installing jpgraph/jpgraph (Option for rendering charts, or including charts with PDF or HTML Writers)
  Package phpoffice/phpexcel is abandoned, you should avoid using it. Use phpoffice/phpspreadsheet instead.

What steps will reproduce the problem?

What is the expected result?

string empty ''

What do you get instead?

1970-01-01 08:00:00

Additional info

 ['attribute' => 'released_time', 'format' => 'datetime'],

if released_time type is null,export file will see 1970-01-01 08:00:00.but my yii config formatter nullDisplay is ''; and when use gridview:widget will render string ''.

How can we wrap text for column

Great repo!

I want to change the label of an attribute of a related table. How can I specify attributes for related tables?

I tried:

'attributes' => [
    'myattrib1',  // works
    'othertable.myattrib2', // works
    [
        'attribute' => 'othertable.myattrib2', // doesn't work
        'label' => 'NewLabel',
    ]
]

I want to export a static colum to excel.

I tried:

attributes' => [
    [
        'label' => "One",
        'value' => 1,
    ]
]

This results in a error Illegal offset type in isset or empty.

Is there any way to loop multiple tables with same header but different category? How?

This may need 3-5 rows break for the next loop

for($i = 0; i < 4; i++ ) {
$exporter = new Spreadsheet([
    'dataProvider' => new ArrayDataProvider([
        'allModels' => [
            [
                'id' => 1,
                'name' => 'first',
            ],
            [
                'id' => 2,
                'name' => 'second',
            ],
        ],
    ]),
    'columns' => [
        [
            'class' => SerialColumn::class,
        ],
        [
            'attribute' => 'id',
        ],
        [
            'attribute' => 'name',
        ],
    ],
])->render();
//Neds some break rows here...
}

I tried to export excel as given example using following code

$exporter = new Spreadsheet([ 'dataProvider' => new ActiveDataProvider([ 'query' => Subject::find(), 'pagination' => [ 'pageSize' => 100, // export batch size ], ]), ]); return $exporter->send('items.xls');

Unfortunately it generates corrupted file. Please find the attachment.

May I know where am I wrong? Do we need to add headers as well? Is there any proper documentation except README?

Thanks

I'm using PHPSpreadsheet to generate an Excel file that I want to attach to a mail message, together with a bunch of PDF files(using Mpdf).

With Mpdf I can simply do: return base64_encode($mpdf->Output())

and attach it to the email with:

 $file = base64_decode($this->actiongetPdfFile($postData['billing_profile']));

$message->attachContent($file, ['fileName' => 'file.pdf', 'contentType' => 'data:application/pdf']);

Is there a way to do something similar with PHPSpreadsheet (without the need to save the file to disk before)?

I saw that suggestions saying that it was possible using $objWriter->save('php://output');, but this did not work with this plugin.

I believe that there is no further documentation aside from what is already on the initial page, right?

I haven't been able to dig in too deep here but even though this library and yii2tech/csv-grid both seem to be almost identical in config and functionality, CsvGrid exports JSON columns without issue while this library just skips over them, with identical options (data provider, column config). What could be the issue here?

How to set rich text in specific column?

I get an error message when using Spreadsheet::send and Xlsx writer saying:

unlink(...\AppData\Local\Temp\php3945.tmp): Permission denied

$tmpResource needs to be closed before $writer->save($tmpFileName)

// ...
$tmpResourceMetaData = stream_get_meta_data($tmpResource);
$tmpFileName = $tmpResourceMetaData['uri'];

$tmpFileStatistics = fstat($tmpResource);
if ($tmpFileStatistics['size'] > 0) {
    return Yii::$app->getResponse()->sendStreamAsFile($tmpResource, $attachmentName, $options);
}

fclose($tmpResource);
$writer = IOFactory::createWriter($this->getDocument(), $writerType);
$writer->save($tmpFileName);
unset($writer);
// ...

The code above is working for .xls or .xlsx, I didn't try with other formats

I get error for 8000+ array items
Allowed memory size of 134217728 bytes exhausted (tried to allocate 20480 bytes)

$orders = [...] // Array of 8000+ items

// item example
// 0 => [
//    "id" => "146472", "created" => "2021-06-01 17:01:28", "site", => "1", "status" => "Отменён", 
//    "address_to_delivery" => "Кропивницкий - 1", "price" => "956", "restaurant"=> "0", "delivery_datetime" => "14:33", 
//    "first_name" => "Дмитрий", "phone" => "0662784754", "start_delivery" => "Не передано курьеру" , 
//   "complited_draw_order" => "2021-06-01 17:02:30", "items_num" => "0", "pizzeria" => "К1", "marketing" => "Нет",
//    "promo" => "", "timeDelivery" => "Да"]

var_dump($orders); // This work

$dataProvider = new ArrayDataProvider([
            'allModels' => $orders,
            'pagination' => [
                'pageSize' => 100,
            ],
        ]);

$exporter = new Spreadsheet([
            'dataProvider' => $dataProvider,
            'title' => 'Orders',
            'columns' => [...],
            'batchSize' => 200,]);

return $exporter->send('Заказы с ' . $date_from . ' по '.$date_to . '.xls');

If number of items 6000 this is work correctly

What steps will reproduce the problem?

'columns' => [
[
'class' => SerialColumn::class,
]
]

What is the expected result?

row numbers in the result table

What do you get instead?

nothing

Additional info

it seems to me, in code some problem near by

  /**
   * Renders sheet table body batch.
   * This method will be invoked several times, one per each model batch.
   * @param array $models batch of models.
   * @param array $keys batch of model keys.
   * @param int $modelIndex model iteration index.
   */
protected function renderBody($models, $keys, &$modelIndex) {
    foreach ($models as $index => $model) {
      $key = isset($keys[$index]) ? $keys[$index] : $index;
      $columnIndex = 'A';
      foreach ($this->columns as $column) {

       /* the class of $column variable is the yii\grid\SerialColumn, but your code
        assumes that class of $column is your own Column and the renderDataCell
        fills the Column->grid property */

        /* @var $column Column */ 

        $column->renderDataCell($columnIndex . $this->rowIndex, $model, $key, $modelIndex);
        $columnIndex++;
      }
      $this->rowIndex++;
      $modelIndex++;
    }
  }

What steps will reproduce the problem?

What is the expected result?

What do you get instead?

Additional info

Question:

Pleaser refer my setup, I am fiailed to formatting the column in generated excel.

Method Spreadsheet::save() changes file path to lowercase:
https://github.com/yii2tech/spreadsheet/blob/master/src/Spreadsheet.php#L673

This will cause unexpected behavior, especially for case-sensitive file systems

в Spreadsheet нет метода saveAs;
а так же последний пример, что за переменная $grid

This line uses yii\web\Response::sendStreamAsFile, this method just prepares the file without send it to be downloaded as documented.
it would be nice to document what should be done if the user faces this use case.

Hi.
This package is using phpoffice/phpexcel which is deprecated (see https://github.com/PHPOffice/PHPExcel)

Migration should not be very hard.

Hi, thank you for this extension.
I'm trying to save a xls file from console but I get this error:
PHP Parse error: syntax error, unexpected 'class' (T_CLASS), expecting identifier (T_STRING) or variable (T_VARIABLE) or '{' or '$' in /home/****/vendor/yii2tech/spreadsheet/src/Spreadsheet.php on line 222
The same code works perfectly if called from the browser.
Any Idea?
Tryed in a different server and it works, but also tryed a different extension in the same server and works perfecty. Also your csv exporter works in both servers

What steps will reproduce the problem?

If export to Csv some text with non latin symbos i get charset errored text.

What is the expected result?

In csv i try to see readable data such as "Несколько слов на русском языке"

What do you get instead?

I see data in wrong charset as "активна"

Additional info

Problem can be solwed with possibility to configure writer such as:

$writer = IOFactory::createWriter($this->getDocument(), $writerType);
if ($writerType=='Csv') {$writer->useBOM=true;}
$writer->save($filename);

OR

$writer = IOFactory::createWriter($this->getDocument(), $writerType);
if ($writerType=='Csv') {$writer->excelCompatibility=true;}
$writer->save($filename);

OR

add $this->writerConfig property to yii2tech\spreadsheet\Spreadsheet and apply it to $writer

OR

create new method and use it to get $writer (so i can use extend yii2tech\spreadsheet\Spreadsheet of it )

publick function createWriter($writerType){
      return IOFactory::createWriter($this->getDocument(), $writerType);
}

!!! Please, add possibility to configure writer!

Hi,

Is there any function to add image and title in data sheet before main data table?

Code returns file with size 0 bytes for writerType Xlsx and works fine for xls.

$exporter = new Spreadsheet([
     'dataProvider' => new ActiveDataProvider([
            'query' => $this->remains($c, $s),
      ]),
      'writerType' => 'Xlsx',
]);
return $exporter->send('remains.xlsx');

Problem here
https://github.com/PHPOffice/PhpSpreadsheet/blob/a1e8c843b79c6bc916cd2a5a725fc26948daba66/src/PhpSpreadsheet/Writer/Xlsx.php#L212
Created temporary file unlink and create a new file with same name, but here

What steps will reproduce the problem?

What is the expected result?

What do you get instead?

Additional info

e.g. force set a number show as string, like

$cell->setCellValueExplicit(
    'A8',
    "01513789642",
    \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING
);

$cell->setDataType(
    \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING
);

в коде нашел только выравнивание текста в ячейке