Tag Archives: Excel

New Office 365 API VS.Net Add-In exposes Javascript Client model

You can now access the Office 365 APIs using libraries available for .NET and JavaScript. These libraries make it easier to interact with the REST APIs from the device or platform of your choice.

 

Office365

The libraries are included in the latest update for Office 365 API Tools for Visual Studio Preview. Along with the libraries, this release also brings you some key updates to the tooling experience, making it easier to interact with Office 365 services.

Client libraries

Office 365 provides REST-based APIs that enable developers to access Office resources such as calendar, contacts, mail, files, and more.

The client libraries will let you:

  • Perform authentication and discovery
  • Use the Mail, Calendar and Contacts API
  • Use the My Files and Sites API (currently .NET only, with JavaScript coming soon)
  • Use the Users and Groups API

 

You can program directly against the REST APIs to interact with Office 365, but it requires you to write and maintain code around managing authentication tokens, constructing the right urls and queries for the API you wanted to access, and perform other tasks.

By using client libraries to access the Office 365 APIs, you can reduce the complexity of the code you need to write to access the APIs. We’re providing these libraries for .NET as well as JavaScript developers for use with the just-announced multi-device hybrid applications.

Here are some examples of how easy it is access the Office 365 APIs using these libraries.

.NET C# code to authenticate and get upcoming events from your Office 365 calendar:

// Shows UI to authenticate
Authenticator = newAuthenticator();
AuthenticationInfo result = await authenticator.AuthenticateAsync("https://outlook.office365.com");

The AuthenticateAsync method will prompt for a username and password and authenticate against the specified resource url, like outlook.office365.com in this case. Once you have the authentication information, you can create a client object that serves as the base for accessing all the APIs for Exchange:


// Create a client object
ExchangeClient client =
newExchangeClient(newUri("https://outlook.office365.com/ews/odata"),
result.GetAccessToken);

Because we’re using .NET here, we get to take advantage of the native language capabilities, like LINQ, so querying the Office 365 calendar is as simple as writing a LINQ query and executing it:

// Obtain calendar event data
var eventsResults = await (from i in client.Me.Events
where i.End >= DateTimeOffset.UtcNow
select i).Take(10).ExecuteAsync();

With just those four lines of code you can start making calls to the Office 365 APIs!

We wanted to make sure that you can reach multiple device and service platforms with a consistent API, so the client libraries are portable .NET libraries, which means they also work with Android and iOS devices through Xamarin. Because authentication needs to display a UI that is different on the various platforms, we also provide platform-specific authentication libraries, which can then be used with the portable ones to provide an end-to-end experience.

For developers creating multi-device hybrid applications that target multiple device platforms through JavaScript, we also have JavaScript versions of these libraries that provide a similar experience while adopting JavaScript’s patterns and practices, such as using the promises pattern instead of await.

 

Here is the same example to authenticate and get calendar events in JavaScript:

var authContext = new O365Auth.Context();
authContext.getIdToken('https://outlook.office365.com/')
.then((function (token) {
// authentication succeeded
var client = new Exchange.Client('https://outlook.office365.com/ews/odata',
token.getAccessTokenFn('https://outlook.office365.com'));
client.me.calendar.events.getEvents().fetch()
.then(function (events) {
// get currentPage of calendar events
var myevents = events.currentPage;
}, function (reason) {
// handle error
});
}).bind(this), function (reason) {
// authentication failed
});

The flow to authenticate and create a client object is similar across .NET and JavaScript, but you’re doing it in a way that should be natural to the language.

Along with the JavaScript files for these libraries, we are also including the TypeScript type definition (.d.ts)—in case you choose to develop your apps in TypeScript.

As you get started using these libraries, there are a few things to keep in mind. This is a very early preview release of the libraries that is meant to prove out the concept and get feedback on it. The libraries do not currently cover all the APIs provided by the services and some of the APIs in the library may not work. The APIs in the libraries themselves will definitely change in future updates.

Note that while we tend to call these “client” libraries, these also work with .NET server technologies like Asp.Net Web Forms and MVC, so you really get to target the breadth of the .NET platform.

 

Tooling updates

With today’s update of our Office 365 API Tools for Visual Studio 2013, the tool displays the available Office 365 services that you can add to your project. Once you’ve signed in with your Office 365 credentials, adding a service to your project is as easy as selecting the appropriate service and applying the required permissions.

dotnetvisualstudioupdate_01

Once you submit the changes, Visual Studio performs the following:

  1. Registers an application (if there isn’t an application registered yet) in Microsoft Azure Active Directory to consume Office 365 services.
  2. Adds the following to the project:
    1. Client libraries from Nuget for the configured services.
    2. Sample code files that use the Client Libraries.

Project types supported

With the broad reach of the client libraries, the Office 365 API tool is now available for a variety of project types (client, desktop, and web) in Visual Studio. Here’s are all the project types supported with the May update:

  • .NET Windows Store Apps
  • Windows Forms Application
  • WPF Application
  • ASP.NET MVC Web Application
  • ASP.NET Web Forms Application
  • Xamarin Android and iOS Applications
  • Multi-device hybrid apps

Installing the latest update

To install the latest update, you can either:

  • Check for updates within Visual Studio. To do so, follow these steps:
    1. In Visual Studio menu, click Tools->Extensions and Updates->Updates.
    2. You should see the update available for Office 365 API Tools.
    3. Click Update to update to the latest version.

–OR–

  • Download the extension and install it manually.

Once you’ve updated, you can invoke the Office 365 API tool as usual, that is, by going to your project node in the Solution Explorer and selecting Add->Connected Service from the context menu.

Looking forward to seeing your Apps out there when I visit the stores!!


MSDN references

Check also new SharePoint Online Solution Pack for branding and provisioning. This package contains also some examples, which originates from the AMS reference implementations. Here’s the direct links for the Solution Pack

You can find introduction to this SharePoint Online Solution Pack for branding and provisioning from following blog post – Introduction to SharePoint Online Solution Pack for branding and provisioning released.

Advertisements

Microsoft BI and the new PowerQuery for Excel – How we empower users

Introduction to Microsoft Power Query for Excel

Microsoft Power Query for Excel enhances self-service business intelligence (BI) for Excel with an intuitive and consistent experience for discovering, combining, and refining data across a wide variety of sources including relational, structured and semi-structured, OData, Web, Hadoop, Azure Marketplace, and more. Power Query also provides you with the ability to search for public data from sources such as Wikipedia.

With Power Query 2.10, you can share and manage queries as well as search data within your organization. Users in the enterprise can find and use these shared queries (if it is shared with them) to use the underlying data in the queries for their data analysis and reporting. For more information about how to share queries, see Share Queries.

With Power Query, you can

  • Find and connect data across a wide variety of sources.
  • Merge and shape data sources to match your data analysis requirements or prepare it for further analysis and modeling by tools such as Power Pivot and Power View.
  • Create custom views over data.
  • Use the JSON parser to create data visualizations over Big Data and Azure HDInsight.
  • Perform data cleansing operations.
  • Import data from multiple log files.
  • Perform Online Search for data from a large collection of public data sources including Wikipedia tables, a subset of Microsoft Azure Marketplace, and a subset of Data.gov.
  • Create a query from your Facebook likes that render an Excel chart.
  • Pull data into Power Pivot from new data sources, such as XML, Facebook, and File Folders as refreshable connections.
  • With Power Query 2.10, you can share and manage queries as well as search data within your organization.

New updates for Power Query

The Power Query team has been busy adding a number of exciting new features to Power Query. You can download the update from this page.

New features for Power Query include the following, please read the rest of this blog post for specific details for each.

  • New Data Sources
    • Updated “Preview” functionality of the SAP BusinessObjects BI Universe connectivity
    • Access tables and named ranges in a workbook
  • Improvements to Query Load Settings
    • Customizable Defaults for Load Settings in the Options dialog
    • Automatic suggestion to load a query to the Data Model when it goes beyond the worksheet limit
    • Preserve data in the Data Model when you modify the Load to Worksheet setting of a query that is loaded to the Data Model
  • Improvements to Query Refresh behaviors in Excel
    • Preserve Custom Columns, Conditional Formatting and other customizations of worksheet tables
    • Preserve results from a previous query refresh when a new refresh attempt fails
  • New Transformations available in the Query Editor
    • Remove bottom rows
    • Fill up
    • New statistic operations in the Insert tab
  • Other Usability Improvements
    • Ability to reorder queries in the Workbook Queries pane
    • More discoverable way to cancel a preview refresh in the Query Editor
    • Keyboard support for navigation and rename in the Steps pane
    • Ability to view and copy errors in the Filter Column dropdown menu
    • Remove items directly from the Selection Well in the Navigator
    • Send a Frown for Service errors

Connect to SAP BusinessObjects BI Universe (Preview)

This connectivity has been a separate Preview feature for the last month or so. In this release, we are incorporating the SAP BusinessObjects BI Universe connector Preview capabilities as part of the main Power Query download for ease of access. With Microsoft Power Query for Excel, you can easily connect to an SAP BusinessObjects BI Universe to explore and analyze data across your enterprise.

Access tables and named ranges in an Excel workbook

With From Excel Workbook, you can now connect to tables and named ranges in your external workbook sheets. This simplifies the process of selecting useful data from an external workbook, which used to be limited to sheets and users had to “manually” scrape the data (using Query transform operations).

 

Customizable Defaults for Load Settings in the Options dialog

You can override the Power Query default Load Settings in the Options dialog. This will set the default Load Settings behavior for new queries in areas where Load Settings are not exposed directly to the user, such as in Online Search results and the Navigator task pane in single-table import mode. In addition, this will set the default state for Load Settings where these settings are available including the Query Editor, and Navigator in multi-table import mode.

           

Preserve Custom Columns, Conditional Formatting and other customizations of worksheet tables

With this Power Query Update, Custom Columns, conditional formatting in Excel, and other customizations of worksheet tables are preserved after you refresh a query. Power Query will preserve worksheet customizations such as Data Bars, Color Scales, Icon Sets or other value-based rules across refresh operations and after query edits.

Preserve results from a previous query refresh when a new refresh attempt fails

After a refresh fails, Power Query will now preserve the previous query results. This allows you to work with slightly older data in the worksheet or Data Model and lets you refresh the query results after fixing the cause of errors.

Automatic suggestion to load a query to the Data Model when it goes beyond the worksheet limits

When you are working with large volumes of data in your workbook, you could reach the limits of Excel’s worksheet size. When this occurs, Power Query will automatically recommend to load your query results to the Data Model. The Data Model can store very large data sets.

Preserve data in the Data Model when modifying the Load to Worksheet setting of a query that is loaded to the Data Model

With Power Query, data and annotations on the Data Model are preserved when modifying the Load to Worksheet setting of a query. Previously, Power Query would reset the query results in both the worksheet and the Data Model when modifying either one of the two load settings.      

Remove Bottom Rows

A very common scenario, especially when importing data from the Web and other semi-structured sources, is having to remove the last few rows of data because the contents do not belong to the data set. For instance, it’s common to remove links to previous/next pages or comments. Previously, this was possible only by using a composition of custom formulas in Power Query. This transformation is now much easier by adding a library function called Table.RemoveLastN(), and a button for this transformation in the Home tab of the Query Editor ribbon.

 

Fill Up

Power Query already supports the ability to fill down values in a column to neighboring empty cells. Starting with this update, you can now fill values up within a column as well. This new transformation is available as a new library function called Table.FillUp(), and a button on the Home tab of the Query Editor ribbon.

New Statistics operations in the Insert tab

The Insert tab provides various ways to insert new columns in queries, based on custom formulas or by deriving values based on other columns. You can now apply Statistics operations based on values from different columns, row by row, in their table.

 

Ability to reorder queries in the Workbook Queries pane

With the latest Power Query update, you can move queries up or down in the Workbook Queries pane. You can right-click on a query and select Move Up or Move Down to reorder queries.

More discoverable way of cancelling refresh of a preview in the Query Editor

The Cancel option is now much more discoverable inside the Query Editor dialog. In addition to the Refresh dropdown menu in the ribbon, this option can now be found in the status bar at the bottom right corner of the Query Editor, next to the download status information.

  

Keyboard support for navigation and rename in the Steps pane

You can now use the Up/Down Arrow keys to navigate between steps in your query. Also, press the F2 key to rename the current step.

Ability to view and copy errors in the Filter Column dropdown menu

You can easily view and copy error details inside the Filter Column menu. This is very useful to troubleshoot errors while retrieving filter values.

Remove items directly from the Selection Well in the Navigator

You can remove items directly from the Selection Well instead of having to find the original item in the Navigator tree to deselect it.

 

Send a Frown for Service errors

We try as hard as possible to improve the quality of Power Query and all of its features. Even then, there are cases in which errors can happen. You can now send a frown directly from experiences where a service error happened, for instance, an error retrieving a Search result preview or downloading a query from the Data Catalog. This will give us enough information about the service request that failed and the client state to troubleshoot the issue.

That’s all for this update! We hope that you enjoy these new Power Query features. Please don’t hesitate to contact us via the Power Query Forum or send us a smile/frown with any questions or feedback that you may have.

You can also follow these links to access more resources about Power Query and Power BI:

A FREE “Export to Excel” C# class, using OpenXML

It’s amazing that even now, in 2014, there are so many developers still asking for help on how to write C# and VB.NET code to export their data to Excel.

Even worse, a lot of them will stumble on articles suggesting that they should write their data to a comma-separated file, but to give the file a .xls extension.

So today, I’m going to walkthrough how to use my C# “Export to Excel” class which you can add to your C# WinForms / WPF / ASP.NET application, using one line of code.

Depending on whether your data is stored in a DataSet, DataTable, or List<>, you simply need to call one of these three functions, and tell them what (Excel) file name you want to write to.

public static bool CreateExcelDocument(List list, string ExcelFilename
public static bool CreateExcelDocument(DataTable dt, string ExcelFilename
public static bool CreateExcelDocument(DataSet ds, string ExcelFilename)

Here’s a simple example.

We create a DataSet, fill it with some data (just add your own CreateSampleData function), then call the CreateExcelFile.CreateExcelDocument function, passing it the DataSet and a filename:

// Step 1: Create a DataSet, and put some sample data in it
DataSet ds = CreateSampleData();

// Step 2: Create the Excel .xlsx file
try
{
CreateExcelFile.CreateExcelDocument(ds, “C:\\Sample.xlsx”);
}
catch (Exception ex)
{
MessageBox.Show(“Couldn’t create Excel file.\r\nException: ” + ex.Message);
return;
}

And that’s all you have to do. The CreateExcelDocument function will create a “real” Excel file for you.

For example, if you had a created a DataSet containing three DataTables called:

Drivers
Vehicles,
Vehicle Owners,

..then here’s what your Excel file would look like. The class would create one worksheet per DataTable, and each worksheet would contain the data from that DataTable.

The full source code for this C# class is freely downloadable here
Adding the library to your application

The C# code above shows how easy it is to call the CreateExcelFile class.

DataSet ds = CreateSampleData();
CreateExcelFile.CreateExcelDocument(ds, “C:\\Sample.xlsx”);

However, to use this library, you’ll need to add two files from the free Microsoft OpenXML SDK:

DocumentFormat.OpenXml.dll: From the free Microsoft Open XML SDK library

WindowsBase.dll: From the Microsoft .NET Framework library

Add these two DLLs to your project’s References section, and remember to set them to “Copy Local”

Then, just download the CreateExcelFile.cs file (via the “Browse code” link in CodeProject’s left-hand panel), and add it to your application.

And that’s it.

Regardless of if your data is stored in a List<>, DataTable, or DataSet, you can export it to a “real” Office 2-10 Excel .xlsx file using that one line of code.

And because it is created using the OpenXML library, you can run this code on machines which don’t have Excel installed.
ASP.NET

For ASP.NET developers, I’ve added three extra functions.

public static bool CreateExcelDocument(List list, string filename, System.Web.HttpResponse Response)

public static bool CreateExcelDocument(DataTable dt, string filename, System.Web.HttpResponse Response)

public static bool CreateExcelDocument(DataSet ds, string filename, System.Web.HttpResponse Response)

Rather than creating the Excel file in a temporary directory, then having to load in the file and output it to the webpage’s HttpResponse, you can get the library to write directly to the HttpResponse.

However, by default, this functionality is disabled (to prevent build issues for the non ASP.NET developers). To enable these three functions, you need to make two changes.

First, uncomment the top line of the CreateExcelFile.cs code, so that it now reads:

#define INCLUDE_WEB_FUNCTIONS

Now, you need to add a Reference to the System.Web .NET library:

Adding a reference

Once you’ve done these two steps, the CreateExcelFile library is ready to go.

For example, in this example, my ASP.NET C# code has a list of Employee records, stored in a List. I add an “Export to Excel” button to my webpage, and when the user clicks on it, I just need one simple call to the CreateExcelFile class.

// In this example, I have a defined a List of my Employee objects.
class Employee;

List listOfEmployees = new List();

// The following ASP.Net code gets run when I click on my “Export to Excel” button.
protected void btnExportToExcel_Click(object sender, EventArgs e)
{
// It doesn’t get much easier than this…
CreateExcelFile.CreateExcelDocument(listOfEmployees, “Employees.xlsx”, Response);
}

And that’s it. A real Excel file, in one line of code.
Going forward

You’ll notice that this library is excellent for one job – writing plain, boring data to an Excel file. I haven’t attempted to add any classes to add formatting, colors, pivot tables or anything else.

However, this class is an excellent way to get started (without paying for third-party software to create the Excel file for you), and if you want to take this further, you’ll soon find that Googling will easily find you extra source code to add on top of this.

For example, if you wanted to add a background color to some of the cells in the Excel file, simply Google “open XML background color” and you’ll have many articles showing you how to do this.

The reason I wrote this article is that I found that it was very hard to find a free, easy to use C# library which actually created the OpenXML Excel file in the first place.

Download File