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 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
Phouttrat 4-Jan-16 6:21am
You can check this excel dll for, 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
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 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
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 23-May-12 12:01pm
Good links, my 5!
Prasad_Kulkarni 23-May-12 23:49pm
Thank you Isomac!
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 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