vb.net DataGrid 导出到 excel

DataGrid export to excel

提问by R.S

I have a DataGrid controlthat fills with a data set.

我有一个DataGrid control用data set填充的。

I don't show all fields of data set in DataGrid control.

我没有在 DataGrid 控件中显示数据集的所有字段。

I want to create an excel filefrom my DataGrid.

我想excel file从我的DataGrid.

How to get solution?

如何获得解决方案？

(windows form , vb net 1.1)

(窗体, vb net 1.1)

回答by Sathish

Try this Link

试试这个 链接

OR

或者

Try This

尝试这个

Imports Excel = Microsoft.Office.Interop.Excel

Dim excel As New Microsoft.Office.Interop.Excel.ApplicationClass
Dim wBook As Microsoft.Office.Interop.Excel.Workbook
Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet

 wBook = excel.Workbooks.Add(System.Reflection.Missing.Value)
wSheet = wBook.Sheets("sheet1")

  With wBook
.Sheets("Sheet1").Select()
.Sheets(1).Name = "NameYourSheet"
End With


For i = 0 To DataGrid1.RowCount - 1
For j = 0 To DataGrid1.ColumnCount - 1
wSheet.Cells(i + 1, j + 1).value = DataGrid1.Rows(i).Cells(j).Value.tosring
Next j
Next i

wSheet.Columns.AutoFit()

回答by Sasidharan

Private Sub btnExportToExcel_Click(sender As Object, e As EventArgs) Handles btnExportToExcel.Click

        Dim xlApp As Excel.Application = New Excel.Application

        xlApp.SheetsInNewWorkbook = 1

        Dim xlWorkBook As Excel.Workbook = xlApp.Workbooks.Add
        Dim xlWorkSheet As Excel.Worksheet = xlWorkBook.Worksheets.Item(1)

        xlWorkSheet.Name = "Example_Export"

        For nRow = 0 To dgvDataToExport.Rows.Count - 1

            For nCol = 0 To dgvDataToExport.Columns.Count - 1
                xlWorkSheet.Cells(nRow + 1, nCol + 1) = dgvDataToExport.Rows(nRow).Cells(nCol).Value
            Next nCol

        Next nRow

        xlApp.DisplayAlerts = False

        xlWorkBook.SaveAs("C:\Example.xlsx", Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, Type.Missing, Type.Missing, _
                           Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlLocalSessionChanges)

        xlWorkBook.Close()
        xlApp.Quit()

    End Sub

回答by eladolo

Try with this one:

试试这个：

Sub create_excel(sender As Object, e As EventArgs)
    Dim strFileName As string 
    Dim tw As New StringWriter() 
    Dim hw As New HtmlTextWriter(tw)    

    strFileName = "some_excel_from_datagrid.xls"
    Response.ContentType = "application/vnd.msexcel"
    Response.AddHeader("Content-Disposition", "attachment; filename=" & strFileName)
    Response.Charset = "UTF-8"
    Response.ContentEncoding = Encoding.Default

    DataGridID.RenderControl(hw)

    Response.Write(tw.ToString())
    Response.End() 
End Sub

回答by user3135909

Try

尝试

        If Not dgv.RowCount = 0 Then
            Dim folderBrowser As New FolderBrowserDialog
            folderBrowser.Description = "Select location to save the report"
            Dim filepath1 As String = ""
            If (folderBrowser.ShowDialog() = DialogResult.OK) Then
                filepath1 = folderBrowser.SelectedPath
            Else
                Exit Sub
            End If

            Dim xlApp As Microsoft.Office.Interop.Excel.Application
            Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
            Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
            Try

                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")
                ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                Dim titleStyle As Excel.Style = xlWorkSheet.Application.ActiveWorkbook.Styles.Add("NewStyle1")
                titleStyle.Font.Bold = True
                titleStyle.Font.Size = "18"
                titleStyle.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red)

                xlWorkSheet.Cells(2, 2) = "Employee Payment Report"
                xlWorkSheet.Cells(2, 4) = DateAndTime.Now.ToString("dd/MM/yyyy")
                xlWorkSheet.Cells(2, 2).Style = "NewStyle1"
                xlWorkSheet.Cells(2, 4).Style = "NewStyle1"
                ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

                '======================================================================================================
                Dim headerStyle As Excel.Style = xlWorkSheet.Application.ActiveWorkbook.Styles.Add("NewStyle")
                headerStyle.Font.Bold = True
                headerStyle.Font.Size = "12"
                headerStyle.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Brown)

                For k = 1 To dgv.Columns.Count
                    xlWorkSheet.Cells(4, k) = dgv.Columns(k - 1).HeaderText
                    xlWorkSheet.Cells(4, k).Style = "NewStyle"

                Next
                '=======================================================================================================
                Dim str As String = ""
                Dim l As Integer = 1
                j = 6
                Dim amt As Double = 0.0
                For i = 0 To dgv.RowCount - 1
                    amt = amt + dgv.Rows(i).Cells(4).Value
                    For m = 0 To dgv.ColumnCount - 1
                        xlWorkSheet.Cells(j, l) = dgv(m, i).Value.ToString()
                        str = dgv(m, i).Value.ToString()
                        l = l + 1
                    Next
                    j = j + 1
                    l = 1
                Next
                '======================================================================================================
                Dim lastStyle As Excel.Style = xlWorkSheet.Application.ActiveWorkbook.Styles.Add("NewStyle2")
                lastStyle.Font.Bold = True
                lastStyle.Font.Size = "12"
                lastStyle.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue)
                Dim c As Integer = dgv.ColumnCount
                xlWorkSheet.Cells(j + 2, c - 1) = "Total Amount"
                xlWorkSheet.Cells(j + 2, c) = amt.ToString
                xlWorkSheet.Cells(j + 2, c - 1).Style = "NewStyle2"
                xlWorkSheet.Cells(j + 2, c).Style = "NewStyle2"
                '=======================================================================================================
                xlWorkSheet.SaveAs(filepath1 + "\EmployeePaymentReport.xlsx")
                xlWorkBook.Close()
                xlApp.Quit()

                cls.releaseObject(xlApp)
                cls.releaseObject(xlWorkBook)
                cls.releaseObject(xlWorkSheet)

                MsgBox("You can find the file at " + filepath1 + "\EmployeePaymentReport.xlsx")
            Catch ex As Exception
                MsgBox(ex.Message)
                For Each Process In System.Diagnostics.Process.GetProcessesByName("EXCEL")
                    If Process.MainModule.ModuleName.ToUpper().Equals("EXCEL.EXE") Then
                        Process.Kill()
                    End If
                Next
            End Try
        Else
            Exit Sub
        End If
    Catch ex As Exception

    End Try
End Sub

回答by Nejc Galof

Maybe in this princip work.

也许在这个princip工作中。

  String path = @"D:\users\....";
    //your path


String connStr = "Provider=//your provider;Data Source=" + path + ";Extended Properties=Excel 12.0;";

            //The connection to that file

            OleDbConnection conn = new OleDbConnection(connStr);

            //The query

            string strSQL = "SELECT * FROM [?]";

            //The command 

            OleDbCommand cmd = new OleDbCommand(/*The query*/strSQL, /*The connection*/conn);

            DataTable dT = new DataTable();

            conn.Open();

            try

            {

                OleDbDataReader dR = cmd.ExecuteReader();

                dT.Load(dR);

                bS.DataSource = dT;

                dGV.DataSource = bS;

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

            finally

            {

                conn.Close();

            }