ASP.NET Excel读写文件代码

1.读取excel文件的数据连接字符串。读取.xls格式文件的excel文件，可设置连接字符串为：<span><span>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。</span></span>

<span><span>2.读取excel表格sheet的名称。</span></span>

<pre>

<blockquote>

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

</blockquote>3.读取excel文件。

</pre>

<pre>

<blockquote>

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

</blockquote>4.将数据写入excel文件。

</pre>

<pre>

<blockquote>

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

</blockquote>

</pre>