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

[Ewan]

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]

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

    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:

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

[Frinavale]

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

[Ewan]

your Welcome :)
.........................................

[aspdotnetuser]

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 "DoddleReport" available @ http://doddlereport.codeplex.com/.

[Landy67]

Hello,
It is not difficult to export data to Excel by using GirdView.
I want to share a simple example with you.

        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:
http://janewdaisy.wordpress.com/2011...-datagridview/

Hope helpful!