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

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

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

Re[4]: Excelの読み込み字に日付がシリアル値で表示されます


(過去ログ 53 を表示中)

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

■29863 / inTopicNo.1)  Excelの読み込み字に日付がシリアル値で表示されます
  
□投稿者/ ひより (1回)-(2008/12/16(Tue) 00:36:03)

分類:[C#] 

初心者です。お世話になります。

使用言語はC#です。
やりたいことは、「OleDbを使ってExcelのファイルを読み込み、データセットに格納する」なのですが、
読み込むExcelファイルの中で、セルの形式が日付型のデータが入っていると、しばしばシリアル値に変換されてデータセットに格納されてしまいます。

例)2003/2/25(Excel)→37678(データセットに格納された値)

まったく同じファイルを読み込んでも、そのときによってシリアル値になったりならなかったりと結果はまちまちです。
Excelファイルを読み込むときになんとか日付型のデータをシリアル値に変換せずにデータセットに格納したいのですが、
調べ方が甘いためか、その方法を見つけられませんでした。

お手数おかけしますが、なにとぞご助言をよろしくお願いします。

以下参考にさせて頂いたソースコードです。
(参考サイト様:http://d.hatena.ne.jp/nagakura_eil/20080716/p1)

///////////////////////////////////////////////////////////////////////////////
string conString = String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;
          Data Source={0};Extended Properties=""Excel 8.0;
          HDR=No;Imex=1"";",path);

OleDbConnection con = new OleDbConnection(conString);
con.Open();
DataTable schemaTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);
con.Close();

DataSet ds = new DataSet();
foreach(DataRow row in schemaTable.Rows) 
{
	string tableName = row["TABLE_NAME"].ToString();
	string sql = String.Format("SELECT * FROM [{0}]",tableName);
	OleDbDataAdapter oda = new OleDbDataAdapter(sql,con);
	DataTable table = new DataTable(tableName);
	oda.Fill(table);
	ds.Tables.Add(table);
}
return ds;
////////////////////////////////////////////////////////////////////////////////////

引用返信 編集キー/
■29884 / inTopicNo.2)  Re[1]: Excelの読み込み字に日付がシリアル値で表示されます
□投稿者/ .SHO (416回)-(2008/12/16(Tue) 11:12:58)
No29863 (ひより さん) に返信

掲載されたソースには問題はなさそうに見えます。
ご質問の状況を再現できません。
引用返信 編集キー/
■29887 / inTopicNo.3)  Re[2]: Excelの読み込み字に日付がシリアル値で表示されます
□投稿者/ こあら (42回)-(2008/12/16(Tue) 11:36:17)
VB.NETですが紹介してみます。

Visual Basic .NET と ADO.NET を使用して Excel ブックのレコードの取得と変更を行う方法
http://support.microsoft.com/kb/257819/ja/


> まったく同じファイルを読み込んでも、そのときによってシリアル値になったりならなかったりと結果はまちまちです。

プログラム的にそのような振る舞いが可能なのかどうかわかりませんが・・・

kbをざっと読んだところ、同じ列中に異なるデータ型(セルの書式のことかもしれません)が混在するケースでは、
正しく取得できないことがあるそうです。
また、Imex=1を指定しても、標準書式と日付書式が混在する列で標準書式のセル数の方が多いと?
本来日付書式のセルの値が標準書式(シリアル値)で取得してしまう、ということなのかもしれません。

これはADO.NETの仕様ということになるのでしょうか。
参考になれば幸いです。

引用返信 編集キー/
■29888 / inTopicNo.4)  Re[3]: Excelの読み込み字に日付がシリアル値で表示されます
□投稿者/ .SHO (417回)-(2008/12/16(Tue) 12:00:21)
No29887 (こあら さん) に返信

> kbをざっと読んだところ、同じ列中に異なるデータ型(セルの書式のことかもしれません)が混在するケースでは、
> 正しく取得できないことがあるそうです。

この情報をもとに、同じ列に標準書式のデータを1つ追加したら再現しました。

> プログラム的にそのような振る舞いが可能なのかどうかわかりませんが・・・

注目しているセル以外のセルを変更することで、再現したりしなかったりするってことかも知れないですね。

> これはADO.NETの仕様ということになるのでしょうか。

これが仕様だと辛いですね^^;
バグ?
引用返信 編集キー/
■29898 / inTopicNo.5)  Re[3]: Excelの読み込み字に日付がシリアル値で表示されます
□投稿者/ 魔界の仮面弁士 (949回)-(2008/12/16(Tue) 12:56:45)
# 以下、蛇足情報(回答に非ず)。

No29887 (こあら さん) に返信
> これはADO.NETの仕様ということになるのでしょうか。

ADO.NET ではなく、Microsoft.Jet.OLEDB.4.0 の Excel I-ISAM ドライバの仕様となります。

JET エンジンによる Excel 読み込み時の型の判断手順については、
Jet 3.x 系 および 4.x 系のドキュメントにも書かれている動作です。
(一部は、Access の開発者向けヘルプにも記載されていたハズ…)


そのため、ADO.NET に関わらず、同エンジンを利用するミドルウェアであれば、
OLE DB API / ADO / DAO 、そして Microsoft の Excel ODBC ドライバでも
同様の問題が発生します。

サードパーティ製の Excel 用 OLE DB/Excel 用マネージプロバイダでは、異なる動作になりえますけれどね。
引用返信 編集キー/
■29900 / inTopicNo.6)  Re[1]: Excelの読み込み字に日付がシリアル値で表示されます
□投稿者/ .SHO (421回)-(2008/12/16(Tue) 13:08:45)
No29863 (ひより さん) に返信

ということで、自分で、取得したデータのフォーマットをチェックして
1900年1月1日からの通算日として、変換しちゃった方が早そうです。
つか、自分にはそれしか解決策がうかばない。。。
引用返信 編集キー/
■29904 / inTopicNo.7)  Re[2]: Excelの読み込み字に日付がシリアル値で表示されます
□投稿者/ みきぬ (309回)-(2008/12/16(Tue) 14:03:06)
No29900 (.SHO さん) に返信
> ■No29863 (ひより さん) に返信
>
> ということで、自分で、取得したデータのフォーマットをチェックして
> 1900年1月1日からの通算日として、変換しちゃった方が早そうです。
> つか、自分にはそれしか解決策がうかばない。。。

計算する場合は、Excelが1900年を閏年として扱うことに注意してください。
# Excel が悪いっつーよりかは、過去のアプリが悪いんだけど
引用返信 編集キー/
■29914 / inTopicNo.8)  Re[3]: Excelの読み込み字に日付がシリアル値で表示されます
□投稿者/ 魔界の仮面弁士 (950回)-(2008/12/16(Tue) 14:53:02)
No29863 (ひより さん) に返信
> 例)2003/2/25(Excel)→37678(データセットに格納された値)
当方(Excel 2007 SP1 + .NET 2.0 SP1)では再現しませんでした。
こちらの環境では、「37678」ではなく、「37677」になってしまいます。
(.SHO さんの環境も、37678 だったのでしょうか?)

環境の違いだとしたら、厄介ですね…。


■No29904 (みきぬ さん) に返信
> 計算する場合は、Excelが1900年を閏年として扱うことに注意してください。
> # Excel が悪いっつーよりかは、過去のアプリが悪いんだけど

その xls ファイルが、日付を「1904 年から計算する」モードに設定されていた場合、
さらなる値のズレが発生する場合があります。


System.DateTime の FromOADate / ToOADate の場合:(Jet の日付型に対応)
 -657435.0 → 非対応
 -657434.0 →   100年 1月 1日
       : :   :
      -1.0 →  1899年12月29日
       0.0 →  1899年12月30日
       1.0 →  1900年12月31日
       2.0 →  1900年 1月 1日
       : :   :
      59.0 →  1900年 2月27日
      60.0 →  1900年 2月28日
      61.0 →  1900年 3月 1日
       : :   :
 2958465.0 →  9999年12月31日
 2958466.0 → 非対応



Excel の場合:(規定のモード)
      -1.0 → 非対応
       0.0 →  1900年 1月 0日 (★こんな日付はありえない…)
       1.0 →  1900年 1月 1日
       2.0 →  1900年 1月 2日
       : :   :
       : :   :
       : :   :
      58.0 →  1900年 2月27日
      59.0 →  1900年 2月28日
      60.0 →  1900年 2月29日 (★こんな日付はありえない…)
      61.0 →  1900年 3月 1日
       : :   :
 2958465.0 →  9999年12月31日
 2958466.0 → 非対応


Excel の場合:(Date1904モード)★負数の扱いに注意★
 -2957004.0 → 非対応
 -2957003.0 → -9999年12月31日
        : :   :
       -1.0 → -1904年 1月 2日
        0.0 →  1904年 1月 1日
        1.0 →  1904年 1月 2日
        2.0 →  1904年 1月 3日
        : :   :
        : :   :
        : :   :
       58.0 →  1904年 2月28日
       59.0 →  1904年 2月29日
       60.0 →  1904年 3月 1日
       61.0 →  1904年 3月 2日
        : :   :
  2957003.0 →  9999年12月31日
  2957004.0 → 非対応

引用返信 編集キー/
■29922 / inTopicNo.9)  Re[4]: Excelの読み込み字に日付がシリアル値で表示されます
□投稿者/ .SHO (425回)-(2008/12/16(Tue) 15:34:31)
No29914 (魔界の仮面弁士 さん) に返信

>>例)2003/2/25(Excel)→37678(データセットに格納された値)
> 当方(Excel 2007 SP1 + .NET 2.0 SP1)では再現しませんでした。
> こちらの環境では、「37678」ではなく、「37677」になってしまいます。
> (.SHO さんの環境も、37678 だったのでしょうか?)

いや、1900年の閏年の件までは検証していませんでした。
で、今やってみたら、私も「37677」です。

引用返信 編集キー/
■29923 / inTopicNo.10)  Re[1]: Excelの読み込み字に日付がシリアル値で表示されます
□投稿者/ .SHO (426回)-(2008/12/16(Tue) 15:47:26)
No29863 (ひより さん) に返信

> まったく同じファイルを読み込んでも、そのときによってシリアル値になったりならなかったりと
> 結果はまちまちです。

いろいろ動かしてて気づいたのですが、対象ファイルをEXCELで開いてるとシリアル値になって
開いていないとシリアル値にはならないですね。

まぁ、その変のことを考慮して、運用に合わせて仕様を決めるしかないでしょう。

引用返信 編集キー/
■29932 / inTopicNo.11)  Re[2]: Excelの読み込み字に日付がシリアル値で表示されます
□投稿者/ みきぬ (311回)-(2008/12/16(Tue) 16:55:20)
> まぁ、その変のことを考慮して、運用に合わせて仕様を決めるしかないでしょう。
>
別の方法を検討したほうがいいような気が。
例えば Excel の COM オブジェクトを使って頑張るとか…でもこの方法もいまいちだしなぁ(悩
引用返信 編集キー/
■29933 / inTopicNo.12)  Re[3]: Excelの読み込み字に日付がシリアル値で表示されます
□投稿者/ .SHO (427回)-(2008/12/16(Tue) 17:01:19)
No29932 (みきぬ さん) に返信

> 別の方法を検討したほうがいいような気が。
> 例えば Excel の COM オブジェクトを使って頑張るとか…でもこの方法もいまいちだしなぁ(悩

まぁ、それも含めて仕様を決めればいいと思います。

自分なら、排他書き込みで、そのファイルをオープンしてみて
失敗したら、ファイルが他で使用されています・・・のようなメッセージを表示して
使用者にクローズさせますね。
引用返信 編集キー/
■29958 / inTopicNo.13)  Re[4]: Excelの読み込み字に日付がシリアル値で表示されます
□投稿者/ ひより (2回)-(2008/12/17(Wed) 00:47:42)
2008/12/17(Wed) 00:48:19 編集(投稿者)

たくさんのアドバイス大変参考になりました。

そしてひとつお詫びを・・・
日付の件ですが私の表記がまちがっていました。
再度検証してみたところ"2003/2/25"は"37677"でした。
いたずらに場を混乱させてしまい、申し訳ありませんでした。

皆様のアドバイスを基に、仕様は以下のようにしようと考えています。
@対象ファイルが開かれていたら、ユーザーに閉じてもらう。
Aもしシリアル値が読み込まれた場合は日付変換を行う。
 ※読み込むファイルのフォーマットは常に固定であり、その中では規定モードで計算されているため、
  今回「1904年から計算する」モードはないだろうと想定し、規定モードでの日付変換でいこうと思います。

皆様本当にありがとうございました。
解決済み
引用返信 編集キー/


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

このトピックに書きこむ

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

管理者用

- Child Tree -