'读Excel表
dim source As string="d:\text.xls"
dim SqlConn As String="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+source+";Extended Properties=Excel 8.0" dim sql As String="Select * from [Sheet1$]" dim Oledbcmd As OledbCommand =new oledbCommand(sql,new oledbConnection(SqlConn)) dim oledbada As oledbDataAdapter =new oledbDataAdapter(oledbcmd)
dim ds As DataSet=new DataSet()
oledbada.Fill(ds,"[Sheet1$]") DataGrid1.DataSource=ds
DataGrid1.DataMember="[Sheet1$]"
DataGrid1.DataBind() '写入数据库
Dim table As DataTable=DataGrid1.DataSource
dim changedRow As Arraylist=new ArrayList()
Dim row As DataRow
for each row in table.Rows
if (row.RowState!=DataRowState.Unchanged) then
changedRow.Add(row)
end if if (changedRow.Count=0) then
return
end if
next dim Conn As SqlConnection=new SqlConnection("server=localhost;uid=da;pwd=;database=PhoneOther")
Conn.open()
Dim Str As String="Select * from Temp"
Dim DataAdapter1 As SqlDataAdapter=new SqlDataAdapter(str,Conn)
Dim builder As SqlCommandBuilder =new SqlCommandBuilder(DataAdapter1)
DataAdapter1.UpdateCommand=builder.GetUpdateCommand
Dim Rows As DataRow=(DataRow)(changedRow.ToArray(GetType(DataRow)))
DataAdapter1.Update(Rows)
Conn.Close()
DataAdapter1.Dispose()
目前共找出三处错误:
1. if (row.RowState!=DataRowState.Unchanged) then
已经改正: if (row.RowState<>DataRowState.Unchanged) then2.Dim Rows As DataRow=(DataRow)(changedRow.ToArray(GetType(DataRow)))
是个转换类型?不知道怎么改3.oledbada.Fill(ds,"[Sheet1$]")
错误原因: 外部表不是预期的格式。原贴地址:http://community.csdn.net/Expert/topic/4265/4265903.xml?temp=.776745(已经结贴)
dim source As string="d:\text.xls"
dim SqlConn As String="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+source+";Extended Properties=Excel 8.0" dim sql As String="Select * from [Sheet1$]" dim Oledbcmd As OledbCommand =new oledbCommand(sql,new oledbConnection(SqlConn)) dim oledbada As oledbDataAdapter =new oledbDataAdapter(oledbcmd)
dim ds As DataSet=new DataSet()
oledbada.Fill(ds,"[Sheet1$]") DataGrid1.DataSource=ds
DataGrid1.DataMember="[Sheet1$]"
DataGrid1.DataBind() '写入数据库
Dim table As DataTable=DataGrid1.DataSource
dim changedRow As Arraylist=new ArrayList()
Dim row As DataRow
for each row in table.Rows
if (row.RowState!=DataRowState.Unchanged) then
changedRow.Add(row)
end if if (changedRow.Count=0) then
return
end if
next dim Conn As SqlConnection=new SqlConnection("server=localhost;uid=da;pwd=;database=PhoneOther")
Conn.open()
Dim Str As String="Select * from Temp"
Dim DataAdapter1 As SqlDataAdapter=new SqlDataAdapter(str,Conn)
Dim builder As SqlCommandBuilder =new SqlCommandBuilder(DataAdapter1)
DataAdapter1.UpdateCommand=builder.GetUpdateCommand
Dim Rows As DataRow=(DataRow)(changedRow.ToArray(GetType(DataRow)))
DataAdapter1.Update(Rows)
Conn.Close()
DataAdapter1.Dispose()
目前共找出三处错误:
1. if (row.RowState!=DataRowState.Unchanged) then
已经改正: if (row.RowState<>DataRowState.Unchanged) then2.Dim Rows As DataRow=(DataRow)(changedRow.ToArray(GetType(DataRow)))
是个转换类型?不知道怎么改3.oledbada.Fill(ds,"[Sheet1$]")
错误原因: 外部表不是预期的格式。原贴地址:http://community.csdn.net/Expert/topic/4265/4265903.xml?temp=.776745(已经结贴)
这个不对,我的是VB.asp的语法?错误原因:BC30203: 应为标识符。
3. 是不是excel数据表有问题
一开始名字不叫Sheet1后来改过来了,数据库的字段名分别为1,2,3,4,5,6,7,8,9,10‘11’12 会不会有什么问题?
3.因为你的excel中的sheet名不是sheet1$
不好意思写错了
好像还是不对!
类型“System.Array”的值无法转换为“System.Data.DataRow”。
也不对。大家没有操作Excel表的吗?
这句是错误的
Dim table As DataTable=DataGrid1.DataSource和(DataRow)(changedRow.ToArray(GetType(DataRow)))应该这样改:ctype(changedRow.ToArray(GetType(DataRow)),DataRow())2.Excel表应该是Excel工作簿格式。不允许其它格式。
更改代码:
'写入数据库
Dim table As DataTable=ctype(DataGrid1.DataSource,datatable)
dim dt as datatable = table.getchanges(DataRowState.Unchanged) 'dim changedRow As Arraylist=new ArrayList() if(dt is nothing)then return 'Dim row As DataRow
'for each row in table.Rows
'....都不要
dim Conn As SqlConnection=new SqlConnection("server=localhost;uid=da;pwd=;database=PhoneOther")
'...省略代码相同
'Dim Rows As DataRow=(DataRow)(changedRow.ToArray(GetType(DataRow)))
DataAdapter1.Update(dt)3,你需要写一个函数来获取Xls文件的ExcelSheets:
private ExcelSheets() as string
public sub SetExcelSheets()
dim dt as datatable = nothing
dim Conn As SqlConnection=new SqlConnection("server=localhost;uid=da;pwd=;database=PhoneOther")
Conn.Open()
dt = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,nothing)'得到工作表集合
if(not dt is nothing) then
ExcelSheets = new string(dt.rows.count){}
dim i as int32
for i = 0 to dt.rows.count - 1
ExcelSheets(i) = dt.rows(i)("TABLE_NAME").tostring()
ExcelSheets(i) = ExcelSheets(i).replace("$","")'要移除$ if(ExcelSheets(i).indexof("'") > -1) then
ExcelSheets(i) = ExcelSheets(i).replace("'","")'可能有分号
next
end if'现在当前xls文件的所有表名都在ExcelSheets数组中
end sub获取数据表:
public function FillDB(SheetName as string) as datatable
dim dt as datatable = new datatable(SheetName)
dim Conn As SqlConnection=new SqlConnection("server=localhost;uid=da;pwd=;database=PhoneOther")
dim oledbada as new OleDbDataAdapter(string.format("Select * From [{0}$]",SheetName),Conn)
oledbada.Fill(dt)'得到指定数据表
return dt
end function