- ExcelDna.AddIn
- ExcelDna.Integration
- Npgsql
- Microsoft.Office.Interop.Excel

using System;
using System.Data;
using System.Runtime.InteropServices;
using Npgsql;
using ExcelDna.Integration;
using Excel = Microsoft.Office.Interop.Excel;

namespace PostgresExcelAddin
{
    public static class Functions
    {
        private static NpgsqlConnection _connection;
        // ...
    }
}

[ExcelFunction(Description = "PostgreSQLに接続します")]
public static string ConnectDB(string host, string database, string username, string password, string port = "5432")
{
    try
    {
        var connectionString = $"Host={host};Port={port};Database={database};Username={username};Password={password}";
        
        // 既存の接続をクリーンアップ
        if (_connection != null && _connection.State == ConnectionState.Open)
        {
            _connection.Close();
            _connection.Dispose();
        }

        // 新しい接続を確立
        _connection = new NpgsqlConnection(connectionString);
        _connection.Open();
        return "接続成功";
    }
    catch (Exception ex)
    {
        return $"接続エラー: {ex.Message}";
    }
}

[ExcelFunction(Description = "PostgreSQLから切断します")]
public static string DisconnectDB()
{
    try
    {
        if (_connection != null)
        {
            if (_connection.State == ConnectionState.Open)
            {
                _connection.Close();
            }
            _connection.Dispose();
            _connection = null;
        }
        return "切断成功";
    }
    catch (Exception ex)
    {
        return $"切断エラー: {ex.Message}";
    }
}

[ExcelFunction(Description = "SQLクエリを実行してシートに結果を書き込みます")]
public static object ExecuteQuery(string query, string sheetName)
{
    try
    {
        // 接続チェック
        if (_connection == null || _connection.State != ConnectionState.Open)
        {
            return "データベースに接続されていません";
        }

        // Excel操作のための準備
        var app = ExcelDnaUtil.Application as Excel.Application;
        Excel.Worksheet sheet;

        // シートの取得または作成
        try
        {
            sheet = app.ActiveWorkbook.Worksheets[sheetName];
        }
        catch
        {
            sheet = app.ActiveWorkbook.Worksheets.Add();
            sheet.Name = sheetName;
        }

        // クエリ実行とデータ取得
        using (var cmd = new NpgsqlCommand(query, _connection))
        using (var reader = cmd.ExecuteReader())
        {
            var dataTable = new DataTable();
            dataTable.Load(reader);

            // シートのクリア
            sheet.UsedRange.Clear();

            // ヘッダーの書き込み
            for (int i = 0; i < dataTable.Columns.Count; i++)
            {
                sheet.Cells[1, i + 1].Value = dataTable.Columns[i].ColumnName;
            }

            // データの一括書き込み
            var data = new object[dataTable.Rows.Count, dataTable.Columns.Count];
            for (int row = 0; row < dataTable.Rows.Count; row++)
            {
                for (int col = 0; col < dataTable.Columns.Count; col++)
                {
                    data[row, col] = dataTable.Rows[row][col];
                }
            }

            if (dataTable.Rows.Count > 0)
            {
                var range = sheet.Range[
                    sheet.Cells[2, 1], 
                    sheet.Cells[dataTable.Rows.Count + 1, dataTable.Columns.Count]
                ];
                range.Value = data;
            }

            // 列幅の自動調整
            sheet.Columns.AutoFit();
            return $"{dataTable.Rows.Count}行のデータを取得しました";
        }
    }
    catch (Exception ex)
    {
        return $"エラー: {ex.Message}";
    }
}

[ExcelFunction(Description = "UPDATE/INSERT/DELETEクエリを実行します")]
public static string ExecuteNonQuery(string query)
{
    try
    {
        if (_connection == null || _connection.State != ConnectionState.Open)
        {
            return "データベースに接続されていません";
        }

        using (var cmd = new NpgsqlCommand(query, _connection))
        {
            int result = cmd.ExecuteNonQuery();
            return $"{result}行が影響を受けました";
        }
    }
    catch (Exception ex)
    {
        return $"エラー: {ex.Message}";
    }
}

' データベース接続
Sub ConnectToDatabase()
    Dim result As String
    result = Application.Run("ConnectDB", "localhost", "postgres", "username", "password", "5432")
    MsgBox result
End Sub

' クエリ実行
Sub ExecuteQueryExample()
    Dim queryResult As String
    queryResult = Application.Run("ExecuteQuery", "SELECT * FROM users", "Results")
    MsgBox queryResult
End Sub

' データベース更新
Sub UpdateDatabase()
    Dim nonQueryResult As String
    nonQueryResult = Application.Run("ExecuteNonQuery", "UPDATE users SET active = true WHERE id = 1")
    MsgBox nonQueryResult
End Sub

' 切断処理
Sub DisconnectFromDatabase()
    Dim disconnectResult As String
    disconnectResult = Application.Run("DisconnectDB")
    MsgBox disconnectResult
End Sub