Is it possible to increase the character limit of an Excel spreadsheet from within PHP?
I don't see any notes or settings in the documentation about an existing character limit or even setting your own max cell character count.
Microsoft does have a hard limit of 32,767 characters per cell.
I have used PHPExcel class in php to export data but PHPExcel version 1.8 is deprecated in 2015.
Can I get any alternative solution for exporting multiple sheets.
You can use PHPSpreadhsheet. This is what the currently deprecated PHPExcel library has been continued as.
According to the docs. You will need to use the Composer package manager to install this library on your machine, by running the following command at the location of installation -
composer require phpoffice/phpspreadsheet
Include the library in your .php file in the following way -
<?php
// load the classes provided by PHPSpreadSheet
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$spreadsheet = new Spreadsheet();
// code to create and use worksheet goes here
?>
To create/add new worksheets you can use this function -
$spreadsheet->createSheet();
You can create multiple worksheets by using the above command in a for() or while() loop.
To get the worksheet and edit it, you can fetch it by index in the following way -
$spreadsheet->getSheet(1);
The above command will fetch the second sheet from the workbook (since the worksheets are always indexed from "0").
I want get named ranges in my file. just name of my ranges. in my file, i have ranges 'detail', 'header', 'footer'. this is my function
$temp_name = [];
foreach($spreadsheet->getNamedRanges() as $name){
$temp_name [] = $name;
}
print_r("<br>Temp Name<br>");
print_r($temp_name[]);
print_r("<br><br>");
but i get so many value in $temp_name.
I don't use PhpSpreadsheet, but looking at their documentation, getNamedRanges returns an array of NamedRanges, which in turn have names, it doesn't return just names alone. So you need to get the name from the range, like -
foreach ($spreadsheet->getNamedRanges() as $range) {
temp_name[] = $range->getName();
}
also re: print_r("<br>Temp Name<br>"); for strings, just use print '..';, print_r() is for arrays.
I am writing to excel row by row and try to get current row height after write to each row.
I tried use getDefaultRowHeightByFont() to get default row height based on font type.
$font_type='times new roman';
$sheet->getDefaultRowHeightByFont(\PhpOffice\PhpSpreadsheet\Style\Font.$font_type)
The row height should be exact value that we get when manually check for row height in excel sheet. But, I got the error
Call to undefined method PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::getDefaultRowHeightByFont()
How to call that function correctly? Thanks in advance.
Able to make it run.
use PhpOffice\PhpSpreadsheet\Shared\Font as SharedFont;
$default_rowheight=SharedFont::getDefaultRowHeightByFont($spreadsheet->getDefaultStyle()->getFont());
But, this method only show row height for default font style in that sheet. So, we could not find accurate row height for row with different font style from default.
I am trying to export data from MySQL to xlsx but in my database may have some 0 and 1 instead of real data. Let me explain better: There's a column used to set if it's day or night but using 0 to day and 1 to night. When i export using the code bellow, i get a xlsx with these 0 and 1.
Already tried stuff like https://phppot.com/php/database-data-export-to-excel-file-using-php/ and https://artisansweb.net/how-to-export-mysql-database-data-to-excel-using-php/ but i just can't edit what's going to xlsx before sending
$filename = "Export_excel.xls";
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=\"$filename\"");
$isPrintHeader = false;
if (! empty($productResult)) {
foreach ($productResult as $row) {
if (! $isPrintHeader) {
echo implode("\t", array_keys($row)) . "\n";
$isPrintHeader = true;
}
echo implode("\t", array_values($row)) . "\n";
}
}
When the code see a zero, i want to set it to "day"
If you want to show specific fields only.
select id,name, ( CASE WHEN table_column = 0 THEN 'day' WHEN table_column = 1 THEN 'night' END ) as table_column from your_table
Output Look Like:
id name table_column
1 Lucas day
I'm using PHPSpreadSheet and I want to set money format for a cell. In PHPExcel you can do this by using the following lines...
$this->phpExcelObject->getActiveSheet()
->getStyle('D4')
->getNumberFormat()
->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);
I tried to this in PHPSpreadSheet, this is what i did...
$this->spreadsheet->getActiveSheet()
->getStyle('D4')
->getNumberFormat()
->setFormatCode('$ #,##0.00');
But it doesn't work. Any ideas? or am I doing it wrong? Thanks in advance.
I couldn't do it with PHPSpreadSheet. In case someone runs with the same problem, this is what I did (not a proper solution but it's something).
I created a function to return the value with the money symbol.
public function formatValueMoney($number){
$val = number_format($number,2,".",",");
$setVal = '$'.$val;
return $setVal;
}
$int = formatValueMoney(34567898765);
echo $int;
and this : $34,567,898,765.00 is the result. i just added this in the cell.
In project of PHPOffice there are two projects associated with spreadsheet file formats:
PHPExcel is a library written in pure PHP and providing a set of classes that allow you to write to and read from different spreadsheet file formats, like Excel (BIFF) .xls, Excel 2007 (OfficeOpenXML) .xlsx, CSV, Libre/OpenOffice Calc .ods, Gnumeric, PDF, HTML, ... This project is built around Microsoft's OpenXML standard and PHP.
and
PhpSpreadsheet is a library written in pure PHP and providing a set of classes that allow you to read from and to write to different spreadsheet file formats, like Excel and LibreOffice Calc.
What are the main differences between them?
PHPExcel has been maintained as a library for working with spreadsheet files for many years now, and has been shackled by retaining support for older versions of PHP (>= 5.2) making it very difficult to move forward and improve it. It is a stable library, but will not be developed any further.
PHPSpreadsheet is the newest version of PHPExcel, and large parts of it have been rewritten to take advantage of the newer features of PHP. While retaining all the functionality of PHPExcel, it requires a minimum PHP version of 5.5 (and soon that will be dropped to require a minimum of 5.6).
The change in library name was to reflect the fact that it isn't limited to Excel spreadsheets; but supports a wider range of spreadsheet file formats.
EDIT 2020:
PHP Excel was officially deprecated in 2017 and permanently archived in 2019.
PHP Excel has not be maintained for years and must not be used anymore. All users must migrate to its direct successor PhpSpreadsheet, or another alternative.
I want to set height and width in my new file from template file
$string = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col); // e.g. 5
$width = $worksheet->getCellByColumnAndRow($col, $row)->getWidth();
$height = $worksheet->getCellByColumnAndRow($col, $row)->getHeight();
$spreadsheet->getActiveSheet()->getColumnDimension($string)->setWidth($width);
$spreadsheet->getActiveSheet()->getRowDimension($row)->setHeight($height);
i have error:
"Call to undefined method PhpOffice\PhpSpreadsheet\Cell\Cell::getWidth()"
There is no such method for a cell, you must set it to the entire colmun or row. Use getColumnDimension() on a column
$sheet->getColumnDimensionByColumn()
$sheet->getColumnDimensions()
$sheet->calculateColumnWidths() // Calculate widths for auto-size columns.
Plus you have a method to do that automatically:
/**
* Update column dimensions when inserting/deleting rows/columns.
*
* @param Worksheet $pSheet The worksheet that we're editing
* @param string $pBefore Insert/Delete before this cell address (e.g. 'A1')
* @param int $beforeColumnIndex Index number of the column we're inserting/deleting before
* @param int $pNumCols Number of columns to insert/delete (negative values indicate deletion)
* @param int $beforeRow Number of the row we're inserting/deleting before
* @param int $pNumRows Number of rows to insert/delete (negative values indicate deletion)
*/
protected function adjustColumnDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
{
$aColumnDimensions = array_reverse($pSheet->getColumnDimensions(), true);
if (!empty($aColumnDimensions)) {
foreach ($aColumnDimensions as $objColumnDimension) {
$newReference = $this->updateCellReference($objColumnDimension->getColumnIndex() . '1', $pBefore, $pNumCols, $pNumRows);
list($newReference) = Coordinate::coordinateFromString($newReference);
if ($objColumnDimension->getColumnIndex() != $newReference) {
$objColumnDimension->setColumnIndex($newReference);
}
}
$pSheet->refreshColumnDimensions();
}
}
In PhpOffice\PhpSpreadsheet\ReferenceHelper
I want to align the cell value to the middle. My output looks like this:-
My expected output should be this:
I want every column to be in the center. I tried the following code:
$styleArray = [
'font' => [
'bold' => true,
],
'alignment' => [
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
],
'fill' => [
'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
'startColor' => [
'argb' => '0070C0',
],
'endColor' => [
'argb' => '0070C0',
],
],
];
$spreadsheet->getDefaultStyle()->getFont()->setSize(10);
I tried all the other attributes like HORIZONTAL_CENTER, RIGHT, LEFT, JUSTIFY, etc. How can I do this properly?
You're setting the wrong (and one too few) key(s) for the alignment setting. What you're attempting to achieve is the vertical and horizontal alignment of the text.
'alignment' => [
'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
],
According to the PhpSpreadsheet Doc it's neccessary to install it with composer. In my case I just have a webspace without Terminal but Plesk. Is it anyway possible to use PhpSpreadsheet, like it is with PHPExcel where you just have to place the files in any location? What do I have to do to get it run? I found no further information how to with only FTP webserver access.
In your case there are two options for you!
Answer: 1
Alternative method without terminal
Run composer with a PHP script in browser
Answer: 2
Third party sites, which allow to download composer packages online. get PHPspreadsheet latest version.
https://php-download.com/package/phpoffice/phpspreadsheet
Bonus You can download almost any composer packages @ https://php-download.com
How get grouping rows in PHP from EXCEL?
This code does not issue a groups:
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
$reader->setReadDataOnly(TRUE);
$spreadsheet = $reader->load("bulat_price.xlsx");
$worksheet = $spreadsheet->getActiveSheet();
$worksheet->toArray();
$spreadsheet = PHPExcel_IOFactory::load($file); // load file
$objWorksheet = $spreadsheet->getActiveSheet();
$worksheet = $spreadsheet->setActiveSheetIndex(0); // select firts sheet
$i = 0;
$arrLevel = [];
foreach ($worksheet->getRowDimensions() as $rowDimension) {
$i++;
$arrLevel[$i]['level'] = $rowDimension->getOutlineLevel(); // get level
}
$highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
foreach ($objWorksheet->getRowIterator() as $row) {
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(true);
foreach ($cellIterator as $cell) {
$arrLevel[$row->getRowIndex()]["excel"][$cell->getColumn()] = $cell->getValue(); // merge level and value
}
}
var_dump($arrLevel);
We get:
Hi I'm new to this library called PHPSpreadsheet. I tried reading it's docs but I can't understand it.
I want to insert a new row on an existing Excel File and here is what I have so far:
<?php
require '../vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$inputFileName = 'Excel/hello.xlsx';
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileName);
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'Updated');
$writer = new Xlsx($spreadsheet);
$writer->save('../controller/excel/hello.xlsx');
?>
This inserts new data on the 'hello.xsls' file replacing the cell's previous data. How can I make it write data into a new row?
To create a new row, you need to call insertNewRowBefore() with the row number you want to insert before...
$sheet = $spreadsheet->getActiveSheet();
$sheet->insertNewRowBefore(1);
$sheet->setCellValue('A1', 'Updated');
You can also call it with a number of rows to insert, the default is 1.
If you want to append a row, you can call getHighestRow() to find the last row and add 1 to it for the new row. Also change the hard coding of the column in the setCellValue() call to use this row as well...
$sheet = $spreadsheet->getActiveSheet();
$row = $sheet->getHighestRow()+1;
$sheet->insertNewRowBefore($row);
$sheet->setCellValue('A'.$row, 'Updated');
I have been using Laravel Excel to export data in csv format and it has been great so far. Now I need to export in xlsx format so that I can include dropdown lists in some of the columns. I have looked at this question but it looks like that is for an older version of Laravel Excel. I also looked at the page in the docs that explains extending the package, but I can't seem to figure out how to add a dropdown list to a column while exporting data.
This is a simplified version of my export class:
namespace App\Exports;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithStrictNullComparison;
class ActionItemExport implements FromCollection, WithHeadings, WithStrictNullComparison
{
public function collection()
{
return $this->getActionItems();
}
public function headings(): array
{
$columns = [
'Column 1',
'Column 2',
'Column 3',
'Column 4',
'Column 5',
'Column 6',
'Column 7'
];
return $columns;
}
private function getActionItems()
{
$select = 'column1, column2, column3, column4, column5, column6, column7';
$query = \DB::table('action_items')->select(\DB::raw($select));
$query->whereNull('action_items.deleted_at');
$ai = $query->orderBy('column1')->get();
return $ai;
}
}
What I would like to do is query a lookup table that has the options for column1 and use those values for a drop down list in the column so that when a user wants to change the excel sheet, they are limited to only the drop down values.
In the docs it mentions to use \Maatwebsite\Excel\Sheet or \Maatwebsite\Excel\Writer, but I'm not even sure where to use those at, or which one to use.
Throughout my searches I just can't seem to piece together a solution so any help would be appreciated.
I'm using:
maatwebsite/excel 3.1, php 7.2, laravel 5.8
The implementation of sheet events can be rather confusing and hard to find examples for, so I try to lend a hand when I see a post like this. First, I'll say you should really be looking at the PHPSpreadsheet documentation for these additional features. It's where you're going to find the important information you need. Then you can translate what you find for use in Laravel Excel.
PHPSpreadsheet: Setting data validation on a cell https://phpspreadsheet.readthedocs.io/en/latest/topics/recipes/#setting-data-validation-on-a-cell
Here is an example building upon your existing file. I also threw in some bonus formatting to autosize the column widths — a must in my opinion.
namespace App\Exports;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithStrictNullComparison;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Events\AfterSheet;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
class ActionItemExport implements FromCollection, WithHeadings, WithEvents, WithStrictNullComparison
{
protected $results;
public function collection()
{
// store the results for later use
$this->results = $this->getActionItems();
return $this->results;
}
// ...
public function registerEvents(): array
{
return [
// handle by a closure.
AfterSheet::class => function(AfterSheet $event) {
// get layout counts (add 1 to rows for heading row)
$row_count = $this->results->count() + 1;
$column_count = count($this->results[0]->toArray());
// set dropdown column
$drop_column = 'A';
// set dropdown options
$options = [
'option 1',
'option 2',
'option 3',
];
// set dropdown list for first data row
$validation = $event->sheet->getCell("{$drop_column}2")->getDataValidation();
$validation->setType(DataValidation::TYPE_LIST );
$validation->setErrorStyle(DataValidation::STYLE_INFORMATION );
$validation->setAllowBlank(false);
$validation->setShowInputMessage(true);
$validation->setShowErrorMessage(true);
$validation->setShowDropDown(true);
$validation->setErrorTitle('Input error');
$validation->setError('Value is not in list.');
$validation->setPromptTitle('Pick from list');
$validation->setPrompt('Please pick a value from the drop-down list.');
$validation->setFormula1(sprintf('"%s"',implode(',',$options)));
// clone validation to remaining rows
for ($i = 3; $i <= $row_count; $i++) {
$event->sheet->getCell("{$drop_column}{$i}")->setDataValidation(clone $validation);
}
// set columns to autosize
for ($i = 1; $i <= $column_count; $i++) {
$column = Coordinate::stringFromColumnIndex($i);
$event->sheet->getColumnDimension($column)->setAutoSize(true);
}
},
];
}
}
I try to install a specific spreachsheet lib with this command:
composer require phpoffice/phpspreadsheet:1.8.2 (because I have Php5 version)
I have this error :
Your requirements could not be resolved to an installable set of packages.
Problem 1
- symfony/symfony is locked to version v3.4.10 and an update of this package was not requested.
- Only one of these can be installed: symfony/twig-bundle[v3.4.26], symfony/symfony[v3.4.10]. symfony/symfony replaces symfony/twig-bundle and thus cannot coexist with it.
- symfony/twig-bundle is locked to version v3.4.26 and an update of this package was not requested.
my composer.json:
{ "name": "symfony/framework-standard-edition", "license": "MIT", "type": "project", "description": "The "Symfony Standard Edition" distribution", "autoload": { "psr-4": { "": "src/" }, "classmap": [ "app/AppKernel.php", "app/AppCache.php" ] }, "autoload-dev": { "psr-4": { "AppBundle\Tests\": "tests/" }, "files": [ "vendor/symfony/symfony/src/Symfony/Component/VarDumper/Resources/functions/dump.php" ] }, "require": { "php": ">=5.5.9", "beberlei/doctrineextensions": "1.1.9", "doctrine/doctrine-bundle": "^1.6", "doctrine/doctrine-migrations-bundle": "^1.3", "doctrine/orm": "^2.5", "incenteev/composer-parameter-handler": "^2.0", "paragonie/random_compat": "~1.3", "phpseclib/phpseclib": "^2.0", "ramsey/uuid": "^3.8", "sensio/distribution-bundle": "^5.0.19", "sensio/framework-extra-bundle": "^5.0.0", "symfony/assetic-bundle": "^2.8", "symfony/monolog-bundle": "^3.1.0", "symfony/polyfill-apcu": "^1.0", "symfony/symfony": "3.4.10", "symfony/translation": "^3.4", "symfony/twig-bundle": "3.4.26", "symfony/validator": "^3.4", "theodo-evolution/legacy-wrapper-bundle": "^1.2", "theodo-evolution/session-bundle": "^1.0", "twig/twig": "^1.0||^2.0" }, "require-dev": { "phpunit/phpunit": "^5.7.27", "sensio/generator-bundle": "^3.0", "symfony/phpunit-bridge": "^3.0" }, "scripts": { "symfony-scripts": [ "Incenteev\ParameterHandler\ScriptHandler::buildParameters", "Sensio\Bundle\DistributionBundle\Composer\ScriptHandler::buildBootstrap", "Sensio\Bundle\DistributionBundle\Composer\ScriptHandler::clearCache", "Sensio\Bundle\DistributionBundle\Composer\ScriptHandler::installAssets", "Sensio\Bundle\DistributionBundle\Composer\ScriptHandler::installRequirementsFile", "Sensio\Bundle\DistributionBundle\Composer\ScriptHandler::prepareDeploymentTarget" ], "post-install-cmd": [ "@symfony-scripts" ], "post-update-cmd": [ "@symfony-scripts" ] }, "config": { "platform": { "php": "5.6" }, "sort-packages": true }, "extra": { "symfony-app-dir": "app", "symfony-bin-dir": "bin", "symfony-var-dir": "var", "symfony-web-dir": "web", "symfony-tests-dir": "tests", "symfony-assets-install": "relative", "incenteev-parameters": { "file": "app/config/parameters.yml" }, "branch-alias": { "dev-master": "3.4-dev" } } }
Someone has an idea please ?
symfony/symfony is a package that contains all components of the Symfony framework. This also contains symfony/twig-bundle. There's no need to install both of these packages, so you can safely remove symfony/twig-bundle. Afterwards, it should be possible to add whatever package you want to add
I am using matwebsite for exporting excel files ,i am getting the data from the database it's stored in the exported excel file upto this part it's working fine .some of the values are more than 15 digits at that time it's displaying as scientifc format for that i made some changes using columnFormats() function now it supports upto 16 digits ,if any value more than 16 digits it's replacing last digits as zero's ,How to rectify this error please help me to fix this issue..
``
public function columnFormats(): array
{
return [
'D' => '#0',
];
}
**my downloaded excel**
1234567890123456 //16 digits it's working fine
12345678901234567000 //actual value is 1234567890123456789
Columnformats function tells the excel to convert to a numeric behaviour in Excel the numeric supports upto 16 digits so this change is not working in your case.
If you want to acheive your scenario use this following reference bindValue() function and change the data type as string(string in Excel supports more characters).
https://docs.laravel-excel.com/3.1/imports/custom-formatting-values.html
Posts
All Comments
