.NET MAUIでExcelファイルを作成、読み取り、および編集

<?xml version="1.0" encoding="utf-8" ?>
<ContentPage xmlns="http://schemas.microsoft.com/dotnet/2021/maui"
             xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"
             x:Class="MAUI_IronXL.MainPage">

    <ScrollView>
        <VerticalStackLayout
            Spacing="25"
            Padding="30,0"
            VerticalOptions="Center">

            <Label
                Text="Welcome to .NET Multi-platform App UI"
                SemanticProperties.HeadingLevel="Level2"
                SemanticProperties.Description="Welcome Multi-platform App UI"
                FontSize="18"
                HorizontalOptions="Center" />

            <Button
                x:Name="createBtn"
                Text="Create Excel File"
                SemanticProperties.Hint="Click on the button to create Excel file"
                Clicked="CreateExcel"
                HorizontalOptions="Center" />

            <Button
                x:Name="readExcel"
                Text="Read and Modify Excel file"
                SemanticProperties.Hint="Click on the button to read Excel file"
                Clicked="ReadExcel"
                HorizontalOptions="Center" />

        </VerticalStackLayout>
    </ScrollView>

</ContentPage>

private void CreateExcel(object sender, EventArgs e)
    {
        //Create Workbook
        WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);

        //Create Worksheet
        var sheet = workbook.CreateWorkSheet("2022 Budget");

        //Set Cell values
        sheet ["A1"].Value = "January";
        sheet ["B1"].Value = "February";
        sheet ["C1"].Value = "March";
        sheet ["D1"].Value = "April";
        sheet ["E1"].Value = "May";
        sheet ["F1"].Value = "June";
        sheet ["G1"].Value = "July";
        sheet ["H1"].Value = "August";

        //Set Cell input Dynamically
        Random r = new Random();
        for (int i = 2; i <= 11; i++)
        {
            sheet ["A" + i].Value = r.Next(1, 1000);
            sheet ["B" + i].Value = r.Next(1000, 2000);
            sheet ["C" + i].Value = r.Next(2000, 3000);
            sheet ["D" + i].Value = r.Next(3000, 4000);
            sheet ["E" + i].Value = r.Next(4000, 5000);
            sheet ["F" + i].Value = r.Next(5000, 6000);
            sheet ["G" + i].Value = r.Next(6000, 7000);
            sheet ["H" + i].Value = r.Next(7000, 8000);
        }

        //Apply formatting like background and border
        sheet ["A1:H1"].Style.SetBackgroundColor("#d3d3d3");
        sheet ["A1:H1"].Style.TopBorder.SetColor("#000000");
        sheet ["A1:H1"].Style.BottomBorder.SetColor("#000000");
        sheet ["H2:H11"].Style.RightBorder.SetColor("#000000");
        sheet ["H2:H11"].Style.RightBorder.Type = IronXL.Styles.BorderType.Medium;
        sheet ["A11:H11"].Style.BottomBorder.SetColor("#000000");
        sheet ["A11:H11"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Medium;

        //Apply Formulas
        decimal sum = sheet ["A2:A11"].Sum();
        decimal avg = sheet ["B2:B11"].Avg();
        decimal max = sheet ["C2:C11"].Max();
        decimal min = sheet ["D2:D11"].Min();

        sheet ["A12"].Value = "Sum";
        sheet ["B12"].Value = sum;

        sheet ["C12"].Value = "Avg";
        sheet ["D12"].Value = avg;

        sheet ["E12"].Value = "Max";
        sheet ["F12"].Value = max;

        sheet ["G12"].Value = "Min";
        sheet ["H12"].Value = min;

        //Save and Open Excel File
        SaveService saveService = new SaveService();
        saveService.SaveAndView("Budget.xlsx", "application/octet-stream", workbook.ToStream());
    }

Private Sub CreateExcel(ByVal sender As Object, ByVal e As EventArgs)
		'Create Workbook
		Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)

		'Create Worksheet
		Dim sheet = workbook.CreateWorkSheet("2022 Budget")

		'Set Cell values
		sheet ("A1").Value = "January"
		sheet ("B1").Value = "February"
		sheet ("C1").Value = "March"
		sheet ("D1").Value = "April"
		sheet ("E1").Value = "May"
		sheet ("F1").Value = "June"
		sheet ("G1").Value = "July"
		sheet ("H1").Value = "August"

		'Set Cell input Dynamically
		Dim r As New Random()
		For i As Integer = 2 To 11
			sheet ("A" & i).Value = r.Next(1, 1000)
			sheet ("B" & i).Value = r.Next(1000, 2000)
			sheet ("C" & i).Value = r.Next(2000, 3000)
			sheet ("D" & i).Value = r.Next(3000, 4000)
			sheet ("E" & i).Value = r.Next(4000, 5000)
			sheet ("F" & i).Value = r.Next(5000, 6000)
			sheet ("G" & i).Value = r.Next(6000, 7000)
			sheet ("H" & i).Value = r.Next(7000, 8000)
		Next i

		'Apply formatting like background and border
		sheet ("A1:H1").Style.SetBackgroundColor("#d3d3d3")
		sheet ("A1:H1").Style.TopBorder.SetColor("#000000")
		sheet ("A1:H1").Style.BottomBorder.SetColor("#000000")
		sheet ("H2:H11").Style.RightBorder.SetColor("#000000")
		sheet ("H2:H11").Style.RightBorder.Type = IronXL.Styles.BorderType.Medium
		sheet ("A11:H11").Style.BottomBorder.SetColor("#000000")
		sheet ("A11:H11").Style.BottomBorder.Type = IronXL.Styles.BorderType.Medium

		'Apply Formulas
		Dim sum As Decimal = sheet ("A2:A11").Sum()
		Dim avg As Decimal = sheet ("B2:B11").Avg()
		Dim max As Decimal = sheet ("C2:C11").Max()
		Dim min As Decimal = sheet ("D2:D11").Min()

		sheet ("A12").Value = "Sum"
		sheet ("B12").Value = sum

		sheet ("C12").Value = "Avg"
		sheet ("D12").Value = avg

		sheet ("E12").Value = "Max"
		sheet ("F12").Value = max

		sheet ("G12").Value = "Min"
		sheet ("H12").Value = min

		'Save and Open Excel File
		Dim saveService As New SaveService()
		saveService.SaveAndView("Budget.xlsx", "application/octet-stream", workbook.ToStream())
End Sub

private void ReadExcel(object sender, EventArgs e)
{
    //store the path of a file
    string filepath="C:\Files\Customer Data.xlsx";
    WorkBook workbook = WorkBook.Load(filepath);
    WorkSheet sheet = workbook.WorkSheets.First();

    decimal sum = sheet ["B2:B10"].Sum();

    sheet ["B11"].Value = sum;
    sheet ["B11"].Style.SetBackgroundColor("#808080");
    sheet ["B11"].Style.Font.SetColor("#ffffff");

    //Save and Open Excel File
    SaveService saveService = new SaveService();
    saveService.SaveAndView("Modified Data.xlsx", "application/octet-stream", workbook.ToStream());

    DisplayAlert("Notification", "Excel file has been modified!", "OK");
}

Private Sub ReadExcel(ByVal sender As Object, ByVal e As EventArgs)
	'store the path of a file
	Dim filepath As String="C:\Files\Customer Data.xlsx"
	Dim workbook As WorkBook = WorkBook.Load(filepath)
	Dim sheet As WorkSheet = workbook.WorkSheets.First()

	Dim sum As Decimal = sheet ("B2:B10").Sum()

	sheet ("B11").Value = sum
	sheet ("B11").Style.SetBackgroundColor("#808080")
	sheet ("B11").Style.Font.SetColor("#ffffff")

	'Save and Open Excel File
	Dim saveService As New SaveService()
	saveService.SaveAndView("Modified Data.xlsx", "application/octet-stream", workbook.ToStream())

	DisplayAlert("Notification", "Excel file has been modified!", "OK")
End Sub

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MAUI_IronXL
{
    public partial class SaveService
    {
            public partial void SaveAndView(string fileName, string contentType, MemoryStream stream);
    }
}

Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports System.Threading.Tasks

Namespace MAUI_IronXL
	Partial Public Class SaveService
			Public Partial Private Sub SaveAndView(ByVal fileName As String, ByVal contentType As String, ByVal stream As MemoryStream)
			End Sub
	End Class
End Namespace

using Windows.Storage;
using Windows.Storage.Pickers;
using Windows.Storage.Streams;
using Windows.UI.Popups;

namespace MAUI_IronXL
{
    public partial class SaveService
    {
        public async partial void SaveAndView(string fileName, string contentType, MemoryStream stream)
        {
            StorageFile stFile;
            string extension = Path.GetExtension(fileName);
            //Gets process windows handle to open the dialog in application process.
            IntPtr windowHandle = System.Diagnostics.Process.GetCurrentProcess().MainWindowHandle;
            if (!Windows.Foundation.Metadata.ApiInformation.IsTypePresent("Windows.Phone.UI.Input.HardwareButtons"))
            {
                //Creates file save picker to save a file.
                FileSavePicker savePicker = new FileSavePicker();
                savePicker.DefaultFileExtension = ".xlsx";
                savePicker.SuggestedFileName = fileName;
                //Saves the file as Excel file.
                savePicker.FileTypeChoices.Add("XLSX", new List<string>() { ".xlsx" });

                WinRT.Interop.InitializeWithWindow.Initialize(savePicker, windowHandle);
                stFile = await savePicker.PickSaveFileAsync();
            }
            else
            {
                StorageFolder local = ApplicationData.Current.LocalFolder;
                stFile = await local.CreateFileAsync(fileName, CreationCollisionOption.ReplaceExisting);
            }
            if (stFile != null)
            {
                using (IRandomAccessStream zipStream = await stFile.OpenAsync(FileAccessMode.ReadWrite))
                {
                    //Writes compressed data from memory to file.
                    using(Stream outstream = zipStream.AsStreamForWrite())
                    {
                     outstream.SetLength(0);
                     //Saves the stream as file.
                     byte [] buffer = outstream.ToArray();
                     outstream.Write(buffer, 0, buffer.Length);
                     outstream.Flush();
                    }
                }
                //Create message dialog box.
                MessageDialog msgDialog = new("Do you want to view the document?", "File has been created successfully");
                UICommand yesCmd = new("Yes");
                msgDialog.Commands.Add(yesCmd);
                UICommand noCmd = new("No");
                msgDialog.Commands.Add(noCmd);

                WinRT.Interop.InitializeWithWindow.Initialize(msgDialog, windowHandle);

                //Showing a dialog box.
                IUICommand cmd = await msgDialog.ShowAsync();
                if (cmd.Label == yesCmd.Label)
                {
                    //Launch the saved file.
                    await Windows.System.Launcher.LaunchFileAsync(stFile);
                }
            }
        }
    }
}

Imports Windows.Storage
Imports Windows.Storage.Pickers
Imports Windows.Storage.Streams
Imports Windows.UI.Popups

Namespace MAUI_IronXL
	Partial Public Class SaveService
		Public Async Sub SaveAndView(ByVal fileName As String, ByVal contentType As String, ByVal stream As MemoryStream)
			Dim stFile As StorageFile
			Dim extension As String = Path.GetExtension(fileName)
			'Gets process windows handle to open the dialog in application process.
			Dim windowHandle As IntPtr = System.Diagnostics.Process.GetCurrentProcess().MainWindowHandle
			If Not Windows.Foundation.Metadata.ApiInformation.IsTypePresent("Windows.Phone.UI.Input.HardwareButtons") Then
				'Creates file save picker to save a file.
				Dim savePicker As New FileSavePicker()
				savePicker.DefaultFileExtension = ".xlsx"
				savePicker.SuggestedFileName = fileName
				'Saves the file as Excel file.
				savePicker.FileTypeChoices.Add("XLSX", New List(Of String)() From {".xlsx"})

				WinRT.Interop.InitializeWithWindow.Initialize(savePicker, windowHandle)
				stFile = Await savePicker.PickSaveFileAsync()
			Else
				Dim local As StorageFolder = ApplicationData.Current.LocalFolder
				stFile = Await local.CreateFileAsync(fileName, CreationCollisionOption.ReplaceExisting)
			End If
			If stFile IsNot Nothing Then
				Using zipStream As IRandomAccessStream = Await stFile.OpenAsync(FileAccessMode.ReadWrite)
					'Writes compressed data from memory to file.
					Using outstream As Stream = zipStream.AsStreamForWrite()
					 outstream.SetLength(0)
					 'Saves the stream as file.
					 Dim buffer() As Byte = outstream.ToArray()
					 outstream.Write(buffer, 0, buffer.Length)
					 outstream.Flush()
					End Using
				End Using
				'Create message dialog box.
				Dim msgDialog As New MessageDialog("Do you want to view the document?", "File has been created successfully")
				Dim yesCmd As New UICommand("Yes")
				msgDialog.Commands.Add(yesCmd)
				Dim noCmd As New UICommand("No")
				msgDialog.Commands.Add(noCmd)

				WinRT.Interop.InitializeWithWindow.Initialize(msgDialog, windowHandle)

				'Showing a dialog box.
				Dim cmd As IUICommand = Await msgDialog.ShowAsync()
				If cmd.Label = yesCmd.Label Then
					'Launch the saved file.
					Await Windows.System.Launcher.LaunchFileAsync(stFile)
				End If
			End If
		End Sub
	End Class
End Namespace