Hello,
I want to export table from database to excel, I will attach the code i used.
The problem, when the code is executed a folder named Reporting is created but it is empty and not have any excel files!
Can anyone help me?
Imports System.IO
Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient
Imports Excel = Microsoft.Office.Interop.Excel
Imports ExcelAutoFormat = Microsoft.Office.Interop.Excel.XlRangeAutoFormat
Public Class Form1
Private SQL As New SQLControl
Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
TextBox1.Text = TextBox1.Text + 1
If TextBox1.Text = 20 Then
Dim constr As String = "Server=DESKTOP-9EKQ5QI;Database=CREST_CONTROLS;User=sa;Pwd=123456"
Using con As SqlConnection = New SqlConnection(constr)
Using cmd As SqlCommand = New SqlCommand("SELECT *FROM Report1")
Dim sda As SqlDataAdapter = New SqlDataAdapter
Try
cmd.Connection = con : con.Open()
sda.SelectCommand = cmd
Dim dt As DataTable = New DataTable
sda.Fill(dt)
If dt.Rows.Count > 0 Then
Dim path As String = "C:\Users\AKRAM\Desktop\Reporting"
If Not Directory.Exists(path) Then
Directory.CreateDirectory(path)
End If
Dim xlAppToExport As New Excel.Application
xlAppToExport.Workbooks.Add()
Dim xlWorkSheetToExport As Excel.Worksheet
xlWorkSheetToExport = CType(xlAppToExport.Sheets("Sheet1"), Excel.Worksheet)
Dim iRowCnt As Integer = 8
With xlWorkSheetToExport
.Cells(1, 1).value = "Raw_Tank_A"
.Cells(1, 1).FONT.NAME = "Calibri"
.Cells(1, 1).Font.Bold = True
.Cells(1, 1).Font.Size = 20
.Range("A1:D1").MergeCells = True
.Cells(iRowCnt - 1, 1).value = "id"
.Cells(iRowCnt - 1, 2).value = "username"
.Cells(iRowCnt - 1, 3).value = "tank_name"
.Cells(iRowCnt - 1, 4).value = "material_name"
.Cells(iRowCnt - 1, 5).value = "Transaction_Type"
.Cells(iRowCnt - 1, 6).value = "Transaction_Value"
.Cells(iRowCnt - 1, 7).value = "Balance"
.Cells(iRowCnt - 1, 8).value = "created_at"
For i = 0 To dt.Rows.Count - 1
.Cells(iRowCnt, 1).value = dt.Rows(i).Item("id")
.Cells(iRowCnt, 2).value = dt.Rows(i).Item("username")
.Cells(iRowCnt, 3).value = dt.Rows(i).Item("tank_name")
.Cells(iRowCnt, 4).value = dt.Rows(i).Item("material_name")
.Cells(iRowCnt, 5).value = dt.Rows(i).Item("Transaction_Type")
.Cells(iRowCnt, 6).value = dt.Rows(i).Item("Transaction_Value")
.Cells(iRowCnt, 7).value = dt.Rows(i).Item("Balance")
.Cells(iRowCnt, 8).value = dt.Rows(i).Item("created_at")
iRowCnt = iRowCnt + 1
Next
xlAppToExport.ActiveCell.Worksheet.Cells(8, 1).AutoFormat( _
ExcelAutoFormat.xlRangeAutoFormatList3)
End With
xlWorkSheetToExport.SaveAs("C:\Users\AKRAM\Desktop\Reporting" & "Raw.xlsx")
xlAppToExport.Workbooks.Close() : xlAppToExport.Quit()
xlAppToExport = Nothing : xlWorkSheetToExport = Nothing
MsgBox("Exported")
End If
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Using
End Using
End If
End Sub
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Timer1.Enabled = True
Timer2.Enabled = True
End Sub
Private Sub Timer2_Tick(sender As Object, e As EventArgs) Handles Timer2.Tick
Label1.Text = DateTime.Now.ToString("dd-MMM-yyyy hh:mm:ss tt")
End Sub
End Class
