VB6操作EXCEL导入数据库

Private Function FunImpExcel(ByVal strFilePath As String) As Integer 'Excel<a href="https://www.f2er.com/tag/wenjian/" target="_blank" class="keywords">文件</a>格式 '第一行为表名，第二行为列名，其余行均为数据 On Error GoTo hErr Dim objConn As New ADODB.Connection Dim objRS As New ADODB.Recordset If Dir(strFilePath) = "" Then Msg<a href="https://www.f2er.com/tag/Box/" target="_blank" class="keywords">Box</a> "<a href="https://www.f2er.com/tag/wenjian/" target="_blank" class="keywords">文件</a>不存在",vbCritical,"<a href="https://www.f2er.com/tag/cuowu/" target="_blank" class="keywords">错误</a>" Exit Function End If '定义Excel对象 Dim xlsApp As Object Dim xlsWb As Object Dim xlsWs As Object Set xlsApp = CreateObject("Excel.Application") '建立excel对象 Set xlsWb = xlsApp.Workbooks.Open(strFilePath) '要打开的文档路径 Set xlsWs = xlsWb.Worksheets(1) '选工作表，有多张表时，可以参考此，变换序号指定不同的表 xlsWs.Activate xlsApp.Visible = false '隐藏，否则会在界面<a href="https://www.f2er.com/tag/xianshi/" target="_blank" class="keywords">显示</a>出来 'Excel表格的行数和列数 Dim iRowCnt As Integer Dim iColCnt As Integer iRowCnt = xlsWs.UsedRange.Rows.Count '这个并不完全准确，在操作数据时要设置<a href="https://www.f2er.com/tag/tuichu/" target="_blank" class="keywords">退出</a>条件 iColCnt = xlsWs.UsedRange.Columns.Count'这个并不完全准确，在操作数据时要设置<a href="https://www.f2er.com/tag/tuichu/" target="_blank" class="keywords">退出</a>条件 '下面要根据具体的表格情况决定，这里前面两行是表名和列名 If iRowCnt <= 2 Then Msg<a href="https://www.f2er.com/tag/Box/" target="_blank" class="keywords">Box</a> "没有需要导入的明细数据","<a href="https://www.f2er.com/tag/cuowu/" target="_blank" class="keywords">错误</a>" GoTo hErr End If '从第3行开始是明细数据 For i = 3 To iRowCnt '设置<a href="https://www.f2er.com/tag/tuichu/" target="_blank" class="keywords">退出</a>条件 If Trim$(xlsWs.Cells(i,3).Value) = "" Then mdlPub.debug_print "on date found anymore:" & i Exit For End If '第一条数据时，先打开<a href="https://www.f2er.com/tag/shujuku/" target="_blank" class="keywords">数据库</a>，这里是access if 3 = i then '<a href="https://www.f2er.com/tag/shujuku/" target="_blank" class="keywords">数据库</a>访问操作可以封装成一个公共的<a href="https://www.f2er.com/tag/hanshu/" target="_blank" class="keywords">函数</a>或过程 Dim strConn as String strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=true;Data Source=test.mdb" objConn.CursorLocation = adUseClient objConn.Open strConn str<a href="https://www.f2er.com/tag/sql/" target="_blank" class="keywords">sql</a> = "select * from [要导入的表名] where 1=2 " objRS.CursorLocation = adUseClient objRS.Open str<a href="https://www.f2er.com/tag/sql/" target="_blank" class="keywords">sql</a>,objConn,adOpenKeyset,adLockOptimistic End if '新增一条记录，注意各个字段的数据类型匹配问题， '最好全部统一先转化为字符串，再转化为对应的类型 objRS.AddNew objRS.Fields("<a href="https://www.f2er.com/tag/shujuku/" target="_blank" class="keywords">数据库</a>列名１") = Trim(CStr(xlsWs.Cells(i,1).Value)) objRS.Fields("<a href="https://www.f2er.com/tag/shujuku/" target="_blank" class="keywords">数据库</a>列名2") = Trim(CStr(xlsWs.Cells(i,2).Value)) '..... objRS.Fields("<a href="https://www.f2er.com/tag/shujuku/" target="_blank" class="keywords">数据库</a>列名n") = CLng(Trim(CStr(xlsWs.Cells(i,n).Value))) '如果Excel列名与要导入的<a href="https://www.f2er.com/tag/shujuku/" target="_blank" class="keywords">数据库</a>列能按顺序一一对应， '则可以按以下方式,但要<a href="https://www.f2er.com/tag/jiejue/" target="_blank" class="keywords">解决</a>不同字段的数据格式匹配问题，比较麻烦 'For j = 0 To RS.Fields.Count - 1 ' RS.Fields(j) = Trim(CStr(xlsWs.Cells(i,1).Value)) 'Next '更新到<a href="https://www.f2er.com/tag/shujuku/" target="_blank" class="keywords">数据库</a> objRS.Update Next i objRS.Close objConn.Close Set objRS = Nothing Set objConn = Nothing xlsWb.Close '<a href="https://www.f2er.com/tag/guanbi/" target="_blank" class="keywords">关闭</a>excel<a href="https://www.f2er.com/tag/wenjian/" target="_blank" class="keywords">文件</a> xlsApp.Quit '<a href="https://www.f2er.com/tag/tuichu/" target="_blank" class="keywords">退出</a>excel Set xlsWs = Nothing Set xlsWb = Nothing Set xlsApp = Nothing FunImpExcel = 0'成功则返回０ Exit Function hErr: ImpExcelCertDtl = -１　'失败则返回１ If Not (xlsWb Is Nothing) Then xlsWb.Close '<a href="https://www.f2er.com/tag/guanbi/" target="_blank" class="keywords">关闭</a><a href="https://www.f2er.com/tag/wenjian/" target="_blank" class="keywords">文件</a> If Not (xlsApp Is Nothing) Then xlsApp.Quit Set xlsWs = Nothing Set xlsWb = Nothing Set xlsApp = Nothing Msg<a href="https://www.f2er.com/tag/Box/" target="_blank" class="keywords">Box</a> "<a href="https://www.f2er.com/tag/wenjian/" target="_blank" class="keywords">文件</a>导入失败","<a href="https://www.f2er.com/tag/cuowu/" target="_blank" class="keywords">错误</a>" End Function

对于一个Excel文件中多个表格的情况，可以循环逐一导入。

为了方便，对于excel对象的定义可以明确一些，这样能自动弹出提示，方便编码。

如：

Dim xlsApp As New Excel.Application Dim xlsWb As Excel.Workbook Dim xlsWs As Excel.Worksheet

但这样定义时需要在工程中引入excel组件。

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

将数据导出至Excel

'----------------- '从数据从<a href="https://www.f2er.com/tag/shujuku/" target="_blank" class="keywords">数据库</a>导出至excel，并弹出保存<a href="https://www.f2er.com/tag/wenjian/" target="_blank" class="keywords">文件</a>对话框 '------------------- Private Function FunExpExcel() On Error GoTo hErr '注意引用excel组件，也可以直接定义为对象object Dim xlsApp As New Excel.Application Dim xlsWb As Excel.Workbook Dim xlsWs As Excel.Worksheet Dim strFilePath As String Dim strFileNm As String Dim iColIdx As Integer Dim objTmp As Object '创建excel Set xlsApp = CreateObject("Excel.Application") xlsApp.Visible = False xlsApp.SheetsInNewWorkbook = 1 '定义表格个数 '新增一张表格， 这里可以<a href="https://www.f2er.com/tag/zengjia/" target="_blank" class="keywords">增加</a>多张表 Set xlsWb = xlsApp.Workbooks.Add '指定sheet，指定第一张，如果有多张，可以具体指定哪一个 Set xlsWs = xlsWb.Worksheets(1) 'xlsApp.Visible = False xlsWs.Activate xlsWs.Select '第一行为<a href="https://www.f2er.com/tag/biaoti/" target="_blank" class="keywords">标题</a> xlsWs.Cells(1,1).Value = "表格<a href="https://www.f2er.com/tag/biaoti/" target="_blank" class="keywords">标题</a>" '第二行为列名,第一列列名“序号” xlsWs.Cells(2,1).Value = "序号" .... xlsWs.Cells(2,n).Value = "序号" '如果是datagrid，可以直接用对应的列名 'For iColIdx = 0 To Me.grdQryInst.Columns.Count - 1 ' xlsWs.Cells(2,iColIdx + 2).Value = Me.datagrid1.Columns(iColIdx).Caption 'Next '设置第一列序号为数字格式 xlsWs.Columns("A:A").NumberFormatLocal = "0_ " '设置其它列为文本格式,<a href="https://www.f2er.com/tag/hanshu/" target="_blank" class="keywords">函数</a>Num<a href="https://www.f2er.com/tag/toch/" target="_blank" class="keywords">toch</a>ar26能将数字转化为对应的excel列名，如2->B,3->C,自已实现 'xlsWs.Columns(Num<a href="https://www.f2er.com/tag/toch/" target="_blank" class="keywords">toch</a>ar26(2) & ":" & Num<a href="https://www.f2er.com/tag/toch/" target="_blank" class="keywords">toch</a>ar26(Me.datagrid1.Columns.Count)).NumberFormatLocal = "@" '----这里打开<a href="https://www.f2er.com/tag/shujuku/" target="_blank" class="keywords">数据库</a>，<a href="https://www.f2er.com/tag/chaxun/" target="_blank" class="keywords">查询</a>数据略，自己实现，如果是datagrid,则可以按下面的<a href="https://www.f2er.com/tag/fangfa/" target="_blank" class="keywords">方法</a> 'Dim RS As ADODB.Recordset 'Set RS = Me.datagrid1.DataSource '从第三行开始写明细数据 RS.MoveFirst For iRowIdx = 0 To RS.RecordCount - 1 xlsWs.Cells(iRowIdx + 3,1).Value = CStr(iRowIdx + 1) '对第一行，按顺序逐列写单元格 For iColIdx = 0 To RS.Fields.Count - 1 xlsWs.Cells(iRowIdx + 3,iColIdx + 2).Value = RS.Fields(iColIdx).Value Next RS.MoveNext Next '-----写完数据，下面设置导出excel格式　 '<a href="https://www.f2er.com/tag/biaoti/" target="_blank" class="keywords">标题</a>格式设置 Set objTmp = xlsWs.Range(xlsWs.Cells(1,1),xlsWs.Cells(1,iColIdx + 2 - 1)) objTmp.Merge　'合并单元格 '<a href="https://www.f2er.com/tag/biaoti/" target="_blank" class="keywords">标题</a>排版 With objTmp .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter End With With objTmp.Font .Name = "宋体" .Size = 18 End With '第2行开始，设置边框，字体与<a href="https://www.f2er.com/tag/biaoti/" target="_blank" class="keywords">标题</a>不同 Set objTmp = xlsApp.Range(xlsWs.Cells(2,xlsWs.Cells(iRowIdx + 3 - 1,iColIdx + 2 - 1)) With objTmp.Font .Name = "宋体" .Size = 10 .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With objTmp.Borders(xlDiagonalDown).LineStyle = xlNone objTmp.Borders(xlDiagonalUp).LineStyle = xlNone With objTmp.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With objTmp.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With objTmp.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With objTmp.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With objTmp.Borders(xlInsideVertical) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With objTmp.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With '设置列宽，<a href="https://www.f2er.com/tag/zidong/" target="_blank" class="keywords">自动</a>扩展 For iColIdx = 1 To Me.grdQryInst.Columns.Count + 1 xlsWs.Columns(Num<a href="https://www.f2er.com/tag/toch/" target="_blank" class="keywords">toch</a>ar26(iColIdx) & ":" &　Num<a href="https://www.f2er.com/tag/toch/" target="_blank" class="keywords">toch</a>ar26(iColIdx)).EntireColumn.AutoFit Next '弹出保存<a href="https://www.f2er.com/tag/wenjian/" target="_blank" class="keywords">文件</a>对话框，要在窗体上<a href="https://www.f2er.com/tag/zengjia/" target="_blank" class="keywords">增加</a>commondialog控件，控件命名dlgFile Me.dlgFile.DialogTitle = "保存至" Me.dlgFile.Flags = &H200 Me.dlgFile.DefaultExt = ".xls" Me.dlgFile.Filter = "Excel数据<a href="https://www.f2er.com/tag/wenjian/" target="_blank" class="keywords">文件</a> *.xls|*.xls" '过滤器 Me.dlgFile.InitDir = App.Path Me.dlgFile.FileName = strFileNm & ".xls" Me.dlgFile.ShowSave If Err <> 32755 Then strFilePath = dlgFile.FileName If "" <> strFilePath Then xlsWb.SaveAs strFilePath Else mdlPub.ShowInfo "<a href="https://www.f2er.com/tag/wenjian/" target="_blank" class="keywords">文件</a>未保存" End If xlsWb.Close xlsApp.Quit Set xlsWs = Nothing Set xlsWb = Nothing Set xlsApp = Nothing FunExpExcel = 0 '成功则返回0 mdlPub.ShowInfo "已保存至" & strFilePath Exit Sub hErr: FunExpExcel = -1'失败则返回1 If Err.Number <> 0 Then mdlPub.ShowErrMsg "导出错" If Not (xlsWb Is Nothing) Then Set xlsWs = Nothing If Not (xlsWb Is Nothing) Then xlsWb.Close Set xlsWb = Nothing End If If Not (xlsWb Is Nothing) Then xlsApp.Quit Set xlsApp = Nothing End If End Function

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

h注意，在使用VB操作excel过程中，对于excel对象的引用都要用到本地定义的excel三个变量xlsApp，xlsWb，xlsWs之一做前缀，否则， 会出现残留EXCEL进程的情况，下次操作EXCEL时会报错。原因是没有加本地定义的变量做前缀，而使用了EXCEl的全局变量形式，xlsWb.Close，xlsApp.Quit语句只是退出局部EXCEL，无法退出全局EXCEL。