Click here to Skip to main content
11,492,523 members (65,040 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: ASP.NET C#3.5
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 11-Feb-10 0:51am
Edited 11-Feb-10 12:18pm
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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:

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:

 GridView gvExcel = new GridView();
//Fill gvExcel
 DataGridToExcel.Export("FileToSave.xls", gvExcel);
  Permalink  
Comments
Dalek Dave at 25-Aug-10 6:21am
   
Very Comprehensive Answer!
arunachallam at 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
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

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.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

Ilya Builuk

Thank You very very much for solution 1. I tried here and the results was amazing!!!!!!!
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 6

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
  Permalink  
Comments
soeun tony at 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
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 8

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

Solution 9

Hi You Can Refer this URL.....
http://www.exceleverywhere.com/excel-asp-net.htm[^]
  Permalink  

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 454
1 Maciej Los 266
2 KarstenK 210
3 CPallini 208
4 Sascha Lefèvre 190
0 Sergey Alexandrovich Kryukov 10,062
1 OriginalGriff 8,620
2 Sascha Lefèvre 3,609
3 Maciej Los 3,372
4 Richard Deeming 2,500


Advertise | Privacy | Mobile
Web01 | 2.8.150520.1 | Last Updated 28 May 2015
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