Save a datatable to excel sheet in vb.net winform application

Question

A year ago I saw a beautiful simple code that gets a data table and saves it in an excel file.

The trick was to use the web library (something with http) and I'm almost sure it was a stream.

I find a lot of code with response but I can't make it work in a win-form environment. There is also a cell by cell code - not interested -too slow.

I want to paste it as a range or something close.

Thanks

Answer 1

There are many component libraries out there that will provide this kind of functionality.

However, you could probably, most simply output the data as a CSV file and the load that into Excel.

Answer 2

What I like to do is put the datatable in a grid allowing the user to sort and filter. Then they can use the clipboard to copy/paste to Excel.

Private Sub mnuCopy_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuCopy.Click
    If dgvDisplaySet.GetClipboardContent Is Nothing Then
        MsgBox("Nothing selected to copy to clipboard.")
    Else
        Clipboard.SetDataObject(dgvDisplaySet.GetClipboardContent)
    End If
End Sub

Answer 3

I believe this is the code you're looking for:

DataTable to Excel

It uses an HtmlTextWriter.

Answer 4

Thanks all especially Jay

my old code just as you suggested is:

at least the next time it will wait for me here ;)

private void cmdSaveToExcel_Click(object sender, EventArgs e)
        {
            saveFileDialog1.Filter = "Excel (*.xls)|*.xls";
            if (saveFileDialog1.ShowDialog() == DialogResult.OK)
            {
                txtPath.Text = saveFileDialog1.FileName;               
            }

            // create the DataGrid and perform the databinding
            System.Web.UI.WebControls.DataGrid grid = new System.Web.UI.WebControls.DataGrid();
            grid.HeaderStyle.Font.Bold = true;


            if (connDBs != null && rtxtCode.Text != "")
            {
                DataTable dt;
                dt = connDBs.userQuery(rtxtCode.Text); // getting a table with one column of the databases names
                //grdData.DataSource = dt;
                grid.DataSource = dt;
                // grid.DataMember = data.Stats.TableName;

                grid.DataBind();

                // render the DataGrid control to a file
                using (StreamWriter sw = new StreamWriter(txtPath.Text))
                {
                    using (HtmlTextWriter hw = new HtmlTextWriter(sw))
                    {
                        grid.RenderControl(hw);
                    }
                }
                MessageBox.Show("The excel file was created successfully");
            }
            else
            {
                MessageBox.Show("Missing connection or query");
            }
        }

Answer 5

You need to convert your datatable into a ADO recordset, and then you can use the Range object's CopyFromRecordset method. See http://www.codeproject.com/KB/database/DataTableToRecordset.aspx