My excel report takes long time to generate

Question

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
 
 
        'Clear connection and dataset before open
        con.Close()
        ds.Clear()
        ' used for excel report
        Dim r, c As Integer
        'To handle exception use here Try catach
 
        ' Visible progress bar
        ' ProgBarReport.Visible = True
        ProgBarReport.Value = 0
        LabelReport.ResetText()
 
        Try
            'Open connection 
            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
 
            ' Standard for Excel application
            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
 
            ' ------- Read Sample Report Location from Text file ------
            'Dim Srpath As String = "c:\mysettxtup\samplereport.txt"
            'Dim Srobjectreader As New System.IO.StreamReader(Srpath)
            'Dim Srpathstring As String = Srobjectreader.ReadLine
 
 
            ' ------- Read Sample Report Location from Text file  ------
            ' xlWorkBook = xlApp.Workbooks.Add(Srpathstring & "\SampleReport")
 
            '******  Chanage above hardcode to read excel template from D drive sampleRepport ********
 
            xlWorkBook = xlApp.Workbooks.Add("D:\mysettxtup\ParagReportSamplePast03")
 
            ' ------- Save as Report Location from Text file  ------
            '   Dim Slrpath As String = "c:\mysettxtup\Savereport.txt"
            ' Dim Slrobjectreader As New System.IO.StreamReader(Slrpath)
            ' Dim Slrpathstring As String = Slrobjectreader.ReadLine
            xlWorkSheet = xlWorkBook.Sheets("ParagReport")
 
            r = ds.Tables(0).Rows.Count ' 7 add here because we start 1 record from 8th row
            c = ds.Tables(0).Columns.Count
 
            ProgBarReport.Step = ds.Tables(0).Rows.Count * 2
            ProgBarReport.Maximum = ds.Tables(0).Rows.Count
 
            ' MessageBox.Show(r, c)
            ' Print Table Row with column of worksheet
            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") 'Coulmn B- "Date & time"
                xlWorkSheet.Cells(i + 13, 2) = Format(ds.Tables(0).Rows(i).Item(1), "") 'Coulmn E - "Customer Part No"
                xlWorkSheet.Cells(i + 13, 3) = Format(ds.Tables(0).Rows(i).Item(2), "") 'Coulmn F - "Tenneco FG SAP part No"
                xlWorkSheet.Cells(i + 13, 4) = Format(ds.Tables(0).Rows(i).Item(3), "") 'Coulmn G - "Tenneco FG increment Sr .No"
                xlWorkSheet.Cells(i + 13, 5) = Format(ds.Tables(0).Rows(i).Item(4), "") 'Coulmn H - "Tenneco canning SAP part No 1"
                xlWorkSheet.Cells(i + 13, 6) = Format(ds.Tables(0).Rows(i).Item(5), "") 'Coulmn H - "Tenneco canning SAP part No 2"
                xlWorkSheet.Cells(i + 13, 7) = Format(ds.Tables(0).Rows(i).Item(6), "") 'Coulmn I - " Canning  Sr.No from Scan"
                xlWorkSheet.Cells(i + 13, 8) = Format(ds.Tables(0).Rows(i).Item(7), "") 'Coulmn I - " Canning  Sr.No from Scan"
                xlWorkSheet.Cells(i + 13, 9) = Format(ds.Tables(0).Rows(i).Item(8), "") 'Coulmn I - " Canning  Sr.No from Scan"
                xlWorkSheet.Cells(i + 13, 10) = Format(ds.Tables(0).Rows(i).Item(9), "") 'Coulmn I - " Canning  Sr.No from Scan"
                xlWorkSheet.Cells(i + 13, 11) = Format(ds.Tables(0).Rows(i).Item(10), "") 'Coulmn I - " Canning  Sr.No from Scan"
                xlWorkSheet.Cells(i + 13, 12) = Format(ds.Tables(0).Rows(i).Item(11), "") 'Coulmn I - " Canning  Sr.No from Scan"
                xlWorkSheet.Cells(i + 13, 13) = Format(ds.Tables(0).Rows(i).Item(12), "") 'Coulmn I - " Canning  Sr.No from Scan"
                xlWorkSheet.Cells(i + 13, 14) = Format(ds.Tables(0).Rows(i).Item(13), "") 'Coulmn I - " Canning  Sr.No from Scan"
                xlWorkSheet.Cells(i + 13, 15) = Format(ds.Tables(0).Rows(i).Item(14), "") 'Coulmn I - " Canning  Sr.No from Scan"
                xlWorkSheet.Cells(i + 13, 16) = Format(ds.Tables(0).Rows(i).Item(15), "") 'Coulmn I - " Canning  Sr.No from Scan"
                xlWorkSheet.Cells(i + 13, 17) = Format(ds.Tables(0).Rows(i).Item(16), "") 'Coulmn I - " Canning  Sr.No from Scan"
                '~~> Progress Bar 
                ' ProgBarReport.PerformStep()
                ProgBarReport.Increment(1)
            Next
 
            ' Excel Animation
            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
 
            ' Save Excel Sheet
            '~~> Save Worksheet file to the following location
            Dim currentdate As String = String.Format("{0:ddMMyy-HHmm}", DateTime.Now)
 
            ' ------- Save as Report Location from Text file  ------
            '******  Chanage above hardcode to Save excel Report to D drive in Report folder ********           
            xlWorkSheet.SaveAs("D:\ReportPast03" & "\ParagDailyReportPast03" & currentdate & ".xlsx")
 
            xlWorkBook.Close()
            xlApp.Quit()
            'objExcel.Quit()
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)
            xlApp = Nothing
 
            ProgBarReport.Value = ProgBarReport.Maximum
 
            '  ProgBarReport.Value = 0
            LabelReport.Text = "Report generated Scucessfully"
 
        Catch ex As Exception
 
            MsgBox(ex.ToString())
 
        End Try
    End Sub

Answer 1

I use nuget package freespire.xls to export. On around 10000 records its very fast.

This is an example using a DataGridView.

    Dim dato As Date = Now.ToShortDateString
    Dim fileName As String = appPath & "\Excel\QN Export " & dato & " " & UserName & ".xlsx"
    Dim book As New Spire.Xls.Workbook
    Dim sheet1 As Spire.Xls.Worksheet
    sheet1 = book.Worksheets(0)

       'Creating DataTable.
        Dim dt As New DataTable()

        'Adding the Columns.
        For Each column As DataGridViewColumn In DataGridView1.Columns
            dt.Columns.Add(column.HeaderText, column.ValueType)
        Next

        'Adding the Rows.
        For Each row As DataGridViewRow In DataGridView1.Rows
            dt.Rows.Add()
            For Each cell As DataGridViewCell In row.Cells
                dt.Rows(dt.Rows.Count - 1)(cell.ColumnIndex) = cell.Value 
            Next
        Next

        sheet1.InsertDataTable(dt, True, 1, 1)

      book.SaveToFile(fileName, ExcelVersion.Version2007)

        'open
        If File.Exists(fileName) Then
            Dim p As New Process
            p.StartInfo = New ProcessStartInfo(fileName)
            p.StartInfo.UseShellExecute = True
            p.Start()
        End If

Hope this helps.