Exporting multiple datatables to multiple excel files

Last Reply 9 days ago By AnandM

Posted 9 days ago

Hi sir,

 im refering "https://www.aspsnippets.com/Articles/Export-DataSet-or-DataTable-to-Word-Excel-PDF-and-CSV-Formats.aspx"

to export datatables to excel files using for loop but it is converting only for first table so please help to do it

here is my code

for (int i = 0; i < dttable.Rows.Count; i++)
                            {
                                cmd.CommandText = "select * from "+dttable.Rows[i]["TABLE_NAME"]+"";
                                //cmd.Parameters.Clear();
                                //cmd.Parameters.AddWithValue("@name", );
                                DataTable dt = objpar.DyGetData(cmd, css);
                                if (dt.Rows.Count > 0)
                                {
                                    //SqlCommand cmd = new SqlCommand(strQuery);
                                    //DataTable dt = GetData(cmd);

                                    //Create a dummy GridView
                                    GridView GridView1 = new GridView();
                                    GridView1.AllowPaging = false;
                                    GridView1.DataSource = dt;
                                    GridView1.DataBind();

                                    Response.Clear();
                                    Response.Buffer = true;
                                    Response.AddHeader("content-disposition",
                                     "attachment;filename=DataTable.xls");
                                    Response.Charset = "";
                                    Response.ContentType = "application/vnd.ms-excel";
                                    StringWriter sw = new StringWriter();
                                    HtmlTextWriter hw = new HtmlTextWriter(sw);

                                    //for (int i = 0; i < GridView1.Rows.Count; i++)
                                    //{
                                    //    //Apply text style to each Row
                                    //    GridView1.Rows[i].Attributes.Add("class", "textmode");
                                    //}
                                    GridView1.RenderControl(hw);

                                    //style to format numbers to string
                                    //string style = @"<style> .textmode { mso-number-format:\@; } </style>";
                                    //Response.Write(style);
                                    Response.Output.Write(sw.ToString());
                                    Response.Flush();
                                    Response.End();
                                }
                            }

 

 

You are viewing reply posted by: AnandM 9 days ago.
Posted 9 days ago

Hi nandish,

I have created a sample which full fill your requirement you need to modify the code according to your need.

First you need to download ClosedXml DLL and add reference of it to your project. For downloading the Dll refer below link.

Download ClosedXML Library

and i have made use of Northwind Database Customers and Employees table you can get the database from below article.

Install the Northwind and Pubs Sample Databases in SQL Server Express

Refer below sample code.

C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using ClosedXML.Excel;

public partial class CS : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        DataSet ds = new DataSet();
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        string query = @"SELECT TOP 10 CustomerId,ContactName,City,Country FROM Customers GO 
                        SELECT TOP 5 EmployeeID,(FirstName + ' ' + LastName) EmployeeName,City,Country FROM Employees";
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand(query))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    cmd.Connection = con;
                    sda.SelectCommand = cmd;
                    sda.Fill(ds);
                    ds.Tables[0].TableName = "Customers";
                    ds.Tables[1].TableName = "Employees";
                }
            }
        }

        for (int i = 0; i < ds.Tables.Count; i++)
        {
            DataTable dt = ds.Tables[i];
            XLWorkbook wb = new XLWorkbook();
            wb.Worksheets.Add(dt, dt.TableName);
            wb.SaveAs(Server.MapPath("~/Files/") + dt.TableName + ".xlsx");
        }
    }
}

VB.Net

Imports System.Data
Imports System.Data.SqlClient
Imports ClosedXML.Excel

Partial Class VB
    Inherits System.Web.UI.Page
    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        Dim ds As New DataSet()
        Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Dim query As String = "SELECT TOP 10 CustomerId,ContactName,City,Country FROM Customers GO SELECT TOP 5 EmployeeID,(FirstName + ' ' + LastName) EmployeeName,City,Country FROM Employees"
        Using con As New SqlConnection(constr)
            Using cmd As New SqlCommand(query)
                Using sda As New SqlDataAdapter()
                    cmd.Connection = con
                    sda.SelectCommand = cmd
                    sda.Fill(ds)
                    ds.Tables(0).TableName = "Customers"
                    ds.Tables(1).TableName = "Employees"
                End Using
            End Using
        End Using

        For i As Integer = 0 To ds.Tables.Count - 1
            Dim dt As DataTable = ds.Tables(i)
            Dim wb As New XLWorkbook()
            wb.Worksheets.Add(dt, dt.TableName)
            wb.SaveAs(Server.MapPath("~/Files/") + dt.TableName + ".xlsx")
        Next
    End Sub
End Class

 

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html?aff_id=108