1 فایل پیوست
آموزش ایمپورت از فایل اکسل به MySql و اکسپورت از MySql به فایل اکسل
با سلام خدمت تمامی کاربران عزیز.
بنا به درخواست یکی از کاربران ، در این آموزش نحوه ایمپورت کردن از فایل اکسل به MySql وهمچنین اکسپورت کردن از MySql به فایل اکسل را خدمت شما دوستان ارائه میکنم .
توضیحات :
کدها در محیط localhost نوشته شده است.
در این آموزش جهت خواندن و نوشتن فایل اکسل از کلاس PHPExcel استفاده شده است .
در این آموزش همچنین خواهید آموخت :
- روش های ارسال اطلاعات از طریق متدهای POST و GET
- نحوه آپلود فایل و ذخیره در سرور
- روش دانلود فایل توسط مرورگر
- تولید کد خطای مناسب برای کاربر
- سوییچ بین صفحات مختلف با تگ A
- ارسال اطلاعات بین صفحات مختلف توسط متد GET
ساخت دیتابیس
ابتدا یک دیتابیس با نام db در phpmyadmin ایجاد کرده و کد اسکریپت زیر را جهت ساخت جدول مربوطه در تب sql کپی و آنرا اجرا میکنیم (go)
کد PHP:
-- phpMyAdmin SQL Dump
-- version 4.1.12
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Jan 21, 2018 at 06:02 AM
-- Server version: 5.6.16
-- PHP Version: 5.5.11
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Database: `db`
--
-- --------------------------------------------------------
--
-- Table structure for table `tbl_test`
--
CREATE TABLE IF NOT EXISTS `tbl_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(150) COLLATE utf8_persian_ci NOT NULL,
`last_name` varchar(150) COLLATE utf8_persian_ci NOT NULL,
`email` varchar(150) COLLATE utf8_persian_ci NOT NULL,
`birthday` varchar(150) COLLATE utf8_persian_ci NOT NULL,
`city` varchar(150) COLLATE utf8_persian_ci NOT NULL,
`country` varchar(150) COLLATE utf8_persian_ci NOT NULL,
`address` varchar(150) COLLATE utf8_persian_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci AUTO_INCREMENT=6 ;
--
-- Dumping data for table `tbl_test`
--
INSERT INTO `tbl_test` (`id`, `first_name`, `last_name`, `email`, `birthday`, `city`, `country`, `address`) VALUES
(1, 'alireza', 'azadi', 'a_azadi1984@yahoo.com', '1984', 'bandar abbas', 'iran', 'golshahr'),
(2, 'hassan', 'bahrami', 'h.bahrami@gmail.com', '1989', 'bandar abbas', 'iran', 'golshahr'),
(3, 'mohamad', 'beiranvand', 'm.beiranvand@yahoo.com', '1989', 'bandar abbas', 'iran', 'unknow'),
(4, 'mohsen', 'safari', 'm.safari@yahoo.com', '1985', 'bandar abbas', 'iran', 'unknow');
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
فایل کانکشن :
بعد از ایجاد جدول tbl_test یک فایل بنام cnn.php ایجاد کرده و کدهای زیر را درون آن کپی میکنیم . این فایل ارتباطمون را با دیتایس برقرار میکنه .
نکته : بجای نام کاربری و پسورد در کد زیر نام کاربری و پسورد دیتابیس خودتون رو قرار دهید .
کد PHP:
<?php
date_default_timezone_set("Asia/Tehran");
$host = 'localhost';
$db_name = 'db';
$db_user = 'نام کاربری دیتابیس';
$db_pass = 'پسورد دیتابیس';
$pdo = new PDO("mysql:host={$host};dbname={$db_name}", $db_user, $db_pass);
?>
فایل واسط برای آپلود فایل توسط کاربر جهت ایمپورت
یک فایل با نام import.php ایجاد و کدهای زیر را در آن کپی میکنیم
این فایل واسط کاربریمون برای آپلود فایل توسط کاربر و خواندن فایل فوق و ایمپورت (وارد کردن) آن به دیتابیس هست.
کد PHP:
<?php
if(isset($_GET['template']))
{
$file = './template.xlsx';
header("Content-type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header("Content-Disposition: attachment; filename=". basename($file));
header("Content-Length: " . filesize($file));
header("Pragma: no-cache");
header("Expires: 0");
readfile( $file );
}
?>
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<meta name="Description" content="import to excel">
<meta name="Keywords" content="import, xlsx, excel">
<meta name="author" content="a.azadi">
<title>Untitled Document</title>
<style>
*{margin: 0;padding: 0;}
.container{ margin: auto;position: absolute;top: 0;bottom: 0;left: 0;right: 0;width: 50%;height: 40%;}
#imp_sec form{line-height: 25px;}
a{text-decoration: none;}
</style>
</head>
<body>
<div class="container" >
<div id="imp_sec">
<div style="text-align: center;">
<?php
if(isset($_GET['code']))
{
$msg = '';
switch($_GET['code'])
{
case 100:
$msg = 'file import successfully';
break;
case 110:
$msg = 'error : unknow error in upload file';
break;
case 120:
$msg = 'error : file extension is not valid';
break;
case 130:
$msg = 'error : file size is not valid / maximum 5mb';
break;
case 140:
$msg = 'error : File not uploaded';
break;
case 150:
$msg = 'error : File not uploaded';
break;
case 200:
$msg = 'error : the excel library not found';
break;
case 210:
$msg = 'error : input file name is not valid';
break;
default:
$msg = 'error : unknow error';
break;
}
$color = ($_GET['code'] == 100) ? '#008000' : '#f00';
echo "<p style='color:{$color};padding:20px 10px;font-size:18px;text-transform: capitalize;'>{$msg}</p>";
}
?>
</div>
<fieldset style="border-radius: 4px;padding: 0 10px;">
<legend style="padding: 0 10px;">import from file</legend>
<form action="run_import.php" method="post" enctype="multipart/form-data" name="frm_imp" style="padding: 40px 25px;">
<span style="color: #f00;font-size: 12px;line-height: 35px;">Allowed files : xls , xlsx</span>
<div>
<input type="file" name="file">
</div>
<a href="?template=1">template</a>
<div style="float: right;margin-top: 15px;">
<button style="padding:2px 10px;">Import</button>
</div>
</form>
</fieldset>
</div>
<div style="float: right;">
<a href="export.php" style="padding:0 10px; " >goto export to file</a>
</div>
</div>
</body>
<script>
</script>
</html>
فایل دریافت و خواندن فایل کاربر و دستورات ذخیره سازی
یک فایل با نام run_import.php ایجاد و کدهای زیر را در آن قرار میدهیم .
کد PHP:
<?php
require_once('cnn.php');
$excel_library_path = 'PHPExcel/Classes/PHPExcel/IOFactory.php';
if( ! file_exists($excel_library_path))
{
header('Location:import.php?code=200');
exit();
}
require_once($excel_library_path);
if ( ! isset($_FILES['file']))
{
header('Location:import.php?code=210');
exit();
}
$file = $_FILES['file'];
$file_name = $file['name'];
$file_type = $file['type'];
$file_err = $file['error'];
$file_size = $file['size'];
$file_tmp = $file['tmp_name'];
if($file_err)
{
header('Location:import.php?code=110');
exit();
}
$ext = pathinfo($file_name, PATHINFO_EXTENSION);
$ext = strtolower($ext);
if( ! in_array($ext, array('xls','xlsx')))
{
header('Location:import.php?code=120');
exit();
}
if($file_size > 50000)
{
header('Location:import.php?code=130');
exit();
}
$target_dir = "uploads/";
$target_file = $target_dir . basename($file_name);
$valid = true;
if( ! is_dir($target_dir))
{
$valid = mkdir($target_dir,'0755');
}
if ( ! ($valid && move_uploaded_file($file_tmp, $target_file)))
{
header('Location:import.php?code=140');
exit();
}
$file = $target_file;
$objPHPExcel = PHPExcel_IOFactory::load($file);
$sheet = $objPHPExcel->getSheet(0);
$total_rows = $sheet->getHighestRow();
$total_columns = $sheet->getHighestColumn();
$fields = array('first_name' , 'last_name', 'email', 'birthday', 'city', 'country', 'address');
$sql = "insert into tbl_test ";
$sql .= '(`'.implode('`,`' , $fields).'`)';
$sql .= 'values ('.rtrim(str_repeat('?,', count($fields)), ',').')';
$stmt = $pdo->prepare($sql);
$error = false ;
for($row = 2; $row <= $total_rows; $row++)
{
$row_data = $sheet->rangeToArray('A' . $row . ':' . $total_columns . $row, NULL, TRUE, FALSE);
$res = @$stmt->execute($row_data[0]);
if( ! $res)
{
$error = true;
}
}
unlink($target_file);
if($error)
{
header('Location:import.php?code=150');
exit($res);
}
header('Location:import.php?code=100');
exit();
?>
فایل واسط نمایش اطلاعات درون دیتابیس
یک فایل با نام export.php ایجاد کرده و کدهای زیر را درون آن کپی میکنیم
این فایل اطلاعات درون جدول tbl_test که ایجاد کردیم را نمایش ودر صورت نیاز از آن خروجی اکسل میگیریم .
کد PHP:
<?php require_once('cnn.php'); ?>
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<meta name="Description" content="export to excel">
<meta name="Keywords" content="export, xlsx, excel">
<meta name="author" content="a.azadi">
<title>Untitled Document</title>
<style>
*{margin: 0;padding: 0;}
html,body
{
margin:0;
padding:0;
height:100%;
}
.header{width: 100%;background-color: #B7C4ED;height: 50px;}
.container{ margin: auto;position: absolute;top: 0;bottom: 0;left: 0;right: 0;width: 80%;height: 90%;}
.footer{width: 100%;background-color: #B7C4ED;height: 50px;}
.tbl{table-layout: fixed;border-collapse: collapse;}
.tbl td, .tbl th {border: 1px solid #333;height: 25px;line-height: 25px; text-align: center;padding: 0 5px;white-space: nowrap;}
.tbl thead tr:nth-child(1){background-color: #CFBCFF;}
.tbl tbody tr:nth-child(even){background-color: #f4feff;}
.tbl tbody tr:nth-child(odd){background-color: #fffded;}
.tbl th {color:#1D4B7B;text-transform: capitalize;}
a{text-decoration: none;text-transform:capitalize; }
</style>
</head>
<body>
<div class="container">
<div style="padding: 40px;">
<table class="tbl">
<?php
$stmt = $pdo->prepare('select * from tbl_test');
$stmt->execute();
$res = $stmt->fetchAll(PDO::FETCH_OBJ);
?>
<?php if($res){ ?>
<thead>
<tr>
<th style="width: 3%;">row</th>
<th style="width: 12%;">first name</th>
<th style="width: 12%;">last name</th>
<th style="width: 17%;">email</th>
<th style="width: 12%;">birthday</th>
<th style="width: 12%;">city</th>
<th style="width: 10%;">country</th>
<th style="width: 22%;">address</th>
</tr>
</thead>
<tbody>
<?php foreach($res as $key => $val){ ?>
<tr>
<td><?php echo ++$key; ?></td>
<td><?php echo $val->first_name; ?></td>
<td><?php echo $val->last_name; ?></td>
<td><?php echo $val->email; ?></td>
<td><?php echo $val->birthday; ?></td>
<td><?php echo $val->city; ?></td>
<td><?php echo $val->country; ?></td>
<td><?php echo $val->address; ?></td>
</tr>
<?php } ?>
</tbody>
<?php } ?>
</table>
<div>
<div style="float: left;padding:5px;"><a href="run_export.php">export excel</a></div>
<div style="float: right;padding:5px;"><a href="import.php">goto import from file</a></div>
</div>
</div>
</div>
</body>
<script>
</script>
</html>
فایل خواندن اطلاعات از دیتابیس و ذخیره در فایل اکسل و دانلود توسط مرورگر
یک فایل با نام run_export.php ایجاد کرده و کدهای زیر را درون آن کپی میکنیم.
کد PHP:
<?php
require_once('cnn.php');
require_once('PHPExcel/Classes/PHPExcel.php');
require_once('PHPExcel/Classes/PHPExcel/IOFactory.php');
$obj_excel = new PHPExcel();
$obj_excel->getProperties()->setCreator("a.azadi")
->setLastModifiedBy("a.azadi")
->setTitle("this a test")
->setSubject("create excel")
->setDescription("description of file.")
->setKeywords("xlsx - xls");
$active_sheet = $obj_excel->setActiveSheetIndex(0);
$stmt = $pdo->prepare('select * from tbl_test');
$stmt->execute();
$data = $stmt->fetchAll(PDO::FETCH_OBJ);
$active_sheet->setCellValue('A1', 'Row');
$active_sheet->setCellValue('B1', 'first_name');
$active_sheet->setCellValue('C1', 'last_name');
$active_sheet->setCellValue('D1', 'email');
$active_sheet->setCellValue('E1', 'birthday');
$active_sheet->setCellValue('F1', 'city');
$active_sheet->setCellValue('G1', 'country');
$active_sheet->setCellValue('H1', 'address');
$i = 1;
foreach($data as $val)
{
$i++;
$active_sheet->setCellValue('A'.$i, $val->id);
$active_sheet->setCellValue('B'.$i, $val->first_name);
$active_sheet->setCellValue('C'.$i, $val->last_name);
$active_sheet->setCellValue('D'.$i, $val->email);
$active_sheet->setCellValue('E'.$i, $val->birthday);
$active_sheet->setCellValue('F'.$i, $val->city);
$active_sheet->setCellValue('G'.$i, $val->country);
$active_sheet->setCellValue('H'.$i, $val->address);
}
$file = 'excel'.date("Y_m_d__H_i_s").'.xlsx';
$objWriter = PHPExcel_IOFactory::createWriter($obj_excel, 'Excel2007');
$objWriter->save($file);
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=". basename($file));
header("Content-Length: " . filesize($file));
header("Pragma: no-cache");
header("Expires: 0");
readfile( $file );
unlink($file);
?>
لازم به ذکر است که در کنار فایل های بالا می بایستی فولدر phpExcel که حاوی کلاسهای مرتبط برای خواندن و نوشتن فایلهای اکسل میباشند را در کنار فایل های گذاشت تا کدها به درستی اجرا شوند
نکته : فایل های فولدر phpExcel (کلاس ها) توسط برنامه نویسان حرفه ای نوشته شده اند
اینجانب فقط فایل های قسمت بالا را کدنویسی کردم
در پایان ، کدهای کامل پروژه رو واستون آپلود کردم ، امیدوارم توانسته باشم خدمتی در زمینه پیشرفت برنامه نویسی تحت وب برای شما عزیزان ارائه داده باشم.
نویسنده : [برای مشاهده لینک ها شما باید عضو سایت باشید برای عضویت در سایت بر روی اینجا کلیک بکنید]
منبع : [برای مشاهده لینک ها شما باید عضو سایت باشید برای عضویت در سایت بر روی اینجا کلیک بکنید]
