Articles

Format Numbers and Dates in box/spout Excel Spreadsheets

I recently moved some of our PHP code from PhpSpreadsheet (phpoffice/phpspreadsheet) to Spout (box/spout) to address memory consumption issues while generating XLSX-formatted Spreadsheets. I have experience writing Excel Spreadsheets using several other PHP packages, so I was able to dive into the Spout source code and figure out how to access number and date formatting features.

Format Numbers

If you cast a PHP value to an (int) or a (float), Spout will save the value as a number, but your spreadsheet application will display an unformatted number. For example, the number “12,345,678.90” would be displayed in your spreadsheet application as “12345678.9”.

You can use setFormat() to format any (int) or (float) value. Find Excel number formatting examples here. See Sample Code below.

Format Dates

If you save a Date to Spout as a (string) value (e.g. “12/31/1999”), your spreadsheet application will display the pre-formatted Date, BUT the date cannot be re-formatted or used for calculations because it is being stored as a String value instead of a Date value.

You can use setFormat() to format a date, ONLY IF the date is passed as an Excel numeric (int) or (float) date value. Several PHP libraries can convert a PHP date to an Excel numeric date value. Since our project uses both Spout and PhpSpreadsheet, we use PhpSpreadsheet’s Date::PHPToExcel() to convert a Carbon value to an Excel numeric value. Find Excel date and time formatting examples here and here. See Sample Code below.

Sample Code

The following sample code produces this output in my spreadsheet application (Numbers):

php box/spout format numbers dates
Formatted Numbers and Dates from PHP box/spout
    use Carbon\Carbon;
    use PhpOffice\PhpSpreadsheet\Shared\Date;
    use Box\Spout\Writer\Common\Creator\Style\StyleBuilder;
    use Box\Spout\Writer\Common\Creator\WriterEntityFactory;

    // Open Spreadsheet
    $writer = WriterEntityFactory::createXLSXWriter();
    $writer->openToFile('example.xlsx');

    // Build Header Row
    $styleBold = (new StyleBuilder())->setFontBold()->build();
    $writer->addRow(
        WriterEntityFactory::createRow([
            WriterEntityFactory::createCell('Num1'),
            WriterEntityFactory::createCell('Num2'),
            WriterEntityFactory::createCell('Num3'),
            WriterEntityFactory::createCell('Date1'),
            WriterEntityFactory::createCell('Date2'),
        ], $styleBold)
    );

    // Define Number and Date Formats
    $styleNum1 = (new StyleBuilder())->setFormat('#,##0.00')->build();
    $styleNum2 = (new StyleBuilder())->setFormat('0.0')->build();
    $styleNum3 = (new StyleBuilder())->setFormat('00000')->build();
    $styleDate1 = (new StyleBuilder())->setFormat('mm/dd/yy')->build();
    $styleDate2 = (new StyleBuilder())->setFormat('mm/dd/yyyy')->build();

    // Define Number and Date Values
    $num1 = 12345678.9;
    $num2 = 1234;
    $date = Date::PHPToExcel(Carbon::parse('03/14/2000'));

    // Build Row of Formatted Numbers and Dates
    $writer->addRow(
        WriterEntityFactory::createRow([
            // Output 12,345,678.90
            WriterEntityFactory::createCell($num1, $styleNum1),
            // Output 1234.0
            WriterEntityFactory::createCell($num2, $styleNum2),
            // Output 01234
            WriterEntityFactory::createCell($num2, $styleNum3),
            // Output 03/14/00
            WriterEntityFactory::createCell($date, $styleDate1),
            // Output 03/14/2000
            WriterEntityFactory::createCell($date, $styleDate2),
        ])
    );

    // Close Spreadsheet
    $writer->close();

Bonus: Custom Column Widths

The box/spout project has an open Pull Request (PR#715) that adds Custom Column Width support. I forked the contributor’s repository (aphofstede/spout) to my own repo (e.g. “example/spout-custom-column-widths”) and added the following snippet to the “repositories” section of our composer.json file, which instructs composer to use the “custom-column-widths” branch in my forked copy of the repository.

When I run “composer require box/spout” in our project, composer uses the “custom-column-widths” branch from my forked copy of the contributor’s repository. Replace “example/spout-custom-column-widths” with the actual path to your forked copy of the contributor’s repository.

  {
      "type": "package",
      "package": {
          "name": "box/spout",
          "version": "3.2",
          "source": {
              "url": "https://github.com/example/spout-custom-column-widths",
              "type": "git",
              "reference": "custom-column-widths"
          },
          "type": "library",
          "autoload": {
              "psr-4": {
                  "Box\\Spout\\": "src/Spout"
              }
          }
      }
  }

Sample Code

You can use the setDefaultColumnWidth() method to set default column width for all of the worksheets in your spreadsheet.

TIP: Use method_exists() to make sure the custom method exists in case you need to temporarily deploy your project without the custom PR.

    // XLS Worksheet Default Column Width
    // NOTE: setDefaultColumnWidth() may only be called BEFORE openToFile()
    if (method_exists($writer, 'setDefaultColumnWidth')) {
        // NOTE: setDefaultColumnWidth() provided by PR#715
        $writer->setDefaultColumnWidth(12);
    }

You can use the setColumnWidth() method to set specific column widths for individual columns. In the example below, we set widths for Column A to Column H.

TIP: Use method_exists() to make sure the custom method exists in case you need to temporarily deploy your project without the custom PR.

    // XLS Worksheet Column Widths
    // NOTE: setColumnWidth() may only be called AFTER openToFile()
    if (method_exists($writer, 'setColumnWidth')) {
        // NOTE: setColumnWidth() provided by PR#715
        $writer->setColumnWidth(8, 1); // Column A
        $writer->setColumnWidth(12, 2); // Column B
        $writer->setColumnWidth(12, 3); // Column C
        $writer->setColumnWidth(12, 4); // Column D
        $writer->setColumnWidth(12, 5); // Column E
        $writer->setColumnWidth(50, 6); // Column F
        $writer->setColumnWidth(12, 7); // Column G
        $writer->setColumnWidth(12, 8); // Column H
    }

The Custom Column Width PR may also provide these setColumnWidthForRange() and setDefaultRowHeight() methods? I have not tested these methods, but thought others may appreciate knowing they exist.

    // Set Column Width to 12 for Columns A-F (1-6)
    $writer->setColumnWidthForRange(12, 1, 6);

    // Set Default Row Height to 3 for all rows
    $writer->setDefaultRowHeight(3);

Did you find this helpful? Let me know by sending me a comment. I tend to update and maintain posts more frequently if I know others find them helpful. Thanks for visiting!

PhpSpreadsheet Cell Caching with Laravel Simple Cache

I spent quite a bit of time attempting to implement PhpSpreadsheet Cell Caching as described in their Memory Saving documentation page. I also read numerous related articles including Stack Overflow “Building very large spreadsheet with PHPSpreadsheet” where @andrewtweber claimed the Cell Caching was too slow and instead recommended “box/spout” XLSX/ODS/CSV writer.

Attempt to use the box/sprout Package

We are generating ~1MB Excel spreadsheets with ~25K rows and a total of ~197K cells. I confirmed box/spout claims of being VERY fast and VERY memory efficient, using ~94MB *less* memory than PhpSpreadsheet when generating these particular XLSX sheets. Unfortunately, I learned box/spout can NOT set column widths as of April 2021. I am tempted to deploy a custom box/spout with PR 715 “Not just another custom column widths PR”

Cell Caching Summary

I attempted to implement PhpSpreadsheet Cell Caching in a Laravel app using a few different methods. I agree that each method I attempted was too slow to be useful, primarily due to the fact that PhpSpreadsheet must make a separate call to the cache for EVERY CELL. In our case, that meant ~197,000 calls for ~197K cells. Regardless, I am sharing the examples I used so other devs can quickly determine whether or not Cell Caching is too slow for their use case.

Attempt 1: Use native Laravel Cache

I am not sure this is the best way to acquire a Simple Cache interface from Laravel, but PhpSpreadsheet seemed to be interacting with our Redis cache. However, 197K round trips over the network was going to take far too long. I gave up after waiting 5 minutes.

$cache = new Cache();
$cacheStore = $cache::getStore();
$cacheInterface = new Repository($cacheStore);
Settings::setCache($cacheInterface);

$spreadsheet = new Spreadsheet();

Attempt 2: Use Shieldon Simple Cache with File Backend

I installed the “shieldon/simple-cache” package and attempted to use the File backend. Unfortunately, this had to create 197K temporary files (1 file for each of the ~197K spreadsheet cells). This took too long, even on an SSD. I gave up after waiting 5 minutes.

$path = 'cache/php-spreadsheet/';
if (!Storage::exists($path)) {
Storage::makeDirectory($path);
}
$storagePath = Storage::path($path);
$cacheInterface = new \Shieldon\SimpleCache\Driver\File([
'storage' => $storagePath,
]);
Settings::setCache($cacheInterface);

$spreadsheet = new Spreadsheet();

Attempt 3: Use Shieldon Simple Cache with SQLite Backend

I used the “shieldon/simple-cache” package again, only this time I attempted to use the Sqlite backend. My hope was that writing each of the ~197K cells to a single SQLite file might be faster. I was wrong. This took too long. I gave up after waiting 5 minutes. NOTE: Although the SQLite file contained 0 rows afterwards, the file was NOT truncated and remained at its peak size of 127MB. If you choose this method, be careful to truncate or delete your SQLite file to prevent a disk full situation.

$rebuildCache = false;
$path = 'cache/php-spreadsheet/';
if (!Storage::exists($path)) {
Storage::makeDirectory($path);
$rebuildCache = true;
}
$storagePath = Storage::path($path);
$cacheInterface = new \Shieldon\SimpleCache\Driver\File([
'storage' => $storagePath,
]);
$cacheInterface = new \Shieldon\SimpleCache\Driver\Sqlite([
'storage' => $storagePath,
]);
if ($rebuildCache) {
$cacheInterface->rebuild();
}
Settings::setCache($cacheInterface);

$spreadsheet = new Spreadsheet();

Other Options

I saw at least one other person mention that they built their own custom Simple Cache interface and that they were writing 500 cells (or 500 rows?) to each file. I’d like to try this since it would reduce the number of files created from ~197K to only 394 files (or 50 files?), but could not find a code sample. Since OP said their attempts were too slow, I abandoned this option.

Getting Started with User Scripts

A User Script is a locally installed script in your web browser that applies JavaScript and CSS customizations to a specific website while you are visiting the website. Here are a few example use cases:

  1. Add Functionality, such as a “Download List to CSV” button that loops through a list on the web page and generates a CSV file.
  2. Remove Functionality, such as hiding links or buttons you never intend to use to help de-clutter a web interface.
  3. Fix Design Issues, such as the position or color of an element on the page that might be bothering you.

User Scripts can be run in any web browser by installing a User Script browser extension. Here are a few example User Script extensions for some of the more popular web browsers:

  1. Safari – Userscripts
  2. Firefox – Violentmonkey
  3. Brave – Violentmonkey
  4. Chrome – Violentmonkey
  5. Edge – Violentmonkey

PRO TIP: If you have access to a WebDAV file sharing folder that supports BASIC authentication, Violentmonkey can sync your User Scripts settings across multiple browsers. Unfortunately, Violentmonkey does not currently support WebDAV DIGEST authentication.

Getting Started with Userscripts Extension on Safari

I’ve provided a few example scripts you can use to suppress extra menus and buttons on several financial websites. Here are step-by-step instructions to install these scripts.

  1. Install the Userscripts browser extension for Safari
  2. Enable the extension
    1. Go to the Safari drop-down menu and select Preferences
    2. When the Preferences window appears, go to the Extensions tab
    3. Make sure the “Userscripts” extension is enabled. The check box next to the extension name should be checked.
  3. Click the “Userscripts” icon on your Safari browser bar. The icon will look similar to this: “</>
  4. You should see a window with a “Search and Filter” text box, Gear icon, and Plus icon in the upper-left corner
  5. Click the Plus icon and choose “New Javascript”. You should see a new JS User Script in the left-hand list and a new User Script template in the right-hand content area.
  6. Download a Sample Script, open the text file, and copy/paste the code into the right-hand content area, replacing the sample template code. Repeat for each User Script you’d like to try. You can install multiple User Scripts.
    1. Minimal QBO – Minimal interface for Intuit QuickBooks Online
    2. Minimal Mint – Minimal interface for Intuit Mint
  7. Use the toggle in the left-hand list to enable/disable individual User Scripts
  8. Browse to the website and enjoy the User Script customizations!

Viscosity VPN set a static hostname on macOS 11.1 (Big Sur)

Each time I use the Viscosity VPN client on macOS 11.1 (Big Sur) to establish an OpenVPN connection, the hostname displayed in new macOS terminal session would change from my computer name (e.g. “user@trogdor“) to the reverse lookup hostname of my VPN IP address (e.g. “user@ip-10-2-3-4”).

Suppose I always wanted my hostname to be “trogdor“. I ran the following command in a Terminal session to set a permanent hostname that does NOT seem to change when I connect to the VPN.

scutil --set HostName trogdor

Huge thank you to Chris Searle for posting this same solution for OSX Lion and Mountain Lion! I assume that means this solution would also work for all macOS versions between 10.7 and 11.1:

  • OSX 10.7: “Lion”
  • OSX 10.8: “Mountain Lion”
  • OSX 10.9: “Mavericks”
  • OSX 10.10: “Yosemite”
  • OSX 10.11: “El Capitan”
  • macOS 10.12: “Sierra”
  • macOS 10.13: “High Sierra”
  • macOS 10.14: “Mojave”
  • macOS 10.15: “Catalina”
  • macOS 11.0: “Big Sur”
  • macOS 11.1: “Big Sur”

Did this help you out? Do you have more info to share? Please reply below! I’d love to hear from you.

State of U2F in December 2020

NOTE: This is an updated version of my original article: “State of U2F in May 2019” (18 months ago)

I am attempting to register physical USB keys with various online accounts in an attempt to improve my online security.

I purchased multiple YubiKey keys (YubiKey 5C and YubiKey 5C Nano) with the intent to register at least two keys with each of my supported online accounts so that I have a backup in case my primary key is lost or stolen. I have access to additional YubiKey keys for testing, so I will attempt to register at least 3 keys with each service.

Before you begin, consider using YubiKey manager to disable all interfaces except “FIDO U2F” and “FIDO2” on each key. I manually labeled each of my keys as “U2F NNNN”, though it is unclear which sites are using older FIDO1 (FIDO U2F) and which sites are using newer FIDO2. The “OTP” interface is similar to Google Authenticator [1] and could leak your identity [2]. Disabling OTP will prevent YubiKey from typing a string every time you tap the YubiKey button. Only enable the interfaces you intend to use.

Here is a summary of my experience with each online provider. Each browser test was performed on macOS 11.1 (Big Sur) on Apple Silicon M1 with latest version of Brave (1.2.40 88.0.4315.5), Firefox (84.0), and Safari (14.0.2) 64-bit browsers. I am able to register keys to my accounts and authenticate to my accounts using Brave, Firefox, and Safari. All 3 browsers appear to have built-in support for “WebAuthn” and/or “U2F”.

Google (Apps, Cloud)

Key Limit: Multiple (6+) — Successfully registered 6 different U2F keys
Supported Browsers:
• Brave (1.2.40 88.0.4315.5) on macOS 11.1 (Big Sur) – M1 Intel/Rosetta 2
• Firefox (84.0) on macOS 11.1 (Big Sur) – M1 Universal App
• Safari (14.0.2) on macOS 11.1 (Big Sur) – M1 Universal App

GitHub

Key Limit: Multiple (6+) — Successfully registered 6 different U2F keys
Supported Browsers:
• Brave (1.2.40 88.0.4315.5) on macOS 11.1 (Big Sur) – M1 Intel/Rosetta 2
• Firefox (84.0) on macOS 11.1 (Big Sur) – M1 Universal App
• Safari (14.0.2) on macOS 11.1 (Big Sur) – M1 Universal App

Facebook

Key Limit: Multiple (6+) — Successfully registered 6 different U2F keys
Supported Browsers:
• Brave (1.2.40 88.0.4315.5) on macOS 11.1 (Big Sur) – M1 Intel/Rosetta 2
• Firefox (84.0) on macOS 11.1 (Big Sur) – M1 Universal App
Unsupported Browser:
• Safari (14.0.2) on macOS 11.1 (Big Sur) – M1 Universal App (fallback to OTP authenticator code)

WordPress (via Plugin)

Key Limit: Multiple (6+) — Successfully registered 6 different U2F keys
Supported Browsers:
• Brave (1.2.40 88.0.4315.5) on macOS 11.1 (Big Sur) – M1 Intel/Rosetta 2
• Firefox (84.0) on macOS 11.1 (Big Sur) – M1 Universal App
• Safari (14.0.2) on macOS 11.1 (Big Sur) – M1 Universal App

NameCheap

Key Limit: Multiple (6+) — Successfully registered 6 different U2F keys
Supported Browsers:
• Brave (1.2.40 88.0.4315.5) on macOS 11.1 (Big Sur) – M1 Intel/Rosetta 2
• Firefox (84.0) on macOS 11.1 (Big Sur) – M1 Universal App
• Safari (14.0.2) on macOS 11.1 (Big Sur) – M1 Universal App

Amazon Web Services (AWS)

Key Limit: ONE (1) — Simple On/Off toggle for 2FA. Must choose either OTP or U2F. Cannot enable both simultaneously. Unable to Register Multiple U2F Keys [3]
Supported Browsers:
• Brave (1.2.40 88.0.4315.5) on macOS 11.1 (Big Sur) – M1 Intel/Rosetta 2
• Firefox (84.0) on macOS 11.1 (Big Sur) – M1 Universal App
Unsupported Browsers:
• Safari (14.0.2) on macOS 11.1 (Big Sur) – M1 Universal App

Twitter

Key Limit: ONE (1) — Simple On/Off toggle for U2F. Unable to Register Multiple U2F Keys. Can enable OTP and U2F simultaneously.
Supported Browsers:
• Brave (1.2.40 88.0.4315.5) on macOS 11.1 (Big Sur) – M1 Intel/Rosetta 2
• Firefox (84.0) on macOS 11.1 (Big Sur) – M1 Universal App
• Safari (14.0.2) on macOS 11.1 (Big Sur) – M1 Universal App

No Support for U2F or FIDO2

LinkedIn – The only supported verification methods are SMS and OTP (Authenticator App) as of 12/2020
MailChimp – The only supported verification methods are SMS and OTP (Authenticator App) as of 12/2020
Slack – The only supported verification methods are SMS and OTP (Authenticator App) as of 12/2020. More Info

References

[1] Medium: The Unofficial FIDO U2F FAQ
[2] Hacker Noon: Avoid Leaking Your Identity with YubiKey
[3] AWS: Use YubiKey security key to sign into AWS Management Console with YubiKey for multi-factor authentication (comments confirm only one U2F device is supported per login)