I have a text file which is in tab deliminator and following is my code to generate its Excel.
protected void to_excel(object sender, EventArgs e)
{
string filepath = Path.Combine(Server.MapPath("~/Files"), fileupload.FileName);
fileupload.SaveAs(filepath);
string fname = fileupload.PostedFile.FileName;
DataTable dt = (DataTable)ReadToEnd(filepath);
string sFilename = fname.Substring(0, fname.IndexOf("."));
HttpResponse response = HttpContext.Current.Response;
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=" + sFilename + ".xls");
Response.Charset = "";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new System.Web.UI.HtmlTextWriter(stringWrite);
System.Web.UI.WebControls.DataGrid dg = new System.Web.UI.WebControls.DataGrid();
dg.DataSource = dt;
dg.DataBind();
dg.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();
}
private object ReadToEnd(string filePath)
{
DataTable dtDataSource = new DataTable();
string[] fileContent = File.ReadAllLines(filePath);
if (fileContent.Count() > 0)
{
string[] columns = fileContent[0].Split('\t');
for (int i = 0; i < columns.Count(); i++)
{
dtDataSource.Columns.Add(columns[i]);
}
for (int i = 1; i < fileContent.Count(); i++)
{
string[] rowData = fileContent[i].Split('\t');
dtDataSource.Rows.Add(rowData);
}
}
return dtDataSource;
}
This code works fine since i am generating 2003 excel file (.xls).
But if i am generating a 2007 (.xlsx) by changing the code to
Response.AddHeader("content-disposition", "attachment;filename=" + sFilename + ".xlsx");
i get an error like this http://s11.postimage.org/i2xt9tonn/error.jpg
I did my homework and came to know that this error is because the .xlsx file generated by my program is done by using HTML (markup language) XML (markup language) which should actually be done for a 2007 excel file.
My question is what changes should i do so that i get the desired result i.e. I get the 2007 excel sheet!!!