EXPORT TO EXCEL

Imports Excel = Microsoft.Office.Interop.Excel

Imports System.Security.Cryptography

Try

‘Make Connection

strCon =

modDataSource.strCon_Online

Dim cnn As New SqlConnection(strCon)

‘ Variable

Dim i, j As Integer

‘Excel WorkBook object

Dim xlApp As Microsoft.Office.Interop.Excel.Application

Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook

Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet

Dim misValue As Object = System.Reflection.Missing.Value

xlApp =

New Microsoft.Office.Interop.Excel.ApplicationClass

xlWorkBook = xlApp.Workbooks.Add(misValue)

‘ Sheet Name or Number

xlWorkSheet = xlWorkBook.Sheets(

“sheet1”)

‘ Sql QUery

‘ xlWorkBook.Sheets.Select(“A1:A2”)

Dim sql As String = “select [TruckNumber],[1stWeight],[1stWeightDate],[2ndWeight],[2ndWeightDate]” _

&

“,([2ndWeight]-[1stWeight]) as [Bruto],[SlipNumber],[DespatchDate],[DeliveryNumber]” _

&

“,[MaterialNumber],[MaterialDescription],[CustomerNumber],[CustomerName],[Driver],[Note]” _

&

” from PublicDespatch where [dbo].MidnightOf([DespatchDate]) >= ‘” & Format(dtDespatchDateStart.Value, “yyyy-MM-dd”) & “‘ and “ _

&

” [dbo].MidnightOf([DespatchDate]) <= ‘” & Format(dtDespatchDateEnd.Value, “yyyy-MM-dd”) & “‘ and “ _

&

” [WeighBridgeNum] = ‘” & modFunction.gcWeighBridgeNum & “‘”

‘ SqlAdapter

Dim dscmd As New SqlDataAdapter(sql, cnn.ConnectionString)

‘ DataSet

Dim ds As New DataSet

dscmd.Fill(ds)

‘COLUMN NAME ADD IN EXCEL SHEET OR HEADING

xlWorkSheet.Cells(1, 1).Value =

“Number”

xlWorkSheet.Cells(1, 2).Value =

“Truck Number”

xlWorkSheet.Cells(1, 3).Value =

“First Weight”

xlWorkSheet.Cells(1, 4).Value =

“First Weight Date”

xlWorkSheet.Cells(1, 5).Value =

“Second Weight”

xlWorkSheet.Cells(1, 6).Value =

“Second Weight Date”

xlWorkSheet.Cells(1, 7).Value =

“Bruto”

xlWorkSheet.Cells(1, 8).Value =

“Slip Number”

xlWorkSheet.Cells(1, 9).Value =

“Despatch Date”

xlWorkSheet.Cells(1, 10).Value =

“Delivery Number”

xlWorkSheet.Cells(1, 11).Value =

“Material Number”

xlWorkSheet.Cells(1, 12).Value =

“Material Description”

xlWorkSheet.Cells(1, 13).Value =

“Customer Number”

xlWorkSheet.Cells(1, 14).Value =

“Customer Name”

xlWorkSheet.Cells(1, 15).Value =

“Driver”

xlWorkSheet.Cells(1, 16).Value =

“Note”

‘ SQL Table Transfer to Excel

For i = 0 To ds.Tables(0).Rows.Count – 1

‘Column

For j = 0 To ds.Tables(0).Columns.Count – 1

‘ this i change to header line cells >>>

If j = 0 Then

xlWorkSheet.Cells(i + 2, j + 1) = i + 1

xlWorkSheet.Cells(i + 2, j + 2) = ds.Tables(0).Rows(i).Item(j)

Else

xlWorkSheet.Cells(i + 2, j + 2) = ds.Tables(0).Rows(i).Item(j)

End If

Next

lblCounter.Text = Trim(Str(i + 1))

Next

‘HardCode in Excel sheet

‘ this i change to footer line cells >>>

xlWorkSheet.Cells(i + 3, 6) =

“Total”

Dim str1 As String = “=SUM(G2:G” & Trim(Str(i + 1)) & “)”

xlWorkSheet.Cells.Item(i + 3, 7) = str1

‘ Save as path of excel sheet

Dim namefile As String = “publicdespatch” & Format(dtDespatchDateStart.Value, “yyyyMMdd”) & “_” & Replace(Trim(Str(RandomGenerator())), “-“, “”)

xlWorkSheet.SaveAs(

“C:\Export\” & namefile & “.xlsx”)

xlWorkBook.Close()

xlApp.Quit()

releaseObject(xlApp)

releaseObject(xlWorkBook)

releaseObject(xlWorkSheet)

‘Msg Box of Excel Sheet Path

MsgBox(

“You can find the file C:\Export\” & namefile & “.xlsx”)

Catch ex As Exception

MsgBox(

“You can’t find data in your choose”)

End Try

Private Sub releaseObject(ByVal obj As Object)

Try

System.Runtime.InteropServices.

Marshal.ReleaseComObject(obj)

obj =

Nothing

Catch ex As Exception

obj =

Nothing

Finally

GC.Collect()

End Try

End Sub

Public Function RandomGenerator() As Integer

Dim byteCount As Byte() = New Byte(6) {}

Dim randomNumber As New RNGCryptoServiceProvider()

randomNumber.GetBytes(byteCount)

Return BitConverter.ToInt32(byteCount, 0)

End Function

and don’t forget to put “Microsoft Excel 15.0 Object Library” in references.

C:\Windows\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\15.0.0.0__71e9bce111e9429c\Microsoft.Office.Interop.Excel.dll

Leave a Reply

Your email address will not be published. Required fields are marked *