با در بخشی از آخرین پروژه ایی که این روزها در حال نوشتن آن هستم لازم بود قابلیت 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 کامل است و نیاز به توضیح متدها نیست. ورود اطلاعات توسط این کد در چهار مرحله انجام می شود:
۱- ریختن داده های فایل اکسل در آرایه $excell_array_data (خطوط ۹ تا ۱۹)
۲- اتصال به پایگاه داده و ایجاد جدول xlsx در دیتابیس و افزودن فیلد rowed به عنوان PRIMARY KEY (خطوط ۲۱ تا ۳۱)
۳- افزودن فیلدهای مربوط به نام ستون های فایل اکسل (A، B، C و …) به جدول xlsx (خطوط ۳۴ تا ۳۹)
۴- خواندن آرایه $excell_array_data و افزودن داده ها به جدول ( خطوط ۴۲ تا ۴۷)
توضیح اضافه اینکه $skip_rows برای رد کردن ردیف ها بالای فایل اکسل مقداردهی می شود. معمولاً اولین ردیف قبل از داده ها معرف نام ستون هایی است که گویااطلاعات نوع فیلد است (فرضاً سن، جنسیت و …) برای رد کردن این ردیف این متغیر برابر ۱ تنظیم می شود.
با نرم افزار heidisql از قسمت tools مثل آی خوردن فارسی هارو بارگزاری کنید به علاوه ی کلی امکانات بیشتر
واقعا خجالت نمیکشید که اینقد زشت کپی میکنید؟ بعدشم مینویسید که تماما از خودتونه؟
کد رو حتی یه بار از اول تا آخر خوندید؟
دوست عزیز انگار منبع رو ندیدین آخر مقاله
لینک دانلود PHPexcel.php
https://github.com/PHPOffice/PHPExcel/archive/1.8.zip
فایل در اکسل با فرمت xlsx ذخیره بشه . وقتی فایل ذخیره شد اسمش به xlsx تغیر کنه.فایل برای ارسال به جایی میخوام گفتن به این صورت باشه.
وقتی خودمان روی فایل اسم نگذاریم و با این فرمت ذخیره بشه اسمش کلن xlsx میشه .اما نمیدونم از چه طریقی؟
یعنی به این صورت باشه ؟ xlsx.xslx
نه منظور اینه که برای دیدن فرمت نیاز نیست propertiese ببینیم وقتی ذخیره میشه کنار اسم فایل میشینه.
و وقتی روی فایل اسمی نزاریم کلا xlsx میشه.
یکی گفت اکسل بدون نام در دسکتاپ ذخیره کنی این مدلی میشه اما نشد.شاید دارم پیچیده میگم اما این طوری نیست.
برای انتقال فایل به بانک هست.
چگونه فایلی در اکسل به صورت xlsx ذخیره کنیم که نامش همان xlsx شود.
لطفا ارهنمایی کنید
لطفا بیشتر توضیح بدید
سلام
ممنون از مطلب مفید
میشه با تابع load فایل رو از آدرس لود کرد؟
سلام دوست عزیز. خوشحالیم مفید بوده .
اگه خوندن فایل از url منظوره از این کد می تونین استفاده کنین :
file_put_contents('filename.xls',
file_get_contents('http://www.mysite.com/file.xls')
);
ممکنه پروزه ای که کار کردید رو به عنوان سمپل بزارید
چون من نمیدونم PHPExcel.php از کجا اوردید
و یا چطور میتونم از کلاس PHPExcel استفاده کنم
سلام.
میشه در مورد اینکه تعدادی از ستون های مورد نظر را وارد دیتابیس کنیم توضیح بدید؟مثلا یک فایل حجیم اکسل داریم که میخواهیم ستون A و D و F وارد دیتابیس بشه نه بقیه اش.ردیف ها هم هر بار متغیره و میخواهیم خود برنامه تشخیص بده.
البته تعداد ستونهای منتخب بیش از اینهاست.مثال زدم.
دیدم که phpexcel کلاس برای اینکار داره اما خوب متوجه نشدم.
آقا خیلی ممنون ولی من یه مشکلی دارم اونم اینکه من نمیتونم اطلاعات فارسی توی دیتا بیسم بریزم و مشکل utf8-encoding رو دارم اگه میشه لطفا راهنمایی کنید.
دقت کنین که collection جدول و فیلدهاتون utf 8 persian باشه . به علاوه صفحه HTML تون که داخلش insert می کنید به دیتابیس،هم باید utf-8 باشه .
سلام.
وقت بخیر.
من نتونستم از کدهایی که برای مثال انتقال اطلاعات نوشتید استفاده کنم چون با چیزی که من میخواستم کمی متفاوت بود و من هم که زیاد وارد نبودم نتونستم کامل تغییرات لازم رو اعمال کنم.
مشکل من اینجا ست که ۹۸۰۰ رکورد در اکسل دارم که لازمه به صورت یونیکد وارد MySQL بشن و تیبل هم ساخته شده و آماده ست و تعدادی هم رکورد درش ثبت شده و حالا این ۹۸۰۰ رکورد باید به اون جدول اضافه بشه.
ممنون میشم در باره تغییر کد راهنماییم کنید…. البته میدونم باید شرط if ($link){ کامل حذف بشه و در قسمت اینزرت foreach اسم جدول خودمو بنویسم اما نمیدونم چطور باید فیلد ها رو مشخص کنم، چون شما از متغییر استفاده کردید و rowID اصن چی هست :/
پیشا پیش ممنونم از محبتتون
سلام خیلی مفید واقع شد آقای زمانی مرسی
یه سوال پیش اومد:
فایلی که رکورداش تا ۶۵۵۳۶ تا هست چطوری میشه تا رکورد ۲۰ خوندش بدون اینکه PHP هنگ کنه یا execeut time error بده
بی زحمت راه حل از سمت PHP باشه نه سمت excel (مثلا پاک کردن روکورد های blank)
مرسی
سلام
ممنونم . اول فایل تون می تونین این کد رو بزارین و تست کنین
set_time_limit(0);سلام مطلب خیلی جالب و سازنده ای بود
مرسی ………..
به ما هم سر بزنید (خوشحال می شیم 🙂 )
سلام
ممنونم دوست عزیز
سایت خوب و مفیدی دارید درسته عنوان نداره سایت 🙂
موفقیت
سلام به دوستان من مشکل ارسال یا همان ایمپورت فایل اکسل در My sql با زبان php و حل مشکل encoding در خواندن اطلاعات فارسی رو با کمک یکی از دوستام حل کردم هرکس مشکل داشت بهم ایمیل بزنه باهاش همکاری میکنم تا مشکلش حل شه
من این مشکل رو دارم
آدرس ایمیل تون رو بفرمایید
با سلام
راستش من یک سئوال دارم خیلی وقت هم هست دارم دنبال جواب می گردم ولی موفق نشدم . سئوال من اینکه من یک excel sheet دارم که به یک website وصل ک به طور مرتب داده ها را که یک تعداد عدد و رقم هست را از اونجا می خونه مشکل من اینه که هر بار دادها را می خونه داده های قبلی از بین می ره چون روی همون قبلی ها ذخیره می شه آیا راهی هست که بشه داد ه های قبلی ذخیره کرد .
باتشکر
سلام
لطفا سوالات تون رو در انجمن سایت بپرسین تا بشه کامل جواب داد
با تشکر
کلاسهای زیادی برای استفاده از اکسل نوشته شده، اما کاملترینش همینی هست که توضیح دادی . ممنون
جالب بود…
ممنون علی جان
ممنون 🙂
مرسی … مفید بود … 🙂
خواهش می کنم 🙂
خیلی خوب بود.
من این رو قبلا دیده بودم اما این که یک سایت فارسی این اطلاعات را منتشر می کنه به همه در یافتن سریعش در حین جستجو کمک زیاددی میکنه.
خسته نباشید.
ممنون ، بله اگه پایین مطلب رو دقت کنین لینک منبع رو گذاشتیم 🙂