Need to show Data in excel format using c#, Asp.net.

abhijit dhanwate

I have a data fetched from sql server in c# and Need to show in excel in specific format. Below is the data.

Hatch	Deck	Orgzn	Num
H1	A	9J	37
H1	A	AY	105
H1	A	KO	12
H1	A	KV	68
H2	A	ZL	62
H2	A	UU	20
H4	A	DO	281
H4	A	SA	95
H1	B	1Z	69
H1	B	7V	49
H3	B	K0	20
H4	B	1Z	69
H4	B	7V	49
H4	B	K0	20
.	.	.	.
.	.	.	.
.	.	.	.
N	N	N	N

Need to show in below format in Excel sheet using c# . Trying from long time but not succeed please help.

	H1		H2		H3		H4		.	N
	Orgzn	Num	Orgzn	Num	Orgzn	Num	Orgzn	Num		N
A	9J	37	ZL	62			DO	281	.	N
	AY	105	UU	20			SA	95	.	N
	KO	12							.	N
	KV	68							.	N
	Orgzn	Num	Orgzn	Num	Orgzn	Num	Orgzn	Num	.	N
B	1Z	69			K0	20	1Z	69	.	N
	7V	49					7V	49	.	N
							K0	20	.	N

.
.
.
N

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

abhijit dhanwate5y ago
Please find required excel sheet format. here H1,H2 row has..... n number of values and A,B column has.... n number of values.
Nikunj Satasiya5y ago
Hi Abhijit, Can you please attach your original and expected output in the proper format so I can understand your actual data format and may I can help you.

Currently, In your posted question I cant identify proper data. You should give your original and expected sample data in a proper tabular manner.

Thank you.
abhijit dhanwate5y ago
Thanks Sachin,Nikunj,Ajay,Mageshwaran for your suggestion and guidance, But for me issue is to diaplay the data in given format in Excel. The format is too important for me.
Nikunj Satasiya5y ago
Hi Abhijit, You can visit these articles, where each and every point are explained in detail, which may help you to resolve your query.

https://www.codingvila.com/2019/12/export-gridview-in-excel-using-jquery.html
https://www.codingvila.com/2018/12/export-json-data-to-excel-csv-file-using-angular-js-with-using-bootstrap.html
Ajay Gohil5y ago
Hello,

Refer the following code:
// Create connection string variable. Modify the "Data Source"
// parameter as appropriate for your environment.
String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + Server.MapPath("../ExcelData.xls") + ";" +
"Extended Properties=Excel 8.0;";
// Create connection object by using the preceding connection string.
OleDbConnection objConn = new OleDbConnection(sConnectionString);
// Open connection with the database.
objConn.Open();
// The code to follow uses a SQL SELECT command to display the data from the worksheet.
// Create a new OleDbCommand to return data from the worksheet.
OleDbCommand objCmdSelect =new OleDbCommand("SELECT * FROM myRange1", objConn);
// Create new OleDbDataAdapter that is used to build a DataSet
// based on the preceding SQL SELECT statement.
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
// Pass the Select command to the adapter.
objAdapter1.SelectCommand = objCmdSelect;
// Create new DataSet to hold information from the worksheet.
DataSet objDataset1 = new DataSet();
// Fill the DataSet with the information from the worksheet.
objAdapter1.Fill(objDataset1, "XLData");
// Bind data to DataGrid control.
DataGrid1.DataSource = objDataset1.Tables[0].DefaultView;
DataGrid1.DataBind();
// Clean up objects.
objConn.Close();
Refer to this article:

https://www.c-sharpcorner.com/article/export-and-import-excel-file/
https://www.aspsnippets.com/Articles/Read-and-display-data-from-an-excel-file-xls-and-xlsx-in-ASPNet.aspx
https://www.syncfusion.com/blogs/post/6-easy-ways-to-export-data-to-excel-in-c-sharp.aspx
https://docs.microsoft.com/en-us/troubleshoot/dotnet/csharp/query-excel-data-aspx-page
Mageshwaran R5y ago
Hi,
https://www.c-sharpcorner.com/UploadFile/0c1bb2/export-gridview-to-excel/
Sachin Singh5y ago
your requirement is very simple , as you really need a simple pivot after grouping them

Fetch your SQL Server data into a datatable and pivote it and then export as excel
public void PullData()
    {
        string connString = @"your connection string here";
        string query = "select Hatch,Deck,Orgzn, Sum(Num) from table group by Hatch,Deck,Orgzn,Num;

using(SqlConnection conn = new SqlConnection(connString))
    {
        SqlCommand cmd = new SqlCommand(query, conn);
        conn.Open();
      DataTable dt= new DataTable();
        // create data adapter
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        // this will query your database and return the result to your datatable
        da.Fill(dt);
    var mydt=    PivoteDataTable(dt);
using (XLWorkbook wb = new XLWorkbook())
                    {
                        wb.Worksheets.Add(mydt, "myTable");

                        Response.Clear();
                        Response.Buffer = true;
                        Response.Charset = "";
                        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                        Response.AddHeader("content-disposition", "attachment;filename=SqlExport.xlsx");
                        using (MemoryStream MyMemoryStream = new MemoryStream())
                        {
                            wb.SaveAs(MyMemoryStream);
                            MyMemoryStream.WriteTo(Response.OutputStream);
                            Response.Flush();
                            Response.End();
                        }
                    }
                }
       }
    }
public static class PivoteDataTable
{

    public static DataTable Pivot(this DataTable tbl)
    {
        var tblPivot = new DataTable();
        tblPivot.Columns.Add(tbl.Columns[0].ColumnName);
        for (int i = 1; i < tbl.Rows.Count; i++)
        {
            tblPivot.Columns.Add(Convert.ToString(i));
        }
        for (int col = 0; col < tbl.Columns.Count; col++)
        {
            var r = tblPivot.NewRow();
            r[0] = tbl.Columns[col].ToString();
            for (int j = 1; j < tbl.Rows.Count; j++)
                r[j] = tbl.Rows[j][col];

            tblPivot.Rows.Add(r);
        }
        return tblPivot;
    }
}
Notice :- to export as excel i have used closedXML library which you can downlod from nuget or
https://www.microsoft.com/en-in/download/details.aspx?id=5124