ARCHIVED
Other
11487
784
4192

PHPExcel Can I export Excel with formulas for cells

$name = 'test.xlsx';

$excel       = \PHPExcel_IOFactory::load($name);
$sheetData   = $excel->getActiveSheet()->toArray(NULL, FALSE, FALSE, TRUE);

and i get this error DOMDocument::loadHTMLFile(): Invalid char in CDATA 0x3 in file:///C:/Windows/TEMP/54366714e6daetest%20importu%20poloziek.xlsx, line: 1

wtf? phpexcel idenitfies the format as excel2007 why does it load html reader then? and why should excel load html files? what kind of bullshit is that?

Hi, I have a CSV with one column to read with this data:

SerialNumber
8954310143063931904
8954310143063931938
8954310143063931920
8954310143063931912
8954310143063931896
8954310143063931516
8954310143063931540

I am doing:
$inputFileType='Csv';
$objReader=IOFactory::createReader($inputFileType);
$objWorksheet = $objPHPExcel->getActiveSheet();
$cell=$objWorksheet->getCellByColumnAndRow($col,$fila);

And $cell gets cientific notation like 8.9543101430639E+18 instead of 8954310143063931904
How can solve this problem and get de values as strings?
Thanks

$this->xls->setActiveSheetIndex(0);
$sheet = $this->xls->getActiveSheet();
// works
$sheet->setTitle('Ventes par region');
// fails if my page is in Windows-1252 charset
$sheet->setTitle('Ventes par région');

e.g. when $objPHPExcel->setTitle("Reporte de Categorías"), in the exported file the property is stored as "Reporte de Categorías". The same thing happens with setDescription(), setLastModifiedBy() and similar methods. No problem occurs when storing utf8 chars in the cells, the only issue is for file properties.

PHPExcel version: 1.8
PHP version: 7.2.4

image

How can I group multiple columns? For example, I want to group column A, B and C together?

phpExcel 1.8.2 introduces a fatal error:

Call to undefined method PHPExcel_Calculation::_getMatrixDimensions()

Regards

Q A
Package version 1.8.2
PHP version 7.1.16

Actual Behaviour

When I used PHP Excel to write a large numeric string, I found that the behaviors of 1.8.1 and 1.8.2 were inconsistent. My code is as follows:

$objPHPExcel = new \PHPExcel();
        // Set document properties
        $objPHPExcel->getProperties()
            ->setCreator("REPORTINFO")
            ->setLastModifiedBy("VeryStar")
            ->setTitle("VeryStar REPORTINFO")
            ->setSubject("VeryStar REPORTINFO")
            ->setDescription("VeryStar REPORTINFO")
            ->setKeywords("VeryStar")
            ->setCategory("VeryStar");
        $sheet_num = 1;

        $phpExcelActiveSheet = $objPHPExcel->getActiveSheet();
        $objPHPExcel->setActiveSheetIndex(0)->setTitle('demo');
        $objPHPExcel->getActiveSheet()->getStyle('E')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
        $objPHPExcel->getActiveSheet()->getStyle('G')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
        $phpExcelActiveSheet->setCellValue('A' . $sheet_num, 'PHP Version');
        $phpExcelActiveSheet->setCellValue('B' . $sheet_num, 'System');
        $phpExcelActiveSheet->setCellValue('C' . $sheet_num, 'Excel Version');
        $phpExcelActiveSheet->setCellValue('D' . $sheet_num, 'test value 1');
        $phpExcelActiveSheet->setCellValue('E' . $sheet_num, 'test value 2');
        $phpExcelActiveSheet->setCellValue('F' . $sheet_num, 'test value 3');
        $phpExcelActiveSheet->setCellValue('G' . $sheet_num, 'test value 4');

        $sheet_num++;
        $phpExcelActiveSheet->setCellValue('A' . $sheet_num, PHP_VERSION);
        $phpExcelActiveSheet->setCellValue('B' . $sheet_num, PHP_OS);
        $phpExcelActiveSheet->setCellValue('C' . $sheet_num, '1.8.2');
        $phpExcelActiveSheet->setCellValue('D' . $sheet_num, '898350553311499'."\t");
        $phpExcelActiveSheet->setCellValue('E' . $sheet_num, '898350553311499');
        $phpExcelActiveSheet->setCellValue('F' . $sheet_num, '898350553311491'."\t");
        $phpExcelActiveSheet->setCellValue('G' . $sheet_num, '898350553311491');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $file_name = '/data/demo-1.8.2.xlsx';
        $objWriter->save($file_name);

1.8.2 Write is the following data:
demo-1.8.2.xlsx
1 8 2

The number is found to be rounded up according to the mantissa
But 1.8.1 does not have this problem:
demo-1.8.1.xlsx
1 8 1

When I read a cell which contains multiple lines of text, I receive string containing x000D.

I have created workaround, but believe this should be in phpexcel itself.

    function unescape($string)
    {
        return preg_replace_callback('/_x([0-9a-fA-F]{4})_/', function ($matches) {
            return iconv('UCS-2', 'UTF-8', hex2bin($matches[1]));
        }, $string);
    }

Link to corresponding spec: https://msdn.microsoft.com/en-us/library/ff534667(v=office.12).aspx

Hello, I am using this library for an application.
I have a problem with the images and the library seems is not working well.
I am using Excel 2016
When filtering columns, the images for the hidden columns still showing.

Look at the attached pictures.
Is there a way to solve this?

1
2

$endCoordinates = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_Cell::stringFromColumnIndex($iInc - 1));

Second including brake work of this class.

I think it need only one call of it.

file: Classes/PHPExcel/Calculation.php, line: 503
'COUNTIFS' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL, 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY', 'argumentCount' => '2')

currently accepts 2 arguments only but according to documentation can be more: 2, 4, 6...

I know this version is deprecated, but corporately we don't have a choice but to be on PHP 5.4.16 at this time (am on CentOS 7 using Docker container - Linux {containerid} 4.9.93-linuxkit-aufs #1 SMP Wed Jun 6 16:55:56 UTC 2018 x86_64). I still need the functionality to be able to create xls with multiple tabs or I wouldn't bother with an add-on. This is the issue that I am having...

PHPEXCEL_ROOT="/var/www/html/DaysRpt/classes/PHPExcel/"

PHPExcel.php has the change to where the PHPExcel class files are and it does find the autoloader ok as well in this configuration --
if (!defined('PHPEXCEL_ROOT')) {
define('PHPEXCEL_ROOT', dirname(FILE) . '/PHPExcel/');
require(PHPEXCEL_ROOT . 'Autoloader.php');
}

Autoloader.php has been changed to this (I've tried using PHPEXCEL_ROOT and various combinations with only slightly different can't find errors). But at least with this, it can find the PHPExcel_Shared_String and does the preload correctly--
$pClassFilePath = "/var/www/html/DaysRpt/classes/" .
str_replace('_', DIRECTORY_SEPARATOR, $pClassName) .
'.php';

No matter how I alter the PHPEXCEL_ROOT or the paths in both of these files, it either doesn't find the files from the autoloader or it doesn't find the class file specificed in the PHPExcel.php....here's the error from the error_log when using this combination....

PHP Fatal error: Class 'PHPExcel_Calculation' not found in /var/www/html/DaysRpt/classes/PHPExcel.php on line 367, referer: http://localhost:9080/DaysRpt/index.php

Again, I know I should upgrade my PHP and use PHPspreadsheet instead and I wish I could, but in the meantime I am hoping someone out there can still assist with this configuration setup. Thanks.

Hello, do you have a simplified version of PHPExcel? I only want an exported function, which can be used in any system. The current automatic loading function of PHPExcel is in conflict with the system I am currently using, so I want to simplify the function of PHPExcel, instead of automatically loading

I can open all type of file,but when the files need passwords I can't open it and get content.

I change server website and i see this issue

"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.

Pls help me fix this

Many thanks

I can't export any excel files, but it returns me a bunch of garbled.I just export an empty excel file.

$PHPExcel = new \PHPExcel(); $fileName='test'; $PHPWriter = \PHPExcel_IOFactory::createWriter($PHPExcel,'Excel2007'); header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Type: application/cha'); header("Content-Disposition: attachment;filename=$fileName".".xlsx"); header('Cache-Control: max-age=0'); $PHPWriter->save("php://output");

Hi, guys, can you please tell me how to load XLS file and save it as static html page without empty rows/columns

My current listing is

require_once 'assets/libs/phpexcel/PHPExcel/IOFactory.php';
$xls=$_GET['xls']; //example

if (!file_exists($xls)) {
	$out = "<p style=\"padding:1rem\">No table attached</p>";
    return $out;
}else{

//new file name
$htm=str_replace('.xlsx','.htm',$xls);
//read
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load($xls);
//saving to html
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'HTML');
$objWriter->setSheetIndex(0);
$objWriter->save(str_replace('.xlsx', '.htm', $xls));
//read from file
$table=file_get_contents($htm);
$output=substr($table,strpos($table,'<body>'),strpos($table,'</body>')-strpos($table,'<body>'));
$output=str_replace('border="0" cellpadding="0" cellspacing="0"','border="1" cellpadding="1" cellspacing="1"',$output);
echo $output;
}

Result produces large amount of empty columns and rows(cut)
2017-02-06 16 32 46

how to avoid empty cells?
Attached sample file
01.xlsx