读取excel表中的数据然后插入到sqlserver数据库中

[fxsy6669]

通过程序将excel表中的数据读取出来，对格式进行处理之后insert到sqlserver数据库中，怎么做呀？
web.config需要配制吗？代码该怎么写呢？最好贴出代码，详细点的，谢谢各位了！！

[teerhu]

public DataTable ReadFromExcel(System.Web.UI.Page page, string xlsFile, string table)
{
try
{
string xlscnn = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0;HDR=Yes;\";Data Source='";
string strConnection = xlscnn + xlsFile + "'";
string sqlstr = "SELECT * FROM [sheet1$]";
System.Data.OleDb.OleDbDataAdapter da=new System.Data.OleDb.OleDbDataAdapter(sqlstr, strConnection);
DataSet ds = new DataSet();
da.Fill(ds);
da.Dispose();

return ds.Tables[0];

}

[lihongxian]

友情帮顶

[mengxj85]

[Quote=引用 1 楼 teerhu 的回复:]
public DataTable ReadFromExcel(System.Web.UI.Page page, string xlsFile, string table)
{
try
{
string xlscnn = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0;HDR=Yes;\";D……
[/Quote]
用上这个，再随便找一个开源的例子参考着做就可以了

[wuyq11]

string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + fileName + ";Extended Properties=Excel 8.0" ;
DataSet ds= new DataSet();
using(OleDbConnection conn= new OleDbConnection(strCon))
{
string strCom = " SELECT * FROM [Sheet1$]" ;
conn.Open() ;
OleDbDataAdapter myCommand = new OleDbDataAdapter(str , conn) ;
myCommand.Fill(ds) ;
conn.Close() ;
}
insert into Tb select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=F:\a.xls',sheet1$)"

遍历excel单元格，ds.Update

[fxsy6669]

public DataTable ReadFromExcel(System.Web.UI.Page page, string xlsFile, string table)
{
try
{
string xlscnn = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0;HDR=Yes;\";D……
}
谢谢一楼的朋友，有点问题想问一下如果参数xlsFile中文的就有问题，读不出来路径，这个应该怎么处理一下呢？

[wangminqi]

用odbc驱动也可以


String source = null;
OdbcConnection conn = null;
try
{
source = "Driver={Microsoft Excel Driver (*.xls)};DBQ=" + filename ;
conn = new OdbcConnection(source);
conn.Open();
}
catch
{
try
{
source = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" + filename;
conn = new OdbcConnection(source);
conn.Open();
}
catch
{
MessageBox.Show("请确认此文件没有被其它程序打开！");
return;
}
}


try
{

string sql = "select * from [Sheet1$]";
OdbcCommand cmd = new OdbcCommand(sql, conn);
OdbcDataReader read = cmd.ExecuteReader();
...

http://www.mystruggle.com.cn