خواندن داده از شیتهای یک فایل اکسل و ترکیب آنها در یک جدول در کلیکویو
بسیاری از شرکتها برای اطلاعات مورد نیازشان از اکسل و شیتهای آن استفاده میکنند. اکسل قابلیتهای بیشماری در نگهداری دادهها، آنالیز و ارزیابی آنها دارد. از دستورهای آماری چون sum و average و ... که با آنها میتوان اطلاعات آماری چون میانگین و انحرافمعیار و از این قبیل بدستآورد و یا دستورهای lookup که امکان جستجوی دادهی خاصی در انبوهی از دادهها به ما میدهد و یا دستورهای مهندسی که این امکان را فراهممیکند تا تابعهای مهندسی مانند تابع بسل و یا عددهای مختلط بر روی داده سلولهای اکسل بکارگرفت. دراینجا هدف آموزش اکسل نیست اما فرض کنید که در یک شرکت، اطلاعات فروش درون یک فایل اکسل ذخیره شده باشد، البته نه در یک شیت بلکه در چندین شیت مختلف. در گذشته نحوه اتصال به یک فایل اکسل و دریافت اطلاعات از یک شیت(Sheet) را آموزش دادهایم. اگر از دو یا چند شیت مجزا(تعداد شیتها محدودباشد) بخواهیم اطلاعات را دریافت کنیم با استفاده از دستورLOAD به تعداد شیتها میتوان اطلاعات را خواند، اما نکته اینجاست که گاهی تعداد شیتها بسیار زیاد میباشد و یا تعداد شیتها متغیر بوده و امکان دارد با گذشت زمان کم و زیاد شود حال چه باید کرد و یا حتی از آن بدتر نام شیتها متغیر باشند(چرا که دستور LOAD از نام شیت استفاده میکند). بهراستی در این وضعیت چهباید کرد؟
با یک مثال مسئله بازتر خواهد شد: فرض کنید اطلاعات فروش برای هر بازاریاب در یک شیت جدا ذخیرهشده باشد و مثلا اگر یک بازاریاب روزی مرخصی بود، هیچ شیتی برای آن بازاریاب در فایل اکسل وجود نخواهد داشت. پس در عمل اگر روزی 5 بازاریاب سرکارباشند ما 5 شیت مجزا داریم و اگر روزی 4 بازاریاب داشتهباشیم(یکی مرخصی باشد) فقط 4 شیت خواهیم داشت.
اگر شیتها تعدادی مشخص داشت و یا حداکثر آن مشخص بود میشد با تعدادی دستور LOAD کار را تمام کرد ولی در این حالت که هیچ محدودیتی برای تعداد شیتها وجود ندارد چهکاری میشود کرد؟
روش زیر یک راه میانبر است که بسته به نوع فایل اکسل(xls یا xlsx یا ...) و یا حتی با ورژنهای مختلف آفیس و یا کلیکویو ممکن است لازم باشد تغییراتی در کد فوق داده شود.
نخست فایل اکسل فوق را دانلود کنید(ورژن آفیس برای فایل فوق 2010 میباشد) و آن را درون یک فولدر قرار دهید. برنامه کلیکویو را باز کنید و یک فایل جدید ایجاد و در فولدر(محل ذخیره فایل اکسل) ذخیره نمایید. سپس کد زیر را درون بخش اسکریپتادیت قرار دهید.
فایل کلیکویو را ذخیره و بارگذاری نمایید(اسکریپت را اجرا کنید). پس از بارگذاری با انتخاب فیلدهای Visitorو Product و Value خواهید دید نام سه شیت فایل اکسل با فیلدی بنام Visitor جایگزین شده است.
همانطور که در تصویر بالا میبینید نام جدولهای مختلف فایل اکسل در یک فیلد قرارگرفتهاست و هر سه فیلد Visitor و Product و Value درون جدول Visitors قرار دارند.
برای فهم بهتر، تکتک خطهای اسکریپت بالا را شرح میدهیم.
- QvWorkPath: یک متغیرداخلی کلیکویو است که مقدار آن مسیر فایل فعلی کلیکویو میباشد.
- Let: یک متغیر تعریف میکند که در اینجا مقدار آن مسیر و نام فایل اکسل ما میباشد.
- ODBC CONNECT TO: یک خط ارتباطی با یک دیتابیس ایجاد میکند(توجه شود که در اینجا این دیتابیس، همان فایل اکسل میباشد).
- SQLtables: لیستی از جداول دیتابیسی که با آن ارتباط برقرار کردهایم را برمیگرداند(این یک متغیر درونی کلیکویو میباشد).
- DISCONNECT: این دستور باعث میشود شما بتوانید ارتباط با دیتابیس را قطع کنید(توجه این ارتباط با پایان اجرای اسکریپت بهصورت خودکار قطع میشود، اما اگر سیستمهای دیگری درحال کار با دیتابیس هستند بهتر است از این دستور استفادهنمایید).
- NoOfRows: تعداد رکوردهای جدول را برمیگرداند(در اینجا جدول همان جدول نام و مشخصات شیتهای فایل اکسل میباشد).
- PEEK: کار این دستور دریافت اطلاعات یک فیلد خاص از یک شماره رکورد خاص از یک جدول میباشد، این دستور به صورت زیر است peek(fieldname, recordnumber, tablename) که در آن tablename نام جدول، recordnumber شماره رکورد و fieldname نام فیلد جدول میباشد.
- purgeChar: از یک رشته(string) یکسری کاراکتر را حذف میکند، شما میتوانید با استفاده از دستور replace این دستور را شبیهسازی کنید(البته به زحمتش نمیارزد).
- replace: در یک رشته(string) یک حرف را با حرفی دیگری جایگزین میکند.
سوال از شما: فرض کنید که در سطر نخست هر شیت نام فارسی بازاریاب قرار دارد و شما میخواهید بجای مثلا نام Bahram از بهرام فرهمند استفاده کنید یعنی هر شیت اکسل شما مشابه عکس زیر باشد:
بنظر شما این کار چگونه عملی است؟ توجه کنید نام خود شیت را نمیخواهیم تغییر دهیم بلکه نام در درون یک فیلد از شیت قرار دارد.
فایلهای مطلب
خواندن از شیتهای مختلف یک فایل اکسل (157.75 کیلو بایت)
