SQL 操作Excel (使用Jet.OLEDB,需要安装Excel)
使用Jet.OLEDB,虽然有很多的局限性.但他无疑是与DataTable操作最方便的方法了.速度也很快.NPOI有点慢.
1.链接:string connstr = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;'",物理地址);
2.取所有表名:
using (OleDbConnection objconn = new OleDbConnection(connstr))
{
try
{
objconn.Open();
dt = objconn.GetSchema("tables");
objconn.Close();
}
catch(Exception ee)
{
//
}
}
3.取表内容
string tbName = "";//表名后会有$
System.Data.DataTable dt = new System.Data.DataTable();
using (OleDbConnection objconn = new OleDbConnection(connstr))
{
try
{
objconn.Open();
OleDbCommand cmd = objconn.CreateCommand();
cmd.CommandText = "select * from [" + tbName + "]";
OleDbDataAdapter adp = new OleDbDataAdapter(cmd);
adp.Fill(dt);
objconn.Close();
}
catch(Exception ee)
{
//
}
}
4.根据DataTable插入更新
using (OleDbConnection objconn = new OleDbConnection(connstr))
{
try
{
objconn.Open();
//发现手工写列名很容易出错,就提取一下表的列表
OleDbCommand cmd = objconn.CreateCommand();
cmd.CommandText = "select * from [Sheet1$]";
OleDbDataAdapter adp = new OleDbDataAdapter(cmd);
adp.Fill(dt);
StringBuilder hdrColumn = new StringBuilder();
for (int i = 0; i < dt.Columns.Count; i++)
{
hdrColumn.Append("[" + dt.Columns[i].ColumnName.ToString() + "],");
}
//插入行
foreach (DataRow dr in dataTable.Rows)
{
StringBuilder columnString = new StringBuilder();
for (int i = 0; i < dataTable.Columns.Count;i++ )
{
DataColumn dc = dataTable.Columns[i];
columnString.Append("'" + dr[dc.ColumnName] + "',");
}
string sqlStr = "insert into [Sheet1$] (" + hdrColumn.ToString().Trim(',') + ") values(" + columnString.ToString().Trim(',') + ")";
OleDbCommand Cmd = new OleDbCommand(sqlStr, objconn);
Cmd.ExecuteNonQuery();
}
objconn.Close();
}
catch(Exception ee)
{
//
}
}
5. Jet 性质决定不支持Delete操作
注:插入行操作时,单元格格式会根据上一行的设置设定.
