PHP: Exporting data table array to Excel xlsx file with PhpSpreadsheet library

In many occasions when a data table is queried from database and/or displayed on the screen of web application, there is a need to enable the user to download the table, preferably as an MS Excel file.

Traditionally, the method of doing so is to export the table as CSV (comma-separated values) or TSV (tab-separated values) text file, and to use MS Excel to import the CSV or TSV into a worksheet.

There are a lot of shortcomings using this method, including but not limited to:

• You need to open a new webpage in order to perform the file export, which looks weird to the user experience.
• The separator character might be different for different users. Some uses comma (","), some uses semicolon (";"), some uses vertical bar pipe ("|"), and so on. Libre Calc in LibreOffice is more flexible in handling the separator character during data import, whereas in MS Excel, if the separator character in the file is not the same with your computer's regional format setting, you will probably get rows of long data in single column.
• The CSV/TSV is in plain text, and no cell formatting can be made.
• You need to properly escape and handle the data to be exported, to ensure your data export is successful without error.
• Numeric fields will most probably be treated as number in the worksheet, although you might want to treat them as text string instead. Common occasions including handling of telephone numbers, batch numbers, serial numbers, etc.

Over the years, there is a trick to export a TSV as XLS (old Excel file format), by putting these 2 lines in the page header:

header("Content-Disposition: attachment; filename=\"$filename\"");

header("Content-Type: application/vnd.ms-excel");

There is a matured sample source code by Chirp Internet (www.chirpinternet.eu) that includes a function to do data cleansing before the export. You can click here to look for the source code of that script.

However, the exported file is actually just a TSV instead of real XLS file. Newer version of MS Excel will actually prompt out warning message when you try to open such exported XLS file.

A much better and proper method is to use the PhpSpreadsheet library to generate the real MS Excel file in xlsx (Microsoft Open XML Spreadsheet) format.

By using this method, you can export the Excel file without the need of opening a new webpage that confuse the user. You can also perform many Excel file manipulation by using the functions, classes and methods provided by the PhpSpreadsheet library.

The PHP codes below are originally posted by billynoah in Stack Overflow forum.

Step 1: Install PhpSpreadsheet library using composer, if you haven't done so.

If you are using Laragon WAMP platform, you can install the library by opening the Terminal window (the 4th button in Laragon control panel) and type the following command:

composer require phpoffice/phpspreadsheet

Step 2: Add these 3 lines at the beginning of your PHP script.

require '/vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;

use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

Step 3: Prepare your dataset in an array. Normally, this is queried from SQL database server using SELECT statement. Note that in order to use the writeXLSX function with source codes in Step 4 below, the dataset needs to be stored in an array instead of an object.

Below is a simple example of such codes, utilizing ezSQL library class:

$db = new ezSQL_mysqli(MY_USR, MY_PWD, MY_DB, MY_HOST);

$sql = "SELECT * FROM ".MY_TABLE;

$items =$db->get_results($sql, $output=ARRAY_A);

$keys = array_keys(current($items));

Step 4: Put the following function somewhere in your script.

function writeXLSX($filename, $rows, $keys = [], $formats = []) {

// instantiate the class

$doc = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

$sheet = $doc->getActiveSheet();

// $keys are for the header row.  If they are supplied we start writing at row 2

if ($keys) {

$offset = 2;

} else {

$offset = 1;

}

// write the rows

$i = 0;

foreach($rows as $row) {

$doc->getActiveSheet()->fromArray($row, null, 'A' . ($i++ + $offset));

}

// write the header row from the $keys

if ($keys) {

$doc->setActiveSheetIndex(0);

$doc->getActiveSheet()->fromArray($keys, null, 'A1');

}

// get last row and column for formatting

$last_column = $doc->getActiveSheet()->getHighestColumn();

$last_row = $doc->getActiveSheet()->getHighestRow();

// if $keys, freeze the header row and make it bold

if ($keys) {

$doc->getActiveSheet()->freezePane('A2');

$doc->getActiveSheet()->getStyle('A1:' . $last_column . '1')->getFont()->setBold(true);

}

// format all columns as text

$doc->getActiveSheet()->getStyle('A2:' . $last_column . $last_row)->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_TEXT);

if ($formats) {

foreach ($formats as $col => $format) {

$doc->getActiveSheet()->getStyle($col . $offset . ':' . $col . $last_row)->getNumberFormat()->setFormatCode($format);

}

}

// enable autofilter

$doc->getActiveSheet()->setAutoFilter($doc->getActiveSheet()->calculateWorksheetDimension());

// autosize all columns to content width

for ($i = 'A'; $i <= $last_column; $i++) {

$doc->getActiveSheet()->getColumnDimension($i)->setAutoSize(TRUE);

}

// write and save the file

$writer = new PhpOffice\PhpSpreadsheet\Writer\Xlsx($doc);

$writer->save($filename);

}

Step 5: Call the writeXLSX function to generate your MS Excel file.

writeXLSX(EXCELFILE, $items, $keys);

That's all about it. Pretty easy and straightforward, isn't it?

Online stock filtering and screening by ROE and ROA

In fundamental analysis, Return of Equity (ROE) and Return of Assets (ROA) are among the most important indicators used to evaluate the investability of targetted stock counters.

There are generally 4 components that make up the shareholders' equity:

• The shares that are sold out to the shareholders, and tradable in the market.
• The shares that are repurchased by the company and kept as treasury shares.
• Company's profit that is reinvested into the business as retained earnings.
• Additional paid-in capital pumped into the company by its shareholders.

In short, shareholders' equity is the money that the company's shareholder had invested into its business, together with the retained earnings from the company's business profit.

ROE is therefore a measurement to evaluate how effective is the company's management team in managing the business and bringing in annual profit to the company.

ROE = Net Profit / Shareholder Equity

The main different between ROE and ROA is that, ROE does not take into account the financial leverage or debt of the company, whereas ROA includes it.

As you might have aware of the basic financial equation as illustrated in the diagram below:

Total Assets = Shareholders' Equity + Total Liabilities

The company needs assets to run its business. The assets can be funded either from shareholders' equity, or from 3rd parties (non-shareholders) as liabilities, for example, in the form of financing loans.

ROA is a measurement to evaluate how effective is the company's management team in utilizing the company's assets to make profit.

ROA = Net Profit / Total Assets

ROA = Net Profit / (Shareholder Equity + Liabilities)

For those companies that are in net cash position (without any debts), its ROE = ROA.

For those companies with liabilities, its ROE is normally higher than ROA, as assets are increased by taking debt.

As a benchmark, if the ROA is lower than bond return rate or even fixed deposit return rate, the shareholders might as well put their money (and borrowed money) in those low-risk money-generating financial instruments, rather than in the business, which is more risky.

When picking target stocks for investment from thousands of counters in the stock market, one of the initial fundamental shortlisting methods is to filter the target stocks to those that meeting the criteria of ROE > 20% and ROA > 15%.

In Rakuten Trade, you can easily do your shortlisting with just a few mouse clicks.

You just need to login to Rakuten Trade online trading website, select the Stock Screener menu, add a new filtering criterion, and select ROE (%).

After that, do the same to select another filtering criterion ROA (%).

Set the minimum of ROE to 20%, and the minimum of ROA to 15%.

You will instantly get the list of matching stocks on your screen, which is around 30 to 40 counters at the time being.

If you don't have a Rakuten Trade account to access to the Stock Screener above, you can use this link to open a new Rakuten Trade account for free, including no charge for opening new CDS account. By opening account with the link, you can get 500 RT Points within 30 days upon your account activation. Beside that, if you start your trading within the first 10 business days after account activation, your first online trading brokerage fee will be rebated as additional RT Points as well.

How to use RT Points in Rakuten Trade to offset brokerage fee

Rakuten Trade is currently the first and the only Malaysian stockbroking online platform with a loyalty point system and also allows offset of stock trading brokerage fee by using your accumulated RT Points.

With this mechanism, if you have enough RT Points in your account, you can literally trade with zero brokerage fee when you wholly offset it with your RT Points.

Each RT Points carries a value of RM0.01. As such, if you have 1,000 RT Points, that is equivalent to RM10 value that you can use to pay for brokerage fee.

Rakuten Trade offers 3 different types of stock trading accounts: Cash Upfront, Contra, and RakuMargin. Each of them will have a separate CDS number, but you can access to all of them using a single Rakuten Trade user account.

Normally, a brokerage firm will charge you RM10 for each new CDS number account opening, but opening stock trading account with Rakuten Trade (including the CDS account) is free of charge and can be completed wholly online without the need to fill up paper forms or meeting in person.

If you use this link to open a new Rakuten Trade account, you can get 500 RT Points within 30 days upon your account activation. Beside that, if you start your trading within the first 10 business days after account activation, your first online trading brokerage fee will be rebated as additional RT Points as well.

The brokerage fee of Rakuten Trade online platform is among the cheapest in Malaysia, ranging from a minimum of RM7 to a maximum of RM100. If you trade in large amount per transaction, the maximum cap of RM100 will be a very attractive rate.

If you do day trading (buying and selling the same share within the same day) using your contra account, the brokerage fee for your selling transaction will also be rebated.

Below are some of the ways to accumulate RT Points:

• Refer your friends to Rakuten Trade (500 RT Points each)
• Trading brokerage fee (RM2 = 1 RT Point)
• Deposit at least RM5,000 into your trading account within 5 business days after account activation (150 RT Points for each type of accounts)
• Transfer shares from your other CDS to your Rakuten Trade CDS (150 RT Points)
• Participate in Rakuten Trade's online and offline events, seminars, talks, survey, etc.

Online trading with Rakuten Trade can be performed either with the Rakuten Trade web platform or the iSPEED.my mobile app.

To offset your brokerage fee with your RT Points, make sure you select the "Use RT Pts to lower Brokerage" option in your Buy/Sell order, before you click on the Confirm button.

The same applies in iSPEED.my mobile app.

Beside able to use for brokerage offsetting, you can also transfer your RT Points to AirAsia BIG, Bonuslink or Boost at a 1:1 conversion rate.