var employees = new[]{
new{ Id="101", Name="Vivek",
Address="Hyderabad" },
new{ Id="102", Name="Ranjeet",
Address="Hyderabad" },
new{ Id="103", Name="Sharath",
Address="Hyderabad" },
new{ Id="104", Name="Ganesh",
Address="Hyderabad" },
new{ Id="105", Name="Gajanan",
Address="Hyderabad" },
new{ Id="106", Name="Ashish",
Address="Hyderabad" }
};
string excelName = "employees";
Response.ClearContent();
Response.AddHeader("content-disposition",
"attachment;filename=" + excelName + ".xls");
Response.AddHeader("Content-Type", "application/vnd.ms-excel");
//Header for table records
//
Response.Write("Id");
Response.Write("\t");
Response.Write("Name");
Response.Write("\t");
Response.Write("Address");
Response.Write("\t");
Response.Write("\n");
//Body for table records
//
foreach (var employee in employees)
{
Response.Write(employee.Name);
Response.Write("\t");
Response.Write(employee.Id);
Response.Write("\t");
Response.Write(employee.Address);
Response.Write("\t");
Response.Write("\n");
}
Response.End();
//Using Gride View
=====================
public void ExportToExcel(string FileName, dynamic datalist)
{
GridView gv = new GridView();
gv.DataSource = datalist;
gv.DataBind();
Response.ClearContent();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=" + FileName + ".xls");
Response.Charset = "";
Response.ContentType = "application/ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
//gv.Font.Bold = false;
//gv.Font.Size = 14;
gv.RenderControl(htw);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
===
In Asp .net
================
public void DownloadDatabase(string AccountType, int AccountID)
{
UserLoginDl objlogindl = new UserLoginDl();
DataSet ds = objlogindl.Get_ReportRecord(AccountType, AccountID);
if (ds != null && ds.Tables.Count > 0)
{
#region tbl
AccountType = AccountType.ToUpper();
if (AccountType == "SUPERADMIN")
{
ds.Tables[0].TableName = "CountryManager";
ds.Tables[1].TableName = "Ministry وزارة";
ds.Tables[2].TableName = "MinistryAdmin منسق وزرارة";
ds.Tables[3].TableName = "EduDistrict منطقة تعليمية";
ds.Tables[4].TableName = "School مدرسة";
ds.Tables[5].TableName = "Supervisor مشرف";
ds.Tables[6].TableName = "Student طالب";
}
else if (AccountType == "COUNTRYMANAGER")
{
ds.Tables[0].TableName = "Ministry وزارة";
ds.Tables[1].TableName = "MinistryAdmin منسق وزرارة";
ds.Tables[2].TableName = "EduDistrict منطقة تعليمية";
ds.Tables[3].TableName = "School مدرسة";
ds.Tables[4].TableName = "Supervisor مشرف";
ds.Tables[5].TableName = "Student طالب";
}
else if (AccountType == "MINISTRY")
{
ds.Tables[0].TableName = "MinistryAdmin منسق وزرارة";
ds.Tables[1].TableName = "EduDistrict منطقة تعليمية";
ds.Tables[2].TableName = "School مدرسة";
ds.Tables[3].TableName = "Supervisor مشرف";
ds.Tables[4].TableName = "Student طالب";
}
else if (AccountType == "MINISTRYADMIN")
{
ds.Tables[0].TableName = "EduDistrict منطقة تعليمية";
ds.Tables[1].TableName = "School مدرسة";
ds.Tables[2].TableName = "Supervisor مشرف";
ds.Tables[3].TableName = "Student طالب";
}
else if (AccountType == "EDUCATIONALDISTRICT")
{
ds.Tables[0].TableName = "School مدرسة";
ds.Tables[1].TableName = "Supervisor مشرف";
ds.Tables[2].TableName = "Student طالب";
}
else if (AccountType == "SCHOOL")
{
ds.Tables[0].TableName = "Supervisor مشرف";
ds.Tables[1].TableName = "Student طالب";
}
else if (AccountType == "SUPERVISOR")
{
ds.Tables[0].TableName = "Student طالب";
}
#endregion
using (XLWorkbook wb = new XLWorkbook())
{
foreach (DataTable dt in ds.Tables)
{
//Add DataTable as Worksheet.
wb.Worksheets.Add(dt);
}
//Export the Excel file.
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.Charset = "";
HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=DataBase_" + DateTime.Now.ToString("dd-MM-yyyy") + "_" + AccountType + ".xlsx");
using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(HttpContext.Current.Response.OutputStream);
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}
}
}
}
public void NewDownloadDatabase(string AccountType,string levelname, int AccountID)
{
UserLoginDl objlogindl = new UserLoginDl();
DataSet ds = objlogindl.Get_NewReportRecord(AccountType, levelname, AccountID);
if (ds != null && ds.Tables.Count > 0)
{
string sheetname = "";
if (levelname == "countrymanager")
{
sheetname = "CountryManager سكرتاريا";
}
else if (levelname == "ministry")
{
sheetname = "Ministry وزارة";
}
else if (levelname == "ministryadmin")
{
sheetname = "MinistryAdmin منسق وزرارة";
}
else if (levelname == "disticmanager")
{
sheetname = "EduDistrict منطقة تعليمية";
}
else if (levelname == "school")
{
sheetname = "School مدرسة";
}
else if (levelname == "suppervisor")
{
sheetname = "Supervisor مشرف";
}
else if (levelname == "student")
{
sheetname = "Student طالب";
}
using (XLWorkbook wb = new XLWorkbook())
{
foreach (DataTable dt in ds.Tables)
{
//Add DataTable as Worksheet.
wb.Worksheets.Add(dt);
}
//Export the Excel file.
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.Charset = "";
HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=DataBase_" + DateTime.Now.ToString("dd-MM-yyyy") + "_" + sheetname + ".xlsx");
using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(HttpContext.Current.Response.OutputStream);
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}
}
//string attachment = "attachment; filename=DataBase_" + DateTime.Now.ToString("dd-MM-yyyy") + "_" + sheetname + ".xls";
//HttpContext.Current.Response.ClearContent();
//HttpContext.Current.Response.AddHeader("content-disposition", attachment);
//HttpContext.Current.Response.ContentType = "application/ms-excel";
//HttpContext.Current.Response.Charset = "utf-8";
//HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Unicode;
//HttpContext.Current.Response.BinaryWrite(System.Text.Encoding.Unicode.GetPreamble());
//string tab = "";
//foreach (DataColumn dc in ds.Tables[0].Columns)
//{
// HttpContext.Current.Response.Write(tab + dc.ColumnName);
// tab = "\t";
//}
//HttpContext.Current.Response.Write("\n");
//int i;
//foreach (DataRow dr in ds.Tables[0].Rows)
//{
// tab = "";
// for (i = 0; i < ds.Tables[0].Columns.Count; i++)
// {
// HttpContext.Current.Response.Write(tab + dr[i].ToString());
// tab = "\t";
// }
// HttpContext.Current.Response.Write("\n");
//}
//HttpContext.Current.Response.End();
// ExporttoExcel(ds.Tables[0], attachment);
}
}
public void DateRangeDownloadDatabase(string AccountType, string levelname, int AccountID, DateTime fromdate, DateTime todate)
{
UserLoginDl objlogindl = new UserLoginDl();
DataSet ds = objlogindl.Get_DateRangeReportRecord(AccountType, levelname, AccountID, fromdate, todate);
if (ds != null && ds.Tables.Count > 0)
{
string sheetname = "";
if (levelname == "countrymanager")
{
sheetname = "CountryManager سكرتاريا";
}
else if (levelname == "ministry")
{
sheetname = "Ministry وزارة";
}
else if (levelname == "ministryadmin")
{
sheetname = "MinistryAdmin منسق وزرارة";
}
else if (levelname == "disticmanager")
{
sheetname = "EduDistrict منطقة تعليمية";
}
else if (levelname == "school")
{
sheetname = "School مدرسة";
}
else if (levelname == "suppervisor")
{
sheetname = "Supervisor مشرف";
}
else if (levelname == "student")
{
sheetname = "Student طالب";
}
using (XLWorkbook wb = new XLWorkbook())
{
foreach (DataTable dt in ds.Tables)
{
//Add DataTable as Worksheet.
wb.Worksheets.Add(dt);
}
//Export the Excel file.
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.Charset = "";
HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=DataBase_" + DateTime.Now.ToString("dd-MM-yyyy") + "_" + sheetname + ".xlsx");
using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(HttpContext.Current.Response.OutputStream);
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}
}
//string attachment = "attachment; filename=DataBase_" + DateTime.Now.ToString("dd-MM-yyyy") + "_" + sheetname + ".xls";
//HttpContext.Current.Response.ClearContent();
//HttpContext.Current.Response.AddHeader("content-disposition", attachment);
//HttpContext.Current.Response.ContentType = "application/ms-excel";
//HttpContext.Current.Response.Charset = "utf-8";
//HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Unicode;
//HttpContext.Current.Response.BinaryWrite(System.Text.Encoding.Unicode.GetPreamble());
//string tab = "";
//foreach (DataColumn dc in ds.Tables[0].Columns)
//{
// HttpContext.Current.Response.Write(tab + dc.ColumnName);
// tab = "\t";
//}
//HttpContext.Current.Response.Write("\n");
//int i;
//foreach (DataRow dr in ds.Tables[0].Rows)
//{
// tab = "";
// for (i = 0; i < ds.Tables[0].Columns.Count; i++)
// {
// HttpContext.Current.Response.Write(tab + dr[i].ToString());
// tab = "\t";
// }
// HttpContext.Current.Response.Write("\n");
//}
//HttpContext.Current.Response.End();
// ExporttoExcel(ds.Tables[0], attachment);
}
}
private void ExporttoExcel(DataTable table,string Filename)
{
#region old code
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.ContentType = "application/ms-excel";
// Response.Write(@"<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0 Transitional//EN"">");
HttpContext.Current.Response.AddHeader("Content-Disposition", Filename);
HttpContext.Current.Response.Charset = "utf-8";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Unicode;
HttpContext.Current.Response.BinaryWrite(System.Text.Encoding.Unicode.GetPreamble());
// Response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1250");
//sets font
HttpContext.Current.Response.Write("<font style='font-size:12px; font-family:Calibri;'>");
//sets the table border, cell spacing, border color, font of the text, background, foreground, font height
HttpContext.Current.Response.Write("<Table bgColor='#ffffff' " + "cellSpacing='0' cellPadding='0' " + "style='font-size:12px; font-family:Calibri; background:white;'> <TR style='border-bottom: 1px solid white'>");
//am getting my grid's column headers
int columnscount = table.Columns.Count;
for (int j = 0; j < columnscount; j++)
{ //write in new column
HttpContext.Current.Response.Write("<Td style='background: gray; color:#fff; font-size:12px; width: 90px;'>");
//Get column headers and make it as bold in excel columns
HttpContext.Current.Response.Write("<B>");
HttpContext.Current.Response.Write(table.Columns[j].ToString());
HttpContext.Current.Response.Write("</B>");
HttpContext.Current.Response.Write("</Td>");
}
HttpContext.Current.Response.Write("</TR>");
foreach (DataRow row in table.Rows)
{//write in new row
HttpContext.Current.Response.Write("<TR>");
for (int i = 0; i < table.Columns.Count; i++)
{
HttpContext.Current.Response.Write("<Td>");
HttpContext.Current.Response.Write(row[i].ToString());
HttpContext.Current.Response.Write("</Td>");
}
HttpContext.Current.Response.Write("</TR>");
}
HttpContext.Current.Response.Write("</Table>");
HttpContext.Current.Response.Write("</font>");
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
//using exlcel4.5 dll and closed dll
//using (XLWorkbook wb = new XLWorkbook())
//{
// foreach (DataTable dt in ds.Tables)
// {
// //Add DataTable as Worksheet.
// wb.Worksheets.Add(dt);
// }
// //Export the Excel file.
// HttpContext.Current.Response.Clear();
// HttpContext.Current.Response.Buffer = true;
// HttpContext.Current.Response.Charset = "";
// HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
// HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=DataBase_" + DateTime.Now.ToString("dd-MM-yyyy") + "_" + AccountType + "_" + levelname + ".xlsx");
// using (MemoryStream MyMemoryStream = new MemoryStream())
// {
// wb.SaveAs(MyMemoryStream);
// MyMemoryStream.WriteTo(HttpContext.Current.Response.OutputStream);
// HttpContext.Current.Response.Flush();
// HttpContext.Current.Response.End();
// }
//}
//using GridView
//HttpContext.Current.Response.ClearContent();
//HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=" + sheetname + ".xls");
//HttpContext.Current.Response.ContentType = "application/excel";
// System.IO.StringWriter sw = new System.IO.StringWriter();
// HtmlTextWriter htw = new HtmlTextWriter(sw);
// GridView GridView1 = new GridView();
// GridView1.DataSource = ds.Tables[0];
// GridView1.DataBind();
// GridView1.RenderControl(htw);
// HttpContext.Current.Response.Write(sw.ToString());
// HttpContext.Current.Response.End();
#endregion
}
public DataTable Exceltodatatable(string FilePath, string Extension)
{
FileStream stream = File.Open(FilePath, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader;
if (Extension.ToLower() == ".xls")
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
else
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();
return result.Tables[0];
}
public DataTable ExceltodatatableImport(string FilePath, string Extension)
{
DataTable dt = new DataTable();
dt.Columns.Add("FirstName", typeof(string));
dt.Columns.Add("SecondName", typeof(string));
dt.Columns.Add("ThirdName", typeof(string));
dt.Columns.Add("FourthName", typeof(string));
dt.Columns.Add("Country", typeof(string));
dt.Columns.Add("Nationality", typeof(string));
dt.Columns.Add("Gender", typeof(string));
dt.Columns.Add("Grade", typeof(string));
dt.Columns.Add("Email", typeof(string));
FileStream stream = File.Open(FilePath, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader;
if (Extension.ToLower() == ".xls")
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
else
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();
// dt = result.Tables[0];
foreach (DataRow row in result.Tables[0].Rows)
{
DataRow dr = dt.NewRow();
dr["FirstName"] =row[0];
dr["SecondName"] =row[1];
dr["ThirdName"] =row[2];
dr["FourthName"] =row[3];
dr["Country"]=row[4];
dr["Nationality"]=row[5] ;
dr["Gender"]=row[6];
dr["Grade"]=row[7];
dr["Email"]=row[8];
dt.Rows.Add(dr);
}
return dt;
// return result.Tables[0];
}
