Where communities thrive


  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
People
Repo info
Activity
  • 13:49
    estevebadia starred PHPOffice/PhpSpreadsheet
  • 11:21

    MarkBaker on Style_NumberFormat-New-Fractions

    (compare)

  • 11:20

    MarkBaker on master

    Support for fixed value divisor… Merge pull request #3339 from P… (compare)

  • 11:20
    MarkBaker closed #3339
  • 11:02
    MarkBaker opened #3339
  • 11:01

    MarkBaker on Style_NumberFormat-New-Fractions

    Support for fixed value divisor… (compare)

  • 02:40
  • Feb 01 22:32
    fdjohnston opened #3338
  • Feb 01 22:05
    MarkBaker synchronize #3334
  • Feb 01 22:05

    MarkBaker on NumberFormat_Wizards

    Wizards for defining Number For… (compare)

  • Feb 01 21:18

    MarkBaker on master

    Potential resolution for Issue … Unit tests for evaluation of de… Merge pull request #3336 from P… (compare)

  • Feb 01 21:18
    MarkBaker closed #3336
  • Feb 01 20:24
    MarkBaker commented #3337
  • Feb 01 20:21
    fdjohnston commented #3337
  • Feb 01 20:19
    MarkBaker commented #3337
  • Feb 01 20:10
    MarkBaker synchronize #3336
  • Feb 01 20:10

    MarkBaker on Issue-3355_Quote-Prefix-in-Worksheet-for-Named-Range-Evaluation

    Unit tests for evaluation of de… (compare)

  • Feb 01 19:56
    fdjohnston opened #3337
  • Feb 01 19:14
    MarkBaker edited #3336
graslo
@graslogamer_twitter
Just installed with composer. Trying out samples but can't find them. Where is the /tmp/ folder supposed to be?
Running sample tells me: "20:49:40 Write Xlsx format to /tmp/phpspreadsheet/01_Simple.xlsx in 0.0325 seconds"
But can't find that anywhere
Adrien Crivelli
@PowerKiKi
@graslogamer_twitter /tmp/phpspreadsheet/01_Simple.xlsx is an absolute path. It exists at the root of your filesystem, not in your current folder/project. Copy/paste that path exactly as is in your favorite spreadsheet editor and it should be able to find it
graslo
@graslogamer_twitter
@PowerKiKi thank you
Salman Tariq
@stariqmi
Is there a migration guide from PHPExcel to PHPSpreadsheet? I have to upgrade to PHP 7.3 and can no longer use PHPExcel
Salman Tariq
@stariqmi
Sorry - I should have searched more. Found the doc from searching stackoverflow :)
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?