匯出Excel 的方式有很多種

以下這種是將Excel 當成DB 來做匯出

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Try '建立暫存Table!! Dim dt As New DataTable dt.Columns.Add(New DataColumn("tItem", System.Type.GetType("System.String"))) dt.Columns.Add(New DataColumn("tDescription", System.Type.GetType("System.String"))) dt.Columns.Add(New DataColumn("tMemon", System.Type.GetType("System.String"))) '建立暫存資料!! Dim dr As DataRow = dt.NewRow dr.Item("tItem") = "1" dr.Item("tDescription") = "你好嗎?" dr.Item("tMemon") = "...." dt.Rows.Add(dr) '建立暫存資料!! dr = dt.NewRow dr.Item("tItem") = "2" dr.Item("tDescription") = "你是誰?" dr.Item("tMemon") = ",,,,," dt.Rows.Add(dr) '呼叫匯出Excel Function If Not ExportExcel(dt, "c:\temp\123.xls", "測試") Then MessageBox.Show("匯出Excel 時發生錯誤!!") Else MessageBox.Show("匯出Excel 成功!!") End If Catch ex As Exception '錯誤則秀出錯誤訊息!! MessageBox.Show(ex.Message) End Try End Sub 'DT: Data Table 'FullFileName: 匯出Excel 的完整路徑 Ex: C:\123.xls 'TableName: 匯出Excel 的Sheet Name Private Function ExportExcel(ByVal DT As DataTable, ByVal FullFileName As String, ByVal TableName As String) As Boolean Dim connection As New Data.OleDb.OleDbConnection(String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0""", FullFileName)) Try '建立Excel Connection connection.Open() '產生Table Dim strCreate As String For i As Int32 = 0 To DT.Columns.Count - 1 strCreate += String.Format(",{0} Text(255)", DT.Columns(i).ColumnName) Next strCreate = String.Format("Create Table [{0}] ({1})", TableName, strCreate.Substring(1)) Dim command As New Data.OleDb.OleDbCommand(strCreate, connection) command.ExecuteNonQuery() '讀取Excel Table (Sheet) Dim queryString As String = String.Format("Select * From [{0}]", TableName) '建立Excel 配接器 Dim adapter As New Data.OleDb.OleDbDataAdapter() '宣告一個Dataset Dim ds As New DataSet adapter.SelectCommand = New Data.OleDb.OleDbCommand(queryString, connection) Dim builder As Data.OleDb.OleDbCommandBuilder = New Data.OleDb.OleDbCommandBuilder(adapter) '產生新增語法 adapter.Fill(ds, TableName) For i As Int32 = 0 To DT.Rows.Count - 1 Dim dr As DataRow = ds.Tables(TableName).NewRow For j As Int32 = 0 To DT.Columns.Count - 1 dr.Item(DT.Columns(j).ColumnName) = DT.Rows(i).Item(j) Next ds.Tables(TableName).Rows.Add(dr) Next builder.GetInsertCommand(True) '執行新增語法 adapter.Update(ds, TableName) Return True Catch ex As Exception '擲出錯誤!! Throw ex Finally '關閉連線 connection.Close() End Try End Function

'建立一個Table Tmp

Create Table Tmp (t1 number,t2 varchar2(20));

'查詢Tmp Column

Select * From User_Tab_Columns Where Table_Name = 'TMP';

'將欄位t1 改成 tt1

Alter Table Tmp Rename Column t1 to tt1;

'查詢修改後Tmp Column

Select * From User_Tab_Columns Where Table_Name = 'TMP';

 1.查看使用中的Session select sid||','||serial# from v$session 2.查看Lock Session select * from DBA_DML_LOCKS 3.查看Lock Session ID select a.sid||','||a.serial#,a.machine from v$session a,DBA_DML_LOCKS b where a.sid = b.session_id group by a.sid||','||a.serial#,a.machine 4.刪除 Session alter system kill session 'xxx,yyyy' immediate 註: xxx,yyyy 為3.查看Lock Session ID 所查得的Session ID

原始資料

Select * From xx001

select xx001.*,rank() over (PARTITION BY address  ORDER BY custname) Rank FROM xx001

得到下列的結果

select xx001.*,dense_rank() over (PARTITION BY address  ORDER BY custname) DenseRank FROM xx001

得到下列的結果

從前面4筆看不出他們的差別,

請仔細看一下從第5筆開始的差異,

Rank 會將CustName 相同的編為同一個號碼, 但他會將相同號碼的部份做計數, 於下一筆相同的時候是以計數後的數字往下編,

Dense_Rank 則是不管資料有重複幾筆都會以上一筆號碼+1往下編

以上是Rank 及 Dense_Rank 的用法及差異, 於做重複資料時,想只取其中一筆時可發揮他的功效

select * From (select xx001.*,rank() over (PARTITION BY address  ORDER BY custname) Rank FROM xx001) A Where Rank = 1 = >可抓取重複資料的第一筆

'傳送端 '此處需要1個WinSock,1個Timer,3個Button Option Explicit Dim blnConnect As Boolean Private Sub cmdConnect_Click() '連線 On Error Resume Next With Winsock1 If .State = sckConnected Then Exit Sub If .State <> sckClosed Then .Close .Protocol = sckTCPProtocol .Connect txtRemoteIP, txtPort End With blnConnect = False Timer1.Interval = txtSec * 1000 End Sub Private Sub cmdExit_Click() Unload Me End Sub Private Sub cmdSend_Click() '傳送資料 On Error Resume Next Winsock1.SendData txtReceivedData End Sub Private Sub Timer1_Timer() ' On Error Resume Next If Not blnConnect Then MsgBox "連線逾時(" & txtSec & ")!!" Timer1.Interval = 0 End Sub Private Sub Winsock1_Connect() blnConnect = True MsgBox "連線OK" End Sub Private Sub Winsock1_DataArrival(ByVal bytesTotal As Long) '接收資料 Dim xx As String Winsock1.GetData xx, vbString txtReceivedData = txtReceivedData & CStr(xx) End Sub '接收端 '此範例是用物件陣列開2個Port 來傳送/接收資料 '此處需要2個winsock,4個Button,4個TextBox Option Explicit Private Sub cmdConnect_Click(Index As Integer) '連線 With Winsock1(Index) .Bind txtPort(Index), .LocalIP .Listen End With End Sub Private Sub cmdExit_Click() '結束 Unload Me End Sub Private Sub cmdSend_Click() '傳送資料 On Error Resume Next Dim intLoop As Integer For intLoop = 0 To 1 Winsock1(intLoop).SendData txtReceivedData(intLoop) Next End Sub Private Sub Timer1_Timer() Dim intLoop As Integer For intLoop = 0 To Winsock1.UBound If Winsock1(intLoop).State <> sckClosed Then lblReceivedData(intLoop).ForeColor = &H8000000F Else lblReceivedData(intLoop).ForeColor = &HFF& End If Next End Sub Private Sub Winsock1_ConnectionRequest(Index As Integer, ByVal requestID As Long) If Winsock1(Index).State <> sckClosed Then Winsock1(Index).Close Winsock1(Index).Accept requestID End Sub Private Sub Winsock1_DataArrival(Index As Integer, ByVal bytesTotal As Long) '接收資料 Dim xx As String Winsock1(Index).GetData xx, vbString txtReceivedData(Index) = txtReceivedData(Index) & CStr(xx) End Sub