First, solve the problem. Then, write the code.: Create excel file

Monday 23 January 2017

Create excel file

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];
}

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)