ASP.NETのEXCEL取込でうまくいきません。

Question

Visual Studio 2015 ASP.NET Webフォーム
で開発しています。

Excelからデータを取り込んでDBに登録したいのですが、
数値がなぜか日付と認識されているようで、
エクセルで表示されている通りのデータが取得できません。
さらに2958465以上の数値が含まれるシートの取り込みに失敗します。
「有効な OleAut 日付ではありません。 」
というエラーが出ます。
「50」という数値が入ったセルの値が「1900/02/19」になってしまったりもします。
どうしたら数値を数値として取得するのか教えてください。
EXCEL取込をしている部分のソースです。

    Dim ds As New DataSet
    Dim excelVersion As Integer
    Dim schemaTable As New DataTable
    Dim flg As Boolean = False
    Dim i As Integer

    Dim strUpFilePath() As String     '戻り配列
    Dim intCnt As Integer       '配列添え字

    Dim conn As New OleDbConnection

    Try

        '行単位データをカンマ部分で分割し、配列へ格納
        strUpFilePath = Split(wUpFilePath, ",")

        excelVersion = CInt(GetExcelVersion(strUpFilePath(intCnt)))

        If excelVersion >= 12 Then
            conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
                                                   + strUpFilePath(intCnt) _
                                                   + ";Extended Properties=""Excel 12.0; HDR=NO; IMEX=1;""")
        Else
            conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.4.0;Data Source=" _
                                                   + strUpFilePath(intCnt) _
                                                   + ";Extended Properties=""Excel 8.0; HDR=NO; IMEX=1;""")
        End If

        conn.Open()

        schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
                                                           New Object() {Nothing, Nothing, Nothing, "TABLE"})
        ds = New DataSet

        For i = 0 To schemaTable.Rows.Count - 1
            If schemaTable.Rows(i)("TABLE_NAME").ToString.Contains("Print_Area") Then
                Continue For
            End If

            Dim ExcelCommand As New OleDbCommand("SELECT * FROM [" & schemaTable.Rows(i)("TABLE_NAME").ToString & "] ", conn)
            Dim ExcelAdapter As New OleDbDataAdapter(ExcelCommand)

            ExcelAdapter.Fill(ds, schemaTable.Rows(i)("TABLE_NAME").ToString)

            ExcelCommand.Dispose()
            ExcelAdapter.Dispose()
        Next i

Answer 1

Extended Properties=""Excel 12.0;HDR=NO;IMEX=1;MaxScanRows=1;""
としました。
EXCELの一行目のセルの書式を文字列とし、スペースを入力しておくことによって
日付判定されていた型が文字型として正しく取り込まれました。
途中に日付型のセルが存在することで、数値型のセルも日付に変換されてしまっていました。

Comments

[twck]

> EXCELはお客さんから提供されているものなので変えられません。

って書いてあったから、書式を変更することも出来ないと思ってました。
まあなんにせよ解決してよかったです。

Answer 2

単純にFillせずに、スキーマ情報（テーブル構造）を取得・変更してからFillしてみたらどうでしょうか？

具体的には ExcelAdapter.Fill の１行の代わりに以下のようなコードにします。

'まずはスキーマ情報を取得する
Dim dt As DataTable = New DataTable
ExcelAdapter.FillSchema(dt, SchemaType.Source)

'Date型の列をDouble型にする
For Each column As System.Data.DataColumn In dt.Columns
	If column.DataType Is GetType(Date) Then
		column.DataType = GetType(Double)
	End If
Next

'スキーマ情報を使ってデータを取得する
ExcelAdapter.Fill(dt)
dt.TableName = schemaTable.Rows(i)("TABLE_NAME").ToString
ds.Tables.Add(dt)

とりあえず Date型と判定された列を全て Double型にしています。

本当にDate型だった列までDouble型にしてしまうんですけどね・・・

Double型とDate型をなるべく正確に判定したい場合は EXCEL自身を操作するか、NPOIなどを使うことになるかも。

EXCELの内部には日付型が無くて数値型として管理しているので難しいですよね。

Comments

[TomomiBaba]

回答ありがとうございます。
やってみましたが、ダメでした。
下記のエラーが出ました。

'DateTime' から 'Double' への無効なキャストです。列 F9 に <1899/12/30 0:00:00> を格納できませんでした。 必要な型は Double です。

EXCELはお客さんから提供されているものなので変えられません。
型も列ごとではなく、セル単位で違います。
金額の入っているセルのデータが日付で取得されてしまって困っています。
EXCELのセルの書式は数値になっているのに、どうして？？？

[twck]

> 型も列ごとではなく、セル単位で違います。

列の中でデータ型が統一されてないとなると、まずは全ての列をObject型にしたらどうでしょうか？

For Each column As System.Data.DataColumn In dt.Columns
column.DataType = GetType(Object)
Next

ただし、これでエラーが出なくなったとしても、結局 Object型 の中に Date型 として入っているので、後から値を１つ１つ調べて Double型 に変換するコードを自分で書かなくてはなりませんが・・・

EXCEL は 1900/01/01 が 1 、1900/01/02 が 2、1900/02/19 が 50 となるので、1899/12/31 からの日数を Double型の数値とすればいいと思います。

それでも出来ないようなら SQLで取り込む仕組みは諦めた方がいいです。

タグに ASP.NET が付いているので EXCEL.exe をプログラムから操作する COM の Microsoft Office xx.x Object Library や .NET の Microsoft.Office.Interop.Excel は使えませんね。

となると Open XML、NPOI、ClosedXML、EPPlus などのライブラリを使うのが最終手段になると思います。おすすめは NPOI ですかね。

> EXCELのセルの書式は数値になっているのに、どうして？？？

EXCELは内部的に Date型が無いので、Double型として扱われているんですけどね。
おそらく ODBCドライバ 辺りが気を利かせて、表示形式とかの情報を元に Date型に変換しているのでしょうけど、ユーザーが作った EXCEL のデータ型なんて信用できませんからね。

[twck]

質問文の中に「2958465以上の数値が含まれるシートの取り込みに失敗します」とあるから、Object型にしてもダメかもしれない。