ASP.NET Excel读写文件代码

1.读取excel文件的数据连接字符串。读取.xls格式文件的excel文件，可设置连接字符串为：provider=microsoft.jet.oledb.4.0;data source=d:myexcel.xls;extended properties=excel 8.0;。如果要读取.xlsx和.xls格式文件的excel文件，则需要将连接字符设置为："provider=microsoft.ace.oledb.12.0;data source=d:myexcel.xls;extended properties=excel 12.0。

2.读取excel表格sheet的名称。

public static string[] getexcelsheetnames(string filepath)
{
string constring = "provider=microsoft.ace.oledb.12.0;data source=" + filepath + ";extended properties="excel 12.0;hdr=yes"";
oledbconnection con = new oledbconnection(constring);
con.open();
datatable dt = con.getoledbschematable(oledbschemaguid.tables, null);
con.close();
if (dt == null)
{
return null;
}
string[] excelsheetnames = new string[dt.rows.count];
int i = 0;
foreach (datarow dr in dt.rows)
{
excelsheetnames[i++] = dr["table_name"].tostring();
}
return excelsheetnames;
}
3.读取excel文件。

public static datatable readexcel(string filepath, string constr)
{
string constring = "provider=microsoft.ace.oledb.12.0;data source=" + filepath + ";extended properties="excel 12.0;hdr=yes"";
oledbconnection con = new oledbconnection(constring);
oledbdataadapter oda = new oledbdataadapter(constr, con);
datatable dt = new datatable();
con.open();
oda.fill(dt);
con.close();
return dt;
}
4.将数据写入excel文件。

public static void writeexcel(string filepath, datatable dt)
{
if (file.exists(filepath))
{
file.delete(filepath);
}
else
{
string constring = "provider=microsoft.ace.oledb.12.0;data source=" + filepath + ";extended properties="excel 12.0;hdr=yes"";
oledbconnection con = new oledbconnection(constring);
string createsql = "create table sheet1 (";
foreach (datacolumn dc in dt.columns)
{
createsql += dc.columnname + " varchar,";
}
createsql = createsql.substring(0, createsql.length - 1) + ")";

oledbcommand cmd = new oledbcommand(createsql, con);
con.open();
cmd.executenonquery();
foreach (datarow dr in dt.rows)
{
string insertsql = "insert into sheet1 values(";
foreach (datacolumn dc in dt.columns)
{
insertsql += "'" + dr[dc].tostring() + "',";
}
insertsql = insertsql.substring(0, insertsql.length - 1) + ")";
cmd = new oledbcommand(insertsql, con);
cmd.executenonquery();
}

con.close();
}
}