Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: Windows Excel Gridview VB.NET , +
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
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 at 22-May-12 2:58am
   
Thanks Abhinav,
Any idea About Excel to Datatable?
losmac at 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 at 23-May-12 12:01pm
   
Good links, my 5!
Prasad_Kulkarni at 23-May-12 23:49pm
   
Thank you Isomac!
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Hi, Not quite sure about what do you want to know. But you can freely download and have a trial: http://www.e-iceblue.com/Download/download-excel-for-net-now.html[^]
Any doubt, please let me know. Thank you
Regards
  Permalink  
v2
Comments
MacParekh at 22-May-12 2:58am
   
I want Excel data to my application as datatable or dataset and my data to Excel.
 
is Spire.xls free version??
Pandvi at 22-May-12 3:09am
   
Hi,Spire.xls is a commercial component. But there is a free alternative Spire.DataExport, only supporting datatable to Excel.
MacParekh at 22-May-12 3:12am
   
Thanks for your comment.
if i want Excel to datatable functionality do i need to purchase it?
Pandvi at 22-May-12 3:39am
   
Hi macParekh,
 
As the support of e-iceblue, I am sorry to tell that Spire.DataExport don't support import Excel to datatable, Only Spire.XLS perfectly support both of the two function. And I present my email: eric.du@e-iceblue.com for your further inquiry. As this forun is for technology discussion. Any information you need will be sent to you immediately once I receive your email. Thanks.
 
Regards
Prasad_Kulkarni at 23-May-12 4:54am
   
My 5!
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 at 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
0 Sergey Alexandrovich Kryukov 851
1 OriginalGriff 390
2 CPallini 265
3 George Jonsson 191
4 Kishore Pogaru 160
0 OriginalGriff 5,450
1 Sergey Alexandrovich Kryukov 4,532
2 CPallini 4,500
3 George Jonsson 3,102
4 Gihan Liyanage 2,445


Advertise | Privacy | Mobile
Web01 | 2.8.140916.1 | Last Updated 24 Nov 2013
Copyright © CodeProject, 1999-2014
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