Click here to Skip to main content
11,702,130 members (65,565 online)
Rate this: bad
Please Sign up or sign in to vote.
See more: Windows Excel Gridview VB.NET , +
Hi all,

I am working on 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
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.
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
Please Sign up or sign in to vote.

Solution 3

Please refer:

Export the Datatable records to Excel sheet in
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[^]
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
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:[^]
Any doubt, please let me know. Thank you
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: 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.

Prasad_Kulkarni at 23-May-12 4:54am
My 5!
Rate this: bad
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

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
Do While i < 3
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
xlSheet = Nothing
xlBook = Nothing
excel = Nothing
If dgv.Rows.Count >= 1 Then
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.Visible = False
Dim i As Integer
For i = 0 To dgv.Columns.Count - 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.Workbooks(1).SaveCopyAs(filename) '保存
End Sub
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 Maciej Los 395
1 Sergey Alexandrovich Kryukov 355
2 OriginalGriff 339
3 CHill60 145
4 Mika Wendelius 119
0 OriginalGriff 8,344
1 Sergey Alexandrovich Kryukov 7,507
2 CPallini 4,922
3 Maciej Los 4,371
4 Mika Wendelius 3,556

Advertise | Privacy | Mobile
Web03 | 2.8.150819.1 | Last Updated 24 Nov 2013
Copyright © CodeProject, 1999-2015
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