Click here to Skip to main content
15,867,304 members
Please Sign up or sign in to vote.
4.75/5 (4 votes)
See more:
I am working on an ASP.NET project (using C#).

In which I want to open a blank Excel file in front of user at run time, so that the user can create a Column according to his requirements ...

After creating column he can also able to fill data in that Excel file..

Finally, on clicking the save button (which will be on that .aspx page only) that Excel file should be save on the hard disk.

Please tell me if any other solution for this type of requirement, so that the user can fill compatible just like he use Microsoft Excel.

Any solution to this is highly appreciable.

Thanks In Advance
Posted
Updated 11-Feb-10 11:18am
v2

You can create Excel file dynamically from GridView and save it on the hard disk. Perhaps your clients have enough GridView's functionality?
This is not quite accurate solution to your problem, but may also help to solve it:

C#
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using System.IO;
public static class DataGridToExcel
{
    public static void Export(string fileName, GridView gv)
    {
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.AddHeader(
        "content-disposition", string.Format("attachment; filename={0}", fileName));
        HttpContext.Current.Response.ContentType = "application/ms-excel";
        HttpContext.Current.Response.Write("<head><meta http-equiv=Content-Type content=:" + '"' + "text/html; charset=utf-8" + '"' + "></head>");


        using (StringWriter sw = new StringWriter())
        {
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))
            {
                Table table = new Table();

                if (gv.HeaderRow != null)
                {
                    PrepareControlForExport(gv.HeaderRow);
                    table.Rows.Add(gv.HeaderRow);
                }

                foreach (GridViewRow row in gv.Rows)
                {
                    PrepareControlForExport(row);
                    table.Rows.Add(row);
                }

                if (gv.FooterRow != null)
                {
                    PrepareControlForExport(gv.FooterRow);
                    table.Rows.Add(gv.FooterRow);
                }
                table.GridLines = gv.GridLines;
                table.RenderControl(htw);

                HttpContext.Current.Response.Write(sw.ToString());
                HttpContext.Current.Response.End();
            }
        }
    }

    private static void PrepareControlForExport(Control control)
    {
        for (int i = 0; i < control.Controls.Count; i++)
        {
            Control current = control.Controls[i];
            if (current is LinkButton)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
            }
            else if (current is ImageButton)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
            }
            else if (current is HyperLink)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
            }
            else if (current is DropDownList)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
            }
            else if (current is CheckBox)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
            }

            if (current.HasControls())
            {
                PrepareControlForExport(current);
            }
        }
    }
}


Using:

C#
 GridView gvExcel = new GridView();
//Fill gvExcel
 DataGridToExcel.Export("FileToSave.xls", gvExcel);
 
Share this answer
 
Comments
Dalek Dave 25-Aug-10 6:21am    
Very Comprehensive Answer!
arunachallam 5-Oct-12 11:22am    
hi when i use this code i am getting an error like this when i opening the excel its saying that excel is not in a proper format stil you want to open when i select yes i am not getting any field or any data in grid
sharadcs05 wrote:
In which I want to open a blank excel file in front of user at run time,So that user can create Column according to his requirement..after creating column he can also able to fill data in that excel file..
Na Finally on the click of save button(which will be on that .aspx page only) that excel file should be save on the hard disk...


Dream on.

The main way you could do this, is to let the user add rows to a datagrid, then allow them to download either a file created as an Excel file on the server using office interop, or just a csv, which Excel can open.
 
Share this answer
 
Hello,

I used this solution to send a file to the users.

The problem is when the page make the postback. the event of the button is never fired.

I mean..The first time the button is fired with the onclick() Event and the server send it the file to the user. Then the second time the Event Onclick() is never fired or is not catched for the Server.

Any body knows the reason of this?

Thank you.
 
Share this answer
 
Ilya Builuk

Thank You very very much for solution 1. I tried here and the results was amazing!!!!!!!
 
Share this answer
 
Ilya Builuck

Thank you very much for the solution - it works flawlessly.

Had only one problem - Excel 2010 shows a warning that the file type and contents don't match. However it does open it correctly. Does it have something to do with the header? Don't know enough about xls filetypes to hazard a guess.

Thank you again for a solution that works excellently except for the minor irritant...

Cheers
 
Share this answer
 
Comments
soeun tony 27-Sep-13 0:30am    
hi sbrahma,
i pasted this code in my project, it display red under line below. it means that the code is error. here is the code error:
sbTop.Append("<html xmlns:o="\"urn:schemas-microsoft-com:office:office\"" xmlns:x="\"urn:schemas-microsoft-com:office:excel\"" hold=" /> sbTop.Append(" xmlns="\"http://www.w3.org/TR/REC-html40\""><head><meta http-equiv="Content-Type" content="\"text/html;" charset="windows-1252\"">");
sbTop.Append("<meta name="ProgId" content="Excel.Sheet"><meta name="Generator" content="\"Microsoft" excel="" mode="hold" /> string bottom = "</meta></meta></head></html>";
Thanks
public string DataTable2ExcelString(DataTable dt)
    {
        StringBuilder sbTop = new StringBuilder();
        sbTop.Append("<html xmlns:o="\"urn:schemas-microsoft-com:office:office\"" xmlns:x="\"urn:schemas-microsoft-com:office:excel\"" hold=" />        sbTop.Append(" xmlns="\"http://www.w3.org/TR/REC-html40\""><head><meta http-equiv="Content-Type" content="\"text/html;" charset="windows-1252\"">");
        sbTop.Append("<meta name="ProgId" content="Excel.Sheet"><meta name="Generator" content="\"Microsoft" excel="" mode="hold" />        string bottom = "</meta></meta></head></html>";
        StringBuilder sb = new StringBuilder();
        //Header
        sb.Append("");
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            sb.Append("" + dt.Columns[i].ColumnName + "");
        }
        sb.Append("");

        //Items
        for (int x = 0; x < dt.Rows.Count; x++)
        {
            sb.Append("");
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                sb.Append("" + dt.Rows[x][i] + "");
            }
            sb.Append("");
        }

        string SSxml = sbTop.ToString() + sb.ToString() + bottom;

        return SSxml;
    }


    protected void btndwn_Click1(object sender, EventArgs e)
    {
        try
        {
            string strBody = DataTable2ExcelString(dtDPS);
            Response.AppendHeader("Content-Type", "application/vnd.ms-excel");
            Response.AppendHeader("Content-disposition", "attachment; filename=MyBooking.xls");
            Response.Write(strBody);
        }
        catch (Exception _ex)
        {
            throw;
        }
    }
 
Share this answer
 
v2
Hi You Can Refer this URL.....
http://www.exceleverywhere.com/excel-asp-net.htm[^]
 
Share this answer
 

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