Where communities thrive


  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
People
Repo info
Activity
  • 00:05
    MarkBaker commented #3323
  • Jan 26 22:12
    MarkBaker commented #3322
  • Jan 26 22:00
    fredmatrack edited #3323
  • Jan 26 22:00
    fredmatrack edited #3323
  • Jan 26 21:58
    fredmatrack opened #3323
  • Jan 26 21:50
    fredmatrack opened #3322
  • Jan 26 21:49
    oleibman commented #182
  • Jan 26 21:40
    oleibman commented #3190
  • Jan 26 20:26
    rafaeldiasbh commented #3321
  • Jan 26 19:45
    MarkBaker commented #3321
  • Jan 26 19:42
    rafaeldiasbh commented #3321
  • Jan 26 19:31
    MarkBaker commented #3321
  • Jan 26 19:31
    MarkBaker commented #3321
  • Jan 26 19:30
    MarkBaker commented #3321
  • Jan 26 19:12
    rafaeldiasbh edited #3321
  • Jan 26 19:01
    rafaeldiasbh opened #3321
  • Jan 26 14:57
    JarJak commented #1384
  • Jan 26 12:15
    diego-tella starred PHPOffice/PhpSpreadsheet
  • Jan 26 10:49
    MarkBaker closed #3301
  • Jan 26 09:19
    sammybammy52 closed #3320
Olaitan .M. Adeboye
@Mercyware
Hello
I am trying to get Images from excel using getDrawingCollection(). However, i need to be able to know the cell that has the image. Is that possible ?
Adrien Crivelli
@PowerKiKi
@Mercyware try
$spreadsheet->getActiveSheet()->getDrawingCollection()[0]->getCoordinates();
dack94
@dack94

Hi all :) I'm creating an excel with PhpSpreadsheet (no prob with that ). I'm working with MVC pattern so flow is :
1) User press download button and call function in controller
2) function in controller call a function in an helpers to create the excel

Now it's a very very long task and i'd like to show the progress of this long task (like a download bar). I've tried with all things that i know (ajax polling , iframe, sse, session variable, static variable) but none seems to work. Could you help me ?

Adrien Crivelli
@PowerKiKi

@dack94 I did exactly what you are talking about a long time ago, and it's a surprisingly trick thing to do. I would try to avoid it actually. One serious limitation with normal ajax thing is the browser limitation of 2 connection to a given server. So in some condition, your ajax polling might not actually start before the excel file is done generating. Anyway here is my solution that you will have to adapt.

Utility Class:

<?php

namespace Application\Utility;

/**
 * This class implements ways to know when a long process is running on
 * the server and when the said process is complete.
 *
 * This file *MUST NOT* be part of Zend code because it has to be accessible concurrently
 * to any process that may occur at the same time by another HTTP request. Hence we
 * cannot use session and cannot use ACL security system neither. It must stay totally
 * separated from all other code.
 */
abstract class Progress
{
    private static $currentIdProgress = '';

    /**
     * Returns the path for the path for the lock file for the specified ID of progress
     *
     * @param string $id
     *
     * @return string
     */
    private static function getLockFilePath(string $id): string
    {
        // Here we use MD5 to prevent security hole to access filesystem with '.' or '/' characters in $id
        return __DIR__ . '/data/tmp/progress_' . md5($id) . '.lock';
    }

    /**
     * Notify the beginning of a long process
     *
     * @param string $id
     */
    public static function start(string $id): void
    {
        self::$currentIdProgress = $id;
        touch(self::getLockFilePath($id));
    }

    /**
     * Notify the end of a long process
     */
    public static function end(): void
    {
        $path = self::getLockFilePath(self::$currentIdProgress);
        if (file_exists($path)) {
            unlink($path);
        }
    }

    /**
     * Allow to poll the status of a progress by its ID
     *
     * @param string $id id of progress
     *
     * @return string current status of progress
     */
    public static function status(string $id): string
    {
        if ($id && file_exists(self::getLockFilePath($id))) {
            return $id . ' in progress ...';
        }

        return '';
    }
}

Then your javascript would start the download with an arbitrary "process id". Any string generated in JS. When starting to generate the excel file and you receive that id in PHP, you would call:

Progress::start($_REQUEST['id']);
$spreadsheet = createMySpreadsheet();
Progress::end();
sendSpreadsheetToBrowser($spreadsheet);

In parallel the JS would start to poll a different endpoint. That endpoint MUST be completely separate from your application and it MUST NOT use PHP session at all. In that new endpoint you use:

<?php

/**
 * This file *MUST NOT* be part of Zend code because it has to be accessible concurrently
 * to any process that may occur at the same time by another HTTP request. Hence we
 * cannot use session and cannot use ACL security system neither. It must stay totally
 * separated from all other code.
 */
require_once __DIR__ . '/../vendor/autoload.php';
echo \Application\Utility\Progress::status($_REQUEST['id']);

The JS can then show an non-deterministric progress bar, until the endpoint says it's over

ahsanmahmood
@ahsanmahmood
i am new with php sread sheet
need help how to use in laravel5.8
any video tutorial or anything docs etc
my email is aoneahsan@gmail.com
lalilalai
@lalilalai
Hello, I newer in licenses and I want to know, can I use PHPOffice/PhpSpreadsheet in my MIT PHP project? Thank you in advance
Adrien Crivelli
@PowerKiKi
lalilalai
@lalilalai
@PowerKiKi Thanks a lot
Marefandho
@marefandho
hai, can someone help ? about using it
I've got Class 'PhpOffice\PhpSpreadshasdeet\Spreadsheet' not found
install it using composer and trying the simple script as it told on online docs

<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'Hello World !');

$writer = new Xlsx($spreadsheet);
$writer->save('hello world.xlsx');

Marefandho
@marefandho
ah finally got the answer, if someone having the same problem like me
use \PhpOffice\PhpSpreadsheet\Spreadsheet;
use \PhpOffice\PhpSpreadsheet\Writer\Xlsx;
ade more backslash before PhpOffice
dabrjn
@dabrjn
composer installation of phpshreadsheet failing on linux2 box with php7.3.

Error is :
[InvalidArgumentException]
Package phpoffice/phpspreadsheet at version has a PHP requirement incompat ible with your PHP version (5.4.16)

Any idea how to fix?

Adrien Crivelli
@PowerKiKi

incompatible with your PHP version (5.4.16)

=> upgrade your PHP Version
@dabrjn

dabrjn
@dabrjn
Never could fix linux2 box php so I created a ubuntu box. All files created using phpspreadsheet cannot open in Excel. Error message is "Excel cannot open the file 'filename.Xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file." Is phpspreadsheet compatible with php 7.3?
dabrjn
@dabrjn
Continuation of above im: Files created using phpspreadsheet on my mac (php7.1.23) and also on a aws linux server (php5.6) work just fine. I did a comparison on loaded/enabled php modules between mac and ubuntu and the following ones are not on the ubuntu box, but are on the mac: bcmath, bz2, dba, ldap, pdo_pgsql, pdo-sqllite, and pgsql. Could one of these missing be an issue? I'm not using pdo, but do use mysqli calls, but even the simplest spreadsheet fails with same message. All that simple spreadsheet did was put some text in several cells.
dabrjn
@dabrjn
Continuations of above im: Problem solved. Had to issue ob_end_clean() immediately prior to $writer->save('php://output'); Why this is required on ubuntu box with php7.3 and not the other machines is puzzling, but problem is solved.
Tolga Ozses
@kartagis
hello
Tolga Ozses
@kartagis
is there a way to write to xlsx without specifying getCell()? dynamically I mean.
Adrien Crivelli
@PowerKiKi
@kartagis not sure to understand what you need, but maybe have a look at \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::fromArray()
Tolga Ozses
@kartagis
@PowerKiKi that might work for my use case. I would first read from the PDF and stuff that into an array, then use fromArray()
Yiğit
@yigitnerukuc_twitter
@PowerKiKi I sent a pull request about Calculation/SWITCH statement, when will be reviewed? PHPOffice/PhpSpreadsheet#983
Adrien Crivelli
@PowerKiKi
@isleshocky77 the v1.7.0 you were waiting for just got released: https://github.com/PHPOffice/PhpSpreadsheet/releases/tag/1.7.0
Fräntz Miccoli
@frantzmiccoli
@PowerKiKi I saw you closed PHPOffice/PhpSpreadsheet#873 could I get a few explanations for my understanding? Thanks
Stephen Ostrow
@isleshocky77
@PowerKiKi Thank you. I saw that come through a github notification.
Neal Anders
@nanderoo
Hello, I hope this is the right place to ask... I'm using PhpSpreadsheet (1.6.0) and encountering performance issues when dealing with large row / column counts and calling $writer->save(). For a 50k row / 26 column test file it takes ~3 1/2 minutes (at 100% cpu) with about 1 minute 20 seconds spent in the garbageCollect() routine.. and about a minute in the for-loop to add worksheets. The spreadsheet itself is nothing special, just text (the row number and column letter) in each cell. Is it necessary to call garbageCollect() on a new file with a very structured row/column cell build-out?
Another performance-related question / thought... in the case of larger xlsx files, would it not be more performant to write all the underlying content (xml files w/ data) out to the file/folder structure on disk, and then simply call zip to roll the folders and files into the xlsx file at the very end?
Here is some example code I'm using to test:
<?php
include_once 'include/bootstrap.php'; // Adjust bootstrap to your environment.
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
print "[".date("Y-m-d H:i:s",time())."] Start Data Population.\n";
$spreadsheet = new Spreadsheet();
$spreadsheet->setActiveSheetIndex(0);
$columnLookup = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"; // Limit to 26 columns
//$numberOfRows = 2500; // Takes Seconds
//$numberOfRows = 25000; // Take Minutes
$numberOfRows = 50000; // Take Minutes
//$numberOfRows = 250000; // Takes Hours
for ($row = 1; $row <= $numberOfRows; $row++) {
    for ($column = 1; $column <= 26; $column++) {
        $columnChar = substr($columnLookup, ($column -1),  1);
        $spreadsheet->getActiveSheet()
                    ->getCell("{$columnChar}{$row}")
                    ->setValueExplicit(
                        "{$columnChar}{$row}",
                        \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING
                    );
    }
    if ($row % 1000 == 0) print "."; // show progress once in awhile..
}
print "\n";
print "[".date("Y-m-d H:i:s",time())."] End Data Population.\n";
print "[".date("Y-m-d H:i:s",time())."] Start File Creation.\n";
$writer = new Xlsx($spreadsheet);
$writer->setPreCalculateFormulas(false); // Has no affect given the spreadsheet has no formulas!
$writer->save('/tmp/xlsxExample.xlsx'); // This takes --forever--  ..but why?
print "[".date("Y-m-d H:i:s",time())."] End File Creation.\n";
sgfgdf
@sgfgdf_twitter
Hello, guys! I'm reading an .xls file, which is formatted as HTML plain text. When I read the file using the \PhpOffice\PhpSpreadsheet\Reader\Html reader and then save the file as "Microsoft Excel 97-2003 (.xls)" format and use the \PhpOffice\PhpSpreadsheet\Reader\Xls the rows in the array from the worksheet are different. Is there any reason for this?
Adrien Crivelli
@PowerKiKi
@frantzmiccoli your PR has been merged manually in PHPOffice/PhpSpreadsheet@9a208b3. This is something that I often do when a few minor ajustements are necessary. Instead of wasting time asking the PR author I make the edits myself, rebase/merge/squash and push manually. This has the unfortunate effect that GitHub is not aware that the PR has actually been merged (see isaacs/github#2). But don't be mistaken, PRs, and yours in particular, are very much appreciated, even though we may not answer as fast as we would like to.
briancstevens
@briancstevens

Hello! Wasn't sure where to post, but I wanted to raise the point that lack of support for MS excel "format as table" creates accessibility issues. "Format as table" is the most widely supported technique for screen readers to properly associate column headers.

Here's a link to the official guide for making accessible Excel files (for meeting US laws):
https://www.section508.gov/create/spreadsheets

It seems like "format as table" is realized using pivot tables...

Related issues on github: #972 #840 #358
Related questions on stackoverflow:
https://stackoverflow.com/questions/55907831/phpspreadsheet-tables-get-lost-in-the-process
https://stackoverflow.com/questions/50934401/phpspreadsheet-multiple-tables-with-format-as-table-in-excel-worksheet

Adrien Crivelli
@PowerKiKi
@briancstevens the best that could happen for feature request like this one is for someone, maybe you, to start working on it and suggest a PR. Unfortunately we typically don't have the time to implement new features
Fräntz Miccoli
@frantzmiccoli
@PowerKiKi Perfectly understandable, thanks for the update and the compliments!
briancstevens
@briancstevens
@PowerKiKi Understood, thanks for the reply!
Alan Christian Ruiz Aguirre
@achristian92
how to save PhpSpreadsheet(excel) in amazon s3 ??? help
FlaviuRadulescu
@FlaviuRadulescu
Hello everybody. Is there an equivalent of "Format Painter" option from excel?
I need to copy the format of a merged cells to another cells and the duplicateStyle copy just the format not the merge property of the cells
Thomas Kristian Jeriko
@jerikothomas
hello i have question about "How to use time values (hh:mm:ss) on chart ?"
moschel26
@moschel26

Hello. I want to ask. How do I make a data automatically move to a new page if the data is separate in the php excel?
I have dynamic data. So my data can change
besides that I also have a data signature that cannot be separated.
This signature data must be on the same page. See the red mark in this image :

enter image description here

It is a unit and cannot be separated. Signature data
must be on the same page

My problem is because my data is dynamic. This makes the position of the signature data can be located in any position. See image below :

enter image description here

Because my data increases, the position of the headmaster in the signature data is separate

How do I make signature data (see picture 1), which red marks automatically move to the next page if the data is separate?

sgfgdf
@sgfgdf_twitter
Hello, guys! Can someone tell me why PhpSpreadsheet strips some non-printable characters when reading from HTML – https://www.pastery.net/xumcsb/? Note the special character (0xa0) between FOO, BAR and BAZ.
sgfgdf
@sgfgdf_twitter
It appears that this was introduced by PHPOffice/PHPExcel#514, which makes different readers inconsistent. If you save the files as xls and read it with PhpOffice\PhpSpreadsheet\Reader\Xls instead of PhpOffice\PhpSpreadsheet\Reader\Html if will preserve the cells content as is.