Yii v2 snippet guide II

You are viewing revision #26 of this wiki article.
This is the latest version of this article.
You may want to see the changes made in this revision.

« previous (#25)

  1. Intro
  2. Connection to MSSQL
  3. Using MSSQL database as the 2nd DB in the Yii2 project
  4. Creating models in Gii for remote MSSQL tables
  5. PhpExcel/PhpSpreadsheet in Yii 2 and sending binary content to the browser

Intro

Hi. I had to split my article as max length was reached. Check my previous articles here (mainly the 1st one):

Connection to MSSQL

You will need MSSQL drivers in PHP. Programatically you can list them or test their presence like this:

var_dump(\PDO::getAvailableDrivers());

if (in_array('sqlsrv', \PDO::getAvailableDrivers())) {
  // ... MsSQL driver is available, do something
}

Based on your system you have to download different driver. The differences are x64 vs x86 and ThreadSafe vs nonThreadSafe. In Windows I always use ThreadSafe. Explanation.

Newest PHP drivers are here.

  • Drivers v5.8 = PHP 7.2 - 7.4

Older PHP drivers here.

  • Drivers v4.0 = PHP 7.0 - 7.1
  • Drivers v3.2 = PHP 5.x

Once drivers are downloaded and extracted, pick one DLL file and place it into folder "php/ext". On Windows it might be for example here: "C:\xampp\php\ext"

Note: In some situations you could also need these OBDC drivers, but I am not sure when:

Now file php.ini must be modified. On Windows it might be placed here: "C:\xampp\php\php.ini". Open it and search for rows starting with word "extension" and paste there cca this:

extension={filename.dll}
// Example:
extension=php_pdo_sqlsrv_74_ts_x64.dll

Now restart Apache and visit phpinfo() web page. You should see section "pdo_sqlsrv". If you are using XAMPP, it might be on this URL: http://localhost/dashboard/phpinfo.php.

Then just add connection to your MSSQL DB in Yii2 config. In my case the database was remote so I needed to create 2nd DB connection. Read next chapter how to do it.

Using MSSQL database as the 2nd DB in the Yii2 project

Adding 2nd database is done like this in yii-config:

'db' => $db, // the original DB
'db2'=>[
  'class' => 'yii\db\Connection',
  'driverName' => 'sqlsrv',
  // I was not able to specify database like this: 
  // 'dsn' => 'sqlsrv:Server={serverName};Database={dbName}',
  'dsn' => 'sqlsrv:Server={serverName}', 
  'username' => '{username}',
  'password' => '{pwd}',
  'charset' => 'utf8',
],

That's it. Now you can test your DB like this:

$result = Yii::$app->db2->createCommand('SELECT * FROM {tblname}')->queryAll();
var_dump($result);

Note that in MSSQL you can have longer table names. Example: CATEGORY.SCHEMA.TBL_NAME

And your first test-model can look like this (file MyMsModel.php):

namespace app\models;
use Yii;
use yii\helpers\ArrayHelper;
class MyMsModel extends \yii\db\ActiveRecord
{
  public static function getDb()
  {
    return \Yii::$app->db2; // or Yii::$app->get('db2');
  }
  public static function tableName()
  {
    return 'CATEGORY.SCHEMA.TBL_NAME'; // or SCHEMA.TBL_NAME
  }
}

Usage:

$result = MyMsModel::find()->limit(2)->all();
var_dump($result);

Creating models in Gii for remote MSSQL tables

Once you have added the 2nd database (read above) go to the Model Generator in Gii. Change there the DB connection to whatever you named the connection in yii-config (in the example above it was "db2") and set tablename in format: SCHEMA.TBL_NAME. If MSSQL server has more databases, one of them is set to be the main DB. This will be used I think. I haven't succeeded to change the DB. DB can be set in the DSN string, but it had no effect in my case.

PhpExcel/PhpSpreadsheet in Yii 2 and sending binary content to the browser

In previous chapters I showed how to use PhpExcel in Yii 1. Now I needed it also in Yii 2 and it was extremely easy.

Note: PhpExcel is deprecated and was replaced with PhpSpreadsheet.

// 1) Command line:
// This downloads everything to folder "vendor"
composer require phpoffice/phpspreadsheet --prefer-source
// --prefer-source ... also documentation and samples are downloaded 
// ... adds cca 40MB and 1400 files 
// ... only for devel system

// 2) PHP:
// Now you can directly use the package without any configuration:
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// Uncomment following rows if you want to set col width:
//$sheet->getColumnDimension('A')->setAutoSize(false);
//$sheet->getColumnDimension('A')->setWidth("50");

$sheet->setCellValue('A1', 'Hello World !');

$writer = new Xlsx($spreadsheet);

// You can save the file on the server:
// $writer->save('hello_world.xlsx'); 

// Or you can send the file directly to the browser so user can download it:
// header('Content-Type: application/vnd.ms-excel'); // This is probably for older XLS files.
header('Content-Type: application/application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); // This is for XLSX files (they are basically zip files).
header('Content-Disposition: attachment;filename="filename.xlsx"');
header('Cache-Control: max-age=0');
$writer->save('php://output');
exit();

Thanks to DbCreator for the idea how to send XLSX to browser. Nevertheless exit() or die() should not be called. Read the link.

Following is my idea which originates from method renderPhpFile() from Yii2:

ob_start();
ob_implicit_flush(false);
$writer->save('php://output');
$file = ob_get_clean();

return \Yii::$app->response->sendContentAsFile($file, 'file.xlsx',[
  'mimeType' => 'application/application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
  'inline' => false
]);

This also worked for me:

$tmpFileName = uniqid('file_').'.xlsx';
$writer->save($tmpFileName);    
header('Content-Type: application/application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); 
header('Content-Disposition: attachment;filename="filename.xlsx"');
header('Cache-Control: max-age=0');
echo file_get_contents($tmpFileName);
unlink($tmpFileName);
exit();

Note: But exit() or die() should not be called. Read the "DbCreator" link above.

2 0
1 follower
Viewed: 1 372 times
Version: 2.0
Category: Tutorials
Written by: rackycz
Last updated by: rackycz
Created on: Aug 26, 2020
Last updated: 20 days ago
Update Article

Revisions

View all history

Related Articles