Where communities thrive


  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
People
Repo info
Activity
rincevent
@rincevent:matrix.org
[m]
Hi team
I'm trying to edit an axisting Xlsx file.
I load it with createReader("Xlsx")
I lanch the update
Create a writter and save it
My issue is about the rest of the grid of the xlsx. It all receive a style on border of cell.
How to keep my xlsx without those borders on all cells ? Did you already met the issue ?
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader("Xlsx");
$spreadsheet = $reader->load($target_path_valid_file);

$sheet = $spreadsheet->getActiveSheet();
$last_row = (int) $sheet->getHighestRow();
$new_row = $last_row+1;

$sheet->setCellValue('A'.$new_row, "14");
$sheet->setCellValue('B'.$new_row, "Alina");
$sheet->setCellValue('C'.$new_row, "PG");
$sheet->setCellValue('D'.$new_row, "$32");
$sheet->setCellValue('E'.$new_row, "Pending");

$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, "Xlsx");
$writer->save($target_path_valid_file);
rincevent
@rincevent:matrix.org
[m]
All the row and col under/after the exiting data have the border set
I already tried to use the global BORDER_NONE but it work on the sheet loaded not on the rest.
Mathieu Cambois
@mcambois_gitlab
I faced the same issue because I was using a template where my sheet contains too much styled rows, are you using borders from col A to XFD ?
2 replies
asifcse07
@asifcse07
image.png
hi, is there anyone have idea how can I generate a chart like this in phpshpreadsheet? kindly help. i have multiple tabs in single excel sheet
Rickard Wettström
@Wetric
Hi. Is there a way to do a XLOOKUP(seems not to be implemented) or something similar to join data?
demokn
@demokn
Hi, when I save as html, the column width is not set. Is there a way to do so, fixed column width same as the original xls column ?
Frédéric Delaunay
@Dfred
Hi there. Is there a simple way to get an unbounded getCellIterator() from a Row ?
if I call $a_row->getCellIterator($column_out_of_bounds) a PhpSpreadsheetException is thrown
Frédéric Delaunay
@Dfred
ok my bad, my version of PhpSpreadsheet seems too old..
於志远
@yuzhiyuan
Hello everyone, I have a question. I imported data with PHPoffice from an excel file, when the field is Chinese string, the field result of sheetData is false. Is this a bug?
like this:
["D"]=>
string(6) "000690"
["E"]=>
bool(false)
Thanks!
9 replies
image.png
Sakthi01
@Sakthi01
Hi everyone, I have used $spreadsheet->getActiveSheet()->setAutoFilter('D4:Q4'); to set auto filter, like this is there any way to unset autofilter for a specific column.
AYCore
@Yryskeldi5
Hello everyone! I'm writing to developers of PhpSpreadsheet lib. I'm using Symfony framework version 5.3.1. I ran into error in xlsx file when opening through MS Excel. I update version of phpspreadsheet from 1.16 to 1.19 after that my xlsx file shows error with xml : Replaced component: part /xl/worksheets/sheet1.xml with XML error. Loading error. Line 2, column 0. and also shows empty table , but If I open in LibroOffice there's no problem, works fine. Firstly I put the version 1.16 and test, until 1.19 version works well. Pay attention to this err, I hope others will not face it.
shyamsasit
@shyamsasit
Hello friends,->getCell('B1')->getStyle()->getFill()->getStartColor()->getARGB() is only returning FFFFFFF for almost all cells but if cell is green its returning correctly
any idea?
tonysb
@tonysb:matrix.org
[m]
new to this phpoffice. just installed phpspreadsheet on my local computer and l am getting this error essage. Notice: Undefined index: rId3 in C:\xampp\htdocs\dk\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Reader\Xlsx.php on line 624
anyone can help please...
iamrobot
@bubigobolemeot_twitter
Hi, everyone
is there a way to set custom linear gradient colors to PHPspreadsheet pie chart?
i only managed to set custom colors
zkenstein
@zkenstein
Hello all, is anyone onow how to modify the cell value depend on id from another cell?
Something like if A1 = foo, and set cell value on B1 = bar
Sergey Popov
@windstep_gitlab
image.png

Hey, guys! I have a big problem with an corrupted xls file. And I could not understand why.

Code is as simple as i sent earlier

anand-impero
@anand-impero
i used following code and chart is created but without data so please help me
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReaderForFile("Flow Balance Worksheet APP Development 01032022.xlsx");
$reader->setIncludeCharts(true);
$spreadsheet = $reader->load(request()->file('file'), $reader::LOAD_WITH_CHARTS);
$sheet = $spreadsheet->getActiveSheet();
$chart = $sheet->getChartCollection();
$writer = new Xlsx($spreadsheet);
$writer->setPreCalculateFormulas(true);
$writer->setIncludeCharts(true);
$writer->save('demo.xlsx');
rohit-barman
@rohit-barman
Hi there!
vanfsy
@vanfsy
Hello
Hey, guys! I try to read several styles in a cell.
we can retrieve almost styles like font-size, font-family, bold, etc.. ,but can't get color.
Plz check it for me and let me know.
vanfsy
@vanfsy
$inputFileType = \PhpOffice\PhpSpreadsheet\IOFactory::identify($inputFileName);
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
$spreadsheet = $reader->load($inputFileName);
$cellValue = $spreadsheet->getActiveSheet()->getCell('A1')->getValue();
if ($cellValue instanceof \PhpOffice\PhpSpreadsheet\RichText\RichText) {
foreach ($cellValue->getRichTextElements() as $richTextElement) {
var_dump($richTextElement->getText());
if ($richTextElement->getFont()) { var_dump($richTextElement->getFont()->getColor()->getRGB()); }
}
}
Norberto Capalbo
@centraldeherramientas.drventas_gitlab
Hi, I have problem with excel generation PHPSpreadsheet when i try to download the excel, excel cant open for invalid format.

$nombreDelDocumento = "Descarga_paqar.xlsx";

Crear un "escritor"

$writer = new Xlsx($documento);

Le pasamos la ruta de guardado

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $nombreDelDocumento . '"');
header('Cache-Control: max-age=0');
$writer = IOFactory::createWriter($documento, 'Xlsx');
ob_end_clean();
$writer->save('php://output');
exit;

milomylo
@milomylo
hi everyone
shoaib2510
@shoaib2510
hi
can anyone help me link my phpspreadsheet output to phpmailer without downloading the xlsx file
6 replies
aswinkumar863
@aswinkumar863

Hi,

I have found that the useful Excel tables feature is missing in the PHPSpreadsheet library. Issue PHPOffice/PhpSpreadsheet#1816.

After some research on how the feature is implemented in exceljs, I'm planning to contribute on adding this feature to PHPSpreadsheet.

I'm here seeking any advice from the team before start working. If I file a pull request, will it merge?


Overview of Excel tables

Reasons to use Excel Tables

1 reply
ukenpachi
@ukenpachi

any idea what causes this error? Fatal error: Uncaught PhpOffice\PhpSpreadsheet\Reader\Exception: Parameter pos=-12 is invalid

$inputFileName = "excel.xls";
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xls');
$spreadsheet = $reader->load($inputFileName);

file: https://filebin.net/sle19tm0kdgduyne/excel.xls?t=u0itbeue

Hi Alex, I am not sure if you can remember how you resolved this issue? Parameter pos=-12 is invalid

$reader = PhpOffice\PhpSpreadsheet\IOFactory::createReader($ext);
$reader->setReadDataOnly(true);
$spreadsheet = $reader->load($file);
// Export to CSV file.
$writer = PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Csv');
$writer->setSheetIndex(0); // Select which sheet to export.
$writer->setDelimiter(';'); // Set delimiter.
$writer->save($temp_storage);
DavidKirn
@DavidKirn
Hey, I have a quick question. Must Excel be installed on the system on which you want to create an Excel file with PhpSpreadsheet?
5 replies
Manish Jain
@manish-coreschedule_gitlab
Hi , i am importing style data from an excel to php and running into an error with the background color, I get 'ffffff' even thou the background is set to "No Fill".
Q. Any way to differentiate between White bg and No Fill bg when reading from file ?
El-Tommy
@El-Tommy

Hello,
How can I display a formatted time range in an Excel worksheet?

I have a range of 25:30 hours that I would like to show using HH:MM:SS format, but if I use the code below it shows as 01:30:00.

$seconds = 91800;
$spreadsheet->getActiveSheet()->setCellValueExplicit('L' . $row, (seconds / 86400) , PHPExcel_Cell_DataType::TYPE_NUMERIC);
$spreadsheet->getActiveSheet()->getStyle('L'.$row)->getNumberFormat()->setFormatCode('HH:MM:SS');

4 replies
LudovicGit
@LudovicGit
Hello,
Is that possible to lock a xlsx file? I mean lock by password when a user tries to open the file. locked to read, not locked to write.
I found few functions to add a password but it's only for write permission and not read permission
1 reply
Howitzer
@KyleHinskens
Hi, is it possible to adjust the horizontal scrollbar width with phpspreadsheet?
3 replies
shan
@shanmugamani96
hello guys,how to set limit to output data when i try to read excel data ,i need to set pagination,cant set limits ,how to resolve that?and i need to display the data as html in browser.,without null values.
Lix
@Oranzh

hello guys,how to set limit to output data when i try to read excel data ,i need to set pagination,cant set limits ,how to resolve that?and i need to display the data as html in browser.,without null values.

Why don't u save the data in other place from excel, so u can paginate it easily.

shan
@shanmugamani96
which data type do u prefer?
Lix
@Oranzh

which data type do u prefer?

Mysql, Tidb , ElasticSearch or Redis