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

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

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

Re[6]: SQLの結果をExcelに書き込みたいのですが。


(過去ログ 64 を表示中)

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

■36837 / inTopicNo.1)  SQLの結果をExcelに書き込みたいのですが。
  
□投稿者/ jin (4回)-(2009/06/06(Sat) 23:10:57)

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

2009/06/06(Sat) 23:11:16 編集(投稿者)
お世話になります。
現在VB2008にて、Olacleに接続し、SQLのSelectを行おうとしています。
以下がそのSQL文です。
'*************************************
Dim dt As DataTable 
Dim ds As DataSet
Dim sql As String = ""
Dim dtRow As DataRow

sql = "select * from T_GOUKEI;"
dt = ds.Tables("T_GOUKEI")  
dtRow = ds.Tables("T_GOUKEI").Rows(0)

'*************************************
長文になってしまうと思い、多少端折った内容になっていますが、
上記がそのSQLの部分のソースです。
私が行いたいのは、上記の抽出結果を、Excelに書き込むというものです。
以前、DataGridViewの内容をExcelに書き込むという質問をし、それについては出来るようになりました。
しかし、今回はSQLの結果なんですが、うまくいきません。
以下が、Excelに書き込む部分のソースです。
'*************************************************

Dim Y As Integer = 0

'ループ処理でSQLの結果を書き込む。
For i As Integer = 0 To dt.Columns.Count - 1
  For j As Integer = 0 To dt.Rows.Count - 1

      'xaをCellsの変数として宣言。
   Dim xa As Excel.Range = xlSheet.Cells
   Dim 番号 As Excel.Range = xa(i + 1, j + 1)

   番号.Value = dtRow(Y)
      '変数の開放を行う。
   MRComObject(番号)
   MRComObject(xa)

   'Yを+1し、次の章に移動させる。
   Y = Y + 1

  Next j
Next i

'************************************************
だいぶ見づらいかも知れませんが、実行すると、
初めの1行目は書き込まれますが、2行目からが書き込まれず、

「列'11'がありません。」というエラーが出てしまいます。

ちなみにテーブルデータは10列までのなので、
10列に達した時点で、次の行に移動し、新たに書き込むという
指定の仕方をご教授願えないでしょうか。
だいぶ長文になってしまいましたが、
よろしくお願いします。

引用返信 編集キー/
■36838 / inTopicNo.2)  Re[1]: SQLの結果をExcelに書き込みたいのですが。
□投稿者/ 魔界の仮面弁士 (1125回)-(2009/06/07(Sun) 01:10:43)
No36837 (jin さん) に返信
> For i As Integer = 0 To dt.Columns.Count - 1
>   For j As Integer = 0 To dt.Rows.Count - 1
>    Dim xa As Excel.Range = xlSheet.Cells
>    Dim 番号 As Excel.Range = xa(i + 1, j + 1)
>    番号.Value = dtRow(Y)
>    MRComObject(番号)
>    MRComObject(xa)
>    Y = Y + 1
>   Next j
> Next i

MRComObject というのが、Marshal.ReleaseComObject の呼び出しを意味していると仮定して…
この場合、xa はループの外に出すべきでしょう。ループ中で毎回、取得と破棄を繰り返す必要は無いはずです。

さらに言えば、このような場合には、複数のセル範囲を持った Range オブジェクトを取得し、
Value に 2 次元配列を渡した方が効率が良いです。あまり巨大な配列は逆効果ですけれども。


それと、ループの前に
> dtRow = ds.Tables("T_GOUKEI").Rows(0)
で、dtRow に「先頭行」への参照をセットしていますが、ループ中では
この dtRow の行位置を変更させていませんが、常に 0 行目のままで良いのですか?


> 「列'11'がありません。」というエラーが出てしまいます。
そもそも、
> 番号.Value = dtRow(Y)
で、変数「Y」を使っているのは何故でしょうか。

dtRow(Y) は、Y列目を意味するわけですよね。列番号を管理しているのは 
> For i As Integer = 0 To dt.Columns.Count - 1
の「i」であって、「Y」では無いと思いますが、この Y は何のために必要なのでしょうか?


> Dim 番号 As Excel.Range = xa(i + 1, j + 1)
Excel のセルは、(0からではなく) 1 から始まるので、+1 しているのは良いとして…
ここでいう xa(x, y) というのは、x = 行番号、y = 列番号の意味になりますよね。
DataTable のループ処理で、i は列番号、j は行番号となっているわけですが、
ここでは、行と列を逆にして出力したいという事で良いのでしょうか?


> 指定の仕方をご教授願えないでしょうか。
そもそも、その10 列の DataTable を、Excel にどのように表示したいのでしょうか?
肝心の仕様を書かずに、期待動作しないコードのみを掲載されても回答に困ってしまいます。

(変数 Y の意味が分からないのと、行列の逆転が意図したコードなのかどうか不明瞭なので…)

引用返信 編集キー/
■36843 / inTopicNo.3)  Re[2]: SQLの結果をExcelに書き込みたいのですが。
□投稿者/ jin (5回)-(2009/06/07(Sun) 17:27:57)
No36838 (魔界の仮面弁士 さん) に返信
回答ありがとうございます。
> MRComObject というのが、Marshal.ReleaseComObject の呼び出しを意味していると仮定して…
> この場合、xa はループの外に出すべきでしょう。ループ中で毎回、取得と破棄を繰り返す必要は無いはずです。

そうでしたか、前回のDataGridViewの値を書き込むというプログラミングで、
似たような書き方で上手くいったので、その方法と同じにしていたもので・・・。

> さらに言えば、このような場合には、複数のセル範囲を持った Range オブジェクトを取得し、
> Value に 2 次元配列を渡した方が効率が良いです。あまり巨大な配列は逆効果ですけれども。

すみません、その2次元配列というのがまだ理解できておりません。
漠然と、SQLの抽出結果を格納するというイメージで書いていました。

> それと、ループの前に
>>dtRow = ds.Tables("T_GOUKEI").Rows(0)
> で、dtRow に「先頭行」への参照をセットしていますが、ループ中では
> この dtRow の行位置を変更させていませんが、常に 0 行目のままで良いのですか?

そうですね、言われて気付きました。Rows(0)ということは、
0行目の値を格納するということで、SQLの抽出結果をすべて格納していることに
ならないんですね。

>>「列'11'がありません。」というエラーが出てしまいます。
> そもそも、
>>番号.Value = dtRow(Y)
> で、変数「Y」を使っているのは何故でしょうか。
>
> dtRow(Y) は、Y列目を意味するわけですよね。列番号を管理しているのは
>>For i As Integer = 0 To dt.Columns.Count - 1
> の「i」であって、「Y」では無いと思いますが、この Y は何のために必要なのでしょうか?
すみません、その部分についてですが、
Y=0ということで、dtRow(0)を書き込んだら、
Y = Y + 1の計算で、Y = 1となり、次の行の値である、dtRow(1) へ移動するという形で
書いていたのですが、ご指摘いただいた、
dtRow = ds.Tables("T_GOUKEI").Rows(0)
という式と矛盾していますので、初めから抽出結果の0行目の値しか
持っていないことになりますよね・・・。

>>Dim 番号 As Excel.Range = xa(i + 1, j + 1)
> Excel のセルは、(0からではなく) 1 から始まるので、+1 しているのは良いとして…
> ここでいう xa(x, y) というのは、x = 行番号、y = 列番号の意味になりますよね。
> DataTable のループ処理で、i は列番号、j は行番号となっているわけですが、
> ここでは、行と列を逆にして出力したいという事で良いのでしょうか?
申し訳ございません。この部分は完全に記載ミスです。
正確には、
Dim 番号 As Excel.Range = xa(j + 1, i + 1)

>>指定の仕方をご教授願えないでしょうか。
> そもそも、その10 列の DataTable を、Excel にどのように表示したいのでしょうか?
> 肝心の仕様を書かずに、期待動作しないコードのみを掲載されても回答に困ってしまいます。
>
> (変数 Y の意味が分からないのと、行列の逆転が意図したコードなのかどうか不明瞭なので…)
言葉足らずなのと、説明があまりにも不十分で、大変申し訳ありません。
まず、変数のYというは、SQLの抽出結果の列の値を示しており、
1列書き込んだら、次の2列目に移動するというイメージでY=0とし、
dtRowの列の数値として宣言しました。
さらに、仕様としましては、まず、項目名(列名)を先頭に書き込みたいと考えておりますが、
それ以外で、特にこうしたいという仕様はありません。
単純に、SQLから取得したすべての値を0行目から順に書き込んでいくというだけです。

それと、改めて自分で読み返しても、完全に質問の仕方そのものが、
おざなりすぎましたので、
大変申し訳ありませんでした。
引用返信 編集キー/
■36846 / inTopicNo.4)  Re[3]: SQLの結果をExcelに書き込みたいのですが。
□投稿者/ 魔界の仮面弁士 (1126回)-(2009/06/08(Mon) 00:50:58)
No36843 (jin さん) に返信
> すみません、その2次元配列というのがまだ理解できておりません。

たとえば、元データが 5行10列だとしたら、
 Dim values(4, 9) As Object
 values(0, 0) = "1行目1列目"
 '   :
 values(4, 9) = "5行目10列目"
のような二次元配列を生成し、それを
 rng = sheet1.Range("A1:J5")
 rng.Value = values
 Marshal.ReleaseComObject(rng)
のように、5行10列のセル範囲に対して、複数データを一度に代入するということです。

Excel への書き込み回数が一回だけになるので、効率が良くなります。


> そうですね、言われて気付きました。Rows(0)ということは、
> 0行目の値を格納するということで、SQLの抽出結果をすべて格納していることに
> ならないんですね。
元のコードを生かすのであれば、こうかな。

Dim cells As Excel.Range = xlSheet.Cells
For rowIndex As Integer = 0 To dt.Rows.Count - 1
 For colIndex As Integer = 0 To dt.Columns.Count - 1
  Dim 番号 As Excel.Range = cells(rowIndex + 1, colIndex + 1)
  番号.Value = dt.Rows(rowIndex)(colIndex)
  MRComObject(番号)
 Next
Next
MRComObject(cells)


>>ここでは、行と列を逆にして出力したいという事で良いのでしょうか?
> 申し訳ございません。この部分は完全に記載ミスです。
意図を明確にするためにも、i や j といった変数名を使うのではなく、
行/列などを意味する変数名にした方が良いと思いますよ。
(もしも、一文字変数を使うにしても、x/y のような名前にするとか)
引用返信 編集キー/
■36852 / inTopicNo.5)  Re[4]: SQLの結果をExcelに書き込みたいのですが。
□投稿者/ hito (19回)-(2009/06/08(Mon) 09:50:42)
No36846 (魔界の仮面弁士 さん) に返信
> ■No36843 (jin さん) に返信

回答ありがとうございます。
無事、目的の動作を行うことが出来ました。

> 元のコードを生かすのであれば、こうかな。
> 
> Dim cells As Excel.Range = xlSheet.Cells
> For rowIndex As Integer = 0 To dt.Rows.Count - 1
>  For colIndex As Integer = 0 To dt.Columns.Count - 1
>   Dim 番号 As Excel.Range = cells(rowIndex + 1, colIndex + 1)
>   番号.Value = dt.Rows(rowIndex)(colIndex)
>   MRComObject(番号)
>  Next
> Next
> MRComObject(cells)

上記のソースを元に書いてみたのですが、
以下のようなソースを書いてみたところ、うまく書き込むことができました。

    'ループでDataGridViewの指定した範囲の値を書き込む
        Dim cells As Excel.Range = xlSheet.Cells
        For rowIndex As Integer = 0 To dt.Rows.Count - 1
            For colIndex As Integer = 0 To dt.Columns.Count - 1
                If colIndex < 11 Then
                    dtRow = ds.Tables("T_GOUKEI").Rows(rowIndex)
                    Dim 番号 As Excel.Range = cells(rowIndex + 1, colIndex + 1)
                    '番号.Value = dtRow(rowIndex)(colIndex)
                    番号.Value = dtRow(colIndex)
                    MRComObject(番号)
                End If
            Next
        Next
        MRComObject(cells)

せっかくご教授いただいて、大変失礼なのですが、
ループ処理内で、dtRowの変数に、SQLで取得したデータを格納するという仕様にしたところ、
目的どおりの結果を得ることはできました。

しかし、上記のソースは、魔界の仮面弁士 さんがご教授してくださった
動作を無視したソースでもあるので、失礼となってしまいますが、
このソースをベースにして、自分なりに改良を加えていこうかと思います。

> 意図を明確にするためにも、i や j といった変数名を使うのではなく、
> 行/列などを意味する変数名にした方が良いと思いますよ。
> (もしも、一文字変数を使うにしても、x/y のような名前にするとか)
ご指摘ありがとうございます。
長文の問題以前に、見る人のことを考えたソースを書くようにするべきでした。
以後、気をつけます。

解決済み
引用返信 編集キー/
■36860 / inTopicNo.6)  Re[5]: SQLの結果をExcelに書き込みたいのですが。
□投稿者/ 魔界の仮面弁士 (1128回)-(2009/06/08(Mon) 12:16:28)
No36852 (hito さん) に返信
ここは、jin さんが投稿されたスレッドのはずですが。。。
とりあえずは、文脈から同一人物と仮定しておきますね。


> 以下のようなソースを書いてみたところ、うまく書き込むことができました。
> 'ループでDataGridViewの指定した範囲の値を書き込む
あれ? DataGridView ではなく、DataTable ですよね。


> For rowIndex As Integer = 0 To dt.Rows.Count - 1
>  For colIndex As Integer = 0 To dt.Columns.Count - 1
>   If colIndex < 11 Then
>    dtRow = ds.Tables("T_GOUKEI").Rows(rowIndex)

変数 dt には、すでに T_GOUKEI テーブルへの参照が入っていますから、
>   dtRow = ds.Tables("T_GOUKEI").Rows(rowIndex)
ではなく、ここでは
  dtRow = dt.Rows(rowIndex)
で充分ですよ。Tables コレクションから再取得する必要は無いはずです。


また、この部分での If 文は不要なはずです。外しておいた方がよいでしょう。というのも、
最初の発言で、『ちなみにテーブルデータは10列までのなので』と書いておられましたよね。
ループ中の colIndex の最大値というのは、For ループの To 句で指定されていますから、
この場合の colIndex 変数は 必ず 0〜10 という範囲をとります。11 以上にはなりません。

もし、DataTable の列数がもっと多くて、その場合でも最大 10 列までしか
出力したくないのだとしても、ループ内で If 判定させる必要はなく、
 For colIndex As Integer = 0 To 10
あるいは、
 For colIndex As Integer = 0 To Math.Min(10, dt.Columns.Count - 1)
のようにしておくだけで済むかと思います。



> ループ処理内で、dtRowの変数に、SQLで取得したデータを格納するという仕様にしたところ、
考え方は良いと思いますが、dtRow を取得する場所が適切では無いようです。
行位置は、外側のループ時点では変化しないので、内側のループで毎回取得しなおすのは
意味がありません。この場合には dtRow の取得位置を

 For rowIndex As Integer = 0 To dt.Rows.Count - 1
  dtRow = dt.Rows(rowIndex) '本来あるべき位置
  For colIndex As Integer = 0 To dt.Columns.Count - 1
   'dtRow = dt.Rows(rowIndex) '元の位置
     :
     :
  Next
 Next

のようにした方が良いでしょう。


> しかし、上記のソースは、魔界の仮面弁士 さんがご教授してくださった
> 動作を無視したソースでもあるので、失礼となってしまいますが、
無視されたとは思いませんよ。慮っていただきありがとうございます。
少なくとも No36838 の件は反映されているわけですし、No36846 のサンプルも
試された上での修正であるように見えますから、読み取っていただけたと思っています。
(むしろ回答側としては、そのまま無加工で使われるよりも嬉しいです)



ついでに、No36846 の「二次元配列」を使った方法を紹介しておきます。
jin さん(hito さん?)の方法は、データを 1 つずつ置いていくので、コードが分かりやすいのが
メリットですが、たとえばデータが 1000 件あると、おそらくは出力完了までに
数十秒かかってしまうかと思います。

配列を使って一度に渡すと、Value プロパティへの書き込みが一度で済むので、
1000 件のデータでも一瞬で表示させることができます。


Dim values(dt.Rows.Count, dt.Columns.Count) As Object

' No36843 で、「項目名(列名)を先頭に書き込みたい」と書かれていたので…。
For Each col As DataColumn In dt.Columns
values(0, col.Ordinal) = col.ColumnName
Next

'1行目からはデータ
For rowIndex As Integer = 1 To dt.Rows.Count
Dim dtRow As DataRow = dt.Rows(rowIndex - 1)
For colIndex As Integer = 0 To dt.Columns.Count - 1
values(rowIndex, colIndex) = dtRow(colIndex)
Next
Next

'出力先となるセル範囲を 変数 rng に取得
Dim cells As Excel.Range = xlSheet.Cells
Dim rngEnd As Excel.Range = cells(dt.Rows.Count, dt.Columns.Count - 1)
Dim rng As Excel.Range = xlSheet.Range("$A$1:" & rngEnd.Address)
MRComObject(rngEnd)

'★複数データを、2次元配列で一度に渡す★
rng.Value = values 'Excel へデータを渡す回数が、この1回だけで済む。

'後始末
MRComObject(rng)
MRComObject(cells)
解決済み
引用返信 編集キー/
■36876 / inTopicNo.7)  Re[6]: SQLの結果をExcelに書き込みたいのですが。
□投稿者/ jin (6回)-(2009/06/08(Mon) 15:59:44)
2009/06/08(Mon) 16:00:51 編集(投稿者)
No36860 (魔界の仮面弁士 さん) に返信
> ■No36852 (hito さん) に返信
> ここは、jin さんが投稿されたスレッドのはずですが。。。
> とりあえずは、文脈から同一人物と仮定しておきますね。
すみません、同一人物です。
元々、jinという名に統一しようと注意していたのですが、
間違えて昔の名を使用してしまいました。
混乱させてしまい申し訳ありません。
 

>>以下のようなソースを書いてみたところ、うまく書き込むことができました。
>>'ループでDataGridViewの指定した範囲の値を書き込む
> あれ? DataGridView ではなく、DataTable ですよね。

以前書いていたDataGridViewの書き込みを元に書いていたので、
その時のメッセージだけが残ったままでした。
 
> また、この部分での If 文は不要なはずです。外しておいた方がよいでしょう。というのも、
> 最初の発言で、『ちなみにテーブルデータは10列までのなので』と書いておられましたよね。
> ループ中の colIndex の最大値というのは、For ループの To 句で指定されていますから、
> この場合の colIndex 変数は 必ず 0〜10 という範囲をとります。11 以上にはなりません。
> 
> もし、DataTable の列数がもっと多くて、その場合でも最大 10 列までしか
> 出力したくないのだとしても、ループ内で If 判定させる必要はなく、
>  For colIndex As Integer = 0 To 10
> あるいは、
>  For colIndex As Integer = 0 To Math.Min(10, dt.Columns.Count - 1)
> のようにしておくだけで済むかと思います。
 
For colIndex As Integer = 0 To 10
の書き方でIf句を用いずに書き込めました。
考えてみれば、自分で列数を知っているのだから、
こういう指定の仕方も出来たんですよね、
今までのソースを元にしてたりして、
要領のいい書き方というものを
考えておりませんでした・・・。
 
>>ループ処理内で、dtRowの変数に、SQLで取得したデータを格納するという仕様にしたところ、
> 考え方は良いと思いますが、dtRow を取得する場所が適切では無いようです。
> 行位置は、外側のループ時点では変化しないので、内側のループで毎回取得しなおすのは
> 意味がありません。この場合には dtRow の取得位置を
> 
>  For rowIndex As Integer = 0 To dt.Rows.Count - 1
>   dtRow = dt.Rows(rowIndex)   '本来あるべき位置
>   For colIndex As Integer = 0 To dt.Columns.Count - 1
>    'dtRow = dt.Rows(rowIndex) '元の位置
>      :
>      :
>   Next
>  Next
> 
> のようにした方が良いでしょう。
For rowIndex As Integer = 0 To dt.Rows.Count - 1
    dtRow = dt.Rows(rowIndex)
    For colIndex As Integer = 0 To 10

という書き方にしたところ、無事うまくいきました。
ありがとうございます。
 
> ついでに、No36846 の「二次元配列」を使った方法を紹介しておきます。
> jin さん(hito さん?)の方法は、データを 1 つずつ置いていくので、コードが分かりやすいのが
> メリットですが、たとえばデータが 1000 件あると、おそらくは出力完了までに
> 数十秒かかってしまうかと思います。
> 
> 配列を使って一度に渡すと、Value プロパティへの書き込みが一度で済むので、
> 1000 件のデータでも一瞬で表示させることができます。
> 
> 
> Dim values(dt.Rows.Count, dt.Columns.Count) As Object
> 
> ' No36843 で、「項目名(列名)を先頭に書き込みたい」と書かれていたので…。
> For Each col As DataColumn In dt.Columns
>     values(0, col.Ordinal) = col.ColumnName
> Next
> 
> '1行目からはデータ
> For rowIndex As Integer = 1 To dt.Rows.Count
>     Dim dtRow As DataRow = dt.Rows(rowIndex - 1)
>     For colIndex As Integer = 0 To dt.Columns.Count - 1
>         values(rowIndex, colIndex) = dtRow(colIndex)
>     Next
> Next
> 
> '出力先となるセル範囲を 変数 rng に取得
> Dim cells As Excel.Range = xlSheet.Cells
> Dim rngEnd As Excel.Range = cells(dt.Rows.Count, dt.Columns.Count - 1)
> Dim rng As Excel.Range = xlSheet.Range("$A$1:" & rngEnd.Address)
> MRComObject(rngEnd)
> 
> '★複数データを、2次元配列で一度に渡す★
> rng.Value = values    'Excel へデータを渡す回数が、この1回だけで済む。
> 
> '後始末
> MRComObject(rng)
> MRComObject(cells)

ここまで分かりやすい説明をしていただいて、本当に感謝です。
今回の質問と同じような仕様をいくつか作成する予定ですので、今回ご教授していただいた内容を
活かしたソースを書こうと思います。
ありがとうございます。

解決済み
引用返信 編集キー/


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

このトピックに書きこむ

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

管理者用

- Child Tree -