Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C#3.0 ASP.NET Excel
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!!!
Posted 5-Dec-12 4:31am
Edited 5-Dec-12 4:40am
v2
Comments
ryanb31 at 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 at 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 at 6-Dec-12 9:08am
   
@nyab31 please check my answer!!!
i think now i am generating a valid Excel(.xlsx) file!!!
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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
 
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();
        }
 
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+
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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[^]
  Permalink  
v2
Comments
prahalad.gaggar at 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 at 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 :)
Deenu India at 6-Dec-12 8:54am
   
k sorry ...here after am using ur code for save excel files.....
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

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....
  Permalink  
Comments
prahalad.gaggar at 6-Dec-12 9:00am
   
Doesn't it takes time if you have a data-table with more than 500 records!!!
prahalad.gaggar at 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!!!
Deenu India at 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)

  Print Answers RSS
0 Zoltán Zörgő 330
1 Peter Leow 120
2 BillWoodruff 120
3 CHill60 110
4 bling 80
0 Sergey Alexandrovich Kryukov 9,423
1 OriginalGriff 6,891
2 Peter Leow 4,637
3 Zoltán Zörgő 4,304
4 CHill60 2,932


Advertise | Privacy | Mobile
Web02 | 2.8.150129.1 | Last Updated 6 Dec 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100