در بخشی از آخرین پروژه ایی که این روزها در حال نوشتن آن هستم لازم بود قابلیت Mail Merge مایکروسافت ورد، تحت وب پیاده سازی شود. بعبارتی کاربر یک الگو در قالب فرمت doc یا docx ایجاد می نمود و بعد از آنالیز الگو، داده های متناظر از یک فایل با فرمت اکسل در بلوک ها قرار می گرفت و فایل نهایی آماده سازی می شد. به واسطه فیلتر سازی داده های فایل اکسل و استفاده بهینه از کد راهی مناسب تر از ایمپورت داده ها از قالب Excel به Mysql پیدا نکردم. اصولاً بکارگیری نرم افزارهای آفیس مایکروسافت در محیط وب با PHP، بخصوص ویرایش فایل های ساخته شده با این مجموعه کار ساده ایی نیست. هرچند استفاده از COM در این موارد چاره ساز است اما محدودیت به سیستم عامل ویندوز را به همراه خواهد داشت.
ساده ترین راه برای انتقال داده ها، ذخیره فایل اکسل با فرمت های xls یا xlsx در قالب فرمت csv توسط مایکروسافت اکسل و ایمپورت آن به Mysql بود. این فرمت خلاصه شده عبارت comma-separated values ( مقادیر جدا شده با کاما) می باشد که داده ها را بصورت جدولی در یک فایل متنی که می توان آن را با یک وبرایش گر متن باز کرد ذخیره می کند. در حقیقت شماره خطها به ردیف های جدول و داده های جدا شده با کاما به فیلدها اشاره دارند. اما تبدیل به csv توسط اکسل UTF-8 را به خوبی پشتیبانی نمی کند و داده های فارسی در تبدیل قابل استفاده نیستند و اینکه می بایست کاربر عملیات تبدیل را خودش انجام می داد و فایل csv را برای ایمپورت وارد می کرد در حالی که استفاده از فایل اکسل با فرمت معمولی xls یا xlsx ارجحیت داشت.
خوشبختانه کلاس PHPExcel به اندازه ایی خوب و کامل نوشته شده بود که دغدغه نوشتن کلاس جدید را نداشتم. این کلاس که بر پایه استانداردهای OpenXML مایکروسافت نوشته شده است قابلیت نوشتن و خواندن از فایل Excel را براحتی فراهم می کند. برای وارد کردن اطلاعات از فایل Excel به Mysql تکه کد زیر را نوشتم:
<?php
require_once 'PHPExcel.php';
$objReader = new PHPExcel_Reader_Excel2007();
$objPHPExcel = $objReader->load('book1.xlsx');
$rowIterator = $objPHPExcel->getActiveSheet()->getRowIterator();
$skip_rows = 0;
$excell_array_data = array();
foreach($rowIterator as $row){
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false);
if($skip_rows >= $row->getRowIndex ()) continue;
$rowIndex = $row->getRowIndex ();
$excell_array_data[$rowIndex] = array();
foreach ($cellIterator as $cell) {
$excell_array_data[$rowIndex][$cell->getColumn()] = $cell->getCalculatedValue();
}
}
$link = @mysql_connect('localhost', 'root', '123');
if ($link){
$db_selected = mysql_select_db('alvanweb.com', $link);
@mysql_set_charset('utf8',$link);
//Create Database table with one Field
$sql = "CREATE TABLE xlsx (
rowID INT NOT NULL ,
PRIMARY KEY (rowID)
)";
mysql_query($sql);
//Create Others Field (A, B, C & ...)
$columns_name = array();
$columns_name = $excell_array_data[$skip_rows+1];
foreach (array_keys($columns_name) as $fieldname ){
$sql = "ALTER TABLE xlsx ADD $fieldname VARCHAR(1000)";
mysql_query($sql);
}
//Insert Excel data to MySQL
foreach( $excell_array_data as $k=>$v){
$keys = join(array_keys($v), ',');
$values = join($v, "','");
$sql = "insert into xlsx (rowID, $keys) values ($k, '$values') " ;
mysql_query($sql);
}
} else {
echo "Error in database connection.";
}
?>
مستندات PHPExcel کامل است و نیاز به توضیح متدها نیست. ورود اطلاعات توسط این کد در چهار مرحله انجام می شود:
1- ریختن داده های فایل اکسل در آرایه $excell_array_data (خطوط 9 تا 19)
2- اتصال به پایگاه داده و ایجاد جدول xlsx در دیتابیس و افزودن فیلد rowed به عنوان PRIMARY KEY (خطوط 21 تا 31)
3- افزودن فیلدهای مربوط به نام ستون های فایل اکسل (A، B، C و …) به جدول xlsx (خطوط 34 تا 39)
4- خواندن آرایه $excell_array_data و افزودن داده ها به جدول ( خطوط 42 تا 47)
توضیح اضافه اینکه $skip_rows برای رد کردن ردیف ها بالای فایل اکسل مقداردهی می شود. معمولاً اولین ردیف قبل از داده ها معرف نام ستون هایی است که گویااطلاعات نوع فیلد است (فرضاً سن، جنسیت و …) برای رد کردن این ردیف این متغیر برابر 1 تنظیم می شود.
من یک مسافرم
به به سلام
چه عجب آ مرتضی
مطلبت آموزنده بود
مرسی
چه خبر از سربازی؟ D:
July 10, 2011 at 10:55 pm
صالح سوزنچی
مهندس ممنون از آپدیتت. مثل همیشه عالی بود
July 11, 2011 at 2:13 am
پیام
ممنون
July 13, 2011 at 11:31 pm
علی
با سلام
خوشحالم که می بینم دوباره دارید می نویسید.
استفاده کردیم
لطفا ادامه دهید
با تشکر
July 16, 2011 at 7:37 pm
مرتضی الوانی
اگر با خطای Fatal error: Class ‘ZipArchive’ not found روبرو شدید راه حل:
فایل های آفیس 2007 به بالا در واقع یک نوع فایل فشرده zip هستن که از چند فایل تشکیل شدن. این کلاس برای خواندن فایل باید اون رو از حالت فشرده خارج کنه. برای این منظور شما به یک کلاس رابطی بنام ZipArchive نیاز دارید. این کلاس بصورت یک افزونه (extension) به نام php_zip ارائه می شه. اگر به مستندات خود کلاس PHPExcel هم رجوع کنید می بینید که این افزونه برای فایل های XLSX پیش نیاز و لازم است. شما باید روی سرور این افزونه رو نصب یا فعال کنید.
July 22, 2011 at 4:16 pm
Enayati
این خطا رو میگیره:::
Fatal error: Uncaught exception ‘Exception’ with message ‘Could not open book1.xlsx for reading! File does not exist.’ in C:\xampp\htdocs\PHPExcel\Classes\PHPExcel\Reader\Excel2007.php:358 Stack trace: #0 C:\xampp\htdocs\ex\excel2.php(4): PHPExcel_Reader_Excel2007->load(‘book1.xlsx’) #1 {main} thrown in C:\xampp\htdocs\PHPExcel\Classes\PHPExcel\Reader\Excel2007.php on line 358
September 8, 2011 at 5:57 pm
parmis amini
این کد رو در کدام قسمت جوملا اجرا کنم.
September 12, 2011 at 2:57 pm
maryam
mer30000
October 7, 2011 at 11:15 am
احمد بالوی پور
با تشکر از مقاله خوبتون
October 29, 2011 at 12:06 pm
الهام
خيلي ممنون
November 6, 2011 at 5:47 pm
مریم
سلام
ممنون از مطلب مفیدتون
من برعکس این برنامه رو لازم دارم یعنی ذخیره کردن داده های یک جدول در یک فایل excel ، ممنون میشم اگه کمک کنید.
December 10, 2011 at 10:27 pm
مينا
با سلام . من در جوملا افزونه اي نصب كردم كه اين افزونه يك فايل csv بعنوان ورودي نياز دارد. در واقع من در اكسل جدول مربوطه را ساختم و سپس آنرا تبديل به csvكردم اما مشكلي كه هست موقع نمايش كاراكترهاي فارسي را به صورت ؟؟ نمايش ميدهد .بعد از سرچ در نت به سايت شما رسيدم .حالا من چطور بايد با اين كد شما اين تبديل را انجام دهم من مبتدي هستم لطفا راهنمايي كنيد خيلي نياز دارم
December 24, 2011 at 12:10 am
مرتضی الوانی
سلام
در متن توضیحات نوشته ام که مایکروسافت اکسل در تبدیل به فرمت CSV فارسی را پشتیبانی نمیکنه. بنابراین شما باید در سیستم خودتون از افزونه ایی استفاده کنید که ورودی دیگری به جز این فرمت رو قبول کنه یا اینکه با یه نرم افزار غیر اکسل مایکروسافت این تبدیل رو انجام بدین که کاراکترها به یونیکد تبدیل بشن که متاسفانه من همچین نرم افزاری سراغ ندارم.
December 24, 2011 at 1:32 pm
پریا
salam duste aziz chnata soal dashtam azatun emkanesh hast man niaze mobrami be dorost karfdane narmafzari daram ke bayad tu exel ejra mish eva tu net ba shoma ashna shodam . mamnun misham
December 28, 2011 at 8:22 pm
محمد
دوستان گرامی بعضی وقتا نیاز هست که پس از import به mysql دیتا رو نشون داد که ممکن فونت ها خراب دیده بشن ، واسه همین کافی کد زیر ابتدا صفحه قرار دهید:
May 21, 2012 at 10:21 am
hossein
سلام به همه من مشکل نخوندن و ایمپورت کلمات فارسی به صورت ؟؟؟؟ را در my sql حل کردم darkoob444.blogfa.com حتما سر بزنید
June 13, 2012 at 10:16 pm
آموزش وارد کردن اطلاعات از Excel به MySQL با PHP - وبلاگ ایران ویکی
[…] منبع ۰ پیگیری وبلاگ ایران ویکی Twitter , Facebook. آگاه سازی از بروز رسانی با RSS […]
August 15, 2012 at 4:42 pm
مرتضی
سلام
راه حل مشکل
Fatal error: Uncaught exception ‘Exception’ with message ‘Could not open book1.xlsx for reading! File does not exist.’ in C:\xampp\htdocs\PHPExcel\Classes\PHPExcel\Reader\Excel2007.php:358 Stack trace: #0 C:\xampp\htdocs\ex\excel2.php(4): PHPExcel_Reader_Excel2007->load(‘book1.xlsx’) #1 {main} thrown in C:\xampp\htdocs\PHPExcel\Classes\PHPExcel\Reader\Excel2007.php on line 358
می توانید برام بیان کنید و اینکه من فایل اکسل رو خواندم و به پایگاه اضافه کردم ولی چند روزه که دیگه این کد اجرا نمی شه و صفحه ای که این کدها رو دارد تو سرور پیدا نمی کنه ممنون می شم راهنمایی کنید.
با تشکر از راهنمایی هاتون
August 24, 2012 at 12:50 pm
فناوران گستر
برای نوشتن انتهای فایل اکسس ایا دستورشو دارید؟؟؟؟
February 4, 2013 at 12:09 pm
حامد
سلام
ممنون خیلی کاربردی بود
برای آپدیت کردن ،وقتی لیست اکسل تغییر میکند، چه دستوری بدیم تا دیتابیس هم تغییر کند؟
April 8, 2013 at 1:16 pm
ali
سلام .ممنون بابت موضوع.. کلاس phpexcel رو هر کاری کردم دانلود نشدامکان ارسال این کلاس به ایمیل هست؟
August 2, 2013 at 11:26 am
cms
بسیار عالی سایتتون خوبه بسیار کمک کرد
موفق باشید
April 21, 2014 at 3:42 pm
فایل خودرو
مرسی از مطالب مفیدتون
May 24, 2014 at 12:52 pm
امیر فرهادی
ممنون مطب مفیدی بود
موفق باشی دوست عزیز
January 24, 2015 at 9:54 am
آموزش php
من که به شدت به سایتتون علاقه مند شدم.هرچند تازه با سایت شما آشنا شدم اما کار شما بی نظیره واقعا.مرسی از سایت عالیتون.
سایت شما رو به همه دوستانم معرفی می کنم.
March 11, 2016 at 7:34 pm
علی محمد شیرازی
با سلام
ممنون از اشتراک مطالب.
یک فایل اکسل با حدود 400 ستون داریم که میخواهیم فقط تعدادی خاص از ستون ها به دیتابیس وارد شوند.مثلا ستون های A,D,F,H,AZ,BB
انتخاب این ستونها در کلاس PHPEXCEL چگونه است؟
مستندات را خواندم ولی خیلی متوجه نشدم.
آیا راه راحت تری هم وجود دارد؟
ممنون از پاسخگویی شما
July 12, 2016 at 9:27 am
وحید
خیلی خیلی مفید بود تشکر فراوان.
July 17, 2016 at 9:33 pm
سایت ساز
سلام. با تشکر از سایت جالب تون. موفق باشید
November 8, 2016 at 3:34 pm
پیامک صوتی
سایت خوب و کاملی دارین . ممنونم
November 8, 2016 at 3:35 pm
mahdi
نمیدونم چرا متن ناقض کپی میشه فکر کنم محدودیت کاراکتر داره
January 25, 2017 at 7:25 pm
mahdi
لینگ ویدئو و سورس او فایل رو میزارم تغییرات با خودتون
https://www.youtube.com/watch?v=QPBgFiRtCJ4
http://drive.google.com/open?id=0B1KkmsYoMNtRVzBKZkR6T0hWQ00
January 25, 2017 at 7:28 pm
فروش گارمین
با سلام و تشکر. موفق و پیروز باشید.
April 18, 2017 at 7:56 am
hamid
لینک دانلود PHPexcel.php
https://github.com/PHPOffice/PHPExcel/archive/1.8.zip
November 17, 2017 at 8:05 pm
مجله پزشکی داروینه
سلام خیلی عالی بود مرسی
January 3, 2018 at 9:31 pm
محسن اسماعیلی
عالی بود ممنون
April 13, 2018 at 2:00 am
طراحی سایت
خیلی عالی بود
September 5, 2020 at 4:50 pm
خرید لباس بارداری
بسیار مفید واقع شد / ممنون
December 22, 2020 at 9:21 am
سید علیرضا
دوستان عزیزی که مشکل وارد کردن کلمات فارسی رو به دیتابیس دارند ، کد زیر را بعد از دستور اتصال به دیتابیس بنویسید 99% مشکلتون حل میشه:
$sql2=”SET CHARACTER SET utf8″;
if($link->query($sql2)===true){}
April 23, 2021 at 11:34 am