Click here to Skip to main content
12,954,280 members (73,894 online)
Rate this:
 
Please Sign up or sign in to vote.
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
OriginalGriff 6,524
CHill60 3,490
Maciej Los 3,123
ppolymorphe 2,030
Jochen Arndt 1,975


Advertise | Privacy | Mobile
Web02 | 2.8.170525.1 | Last Updated 28 Aug 2015
Copyright © CodeProject, 1999-2017
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