本人用VB.NET 编写用SQL语句，导入Excel表格

#Region "导出Excel表格记录集合 At 2015.5.19 0:18"
    Public Function bExportEXCEL(ByVal strSQL As String) As Boolean
        If strSQL = "" Then '如果入口为空则退出
            bExportEXCEL = False
            Exit Function
        End If
        Dim AdapterTmp As New SqlDataAdapter(strSQL, SQLConn)
        Dim DsResults As New DataSet
        AdapterTmp.Fill(DsResults, "Results")
        If DsResults.Tables("Results").Rows.Count > 0 Then
            Dim MyExceL As New Excel.Application
            MyExceL.Visible = True
            MyExceL.Workbooks.Add()
            Dim Col As Integer = 0
            Dim Row As Integer = 0
            MyExceL.Worksheets("sheet1").activate()

            With DsResults.Tables("Results")
                For i = 0 To .Columns.Count - 1
                    MyExceL.Cells(1, i + 1).value = .Columns(i).ColumnName.ToString '表头
                Next
                Do While Row <= .Rows.Count - 1
                    For Col = 0 To .Columns.Count - 1
                        If Len(.Rows(Row)(Col).ToString) > 15 Then
                            '身份证列保证为文本　2015.08.16 修正
                            MyExceL.Cells(Row + 2, Col + 1).NumberFormatLocal = "@ " '默认文本格式存放
                        End If
                        MyExceL.Cells(Row + 2, Col + 1).value = .Rows(Row)(Col).ToString
                    Next
                    Row += 1
                Loop
            End With
            MyExceL.ActiveSheet.columns.autofit()
        End If
        AdapterTmp.Dispose()
        AdapterTmp = Nothing
        DsResults.Dispose()
        DsResults = Nothing
        bExportEXCEL = True
    End Function
    Public Sub ExportEXCEL(ByVal strSQL As String) '2017年7月2日，于上海新增
        Try
            If strSQL = "" Then '如果入口为空则退出
                Exit Sub
            End If
            Dim AdapterTmp As New SqlDataAdapter(strSQL, SQLConn)
            Dim DsResults As New DataSet
            AdapterTmp.Fill(DsResults, "Results")
            If DsResults.Tables("Results").Rows.Count > 0 Then
                Dim MyExceL As New Excel.Application
                MyExceL.Visible = True
                MyExceL.Workbooks.Add()
                Dim Col As Integer = 0
                Dim Row As Integer = 0
                MyExceL.Worksheets("sheet1").activate()
                With DsResults.Tables("Results")
                    For i = 0 To .Columns.Count - 1
                        MyExceL.Cells(1, i + 1).value = .Columns(i).ColumnName.ToString '表头
                    Next
                    Do While Row <= .Rows.Count - 1
                        For Col = 0 To .Columns.Count - 1
                            If Len(.Rows(Row)(Col).ToString) > 15 Then
                                '身份证列保证为文本　2015.08.16 修正
                                MyExceL.Cells(Row + 2, Col + 1).NumberFormatLocal = "@ " '默认文本格式存放
                            End If
                            MyExceL.Cells(Row + 2, Col + 1).value = .Rows(Row)(Col).ToString
                        Next
                        Row += 1
                    Loop
                End With
                MyExceL.ActiveSheet.columns.autofit()
            End If
            AdapterTmp.Dispose()
            AdapterTmp = Nothing
            DsResults.Dispose()
            DsResults = Nothing
        Catch ex As Exception
            MessageBox.Show(Err.Description.ToString, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error) '出错提示
        End Try
    End Sub
#End Region

本人用VB.NET 编写用SQL语句，导入Excel表格。直接复制即可应用，测试通过。

本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。