Export data from database to an existing excel sheet using ASP.NET

Question

User68828652 posted

I have an Template Folder in which i have an Excel Sheet. I want to extract data from database and export it to Excel Sheet. Every time i click Export a copy of the Excel sheet in Template folder should be made and the data should be exported to the copy of the excel sheet and the sheet with data should get downloaded. Can this be done in C# ASP.Net ??

Answer 1

User1120430333 posted

https://www.codebyamir.com/blog/create-excel-files-in-c-sharp

You can certainly use one the Excel frameworks. It's a question of how you are going to query the database to get the data.

Answer 2

User1724605321 posted

Hi ayushagarwal181,

You can also use the EPPlus package, which you can install via Nuget. It allows you to load data onto an Excel worksheet directly from your datatable, and write back to client for downloading like :

System.Web.HttpResponse response = System.Web.HttpContext.Current.Response;
  response.Clear();
  response.Buffer = true;
  response.Charset = "";
  response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
  response.AddHeader("content-disposition", "attachment;filename=ExcelData.xlsx");
  response.BinaryWrite(package.GetAsByteArray());

In mvc ,you can try below code sample :

https://stackoverflow.com/a/40523078/5751404 

Or you can use OpenXml library :

https://www.aspsnippets.com/Articles/Create-and-download-Excel-file-in-ASPNet.aspx 

Best Regards,

Nan Yu

Answer 3

User1341756031 posted

for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
     for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
     {
         data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
         xlWorkSheet.Cells[i + 1, j + 1] = data;
     }
}

Check this...export to excel