Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

piechart #2077

Closed
SwankyTigerYY opened this issue May 8, 2021 · 5 comments · Fixed by #2930
Closed

piechart #2077

SwankyTigerYY opened this issue May 8, 2021 · 5 comments · Fixed by #2930
Labels

Comments

@SwankyTigerYY
Copy link

SwankyTigerYY commented May 8, 2021

This is:

- [ ] a feature request

What is the expected behavior?

image

What is the current behavior?

image

What are the steps to reproduce?

//How to use the third parameter,What does it affect.
$dataSeriesValues = [
            new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$C$' . $row_start . ':$C$' . $row, null, count($pie_data)),
        ];

Which versions of PhpSpreadsheet and PHP are affected?

"phpoffice/phpspreadsheet": "^1.17.1",
“php”: "7.2.10"

@MarkBaker
Copy link
Member

The third argument is an MS Excel format mask to use when displaying the values. If you set your mask to 0.00 it will render the value with 2 decimal places

public function __construct(
    $dataType = self::DATASERIES_TYPE_NUMBER,
    $dataSource = null,
    $formatCode = null,
    $pointCount = 0,
    $dataValues = [],
    $marker = null,
    $fillColor = null
)

@SwankyTigerYY
Copy link
Author

SwankyTigerYY commented May 13, 2021

The third argument is an MS Excel format mask to use when displaying the values. If you set your mask to 0.00 it will render the value with 2 decimal places

public function __construct(
    $dataType = self::DATASERIES_TYPE_NUMBER,
    $dataSource = null,
    $formatCode = null,
    $pointCount = 0,
    $dataValues = [],
    $marker = null,
    $fillColor = null
)

Cannot achieve the image effect.
How do I set the number category as a percentage.

@MarkBaker
Copy link
Member

MarkBaker commented May 13, 2021

It might help if you showed the code that you're using, and then I might be able to see what you were doing incorrectly, or if there was a bug?

@SwankyTigerYY
Copy link
Author

SwankyTigerYY commented May 14, 2021

It might help if you showed the code that you're using, and then I might be able to see what you were doing incorrectly, or if there was a bug?

For Example

        error_reporting(E_ALL);
        ini_set('display_errors', TRUE);
        ini_set('display_startup_errors', TRUE);

        $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, 60],
            ]
        );

        // Set the Labels for each data series we want to plot
        $dataSeriesLabels1 = [
            new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$B$1', null, 1), // 2011
            new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$C$1', null, 1), // 2012
            new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$D$1', null, 1), // 2013
        ];

        // Set the X-Axis Labels
        $xAxisTickValues1 = [
            new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$A$2:$A$5', null, 4), // Q1 to Q4
        ];

        // Set the Data values for each data series we want to plot
        // TODO  I think the third parameter can be set,but I didn't succeed
        $dataSeriesValues1 = [
            new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$B$2:$B$5', null, 4),
            new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$C$2:$C$5', NumberFormat::FORMAT_NUMBER_00, 4),
            new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$D$2:$D$5', NumberFormat::FORMAT_PERCENTAGE_00, 4),
        ];

        // Build the dataseries
        $series1 = [
            new DataSeries(
                DataSeries::TYPE_PIECHART, // plotType
                null, // plotGrouping (Pie charts don't have any grouping)
                range(0, count($dataSeriesValues1) - 1), // plotOrder
                $dataSeriesLabels1, // plotLabel
                $xAxisTickValues1, // plotCategory
                $dataSeriesValues1 // plotValues
            )
        ];

        // Set up a layout object for the Pie chart
        $layout1 = new Layout();
        $layout1->setShowVal(true);
        $layout1->setShowPercent(true);

        // Set the series in the plot area
        $plotArea1 = new PlotArea($layout1, $series1);

        // Set the chart legend
        $legend1 = new Legend(Legend::POSITION_RIGHT, null, false);

        $title1 = new Title('Test Pie Chart');

        $yAxisLabel = new Title('Value ($k)');
        // Create the chart
        $chart1 = new Chart(
            'chart1', // name
            $title1, // title
            $legend1, // legend
            $plotArea1, // plotArea
            true, // plotVisibleOnly
            'gap', // displayBlanksAs
            null, // xAxisLabel
//            null   // yAxisLabel - Pie charts don't have a Y-Axis
            $yAxisLabel
        );

        // 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);

        // Save Excel 2007 file
        $filename='pie';

        $fileType = 'Xlsx';
        $writer = IOFactory::createWriter($spreadsheet, $fileType);

        $writer->setIncludeCharts(true);

        if ($fileType == 'Excel2007' || $fileType == 'Xlsx') {
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename="' . $filename . '.xlsx"');
            header('Cache-Control: max-age=0');
        } else { //Excel5
            header('Content-Type: application/vnd.ms-excel');
            header('Content-Disposition: attachment;filename="' . $filename . '.xls"');
            header('Cache-Control: max-age=0');
        }
        //清空缓存区
        ob_end_clean();
        //输出到表格
        $writer->save('php://output');

        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        exit;

I hope

image

The actual

image

@SwankyTigerYY
Copy link
Author

Help me

oleibman added a commit to oleibman/PhpSpreadsheet that referenced this issue Jul 11, 2022
This was supposed to be mopping up some longstanding chart issues. But one of the sample files exposed a memory leak in Xlsx Writer, unrelated to charts. Since that is my best sample file for this problem, I would like to fix both problems at the same time.

Xlsx Writer for Worksheets calls getRowDimension for all rows on the sheet. As it happens, the sample file had data in the last rows after a huge gap of rows without any data. It correctly did not write anything for the unused rows. However, the call to getRowDimension actually creates a new RowDimension object if it doesn't already exist, and so it wound up creating over a million totally unneeded objects. This caused it to run out of memory when I tried to make a copy of the 8K input file. The logic is changed to call getRowDimension if and only if (there is data in the row or the RowDimension object already exists). It still has to loop through a million rows, but it no longer allocates the unneeded storage.

As for the Chart problems - fix PHPOffice#1797. This is where the file that caused the memory leak originated. Many of its problems were already resolved by the earlier large set of changes to Charts. However, there were a few new properties that needed to be added to Layout to make things complete - numberFormat code and source-linked, and dLblPos (position for labels); and autoTitleDeleted needs to be added to Charts.

Also fix PHPOffice#2077, by allowing the format to be specified in the Layout rather than the DataSeriesValues constructor.
oleibman added a commit that referenced this issue Jul 14, 2022
This was supposed to be mopping up some longstanding chart issues. But one of the sample files exposed a memory leak in Xlsx Writer, unrelated to charts. Since that is my best sample file for this problem, I would like to fix both problems at the same time.

Xlsx Writer for Worksheets calls getRowDimension for all rows on the sheet. As it happens, the sample file had data in the last rows after a huge gap of rows without any data. It correctly did not write anything for the unused rows. However, the call to getRowDimension actually creates a new RowDimension object if it doesn't already exist, and so it wound up creating over a million totally unneeded objects. This caused it to run out of memory when I tried to make a copy of the 8K input file. The logic is changed to call getRowDimension if and only if (there is data in the row or the RowDimension object already exists). It still has to loop through a million rows, but it no longer allocates the unneeded storage.

As for the Chart problems - fix #1797. This is where the file that caused the memory leak originated. Many of its problems were already resolved by the earlier large set of changes to Charts. However, there were a few new properties that needed to be added to Layout to make things complete - numberFormat code and source-linked, and dLblPos (position for labels); and autoTitleDeleted needs to be added to Charts.

Also fix #2077, by allowing the format to be specified in the Layout rather than the DataSeriesValues constructor.
MarkBaker added a commit that referenced this issue Jul 18, 2022
### Added

- Add Chart Axis Option textRotation [Issue #2705](#2705) [PR #2940](#2940)

### Changed

- Nothing

### Deprecated

- Nothing

### Removed

- Nothing

### Fixed

- Fix Encoding issue with Html reader (PHP 8.2 deprecation for mb_convert_encoding) [Issue #2942](#2942) [PR #2943](#2943)
- Additional Chart fixes
  - Pie chart with part separated unwantedly [Issue #2506](#2506) [PR #2928](#2928)
  - Chart styling is lost on simple load / save process [Issue #1797](#1797) [Issue #2077](#2077) [PR #2930](#2930)
  - Can't create contour chart (surface 2d) [Issue #2931](#2931) [PR #2933](#2933)
- VLOOKUP Breaks When Array Contains Null Cells [Issue #2934](#2934) [PR #2939](#2939)
@oleibman oleibman mentioned this issue Jul 22, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Development

Successfully merging a pull request may close this issue.

3 participants