Export de données sous format Excel dans Xamarin.Forms

Dans cet article , nous verrons comment exporter les données qui alimentent une ListView de Xamarin.Forms sous format Excel en utilisant le NuGet package DocumentFormat.OpenXml.

Création du projet Cross-platform Xamarin

Figure 1: Création du projet

Les librairies dont nous avons besoin

1. Autofac: Pour faire l’inversion de contrôle.
2. DocumentFormat.OpenXml: Pour l’export de données sous format Excel.
3. Plugin.Permissions: Pour la gestion des permissions de stockage.

Initialisations et Configurations

Dans cette partie, nous allons initialiser le troisième plugin pour la plateforme Android.

Projet Android

• Initialisation dans le fichier MainActivity.cs

protected override void OnCreate(Bundle bundle)
{         
// ...
    Plugin.CurrentActivity.CrossCurrentActivity.Current.Init(this, bundle);
// ...
}

public override void OnRequestPermissionsResult(int requestCode, string[] permissions, Permission[] grantResults)
{
    PermissionsImplementation.Current.OnRequestPermissionsResult(requestCode, permissions, grantResults);
    base.OnRequestPermissionsResult(requestCode, permissions, grantResults);
}

• Accord de la permission de stockage dans le fichier AssemblyInfo.cs

[assembly: UsesPermission(Android.Manifest.Permission.WriteExternalStorage)]

Pourquoi avons-nous encore besoin d’un Plugin de gestion de permission si cela peut être effectuée dans le fichier AssemblyInfo.cs ?

En effet, l’accord de la permission de stockage telle que présenté ne passe pas sur toute les plateformes Android. Nous aurons par biais de Plugin.Permissions, à vérifier lors de l’export de données, si nous avons l’autorisation de stocker des données sur le stockage interne du téléphone(sinon l’accorder).

Codes du projet partagé

Le principe ici est de créer une collection d’objets servant de source pour une ListView et de pouvoir exporter cette source de données.

Un Model très simple

using System;
using System.Collections.Generic;
using System.Text;

namespace ExportDataToExcel.Models
{
    public class XFDeveloper
    {
        public int ID { get; set; }
        public string FullName { get; set; }
        public string Phone { get; set; }
    }
}

Un Service nous fournissant une liste d’objets de notre Model

using ExportDataToExcel.Models;
using System.Collections.Generic;

namespace ExportDataToExcel.Services
{
    public class XFDeveloperService
    {
        public static List<XFDeveloper> GetAllXamarinDevelopers()
        {
            List<XFDeveloper> developers = new List<XFDeveloper>
            {
                new XFDeveloper
                {
                    ID = 1,
                    FullName = "James Montemagno",
                    Phone = "+00 0000 0001"
                },
                new XFDeveloper
                {
                    ID = 2,
                    FullName = "Leomaris Rayes",
                    Phone = "+00 0000 0002"
                },
                new XFDeveloper
                {
                    ID = 3,
                    FullName = "K. Laurent egbakou",
                    Phone = "+00 0000 0003"
                },
                new XFDeveloper
                {
                    ID = 4,
                    FullName = "Houssem Dellai",
                    Phone = "+00 0000 0004"
                },
                new XFDeveloper
                {
                    ID = 5,
                    FullName = "Yves Gaston",
                    Phone = "+00 0000 0005"
                },
                new XFDeveloper
                {
                    ID = 6,
                    FullName = "John Doe",
                    Phone = "+00 0000 0006"
                },
                new XFDeveloper
                {
                    ID = 7,
                    FullName = "Marcel Adama",
                    Phone = "+00 0000 0007"
                },
                new XFDeveloper
                {
                    ID = 8,
                    FullName = "Carlos Ognankotan",
                    Phone = "+00 0000 0008"
                }
            };

            return developers;
        }                        
    }
}

La vue (nous voulons exporter L’itemSource de notre ListView au format Excel)

<?xml version="1.0" encoding="utf-8" ?>
<ContentPage
    x:Class="ExportDataToExcel.Views.MainMenuView"
    xmlns="http://xamarin.com/schemas/2014/forms"
    xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"
    Title="{Binding Title}">

    <ContentPage.ToolbarItems>
        <ToolbarItem
            Command="{Binding ExportToExcelCommand}"
            Icon="shareexcel.png"
            Text="Export excel" />
    </ContentPage.ToolbarItems>

    <ContentPage.Content>
        <StackLayout>
            <StackLayout VerticalOptions="FillAndExpand">
                <ListView
                    CachingStrategy="RecycleElement"
                    HasUnevenRows="False"
                    ItemsSource="{Binding Developers}"
                    SeparatorColor="Transparent"
                    SeparatorVisibility="None">
                    <ListView.RowHeight>
                        <OnIdiom
                            x:TypeArguments="x:Int32"
                            Phone="80"
                            Tablet="120" />
                    </ListView.RowHeight>
                    <ListView.ItemTemplate>
                        <DataTemplate>
                            <ViewCell>
                                <Grid ColumnSpacing="20" RowSpacing="0">
                                    <Grid.ColumnDefinitions>
                                        <ColumnDefinition Width="10" />
                                        <ColumnDefinition Width="Auto" />
                                        <ColumnDefinition Width="Auto" />
                                    </Grid.ColumnDefinitions>

                                    <Grid.RowDefinitions>
                                        <RowDefinition Height="*" />
                                        <RowDefinition Height="1" />
                                    </Grid.RowDefinitions>
                                    
                                    <BoxView
                                        Grid.Row="0"
                                        Grid.Column="0"
                                        BackgroundColor="LightGray"
                                        HorizontalOptions="Start"
                                        WidthRequest="10" />

                                    <Image
                                        Grid.Row="0"
                                        Grid.Column="1"
                                        HeightRequest="50"
                                        Source="user.png"
                                        WidthRequest="50" />

                                    <StackLayout
                                        Grid.Row="0"
                                        Grid.Column="2"
                                        HorizontalOptions="FillAndExpand"
                                        Orientation="Vertical"
                                        VerticalOptions="Center">
                                        <!--  FULLNAME  -->
                                        <Label
                                            FontSize="18"
                                            HorizontalOptions="FillAndExpand"
                                            Text="{Binding FullName}"
                                            VerticalOptions="Start" />
                                        <!--  PHONE  -->
                                        <Label
                                            FontSize="15"
                                            Text="{Binding Phone}"
                                            TextColor="#43A047"
                                            VerticalOptions="Start" />
                                    </StackLayout>

                                    <BoxView
                                        Grid.Row="1"
                                        Grid.Column="0"
                                        Grid.ColumnSpan="4"
                                        BackgroundColor="LightGray"
                                        HeightRequest="1"
                                        Opacity=".5"
                                        VerticalOptions="End" />
                                </Grid>
                            </ViewCell>
                        </DataTemplate>
                    </ListView.ItemTemplate>
                </ListView>
            </StackLayout>
        </StackLayout>
    </ContentPage.Content>
</ContentPage>

Le code behind correspondant est :

using ExportDataToExcel.ViewModels;

using Xamarin.Forms;
using Xamarin.Forms.Xaml;

namespace ExportDataToExcel.Views
{
    [XamlCompilation(XamlCompilationOptions.Compile)]
    public partial class MainMenuView : ContentPage
    {
        private MainMenuViewModel viewModel;
        public MainMenuView()
        {
            InitializeComponent();
            BindingContext = viewModel = new MainMenuViewModel();
            RegisterMesssages();
        }


        private void RegisterMesssages()
        {
            MessagingCenter.Subscribe<MainMenuViewModel>(this, "DataExportedSuccessfully", (m) =>
            {
                if (m != null)
                {
                    DisplayAlert("Info", "Data exported Successfully. The location is :"+m.FilePath, "OK");
                }
            });

            MessagingCenter.Subscribe<MainMenuViewModel>(this, "NoDataToExport", (m) =>
            {
                if (m != null)
                {
                    DisplayAlert("Warning !", "No data to export.", "OK");
                }
            });
        }

    }
}

Interface d’accès au stockage interne du téléphone

using System;
using System.Collections.Generic;
using System.Text;

namespace ExportDataToExcel.Interfaces
{
    public interface IExportFilesToLocation
    {
        string GetFolderLocation();
    }
}

Chaque Plateforme implémentera cette interface. Sur la plateforme Android, nous allons créer un dossier Export sur le stockage interne et y stocker le fichier Excel et sur IOS, l’export se fera dans le dossier Documents. L’idée est d’obtenir un emplacement de stockage en utilisant un DependencyService.

• Plateforme Android

using ExportDataToExcel.Droid;
using ExportDataToExcel.Interfaces;
using Java.IO;
using Xamarin.Forms;

[assembly: Dependency(typeof(ExportFilesToLocation))]
namespace ExportDataToExcel.Droid
{
    public class ExportFilesToLocation : IExportFilesToLocation
    {
        public ExportFilesToLocation()
        {
        }

        public string GetFolderLocation()
        {
            string root = null;
            if (Android.OS.Environment.IsExternalStorageEmulated)
            {
                root = Android.OS.Environment.ExternalStorageDirectory.AbsolutePath;
            }
            else
                root = System.Environment.GetFolderPath(System.Environment.SpecialFolder.MyDocuments);

            File myDir = new File(root + "/Exports");
            if(!myDir.Exists())
                myDir.Mkdir();

            return root + "/Exports/";
        }
    }
}

• IOS

using ExportDataToExcel.Interfaces;
using ExportDataToExcel.iOS;
using Xamarin.Forms;

[assembly: Dependency(typeof(ExportFilesToLocation))]
namespace ExportDataToExcel.iOS
{
    public class ExportFilesToLocation : IExportFilesToLocation
    {
        public ExportFilesToLocation()
        {
        }

        public string GetFolderLocation()
        {
            string root = System.Environment.GetFolderPath(System.Environment.SpecialFolder.MyDocuments);
            return root + "/";
        }
    }
}

Pour accéder à l’emplacement de stockage quelque soit la plateforme, la ligne de code suivante suffit:

var path = DependencyService.Get<IExportFilesToLocation>().GetFolderLocation();

Le View Model

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using ExportDataToExcel.Interfaces;
using ExportDataToExcel.Models;
using ExportDataToExcel.Services;
using Plugin.Permissions;
using Plugin.Permissions.Abstractions;
using System;
using System.Collections.ObjectModel;
using System.Diagnostics;
using System.Linq;
using System.Windows.Input;
using Xamarin.Forms;
using Cell = DocumentFormat.OpenXml.Spreadsheet.Cell;

namespace ExportDataToExcel.ViewModels
{
    public class MainMenuViewModel : BaseViewModel
    {
       
        public MainMenuViewModel()
        {
            Title = "Xamarin Developers";
            LoadData();
            ExportToExcelCommand = new Command(async () => await ExportDataToExcelAsync());
        }

        /* Get Xamarin developers list from Service*/
        private void LoadData()
        {
            Developers = new ObservableCollection<XFDeveloper>(XFDeveloperService.GetAllXamarinDevelopers());
        }


        /* Export the list to excel file at the location provide by DependencyService */
        public async System.Threading.Tasks.Task ExportDataToExcelAsync()
        {
            // Granted storage permission
            var storageStatus = await CrossPermissions.Current.CheckPermissionStatusAsync(Permission.Storage);

            if (storageStatus != PermissionStatus.Granted)
            {
                var results = await CrossPermissions.Current.RequestPermissionsAsync(new[] { Permission.Storage });
                storageStatus = results[Permission.Storage];
            }

            if (Developers.Count() > 0)
            {
                try
                {
                    string date = DateTime.Now.ToShortDateString();
                    date = date.Replace("/", "_");

                    var path = DependencyService.Get<IExportFilesToLocation>().GetFolderLocation() + "xfdevelopers" + date + ".xlsx";
                    FilePath = path;
                    using (SpreadsheetDocument document = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook))
                    {
                        WorkbookPart workbookPart = document.AddWorkbookPart();
                        workbookPart.Workbook = new Workbook();

                        WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                        worksheetPart.Worksheet = new Worksheet();

                        Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
                        Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Xamarin Forms developers list" };
                        sheets.Append(sheet);

                        workbookPart.Workbook.Save();

                        SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());

                        // Constructing header
                        Row row = new Row();

                        row.Append(
                            ConstructCell("No", CellValues.String),
                            ConstructCell("FullName", CellValues.String),
                            ConstructCell("Phone", CellValues.String)
                            );

                        // Insert the header row to the Sheet Data
                        sheetData.AppendChild(row);

                        // Add each product
                        foreach (var d in Developers)
                        {
                            row = new Row();
                            row.Append(
                                ConstructCell(d.ID.ToString(), CellValues.String),
                                ConstructCell(d.FullName, CellValues.String),
                                ConstructCell(d.Phone, CellValues.String));
                            sheetData.AppendChild(row);
                        }

                        worksheetPart.Worksheet.Save();
                        MessagingCenter.Send(this, "DataExportedSuccessfully");
                    }

                }
                catch (Exception e)
                {
                    Debug.WriteLine("ERROR: "+ e.Message);
                }
            }
            else
            {
                MessagingCenter.Send(this, "NoDataToExport");
            }
        }


        /* To create cell in Excel */
        private Cell ConstructCell(string value, CellValues dataType)
        {
            return new Cell()
            {
                CellValue = new CellValue(value),
                DataType = new EnumValue<CellValues>(dataType)
            };
        }


        public ICommand ExportToExcelCommand { get; set; }

        private ObservableCollection<XFDeveloper> _developers;
        public ObservableCollection<XFDeveloper> Developers
        {
            get { return _developers; }
            set { SetProperty(ref _developers, value); }
        }

        private string _filePath;
        public string FilePath
        {
            get { return _filePath; }
            set { SetProperty(ref _filePath, value); }
        }

    }   
}

Résultat de notre travail

Ressources

Voici le code source de ce projet sur Github.