[ASP.net/MSSQL] 讀寫Office Excel之前要做的相關設定
Client side：
<asp:FileUpload ID="FileUploadQuestion" runat="server"
Width="30%"/>
<asp:Button ID="ButtonUpload" runat="server" Text="匯入"/>
Server side：
Imports System.Data.OleDb
Imports System.Data
Partial Class Test
' 按下『匯入』鈕
Protected Sub
ButtonUpload_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles ButtonUpload.Click
Dim FileUpload As
FileUpload ' 抓取Client
Side的檔案
Dim OLEConn As
OleDbConnection ' 與Excel連繫用
Dim OLECommand As
OleDbCommand
Dim OLEDataAdapter As OleDbDataAdapter
Dim dsResult As
New Data.DataSet ' 存放Excel的資料內容
Dim dtResult As
New Data.DataTable
Dim FileName As
String = "" ' Client Side的檔案名稱
Dim StartIndex As
Integer = 0
Dim ExcelConn As
String = "" ' 與Excel連繫用
Dim ExcelSelect As
String = ""
Dim Field1 As
String = "" ' Primary Key
Dim Field2 As
String = ""
Dim Field3 As
Integer = 0
Dim Field4 As
String = ""
Dim Field5 As
String = ""
Dim Field6 As
String = ""
Dim Field7 As
String = ""
Dim Field8 As
String = ""
Dim SqlString As
String = "" ' 組新增至資料庫的SQL指令
Dim InsertSuccCount As Integer = 0 ' 匯入成功筆數
Dim InsertFailCount As Integer = 0 ' 匯入失敗筆數
Try
FileUpload = CType(Me.FindControl("FileUploadQuestion"),
FileUpload)
FileName = FileUpload.PostedFile.FileName
If FileName = ""
Then
' ut.MessageBox("請選取一份欲匯入的Excel檔案！", MsgBoxStyle.Information, Me.Page)
Exit Sub
Else
StartIndex = FileName.LastIndexOf(".")
+ 1
If FileName.Substring(StartIndex, 3)
<> "xls" Then
' ut.MessageBox("請確認欲匯入的檔案格式為Excel！", MsgBoxStyle.Information,
Me.Page)
Exit Sub
End If
End If
ExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & FileName & ";Extended
Properties=EXCEL 8.0"
ExcelSelect = "SELECT * FROM
[Sheet1$]"
OLEConn = New OleDbConnection(ExcelConn)
OLECommand = New
OleDbCommand(ExcelSelect, OLEConn)
OLEDataAdapter = New
OleDbDataAdapter(OLECommand)
OLEConn.Open()
OLEDataAdapter.Fill(dsResult, "exceltogrid")
OLEConn.Close()
dtResult = dsResult.Tables("exceltogrid")
If dtResult.Rows.Count > 0 Then
For i As
Integer = 0 To
dtResult.Rows.Count – 1
Field1 = dtResult.Rows(i).Item(0).ToString.Trim
If
Field1 <> "" Then
Field2 = dtResult.Rows(i).Item(1).ToString.Trim
Field3 = Convert.ToInt32(dtResult.Rows(i).Item(2).ToString.Trim)
Field4 = dtResult.Rows(i).Item(3).ToString.Trim
Field5 = dtResult.Rows(i).Item(4).ToString.Trim
Field6 = dtResult.Rows(i).Item(5).ToString.Trim
Field7 = dtResult.Rows(i).Item(6).ToString.Trim
Field8 = dtResult.Rows(i).Item(7).ToString.Trim
SqlString = "INSERT INTO TestTable (Field1, Field2, Field3,
Field4, Field5, Field6, Field7, Field8) VALUES ('" & Field1
& "', '" & Field2 & "', " & Field3 & ", '" & Field4 & "', '" & Field5 & "', '" & Field6 & "', '" & Field7 & "', '" & Field8 & "')"
' 呼叫貴單位新增資料庫的函數
If
ExecuteInsert(SqlString) Then
InsertSuccCount += 1
Else
InsertFailCount += 1
End
If
Else
Exit
For
End
If
Next
' ut.MessageBox("匯入成功筆數："
& InsertSuccCount.ToString & "．失敗筆數："
& InsertFailCount.ToString, MsgBoxStyle.Information, Me.Page)
' 此處可寫重新查詢GridView的Code，讓user看到匯入成功後的結果
End If
Catch ex As
Exception
' ut.MessageBox(ex.ToString, MsgBoxStyle.Information,
Me.Page)
Exit Sub
End Try
End Sub
End Class
Excel格式：
A
|
B
|
C
|
D
|
E
|
F
|
G
|
H
|
|
1
|
Field1 Title
|
Field2 Title
|
Field3 Title
|
Field4 Title
|
Field5 Title
|
Field6 Title
|
Field7 Title
|
Field8 Title
|
2
|
A
|
AA
|
1
|
AAA
|
AAAA
|
AAAAA
|
AAAAAA
|
AAAAAAA
|
3
|
B
|
BB
|
2
|
BBB
|
BBBB
|
BBBBB
|
BBBBBB
|
BBBBBBB
|
4
|
C
|
CC
|
3
|
CCC
|
CCCC
|
CCCCC
|
CCCCCC
|
CCCCCCC
|
5
|
D
|
DD
|
4
|
DDD
|
DDDD
|
DDDDD
|
DDDDDD
|
DDDDDDD
|
6
|
E
|
EE
|
5
|
EEE
|
EEEE
|
EEEEE
|
EEEEEE
|
EEEEEEE
|
Generating Excel Report in ASP.NET 2.0
http://www.beansoftware.com/ASP.NET-Tutorials/Generating-Excel-Reports.aspx
上網有查到可透過GetOleDbSchemaTable來取到Excel的Sheet Name(Microsoft Jet database engine could not find the object 'sheet1$'.)，這樣就不用寫死在程式中了，???
【ASP.NET】匯入與匯出 Excel
http://blog.xuite.net/hcktony/blog/13253709
【ASP.NET】匯入與匯出 Excel
http://blog.xuite.net/hcktony/blog/13253709
沒有留言:
張貼留言