Imports System.Data
Imports System.Data.OracleClient
Imports System.Data.OracleClient.OracleDataReader
Imports System.Data.OracleClient.OracleParameterCollection
Imports Excel
'Imports Excel.XlHAlign
'Imports Excel.XlVAlign

Public Class AnkData

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim app As Excel.Application
        Dim wb As Excel.Workbook
        Dim ws As Excel.Worksheet
        wb = app.Workbooks.Add
        ws = wb.Worksheets( 1 )
      
        Dim commIMEI As OracleCommand = New OracleCommand("select t.msisdn, t.rec_type, t.start_time, t.dialed, t.imei from calls t " & _
"where t.imei like :IMEI " & _
"and t.start_time>=:START_DATE " & _
"and t.start_time<=to_date(:END_DATE)+1 " & _
"order by t.msisdn, t.start_time", conn)
        commIMEI.CommandType = CommandType.Text

        Dim im, ps, pe As New OracleClient.OracleParameter
        im.ParameterName = ":IMEI"
        im.Direction = ParameterDirection.Input
        im.OracleType = OracleType.Number
        im.Value = TextBox1.Text
        commIMEI.Parameters.Add(im)

        ps.ParameterName = ":START_DATE"
        ps.Direction = ParameterDirection.Input
        ps.OracleType = OracleType.DateTime
        ps.Value = DTP1.Text
        commIMEI.Parameters.Add(ps)

        pe.ParameterName = ":END_DATE"
        pe.Direction = ParameterDirection.Input
        pe.OracleType = OracleType.DateTime
        pe.Value = DTP2.Text
        commIMEI.Parameters.Add(pe)

        Dim dr As OracleDataReader
        dr = commIMEI.ExecuteReader

        ws.Cells( 4 ,  2 ).value = "Информация по IMEI: " + im.Value + " за период " + ps.Value + " по " + pe.Value
        ws.Cells( 7 ,  2 ).value = "Номер телефона:"
        ws.Cells( 7 ,  3 ).value = "Тип звонка"
        ws.Cells( 7 ,  4 ).value = "Дата/Время"
        ws.Cells( 7 ,  5 ).value = "Направление"
        ws.Cells( 7 ,  6 ).value = "IMEI"

        With ws
            .Range("B4").Font.Bold = True
            .Range("B7", "F7").ColumnWidth =  20 
            .Range("B7", "F7").Font.Bold = True
            '.Range("B7", "F7").HorizontalAlignment = xlHAlignCenter
            '.Range("B7", "F7").VerticalAlignment = xlVAlignCenter
        End With
        Dim pl, i As Long
        i =  8 
        If dr.HasRows Then
            Do While dr.Read()
                ws.Cells(i,  2 ).value.NumberFormat = "#"
                ws.Cells(i,  3 ).value = dr( 1 )
                If pl <> dr( 0 ) Then
                    ws.Cells(i,  2 ).value = dr( 0 )
                    pl = dr( 0 )
                End If
                ws.Cells(i,  4 ).value = dr( 2 )
                ws.Cells(i,  5 ).value.NumberFormat = "#"
                ws.Cells(i,  5 ).value = dr( 3 )
                ws.Cells(i,  6 ).value.NumberFormat = "#"
                ws.Cells(i,  6 ).value = dr( 4 )
                i = i +  1 
            Loop
        Else : ws.Cells(i,  2 ).value = "IMEI: " + im.Value + " Такого IMEI в системе не существует"
        End If
        'ws.Range(ws.Cells(7, 2), ws.Cells(i, 6)).Borders.LineStyle = xlEdgeLeft
        ws.Range(ws.Cells( 8 ,  2 ), ws.Cells(i,  2 )).Font.Bold = True
        dr.Close()
        app.Visible = True
    End Sub
End Class	

wb = CType(app.Workbooks.Add, Excel.Workbook)

Dim app As Excel.Application
             Dim wb As Excel.Workbook
             Dim ws As Excel.Worksheet
             app = CType(CreateObject("Excel.Application"), Excel.Application)
             wb = CType(app.Workbooks.Add, Excel.Workbook)
             ws = CType(wb.Worksheets( 1 ), Excel.Worksheet)