Export data SQL to Excel

আমরা যদি Dadabase(SQL) থেকে Excel এ Data export করতে চাই তবে নিচের Method Button Click  এ লিখলে .csv file এ export হবে।

এখানে নির্দিষ্ট range এর ভিতর data export দেখান হল।

protected void ButtonExport_Click(object sender, EventArgs e)

{

 int startId = 0;

int.TryParse(StartId.Text, out startId);/*Text Box Text Start*/

int endId = 0;

int.TryParse(EndId.Text, out endId);/*Text Box Text End*/

List<string> listString = new List<string>();

MemberCollection memberColl = new MemberController().FetchAll();/*Table এর সব data নিয়ে আসবে*/

listString.Add("Id," + "Email," + "FirstName," + "LastName," + "Country,");

foreach (Member member in memberColl)

 {

int memberId = member.Id;

if (memberId >= startId && memberId <= endId)

{

listString.Add(member.Id.ToString() + "," + member.Email + ","

+ member.FirstName + "," + member.LastName + "," + member.Country);

}

}

ExportTestResult(listString, "report");

}

private void ExportTestExcel(List<string> report, string downloadFileName)

{

downloadFileName = downloadFileName.Replace(" ", "-");

string fileName = Server.MapPath("~/") + Guid.NewGuid().ToString() + ".csv";

using (StreamWriter sw = new StreamWriter(fileName))

 {

foreach (string s in report)

{

sw.WriteLine(s);

}

sw.Flush();

                }

                using (StreamReader sr = new StreamReader(fileName))

                {

Response.Clear();

Response.AddHeader("Content-Disposition", "attachment; filename=" + downloadFileName + ".csv");

Response.Write(sr.ReadToEnd());

Response.ContentType = "text/csv";

Response.Flush();

Response.End();

}

 File.Delete(fileName);

}

সব শেষে server এ তৈরি হওয়া file টি আমরা delete করে ফেলব।