Hello Expert,
In My project I have SQL and Excel report,we need to generate report for every Four Hours. We have following Issue.
The Four Hours data - 30,000 Rows with 7 Column in SQL DATABSE , By using current code , For generating report takes more than 15 min . So Can you please guide me , How I can write code So My report will generate in (Max) 1 min Time.
Private Sub ButtonReport_Click(sender As Object, e As EventArgs) Handles ButtonReport.Click
con.Close()
ds.Clear()
Dim r, c As Integer
ProgBarReport.Value = 0
LabelReport.ResetText()
Try
con.Open()
Dim StartDateRpt = Format(DateTimePickerRptStrt.Value, "yyyy-MM-dd HH:mm:ss")
Dim EndDateRpt = Format(DateTimePickerRptEnd.Value, "yyyy-MM-dd HH:mm:ss")
Dim query As String = "SELECT * FROM [ReportDatabase03].[dbo].[Past03] WHERE Date_Time Between '" + StartDateRpt + "' and '" + EndDateRpt + "' order by Date_Time desc"
adpt.SelectCommand = New SqlCommand(query, con)
ds = New DataSet("wincc")
adpt.Fill(ds)
Dim i As Integer
Dim xlApp As Excelr.Application
Dim xlWorkBook As Excelr.Workbook
Dim xlWorkSheet As Excelr.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value
xlApp = New Excelr.Application
xlWorkBook = xlApp.Workbooks.Add("D:\mysettxtup\ParagReportSamplePast03")
xlWorkSheet = xlWorkBook.Sheets("ParagReport")
r = ds.Tables(0).Rows.Count
c = ds.Tables(0).Columns.Count
ProgBarReport.Step = ds.Tables(0).Rows.Count * 2
ProgBarReport.Maximum = ds.Tables(0).Rows.Count
For i = 0 To ds.Tables(0).Rows.Count - 1
Label_R.Text = r
Label_C.Text = c
Label_I.Text = i
Dim dateValue = ds.Tables(0).Rows(i).Item(0)
Dim xxx = Format(dateValue, "dd/MMM/yy HH:mm:ss.fff")
xlWorkSheet.Cells(i + 13, 1) = Format(dateValue, "dd-MM-yyyy HH:mm:ss:fff")
xlWorkSheet.Cells(i + 13, 2) = Format(ds.Tables(0).Rows(i).Item(1), "")
xlWorkSheet.Cells(i + 13, 3) = Format(ds.Tables(0).Rows(i).Item(2), "")
xlWorkSheet.Cells(i + 13, 4) = Format(ds.Tables(0).Rows(i).Item(3), "")
xlWorkSheet.Cells(i + 13, 5) = Format(ds.Tables(0).Rows(i).Item(4), "")
xlWorkSheet.Cells(i + 13, 6) = Format(ds.Tables(0).Rows(i).Item(5), "")
xlWorkSheet.Cells(i + 13, 7) = Format(ds.Tables(0).Rows(i).Item(6), "")
xlWorkSheet.Cells(i + 13, 8) = Format(ds.Tables(0).Rows(i).Item(7), "")
xlWorkSheet.Cells(i + 13, 9) = Format(ds.Tables(0).Rows(i).Item(8), "")
xlWorkSheet.Cells(i + 13, 10) = Format(ds.Tables(0).Rows(i).Item(9), "")
xlWorkSheet.Cells(i + 13, 11) = Format(ds.Tables(0).Rows(i).Item(10), "")
xlWorkSheet.Cells(i + 13, 12) = Format(ds.Tables(0).Rows(i).Item(11), "")
xlWorkSheet.Cells(i + 13, 13) = Format(ds.Tables(0).Rows(i).Item(12), "")
xlWorkSheet.Cells(i + 13, 14) = Format(ds.Tables(0).Rows(i).Item(13), "")
xlWorkSheet.Cells(i + 13, 15) = Format(ds.Tables(0).Rows(i).Item(14), "")
xlWorkSheet.Cells(i + 13, 16) = Format(ds.Tables(0).Rows(i).Item(15), "")
xlWorkSheet.Cells(i + 13, 17) = Format(ds.Tables(0).Rows(i).Item(16), "")
ProgBarReport.Increment(1)
Next
With xlWorkSheet
xlWorkSheet.Cells(6, 2) = Format(Now, "dd/MMM/yy HH:mm")
xlWorkSheet.Cells(7, 2) = Format(ds.Tables(0).Rows(0).Item(0), "dd/MMM/yy HH:mm:ss")
xlWorkSheet.Cells(8, 2) = Format(ds.Tables(0).Rows(r - 1).Item(0), "dd/MMM/yy HH:mm:ss")
xlWorkSheet.Columns("A:Q").EntireColumn.AutoFit()
.Protect()
End With
Dim currentdate As String = String.Format("{0:ddMMyy-HHmm}", DateTime.Now)
xlWorkSheet.SaveAs("D:\ReportPast03" & "\ParagDailyReportPast03" & currentdate & ".xlsx")
xlWorkBook.Close()
xlApp.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)
xlApp = Nothing
ProgBarReport.Value = ProgBarReport.Maximum
LabelReport.Text = "Report generated Scucessfully"
Catch ex As Exception
MsgBox(ex.ToString())
End Try
End Sub
What I have tried:
I have tried above code and takes more than 15 min to generate a report