Articles

Travel Tips: Downtown Charlotte, NC

by jrklein Travel

Planning to visit downtown Charlotte, NC with a few guys for a long weekend. Sharing my travel notes for other first-time visitors.

Resources

Public Transit vs Ride Sharing vs Car Rental

We initially thought we would need to ride share or rent a car, but it sounds like Charlotte, NC has a great public transit system. We plan to take a bus from Airport to Downtown (bus runs every 15 minutes), then take their light rail system and busses around downtown. Cost is about $2/ride or $30/week for unlimited rides.

CATS Bus Route: Airport Terminal to Downtown (Airport to downtown is 21 minute ride)
https://charlottenc.gov/cats/bus/routes/Pages/

CATS Light Rail: Lynx Blue/Gold Rail Lines
https://charlottenc.gov/cats/rail/Pages/

CATS Map of the Blue Line (04/2022). The trains currently stop every 15 minutes during the day and every 30 minutes in the evenings.

Here’s a Google Map of Blue Line from McCullough Station (8312 N Tryon St) to Arrowood Station (7430 South Blvd). We aren’t planning to go to the far ends of the line (I-485 or UNC) so I didn’t include the last few stops, but it is interesting to see where the line travels through the Charlotte Metro area.
https://bit.ly/map-clt-rail-blue

CATS Map of the Yellow Line (04/2022). The streetcars currently stop every 20 minutes during the day and every 30 minutes in the evenings. The BLUE/YELLOW lines meet at CTC/Arena (Charlotte Transportation Center)

Charlotte has plans to continue expanding their yellow/blue lines and add additional lines, so definitely check the CATS website for the latest maps and schedule.

Nightlife near Light Rail Stations

I compiled a list of the (currently) highest-rated attractions near Charlotte Blue Line Stations w/ the current Google Rating and Review Count. Based on the Google and Yelp reviews I’ve read, Cellar at Duckworth’s, Wooden Robot, and Aura Rooftop get my votes.

9th St Station

7th St Station

CTC/Arena Station

3rd St Station

Stonewall Station

Carson

Bland Station

East/West Blvd Station

New Bern Station

List Inspired by: Bars and Restaurants Along Charlotte’s Blue Line Light Rail
https://www.thrillist.com/eat/charlotte/bars-and-restaurants-along-charlottes-blue-line-light-rail

Charlotte Attractions

Lodging

If you can find a hotel or B&B near a public transit station, you’ll be able to get around the downtown area without renting a car or relying on ride share services (e.g. Lyft, Uber) based on comments I’ve seen in many different downtown B&B reviews.

We initially booked a B&B so that we could stay together, but the host cancelled on us 4 days before the trip due to a water/flooding emergency. We ended up booking a hotel near one of the Blue Line light rail stations. We couldn’t find another B&B near a Blue Line station that seemed like a good fit for us.

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!

How to apply Excel “Center Across Selection” in PhpSpreadsheet XLSX files

by jrklein Developer Tips, PHP+MySQL Tips

Someone suggested replacing Merged Cells in our Excel-formatted (.xlsx) reports with “Center Across Selection” which gives the same visual result without the undesirable side effects of merged cells.

We currently use PhpSpreadsheet v1.16 to generate our Excel-formatted (.xlsx) reports.

I could NOT find any PhpSpreadsheet documentation or discussion of this feature, other than the following PhpSpreadsheet CHANGELOG reference from over a decade ago:

Horizontal center across selection - @Erik Tilt

I dug into Microsoft file format reference material and finally determined that the PhpSpreadsheet equivalent to Excel “Center Across Selection” is the “Alignment::HORIZONTAL_CENTER_CONTINUOUS” value (aka “centerContinuous”).

Here’s how to apply Excel “Center Across Selection” to a range in PhpSpreadsheet:

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->getStyle("A1:C1")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER_CONTINUOUS);
$sheet->setCellValue("A1", "Example");

The code above will write text “Example” to cell A1. The text will be centered across 3 cells (A1,B1,C1).

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!

AWS Aurora MySQL Manual Failover Instructions – Demote Writer instance to Reader instance

by jrklein AWS Tips

This article is for you if…

  • You are using Aurora MySQL on Amazon Web Services (AWS).
  • You have one Writer instance and one Reader instance.
  • You would like to perform a manual failover (so that your Writer instance is demoted to a Reader instance).

I needed to do this recently, but could not find clear instructions anywhere. Here are step-by-step instructions with screenshots.

  1. Login to your AWS Console
  2. Go to the RDS Console and select your Aurora MySQL Cluster
  3. Highlight your Aurora MySQL Writer Instance, choose Failover from the Actions menu

    Comment: Highlighting a specific instance may not be important. I believe if you have multiple Reader instances, the failover will use the “Failover priority” (e.g. “tier-0”, “tier-1”, … “tier-15”) to determine which Reader to promote to Writer. When I added a temporary 2nd Reader with “Failover priority” of “tier-0” and performed failover, that Reader was promoted to Writer. Please post a comment at the bottom of this page if you can confirm how failover is handled with multiple Reader instances! If the “Failover priority” is used to select which Writer instance will be promoted, please confirm if “tier-0” is indeed considered first when promoting a Reader to a Writer and I will update these instructions.

4. Confirm you would like to failover the cluster.

5. Go to the RDS Console and select your Aurora MySQL Cluster. The page will not show any changes for a few seconds.

6. Go to the RDS Console and select your Aurora MySQL Cluster (or refresh the page). Repeat until the page shows that the Writer instance has been demoted to a Reader instance.

7. Failover complete!

In my experience, the failover occurs within 10-15 seconds after submitting the failover request. Our application is aware of the separate reader and writer instances. We have not noticed any application read errors during the manual failover process. However, we have noticed temporary write errors during the failover process. Our application is designed to handle brief write failures and retry the writes, so this was not an issue.

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!

Format Numbers and Dates in box/spout Excel Spreadsheets

by jrklein Uncategorized

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

by jrklein Uncategorized

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.