ASP.NET Excel读写文件代码

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();
}
}