خروجی اکسل از دیتابیس در PHP

سلام

با یک آموزش دیگه از سری آموزش های PHP در خدمتتون هستیم؛ حتما در برخی از وب سایت ها دیدید که به کاربران خود این اجازه را میدهند تا از اطلاعات خود خروجی به صورت فایل csv بگیرند تا توسط نرم افزار Excel بتوانند آن را مدیریت کنند، در این مقاله ی آموزشی می خواهیم تا با انجام چند مرحله ی خیلی ساده از جدوال مورد نیاز در دیتابیس توسط PHP خروجی بگیریم، در ادامه همراه بپرسم باشید.

دریافت خروجی Excel در PHP

شروع آموزش:

فرض میکنیم در دیتابیس جدولی به اسم tbl_beporsam داریم که شامل سه فیلد id,name,family می باشد، حال میخواهیم از این جدول خروجی فایل Excel با فرمت csv بگیریم.

جدول به این شکل می باشد:

tbl_beporsam

در این آموزش برای اتصال به دیتابیس از آنجایی که mysql منقضی شده و PDO امن تراز mysqli هست از روش PDO استفاده میکنیم.

ارتباط با دیتابیس

قبل از هر چیز باید با دیتابیس ارتباط برقرار کنیم، برای این کار از کدهای زیر استفاده میکنیم:

$servername = 'localhost'; // server name
$username = 'root'; // username database
$password = ''; // password database
$dbname = 'beporsam'; //database name
$attr = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8');
$conn = new PDO('mysql:host=' . $servername . ';dbname=' . $dbname, $username, $password, $attr);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$servername = 'localhost'; // server name

$username = 'root'; // username database

$password = ''; // password database

$dbname = 'beporsam'; //database name

$attr = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8');

$conn = new PDO('mysql:host=' . $servername . ';dbname=' . $dbname, $username, $password, $attr);

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

کدهای بالا که نیاز به توضیح خاصی ندارند، در خط:

نام سرور که در ۹۹ درصد موارد localhost هست.
نام کاربری دیتابیس.
رمز عبور دیتابیس.
نام دیتابیس.

مطلب پیشنهادی: غیر فعال کردن تصاویر در فایرفاکس

دریافت مقادیر از جدول

پس از ارتباط با دیتابیس، نوبت میرسه به اینکه با جدول مورد نظر ارتباط برقرار کنیم و اطلاعات رو دریافت کنیم، برای دریافت اطلاعات هم که از کوئری SELECT به صورت زیر استفاده میکنیم:

$stm = $conn->prepare("SELECT * FROM tbl_beporsam");
$stm->execute();

۱ ۲	$stm = $conn->prepare("SELECT * FROM tbl_beporsam"); $stm->execute();

خروجی گرفتن از اطلاعات

نوبت میرسه به کار اصلیمون که خروجی گرفتن از اطلاعات دریافت شده از جدول tbl_beporsam است، کدهای ما به این شکل است:

if ($stm){
            $delimiter = ";";
            $filename = "users_" . date('Y-m-d') . ".csv";

            header('Content-Encoding: UTF-8');
            header('Content-type: application/csv; charset=UTF-8');
            header('Content-Disposition: attachment; filename='.$filename);

            $f = fopen('php://output', 'w');
            fputs( $f, "\xEF\xBB\xBF" ); // UTF-8 BOM !!!!!

            $fields = ['شناسه','نام','نام خانوادگی'];
            fputcsv($f, $fields,$delimiter);

            while ($row = $stm->fetch(PDO::FETCH_ASSOC)){
                $linedata = [$row['id'],$row['name'],$row['family']];
                fputcsv($f, $linedata,$delimiter);
            }

            fpassthru($f);
        }

۱۰

۱۱

۱۲

۱۳

۱۴

۱۵

۱۶

۱۷

۱۸

۱۹

۲۰

۲۱

if ($stm){

$delimiter = ";";

$filename = "users_" . date('Y-m-d') . ".csv";

header('Content-Encoding: UTF-8');

header('Content-type: application/csv; charset=UTF-8');

header('Content-Disposition: attachment; filename='.$filename);

$f = fopen('php://output', 'w');

fputs( $f, "\xEF\xBB\xBF" ); // UTF-8 BOM !!!!!

$fields = ['شناسه','نام','نام خانوادگی'];

fputcsv($f, $fields,$delimiter);

while ($row = $stm->fetch(PDO::FETCH_ASSOC)){

$linedata = [$row['id'],$row['name'],$row['family']];

fputcsv($f, $linedata,$delimiter);

}

fpassthru($f);

}

توضیحات:

ابتدا با شرط if بررسی میکنیم که ایا متغیر stmt$ حاوی اطلاعات دریافت شده از جدول tbl_beporsam هست، یا نه.
سپس متغیری به اسم delimiter$ تعریف میکنیم و مقدارش رو برابر ; قرار میدیم(در ادامه دلیل این کار را خواهید فهمید).
سپس در متغیری به اسم filename$ نام فایل را مشخص کردیم، نام فایل به این صورت هست که ابتدا عبارت _users و سپس تاریخ و سپس پسوند فایل که csv هست خواهد بود، برای مثال نام فایل اکسل به این صورت می شود:

مطلب پیشنهادی: نحوه اتصال آیپد به تلویزیون (7 روش اتصال)

users_2020-05-25.csv

۱	users_2020-۰۵-۲۵.csv

خط ۹ توسط تابع fopen دستور خروجی داده ها دادیم.
خط ۱۰ مربوط به یونکید UTF-8 هست که داده ها در فایل اکسل به درستی نمایش داده شوند.
خط ۱۲ که نام سلول های فایل اکسل را مشخص میکنیم، که مقادیر زیر این سلول ها قرار میگیرند(مانند table داده ها در ستون ها ذخیره میشوند)، همانند تصویر زیر:

ستون های فایل

خط ۱۳ از تابع fputcsv استفاده کردیم که سه ورودی میگیرد، ورودی اول دستور خروجی گرفتن، ورودی دوم تعیین فیلدهای ستون ها و در ورودی آخر جداکننده را مشخص میکنیم(که در متغیر delimiter$ جداکننده را مشخص کردیم).
در خط ۱۵، ۱۶ و ۱۷ هم که اطلاعات رو از دیتابیس fetch کردیم(گرفتیم) و گفتیم که مقادیر id,name و family رو دریافت و در فایل خروجی وارد کنه.

در نهایت تمامی کدهای ما به این شکل میشود:

        $servername = 'localhost';
        $username = 'root';
        $password = '';
        $dbname = 'beporsam';
        $attr = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8');
        $conn = new PDO('mysql:host=' . $servername . ';dbname=' . $dbname, $username, $password, $attr);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $stm = $conn->prepare("SELECT * FROM tbl_beporsam");
        $stm->execute();

        if ($stm){
            $delimiter = ";";
            $filename = "users_" . date('Y-m-d') . ".csv";

            header('Content-Encoding: UTF-8');
            header('Content-type: application/csv; charset=UTF-8');
            header('Content-Disposition: attachment; filename='.$filename);

            $f = fopen('php://output', 'w');
            fputs( $f, "\xEF\xBB\xBF" ); // UTF-8 BOM !!!!!

            $fields = ['شناسه','نام','نام خانوادگی'];
            fputcsv($f, $fields,$delimiter);

            while ($row = $stm->fetch(PDO::FETCH_ASSOC)){
                $linedata = [$row['id'],$row['name'],$row['family']];
                fputcsv($f, $linedata,$delimiter);
            }

            fpassthru($f);
        }

۱۰

۱۱

۱۲

۱۳

۱۴

۱۵

۱۶

۱۷

۱۸

۱۹

۲۰

۲۱

۲۲

۲۳

۲۴

۲۵

۲۶

۲۷

۲۸

۲۹

۳۰

۳۱

۳۲

$servername = 'localhost';

$username = 'root';

$password = '';

$dbname = 'beporsam';

$attr = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8');

$conn = new PDO('mysql:host=' . $servername . ';dbname=' . $dbname, $username, $password, $attr);

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stm = $conn->prepare("SELECT * FROM tbl_beporsam");

$stm->execute();

if ($stm){

$delimiter = ";";

$filename = "users_" . date('Y-m-d') . ".csv";

header('Content-Encoding: UTF-8');

header('Content-type: application/csv; charset=UTF-8');

header('Content-Disposition: attachment; filename='.$filename);

$f = fopen('php://output', 'w');

fputs( $f, "\xEF\xBB\xBF" ); // UTF-8 BOM !!!!!

$fields = ['شناسه','نام','نام خانوادگی'];

fputcsv($f, $fields,$delimiter);

while ($row = $stm->fetch(PDO::FETCH_ASSOC)){

$linedata = [$row['id'],$row['name'],$row['family']];

fputcsv($f, $linedata,$delimiter);

}

fpassthru($f);

}

مطلب پیشنهادی: معرفی ربات تلگرام ماشین حساب

کدها را اجرا کنید و خروجی کار را مشاهده کنید؛ خواهید دید که یک اطلاعات جدول tbl_beporsam به صورت فایل اکسل در اختیار شما قرار میگیرد.

حل مشکل نمایش اطلاعات در فایل اکسل

پس از خروجی گرفتن از دیتابیس، اطلاعات شما مانند تصویر زیر همگی در یک فیلد و سلول نمایش داده میشود:

عدم نمایش صحیح اطلاعات

برای رفع این مشکل در نرم افزار Excel از زبانه Data گزینه From Text/CSV را انتخاب کنید.

From Text/CSV

پنجره ای باز میشود که باید به محل ذخیهر فایل csv. که از دیتابیس خروجی گرفتید، رفته و آن را باز کنید.
سپس در پنجره ی باز شده، از بخش delimiter گزینه Semicolon را انتخاب کنید(اشاره به متغیر delimiter$).

select delimiter

سپس گزینه Load را انتخاب کنید، خواهید دید که فایل خروجی به صورت مرتب داده ها را نمایش میدهد.

نمایش صحیح دادها

در پایان میتوانید سورس آموزش را رایگان دریافت کنید.

این قسمت از آموزش نیز به پایان رسید، همراه ما باشید با دیگر آموزش ها …

موفق باشید.