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

Question

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

abhijit dhanwate · Answer

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 Satasiya · Answer

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 dhanwate · Answer

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 Satasiya · Answer

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 Gohil · Answer

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 R · Answer

Hi, https://www.c-sharpcorner.com/UploadFile/0c1bb2/export-gridview-to-excel/

Sachin Singh · Answer

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 { tblPivot.Columns.Add(Convert.ToString(i)); } for ( int col = 0; col { var r = tblPivot.NewRow(); r[0] = tbl.Columns[col].ToString(); for ( int j = 1; 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

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