エクセルからSQLiteを使う時に、SQLiteForExcelというのを使用していましたが、Excel-DNAでxllを作ってみました。
データベースはマイグレーションしたいのでEntityFrameworkCoreでつくります。
作成したものはこちら。
準備
環境
- 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
デバッグのために
デバッグしやすいように、こちらを参考に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 での範囲パラメータの受け入れ
- vbaの方で2次元配列をつくって渡せばobject[,]となる
- 以下の拡張メソッドを使う
- To2DArray：レコードの取得結果を2次元配列に変換す
- ToGenericList：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を呼び出すようにしておきます。
以下のような感じで使います。
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