Download, Edit and Upload the Excel file in One Drive using Microsoft Graph

19 mins read

Syncfusion Essential XlsIO is a .NET Excel library to create, read and edit Excel documents. Using this library, you can edit the Excel files downloaded from OneDrive and upload them again, with the help of Microsoft Graph.

Steps to download Excel file from OneDrive, Edit and upload again programmatically:

Create a Universal Windows Platform Application:

Create a new Blank Universal Windows Application in Visual Studio.

Create a Blank UWP app in Visual Studio

Name the project

In the New Universal Windows Platform Project dialog, ensure that the Minimum version is set to Windows 10 Fall Creators Update (10.0; Build 16299) or later and select OK.

Set the minimum version to fall creators update

Install the Syncfusion.XlsIO.UWP NuGet package as reference to your .NET Framework application from NuGet.org.

Install NuGet package to the project

Install the following additional NuGet packages that will be used later.

Microsoft.Toolkit.Uwp.Ui.Controls to add some UI controls for in-app notifications and loading indicators.
Microsoft.Toolkit.Uwp.Ui.Controls.DataGrid to display the information returned by Microsoft Graph.
Microsoft.Toolkit.Uwp.Ui.Controls.Graph to handle login and access token retrieval.
Microsoft.Graph for making calls to the Microsoft Graph.

Install NuGet packages to the project

Design the App:

Add the following property in App.xaml.cs

public bool IsAuthenticated { get; set; }

Replace the existing code in MainPage.xaml with the following content, to define the layout for main page.

XML

<Page
    x:Class="graph_tutorial.MainPage"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:local="using:graph_tutorial"
    xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
    xmlns:controls="using:Microsoft.Toolkit.Uwp.UI.Controls"
    xmlns:graphControls="using:Microsoft.Toolkit.Uwp.UI.Controls.Graph"
    mc:Ignorable="d"
    Background="{ThemeResource ApplicationPageBackgroundThemeBrush}">
 
    <Grid>
        <NavigationView x:Name="NavView"
            IsSettingsVisible="False"
            ItemInvoked="NavView_ItemInvoked">
 
            <NavigationView.Header>
                <graphControls:AadLogin x:Name="Login"
                    HorizontalAlignment="Left"
                    View="SmallProfilePhotoLeft"
                    AllowSignInAsDifferentUser="False"
                    />
            </NavigationView.Header>
 
            <NavigationView.MenuItems>
                <NavigationViewItem Content="Home" x:Name="Home" Tag="home">
                    <NavigationViewItem.Icon>
                        <FontIcon Glyph=""/>
                    </NavigationViewItem.Icon>
                </NavigationViewItem>
                <NavigationViewItem Content="OneDrive" x:Name="OneDrive" Tag="onedrive">
                    <NavigationViewItem.Icon>
                        <FontIcon Glyph=""/>
                    </NavigationViewItem.Icon>
                </NavigationViewItem>
            </NavigationView.MenuItems>
 
            <StackPanel>
                <controls:InAppNotification x:Name="Notification" ShowDismissButton="true" />
                <Frame x:Name="RootFrame" Margin="24, 0" />
            </StackPanel>
        </NavigationView>
    </Grid>
</Page>

Now add another XAML page for the Home view. Right-click on the project and select Add -> New Item -> Choose Blank Page, enter HomePage.xaml in the Name field, and select Add.

Add the following code inside the <Grid> element in the file.

XML

<StackPanel>
         <TextBlock FontSize="44" FontWeight="Bold" Margin="0, 12">Microsoft Graph UWP Tutorial</TextBlock>
         <TextBlock x:Name="HomePageMessage">Please sign in to continue.</TextBlock>
</StackPanel>

Add the following function in MainPage.xaml.cs to manage authentication state.

private void SetAuthState(bool isAuthenticated)
{
 (App.Current as App).IsAuthenticated = isAuthenticated;
 //Toggle controls that require auth
              OneDrive.IsEnabled = isAuthenticated;
}

Add the following code in MainPage() constructor after the this.InitializeComponent(); line.

//Initialize auth state to false
SetAuthState(false);
 
//Navigate to HomePage.xaml
RootFrame.Navigate(typeof(HomePage));

When the app first starts, it will initialize the authentication state to false and navigate to the home page.

Add the following event handler to load the requested page when the user selects an item from the navigation view.

private void NavView_ItemInvoked(NavigationView sender, NavigationViewItemInvokedEventArgs args)
{
    var invokedItem = args.InvokedItem as string;
 
    switch (invokedItem.ToLower())
    {
        case "onedrive":
            throw new NotImplementedException();
            break;
        case "home":
        default:
            RootFrame.Navigate(typeof(HomePage));
            break;
    }
}

Save all your changes, then press F5 or select Debug -> Start Debugging in Visual Studio.

Sign-In option

Open a browser and navigate to the Azure Active Directory admin center and login using a personal account or Work or School Account.

Select New registration. On the Register an application page, set the values as follows.

Set Name to UWP Graph Tutorial.
Set Supported account types to Accounts in any organizational directory and personal Microsoft accounts.
Under Redirect URI, change the dropdown to Public client (mobile & desktop), and set the value to urn:ietf:wg:oauth:2.0:oob.

Select Register. On the UWP Graph Tutorial page, copy the value of the Application (client) ID and save it, you will need it in the next step.

Application ID

Add Azure AD Authentication:

Right-click the graph-tutorial project in Solution Explorer and select Add -> New Item. Choose Resources File (.resw), name the file as OAuth.resw and select Add. When the new file opens in Visual Studio, create two resources as follows.

Name: AppId, Value: the app ID you generated in Application Registration Portal
Name: Scopes, Value: User.Read Files.ReadWrite Files.ReadWrite.All

Provide authentication

Configure the AadLogin control:

Open MainPage.xaml.cs and add the following using statement to the top of the file.

using Microsoft.Toolkit.Services.MicrosoftGraph;

Replace the RootFrame.Navigate(typeof(HomePage)); line with the following code.

//Load OAuth settings
var oauthSettings = Windows.ApplicationModel.Resources.ResourceLoader.GetForCurrentView("OAuth");
var appId = oauthSettings.GetString("AppId");
var scopes = oauthSettings.GetString("Scopes");
 
if (string.IsNullOrEmpty(appId) || string.IsNullOrEmpty(scopes))
{
    Notification.Show("Could not load OAuth Settings from resource file.");
}
else
{
    //Initialize Graph
    MicrosoftGraphService.Instance.AuthenticationModel = MicrosoftGraphEnums.AuthenticationModel.V2;
    MicrosoftGraphService.Instance.Initialize(appId,
    MicrosoftGraphEnums.ServicesToInitialize.UserProfile,
    scopes.Split(' '));
 
    //Navigate to HomePage.xaml
    RootFrame.Navigate(typeof(HomePage));
}

This code loads the settings from OAuth.resw and initializes the global instance of the MicrosoftGraphService with those values.

Now add an event handler for the SignInCompleted event on the AadLogin control. Open the MainPage.xaml file and replace the existing <graphControls:AadLogin> element with the following.

XML

<graphControls:AadLogin x:Name="Login"
                    HorizontalAlignment="Left"
                    View="SmallProfilePhotoLeft"
                    AllowSignInAsDifferentUser="False"
                    SignInCompleted="Login_SignInCompleted"
                    SignOutCompleted="Login_SignOutCompleted"
/>

Then add the following functions in MainPage.xaml.cs.

private void Login_SignInCompleted(object sender, Microsoft.Toolkit.Uwp.UI.Controls.Graph.SignInEventArgs e)
{
    //Set the auth state
    SetAuthState(true);
    //Reload the home page
    RootFrame.Navigate(typeof(HomePage));
}
 
private void Login_SignOutCompleted(object sender, EventArgs e)
{
    //Set the auth state
    SetAuthState(false);
    //Reload the home page
    RootFrame.Navigate(typeof(HomePage));
}

Finally, open HomePage.xaml.cs and add the following code after the line this.InitializeComponent();

if ((App.Current as App).IsAuthenticated)
{
    HomePageMessage.Text = "Welcome! Please use the menu to the left to select a view.";
}

Restart the app and click the Sign In control at the top of the app. Once you've signed in, the UI should change to indicate that you've successfully signed-in.

Sign-In with your account

Get the Excel file from OneDrive:

Right-click the graph-tutorial project in Solution Explorer and select Add -> New Item. Choose Blank Page, enter OneDrivePage.xaml in the Name field, and select Add.

Open OneDrivePage.xaml and add the following line inside the existing <Grid> element.

XML

<TextBlock x:Name="Events" TextWrapping="Wrap"/>

Open OneDrivePage.xaml.cs and add the following using statements at the top of the file.

using Microsoft.Toolkit.Services.MicrosoftGraph;
using Microsoft.Toolkit.Uwp.UI.Controls;
using Syncfusion.XlsIO;
using Microsoft.Graph;

Add the following functions to the OneDrivePage class.

private void ShowNotification(string message)
{
    //Get the main page that contains the InAppNotification
    var mainPage = (Window.Current.Content as Frame).Content as MainPage;
 
    //Get the notification control
    var notification = mainPage.FindName("Notification") as InAppNotification;
 
    notification.Show(message);
}
 
protected override async void OnNavigatedTo(NavigationEventArgs e)
{
    //Get the Graph client from the service
    var graphClient = MicrosoftGraphService.Instance.GraphProvider;
 
    try
    {
        //Initialize ExcelEngine
        ExcelEngine excelEngine = new ExcelEngine();
 
        //Initialize IApplication
        IApplication application = excelEngine.Excel;
 
        //Enable fast record parsing
        application.UseFastRecordParsing = true;
 
        //Get the file from OneDrive into stream
        var stream = await graphClient.Me.Drive.Root.ItemWithPath("Sample.xlsx").Content.Request().GetAsync();
 
        //Set the stream position as '0'
        stream.Position = 0;
 
        //Initialize new MemoryStream and copy the existing stream to this new MemeoryStream
        MemoryStream file = new MemoryStream();
        stream.CopyTo(file);
 
        //Set the stream position as '0'
        file.Position = 0;
 
        //Load the stream into IWorkbook
        IWorkbook workbook = await application.Workbooks.OpenAsync(file);
 
        //Modify the Excel document
        workbook.Worksheets[0].Range["A1"].Text = "Syncfusion Essential XlsIO";
 
        //Save the modified Excel document as new MemoryStream
        MemoryStream outputStream = new MemoryStream();
        await workbook.SaveAsAsync(outputStream);
 
        //Set the stream position as '0'
        outputStream.Position = 0;
 
        //Upload the modified Excel document to OneDrive again
        await graphClient.Me.Drive.Root.ItemWithPath("ModifiedExcel.xlsx").Content.Request().PutAsync<DriveItem>(outputStream);
 
        //Close the workbook
        workbook.Close();
 
        //Dispose the ExcelEngine
        excelEngine.Dispose();
    }
 
    catch (Microsoft.Graph.ServiceException ex)
    {
        ShowNotification($"Exception getting events: {ex.Message}");
    }
 
    base.OnNavigatedTo(e);
}

Consider with the code in OnNavigatedTo is doing

The URL that will be called is /v1.0/me/events.
The Select function limits the fields returned for each event to just those the view will use.
The OrderBy function sorts the results by date and time they were created, with the most recent item being first.

Just before running the app, to be able to navigate to this onedrive page, modify the NavView_ItemInvoked method in the MainPage.xaml.cs file to replace the throw new NotImplementedException(); line with as follows.

case "onedrive":
    RootFrame.Navigate(typeof(OneDrivePage));
    break;

You can now run the app, sign in, and click the OneDrive navigation item in the left-hand menu.

The Excel document should be downloaded, modified and uploaded again into OneDrive.

A complete working example to download an Excel document from OneDrive, Modify it and upload the modified document to OneDrive, can be downloaded from MSGraphTutorial.

Take a moment to peruse the documentation, where you can find basic worksheet data manipulation options along with features like Conditional Formatting, worksheet calculations through Formulas, adding Charts in worksheet or workbook, organizing and analyzing data through Tables and Pivot Tables, appending multiple records to worksheet using Template Markers, and most importantly PDF and Image conversions with code examples.

Refer here to explore the rich set of Syncfusion Excel (XlsIO) library features.

Note:

Starting with v16.2.0.x, if you reference Syncfusion assemblies from trial setup or from the NuGet feed, include a license key in your projects. Refer the link to learn about generating and registering Syncfusion license key in your application to use the components without trail message.

Did you find this information helpful?

Yes

Comments (0)

Download, Edit and Upload the Excel file in One Drive using Microsoft Graph

Steps to download Excel file from OneDrive, Edit and upload again programmatically:

Access denied