آموزش فارسی استخراج داده MySQl به Excel در زبان PHP

با سلام و احترام، با یکی دیگه از مقاله‌های آموزشی شرکت روکسو در خدمت شما عزیزان هستیم. در این مقاله قصد داریم نحوه‌ی خروجی گرفتن از داده‌های موجود در پایگاه داده‌ی MySQL به فرمت اکسل را به شما همراهان گرامی آموزش دهیم. با ما همراه باشید.

در این مثال می‌خواهیم یک صفحه ساده‌ی PHP‌ ایجاد کنیم تا بتوانیم اطلاعات موجود در MySQL‌ را در آن نمایش داده و در نهایت یک دکمه برای ذخیره اطلاعات موجود در دیتابیس به فرمت اکسل در کامپیوتر شخصی شما، قرار دهیم. بنابراین یک به یک مراحل زیر را به ترتیب دنبال کنید:

۱) ابتدا یک جدول تحت عنوان tbl_User در MySQL‌ برای ذخیره کاربران ایجاد کنید. این جدول شامل UserName و Password و ... است. برای ساخت جدول با فیلدهای موردنیاز به صورت زیر عمل می‌کنیم:

CREATE TABLE IF NOT EXISTS `tbl_user` (  
`ur_Id` int(11) NOT NULL AUTO_INCREMENT,  
`ur_username` varchar(50) NOT NULL,  
`ur_password` varchar(50) NOT NULL,  
`ur_status` int(11) NOT NULL,  
PRIMARY KEY (`ur_Id`)  
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

در صورتیکه این کار را به درستی انجام داده باشید با خروجی زیر در PhpMyAdmin خودتون مواجه خواهید شد:

ساخت جدول tbl_User

۲) حال باید داخل جدول tbl_User یک سری اطلاعات وارد کنید. بنابراین برای وارد کردن اطلاعات می‌تواند از عبارت زیر در SQL استفاده کنید:

INSERT INTO `tbl_user` (`ur_Id`, `ur_username`, `ur_password`, `ur_status`) VALUES  
(1, 'masoudline@gmail.com', 'مسعود', 1),  
(2, 'support@roxo.ir', 'انجمن روکسو', 1),  
(3, 'info@roxo.ir', 'برنامه نویسی روکسو', 1);

اگر این کار را با موفقیت انجام داده باشید با خروجی زیر روبه رو می شوید:

اطلاعات ذخیره شده در phpmyadmin

۳) در این مرحله اولین صفحه‌ی ساده‌ی PHP خود را ایجاد کرده تا اطلاعات فوق را به نمایش بگذاریم:

<html>  
  
<head>  
    <title>User Report List</title>  
</head>  
  
<body> </body>  
  
</html>

کد بالا را در فایل UserReport.php ذخیره نمایید.

۱-۳) برای نمایش زیباتر اطلاعات از یک جدول HTML‌ استفاده می‌کنیم که این جدول دارای ۳ ستون است:

<table border="1">  
    <tr>  
        <th>Sr NO.</th>  
        <th width="120">User Name</th>  
        <th>Password</th>  
    </tr>  
</table>

در جدول فوق یک ردیف برای سربرگ تعیین کردیم.

۲-۳) حال باید اطلاعات را از دیتابیس واکشی (fetch) کنیم تا مقادیر جدول برای هر ردیف را متناسب با داده‌های موجود پر کنیم. برای اینکار ابتدا یک کانکشن (ارتباط) با MySQL Server ایجاد می‌کنیم و سپس با استفاده از متد mysqli_select_db ارتباط با برقرار می‌کنیم:

<?php  
   $conn = new mysqli('localhost', 'root', '');   
   mysqli_select_db($conn, 'EmployeeDB');   
   $conn->set_charset("utf8");

?>

همانطور که مشاهده می‌کنید نام سرور در این مثال localhost و نام کاربری و پسورد آن به ترتیب برابر root و مقدار خالی (null‌ نیست) تعیین شده است. همچنین نام پایگاه داده نیز EmployeeDB‌ تعریف می‌شود.

توجه: همراهان گرامی فارسی زبان باید توجه داشته باشند که جهت دستیابی به خروجی فارسی باید همواره دیتابیس را در حالت utf8_general_ci ایجاد کرده باشند و حتما کد set_charset را به مجموعه کد خود اضافه کنند.

۳-۳) در این مرحله باید Query موردنظر جهت استخراج داده‌های SrNo و Username و Password از دیتابیس استخراج شود. سپس این Query‌ را به دیتابیس ارسال می‌کنیم:

$sql = mysqli_query($conn,"SELECT `ur_Id`,`ur_username`,`ur_password` FROM `tbl_user`");

با اجرای این دستور تمام اطلاعات یا به اصطلاح رکوردها از دیتابیس MySQL واکشی شده و درون متغییر sql$ ریخته می‌شود.

۴-۳) پس از انجام مراحل فوق نوبت به ایجاد یک حلقه برای نمایش تک تک رکوردها می‌رسد. بنابراین حلقه‌ی while را برای هر ردیف از جدول تکرار می‌کنیم:

while($data = mysqli_fetch_row($sql))  
{  
    echo '  
    <tr>  
    <td>'.$data[0].'</td>  
    <td>'.$data[1].'</td>  
    <td>'.$data[2].'</td>  
    </tr>  
    ';  
}

۵-۳) سپس یک دکمه یا لینک جهت استخراج اطلاعات به فرمت اکسل در انتهای فرم قرار می‌هیم:

<a href="UserReport_Export.php"> Export To Excel </a>

در نهایت کد تکمیل شده شما در فایل UserReport.php باید به صورت زیر باشد:

<html>  
  
<head>  
    <title>User Detail Report</title>  
	<meta http-equiv="content-type" content="text/html; charset=utf-8">
  
<body>  
    <table border="1">  
        <tr>  
            <th>Sr NO.</th>  
            <th width="120">User Name</th>  
            <th>Password</th>  
        </tr>  
        <?php  
		
$conn = new mysqli('localhost', 'root', '');   
mysqli_select_db($conn, 'test');   
$conn->set_charset("utf8");
  
$sql = mysqli_query($conn,"SELECT `ur_Id`,`ur_username`,`ur_password` FROM `tbl_user`");  
  
  
while($data = mysqli_fetch_row($sql)){  
echo '  
<tr>  
<td>'.$data[0].'</td>  
<td>'.$data[1].'</td>  
<td>'.$data[2].'</td>  
</tr>  
';  
}  
?>  
    </table> <a href="UserReport_Export.php"> Export To Excel </a>
</body>  
  
</html>

با اجرای مسیر لوکال پروژه خود باید با همچین صفحه ای مواجه شوید:

ایجاد صفحه نمایش کاربران

همانطور که ملاحظه می‌کنید در حال حاضر یک جدول با مقادیر خوانده‌شده از دیتابیس در اختیار دارید. همچنین یک دکمه یا لینک برای استراج اطلاعات در اختیار شما قرار گرفته است. اما هنوز پروژه ما به اتمام نرسیده است. زیرا فایل UserReport_Export.php ایجاد نشده تا فرامین موردنظر جهت استخراج صحیح در آن قرار بگیرد.

۴) فایل دیگری تحت عنوان UserReport_Export.php ایجاد کرده و کد زیر را درون آن قرار دهید:

<?php  
?>

۱-۴) ابتدا یک کانکشن (ارتباط) با MySQL جهت فراخوانی اطلاعات و واکشی داده‌ها مانند مراحل قبل ایجاد می‌کنیم:

$conn = new mysqli('localhost', 'root', '');   
mysqli_select_db($conn, 'EmployeeDB');   
  
$setSql = "SELECT `ur_Id`,`ur_username`,`ur_password` FROM `tbl_user`";  
$setRec = mysqli_query($conn,$setSql);

در مجموعه کد بالا متغییری تحت عنوان setRec$ جهت دریافت تمام اطلاعات دیتابیس ایجاد شده است.

۲-۴) سپس یک حلقه‌ی while برای دریافت هر ردیف این اطلاعات استفاده می‌شود:

while($rec = mysqli_fetch_row($setRec))   
{  
}

۳-۴) حال برای هر ستون ردیف‌های استخراج شده در مرحله‌ی ۴-۲ از یک حلقه‌ی foreach‌ استفاده کرده‌ایم:

foreach($rec as $value)   
{  
}

۴-۴) سپس برای ایجاد یک تب (tab) بین مقدار هر ستون از علامت t\ استفاده کرده و آن را در یک متغییر دیگری به نام rowData$ ذخیره می‌کنیم:

$rowData = '';  
foreach($rec as $value)   
{  
   $value = '"' . $value . '"' . "\t";  
   $rowData .= $value;  
}

۵-۴) هم اکنون اطلاعات شما داخل هر ستون آماده هستند. حال نوبت به جداسازی هر سطر می‌رسد که برای اینکار از کاراکتر n\ بین هر سطر استفاده خواهیم کرد:

$setData='';  
  
while($rec = mysqli_fetch_row($setRec))   
{  
   $rowData = '';  
foreach($rec as $value)   
{  
   $value = '"' . $value . '"' . "\t";  
   $rowData .= $value;  
}  
   $setData .= trim($rowData)."\n";  
}

۶-۴) ردیف‌ها و ستون‌های داده‌های ما هم اکنون به صورت منظم جدول‌بندی شده‌اند. حال باید یک متغییر جدید را برای ذخیره کردن سربرگ‌های خود ایجاد کنیم:

$columnHeader ='';  
$columnHeader = "Sr NO"."\t"."User Name"."\t"."Password"."\t";

۷-۴) هنگامیکه کاربر روی لینک Export to Excel کلیک می‌کند باید فایل اکسل را عینا مشابه جدولی که در صفحه ایجاد شده است دریافت کند بنابراین از سربرگ‌های زیر زیر جهت تعریف فایل،دانلود و تنظیم کردن فایل برای فرمت UTF-8 در ابتدای کدهای خود استفاده می‌کنیم:

header("Content-type: application/octet-stream");  
header("Content-Disposition: attachment; filename=User_Detail_Reoprt.xls");  
header('Content-Transfer-Encoding: binary');
header("Pragma: no-cache");  
header("Expires: 0");  

echo chr(255).chr(254).iconv("UTF-8", "UTF-16LE//IGNORE", $columnHeader . "\n" . $setData . "\n");

exit()

در صورتیکه مراحل فوق را به درستی انجام داده باشید باید فایل نهایی شما به صورت زیر باشد:

<?php  
  
$conn = new mysqli('localhost', 'root', '');  
mysqli_select_db($conn, 'test');  
$conn->set_charset("utf8");
  
$setSql = "SELECT `ur_Id`,`ur_username`,`ur_password` FROM `tbl_user`";  
$setRec = mysqli_query($conn, $setSql);  
  
$columnHeader = '';  
$columnHeader = "Sr NO" . "\t" . "User Name" . "\t" . "Password" . "\t";  
  
$setData = '';  
  
while ($rec = mysqli_fetch_row($setRec)) {  
    $rowData = '';  
    foreach ($rec as $value) {  
        $value = '"' . $value . '"' . "\t";  
        $rowData .= $value;  
    }  
    $setData .= trim($rowData) . "\n";  
}  
  
 
header("Content-type: application/octet-stream");  
header("Content-Disposition: attachment; filename=User_Detail_Reoprt.xls");  
header('Content-Transfer-Encoding: binary');
header("Pragma: no-cache");  
header("Expires: 0");  

echo chr(255).chr(254).iconv("UTF-8", "UTF-16LE//IGNORE", $columnHeader . "\n" . $setData . "\n");

exit()

?>

۵) اگر تمام مراحل فوق را به درستی انجام داده باشید و روی لینک Export to Excel کلیک کنید با تصویر زیر مواجه خواهید شد:

نمایش صفحه PHP

۶) درصورتیکه فایل User_Detail_Report.xls را با نرم افزار اکسل باز کنید تصویر زیر را مشاهده خواهید کرد:

اطلاعات خروجی در اکسل

از دوستان عزیز ممنونم که این مقاله را مطالعه کردید. امیدوارم مفید واقع شده باشد.

توجه: دوستان عزیز آموزش ویدیویی پی اچ پی (PHP) از مقدماتی تا پیشرفته به زبان فارسی + ساخت CMS مشابه وردپرس را می‌توانید با کلیک روی اینجا یاد بگیرید. (این دوره در حال برگزاری است)

دیدگاه‌های شما (9 دیدگاه)

در این قسمت، به پرسش‌های تخصصی شما درباره‌ی محتوای مقاله پاسخ داده نمی‌شود. سوالات خود را اینجا بپرسید.

حسینی

28 مرداد 1402

سلام دستتون درد نکنه واقعا . کارم رو راه انداخت

روکسو

31 مرداد 1402

خوشحال هستیم که در کنار شماییم.

محسن

09 آبان 1400

استاد واقعا تشکر من تو لوکال هاست اجرا کردم و کامل هم جواب گرفتم و ضمنا توی کد فقط نام دیتابیس خودم رو وارد کردم و کامل جواب گرفتم. تشکر از زحمتی که می کشید. فقط اگه امکانش بود که برعکس این هم اتفاق می افتاد خیلی جالب تر میشد. یعنی یک فایل اکسل داریم و با کدهای php اینپوت بشه به mysql . احتمال خیلی زیاد شی گرایی باشه ولی مشابه ش رو هنوز تو اینترنت پیدا نکردم اگر امکانش هست این آموزش رو هم قرار بدید. باتشکر

حمید

06 آذر 1399

از بیخ ایراد داره و برای یک مثال سادست

mostafa

24 بهمن 1398

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

حامد

08 خرداد 1398

سلام دوستان من دقیقا مثل این برنامه رو پیاده سازی کردم اما وقتی فایل xls رو باز می کنم excel خطا میده که نمی تونی این فایل رو باز کنی امکان آلوده بودن فایل هست. میشه راهنماییم کنید؟

محمد

22 اردیبهشت 1397

سلام ببخشید من تو هاست واقعی امتحان کردم جواب نداد یعین وقتی به صفحه مورد نظر انتقال پیدا می کرد تا فایل اکسل را دانلود کنند ارور میداد که این فایل (صفحه مورد نظر) مرور گر پیدا نکرده است بعد بررسی کردم مشکل از کد خط اخر است باید چی کارکنم echo chr(255).chr(254).iconv("UTF-8", "UTF-16LE//IGNORE", $columnHeader . "\n" . $setData . "\n"); exit()

ارزو

27 دی 1396

سلام من میخام اطلاعات اکسل را وارد پایگاه داده mysql کنم لطفا کمک کنیییید؟؟؟؟؟؟؟؟؟؟؟؟///

علی

06 دی 1396

سلام از این کد استفاده کردم اما در خروجی اکسل فقط عنوان ستون هارو نمایش می دهدعلت چیست؟ممنون میشم کمک کنید.

07 آذر 1396

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

همراهان گرامی فارسی زبان باید توجه داشته باشند که جهت دستیابی به خروجی فارسی باید همواره دیتابیس را در حالت utf8_general_ci ایجاد کرده باشند و حتما کد set_charset را به مجموعه کد خود اضافه کنند.