ExcelからSQLiteを使う時の方法として

エクセルからSQLiteを使う時に、[SQLiteForExcel](https://github.com/govert/SQLiteForExcel)というのを使用していましたが、[Excel-DNA](https://github.com/Excel-DNA/ExcelDna)でxllを作ってみました。
データベースはマイグレーションしたいのでEntityFrameworkCoreでつくります。

作成したものはこちら。

https://github.com/msmsrep/ExcelToSqlite

# 準備
## 環境
- windows11
- エクセル2019（x64）
- .net8
- vscode

## .netで作成
- dotnet new classlib -n ExcelSqlite
- cd ExcelSqlite
- dotnet add package ExcelDna.Addin
- dotnet add package Microsoft.EntityFrameworkCore.Sqlite
- dotnet add package Microsoft.EntityFrameworkCore.Tools

## デバッグのために
デバッグしやすいように、[こちらを参考](https://qiita.com/msms/items/7de38ab10799af195c62#launch%E3%81%A8tasks%E3%81%AE%E5%A4%89%E6%9B%B4)にlaunchとtasksを変更しておきます。

# Excel-DNAでつくっていく
## Excel-DNAの関数
`object[,]`はvba側から2次元配列を渡す。

```DnaFunctions.cs
using ExcelDna.Integration;
using ExcelToSqlite.Models;

namespace ExcelToSqlite;
public static class DnaFunctions
{
    // データベースをマイグレーション
    [ExcelFunction]
    public static string DnaInitialize()
    {
        try
        {
            Todo.InitializeDB();
            return "true";
        }
        catch (Exception ex)
        {
            return ex.ToString();
        }
    }
    // レコードを追加
    [ExcelFunction]
    public static string DnaInsert(object[,] values)
    {
        try
        {
            Todo.InsertTasks(values);
            return "true";
        }
        catch (Exception ex)
        {
            return ex.ToString();
        }
    }
    // レコードを取得
    [ExcelFunction]
    public static object[,] DnaSelect(string name)
    {
        try
        {
            return Todo.SelectTasks(name);
        }
        catch (Exception ex)
        {
            return new object[,] { { "エラー" }, { ex.ToString() } };
        }
    }
}
```

## EntityFrameworkCoreの
`Models`フォルダをつくって以下

```Todo.cs
using System.ComponentModel.DataAnnotations;
using Microsoft.EntityFrameworkCore;
using System.Diagnostics;
using Microsoft.Extensions.Logging;
using Microsoft.EntityFrameworkCore.Diagnostics;
using ExcelDna.Integration;
using ExcelToSqlite.Utils;
using System.Text;

namespace ExcelToSqlite.Models;

public class Tasks
{
    [Key]
    public int Id { get; set; }
    public string TaskName { get; set; } = "-";
}
public class Details
{
    [Key]
    public int Id { get; set; }
    public int TasksId { get; set; }
    public string DetailName { get; set; } = "-";
}
public class Versions
{
    [Key]
    public string Version { get; set; } = "0";
    public string 内容 { get; set; } = "-";
    public string 更新日 { get; set; } = "2024/01/01";
}

public class Context : DbContext
{
    public DbSet<Tasks>? tasks { get; set; }
    public DbSet<Details>? details { get; set; }
    public DbSet<Versions>? versions { get; set; }
    private static DirectoryInfo DirectoryInfo = new(Path.GetDirectoryName(ExcelDnaUtil.XllPath)
                                                ?? new(AppDomain.CurrentDomain.BaseDirectory));
    private static string RootDBPath = "DataSource=" + Path.Combine(DirectoryInfo.FullName, @"Todo.db");
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
                    .LogTo(
                    message => Debug.WriteLine(message),
                    new[] { DbLoggerCategory.Database.Name },
                    LogLevel.Debug,
                    DbContextLoggerOptions.LocalTime)
                    .UseSqlite(RootDBPath);
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    { }
}

public class Todo
{
    public static void InitializeDB()
    {
        // データベースをマイグレーション
        using var context = new Context();
        context.Database.Migrate();
    }
    // 新たなレコードを追加
    public static void InsertTasks(object[,] taskArray)
    {
        List<Tasks> taskList = taskArray.ToGenericList(values => new Tasks
        {
            Id = Convert.ToInt32(values[0]),
            TaskName = (string)values[1]
        });
        using var context = new Context();
        context.tasks?.AddRange(taskList);
        context.SaveChanges();
    }
    // レコードを取得する
    public static object[,] SelectTasks(string name)
    {
        using var context = new Context();
        var tmp = context.tasks?.ToList();
        var list = tmp?
                    .Where(x => NormalizeString(x.TaskName).Contains(NormalizeString(name), StringComparison.OrdinalIgnoreCase))
                    .ToList();
        return list?.To2DArray() ?? throw new ArgumentNullException();
    }
    public static string NormalizeString(string input)
    {
        return input.Normalize(NormalizationForm.FormKC);
    }
}
```

## 範囲パラメータ
- [UDFs での範囲パラメータの受け入れ](https://excel-dna.net/docs/guides-basic/accepting-range-parameters-in-udfs)
- vbaの方で2次元配列をつくって渡せばobject[,]となる
- 以下の拡張メソッドを使う
  - To2DArray<T>：レコードの取得結果を2次元配列に変換す
  - ToGenericList<T>：2次元配列をリストに変換する

```Extentions.cs
using System.Reflection;

namespace ExcelToSqlite.Utils;

public static class ExtensionsLinq
{
    // リストを2次元配列に変換する
    public static object[,] To2DArray<T>(this List<T> list)
    {
        if (list.Count == 0) return new object[,] { { 0 }, { "" } };

        PropertyInfo[] properties = typeof(T).GetProperties();
        int rows = list.Count;
        int cols = properties.Length;
        var result = new object[rows, cols];
        for (int r = 0; r < rows; r++)
        {
            for (int c = 0; c < cols; c++)
            {
                result[r, c] = properties[c].GetValue(list[r]) ?? "";
            }
        }
        return result;
    }
    // 2次元配列をリストに変換する
    public static List<T> ToGenericList<T>(this object[,] array, Func<object[], T> createInstance)
    {
        var list = new List<T>();
        for (int i = 0; i < array.GetLength(0); i++)
        {
            object[] values = new object[array.GetLength(1)];
            for (int j = 0; j < array.GetLength(1); j++)
            {
                values[j] = array[i, j];
            }
            list.Add(createInstance(values));
        }
        return list;
    }
}
```

## マイグレーションする
- データベースを変更する時は、`dotnet ef migrations add InitialCreate`でマイグレーションファイルをつくっておく
- xllとして使う時のDBへのパス参照とマイグレーションする時の参照が違うので以下のようにしてく
`private static DirectoryInfo DirectoryInfo = new(Path.GetDirectoryName(ExcelDnaUtil.XllPath) ?? new(AppDomain.CurrentDomain.BaseDirectory));`


## dllファイルが見つからない
- EFcoreの準備をして実行するとSystem.DllNotFoundException: Unable to load DLL 'e_sqlite3'のようなエラーとなります
- dllファイルをコピーする必要があります
- https://groups.google.com/g/exceldna/c/4PQLjPj76N4/m/AAnIPUszDAAJ
- \bin\Debug\net8.0-windows\runtimes\win-x64\native\e_sqlite3.dll
- を以下へコピー
- \bin\Debug\net8.0-windows\publish
- win-x64かwin-x86かはエクセルのbitと合わせる
- 違うとSystem.BadImageFormatException: 間違ったフォーマットのプログラムを読み込もうとしました。のようなエラーがでます
- ExcelSqlite.csprojにコピーするように追加しておきます
- コピータスクについて
- https://learn.microsoft.com/ja-jp/visualstudio/msbuild/copy-task?view=vs-2022

```ExcelToSqlite.csproj
  <Target Name="CopyFiles" AfterTargets="build">
    <ItemGroup>
      <SourceDll Include="$(OutputPath)runtimes\win-x64\native\*.dll"/>
    </ItemGroup>
    <Copy
      SourceFiles="@(SourceDll)"
      DestinationFolder="$(OutputPath)publish"/>
  </Target>
```

# エクセルVBAから呼び出す
エクセルを開いたときに[xllを呼び出す](https://qiita.com/msms/items/7de38ab10799af195c62#xll%E3%82%92%E4%BD%BF%E7%94%A8%E3%81%99%E3%82%8B)ようにしておきます。
以下のような感じで使います。

``` vb
Option Explicit

Sub マイグレーション()

    Debug.Print Application.Run("DnaInitialize")

End Sub

Sub レコード取得()
    
    Dim tmp
    tmp = Application.Run("DnaSelect", "a")
    
End Sub

Sub レコード追加()

    Dim ary
    ReDim ary(1 To 3, 1 To 2)
    ary(1, 1) = 0
    ary(2, 1) = 0
    ary(3, 1) = 0
    ary(1, 2) = "a"
    ary(2, 2) = "b"
    ary(3, 2) = "c"
    
    Debug.Print Application.Run("DnaInsert", ary)
    
End Sub
```

# Excel-DNAの他
- https://github.com/Excel-DNA/ExcelDna
- [Excel-DNAを使って.NETでエクセルアドイン（.xll）をつくる](https://qiita.com/msms/items/7de38ab10799af195c62)
- [Excel-DNAで非同期処理の関数をつくる](https://qiita.com/msms/items/0978934282c0067e6450)