Answers for "export datagridview to excel vb.net"

export datagridview to excel vb.net

Private Sub Export_Button_Click(ByVal sender As System.Object, ByVal e As 
System.EventArgs) Handles VIEW_Button.Click

    Dim xlApp As Microsoft.Office.Interop.Excel.Application
    Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
    Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
    Dim misValue As Object = System.Reflection.Missing.Value
    Dim i As Integer
    Dim j As Integer

    xlApp = New Microsoft.Office.Interop.Excel.ApplicationClass
    xlWorkBook = xlApp.Workbooks.Add(misValue)
    xlWorkSheet = xlWorkBook.Sheets("sheet1")


    For i = 0 To DataGridView1.RowCount - 2
        For j = 0 To DataGridView1.ColumnCount - 1
            For k As Integer = 1 To DataGridView1.Columns.Count
                xlWorkSheet.Cells(1, k) = DataGridView1.Columns(k - 1).HeaderText
                xlWorkSheet.Cells(i + 2, j + 1) = DataGridView1(j, i).Value.ToString()
            Next
        Next
    Next

    xlWorkSheet.SaveAs("D:\vbexcel.xlsx")
    xlWorkBook.Close()
    xlApp.Quit()

    releaseObject(xlApp)
    releaseObject(xlWorkBook)
    releaseObject(xlWorkSheet)

    MsgBox("You can find the file D:\vbexcel.xlsx")
End Sub

Private Sub releaseObject(ByVal obj As Object)
    Try
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
        obj = Nothing
    Catch ex As Exception
        obj = Nothing
    Finally
        GC.Collect()
    End Try
End Sub

export datagridview to excel c# windows application

private void btn_ExportData_Click(object sender, EventArgs e)
        {
            try
            {
                SaveFileDialog sfd = new SaveFileDialog();
                sfd.Filter = "Excel Documents (*.xlsx)|*.xlsx";
                sfd.FileName = "";
                if (sfd.ShowDialog() == DialogResult.OK)
                {
                    copyAlltoClipboard();
                    Microsoft.Office.Interop.Excel.Application xlexcel;
                    Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
                    Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
                    object misValue = System.Reflection.Missing.Value;
                    xlexcel = new Microsoft.Office.Interop.Excel.Application();
                    xlexcel.Visible = true;
                    xlWorkBook = xlexcel.Workbooks.Add(misValue);
                    xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                    Microsoft.Office.Interop.Excel.Range CR = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1, 1];
                    CR.Select();
                    xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);

                    // For some reason column A is always blank in the worksheet. ¯\_(ツ)_/¯
                    // Delete blank column A and select cell A1
                    Microsoft.Office.Interop.Excel.Range delRng = xlWorkSheet.get_Range("A:A").Cells;
                    delRng.Delete(Type.Missing);
                    xlWorkSheet.get_Range("A1").Select();

                    // Save the excel file under the captured location from the SaveFileDialog
                    xlWorkBook.SaveAs(sfd.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                    xlexcel.DisplayAlerts = true;
                    xlWorkBook.Close(true, misValue, misValue);
                    xlexcel.Quit();

                    releaseObject(xlWorkSheet);
                    releaseObject(xlWorkBook);
                    releaseObject(xlexcel);

                    // Clear Clipboard and DataGridView selection
                    Clipboard.Clear();
                    grv_data.ClearSelection();

                    // Open the newly saved excel file
                    //if (File.Exists(sfd.FileName))
                    //    System.Diagnostics.Process.Start(sfd.FileName);

                    MessageBox.Show("Export Success!");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }