Export DataSet to Multiple Excel Sheets and download into a zip file using asp.net C#

Question

Hi how to Export DataSet to Multiple Excel Sheets and download those file into a zip in C# asp.net?

Answer 1

This has already been well covered on Stack Overflow. First of all, to create your Excel spreadsheets, check this previous post:

• Create Excel (.XLS and .XLSX) file from C#

Next to zip the files, check these excellent answers:

• How to zip multiple files using only .net api in c#
• C# .net code for zipping a file using PKZIP
• Sample C# .net code for zipping a file using 7zip
• more potential answers here

If you want to automatically stream it back to the user, then try these answers:

• Create Zip file from stream and download it
• Help required: zip files streaming
• How do you stream a zip file from the click of an image button in asp.net?
• ASP.NET Download All Files as Zip

Answer 2

Private Sub CopySheet() Dim cmd As OleDbCommand Dim dt As String Dim ds As DataSet = New DataSet() Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\EXCEL\From.xls;Extended Properties=Excel 8.0" Dim oledbConn As OleDbConnection = New OleDbConnection(connString) Try oledbConn.Open() For i = 0 To 1 If i = 0 Then cmd = New OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn) dt = "Sheet3" Else cmd = New OleDbCommand("SELECT * FROM [Sheet2$]", oledbConn) dt = "Sheet4" End If Dim oleda As OleDbDataAdapter = New OleDbDataAdapter() oleda.SelectCommand = cmd oleda.Fill(ds, dt) Next

        ExportDatasetToExcel(ds, "dd")
    Catch
    Finally
        oledbConn.Close()
    End Try
End Sub

Public Sub ExportDatasetToExcel(ByVal ds As DataSet, ByVal strExcelFile As String)

    Dim conn As New OleDbConnection(String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\EXCEL\To.xls;Extended Properties=Excel 8.0"))
    conn.Open()
    Dim strTableQ(ds.Tables.Count) As String
    Dim i As Integer = 0
    'making table query
    For i = 0 To ds.Tables.Count - 1
        strTableQ(i) = "CREATE TABLE [" & ds.Tables(i).TableName & "]("
        Dim j As Integer = 0
        For j = 0 To ds.Tables(i).Columns.Count - 1
            Dim dCol As DataColumn
            dCol = ds.Tables(i).Columns(j)
            strTableQ(i) &= " [" & dCol.ColumnName & "] varchar(255) , "
        Next
        strTableQ(i) = strTableQ(i).Substring(0, strTableQ(i).Length - 2)
        strTableQ(i) &= ")"
        Dim cmd As New OleDbCommand(strTableQ(i), conn)
        cmd.ExecuteNonQuery()
    Next
    'making insert query
    Dim strInsertQ(ds.Tables.Count - 1) As String
    For i = 0 To ds.Tables.Count - 1
        strInsertQ(i) = "Insert Into " & ds.Tables(i).TableName & " Values ("
        For k As Integer = 0 To ds.Tables(i).Columns.Count - 1
            strInsertQ(i) &= "@" & ds.Tables(i).Columns(k).ColumnName & " , "
        Next
        strInsertQ(i) = strInsertQ(i).Substring(0, strInsertQ(i).Length - 2)
        strInsertQ(i) &= ")"
    Next
    'Now inserting data
    For i = 0 To ds.Tables.Count - 1
        For j As Integer = 0 To ds.Tables(i).Rows.Count - 1
            Dim cmd As New OleDbCommand(strInsertQ(i), conn)
            For k As Integer = 0 To ds.Tables(i).Columns.Count - 1
                cmd.Parameters.AddWithValue("@" & ds.Tables(i).Columns(k).ColumnName.ToString(), ds.Tables(i).Rows(j)(k).ToString())
            Next
            cmd.ExecuteNonQuery()
            cmd.Parameters.Clear()
        Next
    Next

    conn.Close()
End Sub

Answer 3

NPoi for export and DotNetZip Library for ziping it.