Create an Excel Output Xamarin. Forms

Question

I'm trying to get an Excel output from my program with a Butten click. I have multiple list views with Entries in them that user can define the values. I don't want to use syncfusion because of license issues!

Here it is my listview:

<ListView x:Name="LL" Grid.Row="3"   ItemsSource="{Binding energy}" HeightRequest="300" >
                        <ListView.ItemTemplate>
                            <DataTemplate>
                                <ViewCell>

                                    <Grid  Padding="5" BackgroundColor="White" RowSpacing="40" >
                                        <Grid.RowDefinitions>
                                            <RowDefinition Height="50"></RowDefinition>
                                            <RowDefinition Height="50"></RowDefinition>
                                        </Grid.RowDefinitions>

                                        <Label x:Name="label" HorizontalOptions="Center" Text="{Binding EE}" FontSize="18"  TextColor="Black" Grid.Column="0" Grid.Row="0" />
                                        <Entry TextChanged="Entry_TextChanged" HorizontalOptions="Center" VerticalOptions="Center" Keyboard="Numeric" Placeholder=". . . . . . . ." Grid.Column="1" Grid.Row="0" BackgroundColor="Beige" WidthRequest="80" />
                                        <Entry TextChanged="Entry_TextChanged_1" HorizontalOptions="Center" VerticalOptions="Center" Keyboard="Numeric" Placeholder=". . . . . . . ." Grid.Column="2" Grid.Row="0" BackgroundColor="Beige" WidthRequest="80"/>
                                        <Entry TextChanged="Entry_TextChanged_2" HorizontalOptions="Center" VerticalOptions="Center" Keyboard="Numeric" Placeholder=". . . . . . . ." Grid.Column="3" Grid.Row="0" BackgroundColor="Beige" WidthRequest="80"/>
                                        <BoxView Grid.Column="0" Grid.Row="0" BackgroundColor="Black" WidthRequest="1" HorizontalOptions="EndAndExpand" VerticalOptions="FillAndExpand"/>
                                        <BoxView Grid.Column="1" Grid.Row="0" BackgroundColor="Black" WidthRequest="1" HorizontalOptions="EndAndExpand" VerticalOptions="FillAndExpand"/>
                                        <BoxView Grid.Column="2" Grid.Row="0" BackgroundColor="Black" WidthRequest="1" HorizontalOptions="EndAndExpand" VerticalOptions="FillAndExpand"/>


                                    </Grid>

                                </ViewCell>
                            </DataTemplate>
                        </ListView.ItemTemplate>
                    </ListView>

Answer 1

I am not sure whether you want to save the value in three entries. So I create three properties E1, E2, E3. For the code below, it only save the EE property value into excel.

Install DocumentFormat.OpenXml package in NuGet.

Install Excel app in your device.

Xaml:

Code behind:

 public partial class Page15 : ContentPage
{
    public Page15()
    {
        InitializeComponent();

        this.BindingContext = new ExportingExcelViewModel();
    }

    private void Entry_TextChanged(object sender, TextChangedEventArgs e)
    {

    }

    private void Entry_TextChanged_1(object sender, TextChangedEventArgs e)
    {

    }

    private void Entry_TextChanged_2(object sender, TextChangedEventArgs e)
    {

    }
}
public class EEModel
{
    public string EE { get; set; }
    public string E1 { get; set; }
    public string E2 { get; set; }
    public string E3 { get; set; }
}

public class ExcelStructure
{
    public List<string> Headers { get; set; } = new List<string>();
    public List<List<string>> Values { get; set; } = new List<List<string>>();
}

public class ExcelService
{
    private string AppFolder => Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData);

    private Cell ConstructCell(string value, CellValues dataTypes) =>
        new Cell()
        {
            CellValue = new CellValue(value),
            DataType = new EnumValue<CellValues>(dataTypes)
        };

    public string GenerateExcel(String fileName)
    {
        Environment.SetEnvironmentVariable("MONO_URI_DOTNETRELATIVEORABSOLUTE", "true");

        // Creating the SpreadsheetDocument in the indicated FilePath
        var filePath = Path.Combine(AppFolder, fileName);
        var document = SpreadsheetDocument.Create(Path.Combine(AppFolder, fileName), SpreadsheetDocumentType.Workbook);

        var wbPart = document.AddWorkbookPart();
        wbPart.Workbook = new Workbook();

        var part = wbPart.AddNewPart<WorksheetPart>();
        part.Worksheet = new Worksheet(new SheetData());

        //  Here are created the sheets, you can add all the child sheets that you need.
        var sheets = wbPart.Workbook.AppendChild
            (
               new Sheets(
                        new Sheet()
                        {
                            Id = wbPart.GetIdOfPart(part),
                            SheetId = 1,
                            Name = "Employment"
                        }
                    )
            );


        // Just save and close you Excel file
        wbPart.Workbook.Save();
        document.Close();
        // Dont't forget return the filePath
        return filePath;
    }

    public void InsertDataIntoSheet(string fileName, string sheetName, ExcelStructure data)
    {
        Environment.SetEnvironmentVariable("MONO_URI_DOTNETRELATIVEORABSOLUTE", "true");

        using (var document = SpreadsheetDocument.Open(fileName, true))
        {
            var wbPart = document.WorkbookPart;
            var sheets = wbPart.Workbook.GetFirstChild<Sheets>().
                         Elements<Sheet>().FirstOrDefault().
                         Name = sheetName;

            var part = wbPart.WorksheetParts.First();
            var sheetData = part.Worksheet.Elements<SheetData>().First();

            var row = sheetData.AppendChild(new Row());

            foreach (var header in data.Headers)
            {
                row.Append(ConstructCell(header, CellValues.String));
            }

            foreach (var value in data.Values)
            {
                var dataRow = sheetData.AppendChild(new Row());

                foreach (var dataElement in value)
                {
                    dataRow.Append(ConstructCell(dataElement, CellValues.String));
                }
            }
            wbPart.Workbook.Save();
        }
    }
}

public class ExportingExcelViewModel
{
    public ICommand ExportToExcelCommand { private set; get; }
    private ExcelService excelService;
    public ObservableCollection<EEModel> energy { get; set; }


    public ExportingExcelViewModel()
    {
        energy = new ObservableCollection<EEModel>
        {
            new EEModel{  EE="A",  },
            new EEModel{  EE="B",  },
            new EEModel{  EE="C",  },
            new EEModel{  EE="D",  },
            new EEModel{  EE="E",  },

        };

        ExportToExcelCommand = new Command(async () => await ExportToExcel());
        excelService = new ExcelService();
    }

    async Task ExportToExcel()
    {
        var fileName = "Employ.xlsx";
        string filepath = excelService.GenerateExcel(fileName);

        var data = new ExcelStructure
        {
            Headers = new List<string>() { "EE", "E1", "E2" }
        };

        foreach (var item in energy)
        {
            data.Values.Add(new List<string>() { item.EE, item.E1, item.E2 });
        }

        excelService.InsertDataIntoSheet(filepath, "Employ", data);

        await Launcher.OpenAsync(new OpenFileRequest()
        {
            File = new ReadOnlyFile(filepath)
        });
    }


}