Click here to Skip to main content
15,902,938 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want to export my gridview to excel using c# when i am exporting with Microsoft.office.intreop then in my image column System.Drawing.image is coming or when i have done with Response.Header then my images are coming but if image size is more than my image is coming out of row.


What I have tried:

<pre>   protected void Unnamed_Click(object sender, EventArgs e)
        {
            System.Data.DataTable dts = new System.Data.DataTable();
            dts.Columns.Add("UserId", typeof(Int32));
            dts.Columns.Add("UserName", typeof(string));
            dts.Columns.Add("Education", typeof(string));
            dts.Columns.Add("Imagepath", typeof(Image));
          //  context.Response.BinaryWrite((Byte[])[ismg.]);
            dts.Rows.Add(1, "Suresh Dasari", "B.Tech", GetImageFromFile("~/images/EC.png"));
            dts.Rows.Add(2, "Suresh Dasarsi", "B.Tech", GetImageFromFile("~/images/EC.png"));
            Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing);
            Microsoft.Office.Interop.Excel._Worksheet worksheet = null;

            try
            {
                worksheet = workbook.ActiveSheet;

                worksheet.Name = "ExportedFromDatGrid";

                int cellRowIndex = 1;
                int cellColumnIndex = 1;
                ////Loop through each row and read value from each column. 
                for (int i = 0; i < dts.Rows.Count; i++)
                {
                    for (int j = 0; j < dts.Columns.Count; j++)
                    {
                        //// Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check. 
                        if (cellRowIndex == 1)
                        {
                            worksheet.Cells[cellRowIndex, cellColumnIndex] =dts.Columns[j].ColumnName;
                            worksheet.Cells[cellRowIndex, cellColumnIndex].Font.FontStyle = FontStyle.Bold;
                        }
                        else
                        {
                            worksheet.Cells[cellRowIndex, cellColumnIndex] =dts.Rows[i][j].ToString();
                        }

                        cellColumnIndex++;
                    }

                    cellColumnIndex = 1;
                    cellRowIndex++;
                }

                worksheet.Columns.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
                worksheet.Columns.AutoFit();

                ////Getting the location and file name of the excel to save from user. 
                workbook.SaveAs("ss.xls", XlFileFormat.xlExcel12, System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, false, XlSaveAsAccessMode.xlShared, XlSaveConflictResolution.xlLocalSessionChanges, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value, false);
            }
            catch (System.Exception ex)
            {
               // MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1);
            }
            finally
            {
                excel.Quit();
                workbook = null;
                excel = null;
            }
        }

 private static Image GetImageFromFile(string fileName)
        {
            string path = fileName;
            //check the existence of the file in disc
            if (File.Exists(path))
            {
                Image image =new  Image();
                image.Url = path;
                return image;
            }
            else
                return null;
        }
Posted
Updated 4-Feb-18 20:24pm

1 solution

Use the Below Code

C#

protected  string GetUrl(string imagepath)

{

    string[] splits = Request.Url.AbsoluteUri.Split('/');

    if (splits.Length  >= 2)

    {

        string url = splits[0] + "//";

        for (int i = 2; i < splits.Length - 1; i++)

        {

            url += splits[i];

            url += "/";

        }

        return url +  imagepath;

    }

    return imagepath;

}


<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns = "false"

Font-Names = "Arial" >

<Columns>

    <asp:BoundField DataField = "ID" HeaderText = "ID"

     ItemStyle-Height = "150" />

    <asp:BoundField DataField = "FileName" HeaderText = "Image Name"

     ItemStyle-Height = "150"/>

    <asp:TemplateField  ItemStyle-Height = "150" ItemStyle-Width = "170">

        <ItemTemplate>

            <asp:Image ID="Image1" runat="server"

             ImageUrl = '<%# Eval("FilePath", GetUrl("{0}")) %>' />

        </ItemTemplate>

    </asp:TemplateField>

</Columns>

</asp:GridView>

Export to Excel Code

private void Excel_Export()

{

    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);

    GridView1.AllowPaging = false;

    GridView1.DataBind();

    for (int i = 0; i < GridView1.Rows.Count; i++)

    {

        GridViewRow row = GridView1.Rows[i];

        //Apply text style to each Row

        row.Attributes.Add("class", "textmode");

    }

    GridView1.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();

}


Link for Source Code:
https://www.aspsnippets.com/Articles/Export-GridView-with-Images-to-Word-Excel-and-PDF-Formats-in-ASP.Net.aspx
 
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