رفتن به نوشته‌ها
تبدیل MySQL‌ به Excel‌ توسط PHP

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

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

منتشر شده در برنامه نویسیPHP (پی اچ پی)