Where communities thrive


  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
People
Repo info
Activity
Mark Baker
@MarkBaker
@yuslielg Features like pivot tables aren't yet supported by PhpSpreadsheet; it's something that I'm working on, but nowhere near ready yet... expect it sometime over the Summer
Mark Baker
@MarkBaker
Ok! A couple of weeks later than I'd hoped; I'm going to devote a good portion of this weekend looking over the remaining PRs, trying to get as much merged as I can, and the release before the end of month.... that will clear the way for my work on the Pivot Tables, and also looking at changing the handling array functions together with #SPILL! and the change to the @ operator; while it will allow time for proper testing of the new namespace handling as well
Aravind Reddy
@iaravindreddyp
Hi everyone, recently I tried reading a .xlsx file where all the first column values were quote prefixed(') and other columns values were just formulas, But when I parse it with PHPSpreadsheet other columns(B and C) are also getting quotes prefixed.. so when I observed the XML the cells in column B and column C also have their quote prefixed flag set to true..I wanna know whether there is any additional check we need to do before adding quote prefix to the cell values? is simple QuotePrefix boolean check not enough?
Mark Baker
@MarkBaker
There shoudn't be anything more than the quotePrefix setting in the xml: if that's true, then the cell value will be treated as a string datatype regardless of whether it contains a formula, a number, whatver
The only way I think that behaviour can be overridden in MS Excel is by a macro
Aravind Reddy
@iaravindreddyp
@MarkBaker the excel am sharing here has quotePrefix set to true even for number cells(please look at cells D19 to D23 and E19 to E23) and MSexcel doesn't append the quote(') at the start of the cell value... am I missing something? Thanks in advance.
styles.PNG
worksheet.PNG
Both c19 and D19 has same styles applied
worksheet.PNG
Mark Baker
@MarkBaker
Well cells C19:C23 are quote prefixed when I load the file in Excel, I don't actually see a problem with that
1 reply
When I load the file with PhpSpreadsheet, the calculations are executed correctly
Cells like B53 and B57 are quote prefixed so that you can see the formula that is being calculated in cells I53:!54 and I57:I58 respectively
There's a lot of unnecessary use of quote prefixing, but I don't see any actual problem
Mark Baker
@MarkBaker
(There's also a lot of unnecessary use of array formulae)
Mark Baker
@MarkBaker
1.18.0 released, we're now free to work on pivot tables and namespacing in the Xml Readers
Rainbowm
@Rainbowm
Hello! Do you have any ideas how I activate the CellWrap when importing HTML? For me, the <br> are cut off.
ahardylaniertech
@ahardylaniertech
Hi! We are working to migrate an older site from phpexcel to phpoffice/phpspreadsheet. the install with composer went well and we used the migration tool and after the upgrade excel files are created, but are corrupt. Been googling and ensured that PHP errors aren't causing the corruption. Running windows server 2019 with xampp 3.3.0, php 7.4.19 and Apache 2.4.47.
7 replies
non1979cn
@non1979cn
in the API documentation - https://phpoffice.github.io/PhpSpreadsheet/,I have see the spreedsheet and worksheet class reference, but now, they have missed,how can I find them?
Adrien Crivelli
@PowerKiKi
@non1979cn thanks for letting us know. PHPOffice/PhpSpreadsheet@e8ebf11 will fix this for the next release.
mrofia
@mrofia

Hi Everyone, I managed to carry out excel charts from a template file, albeit going through hack-ish way, you can see the alteration in the code that will follow. However I would like to further convert the generated file to pdf, and stumbled upon the error

  • realpath(): open_basedir restriction in effect. File(/tmp) is not within the allowed path(s):*

below is my code, I made it so when $conversionLib is not passed the output file will be in xlsx, and it works fine. The issue lies when we pass the $conversionLib parameter (so i can later opt the library).

function SaveViaTempFile($reader, $baseTemplate, $spreadsheet, $conversionLib){
    $writer = new Xlsx($spreadsheet);
    $writer->setIncludeCharts(true);
    $writer->setPreCalculateFormulas(false);
    $randomName=rand(0, getrandmax()) . rand(0, getrandmax());

    $zip = new ZipArchive;

    if ($zip->open($baseTemplate) === TRUE) {
        $zip->extractTo('../my_tmp/'.$randomName);
        $zip->close();

    }

    $filePath = "../my_tmp/".$randomName.".tmp";
    $writer->save($filePath);

    if ($zip->open($filePath) === TRUE) {
        $dirs = dirToArray("../my_tmp/".$randomName);

        //check if the template has chart in it
        if(isset($dirs['xl']['charts'])){
            // add chart format file
            foreach($dirs['xl']['charts'] as $idx=>$file){
                if(is_int($idx)){
                    //memang file
                    $zip->addFile('../my_tmp/'.$randomName.'/xl/charts/'.$file, 'xl/charts/'.$file);
                }
            }

            // add chart rels file
            if(isset($dirs['xl']['charts']['_rels'])){
                foreach($dirs['xl']['charts']['_rels'] as $idx=>$file){
                    if(is_int($idx)){
                        //memang file
                        $zip->addFile('../my_tmp/'.$randomName.'/xl/charts/_rels/'.$file, 'xl/charts/_rels/'.$file);
                    }
                }
            }
        }else{
            //do nothing if the template don't have chart
        }

        $zip->close();
        //deleteDirectory("../my_tmp/".$randomName);
    }

    if($conversionLib){
        $spreadsheetFinal = $reader->load($filePath);
        $randomName2=rand(0, getrandmax()) . rand(0, getrandmax());
        $pdfPath = "../my_tmp/".$randomName2.".tmp";


        //$pdfWriter = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheetFinal,$conversionLib);
        $pdfWriter = new \PhpOffice\PhpSpreadsheet\Writer\Pdf\Tcpdf($spreadsheetFinal);

        $pdfWriter->writeAllSheets();
        $pdfWriter->save($pdfPath);
        readfile($pdfPath);
        unlink($pdfPath);
        deleteDirectory("../my_tmp/".$randomName2);
    }else{
        readfile($filePath);

    }

    unlink($filePath);
    deleteDirectory("../my_tmp/".$randomName);


    exit;
}
mrofia
@mrofia

so the issue is basically when i am trying to convert to pdf. The error got thrown in
$pdfWriter->save($pdfPath)

what is the cause of this issue? the writer for excel file works fine and outputs basically in similar folder hierarchy. So why does the pdfWriter not work? is it something to do with the instance? does phpSpreadsheet prohibits multiple writer instance?

Mark Baker
@MarkBaker
Some of the Writers (like Pdf) might use a temporary folder for partial storage while they are generating the file. The default for this is PHP's upload_tmp_dir if this is set in php.ini (not always defined, particularly if running from the command line), or the system temp directory retrieved b a call to sys_get_temp_dir(). It looks as though access to the system temp directory (/tmp) is restricted.
You can override this default by calling the PDF Writers setTempDir() to provide it with a folder that you do have write access to method before saving
Adrien Crivelli
@PowerKiKi

the system temp directory (/tmp) is restricted

That means your PHP is configured to restrict access. You should either re-configure your PHP, via https://www.php.net/manual/en/ini.core.php#ini.open-basedir, or find a way to tell the writer to write somewhere else

It would seems like you can do that for Tcpdf, before calling the writer, via:
/**
 * Cache directory for temporary files (full path).
 */
define ('K_PATH_CACHE', '/some/path/to/your/writable/directory/');
mrofia
@mrofia

:point_up: June 4, 2021 5:19 PM

Hi Mark, thank you for the insight. You are right, the error boils down to PhpSpreadsheet/Shared/File.php:124
for me i fixed the issue with \PhpOffice\PhpSpreadsheet\Shared\File::setUseUploadTempDirectory(true);
haven't tried your solution with setTempDir on the writer because I am rushed to just make it work, but i think yours might be the cleaner, more proper solution.

my next issue is actually more on performance due to my file having multiple sheet as well as charts, but this is something i can work later. Thanks!

Neri Colon
@ingnrcs
Fatal error: Uncaught Error: Call to private method PhpOffice\PhpSpreadsheet\Shared\StringHelper::buildCharacterSets() from context 'autoLoad' in /home/juicyservice/public_html/public/library/PHPExcel/PHPExcel/Autoloader.php:36 Stack trace: #0 /home/juicyservice/public_html/public/library/PHPExcel/PHPExcel.php(33): require() #1 /home/juicyservice/public_html/autocargar.php(24): require_once('/home/juicyserv...') #2 [internal function]: autoLoad->{closure}('PHPExcelStyle...') #3 /home/juicyservice/public_html/Views/usuario/ExportarPagosSinEnviar.php(38): spl_autoload_call('PHPExcelStyle...') #4 /home/juicyservice/public_html/libs/view.php(12): include('/home/juicyserv...') #5 /home/juicyservice/public_html/Controllers/usuario.php(678): view->render('usuario', 'ExportarPagosSi...') #6 /home/juicyservice/public_html/autocargar.php(48): usuario->ExportarPagosSinEnviar('') #7 /home/juicyservice/public_html/index.php(19): autoLoad->cargar('usuario', 'ExportarPagosSi...', '') #8 {main} thrown in /home/juicyservice/public_html/public/library/PHPExcel/PHPExcel/Autoloader.php on line 36
Mark Baker
@MarkBaker
Why are you trying to use a PhpExcel autoloader with PhpSpreadsheet?
1 reply
Bharani Kumar
@bharanikumar:matrix.org
[m]
$spreadsheetObj = new Spreadsheet();
//$sheet = $spreadsheet->getActiveSheet();
$formula = $spreadsheetObj->getActiveSheet()->getCell('A2')->getValue();
Bharani Kumar
@bharanikumar:matrix.org
[m]
use PhpOffice\PhpSpreadsheet\Spreadsheet;
$spreadsheetObj = new Spreadsheet();
$formula = $spreadsheetObj->getActiveSheet()->getCell('A2')->getValue(); i am getting empty value
Mark Baker
@MarkBaker
So what value do you expect to get from a cell in a completely new, empty spreadsheet?
zkenstein
@zkenstein
Hi, Is it possible to read json data and stored as CellValue? I tried many times, but always return empty
5 replies
zkenstein
@zkenstein
How to do calculation in php office? for example I want to calculate A colom within B
zkenstein
@zkenstein
got it
agenerette
@agenerette
Hello everyone! Do any of you happen to know who I'd need to communicate with about licensing/OSS questions and concerns? We're using the PHPOffice/PhpSpreadsheet library in our application and need to be able to show that their are no requirements for making any parts of that app open-source... or, if there are, we would like to determine what needs to be done to address them.
4 replies
whaliim
@whaliim
Hello Everyone. Could someone please assist me with my issue regarding table formatting and sorting?
https://stackoverflow.com/questions/68128848/how-to-keep-table-formatting-when-sorting-table-generated-by-phpspreadsheet
Thanks in advance.
nirikshan
@nirikshan
hay
hey
Is there any one ?
please help me

nirikshan@Nirikshan-Xprin:/opt/lampp/htdocs/wsn-billing$ composer require phpoffice/phpspreadsheet
Using version ^1.18 for phpoffice/phpspreadsheet
./composer.json has been created
Running composer update phpoffice/phpspreadsheet
Loading composer repositories with package information
Updating dependencies
Your requirements could not be resolved to an installable set of packages.

Problem 1

- Root composer.json requires phpoffice/phpspreadsheet ^1.18 -> satisfiable by phpoffice/phpspreadsheet[1.18.0].
- phpoffice/phpspreadsheet 1.18.0 requires ext-gd * -> it is missing from your system. Install or enable PHP's gd extension.

To enable extensions, verify that they are enabled in your .ini files:

- /etc/php/7.4/cli/php.ini
- /etc/php/7.4/cli/conf.d/10-opcache.ini
- /etc/php/7.4/cli/conf.d/10-pdo.ini
- /etc/php/7.4/cli/conf.d/15-xml.ini
- /etc/php/7.4/cli/conf.d/20-calendar.ini
- /etc/php/7.4/cli/conf.d/20-ctype.ini
- /etc/php/7.4/cli/conf.d/20-dom.ini
- /etc/php/7.4/cli/conf.d/20-exif.ini
- /etc/php/7.4/cli/conf.d/20-ffi.ini
- /etc/php/7.4/cli/conf.d/20-fileinfo.ini
- /etc/php/7.4/cli/conf.d/20-ftp.ini
- /etc/php/7.4/cli/conf.d/20-gettext.ini
- /etc/php/7.4/cli/conf.d/20-iconv.ini
- /etc/php/7.4/cli/conf.d/20-json.ini
- /etc/php/7.4/cli/conf.d/20-mbstring.ini
- /etc/php/7.4/cli/conf.d/20-phar.ini
- /etc/php/7.4/cli/conf.d/20-posix.ini
- /etc/php/7.4/cli/conf.d/20-readline.ini
- /etc/php/7.4/cli/conf.d/20-shmop.ini
- /etc/php/7.4/cli/conf.d/20-simplexml.ini
- /etc/php/7.4/cli/conf.d/20-sockets.ini
- /etc/php/7.4/cli/conf.d/20-sysvmsg.ini
- /etc/php/7.4/cli/conf.d/20-sysvsem.ini
- /etc/php/7.4/cli/conf.d/20-sysvshm.ini
- /etc/php/7.4/cli/conf.d/20-tokenizer.ini
- /etc/php/7.4/cli/conf.d/20-xmlreader.ini
- /etc/php/7.4/cli/conf.d/20-xmlwriter.ini
- /etc/php/7.4/cli/conf.d/20-xsl.ini
- /etc/php/7.4/cli/conf.d/20-zip.ini

You can also run php --ini inside terminal to see which files are used by PHP in CLI mode.

I am getting this error while installing this package
how can I solve this issue ?
Mark Baker
@MarkBaker
You make sure that your build of PHP includes the gd extension - https://www.php.net/manual/en/book.image
whaliim
@whaliim
Hello Everyone. Could someone please assist me with my issue regarding table formatting and sorting?
https://stackoverflow.com/questions/68128848/how-to-keep-table-formatting-when-sorting-table-generated-by-phpspreadsheet
Thanks in advance.
Zer0xFF
@Zer0xFF
Hi there, Im trying to use PhpSpreadsheet to create an xlsx sheet with staff checkin/out time, and using cell formating to highlight late/early checkins, though i noticed the conditions dont seem to apply correctly
image.png