| 网站首页 | 业界新闻 | 小组 | 威客 | 人才 | 下载频道 | 博客 | 代码贴 | 在线编程 | 编程论坛
欢迎加入我们,一同切磋技术
用户名:   
 
密 码:  
共有 2002 人关注过本帖
标题:想做一个报表,从SQL抓数据,但是怎么想要到固定时间段的数据,现在抓出来的 ...
只看楼主 加入收藏
ivonsxdt123
Rank: 1
等 级:新手上路
帖 子:3
专家分:0
注 册:2018-4-1
结帖率:0
收藏
已结贴  问题点数:20 回复次数:3 
想做一个报表,从SQL抓数据,但是怎么想要到固定时间段的数据,现在抓出来的都是表中全部的数据
'References that we need
Imports System.Data.SqlClient
Imports System.Data
Imports
Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop
Public Class Form1

    Private Sub ReleaseObject(ByVal o As Object)
        Try
            While (System.Runtime.InteropServices.Marshal.ReleaseComObject(o) > 0)
            End While
        Catch
        Finally
            o = Nothing
        End Try
    End Sub

    Private Sub btnBrowse_Click_1(sender As System.Object, e As System.EventArgs) Handles btnBrowse.Click
        'Initialize the objects before use
        Dim dataAdapter As New SqlClient.SqlDataAdapter()
        Dim dataSet As New DataSet
        Dim command As New SqlClient.SqlCommand
        Dim datatableMain As New System.Data.DataTable()
        Dim connection As New SqlClient.SqlConnection
        Dim a As Date
        Dim ms As Integer

        a = Now()
        'Assign your connection string to connection object
        connection.ConnectionString = "Server=.;Database=TEST;User Id=sa;Password=sa;"

        command.Connection = connection
         = CommandType.Text
        'You can use any command select
         = "SELECT  * FROM [dbo].[TEST]"
        dataAdapter.SelectCommand = command
        'Dim at As Timer

        'Me.Label1.Text = "Read from Data Base Time"a.Interval.ToString

        Dim f As FolderBrowserDialog = New FolderBrowserDialog
        Try
            If f.ShowDialog() = DialogResult.OK Then

                'This section help you if your language is not English.
                System.Threading.Thread.CurrentThread.CurrentCulture = _
                System.Globalization.CultureInfo.CreateSpecificCulture("en-US")
                Dim oExcel As Excel.Application
                Dim oBook As Excel.Workbook
                Dim oSheet As Excel.Worksheet
                oExcel = CreateObject("Excel.Application")
                oBook = oExcel.Workbooks.Add(Type.Missing)
                oSheet = oBook.Worksheets(1)


                Dim dc As System.Data.DataColumn
                Dim dr As System.Data.DataRow
                Dim colIndex As Integer = 0
                Dim rowIndex As Integer = 0

                'Fill data to datatable

                connection.Open()
                dataAdapter.Fill(datatableMain)
                connection.Close()

                DataGridView1.DataSource = datatableMain
                DataGridView1.Refresh()


                'Export the Columns to excel file
                For Each dc In datatableMain.Columns
                    colIndex = colIndex + 1
                    oSheet.Cells(1, colIndex) = dc.ColumnName
                Next

                'Export the rows to excel file
                For Each dr In datatableMain.Rows
                    rowIndex = rowIndex + 1
                    colIndex = 0
                    For Each dc In datatableMain.Columns
                        colIndex = colIndex + 1
                        oSheet.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
                    Next
                Next

                'Set final path
                Dim fileName As String = "\ExportedAuthors" + ".xls"
                Dim finalPath = f.SelectedPath + fileName
                txtPath.Text = finalPath
                oSheet.Columns.AutoFit()
                'Save file in final path
                oBook.SaveAs(finalPath, XlFileFormat.xlWorkbookNormal, Type.Missing, _
                Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, _
                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)

                'Release the objects
                ReleaseObject(oSheet)
                oBook.Close(False, Type.Missing, Type.Missing)
                ReleaseObject(oBook)
                oExcel.Quit()
                ReleaseObject(oExcel)
                'Some time Office application does not quit after automation:
                'so i am calling GC.Collect method.
                GC.Collect()
                MessageBox.Show("Export done successfully!")
              
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Warning", MessageBoxButtons.OK)
        End Try
    End Sub
搜索更多相关主题的帖子: 数据 System Dim Type Missing 
2018-04-01 17:52
xyxcc177
Rank: 9Rank: 9Rank: 9
等 级:贵宾
威 望:26
帖 子:197
专家分:1249
注 册:2017-7-8
收藏
得分:20 
"SELECT  * FROM [dbo].[TEST]" 这一句缺少时间的限制条件
2018-04-02 17:53
ivonsxdt123
Rank: 1
等 级:新手上路
帖 子:3
专家分:0
注 册:2018-4-1
收藏
得分:0 
回复 楼主 ivonsxdt123
请问改怎么写
2018-04-15 13:41
ivonsxdt123
Rank: 1
等 级:新手上路
帖 子:3
专家分:0
注 册:2018-4-1
收藏
得分:0 
回复 2楼 xyxcc177
请问如何加这一条
2018-04-15 13:41
快速回复:想做一个报表,从SQL抓数据,但是怎么想要到固定时间段的数据,现在抓 ...
数据加载中...
 
   



关于我们 | 广告合作 | 编程中国 | 清除Cookies | TOP | 手机版

编程中国 版权所有,并保留所有权利。
Powered by Discuz, Processed in 0.018561 second(s), 8 queries.
Copyright©2004-2024, BCCN.NET, All Rights Reserved