You must Sign In to post a response.
  • Category: ASP.NET

    How to Solve this issue Excel

    hi

    How to Solve this Excel Export issue i have export gridview data to excel
    sheet my code like this

    My Code
    ========
    Response.Clear();
    Response.Buffer = true;
    Response.AddHeader("content-disposition",
    "attachment;filename=GridViewExport.xls");
    Response.Charset = "";
    Response.ContentType = "application/vnd.ms-excel";
    StringWriter sw = new StringWriter();
    HtmlTextWriter hw = new HtmlTextWriter(sw);
    gr1.AllowPaging = false;
    gr1.DataBind();
    gr1.RenderControl(hw);
    Response.Output.Write(sw.ToString());
    Response.Flush();
    Response.End();


    Please check my Excel Sheet image after export
  • #681031
    check this image
    Name : Dotnet Developer-2015
    Email Id : kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

    Delete Attachment

  • #681033
    For this problem please see resources here is the url of the data export to excel sheet.

    http://www.dotnetspider.com/resources/36196-how-export-gridview-data-excel-sheet.aspx


    Hope this will help you.

  • #681035
    Hi Jaya Kumar
    I guess this will helpful to you

    protected void btnexportexcel_Click(object sender, ImageClickEventArgs e)
    {
    try
    {
    Response.Clear();
    Response.Buffer = true;

    Response.AddHeader("content-disposition",
    "attachment;filename=GridViewExport.xls");
    Response.Charset = "";
    Response.ContentType = "application/vnd.ms-excel";
    StringWriter sw = new StringWriter();
    HtmlTextWriter hw = new HtmlTextWriter(sw);

    grdemployeework.AllowPaging = false;
    //grdemployeework.DataBind();

    //Change the Header Row back to white color
    grdemployeework.HeaderRow.Style.Add("background-color", "#FFFFFF");

    //Apply style to Individual Cells
    grdemployeework.HeaderRow.Cells[0].Style.Add("background-color", "#27a6cb");
    grdemployeework.HeaderRow.Cells[1].Style.Add("background-color", "#27a6cb");
    grdemployeework.HeaderRow.Cells[2].Style.Add("background-color", "#27a6cb");
    grdemployeework.HeaderRow.Cells[3].Style.Add("background-color", "#27a6cb");
    grdemployeework.HeaderRow.Cells[4].Style.Add("background-color", "#27a6cb");
    grdemployeework.HeaderRow.Cells[5].Style.Add("background-color", "#27a6cb");
    grdemployeework.HeaderRow.Cells[6].Style.Add("background-color", "#27a6cb");
    grdemployeework.HeaderRow.Cells[7].Style.Add("background-color", "#27a6cb");

    grdemployeework.HeaderRow.Cells[0].Style.Add("color", "#ffffff");
    grdemployeework.HeaderRow.Cells[1].Style.Add("color", "#ffffff");
    grdemployeework.HeaderRow.Cells[2].Style.Add("color", "#ffffff");
    grdemployeework.HeaderRow.Cells[3].Style.Add("color", "#ffffff");
    grdemployeework.HeaderRow.Cells[4].Style.Add("color", "#ffffff");
    grdemployeework.HeaderRow.Cells[5].Style.Add("color", "#ffffff");
    grdemployeework.HeaderRow.Cells[6].Style.Add("color", "#ffffff");
    grdemployeework.HeaderRow.Cells[7].Style.Add("color", "#ffffff");




    //grdemployeework.HeaderRow.Cells[6].Style.Add("background-color", "green");

    for (int i = 0; i < grdemployeework.Rows.Count; i++)
    {
    GridViewRow row = grdemployeework.Rows[i];

    //Change Color back to white
    row.BackColor = System.Drawing.Color.White;

    //Apply text style to each Row
    row.Attributes.Add("class", "textmode");

    //Apply style to Individual Cells of Alternating Row
    if (i % 2 != 0)
    {
    row.Cells[0].Style.Add("background-color", "#98d4e6");
    row.Cells[1].Style.Add("background-color", "#98d4e6");
    row.Cells[2].Style.Add("background-color", "#98d4e6");
    row.Cells[3].Style.Add("background-color", "#98d4e6");
    row.Cells[4].Style.Add("background-color", "#98d4e6");
    row.Cells[5].Style.Add("background-color", "#98d4e6");
    row.Cells[6].Style.Add("background-color", "#98d4e6");

    row.Cells[0].Style.Add("border", "1px solid black");
    row.Cells[1].Style.Add("border", "1px solid black");
    row.Cells[2].Style.Add("border", "1px solid black");
    row.Cells[3].Style.Add("border", "1px solid black");
    row.Cells[4].Style.Add("border", "1px solid black");
    row.Cells[5].Style.Add("border", "1px solid black");
    row.Cells[6].Style.Add("border", "1px solid black");
    }
    else
    {
    row.Cells[0].Style.Add("background-color", "#98d4e6");
    row.Cells[1].Style.Add("background-color", "#98d4e6");
    row.Cells[2].Style.Add("background-color", "#98d4e6");
    row.Cells[3].Style.Add("background-color", "#98d4e6");
    row.Cells[4].Style.Add("background-color", "#98d4e6");
    row.Cells[5].Style.Add("background-color", "#98d4e6");
    row.Cells[6].Style.Add("background-color", "#98d4e6");

    row.Cells[0].Style.Add("border", "1px solid black");
    row.Cells[1].Style.Add("border", "1px solid black");
    row.Cells[2].Style.Add("border", "1px solid black");
    row.Cells[3].Style.Add("border", "1px solid black");
    row.Cells[4].Style.Add("border", "1px solid black");
    row.Cells[5].Style.Add("border", "1px solid black");
    row.Cells[6].Style.Add("border", "1px solid black");
    }
    }
    grdemployeework.RenderControl(hw);

    //style to format numbers to string
    string style = @"<style> .textmode { mso-number-format:\@; } </style>";
    Response.Write(style);
    Response.Output.Write(sw.ToString());
    Response.Flush();
    Response.End();
    }
    catch (Exception mesg) { }
    }

    Thanks in Advance
    Satish Madugundu

  • #681036
    hi try like this
    Response.Clear();
    Response.Charset = "";
    Response.ContentType = "application/vnd.ms-excel";
    Response.AppendHeader("content-disposition", "attachment; filename=" + FileName + ".xls");

    System.IO.StringWriter sw = new System.IO.StringWriter();
    System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(sw);

    GridView gv = new GridView();
    gv.DataSource = dSet.Tables[TableIndex];
    gv.DataBind();
    gv.RenderControl(hw);

    Response.Write(sw.ToString());
    Response.End();

    Rayala HariKishore

    try..try..try...you achieved it.
    http://rayalaharikishore.wordpress.com/

  • #681039
    You can use the below code to export data from Grid to excel.


    Response.Clear();
    Response.AddHeader("content-disposition", "attachment;filename=FileName.xls");
    Response.Charset = "";
    Response.Cache.SetCacheability(HttpCacheability.NoCache);
    Response.ContentType = "application/Pandey.xls";

    System.IO.StringWriter stringWrite = new System.IO.StringWriter();
    System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

    myDataGrid.RenderControl(htmlWrite);
    Response.Write(stringWrite.ToString());
    Response.End();

    Thanks & Regards
    Anil Kumar Pandey
    Microsoft MVP, DNS MVM

  • #681063
    1. Open your Registry (Start -> Run -> regedit.exe)
    2. Navigate to HKEY_CURRENT_USER\SOFTWARE\MICROSOFT\OFFICE\12.0\EXCEL\SECURITY
    3. Right click in the right window and choose New -> DWORD
    4. Type "ExtensionHardening" as the name (without the quotes)
    5. Verify that the data has the value "0?


    Ref:
    http://support.microsoft.com/kb/948615
    http://stackoverflow.com/questions/940045/how-to-suppress-the-file-corrupt-warning-at-excel-download

    SQL Server Programmers and Consultants

  • #681090
    Hai Jaya,
    There could be any issue like access permission to write the data into the excel sheet.
    Also there could be issue that you are trying to export in .xls and you have not installed the MS-Excel 2003 so try to check the compatibility issues.
    Hope it will be helpful to you.

    Regards,
    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
    pawansoftit@gmail.com

  • #681093
    You may use Microsfot Excel x.x Object Library COM component to export DataTable to Excel. The DataTable you are using to bind the GridView should be used to export to Excel.

    Add a reference of Microsfot Excel x.x Object Library COM component and use the following code:



    public string ConvertToExcel(DataTable dt)
    {
    string FilePath;
    Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
    ExcelApp.Application.Workbooks.Add(Type.Missing);

    Microsoft.Office.Interop.Excel.Worksheet Sheet1 = (Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[1];

    for (int i = 0; i < dt.Columns.Count; i++)
    {
    Sheet1.Cells[1, i + 1] = dt.Columns[i].ColumnName;
    }

    for (int i = 0; i < dt.Rows.Count; i++)
    {
    for (int j = 0; j < dt.Columns.Count; j++)
    {
    Sheet1.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString();
    }
    }


    FilePath = "d:\\" + Guid.NewGuid() + ".xls";
    if (FilePath != string.Empty)
    {
    ExcelApp.ActiveWorkbook.SaveAs(FilePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel5, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);

    ExcelApp.ActiveWorkbook.Saved = true;
    ExcelApp.Quit();
    }
    return FilePath;
    }


    Thanks & Regards
    Paritosh Mohapatra
    Microsoft MVP (ASP.Net/IIS)
    DotNetSpider MVM

  • #681137
    This is the safty warning given by Excel when ship some data to excel without using excel interop API.
    if you want to avoid this error then you can change the Registry value


    1. Start -> Run -> regedit.exe
    2. find given key "HKEY_CURRENT_USER\SOFTWARE\MICROSOFT\OFFICE\12.0\EXCEL\SECURITY"
    3. choose New -> DWORD by Right click in right side window
    4. Type "ExtensionHardening" (without the quotes)
    5. Put the data value is "0"


    hope it helps

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]


  • This thread is locked for new responses. Please post your comments and questions as a separate thread.
    If required, refer to the URL of this page in your new post.