Productivity in the development of Office Add-ins
Speaking of Office Add-ins (Office add-ons), "productivity" is often mentioned. This is logical, because the main goal of developers is to increase productivity in the work of end users. But productivity is important for the developers themselves. This article is about productivity in development. It's about the new JavaScript API for Office and what tools we use to create new solutions.
In December 2015, I was invited to participate in a work session on Office 365 as a consultant for creating Office add-ons. The event brought together teams interested in promoting their products in the Office Store. The goal is to immerse yourself in the subtleties of the JavaScript API for Office in two days, to form the concepts of future solutions and implement prototypes.
')
On the second day, between the answers to the questions from the participants, I had free time. This allowed to start work on a new idea. In the VSTO version of the XLTools extension for Excel, there is a popular feature among users - the calendar for entering dates . I have long wanted to implement it for the Office Store, but my hands did not reach. And since The theme of the working session was productivity, the idea fits perfectly, because the calendar significantly increases the speed of working with documents requiring dates.
For example, take the standard template for Excel from the available online - "To-do List":
To create such a document, open in Excel menu File > New > Enter in the field “Search for templates in the network” the name of the list “To-do list”.
On the screen above, you can see that the table contains two fields with the type "Date": "Start date" and "Execution date". Anyone who has had to work with dates in Excel knows that entering dates is fraught with some difficulties. Now, if you could just choose a date from the calendar!
So the idea was born. Upon reflection, I derived the following list of requirements:
I am using Visual Studio. Other tools, such as Napa or another code editor, are suitable, but in terms of performance and convenience, Visual Studio is the best option because allows you to create an Office add-in project ready for launching and debugging in a minute. Debugging Office Add-ins is not yet possible in Napa and other third-party editors.
Open Visual Studio> File> New> Project> Templates> Office / SharePoint> Apps> Select the type of application "App for Office". I note that at the time of this writing, Microsoft replaced the name “App” with “Add-in.” Surely in the future will change the name of the project.
Next, set the type of application. Since add-in built into the body of the document, select the type "Content" - ideal for solving the task. The difference between the Task pane type and the Content is clearly demonstrated by the figure below.
At the last step, we are offered to choose a project template (Basic App - basic, Document Visualization App - more complicated) and a set of supported office programs: A ccess, Excel, PowerPoint . Choose the Basic App and Excel support. Click Finish . Project created and ready to run (F5) and debug.
Let's go over the requirements again and think about how to implement each of them. It is worth starting with the last one - “embedding”. Here lies the distinctive feature of the new type of Office Add-ins. Compared to traditional VSTOs, they do not require the installation of additional components. Those. If you create an Excel document, add an add-in to it from the Office Store and send the document to your colleagues, they will not need to install anything else to work with the add-in. This is exactly what the VSTO users of the XLTools calendar lacked.
The calendar does not provide for a complex UI. On the Internet there is a sea of ​​freely distributed JavaScript-libraries that implement the functionality of the calendar display. I chose the Pikaday library, which satisfies the rest of our requirements 100%. The library allows you to:
Of course, we often solve more complex tasks that require a unique design. Here you should pay attention to the recently released Office UI Fabric framework, which provides a set of CSS classes and UI components pre-styled for office applications. Styles and components are adapted to work under all platforms supported by Office: Mobile Apps, Web, Desktop. Using the Office UI Fabric greatly simplifies the design and development of the UI. It's like Bootstrap, only for Office Add-ins developers.
The UI took nine lines of code. Connect CSS and JavaScript in the Home.html file:
Initializing the calendar in the Office.initialize method in the Home.js file:
Pikaday allows you to "hang" the handler on the event of a date change in the calendar. After receiving the date, we put it in the current selected cell using the method from the Office API - setSelectedDataAsync :
The example uses the getLocaleShortDateString function to work with the date. It is necessary because Excel takes dates as numbers and displays them as dates only if the cell has the appropriate format. The problem lies in the fact that we can write the number to the cell, but the current JavaScript API does not allow changing the cell format. Fortunately, there was a workaround. To get the date in the cell, you need to put it in the form of text, observing the format of the region (locale) chosen by the user. The getLocaleShortDateString function is just used to convert a Date object into a localized text format. You can find out which regional standard is selected by the user through the context object property - Office.context.displayLanguage .
JavaScript API for Excel allows you to handle the event changes the selected area of ​​cells. Use this to track the user changing the selected cell:
“Catching” the moment when a user selects a new cell, we check whether the selected value is a date. To get the value from the selected cell, we use the Office.context.document.getSelectedDataAsync function, telling it to return an unformatted value. In the case of a date, the value will be an integer (dates over time are not considered). Further, there are checks for compliance with the date. It is not possible to determine by 100% whether the value in the cell is a date. So, if the user selects a cell with a number corresponding to the numeric representation of the date, the algorithm considers that this is the date. To minimize the number of false positives for numbers, set a restriction: check the date for + -50 years from the current year. If the value fits all criteria, use the Pikaday calendar's setDate method to highlight.
Office Store now supports 40 languages. The examples above already show how to localize the date value. In addition to the date, localization also requires UI. In the case of the Pikaday calendar, everything is simple:
For a start, we support 2 languages: Russian and English. Pikaday is English by default. To translate into Russian, we check the current region of the user and substitute new values ​​for text labels, if the interface language is Russian.
In less than one day I was able to create an Office Add-in, ready for publication in the Office Store . This speed is possible thanks to the experience and ready-made solutions accumulated over the years in web development. The advantage is the simplicity of the JavaScript API for Office, as well as the availability of a ready-made Office UI Fabric framework for building a UI.
Of the difficulties it can be noted that debugging add-ons on different platforms is an extraordinary task. After sending the application for review in the Office Store, I had to struggle with debugging, since The first version of the add-on failed to check due to errors on the iPad and the Web. I coped with debugging problems and write a separate article about it.
Recently, the XLTools.net Calendar add-in successfully passed the test and is now available for download in the Office Store . For two weeks we scored 1832 downloads, received 1 review and 4 maximum marks in the Office Store. A lot of positive feedback and requests for functionality refinement came by email. In the near future we plan to release an improved calendar with new options. Stay tuned!
Peter Lyapin - Technical Director of Wave Point Ltd.
More than 10 years of experience in implementing automation projects
business processes. Worked with many Russian and
foreign companies. Founder of the XLTools.net project.
Office Add-in for 1 day
In December 2015, I was invited to participate in a work session on Office 365 as a consultant for creating Office add-ons. The event brought together teams interested in promoting their products in the Office Store. The goal is to immerse yourself in the subtleties of the JavaScript API for Office in two days, to form the concepts of future solutions and implement prototypes.
')
On the second day, between the answers to the questions from the participants, I had free time. This allowed to start work on a new idea. In the VSTO version of the XLTools extension for Excel, there is a popular feature among users - the calendar for entering dates . I have long wanted to implement it for the Office Store, but my hands did not reach. And since The theme of the working session was productivity, the idea fits perfectly, because the calendar significantly increases the speed of working with documents requiring dates.
Conceived functionality
For example, take the standard template for Excel from the available online - "To-do List":
To create such a document, open in Excel menu File > New > Enter in the field “Search for templates in the network” the name of the list “To-do list”.
On the screen above, you can see that the table contains two fields with the type "Date": "Start date" and "Execution date". Anyone who has had to work with dates in Excel knows that entering dates is fraught with some difficulties. Now, if you could just choose a date from the calendar!
So the idea was born. Upon reflection, I derived the following list of requirements:
- Enter the date in the selected cell in one click;
- Automatic selection of the date in the calendar when selecting a cell with a value;
- Visual display of information about the selected date (day of the week, week number);
- Localization for date formats in different regions;
- "Embedding" calendar directly into the document without the need to install additional components.
Implementation
I am using Visual Studio. Other tools, such as Napa or another code editor, are suitable, but in terms of performance and convenience, Visual Studio is the best option because allows you to create an Office add-in project ready for launching and debugging in a minute. Debugging Office Add-ins is not yet possible in Napa and other third-party editors.
Project creation
Open Visual Studio> File> New> Project> Templates> Office / SharePoint> Apps> Select the type of application "App for Office". I note that at the time of this writing, Microsoft replaced the name “App” with “Add-in.” Surely in the future will change the name of the project.
Next, set the type of application. Since add-in built into the body of the document, select the type "Content" - ideal for solving the task. The difference between the Task pane type and the Content is clearly demonstrated by the figure below.
At the last step, we are offered to choose a project template (Basic App - basic, Document Visualization App - more complicated) and a set of supported office programs: A ccess, Excel, PowerPoint . Choose the Basic App and Excel support. Click Finish . Project created and ready to run (F5) and debug.
Design and implementation of UI
Let's go over the requirements again and think about how to implement each of them. It is worth starting with the last one - “embedding”. Here lies the distinctive feature of the new type of Office Add-ins. Compared to traditional VSTOs, they do not require the installation of additional components. Those. If you create an Excel document, add an add-in to it from the Office Store and send the document to your colleagues, they will not need to install anything else to work with the add-in. This is exactly what the VSTO users of the XLTools calendar lacked.
The calendar does not provide for a complex UI. On the Internet there is a sea of ​​freely distributed JavaScript-libraries that implement the functionality of the calendar display. I chose the Pikaday library, which satisfies the rest of our requirements 100%. The library allows you to:
- Display the calendar directly on the web page of the application;
- "Hang" handler for the event "Select date from calendar" ( onSelect );
- Set the value of the date selected in the calendar from the code ( setDate );
- Translate the name of the days of the week and months into the required languages.
Of course, we often solve more complex tasks that require a unique design. Here you should pay attention to the recently released Office UI Fabric framework, which provides a set of CSS classes and UI components pre-styled for office applications. Styles and components are adapted to work under all platforms supported by Office: Mobile Apps, Web, Desktop. Using the Office UI Fabric greatly simplifies the design and development of the UI. It's like Bootstrap, only for Office Add-ins developers.
The UI took nine lines of code. Connect CSS and JavaScript in the Home.html file:
<link href="pikaday.css" rel="stylesheet" type="text/css" /> <script src="pikaday.js" type="text/javascript"></script> Initializing the calendar in the Office.initialize method in the Home.js file:
var calOptions = { showWeekNumber: true, // defaultDate: new Date() // }; var placeholder = $("body"); // « » body var picker = new Pikaday(calOptions); // placeholder.append(picker.el); // body Handling the date selection in the calendar
Pikaday allows you to "hang" the handler on the event of a date change in the calendar. After receiving the date, we put it in the current selected cell using the method from the Office API - setSelectedDataAsync :
calOptions.onSelect = function (date) { // date = getLocaleShortDateString(date); // Date Office.context.document.setSelectedDataAsync(date, // { coercionType: Office.CoercionType.Text // – «» }, function (asyncResult) { // if (asyncResult.status == "failed") { app.showNotification("Failed", asyncResult.error.message, 'error'); } } ); }; The example uses the getLocaleShortDateString function to work with the date. It is necessary because Excel takes dates as numbers and displays them as dates only if the cell has the appropriate format. The problem lies in the fact that we can write the number to the cell, but the current JavaScript API does not allow changing the cell format. Fortunately, there was a workaround. To get the date in the cell, you need to put it in the form of text, observing the format of the region (locale) chosen by the user. The getLocaleShortDateString function is just used to convert a Date object into a localized text format. You can find out which regional standard is selected by the user through the context object property - Office.context.displayLanguage .
function getLocaleShortDateString(d) { var f = getLocaleDateString(), y = d.getFullYear(), m = d.getMonth() + 1, d = d.getDate(); function z(s) { s = '' + s; return s.length > 1 ? s : '0' + s; } f = f.replace(/yyyy/, y); f = f.replace(/yy/, String(y).substr(2)); f = f.replace(/MM/, z(m)); f = f.replace(/M/, m); f = f.replace(/dd/, z(d)); f = f.replace(/d/, d); return f; } function getLocaleDateString() { var formats = { "en-US": "M/d/yyyy", "ru-RU": "dd.MM.yyyy", ... // 200 }; return formats[Office.context.displayLanguage] || 'dd/MM/yyyy'; } Highlight the selected date in the calendar
JavaScript API for Excel allows you to handle the event changes the selected area of ​​cells. Use this to track the user changing the selected cell:
// Office.context.document.addHandlerAsync(Office.EventType.DocumentSelectionChanged, function (eventArgs) { // Office.context.document.getSelectedDataAsync(Office.CoercionType.Text, { // , () valueFormat: Office.ValueFormat.Unformatted }, function (ufResult) { if (ufResult.status != "failed") { var value = ufResult.value; // - if (isInt(value) && value > 0) { // var date = getJsDateFromExcel(value); // +-50 if (new String(date) != "InvalidDate" && date.getYear() > new Date().getYear() - 50 && date.getYear() < new Date().getYear() + 50) { // picker.setDate(date, true); } } } }); }); “Catching” the moment when a user selects a new cell, we check whether the selected value is a date. To get the value from the selected cell, we use the Office.context.document.getSelectedDataAsync function, telling it to return an unformatted value. In the case of a date, the value will be an integer (dates over time are not considered). Further, there are checks for compliance with the date. It is not possible to determine by 100% whether the value in the cell is a date. So, if the user selects a cell with a number corresponding to the numeric representation of the date, the algorithm considers that this is the date. To minimize the number of false positives for numbers, set a restriction: check the date for + -50 years from the current year. If the value fits all criteria, use the Pikaday calendar's setDate method to highlight.
Localization
Office Store now supports 40 languages. The examples above already show how to localize the date value. In addition to the date, localization also requires UI. In the case of the Pikaday calendar, everything is simple:
var myLanguage = Office.context.displayLanguage.split("-")[0]; if (myLanguage == "ru") { calOptions.firstDay = 1; calOptions.i18n = { previousMonth: ' ', nextMonth: ' ', months: ['', '', '', '', '', '', '', '', '', '', '', ''], weekdays: ['', '', '', '', '', '', ''], weekdaysShort: ['', '', '', '', '', '', ''] }; } For a start, we support 2 languages: Russian and English. Pikaday is English by default. To translate into Russian, we check the current region of the user and substitute new values ​​for text labels, if the interface language is Russian.
Work with XLTools.net Calendar
findings
In less than one day I was able to create an Office Add-in, ready for publication in the Office Store . This speed is possible thanks to the experience and ready-made solutions accumulated over the years in web development. The advantage is the simplicity of the JavaScript API for Office, as well as the availability of a ready-made Office UI Fabric framework for building a UI.
Of the difficulties it can be noted that debugging add-ons on different platforms is an extraordinary task. After sending the application for review in the Office Store, I had to struggle with debugging, since The first version of the add-on failed to check due to errors on the iPad and the Web. I coped with debugging problems and write a separate article about it.
Recently, the XLTools.net Calendar add-in successfully passed the test and is now available for download in the Office Store . For two weeks we scored 1832 downloads, received 1 review and 4 maximum marks in the Office Store. A lot of positive feedback and requests for functionality refinement came by email. In the near future we plan to release an improved calendar with new options. Stay tuned!
about the author
Peter Lyapin - Technical Director of Wave Point Ltd.
More than 10 years of experience in implementing automation projects
business processes. Worked with many Russian and
foreign companies. Founder of the XLTools.net project.
Source: https://habr.com/ru/post/279735/
All Articles