最近上班发现一定要注意两点:
1.前台的字段类型一定要与数据库保持一致!!!
2.一定要做非空判断!!!
下面进入正题,最近两天在做excel表格批量导入,从前台程序直接打开EXCEL,批量导入到数据库中
思路是:
1.先把EXCEL表格转换成Datatable,
2.然后datatable转成list,
3.把list保存到数据库中
1.首先在NPOI类中写四个方法,导入的,两个导入进入转换成datatable,(一个xls,一个xlsx),两个读取格式的(*就是第一步,转换成datatable)
Public Shared Function ExcelToTableForXLS(ByVal filePath As String) As DataTable
Dim dt As DataTable = New DataTable
Dim fs As FileStream = New FileStream(filePath, FileMode.Open, FileAccess.Read)
Dim hssfworkbook As HSSFWorkbook = New HSSFWorkbook(fs)
Dim sheet As ISheet = hssfworkbook.GetSheetAt(0)
'表头
Dim header As IRow = sheet.GetRow(sheet.FirstRowNum)
Dim columns As List(Of Integer) = New List(Of Integer)
Dim i As Integer = 0
Do While (i < header.LastCellNum)
Dim obj As Object = GetValueTypeForXLS(CType(header.GetCell(i), HSSFCell))
If ((obj Is Nothing) OrElse (obj.ToString = String.Empty)) Then
dt.Columns.Add(New DataColumn(("Columns" + i.ToString)))
'continue;
Else
dt.Columns.Add(New DataColumn(obj.ToString))
End If
columns.Add(i)
i = (i + 1)
Loop
'数据
Dim m As Integer = (sheet.FirstRowNum + 1)
Do While (m <= sheet.LastRowNum)
Dim dr As DataRow = dt.NewRow
Dim hasValue As Boolean = False
For Each j As Integer In columns
dr(j) = GetValueTypeForXLS(CType(sheet.GetRow(m).GetCell(j), HSSFCell))
If ((Not (dr(j)) Is Nothing) _
AndAlso (dr(j).ToString <> String.Empty)) Then
hasValue = True
End If
Next
If hasValue Then
dt.Rows.Add(dr)
End If
m = (m + 1)
Loop
Return dt
End Function
Private Shared Function GetValueTypeForXLS(ByVal cell As HSSFCell) As Object
If (cell Is Nothing) Then
Return Nothing
End If
Select Case (cell.CellTy