Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have a text file which is in tab deliminator and following is my code to generate its Excel.

C#
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

C#
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!!!
Posted
Updated 5-Dec-12 3:40am
v2
Comments
ZurdoDev 5-Dec-12 9:53am    
Your code worked in Excel 2003 because Excel 2003 could open that format but the file was not in Excel format. It can read text files, html, etc, as well as Excel files. The 2007 format is entirely different. Your code can still work for 2007, just leave the extension as .xls and 2007 can open it.
prahalad.gaggar 5-Dec-12 10:01am    
I see, then what is the correct way to convert a text (tab deliminator) to an excel (.xlsx) file!!!
very very well said sir!!!
actually i could have solve the problem just by renaming the text file abc.txt to abc.xls but i created a data-table for it!!!
prahalad.gaggar 6-Dec-12 9:08am    
@nyab31 please check my answer!!!
i think now i am generating a valid Excel(.xlsx) file!!!

You have to use extended libraries which I recommend using EPPlus which is a .net library that reads & writes Excel 2007/2010 files using the Open Office Xml format (xlsx). http://epplus.codeplex.com

and then replace the code

C#
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("."));
            sFilename = sFilename + ".xlsx";
            MemoryStream ms = DataTableToExcelXlsx(dt, "Sheet1");
            ms.WriteTo(HttpContext.Current.Response.OutputStream);
            HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + sFilename);
            HttpContext.Current.Response.StatusCode = 200;
            HttpContext.Current.Response.End();
        }


C#
public void toexcel(DataTable dt, string Filename)
        {
            MemoryStream ms = DataTableToExcelXlsx(dt, "Sheet1");
            ms.WriteTo(HttpContext.Current.Response.OutputStream);
            HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + Filename);
            HttpContext.Current.Response.StatusCode = 200;
            HttpContext.Current.Response.End();
        }
        public bool IsReusable
        {
            get { return false; }
        }
        public static MemoryStream DataTableToExcelXlsx(DataTable table, string sheetName)
        {
            MemoryStream Result = new MemoryStream();
            ExcelPackage pack = new ExcelPackage();
            ExcelWorksheet ws = pack.Workbook.Worksheets.Add(sheetName);
            int col = 1;
            int row = 1;
            foreach (DataColumn column in table.Columns)
            {
                ws.Cells[row, col].Value = column.ColumnName.ToString();
                col++;
            }
            col = 1;
            row = 2;
            foreach (DataRow rw in table.Rows)
            {
                foreach (DataColumn cl in table.Columns)
                {
                    if (rw[cl.ColumnName] != DBNull.Value)
                        ws.Cells[row, col].Value = rw[cl.ColumnName].ToString();
                    col++;
                }
                row++;
                col = 1;
            }
            pack.SaveAs(Result);
            return Result;
        }


I got this solution here http://forums.asp.net/p/1863741/5230401.aspx/1?Unable+to+generate+the+xlsx+file+
 
Share this answer
 
v2
Try this coding in any button control ...this coding surely helpful for u....


 // creating Excel Application

 Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
 // creating Excel Application
 Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);

 //Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();


 Microsoft.Office.Interop.Excel.Worksheet objSheet = new Microsoft.Office.Interop.Excel.Worksheet();
 //Microsoft.Office.Interop.Excel.Workbook objWorkBook = null;


 // see the excel sheet behind the program

app.Visible = true;


// get the reference of first sheet. By default its name is Sheet1.

 // store its reference to worksheet
 objSheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets["Sheet1"];
 objSheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.ActiveSheet;


 // changing the name of active sheet

 objSheet.Name = "Exported from gridview";


 // storing header part in Excel

 ////for (int j = 1; j < dataGridView1.Columns.Count + 1; j++)
 ////{

 ////    objSheet.Cells[1, j] = dataGridView1.Columns[j - 1].HeaderText;

 ////}


 // storing Each row and column value to excel sheet

 for (int k = 0; k < dataGridView1.Rows.Count - 1; k++)
 {

     for (int l = 0; l< dataGridView1.Columns.Count; l++)
     {

         objSheet.Cells[k + 1, l + 1] = dataGridView1.Rows[k].Cells[l].Value.ToString();

     }

 }



// save the application

// workbook.SaveAs(@"C:\\Book1.xml", Microsoft.Office.Interop.Excel.XlFileFormat.xlXMLSpreadsheet, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

 }



or try below link:

http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.workbook.saveas(v=vs.80).aspx[^]
 
Share this answer
 
v2
Comments
prahalad.gaggar 6-Dec-12 8:36am    
@Deenuji: your code is too untidy!!!
also is your code returning xlsx file???
Even your "For" loops are not working as "dataGridView1.Rows.Count" is not valid!!!
prahalad.gaggar 6-Dec-12 8:51am    
Very Untidy code, also takes a lot time to generate the excel file!!!
10 - 15 secs just to enter 800 records!!!
I edited the code:

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("."));
sFilename = sFilename + ".xlsx";
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel.Worksheet objSheet = new Microsoft.Office.Interop.Excel.Worksheet();
Microsoft.Office.Interop.Excel.Workbook objWorkBook = null;
app.Visible = true;
objSheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets["Sheet1"];
objSheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.ActiveSheet;
objSheet.Name = "Sheet1";
int col = 1;
int row = 1;
foreach (DataColumn column in dt.Columns)
{
objSheet.Cells[row, col] = column.ColumnName.ToString();
col++;
}
col = 1;
row = 2;
foreach (DataRow rw in dt.Rows)
{
foreach (DataColumn cl in dt.Columns)
{
if (rw[cl.ColumnName] != DBNull.Value)
objSheet.Cells[row, col] = rw[cl.ColumnName].ToString();
col++;
}
row++;
col = 1;
}
workbook.SaveAs(sFilename, Microsoft.Office.Interop.Excel.XlFileFormat.xlXMLSpreadsheet, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}

Also i don't know how to auto save the result!!!
Total Waste, this code won't work in real world!!!

Better to use my solution. (I am not saying this because i found the solution, but after comparing your result with mine i came to this conclusion)

No hard Feelings bro!!!
And I appreciate your help!!!
Keep Smiling :)
Deenuji 6-Dec-12 8:54am    
k sorry ...here after am using ur code for save excel files.....
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using Microsoft.Office;


namespace Littleflower
{

public partial class Filter : Form
{
SqlCommand cmd;
SqlConnection con;
SqlDataAdapter da;
SqlDataReader dr;
DataSet ds;
//public enum XlSaveAsAccessMode
//public enum XlSaveConflictResolution
public Filter()
{
InitializeComponent();
con = new SqlConnection("Data Source=FABSYS27\\SQLEXPRESS; Initial Catalog=Questionare; User Id=sa; Pwd=saadmin");


}

private void Form1_Load(object sender, EventArgs e)
{

con.Open();
cmd = new SqlCommand("select distinct(subjectname) from subject", con);
dr = cmd.ExecuteReader();
comboBox1.Items.Clear();
while (dr.Read())
{
if (dr[0].ToString() != "")
{
comboBox1.Items.Add(dr[0].ToString());
}
}
dr.Close();
con.Close();

con.Open();
string a = "";
SqlCommand c = new SqlCommand("delete from questiontype where questions='" + a + "'", con);
c.ExecuteNonQuery();
con.Close();
con.Open();
String strSQL = "select q.questions from subject s ,questiontype q where q.subjectid=s.subjectid ";

SqlDataAdapter dataAdapter = new SqlDataAdapter(strSQL, con);

SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);



// Populate a new data table and bind it to the BindingSource.

DataTable table = new DataTable();

table.Locale = System.Globalization.CultureInfo.InvariantCulture;

dataAdapter.Fill(table);

dbBindSource.DataSource = table;

// finally bind the data to the grid

dataGridView1.DataSource = dbBindSource;


con.Close();

}






private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{

}

private void comboBox3_SelectedIndexChanged(object sender, EventArgs e)
{

}


private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{

}



private void comboBox1_SelectedIndexChanged_1(object sender, EventArgs e)
{

con.Open();
String strSQL = "select q.questions from subject s ,questiontype q where q.subjectid=s.subjectid and s.subjectname='" + comboBox1.SelectedItem.ToString() + "' ";

SqlDataAdapter dataAdapter = new SqlDataAdapter(strSQL, con);

SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);



// Populate a new data table and bind it to the BindingSource.

DataTable table = new DataTable();

table.Locale = System.Globalization.CultureInfo.InvariantCulture;

dataAdapter.Fill(table);

dbBindSource.DataSource = table;

// finally bind the data to the grid

dataGridView1.DataSource = dbBindSource;


con.Close();

con.Open();
cmd = new SqlCommand("select distinct (topic) from subject where subjectname='" + comboBox1.SelectedItem + "' ", con);
dr = cmd.ExecuteReader();
comboBox3.Items.Clear();
while (dr.Read())
{
if (dr[0].ToString() != "")
{
comboBox3.Items.Add(dr[0].ToString());
}
}
dr.Close();
con.Close();


}



private void comboBox3_SelectedIndexChanged_1(object sender, EventArgs e)
{
con.Open();
String strSQL = "select q.questions from subject s ,questiontype q where q.subjectid=s.subjectid and s.subjectname='" + comboBox1.SelectedItem.ToString() + "' and s.topic='" + comboBox3.SelectedItem.ToString() + "' ";

SqlDataAdapter dataAdapter = new SqlDataAdapter(strSQL, con);

SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);



// Populate a new data table and bind it to the BindingSource.

DataTable table = new DataTable();

table.Locale = System.Globalization.CultureInfo.InvariantCulture;

dataAdapter.Fill(table);

dbBindSource.DataSource = table;

// finally bind the data to the grid

dataGridView1.DataSource = dbBindSource;


con.Close();
con.Open();
cmd = new SqlCommand("select distinct (mark) from subject where subjectname='"+comboBox1.SelectedItem+"' and topic='"+comboBox3.SelectedItem+"'", con);
dr = cmd.ExecuteReader();
comboBox2.Items.Clear();
while (dr.Read())
{
if (dr[0].ToString() != "")
{
comboBox2.Items.Add(dr[0].ToString());
}
}
dr.Close();

con.Close();

}

private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
{
con.Open();
String strSQL = "select q.questions from subject s ,questiontype q where q.subjectid=s.subjectid and s.subjectname='" + comboBox1.SelectedItem.ToString() + "'and s.topic='" + comboBox3.SelectedItem.ToString() + "' and s.mark=" + comboBox2.SelectedItem.ToString() + " ";

SqlDataAdapter dataAdapter = new SqlDataAdapter(strSQL, con);

SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);



// Populate a new data table and bind it to the BindingSource.

DataTable table = new DataTable();

table.Locale = System.Globalization.CultureInfo.InvariantCulture;

dataAdapter.Fill(table);

dbBindSource.DataSource = table;

// finally bind the data to the grid

dataGridView1.DataSource = dbBindSource;


con.Close();

}

private void comboBox4_SelectedIndexChanged(object sender, EventArgs e)
{
con.Open();
try
{

String strSQL = "SELECT TOP " + comboBox4.SelectedItem + " q.questions FROM questiontype q, subject s where q.subjectid=s.subjectid and s.subjectname='" + comboBox1.SelectedItem.ToString() + "'and s.topic='" + comboBox3.SelectedItem.ToString() + "' and s.mark=" + comboBox2.SelectedItem.ToString() + " ORDER BY newid()";

SqlDataAdapter dataAdapter = new SqlDataAdapter(strSQL, con);

SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);



// Populate a new data table and bind it to the BindingSource.

DataTable table = new DataTable();

table.Locale = System.Globalization.CultureInfo.InvariantCulture;

dataAdapter.Fill(table);

dbBindSource.DataSource = table;

// finally bind the data to the grid

dataGridView1.DataSource = dbBindSource;



}
catch (Exception)
{
MessageBox.Show("Select filters...");
}
con.Close();
}

private void panel1_Paint(object sender, PaintEventArgs e)
{

}

private void button1_Click(object sender, EventArgs e)
{
// Saving in database

string a = "";
con.Open();
SqlCommand c = new SqlCommand("delete from QuestionPaper", con);
c.ExecuteNonQuery();
int i = 0;
while (dataGridView1.RowCount > i)
{
cmd = new SqlCommand("INSERT INTO QuestionPaper VALUES(" + dataGridView1.Rows[i].HeaderCell.Value + ",'" + dataGridView1.Rows[i].Cells[0].Value + "')", con);
cmd.ExecuteNonQuery();



i++;

}
SqlCommand c1 = new SqlCommand("delete from QuestionPaper where Qvalues='"+a+"'", con);
c1.ExecuteNonQuery();
con.Close();
MessageBox.Show("Submitted Successfully");
////this.Close();



// creating Excel Application

Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
// creating Excel Application
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);

//Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();


Microsoft.Office.Interop.Excel.Worksheet objSheet = new Microsoft.Office.Interop.Excel.Worksheet();
//Microsoft.Office.Interop.Excel.Workbook objWorkBook = null;


// see the excel sheet behind the program

app.Visible = true;


// get the reference of first sheet. By default its name is Sheet1.

// store its reference to worksheet
objSheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets["Sheet1"];
objSheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.ActiveSheet;


// changing the name of active sheet

objSheet.Name = "Exported from gridview";


// storing header part in Excel

////for (int j = 1; j < dataGridView1.Columns.Count + 1; j++)
////{

//// objSheet.Cells[1, j] = dataGridView1.Columns[j - 1].HeaderText;

////}


// storing Each row and column value to excel sheet

for (int k = 0; k < dataGridView1.Rows.Count - 1; k++)
{

for (int l = 0; l< dataGridView1.Columns.Count; l++)
{

objSheet.Cells[k + 1, l + 1] = dataGridView1.Rows[k].Cells[l].Value.ToString();

}

}



// save the application

// workbook.SaveAs(@"C:\\Book1.xml", Microsoft.Office.Interop.Excel.XlFileFormat.xlXMLSpreadsheet, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

}

private void dataGridView1_DataBindingComplete(object sender, DataGridViewBindingCompleteEventArgs e)
{

if (null != dataGridView1)
{
foreach (DataGridViewRow r in dataGridView1.Rows)
{
dataGridView1.Rows[r.Index].HeaderCell.Value = (r.Index + 1).ToString();
}
}
}

// private void button2_Click(object sender, EventArgs e)
// {
// // Saving in database

// string a = "";
// con.Open();
// SqlCommand c = new SqlCommand("delete from QuestionPaper", con);
// c.ExecuteNonQuery();
// int i = 0;
// while (dataGridView1.RowCount > i)
// {
// cmd = new SqlCommand("INSERT INTO QuestionPaper VALUES(" + dataGridView1.Rows[i].HeaderCell.Value + ",'" + dataGridView1.Rows[i].Cells[0].Value + "')", con);
// cmd.ExecuteNonQuery();
// i++;
// }
//SqlCommand c1 = new SqlCommand("delete from QuestionPaper where Qvalues='" + a + "'", con);
// c1.ExecuteNonQuery();
// con.Close();

// LittleFlower.ReportDocument reportdoc = new LittleFlower.ReportDocument();
// reportdoc.Show();

// }


}

}


this my full coding part....this code still running in my project....
 
Share this answer
 
Comments
prahalad.gaggar 6-Dec-12 9:00am    
Doesn't it takes time if you have a data-table with more than 500 records!!!
prahalad.gaggar 6-Dec-12 9:06am    
your code cannot be deployed deployed on cloud, because office automation is illegal on asp.net, for that u have to use share-point!!!
which is very tedious!!!
Deenuji 6-Dec-12 9:10am    
k bro:) thanks for ur comment:)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900