آموزش تبدیل داده‌های MySQL به Excel توسط PHP

16 اسفند 1395
main-export-to-excel

با سلام و احترام، با یکی دیگه از مقاله‌های آموزشی شرکت روکسو در خدمت شما عزیزان هستیم. در این مقاله قصد داریم نحوه‌ی خروجی گرفتن از داده‌های موجود در پایگاه داده‌ی 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 مشابه وردپرس را می‌توانید با کلیک روی اینجا یاد بگیرید. (این دوره در حال برگزاری است)

آموزش مقدماتی تا پیشرفته PHP7 به همراه ساخت CMS اختصاصی مشابه وردپرس

نویسنده شوید

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

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

حمید
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
سلام من این کد شما رو استفاده کردم ولی بازم در خروجی اکسل متن های فارسی پایگاه داده را بهم ریخته نشوم میده ؟؟راهنمایی می کنید؟؟

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

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

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

محمد امیر
14 اسفند 1396
سلام من هر دوی این کارها رو انجام دادم ولی بازم خروجی کلمات فارسی اینجوریه "ظ…ط¯غŒط±غŒطھ" میشه بگید باید چیکار کنم ؟ ممنون

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

danielnv
14 خرداد 1398
اگه کد های مربوط به Header رو بعد از <?php اول صفحه قرار بدین مشکل بهم ریختگی فونت فارسی حل میشه

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