111

  Dim conString As String = "Server=192.168.0.208;Port=5432; Userid=confluence_user;Password=e_user; Protocol=3;SSL=false;Pooling=true;Mi" & _

      "nPoolSize=1; MaxPoolSize=20;Encoding=UNICODE; Timeout=15;SslMode=Disable;Database=confluence"

        Dim ourDataTable As DataTable = Nothing

        Dim ourDataTable2 As DataTable = Nothing

        Dim tmpdata As String = ""

        Dim connection As Npgsql.NpgsqlConnection = New Npgsql.NpgsqlConnection(conString)

        ' Try

        ourDataTable = New DataTable

        Dim ourAdapter As Npgsql.NpgsqlDataAdapter = New NpgsqlDataAdapter("SELECT distinct sp.permid, sp.permtype, s.spacekey, s.spacename, sp.permgroupname, um.user_name " & _

           "FROM SPACEPERMISSIONS sp " & _

"JOIN SPACES s ON sp.spaceid = s.spaceid " & _

"LEFT JOIN cwd_user um ON sp.permusername = um.user_name where sp.permtype='SETSPACEPERMISSIONS' AND s.spacekey='" & no & "'", connection)

        'ourAdapter.SelectCommand.Parameters.AddWithValue("@no", no)

        ourAdapter.Fill(ourDataTable)

        'MsgBox(no & "<br>")

        For i As Integer = 0 To ourDataTable.Rows.Count - 1

            tmpdata += ourDataTable.Rows(i)("user_name") & "/"

        Next

        Return tmpdata

        'If IsDBNull(no) Then

        '    Return ""

        'Else

        'End If

        ' Catch ex As Exception

        ' End Try

    End Function

Dim conString As String = "Server=192.168.0.208;port=5432; Userid=confluence_user;Password=confluence_user; Protocol=3;SSL=false;Pooling=true;Mi" & _

 "nPoolSize=1; MaxPoolSize=20;Encoding=UNICODE; Timeout=15;SslMode=Disable;Database=confluence"

        Dim ourDataTable As DataTable = Nothing

        Dim connection As Npgsql.NpgsqlConnection = New Npgsql.NpgsqlConnection(conString)

        connection.Open()

        Dim exl_ap As New Excel.Application

        Dim exl_wkb As Excel.Workbook

        Dim exl_wks As Excel.Worksheet

        Dim j As Integer = 2

        '定義存檔路徑及檔案名稱

        Dim sPath, sFilename As String

        sPath = Application.StartupPath.ToString

        sFilename = "\db\" & Format(Now, "yyyyMMdd") & ".xls"

        exl_wkb = exl_ap.Workbooks.Open(sPath & "\employee.xls", , True) '這個TRUE是Readonly  

        exl_wks = exl_wkb.Worksheets("sheet1")  ' exl_wks = exl_wkb.Worksheets(1) 同等意思

        '或者 exl_wks = exl_wkb.ActiveSheet 也行

        '標題  Header

        With exl_wks

            .Name = Format(Now, "yyyyMM")

            .Range("A" & j).Value = "員工編號"

            .Range("B" & j).Value = "員工姓名"

            .Range("A1:E1").Interior.ColorIndex = 6  '顏色

            .Cells().ColumnWidth = 10  '欄位寬度

        End With

        Dim objWriter As New System.IO.StreamWriter(sFilename)

        '抓DB  GetDataSet函數省略不介紹

        Dim cmd As New NpgsqlCommand("SELECT spaceid,spacename from spaces", connection)

        'cmd.CommandText = "SELECT EmployeeID, FirstName, LastName FROM Employee ORDER BY EmployeeID"

        Dim lrd As NpgsqlDataReader = cmd.ExecuteReader

        While lrd.Read()

            ' objWriter.WriteLine(lrd.Item(0) & ", " & lrd.Item(1))

            ' If loDS.Tables(0).Rows.Count > 0 Then

            '內容 Content

            '   For i As Integer = 0 To loDS.Tables(0).Rows.Count - 1

            j += 1

            With exl_wks

                .Range("A" & j).Value = lrd.Item(0).ToString().Trim()

                .Range("B" & j).Value = lrd.Item(1).ToString().Trim()

            End With

            ' Next

            ' End If

        End While

        'Dim FILE_NAME As String = "C:\db\EmployeeList.xls"

        'Dim objWriter2 As New System.IO.StreamWriter(sFilename)

        objWriter.Close()

        'objWriter2.Close()

        'Dim sqlstr, lcReturn As String

        'Dim loDS As New DataSet

        'sqlstr = "select empid,firstName,jobtitle,Case when sex='M' then '男' when sex='F' then '女' else null End as gender " & _

        '         ",Convert(varchar(10),startdate,111) as startdate from dbo.OHEM "

        'lcReturn = GetDataSet(sqlstr, loDS, "OHEM")

        '判斷是否有錯誤訊息

        '存檔

        '  exl_ap.SaveWorkspace() '另開視窗

        exl_wkb.SaveAs(sFilename)

        '   exl_ap.Visible = True

        '  exl_wks = Nothing

        '   exl_wkb.Close()

        '  exl_wkb = Nothing

        '  exl_ap.Quit()

        '  exl_ap = Nothing

        '  MsgBox("匯出完成!!")

        GC.Collect()