Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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.
VB
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


What I have tried:

I have tried above code and takes more than 15 min to generate a report
Posted
Updated 18-Aug-22 9:54am
Comments
0x01AA 18-Aug-22 14:53pm    
How is about to write the data first into a csv file and load it by Excel Interop from there?
Member 11043816 18-Aug-22 15:01pm    
Do you have some code for the same
0x01AA 18-Aug-22 16:00pm    
See solution 1.
Member 11043816 18-Aug-22 17:05pm    
Thanks for your Support..I will check and revert back to you on same.Can you please share a code on "database by an ADO connection. Makes it most probably more powerful. For more details, have a look to workbooks.OpenDatabase(...)"
Dave Kreskowiak 18-Aug-22 15:47pm    
Office Interop is notoriously slow. You would be much better off using OpenXML instead.

1 solution

How to load data to an Excel file by Interop from a Tab delimited Textfile.
This works for me:
private void buttonLoadCsv_Click(object sender, EventArgs e)
{
    // The tab delimited text file
    string csvFileName = @"C:\Temp\cp.excel\TexfileImport\report.txt";

    // Excel Application
    Microsoft.Office.Interop.Excel.Application _excel = new _Excel.Application();

    // The workbooks
    Workbooks workbooks = _excel.Workbooks;

    // No User Intervention Dialogues
    _excel.DisplayAlerts = false;

    // Open the textfile
    workbooks.OpenText(csvFileName,
                       DataType: Microsoft.Office.Interop.Excel.XlTextParsingType.xlDelimited,
                       TextQualifier: Microsoft.Office.Interop.Excel.XlTextQualifier.xlTextQualifierNone,
                       ConsecutiveDelimiter: true,
                       Semicolon: true);

    // SaveAs the workbook as xlsx excel file
    workbooks[1].SaveAs(csvFileName + ".xlsx", XlFileFormat.xlOpenXMLWorkbook);
}

Remark: Another solution is to prepare the report data in your/a database and load the report from the database by an ADO connection. Makes it most probably more powerful. For more details, have a look to workbooks.OpenDatabase(...)
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900