public partial class ExportInExcel : System.Web.UI.Page
{
System.Data.DataTable table = null;
protected void Page_Load(object sender, EventArgs e)
{
table = new System.Data.DataTable();
table.Columns.Add("Dosage", typeof(int));
table.Columns.Add("Drug", typeof(string));
table.Columns.Add("Patient", typeof(string));
table.Columns.Add("Date", typeof(DateTime));
table.Rows.Add(25, "Indocin", "Sam", DateTime.Now);
table.Rows.Add(27, "Indocin", "David", DateTime.Now);
table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
table.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
table.Rows.Add(23, "Combivent", "steav", DateTime.Now);
table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);
GridView1.DataSource = table;
GridView1.DataBind();
}
protected void Button2_Click(object sender, EventArgs e)
{
ExportTableDataAsCSV(this.Page);
}
public void ExportTableDataAsCSV(System.Web.UI.Page _page)
{
if (table != null && table.Rows.Count > 0)
{
string GridViewDataExportedFileInfo = "CSharpeDataTableExportedAsCSV_File_" + DateTime.Now.ToUniversalTime() + ".csv";
GridViewDataExportedFileInfo = GridViewDataExportedFileInfo.Replace(":", "_").Replace("/", "_").Replace(" ", "_");
StringBuilder sb = new StringBuilder();
string[] columnNames = table.Columns.Cast<DataColumn>().Select(column => column.ColumnName).ToArray();
var header = string.Join(",", columnNames);
sb.AppendLine(header);
//List<string> valueLines = table.AsEnumerable().Select(row => string.Join(",", row.ItemArray)).ToList();
//var rows = string.Join(Environment.NewLine, valueLines);
//sb.AppendLine(rows);
#region If above 3 lines not working as expected
foreach (DataRow dr in table.Rows)
{
foreach (DataColumn dc in table.Columns)
sb.Append(ValidateColumnData(dr[dc.ColumnName].ToString()) + ",");
sb.Remove(sb.Length - 1, 1);
sb.AppendLine();
}
#endregion
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ContentType = "application/octet-stream";
HttpContext.Current.Response.AddHeader("content-length", sb.Length.ToString());
HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + GridViewDataExportedFileInfo);
HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.NoCache);
HttpContext.Current.Response.Write(sb.ToString());
HttpContext.Current.Response.End();
}
else if (_page != null)
{
_page.ClientScript.RegisterStartupScript(this.GetType(), "No Data Alert Box.", "alert('There are no records to Download.');", true);
}
}
public static string ValidateColumnData(string input)
{
try
{
if (input == null)
return string.Empty;
bool isQuote = false;
bool isComma = false;
int len = input.Length;
for (int i = 0; i < len && (isComma == false || isQuote == false); i++)
{
char ch = input[i];
if (ch == '"')
isQuote = true;
else if (ch == ',')
isComma = true;
}
if (isQuote)
input = input.Replace("\"", "\"\"");
if (isComma)
return "\"" + input + "\"";
else
return input;
}
catch
{
throw new Exception(string.Format("Data Parsing Error: Column Data : {0}", input));
}
}
}
Watch The Video:
Hi, can I define a date in the query before exporting?
ReplyDeleteI say insert the date after login, as if it were a range of dates to extract a report looking for information in a SQL Azure database?
Hi Bruno,
DeleteYes, you can definately do that. I strongly suggest you to take my code from GitHub and do the sample Implementation according to your need.
Git Repo Link: https://github.com/TechWebDots/AspNetWebFormsApp.git
Your feedback is important.
Don't forget to Follow/Subscribe (if not yet done 🙂) & encourage me to create more such videos.
If you like these Blogs/Videos, please click on the THUMBS UP👍 and share with others where this might be helpful.
Happy Coding and Learning,
Good Luck TechWebDots!
hi,is there reference I should add to project to use using Microsoft.Office.Interop.Excel ?
ReplyDeleteHi ,
DeleteI strongly suggest you to take my code & Check all the required references from GitHub.
Git Repo Link: https://github.com/TechWebDots/AspNetWebFormsApp.git
Your feedback is important.
Don't forget to Follow/Subscribe (if not yet done 🙂) & encourage me to create more such videos.
If you like these Blogs/Videos, please click on the THUMBS UP👍 and share with others where this might be helpful.
Happy Coding and Learning,
Good Luck TechWebDots!
Nice job!
ReplyDeleteHi Mark,
DeleteThank you very much for taking time to give feedback. Your feedback is important.
Don't forget to Follow/Subscribe (if not yet done 🙂) & encourage me to create more such videos.
If you like these Blogs/Videos, please click on the THUMBS UP👍 and share with others where this might be helpful.
Happy Coding and Learning,
Good Luck TechWebDots!