Excel Operations: Reading Data From Excel To DataTable & Generating Excel from DataTable

2011-06-12

Reading data from Excel to DataTable
----------------------------------------





1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
Public Function ConvertExcelToDT(ByVal sPath As String, ByVal sQuery As String) As DataTable
 
ConvertExcelToDT = Nothing
 
Dim ds As New DataSet
 
Dim StrXLProvider As String = ""
 
Dim OleAdpt As Data.OleDb.OleDbDataAdapter = Nothing
 
Dim Connection As OleDb.OleDbConnection
 
Try
 
 
 
Connection = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sPath + ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""")
 
OleAdpt = New Data.OleDb.OleDbDataAdapter(sQuery, Connection)
 
OleAdpt.TableMappings.Add("Table", "Table")
 
OleAdpt.Fill(ds)
 
Connection.Close()
 
resetcounter:
 
For Each dr In ds.Tables(0).Rows
 
If dr(0).ToString() = "" Then
 
ds.Tables(0).Rows.Remove(dr)
 
ds.Tables(0).AcceptChanges()
 
GoTo resetcounter
 
End If
 
Next
 
 
 
 
 
 
 
Catch ex As Exception
 
ConvertExcelToDT = Nothing
 
Me.Response.TransRemarks = ex.ToString()
 
Me.Response.TransStatus = True
 
Finally
 
If Connection IsNot Nothing Then
 
Connection.Close()
 
Connection.Dispose()
 
End If
 
ConvertExcelToDT = ds.Tables(0)
 
End Try
 
 
 
End Function


Reqs>>1. It should be Excel file
2. AccessDatabaseEngine.exe should be installed to read data from Excel to DataTable other wise you will get The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.
3. No need of Excel to be installed in the hosting server

Generating Excel from DataTable
----------------------------------

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
Private Sub GenerateExcel(ByVal dtExcel As DataTable)
 
Dim oAppln As Microsoft.Office.Interop.Excel.Application
 
Dim oWorkBook As Microsoft.Office.Interop.Excel.Workbook
 
Dim oWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
 
Dim ColumnIndex As Int32 = 0
 
Dim rowIndex As Int32 = 0
 
Dim fileName As String = "JNTax" & DateTime.Now.ToString().Replace(":", "_").Replace("/", "-") + ".xls"
 
Dim uploadingFilePath As String = Server.MapPath("AirlineRuleFiles")
 
Try
 
oAppln = New Microsoft.Office.Interop.Excel.Application()
 
oWorkBook = DirectCast(oAppln.Workbooks.Add(True), Microsoft.Office.Interop.Excel.Workbook)
 
'oWorkSheet = DirectCast(oWorkBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing), Microsoft.Office.Interop.Excel.Worksheet)
 
'oWorkSheet.Activate()
 
oWorkSheet = oWorkBook.ActiveSheet()
 
For Each col As DataColumn In dtExcel.Columns
 
ColumnIndex += 1
 
oWorkSheet.Cells(1, ColumnIndex) = col.ColumnName
 
 
 
Next
 
 
 
For Each row As DataRow In dtExcel.Rows
 
rowIndex += 1
 
ColumnIndex = 0
 
For Each col As DataColumn In dtExcel.Columns
 
ColumnIndex += 1
 
oWorkSheet.Cells(rowIndex + 1, ColumnIndex) = row(col.ColumnName).ToString()
 
Next
 
 
 
Next
 
If System.IO.Directory.Exists(uploadingFilePath) Then
 
System.IO.Directory.Delete(uploadingFilePath, True)
 
End If
 
 
 
System.IO.Directory.CreateDirectory(uploadingFilePath)
 
fileName = uploadingFilePath & fileName
 
oWorkBook.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Nothing, Nothing, False, False, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, False, False, Nothing, Nothing, Nothing)
 
oWorkBook.Close(Nothing, Nothing, Nothing)
 
oAppln.Quit()
 
 
 
Response.ClearContent()
 
Response.ContentType = "application/vnd.ms-excel"
 
Response.WriteFile(fileName)
 
Response.End()
 
Catch ex As Exception
 
 
 
End Try
 
End Sub


Reqs: 1. Excel should be installed if you are generating Excel using Excel Application class. Plus you need to set DCOM settings. I tried this one but faced issues so I won't recommend this method.

There is another way to Generate Excel files without using Microsoft Excel Application class.


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
Protected Sub GenerateExcelWithOutUsingExcelInterop(ByVal dtExcel As DataTable, ByVal fileName As String, ByVal sheetName As String)
 
Dim stream As FileStream = Nothing
 
Dim writer As ExcelWriter
 
Dim uploadingFilePath As String = Server.MapPath("AirlineRuleFiles")
 
Dim iheaderColumnsCount As Int16 = 0
 
Dim iRow As Int16 = 0
 
Dim iColumn As Int16 = 0
 
Try
 
 
 
If System.IO.Directory.Exists(uploadingFilePath) Then
 
System.IO.Directory.Delete(uploadingFilePath, True)
 
End If
 
 
 
System.IO.Directory.CreateDirectory(uploadingFilePath)
 
 
 
If Not System.IO.File.Exists(uploadingFilePath & fileName) Then
 
stream = System.IO.File.Create(uploadingFilePath & fileName)
 
End If
 
 
 
writer = New ExcelWriter(stream)
 
 
 
writer.BeginWrite()
 
 
 
iRow = 0
 
iColumn = 0
 
For Each column As DataColumn In dtExcel.Columns
 
writer.WriteCell(iRow, iColumn, column.ColumnName)
 
iColumn += 1
 
Next
 
 
 
iRow = 1
 
iColumn = 0
 
For Each row As DataRow In dtExcel.Rows
 
iColumn = 0
 
For Each column As DataColumn In dtExcel.Columns
 
writer.WriteCell(iRow, iColumn, row(column.ColumnName).ToString())
 
iColumn += 1
 
Next
 
iRow += 1
 
Next
 
 
 
writer.EndWrite()
 
stream.Close()
 
 
 
Response.ClearContent()
 
Response.ContentType = "application/vnd.ms-excel"
 
Response.AppendHeader("Content-Disposition", "attachment; filename=" & sheetName & ".xls")
 
Response.WriteFile(uploadingFilePath & fileName)
 
Response.End()
 
Catch ex As Exception
 
Throw ex
 
End Try
 
 
 
End Sub

0 comments: