3 votes

Modifying the font size (or style) of a chart title with PHPExcel or PHPSpreadsheet

When creating an excel file with PHPExcel or PHPspreadSheet, I want to change the font size (or style) of the title of a chart. How can I do this?

introducir la descripción de la imagen aquí

Attached is my code. The code is correct, it does not generate any errors and the graphics print correctly.

$title= new \PhpOffice\PhpSpreadsheet\Chart\Title("titulo", null);
$title->getFont()->setSize(10);

$chart = new \PhpOffice\PhpSpreadsheet\Chart(
    'chart1',   // name
    $title,       // title
    $legend,       // legend
    $plotArea,  // plotArea
    true,       // plotVisibleOnly
    0,          // displayBlanksAs
    $xAxisLabel,       // xAxisLabel
    $yAxisLabel        // yAxisLabel
);

Title Class:

<?php

namespace PhpOffice\PhpSpreadsheet\Chart;
use PhpOffice\PhpSpreadsheet\Style\Font;

class Title
{
    /**
     * Title Caption.
     *
     * @var string
     */
    private $caption;

    /**
     * Title Layout.
     *
     * @var Layout
     */
    private $layout;

     /**
     * Title Font
     *
     * @var Font
     */
    private $font;

    /**
     * Create a new Title.
     *
     * @param null|mixed $caption
     * @param null|Layout $layout
    */
    public function __construct($caption = null, Layout $layout = null)
    {
        $this->caption = $caption;
        $this->layout = $layout;
        $this->font = new PhpOffice\PhpSpreadsheet\Style\Font();
    }

    /**
     * Get caption.
     *
     * @return string
     */
    public function getCaption()
    {
       return $this->caption;
    }

    /**
     * Set caption.
     *
     * @param string $caption
     *
     * @return Title
     */
    public function setCaption($caption)
    {
       $this->caption = $caption;

       return $this;
    }

    /**
     * Get Layout.
     *
     * @return Layout
     */
    public function getLayout()
    {
        return $this->layout;
    }

    /**
     * Get font
     *
     * @return Font
     */
    public function getFont() {
        return $this->font;
    }
    /**
     * Set font
     *
     * @param Font $font
     * @return Title
     */
    public function setFont( PhpOffice\PhpSpreadsheet\Style\Font $font = null)  {
        $this->font = $font;
        return $this;
    }
}

I found this post: https://github.com/PHPOffice/PHPExcel/pull/688 Following the indications of the post I have modified the class chart.php the class StringTable.php and the class Title .

Classes/PhpSpreadsheet/Writer/Xlsx/Chart.php

private function writeTitle(Title $title = null, $objWriter)
    if ((is_array($caption)) && (count($caption) > 0)) {
        $caption = $caption[0];
    }
    //$this->getParentWriter()->getWriterPart('stringtable')->writeRichTextForCharts($objWriter, $caption, 'a');
    $pRichText =new \PhpOffice\PhpSpreadsheet\RichText\RichText();
    $pRichText->createTextRun($caption);
    $elements = $pRichText->getRichTextElements();
    foreach ($elements as $element) {
        $element->setFont($title->getFont());
    }
    $this->getParentWriter()->getWriterPart('stringtable')->writeRichTextForCharts($objWriter, $pRichText, 'a');
    $objWriter->endElement();
    $objWriter->endElement();

In the StringTable class, I have added the lines of code necessary to modify the font size of the chart title, since there was no line related to the font size. So that there are no errors, I have put directly by hand that it has a size of 10pt ($objWriter->writeAttribute('val', 10))

Classes/PhpSpreadsheet/Writer/Xlsx/StringTable.php

 /**
 * Write Rich Text.
 *
 * @param XMLWriter $objWriter XML Writer
 * @param RichText|string $pRichText text string or Rich text
 * @param string $prefix Optional Namespace prefix
 */
public function writeRichTextForCharts(XMLWriter $objWriter, $pRichText = null, $prefix = null)
       //size Font
        $objWriter->startElement($prefix . 'sz');
        $objWriter->writeAttribute('val', 10);
        $objWriter->endElement();

By default, the font size of the chart titles is 18pt. I want them to be 10pt.

0 votes

Since I don't know PHP, a quick search led me to this one reply . If this answer solves your problem, vote yes to the answer, otherwise, you can search on Google using the term phpexcel change font size . Greetings.

0 votes

Thanks for the input but I need to modify the chart title styles, not the cell styles. Regarding the search you mentioned, it is too ambiguous.

0 votes

Ivan.depi have you consulted the phpspreadsheet documentation?

0voto

ivan.depi Points 349

I have managed to solve it. To do this I had to modify classes of the Phpspreadsheet library. In the code I added when I asked the question, I had to modify the StringTable.php class.

Attached is the correct code for this class.

Classes/PhpSpreadsheet/Writer/Xlsx/StringTable.php

 /**
 * Write Rich Text.
 *
 * @param XMLWriter $objWriter XML Writer
 * @param RichText|string $pRichText text string or Rich text
 * @param string $prefix Optional Namespace prefix
 */
 public function writeRichTextForCharts(XMLWriter $objWriter, $pRichText = null, $prefix = null)
       //size Font
       $objWriter->writeAttribute('sz', $element->getFont()->getSize() * 100 );

0 votes

I think you could raise it with the creators of the project as a feature request The project will be added in a future update of the project so that you do not have to modify the files manually.

0 votes

I agree, besides, I'm going to upload it to github too.

-1voto

A. Cedano Points 48884

To change the font size, as explained by the PHPSpreadSheet documentation you can do it directly in the cell where the title ( A1 in this case).

The process would be as follows:

  • Obtain the active sheet with getActiveSheet()
  • Get the style of the cell (or range of cells) with getStyle()
  • Get the source of that style with getFont()
  • Finally, set the size of the font already obtained with setSize

Following the above steps, the code would look like this:

$spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setSize(10);

Here $spreadsheet represents the reference to the SpreadSheet object that has been created.

The documentation also explains that in getStyle you can include a range of cells, for example: getStyle('A1:A10')


Another way to do this would be to apply a multi-component style by means of applyFromArray . This would work like CSS and is recommended in situations where multiple styles need to be applied (it would be like having CSS classes).

For example here we apply a large font size and bold:

$styleTitle = [
    'font' => [
        'bold' => true,
        'size' => 20
    ]
];

$spreadsheet->getActiveSheet()->getStyle('A1')->applyFromArray($styleTitle);

This would be a simplified style. More complex styles can be created for all possible elements (not only the font), but also the borders, background colors, etc. And, they can also be applied to ranges of cells.


Recommendations

Finally, I recommend not to give up PHPSpreadSheet in favor of PHPExcel as the latter is an obsolete library, which has not been maintained for several years. maintained for some years now. When looking for tutorials or or even questions here or in the English counterpart, you will find thousands of questions/answers based on questions/answers based on PHPExcel because this was for many years the reference for many years the reference library. However nowadays its use is not is not recommended because of its obsolescence 1 .

The reading of Exploring PhpSpreadsheet's Formatting Capabilities may be of great help in this case.


Notes:

  1. At this answer I have translated the note reporting on this matter, referring to the original link.

0 votes

You are putting the code to change the styles of a cell, instead of putting the code to change the styles of the title of a chart. Please see phpoffice.github.io/PhpSpreadsheet/1.2.1/PhpOffice/

0 votes

Hello! I think Ivan is not referring to change the style of a cell, but rather to change the style of a title within a chart. Best regards.

-2voto

Juan Carlos Hdz Points 931

uses the setSize() function to apply the font size.

Here is an example:

$this->font
        ->setName('Arial')
        ->setSize(15);

0 votes

It doesn't work. I already tried this: $title->getFont()->setSize(10);

HolaDevs.com

HolaDevs is an online community of programmers and software lovers.
You can check other people responses or create a new question if you don't find a solution

Powered by:

X