Click here to Skip to main content
13,355,018 members (57,055 online)
Rate this:
 
Please Sign up or sign in to vote.
I have 10 Lakh records in my table, How to export the data to excel by splitting into 1 lakh records into one Excel file.

If I am having 10 lakh records if i export the data using ASP.NET it should generate 10 Excel files containing 1 lakh records.

I tried the following code, It is able to download the first 100,000 records after that it is not going back to the loop again, is there any way to download excel file in loop?

What I have tried:

I tried the following code it works for me to export the data into Excel.

<pre>Protected Sub ExportExcel(ByVal sender As Object, ByVal e As EventArgs)
        Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Dim cmd As New SqlCommand

        If txtRows.Text <> String.Empty Then
            rows = txtRows.Text
        End If

        If txtTable.Text <> String.Empty Then
            tbl = txtTable.Text
        End If

        'Dim excelfiles As Integer = dt.Rows.Count / 100000

        If txtfltclmn.Text <> String.Empty AndAlso txtfltclmnval.Text <> String.Empty AndAlso txtTable.Text <> String.Empty Then
            cmd = New SqlCommand("SELECT TOP " & rows & " * FROM " & txtTable.Text & " WHERE " & txtfltclmn.Text & "=" & "'" & txtfltclmnval.Text & "'" & " ")
        ElseIf txtfltclmn.Text = String.Empty AndAlso txtfltclmnval.Text = String.Empty AndAlso txtTable.Text <> String.Empty Then
            cmd = New SqlCommand("SELECT TOP " & rows & " * FROM " & txtTable.Text & " ")
        ElseIf txtfltclmn.Text = String.Empty AndAlso txtfltclmnval.Text = String.Empty AndAlso txtTable.Text = String.Empty Then
            cmd = New SqlCommand("SELECT TOP " & rows & " * FROM " & tbl & " ")
        End If
        Dim con As New SqlConnection(constr)
        con.Open()
        'Using cmd As New SqlCommand("SELECT * FROM " & txtTable.Text & " WHERE " & txtfltclmn.Text & "=" & "'" & txtfltclmnval.Text & "'" & " ")
        Using sda As New SqlDataAdapter()
            cmd.Connection = con
            sda.SelectCommand = cmd
            Using dt As New DataTable()
                sda.Fill(dt)

                Dim i As Integer = 0
                i = dt.Rows.Count / 100000
                For j = 0 To i
                    Dim dtfind As DataTable = dt.AsEnumerable().Skip(j * 100000).Take(100000).CopyToDataTable()
                    If dtfind.Rows.Count > 0 Then
                        Using wb As New XLWorkbook()
                            wb.Worksheets.Add(dtfind, "TSTRAN")
                            Response.Clear()
                            Response.Buffer = True
                            Response.Charset = ""
                            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
                            Response.AddHeader("content-disposition", "attachment;filename=" & tbl & "_" & j & ".xlsx")
                            Using MyMemoryStream As New MemoryStream()
                                wb.SaveAs(MyMemoryStream)
                                MyMemoryStream.WriteTo(Response.OutputStream)
                                Response.Flush()
                                Response.End()
                            End Using
                        End Using
                    End If
                    j = j + 1
                Next
            End Using
        End Using
        
        con.Close()
        'End Using
        'End Using
    End Sub
Posted 8-Nov-17 1:56am
Updated 8-Nov-17 5:06am
v5
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

cmd = New SqlCommand("SELECT TOP " & rows & " * FROM " & txtTable.Text & " WHERE " & txtfltclmn.Text & "=" & "'" & txtfltclmnval.Text & "'" & " ")

Not a solution to your question, but another problem you have.
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]
  Permalink  
Comments
Sree_Ranga 8-Nov-17 10:00am
   
Thank you for your suggestion. I Understand the problem with SQL Injection. If is there any way to download the excel like split every 100,000 records into separate Excel file.

If 1,000,000 records should get to downloaded into 10 Excel files containing 100,000 records.

Please suggest
CHill60 8-Nov-17 10:29am
   
I gave you a suggestion in Solution 1 - you need to "Page" your results
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

The technique you are looking for is "Paging" using SQL.

There are several posts here on CodeProject that will explain it with examples - here is a starter search ... just filter it until you find one that you can relate to
CodeProject Search[^]
  Permalink  

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web02 | 2.8.180111.1 | Last Updated 8 Nov 2017
Copyright © CodeProject, 1999-2018
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100