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

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

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

Re[10]: excelのセルからテキストと文字色を取得

(過去ログ 137 を表示中)

[トピック内 18 記事 (1 - 18 表示)]　 << 0 >>

■80517 / inTopicNo.1)

　excelのセルからテキストと文字色を取得

▼■

□投稿者/ さい (1回)-(2016/07/25(Mon) 15:52:07)

分類:[VB.NET/VB2005 以降]　

環境：vb.net visual studio2015


お世話になります

ExcelのセルからValue/文字色/背景色を取得しDataGridViewのセルに設定する処理で


For a = 0 To X
    For c = 0 To 30

           '背景色
           DGV.Rows(b).Cells(c+1).Style.BackColor =
           ColorTranslator.FromWin32(xlSheet.Cells(a, c).Interior.Color)

           '文字色
           DGV.Rows(b).Cells(c+1).Style.ForeColor =
           ColorTranslator.FromWin32(xlSheet.Cells(a, c).Font.Color)

           'Value
           DGV.Rows(b).Cells(c+1).Value = xlSheet.Cells(a, c).value
     Next
     b += 1
Next


上記方法で設定を行おうとしたのですが、設定するセルがかなり多いため処理に相当時間がかかってしまいます。

どうやらColorTranslater.FromWin32()で時間がかかっているようなのですが、処理速度を上げる方法をご存じの方はいらっしゃいませんでしょうか

引用返信 編集キー/

■80520 / inTopicNo.2)

　Re[1]: excelのセルからテキストと文字色を取得

▲▼■

□投稿者/ furu (63回)-(2016/07/25(Mon) 16:35:24)

■No80517 (さいさん) に返信
こうゆうので遅い場合、とりあえずDictionaryでやってます

引用返信 編集キー/

■80521 / inTopicNo.3)

　Re[1]: excelのセルからテキストと文字色を取得

▲▼■

□投稿者/ 魔界の仮面弁士 (783回)-(2016/07/25(Mon) 17:18:01)

■No80517 (さいさん) に返信
> 上記方法で設定を行おうとしたのですが、
COM 解放の面からいえば、問題のありそうなコードですが、
それはとりあえず追いといて：

> 設定するセルがかなり多いため処理に相当時間がかかってしまいます。
Excel から色情報を取得するのに要する時間と
DataGridView にスタイルを設定する時間を
それぞれ計測してみてください。

ボトルネックはどちらですか?
また、対象となるセル数はどの程度でしょうか?

> どうやらColorTranslater.FromWin32()で時間がかかっているようなのですが、
本来使うべきは FromWin32 ではなく FromOle ですね。
ついでに言えば、質問文内のクラス名をスペルミスしているようです。

さて、時間がかかっているのは、ColorTranslator を利用する処理ではなく、
そこに渡すべき前景色/背景色を取得する処理だったりはしませんか?

下記のコードで実行してみましたが、463 色を変換するのに要する時間は、
せいぜい 3ミリ秒～20ミリ秒程度でした。

Dim 既知色の名前 = System.Enum.GetNames(GetType(KnownColor))
Dim システム色名 = GetType(SystemColors).GetProperties().Select(Function(p) p.Name)

Dim 変換元の色 = 既知色の名前.Concat(システム色名).Select(AddressOf Color.FromName).ToList()
For i = 0 To 255
　　変換元の色.Add(Color.FromArgb(i, i, i))
Next

MsgBox("変換する色数 = " & CStr(変換元の色.Count))

Dim sw = Stopwatch.StartNew()
Dim colFromWin32 As New List(Of Color)()
Dim colToOle As New List(Of Integer)()
Dim colFromOle As New List(Of Color)()
For Each c In 変換元の色
　　Dim ole = ColorTranslator.ToOle(c)
　　colFromWin32.Add(ColorTranslator.FromWin32(c.ToArgb()))
　　colToOle.Add(ole)
　　colFromOle.Add(ColorTranslator.FromOle(ole))
Next
sw.Stop()

MsgBox($"{変換元の色.Count}色 => {sw.Elapsed}")

引用返信 編集キー/

■80524 / inTopicNo.4)

　Re[1]: excelのセルからテキストと文字色を取得

▲▼■

□投稿者/ PANG2 (125回)-(2016/07/25(Mon) 17:32:41)

■No80517 (さいさん) に返信
> どうやらColorTranslater.FromWin32()で時間がかかっているようなのですが、

と思う根拠は？
Int32の引数を渡してColor型を返すだけの関数ですよね。

重いのはExcelオブジェクトを触っているところでしょうから、触る回数を減らす。
例えば、xlSheet.Cells(a, c)を三回呼んでいるところを、一回呼んで変数に格納する。

引用返信 編集キー/

■80525 / inTopicNo.5)

　Re[1]: excelのセルからテキストと文字色を取得

▲▼■

□投稿者/ 真田昌幸 (3回)-(2016/07/25(Mon) 17:41:23)

■No80517 (さいさん) に返信
> 環境：vb.net visual studio2015
>
>
> お世話になります
>
> ExcelのセルからValue/文字色/背景色を取得しDataGridViewのセルに設定する処理で
>
>
> For a = 0 To X
> For c = 0 To 30
>
> '背景色
> DGV.Rows(b).Cells(c+1).Style.BackColor =
> ColorTranslator.FromWin32(xlSheet.Cells(a, c).Interior.Color)
>
> '文字色
> DGV.Rows(b).Cells(c+1).Style.ForeColor =
> ColorTranslator.FromWin32(xlSheet.Cells(a, c).Font.Color)
>
> 'Value
> DGV.Rows(b).Cells(c+1).Value = xlSheet.Cells(a, c).value
> Next
> b += 1
> Next
>
>
> 上記方法で設定を行おうとしたのですが、設定するセルがかなり多いため処理に相当時間がかかってしまいます。

どの程度でしょう？
想定されるMaxの行数によってとるべき対処が変わる気がします。

>
> どうやらColorTranslater.FromWin32()で時間がかかっているようなのですが、処理速度を上げる方法をご存じの方はいらっしゃいませんでしょうか

相対的には、色の設定のほうが重いでしょうが、
高速にするのが簡単なのは値の設定の方かと思います。

一般論として、Excelの情報を1セルずつ参照するのは激烈に遅いです。
色の設定の箇所がボトルネックなのが確実なのであるか実測の平均値をとって調べる必要がまずありそうなのと、
本当に1セルずつ見る必要性があるかを見直しすべきかと。
行ごとで判定できるのであれば、
そのようにすれば、参照、設定の回数が減るので1セルずつよりは当然早くなります。

引用返信 編集キー/

■80527 / inTopicNo.6)

　Re[1]: excelのセルからテキストと文字色を取得

▲▼■

□投稿者/ 魔界の仮面弁士 (786回)-(2016/07/25(Mon) 20:32:56)

■No80517 (さいさん) に返信
> 設定するセルがかなり多いため処理に相当時間がかかってしまいます。

同じコードを、VBA で処理させてみました。

その場合、VBA コードと同じ Excel 上で開かれたブックであれば、
2000行×20列であってもわずか 0.36 秒で取得できました。

しかし VBA といえども、自身以外の Excel.Application インスタンスに
対する操作の場合、20行×20列で 0.60～0.61秒、
200行×20列で 5.30～5.43秒という速度しか得られません。

VB.NET から操作する場合も、当然ながら別プロセスへの
操作となりますから、同様の低速化は避けられません。
しかも COM Interop のオーバーへッドが加わるので、さらに遅くなるでしょう。

たとえば下記の場合、100行×20列で 4.2 秒かかります。
（1 行当たりの所要時間は 30ミリ秒～50ミリ秒でした）

Dim cells = ws.Cells

For r = 1 To MaxRow
　For c = 1 To 20
　　Dim cell = DirectCast(cells(r, 1), Excel.Range)
　　　Dim itr = cell.Interior
　　　Dim o As Object = itr.Color
　　　Dim p As Color = ColorTranslator.FromOle(CInt(o))

　　　Marshal.ReleaseComObject(itr)
　　　Marshal.ReleaseComObject(cell)
　Next
Next
Marshal.ReleaseComObject(cells)

ColorTranslator クラスや ReleaseComObject メソッドの
所要時間は微々たるもので、やはり別プロセスに対する
COM 操作の部分がボトルネックになっているようです。

Cells(行, 列) の代わりに、Range(文字列) や、
newRange = range.Offset(行数) を使っても大差無し。

> 処理速度を上げる方法をご存じの方はいらっしゃいませんでしょうか

値(Range.Value)や書式(Range.NumberFormat)であれば、
二次元配列を使って一括操作できるのですが、
Interior や Font が相手だと、そういうわけにもいきません。

上記の結果から、セル数が多い場合は、列挙処理を
インプロセスの VBA に担当させることで高速化するものと予想します。

VBA 側で列挙された結果は、一時ファイルなどを通じて受け取るようにするか、
あるいは Range.Value 経由で 2 次元配列として受け取るなどすれば良いかと。

そのために必要な VBA コードは、あらかじめマクロブックとして用意しておく方法と、
VB.NET 側から動的に作成する方法が考えられます。
もしも動的に作成する場合は、下記を参考にしてみてください。

https://support.microsoft.com/ja-jp/kb/219905
https://support.microsoft.com/ja-jp/kb/303871
http://nasunoblog.blogspot.jp/2013/10/excel-vbavbprojectvbe.html

引用返信 編集キー/

■80530 / inTopicNo.7)

　Re[2]: excelのセルからテキストと文字色を取得

▲▼■

□投稿者/ さい (2回)-(2016/07/26(Tue) 12:19:37)

furu様魔界の仮面弁士様 PANG2様真田昌幸様

ご返信いただきありがとうございました。

失礼ですが、まとめて返答させて頂きます。

・セル数ですが31セル×20行程度を予測していますが、もっと行数は増える可能性があります。

・現在のソースで31セル×6行で約1分かかっています。
(提示したソースに抜けがあり、Value/文字色/背景色設定前にValueがExcelセルに設定されているかの判定がありました)

・背景色/文字色設定を無効にした場合、劇的にスピードアップしましたので遅い原因はやはり色のようです
(Excelからの色取得/DatagridViewへの色設定どちらが重いのかはこれから検証してみます)

ここまで処理が遅いと何か根本的な問題がソースにあるような気がしますので、頂いたご回答を参考にソースの見直しを行いたいと思います。

引用返信 編集キー/

■80531 / inTopicNo.8)

　Re[3]: excelのセルからテキストと文字色を取得

▲▼■

□投稿者/ 真田昌幸 (4回)-(2016/07/26(Tue) 13:30:32)

> ・現在のソースで31セル×6行で約1分かかっています。
> (提示したソースに抜けがあり、Value/文字色/背景色設定前にValueがExcelセルに設定されているかの判定がありました)

6行で1分はちょっと考えづらいですね。
参照しているExcel(ひな形？)のファイルサイズが無駄に大きいとかありませんか？
まず2007以降でxls形式だと互換情報が入るため、それだけでファイルサイズが大きくなりますし、
シートの下の方にemptyでなくvbNullstringのセルが残っているとか、
行削除の際に気づかずに残ったオブジェクトがあるとか、
ファイルサイズ無駄に大きくする要因は結構考えられます。

引用返信 編集キー/

■80532 / inTopicNo.9)

　Re[4]: excelのセルからテキストと文字色を取得

▲▼■

□投稿者/ さい (4回)-(2016/07/26(Tue) 15:28:36)

■No80531 (真田昌幸 さん) に返信

ご返信ありがとうございます

Excelは2007のxlsx形式になります。

計測(System.Diagnostics.Stopwatch)の結果
どうやら時間を食っているのは背景色取得のxlSheet.Cells(a, c).Interior.Colorで、1セル0.5秒かかっていました。

文字色のほうはDataGridViewへの設定込みでも0.001秒でした。

行/列共に値の設定判定を行い、値が入っているセルのみを処理対象にしていましたのでExcelのファイルサイズはあまり気にしていませんでした。

ステップ実行で確認しましたが関係ない行やセルは見ていないようです。

引用返信 編集キー/

■80533 / inTopicNo.10)

　Re[5]: excelのセルからテキストと文字色を取得

▲▼■

□投稿者/ 真田昌幸 (5回)-(2016/07/26(Tue) 15:48:13)

> Excelは2007のxlsx形式になります。

ということは、office2003以前との互換性考慮不要ということですね？
なら、xlsb形式(バイナリ形式)を試してみてもいいかも。
内容によってはファイルサイズが1ケタ小さくなることもあります。
そうなれば、おなじ重い処理でもだいぶパフォーマンスが違うと思います。

引用返信 編集キー/

■80534 / inTopicNo.11)

　Re[6]: excelのセルからテキストと文字色を取得

▲▼■

□投稿者/ さい (5回)-(2016/07/26(Tue) 16:02:29)

■No80533 (真田昌幸さん) に返信

早速のご返信ありがとうございます。

xlsb形式で早速試してみます。

引用返信 編集キー/

■80537 / inTopicNo.12)

　Re[5]: excelのセルからテキストと文字色を取得

▲▼■

□投稿者/ 魔界の仮面弁士 (787回)-(2016/07/26(Tue) 18:16:32)

■No80532 (さいさん) に返信
> どうやら時間を食っているのは背景色取得のxlSheet.Cells(a, c).Interior.Colorで、1セル0.5秒かかっていました。

0.5 秒は異常ですね。

ループ内で、それ以外に余計な処理が含まれていたりはしませんか?
http://answers.microsoft.com/ja-jp/office/forum/office_2013_release-excel/excel2013/150c087b-80c6-4e5a-99a4-de6f89614583?auth=1

また、バージョン固有の問題という可能性もありますので、可能であれば、
Office のアップデートやバージョンアップを試してみたり、
別バージョンの環境でテストするなどしてみてください。

もう一つ。No80524 でも触れられていますが、元コードにある
　xlSheet.Cells(a, c).Interior.Color
という一行においては、
　　xlSheet は、Excel.Worksheet (あるいは Excel.Sheet)型のオブジェクト
　　→ その Cells プロパティから、シート全体を示す Excel.Range オブジェクトを取得
　　→ その既定プロパティから、(a, c) を示す Excel.Range オブジェクトを取得
　　→ その Interior プロパティから、セル自身の色情報を示す Excel.Interior オブジェクトを取得
　　→ その Color プロパティから、OLEカラー値を示す Int32 値を System.Object 型で取得
のようにして、複数の『COM オブジェクト』への参照が発生することになります。

※Cells プロパティ自身は引数を取らないことに注意。

今回の場合、同じセルに対して背景色や前景色などといった
複数のプロパティにアクセスするわけですよね。

であれば No80527 のように、COM オブジェクトごとに変数に格納し、
同じオブジェクトへの参照をキャッシュしてみてください。
これにより、ある程度パフォーマンスを改善できるかもしれませんし、
特にどのプロパティへのアクセスに時間がかかっているのかも
切り分けられると思います。

使い終わった COM オブジェクトを Marshal.ReleaseComObject で
解放することも忘れずに。

引用返信 編集キー/

■80551 / inTopicNo.13)

　Re[6]: excelのセルからテキストと文字色を取得

▲▼■

□投稿者/ 真田昌幸 (6回)-(2016/07/27(Wed) 09:11:41)

■No80537 (魔界の仮面弁士さん) に返信
> ■No80532 (さいさん) に返信
>>どうやら時間を食っているのは背景色取得のxlSheet.Cells(a, c).Interior.Colorで、1セル0.5秒かかっていました。
>
> 0.5 秒は異常ですね。
>
> ループ内で、それ以外に余計な処理が含まれていたりはしませんか?
> http://answers.microsoft.com/ja-jp/office/forum/office_2013_release-excel/excel2013/150c087b-80c6-4e5a-99a4-de6f89614583?auth=1
>
>
> また、バージョン固有の問題という可能性もありますので、可能であれば、
> Office のアップデートやバージョンアップを試してみたり、
> 別バージョンの環境でテストするなどしてみてください。
>

そうですね。2007ならその可能性はありますね。
Excelの使用可能メモリが2007まで固定で2010からメモリ増設したらあるだけ使えるように(たしか)仕様変更されたので、
それも効果あるかも知れないです。

引用返信 編集キー/

■80552 / inTopicNo.14)

　Re[7]: excelのセルからテキストと文字色を取得

▲▼■

□投稿者/ さい (6回)-(2016/07/27(Wed) 09:46:26)

■No80551 (真田昌幸さん) に返信
■No80537 (魔界の仮面弁士さん) に返信

ご返信ありがとうございました

やはり1セル0.5秒は異常ですよね…

諸般の事情でxlsb形式使用はボツになりました。

Officeの他バージョン使用については上位バージョンOfficeが手元にない(財布が薄い(泣)のでちょっと考えてみます。

それと、VBAは全く触ったことがないのでハードルが…

COM オブジェクトごとに変数格納/ループの無駄の見直し等も含めもう一度考えてみます。

ありがとうございました。

引用返信 編集キー/

■80553 / inTopicNo.15)

　Re[7]: excelのセルからテキストと文字色を取得

▲▼■

□投稿者/ 真田昌幸 (7回)-(2016/07/27(Wed) 09:53:59)

> Excelの使用可能メモリが2007まで固定で2010からメモリ増設したらあるだけ使えるように(たしか)仕様変更されたので、

使用可能メモリ、32bit版は2007と変わらんらしいです。
なので、環境が32bitOSだとバージョンアップはそこまで効果ないかもしれませんが、
2007から、1シートの最大行数の増加、規定ファイル形式がxml形式にするなど、
メモリをたくさん使うようなものをこれでもかというほど増やした割に、
使用可能メモリが1G→2Gと2倍にしか増えていないため、
2003→2007は実質的にパフォーマンスの低下を招いたと体感することが多かったです。
(実際いまだにoffice2003を使っている企業ユーザーもいます)

MSも2010のバージョンアップのアピールポイントのパフォーマンス改善を結構大々的にうたっていたので、
32bit版でも多少はメモリの使い方などに改善はあるかもしれません。
ただし、根拠のない想像です。
環境改善の提案とかが可能なら、OS、officeとも64bitにするでしょうね。
将来的な運用改善にはつながります。

引用返信 編集キー/

■80622 / inTopicNo.16)

　Re[8]: excelのセルからテキストと文字色を取得

▲▼■

□投稿者/ さい (7回)-(2016/07/28(Thu) 17:20:05)

ご返信頂いた皆様方

別件作業が割り込みで入ってきてしまい、本件を一時中断せざるをえなくなってしまいました。

解決はしていないのですが、解決のための時間が取れるかどうか解らないため解決という形にさせて頂こうと思います。

別件が片付き次第、再チャレンジします。

本当にありがとうございました。

解決済み

引用返信 編集キー/

■80650 / inTopicNo.17)

　Re[9]: excelのセルからテキストと文字色を取得

▲▼■

□投稿者/ さい (8回)-(2016/07/29(Fri) 12:09:26)

2016/07/29(Fri) 12:11:10 編集(投稿者)
2016/07/29(Fri) 12:10:52 編集(投稿者)

ご返答頂いた皆様方

処理速度が遅かった原因が判明しましたので報告させて頂きます。

原因は画面表示に時間がかかりそうだったため空画面だけでも先に表示しておこうと「form_shown」イベントをメインに使用していたのですが、どうもこれが原因だったようです。

理由は不明のままなのですが、「form_shown」イベントを不使用としてform_loadイベントでExcelからDataGridViewへの設定を行うよう修正してみたところ31セル×22行を6秒で表示に成功しました。

まぁこれでも遅いっちゃあ遅いのですが、各セルにツールチップの設定も行っての結果ですのでこの辺で勘弁して貰おうかなと…

ご協力ありがとうございました。

解決済み

引用返信 編集キー/

■80651 / inTopicNo.18)

　Re[10]: excelのセルからテキストと文字色を取得

▲▼■

□投稿者/ 真田昌幸 (10回)-(2016/07/29(Fri) 13:05:48)

■No80650 (さいさん) に返信
> 2016/07/29(Fri) 12:11:10 編集(投稿者)
> 2016/07/29(Fri) 12:10:52 編集(投稿者)
>
> ご返答頂いた皆様方
>
>
> 処理速度が遅かった原因が判明しましたので報告させて頂きます。
>
> 原因は画面表示に時間がかかりそうだったため空画面だけでも先に表示しておこうと「form_shown」イベントをメインに使用していたのですが、どうもこれが原因だったようです。
>
> 理由は不明のままなのですが、「form_shown」イベントを不使用としてform_loadイベントでExcelからDataGridViewへの設定を行うよう修正してみたところ31セル×22行を6秒で表示に成功しました。
>

shownイベントは文字通り表示時なので、画面の描画とほぼ同時なのでそりゃ重くなるでしょうね。
Excelだけでなく、DBの接続とかでもここでやると重くなります。

Windowsの処理で一番重いのは描画関係がほとんどなので、
それが複数重なるとか、そこにオブジェクトへの参照とかを同時にやるのは避けたほうがよいでしょう。
VB6時代にも、activateイベントで初期処理をやらせすぎで重たいアプリがほぼありました。

描画が重たい原因なのだとしたら、
描画を後回しにするとか、処理中非表示にするとか、グリッド側の処理改善が効果あるかもしれません。

引用返信 編集キー/

トピック内ページ移動 / << 0 >>

このトピックに書きこむ

過去ログには書き込み不可

- Child Tree -