Click here to Skip to main content
12,395,388 members (66,344 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: VB Windows MS-Excel Gridview , +
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
Posted 21-May-12 20:32pm
Comments
Phouttrat 4-Jan-16 6:21am
   
You can check this excel dll for vb.net, it's an alternative the mentioned one.
Also here you can find a demonstration on how to import and export a DataTable to and from an Excel file.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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

This discussion[^] might also be of some interest to you.
  Permalink  
Comments
MacParekh 22-May-12 2:58am
   
Thanks Abhinav,
Any idea About Excel to Datatable?
losmac 23-May-12 12:00pm
   
MacParekh, use Google!
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

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[^]
  Permalink  
Comments
losmac 23-May-12 12:01pm
   
Good links, my 5!
Prasad_Kulkarni 23-May-12 23:49pm
   
Thank you Isomac!
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

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
  Permalink  
Comments
losmac 23-May-12 12:04pm
   
This answer isn't perfect. Reading data cell by cell is not effective. Please, take a look at Prasad_Kulkarni answer.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160721.1 | Last Updated 28 Aug 2015
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100