اصول برای اسکریپت های اداری در اکسل

این مقاله شما را با جنبه های فنی اسکریپت های اداری آشنا می کند. قسمتهای مهم کد اسکریپت مبتنی بر Typescript و نحوه عملکرد اشیاء اکسل و API ها را با هم یاد خواهید گرفت.

TypeScript: زبان اسکریپت های اداری

اسکریپت های اداری در TypeScript نوشته شده است ، که یک سوپراست جاوا اسکریپت است. اگر با JavaScript آشنا باشید ، دانش شما به این نتیجه می رسد زیرا بخش اعظم کد در هر دو زبان یکسان است. توصیه می کنیم قبل از شروع سفر به برنامه نویسی اسکریپت های اداری ، دانش برنامه نویسی در سطح مبتدی داشته باشید. منابع زیر می تواند به شما در درک سمت برنامه نویسی اسکریپت های اداری کمک کند.

• اصول اولیه جاوا اسکریپت را بیاموزید. شما باید با مفاهیمی مانند متغیرها ، جریان کنترل ، توابع و انواع داده احساس راحتی کنید. موزیلا یک آموزش خوب و جامع در مورد JavaScript ارائه می دهد.
• در مورد انواع موجود در TypeScript بیاموزید. TypeScript با اطمینان از زمان کامپایل که از انواع مناسب برای تماس ها و تکالیف روش استفاده می شود ، بر روی JavaScript ساخته می شود. مستندات Typescript در مورد انواع شی ، استنباط نوع و سازگاری نوع مفیدترین خواهد بود.

عملکرد اصلی: نقطه شروع فیلمنامه

هر اسکریپت باید دارای یک عملکرد اصلی با نوع excelscript. workbook به عنوان اولین پارامتر آن باشد. هنگامی که عملکرد اجرا می شود ، برنامه اکسل با تهیه کتاب کار به عنوان اولین پارامتر خود ، عملکرد اصلی را فراخوانی می کند. یک کتابچه excelscript. workbook همیشه باید اولین پارامتر باشد.

کد داخل عملکرد اصلی هنگام اجرای اسکریپت اجرا می شود. Main می تواند توابع دیگر را در اسکریپت شما فراخوانی کند ، اما کدی که در یک عملکرد موجود نیست اجرا نمی شود. اسکریپت ها نمی توانند اسکریپت های اداری دیگر را فراخوانی یا تماس بگیرند.

Power Automate به شما امکان می دهد اسکریپت ها را در جریان وصل کنید. داده ها بین اسکریپت ها و جریان از طریق پارامترها و بازده عملکرد اصلی منتقل می شود. نحوه ادغام اسکریپت های اداری با Automate Power با جزئیات در اسکریپت های Office Run با Power Automate پوشش داده شده است.

نمای کلی مدل شیء

برای نوشتن یک اسکریپت ، باید درک کنید که چگونه API های مربوط به اسکریپت ها در کنار هم قرار می گیرند. مؤلفه های یک کتاب کار با یکدیگر روابط خاصی دارند. از بسیاری جهات ، این روابط مطابق با UI اکسل است.

• یک کتاب کار شامل یک یا چند برگه است.
• یک کاربرگ از طریق اشیاء محدوده به سلول ها دسترسی می دهد.
• دامنه گروهی از سلولهای پیوسته را نشان می دهد.
• از دامنه ها برای ایجاد و قرار دادن جداول ، نمودارها ، شکل ها و سایر تجسم داده ها یا اشیاء سازمان استفاده می شود.
• یک برگه شامل مجموعه هایی از آن اشیاء داده است که در برگه جداگانه وجود دارد.
• کتابهای کار شامل مجموعه هایی از برخی از آن اشیاء داده (مانند جداول) برای کل کتاب کار هستند.

کتاب کار

هر اسکریپت توسط عملکرد اصلی یک موضوع کتاب کار از نوع کار ارائه می شود. این نشان دهنده شیء سطح بالایی است که از طریق آن اسکریپت شما با کتاب کار اکسل در تعامل است.

اسکریپت زیر برگه فعال را از کتاب کار دریافت می کند و نام آن را ثبت می کند.

دامنه

دامنه گروهی از سلولهای متناوب در کتاب کار است. اسکریپت ها به طور معمول از نماد A1 به سبک (به عنوان مثال ، B3 برای سلول منفرد در ستون B و ردیف 3 یا C2 استفاده می کنند: F4 برای سلولها از ستون های C تا F و ردیف های 2 تا 4) برای تعریف دامنه.

دامنه ها دارای سه ویژگی اصلی هستند: مقادیر ، فرمول ها و قالب. این خصوصیات مقادیر سلول ، فرمول ها را مورد ارزیابی قرار می دهند و یا قالب بندی بصری سلول ها را تنظیم می کنند. آنها از طریق GetValues ، GetFormulas و GetFormat قابل دسترسی هستند. مقادیر و فرمول ها را می توان با setValues و setFormulas تغییر داد ، در حالی که قالب یک شیء محدوده است که از چندین شیء کوچکتر تشکیل شده است که به صورت جداگانه تنظیم می شوند.

دامنه ها برای مدیریت اطلاعات از آرایه های دو بعدی استفاده می کنند. برای کسب اطلاعات بیشتر در مورد رسیدگی به آرایه ها در چارچوب اسکریپت های دفتر ، به کار با دامنه مراجعه کنید.

نمونه محدوده

نمونه زیر نحوه ایجاد سوابق فروش را نشان می دهد. این اسکریپت از اشیاء محدوده برای تنظیم مقادیر ، فرمول ها و قسمت هایی از قالب استفاده می کند.

اجرای این اسکریپت داده های زیر را در برگه فعلی ایجاد می کند:

انواع مقادیر دامنه

هر سلول دارای ارزش است. این مقدار مقدار اصلی وارد شده به سلول است که ممکن است با متن نمایش داده شده در اکسل متفاوت باشد. به عنوان مثال ، شما ممکن است "5/2/2021" را به عنوان تاریخ در سلول نمایش داده کنید ، اما مقدار واقعی 44318 است. این نمایشگر با فرمت شماره قابل تغییر است ، اما مقدار واقعی و نوع در سلول فقط وقتی تغییر می کندیک مقدار جدید تنظیم شده است.

هنگامی که از ارزش سلول استفاده می کنید ، مهم است که به TypeScript بگویید که چه مقدار را انتظار دارید از یک سلول یا دامنه دریافت کنید. یک سلول شامل یکی از انواع زیر است: رشته ، شماره یا بولی. برای اینکه اسکریپت شما مقادیر برگشتی را به عنوان یکی از این انواع رفتار کند ، باید نوع را اعلام کنید.

اسکریپت زیر در نمونه قبلی قیمت متوسط را از جدول دریافت می کند. توجه داشته باشید کد pricerange. getValues () به عنوان شماره [] []. این ادعا می کند که نوع مقادیر دامنه یک عدد است [] []. تمام مقادیر موجود در آن آرایه می توانند به عنوان اعداد موجود در فیلمنامه رفتار شوند.

نمودارها، جداول و سایر اشیاء داده

اسکریپت ها می توانند ساختارهای داده و تجسم ها را در اکسل ایجاد و دستکاری کنند. جداول و نمودارها دو مورد از مواردی هستند که بیشتر مورد استفاده قرار می گیرند، اما API ها از PivotTables، اشکال، تصاویر و موارد دیگر پشتیبانی می کنند. اینها در مجموعه هایی ذخیره می شوند که بعداً در این مقاله مورد بحث قرار خواهد گرفت.

یک جدول ایجاد کنید

با استفاده از محدوده های پر داده جداول ایجاد کنید. قالب بندی و کنترل های جدول (مانند فیلترها) به طور خودکار در محدوده اعمال می شوند.

اسکریپت زیر با استفاده از محدوده های نمونه قبلی جدولی ایجاد می کند.

اجرای این اسکریپت روی کاربرگ با داده های قبلی جدول زیر را ایجاد می کند:

یک نمودار ایجاد کنید

نمودارهایی را برای تجسم داده ها در یک محدوده ایجاد کنید. اسکریپت ها به ده ها نوع نمودار اجازه می دهند که هر کدام را می توان مطابق با نیازهای شما سفارشی کرد.

اسکریپت زیر یک نمودار ستونی ساده برای سه مورد ایجاد می کند و آن را 100 پیکسل زیر بالای صفحه کار قرار می دهد.

اجرای این اسکریپت روی کاربرگ با جدول قبلی نمودار زیر را ایجاد می کند:

مجموعه ها

هنگامی که یک شی اکسل مجموعه ای از یک یا چند شی از یک نوع دارد، آنها را در یک آرایه ذخیره می کند. به عنوان مثال، یک شی Workbook حاوی یک کاربرگ[] است. این آرایه با متد Workbook. getWorksheets() قابل دسترسی است. متدهایی که جمع هستند، مانند Worksheet. getCharts() ، کل مجموعه شی را به عنوان یک آرایه برمی گرداند. این الگو را در سراسر APIهای Office Scripts خواهید دید: شی Worksheet دارای یک متد getTables() است که یک Table[] را برمی گرداند، شی Table دارای یک متد getColumns() است که یک TableColumn[] و غیره را برمی گرداند.

آرایه برگشتی یک آرایه معمولی است، بنابراین تمام عملیات آرایه معمولی برای اسکریپت شما در دسترس است. همچنین می توانید با استفاده از مقدار شاخص آرایه به اشیاء مجزا در مجموعه دسترسی داشته باشید. برای مثال workbook. getTables()[0] اولین جدول مجموعه را برمی گرداند. برای اطلاعات بیشتر در مورد استفاده از عملکرد آرایه داخلی با چارچوب Office Scripts، به کار با مجموعه ها مراجعه کنید.

اشیاء منفرد نیز از طریق یک متد get از مجموعه قابل دسترسی هستند. متدهایی را دریافت کنید که تک هستند، مانند Worksheet. getTable(name)، یک شی واحد را برمی گرداند و به یک شناسه یا نام برای شی خاص نیاز دارد. این شناسه یا نام معمولاً توسط اسکریپت یا از طریق رابط کاربری Excel تنظیم می شود.

اسکریپت زیر همه جداول را در کتاب کار دریافت می کند. سپس اطمینان حاصل می کند که هدرها نمایش داده می شوند، دکمه های فیلتر قابل مشاهده هستند و سبک جدول روی "TableStyleLight1" تنظیم شده است.

اشیاء اکسل را با یک اسکریپت اضافه کنید

می توانید با فراخوانی روش افزودنی مربوطه در شیء والدین ، به صورت برنامه ای اشیاء اسناد مانند جداول یا نمودارها را اضافه کنید.

به صورت دستی اشیاء را به آرایه های جمع آوری اضافه نکنید. به عنوان مثال از روش های افزودن در اشیاء والدین استفاده کنید ، یک جدول را به یک برگه با روش کاربرگ اضافه کنید.

اسکریپت زیر یک جدول در اکسل در اولین برگه در کتاب کار ایجاد می کند. توجه داشته باشید که جدول ایجاد شده با روش addTable برگردانده می شود.

بیشتر اشیاء اکسل دارای یک روش setName هستند. این یک روش آسان برای دسترسی به اشیاء اکسل بعداً در اسکریپت یا اسکریپت های دیگر برای همان کتاب کار به شما می دهد.

تأیید کنید که یک شیء در مجموعه وجود دارد

اسکریپت ها اغلب قبل از ادامه باید بررسی کنند که آیا یک جدول یا شی مشابه وجود دارد. برای شناسایی اشیاء لازم و از طریق Excel UI از نامهای ارائه شده توسط اسکریپت ها یا از طریق UI Excel استفاده کنید. وقتی شیء درخواست شده در مجموعه نیست ، روشهای برگشت یافته را برگردانید.

اسکریپت زیر یک جدول با نام "myTable" درخواست می کند و از IF استفاده می کند. بیانیه دیگری برای بررسی اینکه آیا جدول پیدا شده است یا خیر.

یک الگوی مشترک در اسکریپت های اداری بازآفرینی یک جدول ، نمودار یا شیء دیگر هر بار که اسکریپت اجرا می شود. اگر به داده های قدیمی احتیاج ندارید ، بهتر است قبل از ایجاد مورد جدید ، شیء قدیمی را حذف کنید. این امر از درگیری نام یا اختلافات دیگری که ممکن است توسط سایر کاربران معرفی شده باشد ، جلوگیری می کند.

اسکریپت زیر جدول با نام "myTable" را حذف می کند ، در صورت وجود ، یک جدول جدید با همین نام اضافه می کند.

اشیاء اکسل را با یک اسکریپت حذف کنید

برای حذف یک شی ، با روش حذف شی تماس بگیرید.

همانطور که با اضافه کردن اشیاء ، اشیاء را از آرایه های مجموعه به صورت دستی حذف نکنید. از روش های حذف در اشیاء نوع مجموعه استفاده کنید. به عنوان مثال ، با استفاده از Table. delete یک جدول را از یک صفحه کار خارج کنید.

اسکریپت زیر اولین صفحه کار در کتاب کار را حذف می کند.

خواندن بیشتر در مدل شی

مستندات مرجع API Scripts API یک لیست جامع از اشیاء مورد استفاده در اسکریپت های اداری است. در آنجا ، می توانید از فهرست مطالب برای حرکت به هر کلاس مورد نظر برای کسب اطلاعات بیشتر استفاده کنید. موارد زیر چندین صفحه رایج مشاهده شده است.

برای اطلاعات خاص در مدل شیء Pivottable ، به کار با Pivottables در اسکریپت های اداری مراجعه کنید.