If you want to convert Dataset in to excel there are two ways
1-By using GridView
2- You have to convert your dataset in to datable
3- If you want to export file save in a particular folder and download automatically
First I show you using a GridView method I personally don't like this because it is not good for heavy data but you can control to marked color any row like I need to marked red if it meet my some condition we can't do this on by using datable method.
Make a function pass Dataset and file name
1-By using GridView
public void ExportToExcel(DataSet ds, string fileName)
{
//Create a dummy GridView
GridView GridView1 = new GridView();
GridView1.AllowPaging = false;
GridView1.DataSource = ds.Tables(0);
GridView1.DataBind();
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Buffer = true;
for (int i = 0; i <= GridView1.Rows.Count - 1; i++) {
string status = GridView1.Rows(i).Cells(10).Text;
if (!(status == "Active")) {
GridView1.Rows(i).BackColor = Drawing.Color.Red;
GVReport.Rows(i).ForeColor = Drawing.Color.White;
} else {
}
}
HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment;filename={0}", fileName));
HttpContext.Current.Response.Charset = "";
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Unicode; --Multilingual text in Excel
HttpContext.Current.Response.BinaryWrite(System.Text.Encoding.Unicode.GetPreamble()); -- Multilingual text in Excel
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
for (int i = 0; i <= GridView1.Rows.Count - 1; i++) {
//Apply text style to each Row
GridView1.Rows(i).Attributes.Add("class", "textmode");
}
GridView1.RenderControl(hw);
//style to format numbers to string
string style = "<style> .textmode{mso-number-format:\\@;}</style>";
HttpContext.Current.Response.Write(style);
string headerTable = "<table width='100%' class='TestCssStyle'><tr><td><h4>Report Date/Time</h4> </td><td></td><td><h4>" + DateTime.Now.ToString() + "</h4></td></tr></table>";
HttpContext.Current.Response.Write(headerTable);
HttpContext.Current.Response.Output.Write(sw.ToString());
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}
2- You have to convert your dataset in to datable
DataSet ds = new DataSet();
-- Populate a dataset from database
ExportIntoExcelRowCol(ds.Tables(0)); -- by doing so you can convert your dataset into datable, ExportIntoExcelRowCol is a function passing datatable
private void ExportIntoExcelRowCol(DataTable dt)
{
try {
string attachment = "attachment; filename=Product Stock Ledger.xls";
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/vnd.ms-excel";
string tab = "";
foreach (DataColumn dc in dt.Columns) {
Response.Write(tab + dc.ColumnName);
tab = Constants.vbTab;
}
Response.Write(Constants.vbLf);
int o = 0;
foreach (DataRow dr in dt.Rows) {
tab = "";
for (o = 0; o <= dt.Columns.Count - 1; o++) {
Response.Write(tab + dr(o).ToString());
tab = Constants.vbTab;
}
Response.Write(Constants.vbLf);
}
Response.End();
} catch (Exception ex) {
}
}
3- If you want to export file save in a particular folder and download automatically
public void ExportToExcel(DataTable dt)
{
string filename = "BATCH PRINTER REPORT(" + DateTime.Now.ToString("yyyyMMddhhmmsstt") + ").xls";
string fpath = System.Configuration.ConfigurationManager.AppSettings("ExcelFilePath");
dynamic Combine_Path = (Convert.ToString(fpath) + filename);
string attachment = "attachment; filename=" + filename + "";
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/vnd.ms-excel";
string tab = "";
StreamWriter wr = new StreamWriter(Combine_Path);
try {
for (int i = 0; i <= dt.Columns.Count - 1; i++) {
wr.Write(dt.Columns(i).ToString().ToUpper() + Constants.vbTab);
Response.Write(dt.Columns(i).ToString().ToUpper() + Constants.vbTab);
tab = Constants.vbTab;
}
wr.WriteLine();
Response.Write(Constants.vbLf);
//write rows to excel file
for (int i = 0; i <= (dt.Rows.Count) - 1; i++) {
for (int j = 0; j <= dt.Columns.Count - 1; j++) {
if (dt.Rows(i)(j) != null) {
wr.Write(Convert.ToString(dt.Rows(i)(j)) + Constants.vbTab);
Response.Write(Convert.ToString(dt.Rows(i)(j)) + Constants.vbTab);
tab = Constants.vbTab;
} else {
wr.Write(Constants.vbTab);
}
}
//go to next line
wr.WriteLine();
Response.Write(Constants.vbLf);
}
//close file
wr.Close();
Response.End();
} catch (Exception ex) {
throw ex;
}
}
