Write data In Excel

Html Code






Code behind page

protected void Page_Load(object sender, EventArgs e)
{
string HospitalData = "Query";

string Servicesdata = "Query2";

hospital = SqlHelper.ExecuteDataset(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString(), CommandType.Text, HospitalData);
Services = SqlHelper.ExecuteDataset(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString(), CommandType.Text, Servicesdata);
DataTable HDT = hospital.Tables[0];
DataTable SDT = Services.Tables[0];
DataTable PrintRecord = CreateDataTable();
PrintRecord.Clear();

foreach (DataRow dr in HDT.Rows)
{

DataRow objDR = PrintRecord.NewRow();
objDR["Hospital"] = dr["Hospital"];
objDR["Address"] = dr["Address"];
objDR["City"] = dr["City"];
objDR["State"] = dr["State"];
objDR["Phone"] = dr["Phone"];
objDR["Fax"] = dr["Fax"];
objDR["Email"] = dr["Email"];
objDR["Website"] = dr["Website"];

string ser = string.Empty;
foreach (DataRow dr1 in SDT.Rows)
{
if(dr1["PropertyID"].ToString().Trim() == dr["PropertyID"].ToString().Trim())
{
ser = ser + dr1["Service"].ToString() + "
";
//DataRow objDR1 = PrintRecord.NewRow();
//objDR1["Services"] = dr1["Service"].ToString();
//PrintRecord.Rows.Add(objDR1);
}
}
objDR["Services"] = ser.ToString();
PrintRecord.Rows.Add(objDR);
//DataGrid DataGrid1 = new DataGrid();
DataGrid1.DataSource = PrintRecord;
DataGrid1.DataBind();


string filename = dr["Hospital"].ToString().Trim().Replace(" ", "_").Replace(".", "").Replace("\'", "").Replace("(", " ").Replace(")", "").Replace("/", "").Replace("\\", "").Replace("'", "");

this.DataGrid1.Page.EnableViewState = false;
StringWriter tw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(tw);
this.DataGrid1.RenderControl(hw);
string HtmlInfo = tw.ToString().Trim();

string DocFileName = filename + ".xls";
string FilePathName = Request.PhysicalPath;
FilePathName = FilePathName.Substring(0, FilePathName.LastIndexOf("\\"));

FilePathName = FilePathName + "\\Excal" + "\\" + DocFileName;
File.Delete(FilePathName);
FileStream Fs = new FileStream(FilePathName, FileMode.Create);
BinaryWriter BWriter = new BinaryWriter(Fs, Encoding.GetEncoding("UTF-8"));

BWriter.Write(HtmlInfo);
BWriter.Close();
Fs.Close();

//Write Excal File and save.

PrintRecord.Clear();
}
}
// structure of datatable

public static DataTable CreateDataTable()
{
DataTable dt = new DataTable();
DataColumn dc;
dc = new DataColumn("Hospital");
dt.Columns.Add(dc);
dc = new DataColumn("Services");
dt.Columns.Add(dc);
dc = new DataColumn("Address");
dt.Columns.Add(dc);
dc = new DataColumn("City");
dt.Columns.Add(dc);
dc = new DataColumn("State");
dt.Columns.Add(dc);
dc = new DataColumn("Phone");
dt.Columns.Add(dc);
dc = new DataColumn("Fax");
dt.Columns.Add(dc);
dc = new DataColumn("Email");
dt.Columns.Add(dc);
dc = new DataColumn("Website");
dt.Columns.Add(dc);
return dt;
}