WebApp匯入CSV資料
1、情景
點擊“匯入”按鈕，打開匯入對話框(圖一)以匯入Excel或CSV文件。匯入的過程中，會check資料是否合法有效。check每一筆并記錄error信息，如果一筆或多餘一筆則show error。
否則show在Datagrid上（圖二）。
然後，按下“保存”按鈕，Datagrid上的資料寫入DB。
2、分析
CSV文件上upload到server上，并將CSV文件轉為dataset，dataset轉為xml。然後xml的值為對話框的值返回，賽道隱藏欄位。
再將隱藏欄位的值轉為xml，轉為Dataset。check每一行，有error寫入DB，跳轉到error頁面，從DB中抓出error以顯示。
3、實現
上傳CSV對話框部分
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
End Sub
Public Function GetImportDataSetFromCSV(ByVal sCSVFile As String) As String
GetImportDataSetFromCSV = ""
Try
Dim dtImport As New DataTable
Dim sContentb As Byte()
IO.ReadBinaryFile(sContentb, sCSVFile)
Dim sContent As String
Dim splitChar As String = ","
If sContentb(0) = 255 And sContentb(1) = 254 Then
sContent = New System.Text.UnicodeEncoding().GetString(sContentb)
sContent = sContent.Substring(1, sContent.Length - 1)
splitChar = vbTab
Else
sContent = New String(System.Text.Encoding.Default.GetString(sContentb))
splitChar = ","
End If
Dim aryLine() As String = sContent.Replace(vbCrLf, Chr(3)).Split(Chr(3))
Dim iLine As Integer
Dim iCol1 As Integer
Dim tstr As String
For iLine = 0 To aryLine.Length - 1
If aryLine(iLine) <> "" Then
Dim aryCol() As String = aryLine(iLine).Replace(""" & splitChar & """, Chr(2) + Chr(3) + Chr(2)).Replace(""" & splitChar, Chr(2) + Chr(3)).Replace( splitChar & """, Chr(3) + Chr(2)).Split(Chr(3))
If iLine = 0 Then
tstr = aryCol(0).Replace(Chr(2), "")
For iCol1 = 0 To tstr.Split(splitChar).Length - 1
dtImport.Columns.Add(tstr.Split(splitChar).GetValue(iCol1))
Next
Else
Dim iCol As Integer
For iCol = 0 To aryCol.Length - 1
If aryCol(iCol).IndexOf(Chr(2)) < 0 Then aryCol(iCol) = aryCol(iCol).Replace(splitChar, Chr(3))
aryCol(iCol) = aryCol(iCol).Replace("""""", """")
Next
Dim sNewLine As String = ""
For iCol = 0 To aryCol.Length - 1
sNewLine += IIf(sNewLine = "", aryCol(iCol), Chr(3) + aryCol(iCol))
Next
aryCol = sNewLine.Split(Chr(3))
Dim dr As DataRow = dtImport.NewRow()
For iCol = 0 To aryCol.Length - 1
dr.Item(iCol) = aryCol(iCol).Replace(Chr(2), "")
Next
dtImport.Rows.Add(dr)
End If
End If
Next
Dim ds As New DataSet
ds.Tables.Add(dtImport)
Return ds.GetXml()
Catch ex As Exception
Dim s As String = ex.Message.ToString()
End Try
End Function
Protected Sub btnOK_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnOK.Click
If Not (UploadFile.PostedFile Is Nothing) Then
Dim strBasePath As String = Server.MapPath("bin")
Dim strFileName As String = Path.GetFileName(UploadFile.PostedFile.FileName)
Dim strFileSize As String = UploadFile.PostedFile.ContentLength
If UCase(Right(strFileName, 3)) <> "CSV" Then
Dim sScript As String = ""
sScript += "<script language=javascript>"
sScript += "alert('Import file format only support Excel .csv file !')"
sScript += "</script>"
Page.RegisterStartupScript("OkClick", sScript)
Exit Sub
End If
Try
Dim sKeyId As String = Guid.NewGuid.ToString()
UploadFile.PostedFile.SaveAs(strBasePath & "\" & sKeyId & ".csv")
Dim sData As String = GetImportDataSetFromCSV(strBasePath & "\" & sKeyId & ".csv")
Dim sScript As String = ""
sScript += "<script language=javascript>"
sScript += "window.returnValue = '" + DESEncrypt.Encrypt(Server.UrlEncode(sData)) & "';"
sScript += "window.close();"
sScript += "</script>"
Page.RegisterStartupScript("OkClick", sScript)
File.Delete(strBasePath & "\" & sKeyId & ".csv")
Catch ex As Exception
End Try
End If
End Sub
Check CSV內容部分
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
End Sub
Private Sub ShowImportData()
Dim myCmd As New SqlClient.SqlCommand()
Dim Param As SqlClient.SqlParameter
Dim sImportCSVData As String
Dim ds As New DataSet
Dim sr As System.IO.StringReader
Dim bError As Boolean = False
' Xml to dataset
sImportCSVData = Server.UrlDecode(DESEncrypt.Decrypt(ImportExcel.Text))
sr = New System.IO.StringReader(sImportCSVData)
ds.ReadXml(sr)
' Check Row
Dim dv As New DataView(ds.Tables(0))
Dim iRow As Integer
Dim iCol1 As Integer
For iRow = 0 To ds.Tables(0).Rows.Count - 1
Dim sError As String = ""
Dim tstr As String
With ds.Tables(0).Rows(iRow)
.Item("NO") = iRow + 1
tstr = CheckDataInCS() 'check func.
If tstr = "" Then
Else
sError += tstr & "<br>"
End If
If sError.Trim() <> String.Empty Then
.Item("ERROR") = sError
End If
If sError <> "" Then bError = True
End With
Next
' if error, then insert it into table,and jump error message,show error.
If bError Then
ImportExcel.text = ""
Dim sGuid As String = Guid.NewGuid().ToString()
myCmd.CommandText = "INSERT INTO [TMP_EXCELIMPORT] (KeyID, FIELDVALUE) VALUES (@KeyID, @FIELDVALUE)"
Param = New SqlClient.SqlParameter("@KeyID", sGuid)
myCmd.Parameters.Add(Param)
Param = New SqlClient.SqlParameter("@FIELDVALUE", System.Data.SqlDbType.NText)
Param.Value = ds.GetXml()
myCmd.Parameters.Add(Param)
myCmd.ExecuteNonQuery()
Dim sErrorScript As String = "<script language=javascript>"
sErrorScript += "alert('Import source data occur error or duplicate, Press OK to view output file !');"
sErrorScript += "var OpenUrl = escape('ImportError.aspx');"
sErrorScript += "var OpenParam = 'KeyId=' + escape('" + sGuid + "');"
sErrorScript += "var url = OpenUrl + '?' + OpenParam;"
sErrorScript += "ret = window.open(url);"
sErrorScript += "</script>"
Page.RegisterStartupScript("GridNoData", sErrorScript)
Exit Sub
End If
End Sub
