Export data from multiple tables to Excel in ASP.Net using C# and VB.Net

lingers

SOLVED CLOSED ACTIVE ANSWERED
User: lingers
Posted: on Oct 26, 2022 11:43 PM
Forum: Data Controls
Answer: 1
Views: 3323

Please help i am having the error below while exporting data to excel.

please note i have the description column in 2 different table and i also want only the first record in job_material for each pid in job_material table to be displayed in the excel.

Error

The column 'description' was specified multiple times for 't'. Invalid column name 'id'.

I have five table namely

1. job

2. job_cylinder

3. job_die

4. job_ink

5. job_material

I am trying to export data from this five (5) table into excel

1.) the id in the job table(j) is the pid in the cylinder table (c) (inner join job_cylinder c on j.id =c.pid)

2.) the dierefernceno in the job table (j) the id in job_die table (d) ( inner join job_die d on j.dierefernceno=d.id)

3.)the id in the job table(j) is the pid in the job_ink table (i) (inner join job_ink i on j.id =i.pid)

4.) the id in the job table(j) is the pid in the job_material table (m) (inner join job_material m on j.id =m.pid)

USE [Test]
GO
/****** Object: Table [dbo].[job] Script Date: 23/10/2022 23:23:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[job](
	[id] [int] NULL,
	[pid] [nvarchar](50) NULL,
	[description] [nvarchar](50) NULL,
	[variant] [nvarchar](50) NULL,
	[country] [nvarchar](50) NULL,
	[pack] [nvarchar](50) NULL,
	[customer] [nvarchar](50) NULL,
	[artworkdate] [nvarchar](50) NULL,
	[dierefernceno] [nvarchar](50) NULL,
	[templateno] [nvarchar](50) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[job_cylinder] Script Date: 23/10/2022 23:23:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[job_cylinder](
	[id] [int] NULL,
	[pid] [int] NULL,
	[unit] [int] NULL,
	[posino] [nvarchar](50) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[job_die] Script Date: 23/10/2022 23:23:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[job_die](
	[id] [int] NULL,
	[dieno] [nvarchar](50) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[job_ink] Script Date: 23/10/2022 23:23:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[job_ink](
	[id] [int] NULL,
	[pid] [int] NULL,
	[unit] [int] NULL,
	[description] [nvarchar](50) NULL,
	[inkcode] [nvarchar](50) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[job_material] Script Date: 23/10/2022 23:23:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[job_material](
	[id] [int] NULL,
	[pid] [int] NULL,
	[materialcode] [nvarchar](50) NULL,
	[boardname] [nvarchar](50) NULL,
	[materialgsm] [nvarchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[job] ([id], [pid], [description], [variant], [country], [pack], [customer], [artworkdate], [dierefernceno], [templateno]) VALUES (1, N'SP001', N'AB-CD-EF', N'TALL', N'SPAIN', N'24A', N'SONI', N'20/10/2022', N'1', N'2001A')
GO
INSERT [dbo].[job] ([id], [pid], [description], [variant], [country], [pack], [customer], [artworkdate], [dierefernceno], [templateno]) VALUES (2, N'SP002', N'GH-IJ-KL', N'SHORT', N'UK', N'24B', N'PANON', N'26/11/2013', N'2', N'2002B')
GO
INSERT [dbo].[job] ([id], [pid], [description], [variant], [country], [pack], [customer], [artworkdate], [dierefernceno], [templateno]) VALUES (3, N'SP003', N'MN-OP-QR', N'MEDIUM', N'USA', N'24C', N'LGE', N'20/9/2017', N'3', N'2003C')
GO
INSERT [dbo].[job] ([id], [pid], [description], [variant], [country], [pack], [customer], [artworkdate], [dierefernceno], [templateno]) VALUES (4, N'SP004', N'ST-UV-WX', N'TALL-SHORT', N'DENMARK', N'24D', N'HPA', N'18/6/2016', N'4', N'2004D')
GO
INSERT [dbo].[job_cylinder] ([id], [pid], [unit], [posino]) VALUES (1, 1, 1, N'A2300')
GO
INSERT [dbo].[job_cylinder] ([id], [pid], [unit], [posino]) VALUES (2, 1, 2, N'A2301')
GO
INSERT [dbo].[job_cylinder] ([id], [pid], [unit], [posino]) VALUES (3, 1, 3, N'A2302')
GO
INSERT [dbo].[job_cylinder] ([id], [pid], [unit], [posino]) VALUES (4, 1, 4, N'A2303')
GO
INSERT [dbo].[job_die] ([id], [dieno]) VALUES (1, N'D3900')
GO
INSERT [dbo].[job_die] ([id], [dieno]) VALUES (2, N'D4900')
GO
INSERT [dbo].[job_die] ([id], [dieno]) VALUES (3, N'D5900')
GO
INSERT [dbo].[job_die] ([id], [dieno]) VALUES (4, N'D6900')
GO
INSERT [dbo].[job_ink] ([id], [pid], [unit], [description], [inkcode]) VALUES (1, 1, 1, N'6700A', N'BC678')
GO
INSERT [dbo].[job_ink] ([id], [pid], [unit], [description], [inkcode]) VALUES (2, 1, 2, N'6700B', N'BD679')
GO
INSERT [dbo].[job_ink] ([id], [pid], [unit], [description], [inkcode]) VALUES (3, 1, 3, N'6700C', N'BD701')
GO
INSERT [dbo].[job_ink] ([id], [pid], [unit], [description], [inkcode]) VALUES (4, 1, 4, N'6700D', N'BD703')
GO
INSERT [dbo].[job_material] ([id], [pid], [materialcode], [boardname], [materialgsm]) VALUES (1, 1, N'SNG111', N'SILBLUE', N'G2000')
GO
INSERT [dbo].[job_material] ([id], [pid], [materialcode], [boardname], [materialgsm]) VALUES (2, 1, NULL, NULL, NULL)
GO
INSERT [dbo].[job_material] ([id], [pid], [materialcode], [boardname], [materialgsm]) VALUES (3, 2, N'SNG222', N'GLDRED', N'G3000')
GO
INSERT [dbo].[job_material] ([id], [pid], [materialcode], [boardname], [materialgsm]) VALUES (4, 2, NULL, NULL, NULL)
GO
INSERT [dbo].[job_material] ([id], [pid], [materialcode], [boardname], [materialgsm]) VALUES (5, 3, N'SNG333', N'BLKBLUE', N'G4000')
GO
INSERT [dbo].[job_material] ([id], [pid], [materialcode], [boardname], [materialgsm]) VALUES (6, 3, NULL, NULL, NULL)
GO
INSERT [dbo].[job_material] ([id], [pid], [materialcode], [boardname], [materialgsm]) VALUES (7, 4, N'SNG444', N'YLWGRY', N'G5000')
GO
INSERT [dbo].[job_material] ([id], [pid], [materialcode], [boardname], [materialgsm]) VALUES (8, 4, NULL, NULL, NULL)
GO

public partial class Default3 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
    }

    private DataTable GetData(SqlCommand cmd)
    {
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
        {
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;
            DataTable dt = new DataTable();
            sda.Fill(dt);

            return dt;
        }
    }

    protected void OnExport(object sender, EventArgs e)
    {
        SqlCommand cmd = new SqlCommand("SELECT id, pid,description,variant,country,pack,customer,artworkdate,templateno,dieno,materialcode,boardname,materialgsm,posi1=(select posino from job_Cylinder where unit='1'),inkcode1=(select inkcode from job_ink where unit='1'),description1=(select description from job_ink where unit='1')FROM(SELECT ROW_NUMBER() OVER(PARTITION BY j.id ORDER BY (j.id)) RowNo,j.pid,j.description,j.variant,j.country,j.pack,j.customer,j.artworkdate,j.templateno,d.dieno,m.materialcode,m.boardname,m.materialgsm,c.posino,i.inkcode,i.description from job j inner join job_die d on j.dierefernceno=d.id inner join job_material m on j.id = m.pid inner join job_cylinder c on j.id =c.pid inner join job_ink i on j.id=i.pid ) t WHERE t.RowNo = 1");
        DataTable dt1 = GetData(cmd);
        DataTable dt = new DataTable("Data");
        dt.Columns.AddRange(new DataColumn[]
        {
            new DataColumn("Pid"),
            new DataColumn(" Description"),
            new DataColumn("Variant"),
            new DataColumn("Country"),
            new DataColumn("Pack"),
            new DataColumn("Customer"),
            new DataColumn("Artworkdate"),
            new DataColumn("Templateno"),
            new DataColumn(" Die No"),
            new DataColumn("Materialcode"),
            new DataColumn("Boardname"),
            new DataColumn("Materialgsm"),
            new DataColumn("CYL POSI NO.1"),
            new DataColumn("INKCODE.1"),
            new DataColumn("INKDESCRTION.1")
        });
        using (XLWorkbook wb = new XLWorkbook())
        {
            foreach (DataRow row in dt1.Rows)
            {
                dt.Rows.Add();
                for (int j = 0; j < row.ItemArray.Length; j++)
                {
                    dt.Rows[dt.Rows.Count - 1][j] = row.ItemArray[j].ToString().Trim();
                }
            }

            var ws = wb.Worksheets.Add(dt);
            ws.Table(0).ShowAutoFilter = false;
            ws.Table(0).Theme = XLTableTheme.None;
            ws.Columns().AdjustToContents();
            ws.Row(1).Style.Font.Bold = true;
            Response.Clear();
            Response.Buffer = true;
            Response.Charset = "";
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("content-disposition", "attachment;filename=Data.xlsx");

            using (MemoryStream memoryStream = new MemoryStream())
            {
                wb.SaveAs(memoryStream);
                memoryStream.WriteTo(Response.OutputStream);
                Response.Flush();
                Response.End();
            }
        }
    }
}

Download FREE API for Word, Excel and PDF in ASP.Net

Shivam.M

0 3

Reply
ANSWER
User: Shivam.M
Replied: on Oct 27, 2022 05:16 AM
Modified: on Oct 27, 2022 05:18 AM
Report

Hi lingers,

Please refer below sample.

HTML

<asp:Button ID="btnExport" runat="server" Text="Export" OnClick="OnExport" />

Namespaces

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using ClosedXML.Excel;

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports ClosedXML.Excel

Code

protected void Page_Load(object sender, EventArgs e)
{
}

private DataTable GetData(SqlCommand cmd)
{
    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
    {
        SqlDataAdapter sda = new SqlDataAdapter(cmd);
        cmd.CommandType = CommandType.Text;
        cmd.Connection = con;
        DataTable dt = new DataTable();
        sda.Fill(dt);

        return dt;
    }
}

protected void OnExport(object sender, EventArgs e)
{
    string query = "SELECT id, pid,[description], variant, country, pack,customer,artworkdate,dierefernceno, templateno,dieno, materialcode, boardname, materialgsm ,ISNULL(posino, '') CYL, ISNULL(inkcode, '') inkcode,ISNULL([descript],'') INKDESC ";
    query += "FROM(Select ROW_NUMBER() OVER(PARTITION BY j.[pid] ORDER BY(j.[id])) RowNo,j.id, j.pid, j.[description], j.variant, j.country, j.pack, j.customer, j.artworkdate, j.dierefernceno, j.templateno,d.dieno, m.materialcode, m.boardname, m.materialgsm, c.posino, i.inkcode, i.[description][descript] From job j ";
    query += "inner join job_die d on j.id= d.id inner join job_material m on d.id = m.pid" +
        " LEFT OUTER join job_cylinder c on j.id = c.pid LEFT OUTER join job_ink i on j.id = i.pid)t WHERE t.RowNo = 1";
    SqlCommand cmd = new SqlCommand(query);
    DataTable dt = GetData(cmd);
    using (XLWorkbook wb = new XLWorkbook())
    {
        var ws = wb.Worksheets.Add(dt, "Table");
        ws.Table(0).ShowAutoFilter = false;
        ws.Table(0).Theme = XLTableTheme.None;
        ws.Columns().AdjustToContents();
        ws.Row(1).Style.Font.Bold = true;
        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "";
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        Response.AddHeader("content-disposition", "attachment;filename=Data.xlsx");

        using (MemoryStream memoryStream = new MemoryStream())
        {
            wb.SaveAs(memoryStream);
            memoryStream.WriteTo(Response.OutputStream);
            Response.Flush();
            Response.End();
        }
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
End Sub

Private Function GetData(ByVal cmd As SqlCommand) As DataTable
    Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
        Dim sda As SqlDataAdapter = New SqlDataAdapter(cmd)
        cmd.CommandType = CommandType.Text
        cmd.Connection = con
        Dim dt As DataTable = New DataTable()
        sda.Fill(dt)
        Return dt
    End Using
End Function

Protected Sub OnExport(ByVal sender As Object, ByVal e As EventArgs)
    Dim query As String = "SELECT id, pid,[description], variant, country, pack,customer,artworkdate,dierefernceno, templateno,dieno, materialcode, boardname, materialgsm ,ISNULL(posino, '') CYL, ISNULL(inkcode, '') inkcode,ISNULL([descript],'') INKDESC "
    query += "FROM(Select ROW_NUMBER() OVER(PARTITION BY j.[pid] ORDER BY(j.[id])) RowNo,j.id, j.pid, j.[description], j.variant, j.country, j.pack, j.customer, j.artworkdate, j.dierefernceno, j.templateno,d.dieno, m.materialcode, m.boardname, m.materialgsm, c.posino, i.inkcode, i.[description][descript] From job j "
    query += "inner join job_die d on j.id= d.id inner join job_material m on d.id = m.pid" & " LEFT OUTER join job_cylinder c on j.id = c.pid LEFT OUTER join job_ink i on j.id = i.pid)t WHERE t.RowNo = 1"
    Dim cmd As SqlCommand = New SqlCommand(query)
    Dim dt As DataTable = GetData(cmd)

    Using wb As XLWorkbook = New XLWorkbook()
        Dim ws = wb.Worksheets.Add(dt, "Table")
        ws.Table(0).ShowAutoFilter = False
        ws.Table(0).Theme = XLTableTheme.None
        ws.Columns().AdjustToContents()
        ws.Row(1).Style.Font.Bold = True
        Response.Clear()
        Response.Buffer = True
        Response.Charset = ""
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        Response.AddHeader("content-disposition", "attachment;filename=Data.xlsx")

        Using memoryStream As MemoryStream = New MemoryStream()
            wb.SaveAs(memoryStream)
            memoryStream.WriteTo(Response.OutputStream)
            Response.Flush()
            Response.End()
        End Using
    End Using
End Sub

Screenshot

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

Export data from multiple tables to Excel in ASP.Net using C# and VB.Net

Company

Explore

Follow us

Contact Us