.NET .ADO でのSQL文でEXCELファイルのUPDATEで時刻の列に文字があると時刻が正しく入力されません。

質問

文字が含まれない列の時刻は正しく更新されるのですが、文字が含まれる列は通常は右寄せのはずが左寄せになってしまい、表示形式は「h:mm」なのですがセルを選択すると上のバー？に出るはずの秒が出ません・・・書式設定は一応、時刻になっているのですが・・・たぶん文字列になってしまっているような感じです。

EXCELファイルの形式は下記のようになっています。書式設定は、A列はすべて日付、BとC列はすべて時刻になっています。問題の「休」のセルも時刻のままです。
........A.........B........C.....
1......日付......出勤.....退勤
2....2010/1/1....7:54....18:24
3....2010/1/2....休........
4....2010/1/3....7:48....18:28
5....2010/1/4....7:56....18:33

日付と退勤の列はうまく更新できるのですが、B列の出勤列に「休」と書かれている行があるとB列の更新がうまくいきません。

ソースは、下記のようになっています。
con.ConnectionString = _
"Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=" & _
"C:\DATE\タイムカード.xls;" & _
"Extended Properties=""Excel 12.0;HDR=YES;"""
Dim cmd As New OleDbCommand()
cmd.Connection = con
con.Open()
cmd.CommandText = "UPDATE [Sheet1$] SET 日付 = @hiduke, 出勤 = @jikoku, 退勤 = @nijikoku WHERE 日付 = 40203"
cmd.Parameters.Add("@hiduke", OleDbType.Variant).SourceColumn = "日付"
cmd.Parameters("@hiduke").Value = Format(DateValue("2010/1/21"), "yyyy/MM/dd")
cmd.Parameters.Add("@jikoku", OleDbType.Variant).SourceColumn = "出勤"
cmd.Parameters("@jikoku").Value = Format(TimeValue("2:40"), "H:mm")
cmd.Parameters.Add("@nijikoku", OleDbType.VarChar).SourceColumn = "退勤"
cmd.Parameters("@nijikoku").Value = Format(TimeValue("15:2"), "H:mm")
cmd.ExecuteNonQuery()

.NET自体初めてなのでおかしなところもあると思いますが、色々試しても結局どうにもならず現在は上記のような状態です。
上記の出勤と退勤のOleDbTypeが違いますが色々試しましたがすべてダメでした・・・
試したのは下記になります
Variant
VarChar
VarChar, 255
Char
DBTime
どのようにすればうまくいくでしょうか?

回答No.12

NOBNNN · ベストアンサー

#1です。
#10にも書いてありますが

＞＞説明不足で申し訳ないのですが、エクセルファイルの時刻を入力する列の書式設定は文字列でなく時刻＞＞にしたいのです。この列の書式設定をすべて文字列にしてしまうなら、特に変更なく私のソースでも問＞＞題なく更新できてしまいます・・・

SQLサーバーやＡＣＣＥＳＳのフィールドも当然、文字型 NvarChar(10) とか　あります。

それを　時刻型という型はありませんので日付時刻型（DateTime）にしなければなりません。

時刻の場合　1900/01/01 13:52:00.000 というような形になります。

これを無視して　「休」　などの文字を日付時刻型 に書き込むと　エラーになります。

だから　すべて文字列で扱うしかないのです。

#10 で説明した　出勤(時）と出勤(分）入力規則のリストで入力させて　VB.NET での
Excelシート入力時におけるＳＱＬで　合体させてあげれば大丈夫です。

(IIF(ISNUMERIC(TB.出勤_時),TB.出勤_時 & &':' & TB.出勤_分,TB.出勤_時)) as 出勤

というよな構文です　テストしてませんのでデバッグしてください

もし、文字型がどうしてもいやだというならExcelで「休」は別のセルに分けて記入
できるようにしてください。
ＤＢの出力項目も「休」(Nvarchar(4))と出勤（DtaeTime型）　としてください。

お礼

>すべて文字列で扱うしかないのです。
なるほど・・・やはりそうなのですね。
どの方法でやるか、やり方を検討してみます。

初心者の私にはNOBNNN様のソースが非常に勉強になりました。
ありがとうございました。

回答No.11

tom11

追伸

エクセルファイルをサーバにして、SQLで、操作するとき
データの挿入、更新、参照を、SQLで、
管理しないと、データの整合性が取れなくなると
すぐおかしくなるかも、

スーパーモードとして、エクセルファイルを
開いて、操作確認する場合は、良いのですが。
サーバファイルを、直接いろいろ操作すると
問題が、いろいろ起きるかもしれません。

そういう意味で、もし、文字型で、今のところ解決しているなら、
そのデータを見るときに、
SQLで、参照して、文字型を、時間型に変えるとかの処理を
するとか、そのような逃げ道の方が、良いかもしれません。

お礼

なるほど・・・色々検討して方法を考えようと思います。
勉強になりました。
ありがとうございました。

回答No.10

tom11

そもそも、勘違いしていました。
これって、エクセルファイル自体をサーバファイルにしているのですよね。

通常、データーベースは、それを構成するテーブル(エクセルで言うシート)
のカラムは、最初の段階で、データ型を定義しています。
データを見て、不思議に思ったのは、もともとのデータに
時間データと文字データが、混在しているということです。
時間データは、文字型にすれば、文字に統一できますが。
エクセルで、時間データとして、、扱いたい時は
別に、処理しなくては、ならなくなると思います。
文字型と、時間型は、混在できいないと思います。

逃げ道として、時間と、休を、別カラムにして、
時間は、時間型として
逃げるか、SQLをあきらめ、エクセルを直接
VB.netで、操作するか？？？

いろいろ、逃げ道は、あると思いますが。

あと時間型にしても、データタイプは、時間なので、
H:mmという変換は、無意味です。
表示する時に、時間型をフォーマットして、H:mmの
文字列に変換することは、出来ると思いますが。

回答No.9

NOBNNN

>>やはり、時刻の書式設定の列に文字列が入っているということでおかしくなってしまうのでしょうが、>>何とか方法はないものでしょうか？
>>また、入力後にそのセルの書式設定をし直すということはできるのでしょうか？（エクセルファイルを>>開かずに）

全部、文字列でもかまわないんでは？

時刻と「休」以外の文字が入力されて困るのならExcelの関数、ＶＢＡで作成すれば対処できます。

ユーザー関数といいます。

もうひとつは　入力規則です。

■ 入力規則:http://officetanaka.net/excel/function/tips/tips05.htm
(入力規則のリストを使う)

まず、出勤について　時刻と分の部分の２つのセルに分けます。

「時」のセルには　入力規則のリストで　「休」と　00,01・・・・・24 とします。
「分」のセルには　入力規則のリストで　00,01・・・・・59 とします。

これで対応できるはずです。

=============
>>せっかく頂いたものですのでサンプルも試そうとSQL2008を入れてなかったのでインストールして試そ>>うと頑張ったのですが、どうしてもmanagement studioがエラーでうまくインストールされず仕方なく>>2005をインストールして試したのですが・・・今度は接続がうまくいかずできませんでした・・・初心>>者なので知識不足のようです・

これについては　自習書などで　学んでもらうほかありませんので・・・

===============

今回は　ＤＢは　Access 2007　などですか　ＳＱＬ　SERVER　ではないのですか？
ＡＣＣＥＳＳバージョンにするのも　そんなに難しくはないので・・・

■ SQL Server 2008 自習書シリーズ
http://www.microsoft.com/japan/sqlserver/2008/self-learning/default.mspx

入門編で　勉強してください。

回答No.8

NOBNNN

#1　です。
　＃７さんの書いている

>>これって、エクセルの問題なのでは、？？？
>>SQL関係ないと思うのですが。

>>文字があると、エクセルのカラムBが、多分文字列になっているのですよね。
>>なら、カラムBのセルの書式設定を、データを読み込んだ後に
>>時刻に、再設定し見てください。

という現象は当然、折込済みです。

ちょっと、興味がありましたので実験的にプログラムを作ってみました。

添付(ダウンロード）したExcel のデータ「TEST出勤.xls」
は　すべて　書式設定は標準ではなく文字列書式にしてあります。
文字列なので、何でも入ります。　だからExcel側で入力規則などで
チェックをする必要があると思います。

まずは　ダウンロードしてお試ししてください。

機能として　Datagridview に一旦読み込んで、そこでも修正ができるようにしてあります。

なお、テストした結果　正常にＤＢ側に反映がされています。

補足

サンプル作製までしていただいてありがとうございます。
・・・ただ、説明不足で申し訳ないのですが、エクセルファイルの時刻を入力する列の書式設定は文字列でなく時刻にしたいのです。この列の書式設定をすべて文字列にしてしまうなら、特に変更なく私のソースでも問題なく更新できてしまいます・・・

せっかく頂いたものですのでサンプルも試そうとSQL2008を入れてなかったのでインストールして試そうと頑張ったのですが、どうしてもmanagement studioがエラーでうまくインストールされず仕方なく2005をインストールして試したのですが・・・今度は接続がうまくいかずできませんでした・・・初心者なので知識不足のようです・・・

私のやりたいこととしては、このエクセルファイルはタイムカードと給料計算を兼ねたもので、出勤 退勤 休憩などの入力自体はVBAのユーザーフォームで作成されていて、計算は関数とVBAで作成されています。このファイルが仕事場のPCで開かれていて、従業員に押してもらっているのですが、時刻入力の修正やうち忘れの記入を、違うPCから.NETで作成したもので操作できるようにしたいと思い作成しています。

VBAの管理用フォームで修正はできるように作ってはあるのですが、VBAを始めたばかりなのに.NET（VB2008）に興味を持ってしまい.NETで作成してみたいと思ったわけです・・・最初は、.NETでエクセルファイルを開いて修正するものを作ってうまくいったのですが、ADOなるものを発見したためこれを利用してできないものかと・・・

やはり、時刻の書式設定の列に文字列が入っているということでおかしくなってしまうのでしょうが、何とか方法はないものでしょうか？
また、入力後にそのセルの書式設定をし直すということはできるのでしょうか？（エクセルファイルを開かずに）

回答No.7

tom11

******************引用******************
文字が含まれない列の時刻は正しく更新されるのですが、
文字が含まれる列は通常は右寄せのはずが左寄せになってしまい、
表示形式は「h:mm」なのですがセルを選択すると上のバー？に出るはずの秒が出ません・・・
書式設定は一応、時刻になっているのですが・・・たぶん文字列になってしまっているような感じです。

EXCELファイルの形式は下記のようになっています。
書式設定は、A列はすべて日付、BとC列はすべて時刻になっています。
問題の「休」のセルも時刻のままです。
........A.........B........C.....
1......日付......出勤.....退勤
2....2010/1/1....7:54....18:24
3....2010/1/2....休........
4....2010/1/3....7:48....18:28
5....2010/1/4....7:56....18:33

日付と退勤の列はうまく更新できるのですが、
B列の出勤列に「休」と書かれている行があるとB列の更新がうまくいきません。
********************************************

これって、エクセルにクエリで、データベースのデータを読み込んでいるのでよね。
これって、エクセルの問題なのでは、？？？
SQL関係ないと思うのですが。

文字があると、エクセルのカラムBが、多分文字列になっているのですよね。
なら、カラムBのセルの書式設定を、データを読み込んだ後に
時刻に、再設定し見てください。

たとえば
カラムAに

7：30
休

と二つのデータを入れると、7：30は、時刻タイプと文字タイプになります。
カラムAを、強引に文字列に、書式設定すると、

0.3215
休

の二つの文字列に変化されます。

エクセルで、読み込むと、確か、最初の、何個目かで、
そのカラムの、データタイプをエクセルが、勝手に設定していると
聞いたことがあります。
休が入ったので、そのカラムが、文字列型になって、
時間も、文字に変換されている可能性があります。

対応方法としては、カラムBの時刻を、再度、書式設定で、
時間に戻しても、解決しなかったら、
ごめんなさい。

補足

回答ありがとうござあいます。

＞エクセルで、読み込むと、確か、最初の、何個目かで、そのカラムの、データタイプをエクセルが、勝手に設定していると聞いたことがあります。

やはりそうなのですか？色々試しているうちにわかったのですが、質問の例では行数が少ないですが本当はもっと下に続いていて「休」ももっとあります。で、何個か「休」を消してみると、「休」がすべてないわけではないのに、時刻の更新がうまくいく（書式が正しく時刻になる）ときもあるのです。・・・といっても、消すわけにもいかないので解決にはなりませんが・・・

＞対応方法としては、カラムBの時刻を、再度、書式設定で・・・

再度、書式設定というのはエクセルファイルを開いてということでしょうか？・・・だとすると、毎回修正（更新）の度にそのあとエクセルファイルを開いて書式設定の修正をするようですと、最初からエクセルファイルを開いてそのまま時刻修正したほうが早くなってしまいますので・・・
しかも不思議なことにエクセルファイルを開いて書式を「時刻」に再設定してもなぜか文字列のままでなおらないのです・・・

エクセルファイルを開かずに書式の再設定ができる方法があれば良いのですが・・・

回答No.6

NOBNNN

#1　です

いままで説明をしましたが大変、長いので

期間限定で　プログラムなど全データをダウンロードできるようにしておきます。

■ファイルの受取
以下URLからファイル受取サイトへアクセスして頂くことでファイルのダウン
ロードを行うことができます。
ファイルの取得期限は 2010年1月25日 3時03分06秒 JST です。

https://www.datadeliver.net/receiver/fileBox.do?fb=0ac91aa3edcf479ca1418b67f5cb8b1c&rc=9a229746481e469389b4e638ec707a4e&lang=ja

============

ZIP 形式です。　解凍を行ってください。

回答No.5

NOBNNN

サンプルプログラムでは　データセットのクラスを　プロジェクトに追加し、作成しています。

フォームのデザイン画面にて　メニューのデータ／新しいデータの接続にて　「KINTAI」　のＤＢを接続し、データソースより。　適出勤名簿の　テーブルのいづれかの項目を　配置（ドラッグ＆ドロップ）
すると自動的に　必要な項目が作成されます。(TAbleADpter BindingDatasource)

つぎに作成されたデータセットをデザイナ画面にて　新しいデータテーブルを作成します。

名前は　「出勤名簿_excel」としています。
このデーターテーブルに次の項目を追加します。

１、日付　（文字列）
２、出勤 (文字列）
３、出退 (文字列）
４、社員番号(文字列）

とします。

フォーム(Form1)のデザイナ画面を開きます。
データーソースの「出勤名簿_Excel」のプルダウンにてDatagridview にします。
そのまま　「出勤名簿_Excel」のを選択したまま　画面へドラッグ＆ドロップします。

「出勤名簿_Excel」のDatagridview が画面に配置されます。

これらの作業を先に行ってから　サンプルプログラムのソースをコピー＆ペーストしてください。

なおexcelシートの項目名が一致しないとエラーになります。
データーテーブル「出勤名簿_Excelの項目名と一致させてください。

回答No.4

NOBNNN

#3のつづき
============

Private Sub Btn_DBUPD_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btn_DBUPD.Click

Call S_DB出勤名簿_UPD()
End Sub

Private Sub S_DB出勤名簿_UPD()
'サーバーより保存データ(勤務表）を読み込む、社員番号で絞り込む
Me.出勤名簿TableAdapter.FillBy(Me.KINTAIDataSet.出勤名簿, Me.KINTAIDataSet.出勤名簿_excel.Rows(0).Item("社員番号"))

'日付順に並び替える。　検索準備

Dim Dv As DataView = New DataView(Me.KINTAIDataSet.出勤名簿, "", "日付", DataViewRowState.CurrentRows)

For I As Integer = 0 To Me.KINTAIDataSet.出勤名簿_excel.Rows.Count - 1

Dim Wrow As KINTAIDataSet.出勤名簿_excelRow = Me.KINTAIDataSet.出勤名簿_excel.Rows(I)

Dim Sidx As Integer = Dv.Find(Wrow.Item("日付").ToString) '日付で検索

If Sidx <> -1 Then 'データが存在したなら
Dv.Item(Sidx).Delete() '同じ日付のデータは上書きするため一旦削除
End If

With Dv.AddNew() '新しい行を追加する
.Item("社員番号") = Wrow.Item("社員番号").ToString
.Item("日付") = Wrow.Item("日付").ToString
.Item("出勤") = Wrow.Item("出勤").ToString
.Item("退勤") = Wrow.Item("退勤").ToString
.EndEdit()
End With

Me.出勤名簿TableAdapter.Update(Me.KINTAIDataSet.出勤名簿) 'ＤＢへ更新

Next
End Sub
End Class
========================================
サンプルはここで終わり.

◆つづく

回答No.3

NOBNNN

#2　のつづき
==================
Private Function F_Excel_Open() As String
Dim ofd As New OpenFileDialog()

'はじめのファイル名を指定する
'はじめに「ファイル名」で表示される文字列を指定する
ofd.FileName = "" '"default.Xls"
'はじめに表示されるフォルダを指定する
'指定しない（空の文字列）の時は、現在のディレクトリが表示される
ofd.InitialDirectory = "C:\"
'[ファイルの種類]に表示される選択肢を指定する
'指定しないとすべてのファイルが表示される
ofd.Filter = _
"Excelファイル(*.Xls)|*.Xls|すべてのファイル(*.*)|*.*"
'[ファイルの種類]ではじめに
'「Excelファイル」が選択されているようにする
ofd.FilterIndex = 1
'タイトルを設定する
ofd.Title = "開くファイル(Excel)を選択してください"
'ダイアログボックスを閉じる前に現在のディレクトリを復元するようにする
ofd.RestoreDirectory = True
'存在しないファイルの名前が指定されたとき警告を表示する
'デフォルトでTrueなので指定する必要はない
ofd.CheckFileExists = True
'存在しないパスが指定されたとき警告を表示する
'デフォルトでTrueなので指定する必要はない
ofd.CheckPathExists = True

'ダイアログを表示する
If ofd.ShowDialog() = DialogResult.OK Then
'OKボタンがクリックされたとき
'選択されたファイル名を返す
Return ofd.FileName
Else
Return Nothing
End If

End Function
==============

つづく

回答No.2

NOBNNN

#1　のつづき　以下はサンプルプログラムのソース
============

Public Class Form1

Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

End Sub

Private Sub 出勤名簿BindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 出勤名簿BindingNavigatorSaveItem.Click
Me.Validate()
Me.出勤名簿BindingSource.EndEdit()

Call S_DB出勤名簿_UPD()

End Sub

Private Sub btn_Excel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_Excel.Click

Dim con As New OleDb.OleDbConnection()
Dim cmd As New OleDb.OleDbCommand()
Dim da As New OleDb.OleDbDataAdapter()

Dim strXL As String = F_Excel_Open() 'Excel ファイルのダイアログにて開くファイルを指定

If strXL = Nothing Then
Exit Sub
End If

' DB接続文字列の設定
con.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=" & _
strXL & ";" & _
"Extended Properties=""Excel 8.0;HDR=YES;"""

' コネクションの設定
cmd.Connection = con

' SQL文の設定
' シート名は、デフォルトの"Sheet1"のまま使っています. 項目名はExcelシートの項目名（ヘッダー）です。
cmd.CommandText = "SELECT FORMAT(TB.日付,'yyyy/mm/dd') as 日付,出勤,退勤,社員番号 FROM [Sheet1$] as TB"

' SELECTコマンドの設定
da.SelectCommand = cmd

' SQL文パラメータの設定
'cmd.Parameters.Add("@bango", OleDbType.Char, 8)
'cmd.Parameters("@bango").Value = _
' Me.TxtYubinBango.Text

' データを取得する
Try

da.Fill(Me.KINTAIDataSet.出勤名簿_excel)

Catch except As System.Exception

MsgBox("Excelファイル（" & strXL & ")の読み込みのエラー" & vbCrLf & vbCrLf & _
"(詳細)" & vbCrLf & _
except.Message, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, Me.Name)

End Try

End Sub
===========
つづく