Closed Raman117 closed 1 month ago
A few preliminaries first. It is difficult for us to work with pictures of code. You can embed code directly in your comment by including a line consisting of 3 tick-marks followed by the string php, then adding your code, and terminating it with a line consisting of 3 tick-marks. Now, on to your question ...
Are you trying to color the labels in your pie chart, or are you trying to color the pieces of pie? If it is the latter, test BarChartCustomColorsTest does what you want, and you can borrow the code from it. (No, I do not know why the test is titled BarChart... but it deals with pie charts, but the same code applies to both.) In particular, you are interested in this code:
$dataSeriesValues1Element = new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$C$2:$C$5', null, 4);
$dataSeriesValues1Element->setFillColor($colors);
$dataSeriesValues1 = [$dataSeriesValues1Element];
Now, I think you possibly have done the equivalent by setting colors in the constructor which you use in assigning a value to $values
, so I do need to look further. But, as I mentioned above, it is much easier to investigate when I can start with code vs. a picture.
I adapted your code to try to eliminate the differences between it and the test case mentioned above.
$worksheet = $spreadsheet->getActiveSheet();
$worksheet->fromArray(
[
['', 2010, 2011, 2012],
['Q1', 12, 15, 21],
['Q2', 56, 73, 86],
['Q3', 52, 61, 69],
['Q4', 30, 32, 0],
]
);
$categorieCoOrdinate = 'Worksheet!$A$2:$A$5';
$labelCell = '$B$6';
$labelCellRelative = 'B6';
$worksheet->getCell($labelCellRelative)->setValue('ChartLabel');
// Custom colors for dataSeries (greenish, yellowish, red, gray)
$colors = [
'70ad47',
'ffc000',
'ff0000',
'757575',
];
$label = [
new DataSeriesValues(
DataSeriesValues::DATASERIES_TYPE_STRING,
$labelCell,
null,
1,
[],
null,
$colors
),
];
$categories = [
new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, $categorieCoOrdinate, null, 4),
];
$values = [
new DataSeriesValues(
DataSeriesValues::DATASERIES_TYPE_NUMBER, // dataType
'Worksheet!$C$2:$C$5', // dataSource
null, // formatCode
4, // pointCount
[], // dataValues
null, // marker
$colors // fillColor
)
];
// Build the dataseries
$series1 = new DataSeries(
DataSeries::TYPE_PIECHART, // plotType
null, // plotGrouping (Pie charts don't have any grouping)
range(0, count($values) - 1), // plotOrder
$label, // plotLabel
$categories, // plotCategory
$values // plotValues
);
$plotArea1 = new PlotArea(null, [$series1]);
$legend1 = new ChartLegend(ChartLegend::POSITION_BOTTOM, null, false);
$title1 = new Title('Test Pie Chart');
// Create the chart
$chart1 = new Chart(
'chart1', // name
$title1, // title
$legend1, // legend
$plotArea1, // plotArea
true, // plotVisibleOnly
DataSeries::EMPTY_AS_GAP, // displayBlanksAs
null, // xAxisLabel
null // no Y-Axis for Pie Chart
);
// Set the position where the chart should appear in the worksheet
$chart1->setTopLeftPosition('A7');
$chart1->setBottomRightPosition('H20');
// Add the chart to the worksheet
$worksheet->addChart($chart1);
Here's the result:
That looks right to me based on the colors you supplied. Do you see something different?
Added the same code but there is still this issue
now I am using Laravel Excell's withCharts but it uses PhpSpreadsheet which I upgraded to latest
shouldve been Green,yellow,red, and grey but here is default colors
code is same as you wrote, again the code is same as you gave changed nothing
I'm having the same problem for months now. I copy and pasted the code @oleibman has given in the comment above. This is my result:
I'm using PHP 8.3 and PHPSpreadsheet 2.1.0. I'm opening the excel document on Mac. This is the resulted file.
I am utterly baffled. @jaapdh I know you just copied and pasted my code above. Nevertheless, what I supplied is missing a bit at the beginning and the end. Can you please upload your complete code so that I can check why you're getting a different result than I. Also, although I can't imagine why it would matter, are you running it on a Mac?
@oleibman i had to extract it from my own application. But this is the code i have in a dev-controller which runs stand alone.
I'm working on a Mac but inside a Docker container based on Ubuntu, so that can not be the difference. I am opening the spreadsheet with my mac-based excel, could that be the issue?
$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();
$worksheet->fromArray(
[
['', 2010, 2011, 2012],
['Q1', 12, 15, 21],
['Q2', 56, 73, 86],
['Q3', 52, 61, 69],
['Q4', 30, 32, 0],
]
);
$categorieCoOrdinate = 'Worksheet!$A$2:$A$5';
$labelCell = '$B$6';
$labelCellRelative = 'B6';
$worksheet->getCell($labelCellRelative)->setValue('ChartLabel');
// Custom colors for dataSeries (greenish, yellowish, red, gray)
$colors = [
'70ad47',
'ffc000',
'ff0000',
'757575',
];
$label = [
new DataSeriesValues(
DataSeriesValues::DATASERIES_TYPE_STRING,
$labelCell,
null,
1,
[],
null,
$colors
),
];
$categories = [
new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, $categorieCoOrdinate, null, 4),
];
$values = [
new DataSeriesValues(
DataSeriesValues::DATASERIES_TYPE_NUMBER, // dataType
'Worksheet!$C$2:$C$5', // dataSource
null, // formatCode
4, // pointCount
[], // dataValues
null, // marker
$colors // fillColor
)
];
// Build the dataseries
$series1 = new DataSeries(
DataSeries::TYPE_PIECHART, // plotType
null, // plotGrouping (Pie charts don't have any grouping)
range(0, count($values) - 1), // plotOrder
$label, // plotLabel
$categories, // plotCategory
$values // plotValues
);
$plotArea1 = new PlotArea(null, [$series1]);
$legend1 = new Legend(Legend::POSITION_BOTTOM, null, false);
$title1 = new Title('Test Pie Chart');
// Create the chart
$chart1 = new Chart(
'chart1', // name
$title1, // title
$legend1, // legend
$plotArea1, // plotArea
true, // plotVisibleOnly
DataSeries::EMPTY_AS_GAP, // displayBlanksAs
null, // xAxisLabel
null // no Y-Axis for Pie Chart
);
// Set the position where the chart should appear in the worksheet
$chart1->setTopLeftPosition('A7');
$chart1->setBottomRightPosition('H20');
// Add the chart to the worksheet
$worksheet->addChart($chart1);
$properties = $spreadsheet->getProperties();
$document_title = $properties->getTitle();
// open the document on the first sheet
$spreadsheet->setActiveSheetIndex(0);
// force download
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=utf-8');
header('Content-Disposition: attachment; filename="' . $document_title . '.xlsx"');
// needed for IE9
header('Cache-Control: max-age=1');
// needed for IE + SSL
$now = gmdate(DATE_RFC1123, time());
$past = gmdate(DATE_RFC1123, (time() - 60 * 60 * 24 * 365));
header('Expires: ' . $past); // never
header('Last-Modified: ' . $now); // always
header('Cache-Control: cache, must-revalidate');
header('Pragma: public');
// write to browser
$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
// duh
$writer->setIncludeCharts(true);
$writer->setPreCalculateFormulas(false);
$writer->save('php://output');
die;
The issue is not with Mac-based Excel. The spreadsheet you uploaded does not have the color information in it. It looks the same in Windows as it does in your Mac screenshot.
The moment I remove $writer->setPreCalculateFormulas(false);
from the code example I do get the colors from the PieChart. So the problem is in there. @Raman117 do you have something similar in your code?
@jaapdh Thank you for supplying the complete code. This line is the problem:
$writer->setPreCalculateFormulas(false);
I'm not entirely sure why, but, when I comment it out, I get the correct colors. Can you please try that and report back?
@Raman117 are you also using the line in question in your script?
When preCalculateFormulas is off, PhpSpreadsheet does not perform $chart->refresh()
when writing the chart out. If it is important to you to that preCalculateFormulas be off, you can manually call refresh
for all your charts before writing.
@oleibman the example works indeed when removing the line for setPreCalculateFormulas
. The refresh or remove of that line breaks my "real" code because the code is a little bit different.
My values come from different locations in the worksheet. Thus I use an implode to get those locations in the dataSource string. When I do a $chart->refresh()
or remove the PreCalculateFormulas I get a formulaException.
I tried the implode both with an ;
and a ,
, but in both cases I get different exceptions. Below the example altered so that you can run it to see it happening.
Maybe you can find what is going wrong here.
$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();
$worksheet->fromArray(
[
['', 2010, 2011, 2012],
['Q1', 12, 15, 21],
['Q2', 56, 73, 86],
['Q3', 52, 61, 69],
['Q4', 30, 32, 0],
]
);
$categorieCoOrdinate = 'Worksheet!$A$2:$A$5';
$labelCell = '$B$6';
$labelCellRelative = 'B6';
$worksheet->getCell($labelCellRelative)->setValue('ChartLabel');
// Custom colors for dataSeries (greenish, yellowish, red, gray)
$colors = [
'70ad47',
'ffc000',
'ff0000',
'757575',
];
$label = [
new DataSeriesValues(
DataSeriesValues::DATASERIES_TYPE_STRING,
$labelCell,
null,
1,
[],
null,
$colors
),
];
$categories = [
new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, $categorieCoOrdinate, null, 4),
];
// CHANGED CODE HERE
$dataset = [
'Worksheet!$C$2',
'Worksheet!$C$3',
'Worksheet!$C$4',
'Worksheet!$C$5',
];
$values = [
new DataSeriesValues(
DataSeriesValues::DATASERIES_TYPE_NUMBER, // dataType
implode(';', $dataset), // dataSource
null, // formatCode
4, // pointCount
[], // dataValues
null, // marker
$colors // fillColor
)
];
// Build the dataseries
$series1 = new DataSeries(
DataSeries::TYPE_PIECHART, // plotType
null, // plotGrouping (Pie charts don't have any grouping)
range(0, count($values) - 1), // plotOrder
$label, // plotLabel
$categories, // plotCategory
$values // plotValues
);
$plotArea1 = new PlotArea(null, [$series1]);
$legend1 = new Legend(Legend::POSITION_BOTTOM, null, false);
$title1 = new Title('Test Pie Chart');
// Create the chart
$chart1 = new Chart(
'chart1', // name
$title1, // title
$legend1, // legend
$plotArea1, // plotArea
true, // plotVisibleOnly
DataSeries::EMPTY_AS_GAP, // displayBlanksAs
null, // xAxisLabel
null // no Y-Axis for Pie Chart
);
// Set the position where the chart should appear in the worksheet
$chart1->setTopLeftPosition('A7');
$chart1->setBottomRightPosition('H20');
// Add the chart to the worksheet
$worksheet->addChart($chart1);
$properties = $spreadsheet->getProperties();
$document_title = $properties->getTitle();
// open the document on the first sheet
$spreadsheet->setActiveSheetIndex(0);
// force download
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=utf-8');
header('Content-Disposition: attachment; filename="' . $document_title . '.xlsx"');
// needed for IE9
header('Cache-Control: max-age=1');
// needed for IE + SSL
$now = gmdate(DATE_RFC1123, time());
$past = gmdate(DATE_RFC1123, (time() - 60 * 60 * 24 * 365));
header('Expires: ' . $past); // never
header('Last-Modified: ' . $now); // always
header('Cache-Control: cache, must-revalidate');
header('Pragma: public');
// write to browser
$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
// duh
$writer->setIncludeCharts(true);
$writer->save('php://output');
die;
@jaapdh You need to supply the dataSource as an Excel array rather than just a semicolon-separated list. Try:
'{' . implode(';', $dataset) . '}'
If that doesn't work, try ','
, but I think either comma or semicolon will work in this case.
@oleibman That is the solution! I tried it myself first with '('.implode.')'
, but that did not work. Thank you very much!!
Still it seems weird that disabling the calculation also removes the colors from the charts.
Agreed that it's weird, but that decision was made long ago, presumably for good enough reasons at the time. Since we now have a solution to your problem, I don't see any need to re-evaluate that decision.
I tried this out and even copied the code, but it didn't work with the implode and stuff I had WithPreCalculateFormulas implemented I removed this but there is still the issue
this is laravel excel but it should work with phpSpreadsheet
any idea why?
and when i add , in implode i get the data but color is still wrong
i added the sudo file here bc the code wasn't pasting well
@Raman117 I don't know. The text file you supplied supplies a PHP array, rather than an Excel array, as data source. That won't work. You say you tried implode to make it into a string. That's something you need to do. But you don't include the code that actually writes out the spreadsheet file. If you use preCalculate false when writing, that's the explanation. If you don't specify a value for preCalculate, try setting it to true and see what happens. If you use preCalculate true and it still doesn't work, I don't know what else to suggest.
@jaapdh Can you send the file with file name and imp stuff removed
okey so update i copied the code @oleibman added and it is working in normal Spreadsheet format
but its not when done in laravel excel withCharts charts()
any guess?
or better @oleibman can you tell me where the default color is comming from i am thinking just change it there
The default color is coming from Excel itself.
Have you tried opening an issue with Laravel?
this is what they said
Have you asked them if they set preCalculateFormulas to false?
I just downloaded the current version of Laravel-Excel. It comes with a config file (config/excel.php) and look what's there:
'pre_calculate_formulas' => false,
Try changing that value and see what happens. There's probably a way to do it without changing the config file, but I don't know what that would be. For now, just change the config file and report back.
I'm just guessing, but maybe you don't need to alter your config file if you add WithPreCalculateFormulas
here:
class file implements
WithTitle,
WithEvents,
WithCharts
Plus, among your use statements:
use Maatwebsite\Excel\Concerns\WithPreCalculateFormulas;
Yes, it works now
Thank you for taking the time to help me
I really appreciate the help
@oleibman @jaapdh
Glad we were able to work it out. Closing the issue now.
@oleibman I question the legends show up in the wps but not in excel any reason why it comes as 1,2,3,4
can you please help with this,
public function charts() { $chartArray = [];
$valueCoOrdinates = [
'$C$7:$C$10',
'$C$27:$C$30',
'$C$47:$C$50',
'$C$67:$C$70',
'$C$87:$C$90',
'$C$107:$C$110',
];
$categorieCoOrdinate = '$B$7:$B$10';
$chartPositions = [
['topLeft' => 'E5', 'bottomRight' => 'L18'],
['topLeft' => 'E25', 'bottomRight' => 'L38'],
['topLeft' => 'E45', 'bottomRight' => 'L58'],
['topLeft' => 'E65', 'bottomRight' => 'L78'],
['topLeft' => 'E85', 'bottomRight' => 'L98'],
['topLeft' => 'E105', 'bottomRight' => 'L118'],
];
$colors = [
'70ad47',
'ffc000',
'ff0000',
'757575',
];
$labels = [
'$B$6',
'$B$26',
'$B$46',
'$B$66',
'$B$86',
'$B$106'
];
for ($index = 0; $index < 6; $index++) {
$label = [
new DataSeriesValues(
DataSeriesValues::DATASERIES_TYPE_STRING,
$labels[$index],
null,
1,
[],
null,
$colors
),
];
$categories = [new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, $categorieCoOrdinate, null, 4)];
$values = [
new DataSeriesValues(
DataSeriesValues::DATASERIES_TYPE_NUMBER,
$valueCoOrdinates[$index],
null,
4,
[],
null,
$colors
)
];
$series = new DataSeries(
DataSeries::TYPE_PIECHART,
null,
range(0, count($values) - 1),
$label,
$categories,
$values,
);
$plot = new PlotArea(null, [$series]);
$legend = new Legend(Legend::POSITION_BOTTOM, null, false);
$chart = new Chart(
$this->chartsTitles[$index],
new Title($this->chartsTitles[$index]),
$legend,
$plot,
true,
DataSeries::EMPTY_AS_GAP,
null,
null
);
$chart->setTopLeftPosition($chartPositions[$index]['topLeft']);
$chart->setBottomRightPosition($chartPositions[$index]['bottomRight']);
$chartArray[] = $chart;
}
return [$chartArray[0], $chartArray[1], $chartArray[2], $chartArray[3], $chartArray[4], $chartArray[5]];
}
I don't understand your question. In the picture which you most recently uploaded, there is definitely a legend for your chart, and it doesn't consist of 1/2/3/4. Also, I'm not sure what you mean by 'wps'.
sorry about that forgot to add this
WPS is an excel opening tool but in excel i see 1,2,3,4 in the legend
WPS shows implemented, not implemented, etc
Can you upload a spreadsheet which shows this problem?
NCA_ECC-1-2018-Assessment-and-Compliance-Tool 1 22-07-2024 2.xlsx
here is the file
My first comment is that the spreadsheet you uploaded was not created by PhpSpreadsheet (perhaps you took our output, opened it in Excel or some other program, and saved the result). I can tell because many of the tags in the chart xml are not tags that we generate, e.g. filteredSeriesTitle. Nevertheless, things appear to be set as they should in the Xml, so you might have this problem even with the spreadsheet as generated by PhpSpreadsheet. Chances are that you will have to talk to Microsoft to figure out what's happening. However, I do have a workaround for you if that's of interest ...
Select the legend and get a context menu. I do that with a right-click; I don't know if it's different for right-to-left usage.
From the context menu, choose "Select Data" and you'll see:
That's where 1, 2, 3, 4 are coming from ...
Hit that "Edit" button and you'll see:
It looks like the correct range, which is why wps can handle it. But not the correct values? The mystery is what Excel is up to here.
Now, in that Axis label range textbox, replace the 7 with an 8 and hit okay. You should now see labels (probably the wrong ones) in place of 1, 2, and 3, and nothing where 4 was. So repeat the exercise, changing the 8 back to 7. Your legend should now have the correct labels.
Just for the record, LibreOffice and Google Sheets have no problem with the legend with my sample, but, for your file, LibreOffice shows 1/2/3/4, and Google Sheets doesn't show any text for the Legend. And they both get the legend correct when I do the editing steps I described above.
is there no way to do this from phpspreadsheet itself? this report is to be downloaded from client side so we cant edit the excel
As I said, the workbook you uploaded was not generated by PhpSpreadsheet. If you upload a worksheet that was generated by us, I will take a look. Regardless, the problem may lie in Excel.
This is: