Option ExplicitDim Tbl As Workbook ' workbook
Dim PthNm As String ' file pass and file name
Dim Nm As String ' file name
Dim DtAry(1 To 53) As String ' workbook 的列
Dim cn As New ADODB.Connection ' new connection
Dim rs As New ADODB.Recordset ' new recordsent
Dim stoCD As Integer
Dim stoNm As String'database'connection
Public Sub CnDB()
cn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=develop; password=develop;Data Source=172.18.1.2;DATABASE=OTBSystem"End Sub
'数据的有效列数
Public Function CntCells() As Integer Dim CntCell As Integer
CntCell = 1
Do Until Tbl.Worksheets(1).Cells(12, CntCell) = "" And Tbl.Worksheets(1).Cells(12, CntCell + 1) = ""
CntCell = CntCell + 1
Loop
CntCells = CntCellEnd Function'return now'time :year&month
Public Function YrMth() As Long If Val(Mid$(Tbl.Worksheets(1).Range("U2"), 2, 2)) < 10 Then
YrMth = (Mid$(Tbl.Worksheets(1).Range("O2"), 1, 4)) & "0" & (Mid$(Tbl.Worksheets(1).Range("U2"), 2, 1))
Else
YrMth = (Mid$(Tbl.Worksheets(1).Range("O2"), 1, 4)) & (Mid$(Tbl.Worksheets(1).Range("U2"), 2, 2))
End IfEnd Function'data 的插入
Public Sub IstDt1(Sht, Row, Cln) Dim strSQL As String
Dim Flg As Integer
strSQL = "INSERT INTO F_MONTH_OTB VALUES (" & YrMth & ", " & stoCD & ", '" & stoNm & "'"
For Flg = 1 To Cln - 1
If Flg = 2 Or Flg = 50 Then
ElseIf Flg = 3 Then
DtAry(Flg) = Tbl.Worksheets(Sht).Cells(Row, Flg)
strSQL = strSQL & ", '" & DtAry(Flg) & "'"
Else
DtAry(Flg) = Tbl.Worksheets(Sht).Cells(Row, Flg)
If DtAry(Flg) = "" Then
DtAry(Flg) = 0
End If
strSQL = strSQL & ", " & DtAry(Flg)
End If
Next Flg
strSQL = strSQL & ")"
CnDB
cn.Execute strSQL
cn.CloseEnd Sub'data 的插入
Public Sub IstDt2(Sht, Row, Cln) Dim strSQL As String
Dim Flg As Integer
strSQL = "INSERT INTO F_MONTH_OTB VALUES (" & YrMth & ", " & stoCD & ", '" & stoNm & "'"
For Flg = 1 To Cln - 1
If Flg = 2 Or Flg >= 50 Then
ElseIf Flg = 3 Then
DtAry(Flg) = Tbl.Worksheets(Sht).Cells(Row, Flg)
strSQL = strSQL & ", '" & DtAry(Flg) & "'"
Else
DtAry(Flg) = Tbl.Worksheets(Sht).Cells(Row, Flg)
If DtAry(Flg) = "" Then
DtAry(Flg) = 0
End If
strSQL = strSQL & ", " & DtAry(Flg)
End If
Next Flg
strSQL = strSQL & ", 0, 0, 0)"
Call CnDB
cn.Execute strSQL
cn.CloseEnd Sub'seaching datasource : excel 文件
Private Sub cmdView_Click() With dlgView
.FileName = "*.xls"
.InitDir = "C:\Documents and Settings\中华\My Documents\a\b\月OTB": 'excel 文件路径
'.Action = 1
.Filter = "エクセルファイル(*.xls)|(*.xls)"
.ShowOpen
End With
PthNm = dlgView.FileName
Nm = dlgView.FileTitle
txtPthNm.Text = NmEnd Sub
'menu de chu li Private Sub cmdInput_Click()
On Error GoTo Err_Handler Dim i As Integer '行No.
Dim j As Integer 'worksheet's NO. 表
Dim cnt As Integer
Dim tm As Integer 'time
Dim ret As Integer '返回MessageBox
Dim strSQL As String 'DB処理用
If Me.txtPthNm.Text = "" Then
ret = MsgBox("取参照", vbInformation + vbOKOnly, "参照") 'MessageBox 提示
Exit Sub
End If
Application.Workbooks.Open PthNm
Set Tbl = Application.Workbooks(Nm) 'Tbl'seting :将数据代入 which workbook
i = 6
j = 1
' progressbar初始化
Do Until Tbl.Worksheets(j).Name = "全店" '全店
j = j + 1
Loop
With Me.prbProcess
.Min = 0
.Max = j
.Value = 0
End With
j = 1
'去除重的数据
strSQL = "DELETE FROM F_MONTH_OTB WHERE 年月 =" & YrMth Call CnDB cn.Execute strSQL
cn.Close cnt = CntCells
Do Until Tbl.Worksheets(j).Name = "全店" '全店
Me.lblMessage.Caption = Tbl.Worksheets(j).Name & "店処理。"
Me.lblMessage.Refresh
Me.prbProcess.Value = j
stoCD = Tbl.Worksheets(j).Cells(2, 1)
stoNm = Tbl.Worksheets(j).Name
'SLS型的chu li
If Left(Tbl.Worksheets(j).Name, 3) = "SLS" Or Left(Tbl.Worksheets(j).Name, 3) = "SLS" Then
Do Until Tbl.Worksheets(j).Cells(i, 1) = "" And Tbl.Worksheets(j).Cells(i + 1, 1) = "" If Val(Tbl.Worksheets(j).Cells(i, 1)) > 0 And Val(Tbl.Worksheets(j).Cells(i, 1)) < 1000 Then
Call IstDt2(j, i, cnt)
End If i = i + 1 Loop
'通常的店的 chu li
Else Do Until Tbl.Worksheets(j).Cells(i, 1) = "" And Tbl.Worksheets(j).Cells(i + 1, 1) = "" And i <> 136 If (Val(Tbl.Worksheets(j).Cells(i, 1)) > 0 And Val(Tbl.Worksheets(j).Cells(i, 1)) < 1000) Or i = 136 Then If i = 101 Then
i = 131
Call IstDt1(j, i, cnt)
ElseIf i = 136 Then
i = 107
Call IstDt1(j, i, cnt)
Else
Call IstDt1(j, i, cnt)
End If End If i = i + 1 Loop
End If
j = j + 1
i = 6
Loop
strSQL = "UPDATE F_MONTH_OTB SET 店舗CD = 990 WHERE 店舗CD = 90 UPDATE F_MONTH_OTB SET 店舗CD = 993 WHERE 店舗CD = 93 UPDATE F_MONTH_OTB SET 店舗CD = 994 WHERE 店舗CD = 94 UPDATE F_MONTH_OTB SET 店舗CD = 996 WHERE 店舗CD = 96 UPDATE F_MONTH_OTB SET 店舗CD = 997 WHERE 店舗CD = 97 UPDATE F_MONTH_OTB SET 店舗CD = 998 WHERE 店舗CD = 98 DELETE FROM F_MONTH_OTB WHERE 部門名 = '生鮮ディビジョン'" Call CnDB
cn.Execute strSQL
cn.Close lblMessage.Caption = "無事" ' 提示信息
txtPthNm.Text = ""
ret = MsgBox("終わりにゃー、今入れたのは" & YrMth & "のデータにゃー", vbInformation + vbOKOnly, "(=^..^=)")
' Tbl.Save
Tbl.Close
Application.Quit
Exit SubErr_Handler: 'error 的 chu li
ret = MsgBox(Err.Number & ", " & Err.Description, vbInformation + vbOKOnly, "エラー")
Tbl.Close
Exit SubEnd SubPrivate Sub cmdExit_Click() Unload MeEnd SubPrivate Sub txtPthNm_OLEDragOver(Data As DataObject, Effect As Long, Button As Integer, Shift As Integer, X As Single, Y As Single, State As Integer) Dim FileNm As String
FileNm = Data.Files.Item(1)
Me.txtPthNm.Text = FileNmEnd Sub
Dim PthNm As String ' file pass and file name
Dim Nm As String ' file name
Dim DtAry(1 To 53) As String ' workbook 的列
Dim cn As New ADODB.Connection ' new connection
Dim rs As New ADODB.Recordset ' new recordsent
Dim stoCD As Integer
Dim stoNm As String'database'connection
Public Sub CnDB()
cn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=develop; password=develop;Data Source=172.18.1.2;DATABASE=OTBSystem"End Sub
'数据的有效列数
Public Function CntCells() As Integer Dim CntCell As Integer
CntCell = 1
Do Until Tbl.Worksheets(1).Cells(12, CntCell) = "" And Tbl.Worksheets(1).Cells(12, CntCell + 1) = ""
CntCell = CntCell + 1
Loop
CntCells = CntCellEnd Function'return now'time :year&month
Public Function YrMth() As Long If Val(Mid$(Tbl.Worksheets(1).Range("U2"), 2, 2)) < 10 Then
YrMth = (Mid$(Tbl.Worksheets(1).Range("O2"), 1, 4)) & "0" & (Mid$(Tbl.Worksheets(1).Range("U2"), 2, 1))
Else
YrMth = (Mid$(Tbl.Worksheets(1).Range("O2"), 1, 4)) & (Mid$(Tbl.Worksheets(1).Range("U2"), 2, 2))
End IfEnd Function'data 的插入
Public Sub IstDt1(Sht, Row, Cln) Dim strSQL As String
Dim Flg As Integer
strSQL = "INSERT INTO F_MONTH_OTB VALUES (" & YrMth & ", " & stoCD & ", '" & stoNm & "'"
For Flg = 1 To Cln - 1
If Flg = 2 Or Flg = 50 Then
ElseIf Flg = 3 Then
DtAry(Flg) = Tbl.Worksheets(Sht).Cells(Row, Flg)
strSQL = strSQL & ", '" & DtAry(Flg) & "'"
Else
DtAry(Flg) = Tbl.Worksheets(Sht).Cells(Row, Flg)
If DtAry(Flg) = "" Then
DtAry(Flg) = 0
End If
strSQL = strSQL & ", " & DtAry(Flg)
End If
Next Flg
strSQL = strSQL & ")"
CnDB
cn.Execute strSQL
cn.CloseEnd Sub'data 的插入
Public Sub IstDt2(Sht, Row, Cln) Dim strSQL As String
Dim Flg As Integer
strSQL = "INSERT INTO F_MONTH_OTB VALUES (" & YrMth & ", " & stoCD & ", '" & stoNm & "'"
For Flg = 1 To Cln - 1
If Flg = 2 Or Flg >= 50 Then
ElseIf Flg = 3 Then
DtAry(Flg) = Tbl.Worksheets(Sht).Cells(Row, Flg)
strSQL = strSQL & ", '" & DtAry(Flg) & "'"
Else
DtAry(Flg) = Tbl.Worksheets(Sht).Cells(Row, Flg)
If DtAry(Flg) = "" Then
DtAry(Flg) = 0
End If
strSQL = strSQL & ", " & DtAry(Flg)
End If
Next Flg
strSQL = strSQL & ", 0, 0, 0)"
Call CnDB
cn.Execute strSQL
cn.CloseEnd Sub'seaching datasource : excel 文件
Private Sub cmdView_Click() With dlgView
.FileName = "*.xls"
.InitDir = "C:\Documents and Settings\中华\My Documents\a\b\月OTB": 'excel 文件路径
'.Action = 1
.Filter = "エクセルファイル(*.xls)|(*.xls)"
.ShowOpen
End With
PthNm = dlgView.FileName
Nm = dlgView.FileTitle
txtPthNm.Text = NmEnd Sub
'menu de chu li Private Sub cmdInput_Click()
On Error GoTo Err_Handler Dim i As Integer '行No.
Dim j As Integer 'worksheet's NO. 表
Dim cnt As Integer
Dim tm As Integer 'time
Dim ret As Integer '返回MessageBox
Dim strSQL As String 'DB処理用
If Me.txtPthNm.Text = "" Then
ret = MsgBox("取参照", vbInformation + vbOKOnly, "参照") 'MessageBox 提示
Exit Sub
End If
Application.Workbooks.Open PthNm
Set Tbl = Application.Workbooks(Nm) 'Tbl'seting :将数据代入 which workbook
i = 6
j = 1
' progressbar初始化
Do Until Tbl.Worksheets(j).Name = "全店" '全店
j = j + 1
Loop
With Me.prbProcess
.Min = 0
.Max = j
.Value = 0
End With
j = 1
'去除重的数据
strSQL = "DELETE FROM F_MONTH_OTB WHERE 年月 =" & YrMth Call CnDB cn.Execute strSQL
cn.Close cnt = CntCells
Do Until Tbl.Worksheets(j).Name = "全店" '全店
Me.lblMessage.Caption = Tbl.Worksheets(j).Name & "店処理。"
Me.lblMessage.Refresh
Me.prbProcess.Value = j
stoCD = Tbl.Worksheets(j).Cells(2, 1)
stoNm = Tbl.Worksheets(j).Name
'SLS型的chu li
If Left(Tbl.Worksheets(j).Name, 3) = "SLS" Or Left(Tbl.Worksheets(j).Name, 3) = "SLS" Then
Do Until Tbl.Worksheets(j).Cells(i, 1) = "" And Tbl.Worksheets(j).Cells(i + 1, 1) = "" If Val(Tbl.Worksheets(j).Cells(i, 1)) > 0 And Val(Tbl.Worksheets(j).Cells(i, 1)) < 1000 Then
Call IstDt2(j, i, cnt)
End If i = i + 1 Loop
'通常的店的 chu li
Else Do Until Tbl.Worksheets(j).Cells(i, 1) = "" And Tbl.Worksheets(j).Cells(i + 1, 1) = "" And i <> 136 If (Val(Tbl.Worksheets(j).Cells(i, 1)) > 0 And Val(Tbl.Worksheets(j).Cells(i, 1)) < 1000) Or i = 136 Then If i = 101 Then
i = 131
Call IstDt1(j, i, cnt)
ElseIf i = 136 Then
i = 107
Call IstDt1(j, i, cnt)
Else
Call IstDt1(j, i, cnt)
End If End If i = i + 1 Loop
End If
j = j + 1
i = 6
Loop
strSQL = "UPDATE F_MONTH_OTB SET 店舗CD = 990 WHERE 店舗CD = 90 UPDATE F_MONTH_OTB SET 店舗CD = 993 WHERE 店舗CD = 93 UPDATE F_MONTH_OTB SET 店舗CD = 994 WHERE 店舗CD = 94 UPDATE F_MONTH_OTB SET 店舗CD = 996 WHERE 店舗CD = 96 UPDATE F_MONTH_OTB SET 店舗CD = 997 WHERE 店舗CD = 97 UPDATE F_MONTH_OTB SET 店舗CD = 998 WHERE 店舗CD = 98 DELETE FROM F_MONTH_OTB WHERE 部門名 = '生鮮ディビジョン'" Call CnDB
cn.Execute strSQL
cn.Close lblMessage.Caption = "無事" ' 提示信息
txtPthNm.Text = ""
ret = MsgBox("終わりにゃー、今入れたのは" & YrMth & "のデータにゃー", vbInformation + vbOKOnly, "(=^..^=)")
' Tbl.Save
Tbl.Close
Application.Quit
Exit SubErr_Handler: 'error 的 chu li
ret = MsgBox(Err.Number & ", " & Err.Description, vbInformation + vbOKOnly, "エラー")
Tbl.Close
Exit SubEnd SubPrivate Sub cmdExit_Click() Unload MeEnd SubPrivate Sub txtPthNm_OLEDragOver(Data As DataObject, Effect As Long, Button As Integer, Shift As Integer, X As Single, Y As Single, State As Integer) Dim FileNm As String
FileNm = Data.Files.Item(1)
Me.txtPthNm.Text = FileNmEnd Sub
解决方案 »
- 关于进制间的转换,求助。!!!!!!!!!
- 用c#编写.NET的时候,怎样禁用F5刷新以及后退之类的快捷键啊?
- webbrowser中能随心所欲修改显示内容吗?
- String 和 StringBuilder 的区别和使用环境?
- 高手解答:"目标机器积极拒绝,无法连接"
- webform 关于DropDownList的问题?
- 搜索internet各个网站的信息,请问是怎样的一个原理?
- C#串口通讯为什么老是显示找不到串口,串口检测时可以找到串口,打开串口传数据就显示找不到了,怎么回事啊?
- C# Winform中编写自动关机程序无效果
- 请问如何列出本机所有的共享目录?(不管权限如何设置的,只要共享的都要列)
- TextBox(RichTextBox)如何实现"单击"、"双击"等事件都是一直选中文本,最重要的是不要闪烁
- 未处理的“System.Data.OleDb.OleDbException”类型的异常出现在 system.data.dll 中。
把Interop.Excel加上,然后:
Excel.Application application = new Excel.Application();
Excel.Workbook workbook = application.Workbooks.Open("d:1.xls", Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
Excel.Worksheet workSheet = (Excel.Worksheet)workbook.Sheets[0];if ((Excel.Range)workSheet.Cells[6, iColIndex] != null)
{
string strvalue = (Excel.Range)workSheet.Cells[6, iColIndex].ToString();
}
Microsoft.Office.Interop.Excel.Application ap = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook ew = ap.Workbooks.Add(m_obj);
Microsoft.Office.Interop.Excel.Sheets sheets = (Microsoft.Office.Interop.Excel.Sheets)ew.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet es = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);
es.Cells[1, 1] = "aaaaa";
es.get_Range(es.Cells[1, 1], es.Cells[1, 2]).MergeCells = true;
ap.Visible = true;