Valid directory to TrueType Font files not specified

[marcinrosinski]

Hi,

Looks like 1.8.1 update introduced some bug which now throws the following Exception (all works fine under 1.8.0, no exception is thrown in there):

Uncaught exception 'PHPExcel_Exception' with message 'Valid directory to TrueType Font files not specified' in MY_PATH/vendor/phpoffice/phpexcel/Classes/PHPExcel/Shared/Font.php:426

Yes, I can catch that exception but why there is no problem with it under 1.8.0 only in 1.8.1, looks to me like a bug.

[FHenry]

Affect me also
PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);
foreach ( $column_array as $columnID ) { $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
}

[zackwhedbee]

I also had to go back to 1.8.0 because of this

[maxiwheat]

I have the same issue when I try to use PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);

[Easthy]

version 1.8.0 has this error too.
PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+1)->setAutoSize(true);

[Sun Nov 22 20:18:40 2015] [error] [client 172.16.0.50] PHP PHPExcel_Exception: Valid directory to TrueType Font files not specified in /srv/www/test/app/webroot/PHPExcelClasses/PHPExcel/Shared/Font.php on line 428,i$

PRETTY_NAME="Debian GNU/Linux 7 (wheezy)"
NAME="Debian GNU/Linux"
VERSION_ID="7"
VERSION="7 (wheezy)"
ID=debian

php --version
PHP 5.4.45-0+deb7u2 (cli) (built: Oct 17 2015 08:26:31)
Copyright (c) 1997-2014 The PHP Group
Zend Engine v2.4.0, Copyright (c) 1998-2014 Zend Technologies
with Xdebug v2.2.1, Copyright (c) 2002-2012, by Derick Rethans

But autoSize perfectly works on kubuntu 14.04 LTS

[christhomas]

I'm having this exact problem now, I'm going to try a previous version

[christhomas]

downgrading to 1.8.0 also solved the problem for me as well, I updated composer to use

"phpoffice/phpexcel": "1.8.0"

and the problem disappeared and files are rendering again

[MarkBaker]

OK! I get the picture! I've managed to introduce a bug!

While you're all busy downgrading, has anybody actually tried to figure out the actual problem?

[sharpensteel]

hi guys!
i debugged a bit,
there is exception at PHPExcel/Shared/Font.php, in line 272:

$columnWidthAdjust = ceil(self::getTextWidthPixelsExact('n', $font, 0) * 1.07);

in old versions this line was inside try/catch block, so exception was suppressed.
exception:

if (!file_exists(self::$trueTypeFontPath) || !is_dir(self::$trueTypeFontPath)) {
    throw new PHPExcel_Exception('Valid directory to TrueType Font files not specified');
}

the deep reason is that PHPExcel_Shared_Font::$trueTypeFontPath was newer set, PHPExcel_Shared_Font::setTrueTypeFontPath() never called

[christhomas]

is this fix being merged anytime soon? I don't want to create a branch just to have this, but there is a bug in 1.8.0 because of issue: #434

[Pacoup]

2016-05-05
Updated with strategies for using open fonts to calculate sizes and corrections about how fonts get rendered on different platforms (you can use Linux to reliably calculate column sizes for Windows), plus notes on uses of try catch blocks.

---

This is not a bug. In order to calculate exact font widths, PHPExcel actually needs fonts to work with.

Before using setAutoSizeMethod(), you first need to call setTrueTypeFontPath():

PHPExcel_Shared_Font::setTrueTypeFontPath('C:/Windows/Fonts/');
PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);

foreach (range('A','L') as $column) {
  $objPHPExcel->getActiveSheet()->getColumnDimension($column)->setAutoSize(TRUE);
}

• You can use forward slashes, Windows doesn't care
• Don't forget the last slash, PHPExcel does not automatically append it to the final path

However, this is OS-specific, so you may also want to test for Linux, Windows, OS X, and so on:

switch (PHP_OS) {
  case 'WINNT':
    PHPExcel_Shared_Font::setTrueTypeFontPath('C:/Windows/Fonts/');
    PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);
    break;

  case 'Darwin':
    PHPExcel_Shared_Font::setTrueTypeFontPath('/Library/Fonts/');
    PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);
    break;

  case 'Linux':
    PHPExcel_Shared_Font::setTrueTypeFontPath('/usr/share/fonts/truetype/');
    PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);
    break;
}

foreach (range('A','L') as $column) {
  $objPHPExcel->getActiveSheet()->getColumnDimension($column)->setAutoSize(TRUE);
}

But, this will not work, because:

• Font names are different from platform to platform
• PHPExcel expects Windows font names
• PHPExcel expects fonts to be located directly in the specified directory, not in subfolders, as is the case on Linux
• Typical Linux servers won't have fonts like Arial installed

Therefore, a better strategy would be to set AUTOSIZE_METHOD_EXACT only on Windows (assuming your production machine runs it):

if (PHP_OS == 'WINNT') {
  PHPExcel_Shared_Font::setTrueTypeFontPath('C:\Windows\Fonts');
  PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);
}

foreach (range('A','L') as $column) {
  $objPHPExcel->getActiveSheet()->getColumnDimension($column)->setAutoSize(TRUE);
}

Also note that PHPExcel supports a limited set of fonts for this method:

• Arial
• Calibri
• Courier New
• Comic Sans MS
• Georgia
• Impact
• Liberation Sans
• Lucida Console
• Lucida Sans Unicode
• Microsoft Sans Serif
• Palatino Linotype
• Symbol
• Tahoma
• Times New Roman
• Trebuchet MS
• Verdana

If you try to use an unsupported font like Arial Narrow, the method will fail:

Unknown font name "Arial Narrow". Cannot map to TrueType font file - /PHPExcel/Classes/PHPExcel/Shared/Font.php:531

The method will also fail if the font can't be found on the server. Not all fonts above come with Windows Server.

You can add new fonts by patching the PHPExcel code:

diff --git a/PHPExcel/Classes/PHPExcel/Shared/Font.php b/PHPExcel/Classes/PHPExcel/Shared/Font.php
index 8e5b27b..47b9eb6 100644
--- a/PHPExcel/Classes/PHPExcel/Shared/Font.php
+++ b/PHPExcel/Classes/PHPExcel/Shared/Font.php
@@ -72,6 +72,11 @@ class PHPExcel_Shared_Font
        const ARIAL_ITALIC                              = 'ariali.ttf';
        const ARIAL_BOLD_ITALIC                         = 'arialbi.ttf';

+       const ARIAL_NARROW                              = 'ARIALN.TTF';
+       const ARIAL_NARROW_BOLD                         = 'ARIALNB.TTF';
+       const ARIAL_NARROW_ITALIC                       = 'ARIALNI.TTF';
+       const ARIAL_NARROW_BOLD_ITALIC                  = 'ARIALNBI.TTF';
+
        const CALIBRI                                   = 'CALIBRI.TTF';
        const CALIBRI_BOLD                              = 'CALIBRIB.TTF';
        const CALIBRI_ITALIC                            = 'CALIBRII.TTF';
@@ -439,6 +444,13 @@ class PHPExcel_Shared_Font
                                );
                                break;

+                       case 'Arial Narrow':
+                               $fontFile = (
+                                       $bold ? ($italic ? self::ARIAL_NARROW_BOLD_ITALIC : self::ARIAL_NARROW_BOLD)
+                                                 : ($italic ? self::ARIAL_NARROW_ITALIC : self::ARIAL_NARROW)
+                               );
+                               break;
+
                        case 'Calibri':
                                $fontFile = (
                                        $bold ? ($italic ? self::CALIBRI_BOLD_ITALIC : self::CALIBRI_BOLD)

In order to make this platform-independent, you would need to include the font with your application, for example, in a local directory:

PHPExcel_Shared_Font::setTrueTypeFontPath('fonts');
PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);

However, this approach would require licensing fonts like Arial for redistribution. To circumvent this, you can use open clone fonts and rename them to the expected Windows file name of their equivalent. Liberation Sans, for instance, is a metrically compatible alternative to Arial, so the resulting autosizing should be the same.

It's also impossible to embed fonts in Excel documents, so open fonts are really only an option if you're using them for their metrical compatibility.

Note that even though this functionality relies on GD's imagettfbbox function, which means font sizes will be calculated the same regardless of platforms, not all platforms render fonts the same. As an example, I have been able to reliably generate Excel documents on Linux which render correctly on both Windows and Android, but the sizing is all wrong on OS X. I'm guessing iOS suffers from the same issues (but I have no device to test this on), but this may be a problem with Microsoft's implementation of font rendering in Excel on these platforms, not the platforms themselves. This is also not a problem with the font itself. I have tried replacing OS X's Arial font with a copy from Windows, but to no avail, the font still renders wrong.

What about previous versions of PHPExcel?

Before 1.8.1, if you hadn't run setTrueTypeFontPath() before setting setAutoSizeMethod() to AUTOSIZE_METHOD_EXACT, the getTextWidthPixelsExact() method in Font.php would simply fail silently and setAutoSize() would apply approximate widths, as explained by @sharpensteel.

This is because the code for PHPExcel_Shared_Font::calculateColumnWidth(), a method called when calculating automatic column widths, looked like this:

// Try to get the exact text width in pixels
try {
  // If autosize method is set to 'approx', use approximation
  if (self::$autoSizeMethod == self::AUTOSIZE_METHOD_APPROX) {
    throw new PHPExcel_Exception('AutoSize method is set to approx');
  }

  // Width of text in pixels excl. padding
  $columnWidth = self::getTextWidthPixelsExact($cellText, $font, $rotation);

  // Excel adds some padding, use 1.07 of the width of an 'n' glyph
  $columnWidth += ceil(self::getTextWidthPixelsExact('0', $font, 0) * 1.07); // pixels incl. padding

} catch (PHPExcel_Exception $e) {
  // Width of text in pixels excl. padding, approximation
  $columnWidth = self::getTextWidthPixelsApprox($cellText, $font, $rotation);

  // Excel adds some padding, just use approx width of 'n' glyph
  $columnWidth += self::getTextWidthPixelsApprox('n', $font, 0);
}

Assuming we didn't run setTrueTypeFontPath(), the next method, getTextWidthPixelsExact(), would call getTrueTypeFontFileFromFont() which itself would throw, but because getTextWidthPixelsExact() is called from within a try block, PHP would catch the throw and the function would go on with getTextWidthPixelsApprox() in the catch block.

So even though you didn't get any errors, it didn't actually work and the column sizes would be approximate, which probably explains #853.

In 1.8.1, the code now looks like this:

// Try to get the exact text width in pixels
$approximate = self::$autoSizeMethod == self::AUTOSIZE_METHOD_APPROX;
if (!$approximate) {
  $columnWidthAdjust = ceil(self::getTextWidthPixelsExact('n', $font, 0) * 1.07);
  try {
    // Width of text in pixels excl. padding
    // and addition because Excel adds some padding, just use approx width of 'n' glyph
    $columnWidth = self::getTextWidthPixelsExact($cellText, $font, $rotation) + $columnWidthAdjust;
  } catch (PHPExcel_Exception $e) {
    $approximate = true;
  }
}

if ($approximate) {
  $columnWidthAdjust = self::getTextWidthPixelsApprox('n', $font, 0);
  // Width of text in pixels excl. padding, approximation
  // and addition because Excel adds some padding, just use approx width of 'n' glyph
  $columnWidth = self::getTextWidthPixelsApprox($cellText, $font, $rotation) + $columnWidthAdjust;
}

As you can see, the first call to getTextWidthPixelsExact() is not within a try block, so when getTextWidthPixelsExact() calls getTrueTypeFontFileFromFont(), the throw properly* halts execution and outputs an error.

* Technically, this seems to have been "fixed" inadvertently: ddec5e9
Besides, try catch blocks should not be used as conditionals because throw is dynamically scoped. Anything which throws within the execution context of a try catch block will be caught by the catch block, so if you don't handle it, the error gets lost. It's called catch because you're supposed to catch exceptions and do something with them. That $e parameter isn't allocated for nothing. But that's a note for @MarkBaker

[andrew-pause]

This appears to still be broken, at least in the tagged release for 1.8.1. I just attempted to upgrade with composer and was getting this error.

[Pacoup]

Although all new development has moved to a new project (https://github.com/PHPOffice/PhpSpreadsheet), this is not a bug. If you're getting the error, your client code isn't using the API properly.

Yes, the error reporting to could be better. Yes, the API could assume some system defaults. But the point is, if you're not setting the font path, even if you had no error before, setAutoSizeMethod() never actually worked.

You can upgrade through Composer and fix the error by calling setTrueTypeFontPath() before you call setAutoSizeMethod().

Here's an excerpt from a Drupal module I work on:

$objPHPExcel = new PHPExcel();
$active_sheet = $objPHPExcel->getActiveSheet();

// ...

PHPExcel_Shared_Font::setTrueTypeFontPath(DRUPAL_ROOT . '/' . drupal_get_path('module', 'learning_app') . '/fonts/');
PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);

foreach (range('A','O') as $column) {
  $active_sheet->getColumnDimension($column)->setAutoSize(TRUE);
}

There's more details in the first part of my comment above.

[PowerKiKi]

Thanks @Pacoup for your answer.