将Excel导出到Datatable和Datatable到Excel vb.net [英] Export Excel to Datatable and Datatable to Excel vb.net

问题描述

大家好，



我正在使用VB.net桌面应用程序。我的要求就是这样，

我想从Excel工作表中获取数据并将其从应用程序更改并写入新的Excel工作表。



所以我如何实现Excel数据表和数据表到Excel功能

我已经读过spire.xls dll任何人都知道这个DLL。如果您有任何其他选择，请告诉我。



提前使用Thanx

Hi all,

I am working on VB.net Desktop Application. My requirement is like that,
I want data from Excel sheet and change it from application and Write in to new Excel sheet.

So how can i achieve Excel to datatable and Datatable to Excel functionality
I have read about spire.xls dll anyone know about this dll. If you have any alternative please let me Know.

Thanx in advance

推荐答案

尝试使用ExcelLibrary的ExcelSet到Excel文件转换 [ ^ ]。



此讨论 [ ^ ]也可能对你有用。

Try DataSet to Excel File Conversion using ExcelLibrary[^].

This discussion[^] might also be of some interest to you.

请参考：



将数据表记录导出到C＃.net中的Excel表格：
从C＃.Net中的数据表导出到EXCEL [ ^ ]



类似讨论：点击此处 [ ^ ]



将DataTable导出到ASP.NET中的Excel [ ^ ]



以下链接显示了使用 GemBox.Spreadsheet .NET组件将DataTable导入或导出为Excel或HTML文件。

将DataTable导入或导出到Excel [ ^ ]

Please refer:

Export the Datatable records to Excel sheet in C#.net:
Export to EXCEL from Datatable in C#.Net[^]

Similar discussion: Click here[^]

Export a DataTable to Excel in ASP.NET[^]

Following link shows how to import or export DataTable to Excel or HTML files by using GemBox.Spreadsheet .NET component.
Import or Export DataTable to Excel[^]

Imports Microsoft.Office.Interop.Excel

Imports System.Collections .Generic

Imports System.Diagnostics

进口Microsoft.Office.Interop





'把Excel数据导入到DataGridView里

Private Sub Button1_Click（ByVal sender As System.Object，ByVal e As System.EventArgs）处理btn1.Click

Dim excel正如Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application（）

Dim xlBook As Microsoft.Office.Interop.Excel.Workbook

Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet

Dim fileDialog As OpenFileDialog = New OpenFileDialog（）

Dim FileName As String

fileDialog.Filter =Microsoft Excel文件（* .xls）| * .xls

如果fileDialog.ShowDialog = System.Windows.Forms.DialogResult.Cancel则退出Sub

如果fileDialog.FileName = Nothing那么

MsgBox（请选择要导入的excel文件，提示）

结束如果

FileName = fileDialog.FileName

xlBook = excel.Application.Workbooks.Open（FileName）

xlSheet = xlBook.Application.Worksheets（1）

Dim col As Integer = 0

Dim i As Integer = 2

Me.dgv.Rows.Clear（）

Do while i < 3

dgv.Rows.Add（i）

dgv.Rows（col）.Cells（0）.Value = xlSheet.Cells（i，1）.value

dgv.Rows（col）.Cells（1）.Value = xlSheet.Cells（i，2）.value

dgv.Rows（col）.Cells（2） .Value = xlSheet.Cells（i，3）.value

dgv.Rows（col）.Cells（3）.Value = xlSheet.Cells（i，4）.value

dgv.Rows（col）.Cells（4）.Value = xlSheet.Cells（i，5）.value

dgv.Rows（col）.Cells（5）.Value = xlSheet .Cells（i，6）.value

i + = 1

col + = 1

循环

excel .ActiveWorkbook.Close（False）

xlSheet =没什么

xlBook =没什么

excel =没什么

如果是dgv .Rows.Count> = 1然后

MessageBox.Show（导入成功）

Else

MessageBox.Show（导入失败 ）

结束如果

End Sub







Private Sub Button2_Click（ByVal sender As System.Object，ByVal e As System.EventArgs）处理btn2.Click

Dim saveExcel作为SaveFileDialog

saveExcel = New SaveFileDialog

saveExcel.Filter =Excel文件（.xls）| *。 xls

Dim filename As String

如果saveExcel.ShowDialog = System.Windows.Forms.DialogResult.Cancel则退出Sub

filename = saveExcel .FileName

Dim excel作为新应用程序

excel =新Excel.Application

excel.DisplayAlerts = False

excel.Workbooks.Add（True）

excel.Visible = False

Dim i As Integer

For i = 0 to dgv.Columns。数 - 1

excel.Cells（1，i + 1）= dgv.Columns（i）.HeaderText

下一页

'设置标题

Dim j As Integer

For i = 0 To dgv.Rows.Count - 1'填充数据

for j = 0 to dgv.Columns.Count - 1

excel.Cells（i + 2，j + 1）= dgv（j，i）.Value

下一页

下一页

excel。工作簿（1）.SaveCopyAs（文件名）'保存

Me.Close（）

结束子

Imports Microsoft.Office.Interop.Excel
Imports System.Collections.Generic
Imports System.Diagnostics
Imports Microsoft.Office.Interop


'把Excel数据导入到DataGridView里
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn1.Click
Dim excel As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application()
Dim xlBook As Microsoft.Office.Interop.Excel.Workbook
Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet
Dim fileDialog As OpenFileDialog = New OpenFileDialog()
Dim FileName As String
fileDialog.Filter = "Microsoft Excel files (*.xls)|*.xls"
If fileDialog.ShowDialog = System.Windows.Forms.DialogResult.Cancel Then Exit Sub
If fileDialog.FileName = Nothing Then
MsgBox("请选择要导入的excel文件", , "提示")
End If
FileName = fileDialog.FileName
xlBook = excel.Application.Workbooks.Open(FileName)
xlSheet = xlBook.Application.Worksheets(1)
Dim col As Integer = 0
Dim i As Integer = 2
Me.dgv.Rows.Clear()
Do While i < 3
dgv.Rows.Add(i)
dgv.Rows(col).Cells(0).Value = xlSheet.Cells(i, 1).value
dgv.Rows(col).Cells(1).Value = xlSheet.Cells(i, 2).value
dgv.Rows(col).Cells(2).Value = xlSheet.Cells(i, 3).value
dgv.Rows(col).Cells(3).Value = xlSheet.Cells(i, 4).value
dgv.Rows(col).Cells(4).Value = xlSheet.Cells(i, 5).value
dgv.Rows(col).Cells(5).Value = xlSheet.Cells(i, 6).value
i += 1
col += 1
Loop
excel.ActiveWorkbook.Close(False)
xlSheet = Nothing
xlBook = Nothing
excel = Nothing
If dgv.Rows.Count >= 1 Then
MessageBox.Show("导入成功")
Else
MessageBox.Show("导入失败")
End If
End Sub



Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn2.Click
Dim saveExcel As SaveFileDialog
saveExcel = New SaveFileDialog
saveExcel.Filter = "Excel文件(.xls)|*.xls"
Dim filename As String
If saveExcel.ShowDialog = System.Windows.Forms.DialogResult.Cancel Then Exit Sub
filename = saveExcel.FileName
Dim excel As New Application
excel = New Excel.Application
excel.DisplayAlerts = False
excel.Workbooks.Add(True)
excel.Visible = False
Dim i As Integer
For i = 0 To dgv.Columns.Count - 1
excel.Cells(1, i + 1) = dgv.Columns(i).HeaderText
Next
'设置标题
Dim j As Integer
For i = 0 To dgv.Rows.Count - 1 '填充数据
For j = 0 To dgv.Columns.Count - 1
excel.Cells(i + 2, j + 1) = dgv(j, i).Value
Next
Next
excel.Workbooks(1).SaveCopyAs(filename) '保存
Me.Close()
End Sub

这篇关于将Excel导出到Datatable和Datatable到Excel vb.net的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！