How to export data into Excel which is diaplayed using GridView in Visual Studio 2008

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Ewan
    New Member
    • Feb 2011
    • 18

    How to export data into Excel which is diaplayed using GridView in Visual Studio 2008

    Hi

    Im using Visual Studio 2008 and i have created a GridView which displays data from an SQL Table.

    Could anyone help me on how could i export this data into an Excel sheet on click of a button.

    Thanks
  • Ewan
    New Member
    • Feb 2011
    • 18

    #2
    I managed to get some answers after alot of googleing..
    i created a button and defined its ID as Export2XL

    In the Export2XL_Click event i pasted the following code
    Code:
        Protected Sub Export2XL_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles Export2XL.Click
    
            refGridView.Visible = True
    
            Response.Clear()
            Response.Buffer = True
            Response.ContentType = "application/vnd.ms-excel"
            Response.Charset = ""
            Me.EnableViewState = False
            Dim oStringWriter As New System.IO.StringWriter
            Dim oHtmlTextWriter As New System.Web.UI.HtmlTextWriter(oStringWriter)
    
            refGridView.RenderControl(oHtmlTextWriter)
    
            Response.Write(oStringWriter.ToString())
            Response.End()
    
        End Sub
    However i got an error message as below:
    "Control 'refGridView' of type 'GridView' must be placed inside a form tag with runat=server."

    So by further googleing i found that by adding the below code, it sorts out the issue:

    Code:
        Public Overloads Overrides Sub VerifyRenderingInServerForm(ByVal Control As Control)
    
            ' Verifies that the control is rendered 
    
        End Sub

    Comment

    • Frinavale
      Recognized Expert Expert
      • Oct 2006
      • 9749

      #3
      I'm glad you found a solution :)
      Thanks for sharing it with us

      Comment

      • Ewan
        New Member
        • Feb 2011
        • 18

        #4
        your Welcome :)
        ............... ............... ...........

        Comment

        • aspdotnetuser
          New Member
          • Nov 2010
          • 22

          #5
          Hi Ewan,

          With this render control option did you try a gridview with hyperlinkedtext columns and template columns? I think there it fails and the format in excel is incorrect.

          May be give a try with "DoddleRepo rt" available @ http://doddlereport.codeplex.com/.

          Comment

          • Landy67
            New Member
            • Feb 2012
            • 3

            #6
            Hello,
            It is not difficult to export data to Excel by using GirdView.
            I want to share a simple example with you.
            Code:
                    private void button1_Click(object sender, EventArgs e)
            
                    {
            
                        Workbook workbook = new Workbook();
            
                        //Initialize worksheet
            
                        workbook.CreateEmptySheets(1);
            
                        Worksheet sheet = workbook.Worksheets[0];
            
                        //Insert DataTable to Excel
            
                        sheet.InsertDataTable((DataTable)this.dataGridView1.DataSource, true, 2, 1, -1, -1);
            
                        //Set Excel Style
            
                        CellStyle Style = workbook.Styles.Add(“Style”);
            
                        Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
            
                        Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
            
                        Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
            
                        Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
            
                        Style.Borders.Color = Color.DarkCyan;
            
                        Style.Color = Color.Lavender;
            
                        Style.Font.FontName = “Calibri”;
            
                        Style.Font.Size = 12;
            
                        CellRange range = sheet.Range["A3:F26"];
            
                        range.CellStyleName = Style.Name;
            
                        //Set Header Style
            
                        CellStyle styleHeader = sheet.Rows[0].Style;
            
                        styleHeader.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
            
                        styleHeader.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
            
                        styleHeader.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
            
                        styleHeader.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
            
                        styleHeader.Borders.Color = Color.DarkCyan;
            
                        styleHeader.VerticalAlignment = VerticalAlignType.Center;
            
                        styleHeader.HorizontalAlignment = HorizontalAlignType.Center;
            
                        styleHeader.KnownColor = ExcelColors.Cyan;
            
                        styleHeader.Font.FontName = “Calibri”;
            
                        styleHeader.Font.Size = 14;
            
                        styleHeader.Font.IsBold = true;
            
                        //Set Row Height and Column Width
            
                        sheet.AllocatedRange.AutoFitColumns();
            
                        sheet.AllocatedRange.AutoFitRows();
            
                        sheet.Range["A3:F26"].RowHeight = 16;
            
                        sheet.Rows[0].RowHeight = 20;
            
                        //Save and Launch File
            
                        workbook.SaveToFile(“DataImport.xlsx”, ExcelVersion.Version2010);
            
                        System.Diagnostics.Process.Start(workbook.FileName);
            
                    }
            
                    private void Form1_Load_1(object sender, EventArgs e)
            
                    {
            
                        //Load Data from Database to DataGridView
            
                        string connString = @”Provider=Microsoft.ACE.OLEDB.12.0;
            
                                            Data Source=D:\work\VIP.mdb;Persist Security Info=False;”;
            
                        DataTable dataTable = new DataTable();
            
                        using (OleDbConnection conn = new OleDbConnection(connString))
            
                        {
            
                            conn.Open();
            
                            string sql = “select Name,Gender,Birthday,Email,Number,Country from VIP”;
            
                            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(sql, conn);
            
                            dataAdapter.Fill(dataTable);
            
                        }
            
                        this.dataGridView1.DataSource = dataTable;
            
                    }
            If you want to get some more details about it, please visit this article:


            Hope helpful!

            Comment

            Working...