VBAーExcelAddOnの活用

ExcelのVBAではいろんなことをできるが、複雑な処理をやると、スピードが遅い、必要なライブが不足など、不便が出てきた。

ExcelAddOn経由で、.netFrameworkにより作ったライブを簡単に利用できるのはとっても便利でした。

ExcelよりOracle Clientをインストールせずに、OracleDBを接続して、SQLを実行させます。

C#の宣言部分
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
using ExcelDna.Integration;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;

namespace XlsWxg
{
    public class OraXE
    {
        [ExcelFunction(Category = "Database", Description = "Select By SQL, Return Data Arrays.")]
        public static string Batch([ExcelArgument(Description = "select cols from tables where ...", Name = "sql")]string batchSQL, string dataSource)
        {
            List<string> lstValue = new List<string>();
            try
            {
                using (var con = new OracleConnection(dataSource))
                {
                    con.Open();
                    DbTransaction transaction = con.BeginTransaction();

　　　　   //一括で受取ったSQLを；より切出す
                    string[] sqls = batchSQL.Split(";".ToCharArray());
                    foreach (var sql in sqls)
                    {
　　　　　　 //SELECT文の場合、
                        if (sql.Trim().StartsWith("SELECT", StringComparison.OrdinalIgnoreCase))
                        {
                            DataTable dt = new DataTable();
                            using (var adapter = new OracleDataAdapter(sql, con))
                            {
                                adapter.Fill(dt);
                            }
                            lstValue.Add(DataTabletoString(dt));
                        }
                        else if (string.IsNullOrWhiteSpace(sql)) 
                        {
                            //処理なし
                        }
　　　　　　 //INSERT/UPDATE/DELETE文の場合、
                        else
                        {
                            using (var cmd = new OracleCommand(sql, con))
                            {
                                int count = cmd.ExecuteNonQuery();
                                lstValue.Add(count.ToString());
                            }
                        }
                    }
                    transaction.Commit();
                }
            }
            catch (Exception ex)
            {
                IOWxg.Log(ex.Message);
                return "error:" + ex.Message;
            }
            return string.Join(";", lstValue);
        }
　  //EXCELにDataTableを直接受け取れないため、Stringに変更しておこう、
        private static string DataTabletoString(DataTable dt)
        {
　　　 //列名
            string header = string.Join("|", dt.Columns.OfType<DataColumn>().Select(x => x.ColumnName));
            List<string> lstTable = new List<string>();
            foreach (DataRow row in dt.Rows)
            {
                List<string> lstRow = new List<string>();
                lstRow.Clear();
                foreach (DataColumn col in dt.Columns)
                {
                    if (!row.IsNull(col))
                    {
                        lstRow.Add(row[col].ToString());
                    }
                    else
                    {
                        lstRow.Add(string.Empty);
                    }
                }
　　　　 //行データ
                lstTable.Add(string.Join("|", lstRow));
            }
            string datas = string.Join(Environment.NewLine, lstTable);
　　　 //列情報とデータ行を改行で区切り
            return header + Environment.NewLine + datas;
        }

    }

}
ソース格納のgithub:https://github.com/lqwangxg/Xlsw.git
Excel4OracleDB.xlsmのgithub:https://github.com/lqwangxg/XlsWxg