C# と VB.NET の質問掲示板

わんくま同盟

ASP.NET、C++/CLI、Java 何でもどうぞ

C# と VB.NET の入門サイト

ツリー一括表示

検索を早く /よし (24/04/15(Mon) 10:40) #103060
Re[1]: 検索を早く /WebSurfer (24/04/15(Mon) 12:43) #103061
│└ Re[2]: 検索を早く /ぶなっぷ (24/04/15(Mon) 13:31) #103062
│  └ Re[3]: 検索を早く /ぶなっぷ (24/04/15(Mon) 13:36) #103063
Re[1]: 検索を早く /魔界の仮面弁士 (24/04/15(Mon) 14:40) #103064
  └ Re[2]: 検索を早く /furu (24/04/16(Tue) 11:28) #103065
    └ Re[3]: 検索を早く /よし (24/04/19(Fri) 17:11) #103084 解決済み


親記事 / ▼[ 103061 ] ▼[ 103064 ]
■103060 / 親階層)  検索を早く
□投稿者/ よし (1回)-(2024/04/15(Mon) 10:40:55)

分類:[C#] 

Windows フォームアプリケーション.NetFramework c#

宜しくお願い致します

現在フォルダの中にエクセルファイルが10個入っています
そのエクセルファイルの中に1行に26列までデータが入っていまして1000行まであります


txtModelNoとtxtModelNo2に入れた文字が合致したものをデータグリッドビューに表示させています

txtModelNoはエクセルファイルのA列、txtModelNo2はB列を見に行っています

合致したものは正しく表示されるのですが
凄く遅く感じます

y列に何か入っている場合は次の行に飛びまして
空白が8行続いたら次のエクセルファイルに移るようにしています

速く検索できる方法はあるでしょうか
宜しくお願い致します


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;

namespace チェックPGM
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();

            this.WindowState = FormWindowState.Maximized;
            dataGridView1.DefaultCellStyle.Font = new Font("Arial", 11);
            dataGridView1.ColumnHeadersHeight = 600;

            // 列の追加
            string[] columnHeaders = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA" };
            foreach (var columnHeader in columnHeaders)
            {
                dataGridView1.Columns.Add("", columnHeader);
            }
        }

        private void buttonSearch_Click_1(object sender, EventArgs e)
        {
            string searchValue1 = txtModelNo.Text;
            string searchValue2 = txtModelNo2.Text;
            bool ignoreSuccess = checkBoxIgnoreSuccess.Checked;

          SearchExcelFiles(searchValue1, searchValue2, @"C:\Users\AZ\Documents\az-test", ignoreSuccess);
        }

        private void SearchExcelFiles(string searchValue1, string searchValue2, string folderPath, bool ignoreSuccess)
        {
            if (!Directory.Exists(folderPath))
            {
                MessageBox.Show("指定されたフォルダーは存在しません。");
                return;
            }

            Excel.Application excelApp = new Excel.Application();
            Excel.Workbook excelWorkbook = null;

            bool matchFound = false;
            try
            {
                DirectoryInfo directoryInfo = new DirectoryInfo(folderPath);
                foreach (FileInfo fileInfo in directoryInfo.GetFiles("*.xlsx"))
                {
                    excelWorkbook = excelApp.Workbooks.Open(fileInfo.FullName);
                    Excel.Worksheet excelWorksheet = excelWorkbook.Sheets[1];

                    textBox3.Text = fileInfo.Name;

                    Excel.Range usedRange = excelWorksheet.UsedRange;
                    int rowCount = usedRange.Rows.Count;

                    int emptyCellCount = 0;
                    for (int i = 1; i <= rowCount; i++)
                    {
                        textBox4.Text = i.ToString();

                        if (usedRange.Cells[i, "Y"].Value2 != null && !string.IsNullOrEmpty(usedRange.Cells[i, "Y"].Value2.ToString()))
                        {
                            continue;
                        }

                        if (usedRange.Cells[i, 1].Value2 == null || string.IsNullOrEmpty(usedRange.Cells[i, 1].Value2.ToString()))
                        {
                            emptyCellCount++;
                        }
                        else
                        {
                            emptyCellCount = 0;
                        }

                        if (emptyCellCount >= 8) //空白セルが8回続けば次のファイルへ
                        {
                            break;
                        }

                        bool matchesSearch1 = usedRange.Cells[i, 1].Value2 != null && usedRange.Cells[i, 1].Value2.ToString() == searchValue1;
                        bool matchesSearch2 = usedRange.Cells[i, 2].Value2 != null && usedRange.Cells[i, 2].Value2.ToString() == searchValue2;

                        if (matchesSearch1 && matchesSearch2)
                        {
                            matchFound = true;
                            List<object> rowData = new List<object>();
                            for (int j = 1; j <= 26; j++)
                            {
                                rowData.Add(usedRange.Cells[i, j].Value2);
                            }
                            rowData.Add(fileInfo.FullName);
                            rowData.Add(i);
                            dataGridView1.Invoke((MethodInvoker)delegate
                            {
                                dataGridView1.Rows.Add(rowData.ToArray());
                            });

                            break; //検索終
                        }

                    }

                    Marshal.ReleaseComObject(usedRange);
                    Marshal.ReleaseComObject(excelWorksheet);
                    excelWorkbook.Close(false);
                    Marshal.ReleaseComObject(excelWorkbook);

                    if (matchFound)
                    {
                        break; // 検索を終了する
                    }

                }
                if (!matchFound)
                {
                    MessageBox.Show("検索結果が見つかりませんでした。");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("エラーが発生しました: " + ex.Message);
            }
            finally
            {
                excelApp.Quit();
                Marshal.ReleaseComObject(excelApp);
            }
        }
    }
}

[ □ Tree ] 返信 編集キー/

▲[ 103060 ] / ▼[ 103062 ]
■103061 / 1階層)  Re[1]: 検索を早く
□投稿者/ WebSurfer (2895回)-(2024/04/15(Mon) 12:43:46)
No103060 (よし さん) に返信

どこがボトルネックなのか調査していただけませんか?

それが今すぐにでもできる環境を持っているのは質問者さんだけなのです。
[ 親 103060 / □ Tree ] 返信 編集キー/

▲[ 103061 ] / ▼[ 103063 ]
■103062 / 2階層)  Re[2]: 検索を早く
□投稿者/ ぶなっぷ (3回)-(2024/04/15(Mon) 13:31:08)
特に重い処理をやっているようには見えません。

焼け石に水でしょうが、わずかには速くなりそうなものとしては、
> bool matchesSearch1 = usedRange.Cells[i, 1].Value2 != null && usedRange.Cells[i, 1].Value2.ToString() == searchValue1;
などと、同じセル usedRange.Cells[i, 1] を2度参照しているものを1度にしてみるとかでしょうか。

こんな感じですね。
> bool matchesSearch1 = usedRange.Cells[i, 1].Value2?.ToString() == searchValue1;

[ 親 103060 / □ Tree ] 返信 編集キー/

▲[ 103062 ] / 返信無し
■103063 / 3階層)  Re[3]: 検索を早く
□投稿者/ ぶなっぷ (4回)-(2024/04/15(Mon) 13:36:54)
2024/04/15(Mon) 13:37:16 編集(投稿者)

すみません。

ToString()があるから、nullチェック必須ですね。
> var value2 = usedRange.Cells[i, 1].Value2;
> bool matchesSearch1 = value2 != null && value2.ToString() == searchValue1;
[ 親 103060 / □ Tree ] 返信 編集キー/

▲[ 103060 ] / ▼[ 103065 ]
■103064 / 1階層)  Re[1]: 検索を早く
□投稿者/ 魔界の仮面弁士 (3761回)-(2024/04/15(Mon) 14:40:04)
2024/04/15(Mon) 15:18:10 編集(投稿者)

No103060 (よし さん) に返信
> using Excel = Microsoft.Office.Interop.Excel;
現在の方法は、実行環境に Excel をインストールしておき、
それをプログラムから操作する方法ですよね。

高速参照が目的ならば、Excel を COM オートメーションで操作するのではなく、
xlsx ファイル自体を直接読み込めるタイプのライブラリを利用することをお奨めします。
たとえば、速度に定評があるところで ExcelDataReader とか。

https://techblog.gracetory.co.jp/entry/2020/11/24/110000
https://docs.sakai-sc.co.jp/article/programing/closedxml-vs-epplus.html
https://ja.stackoverflow.com/questions/72835/%E5%A4%A7%E5%AE%B9%E9%87%8F%E3%82%B7%E3%83%BC%E3%83%88%E3%82%92%E5%90%AB%E3%82%80excel%E3%83%95%E3%82%A1%E3%82%A4%E3%83%AB%E3%82%92%E9%AB%98%E9%80%9F%E3%81%A7%E9%96%8B%E3%81%8F%E6%96%B9%E6%B3%95


使用するライブラリを今更変えられないのであれば、コードを見直してみましょう。


Excel VBA が ThisWorkbook を操作する場合、自分自身の操作(インプロセス)なので比較的高速に処理できるのですが、
Excel を C# などから操作するような場合は、アウトプロセス COM サーバーとの通信となるため、
メソッドやプロパティの読み書きのパフォーマンスが、VBA の時よりも幾分遅くなります。

※たとえ Excel VBA であっても、自身以外の Application インスタンスへの操作はパフォーマンスが劣化します。


とはいえ、どの操作が特に遅くなりえるのかは、実際の環境でないと分からないため、
ボトルネックの調査はご自身で行ってみてください。
VBA で処理しても遅い操作は、C# ならもっと遅くなるわけですし。

またボトルネック調査の際には、その処理が何回繰り返されるのかも重要です。

たとえば仮に、ある操作の呼び出しが、VBA の実装だと 0.0003 秒で終わる処理があったとして、
同じ処理を C# から呼び出すと、0.01 秒かかってしまうとしましょう。この処理を実行するための
アクセス回数が 10 回なら、 0.003 秒 VS 0.1 秒なので、体感的にはどちらも一瞬で終わりますが、
アクセス回数が 1000 回なら、0.3 秒 VS 10.0 秒なので、体感する速度差に大きく差が付きます。



> if (usedRange.Cells[i, "Y"].Value2 != null && !string.IsNullOrEmpty(usedRange.Cells[i, "Y"].Value2.ToString()))

たとえばこの一行だけで、最大 8 回も Excel との通信が行われる可能性があります。

 1. Range オブジェクト (usedRange:変数)
 2. Range オブジェクト (.Cells: 引数無し property の getter)
 3. Range オブジェクト ([i, "Y"]: 引数あり property の getter/インデクサ)
 4. Variant(Object) (.Value2 引数無し property の getter)
 5. Range オブジェクト (usedRange 変数)
 6. Range オブジェクト (.Cells: 引数無し property の getter)
 7. Range オブジェクト ([i, "Y"]: 引数あり property の getter/インデクサ)
 8. Variant(Object) (.Value2 引数無し property の getter)


「Excel を外部から操作する場合は、繰り返し回数を極力減らすべき」というのが、
.NET が登場する前から言われてきたノウハウだったりします。

下記は、Excel 97〜2000 (VB5〜VB6 頃) の古い資料ですが、参考までに。
(既に公開が終了しているので、Internet Archive から拾いだしています)

KB414107: [XL2000] オートメーションでセルの値の取得やコピーを繰り返すと応答しない
https://web.archive.org/web/20140126120748/http%3A%2F%2Fsupport.microsoft.com/kb/414107/ja


ということで、Excel を外部操作する場合、Excel との通信回数を減らすことで高速化できる可能性があります。

たとえば、セルを一つ一つ読み取るのではなく、Excel の Find / FindNext メソッドを併用して捜索したり、
あるいは複数の範囲を一括で読み取って配列として読み書きするといった手もあります。


たとえば
> Excel.Range usedRange = excelWorksheet.UsedRange;
の後で、
 int rowCount = usedRange.Rows.Count;
 if (usedRange.Cells[i, "Y"].Value2 != null && !string.IsNullOrEmpty(usedRange.Cells[i, "Y"].Value2.ToString()))
のように処理するのではなく、複数セル範囲のまま
 rangeValues = usedRange.Value;
のように丸ごと受け取って、それを (Excel を介さず C# 側だけで)配列データとして扱ってみます。

UsedRange.Value の値は、通常は object[,] な二次元配列を返しますが、
使用しているセルが一つ以下だと、戻り値が配列では無く、null や string や double になりえるため、
この場合の rangeValues 変数は、一旦 dynamic 型 または object 型で受けておく方が良いでしょう。


 // ここは Excel との通信が発生する
 dynamic rangeValues = usedRange.Value; // セル範囲内の値を一括して受け取る

 // ここから下は C# 側だけで完結しているため、Excel との通信はおこなわれない
 var array = rangeValues as object[,];
 if ( array != null ) { // 配列として受け取れた場合
  // 受けとった配列のインデックスが 0 始まりになるか 1 始まりになるかは、
  // 環境によって変わることがあるので、念のために GetLowerBound を使った方が良い
  int rowLower = array.GetLowerBound(0);
  int rowUpper = array.GetUpperBound(0);
  int colLower = array.GetLowerBound(1);
  int colUpper = array.GetUpperBound(1);

  for (int rowIndex = rowLower ; rowIndex <= rowUpper; rowIndex++ ) {
   for (int colIndex = colLower ; colIndex <= colUpper; colIndex++ ) {
    object data = array[rowIndex, colIndex];
    Debug.WriteLine("[{0},{1}]={2}", rowIndex, colIndex, data);
   }
  }
 }


なお、セル範囲が余りに巨大な場合、メモリの確保(と解放)のコストが高くなるため、
その場合は、複数の配列範囲にブロック分割して処理するといった手もあります。


> excelWorkbook = excelApp.Workbooks.Open(fileInfo.FullName);
> Excel.Worksheet excelWorksheet = excelWorkbook.Sheets[1];
そういった操作は、COM オブジェクトの解放漏れを引き起こします。

excelApp.Workbooks が返す Excel.Workbook オブジェクトや、
excelWorkbook.Sheets が返す Excel.Sheets オブジェクトといった
ひとつひとつの COM オブジェクトを変数に受け取るように書き換えるべきかと。

https://support.microsoft.com/ja-jp/topic/96068fdb-7a84-ecf0-3b91-282fae81a618
https://hanatyan.sakura.ne.jp/dotnet/Excel08.htm
https://hanatyan.sakura.ne.jp/dotnet/Excel09.htm
[ 親 103060 / □ Tree ] 返信 編集キー/

▲[ 103064 ] / ▼[ 103084 ]
■103065 / 2階層)  Re[2]: 検索を早く
□投稿者/ furu (226回)-(2024/04/16(Tue) 11:28:25)
2024/04/16(Tue) 11:29:30 編集(投稿者)
COM オートメーション使用でなら
Excelとの通信回数を少なくすることです。

案1
  セルの値を配列で取得する。(魔界の仮面弁士さんも書かれています)

    var cells = (object[,])excelWorksheet.UsedRange.Value2;
    for (int i = 1; i <= rowCount; i++)
    {
        //y列に何か入っている場合は次の行に飛びまして
        if (Convert.ToString(cells[i, 25]) != "") continue;


案2
  テキストファイルに変換し、テキストファイルで作業する。

  var filepath = System.IO.Path.GetTempPath() + System.IO.Path.GetRandomFileName();
    excelWorkbook.SaveAs(filepath, 42, …); //Unicode テキスト
    var lines = File.ReadAllLines(filepath);
    File.Delete(filepath);

[ 親 103060 / □ Tree ] 返信 編集キー/

▲[ 103065 ] / 返信無し
■103084 / 3階層)  Re[3]: 検索を早く
□投稿者/ よし (2回)-(2024/04/19(Fri) 17:11:55)
皆さんありがとうございます
色々試させて頂いた結果早くなりました
ありがとうございました
解決済み
[ 親 103060 / □ Tree ] 返信 編集キー/


管理者用

- Child Tree -