vb.net 如何从/向 datagridview 导出/导入数据到 Excel 文件？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16335944/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to export/import data from/to datagridview to a Excel file?
提问by aniruddhaamle
I am using Microsoft Office 2007and Visual studio 2010.
我正在使用Microsoft Office 2007和Visual Studio 2010。
I want to save data of DataGridView into Excel file once I click the button on Windows form.
单击 Windows 窗体上的按钮后，我想将 DataGridView 的数据保存到 Excel 文件中。
Also I want to load data from Excel file into DataGridView by clicking a button.
我还想通过单击按钮将 Excel 文件中的数据加载到 DataGridView 中。
Pls help...I am very new to VB so unable to write code. Plsss help..
请帮助......我对VB很陌生，所以无法编写代码。请帮助..
回答by Ciarán
This is a rather big question and there's any number of solutions.
这是一个相当大的问题，有很多解决方案。
1) Writing to Excel.. There are 3 common ways to do this, 1) Write the data out as CSV format and Excel will happily read it. 2) Use Excel Interop to create a Workbook and write a work sheet to it. This requires that Excel is installed and 3) Use OleDB to create an Excel.
1) 写入 Excel.. 有 3 种常用方法可以做到这一点，1) 将数据以 CSV 格式写出，Excel 会很高兴地读取它。2) 使用 Excel Interop 创建工作簿并向其写入工作表。这需要安装 Excel 并且 3) 使用 OleDB 创建 Excel。
2) Reading Excel... Again you can use Excel Interop to read Excel Workbooks and again you need Excel installed, and you can also use OleDB to read Excel files.
2) 读取Excel... 再次可以使用Excel Interop 读取Excel 工作簿，再次需要安装Excel，并且还可以使用OleDB 读取Excel 文件。
There are other ways, but these are the most commonly used ones.
还有其他方法，但这些是最常用的方法。
On balance I would approach this by using OleDB in the first instance and there's lots of examples on StackOverflow on how to read and write to Excel.
总的来说，我会通过在第一个实例中使用 OleDB 来解决这个问题，并且 StackOverflow 上有很多关于如何读取和写入 Excel 的示例。
If you need access to all of Excel's formatting or charting facilities then you will need Interop. It's relatively easy to use and again there's lots of examples of how to do it.
如果您需要访问所有 Excel 的格式设置或图表工具，那么您将需要 Interop。它相对易于使用，并且有很多示例说明如何使用它。
EDIT:
编辑：
At it's most simple...
最简单的...
First add a reference to Microsoft.Office.Interop.Excel under the project properties, then...
首先在项目属性下添加对Microsoft.Office.Interop.Excel的引用，然后...
Imports Excel = Microsoft.Office.Interop.Excel
Public Class frmExcelExport
Private Sub frmExcelExport_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
dgvDataToExport.Columns.Add("C1", "Column 1")
dgvDataToExport.Columns.Add("C2", "Column 2")
dgvDataToExport.Rows.Add("Col1-Row1", "Col2-Row1")
dgvDataToExport.Rows.Add("Col1-Row2", "Col2-Row2")
End Sub
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
End Class
But, really there's massive amounts of stuff on this all over the internet. This is simply adding to it
但是，互联网上确实有大量关于此的内容。这只是添加到它
回答by rocky
Use OpenXML SDK. You can find lots of examples around the internet. E.g. there is nice summaryon MSDN. Or take a look on CodeProject.
使用OpenXML SDK。你可以在互联网上找到很多例子。例如，MSDN 上有很好的总结。或者看看CodeProject。
