【转帖】利用ado.net操作excel - 精华帖集合
 

利用ado.net操作excel - 精华帖集合
www.dimcax.com
利用ado.net操作excel

根据才鸟的" "一文，做了这个练习，希望对大家有用！
[commandmethod("mtest")]
public void m_test()
{
editor m_ed = autodesk.autocad.applicationservices.application.documentmanager.mdiactivedocument.editor;
string m_filepath = @"d:\testexcel.xls";
#region 利用ado.net操作excel数据
string m_excelconnectstr = "provider = microsoft.jet.oledb.4.0 ; data source = '" +
m_filepath + "';extended properties=excel 8.0";
oledbconnection m_odbc = new oledbconnection(m_excelconnectstr);//连接excel（如果给定文件不存在，自动创建新文件）
string m_sheetname = "mkhsheet";
#region 创建一个excel表，并写入数据
m_odbc.open();//打开数据库

oledbcommand m_odbcmd = new oledbcommand();
m_odbcmd.connection = m_odbc;
string m_cmdstr = "";
string[] m_titlename = new string[] { "序号", "数据1", "数据2", "数据3", "数据4" };//表头（excel表中第一行数据）
string[] m_valuetype = new string[] { "numeric", "numeric", "numeric", "numeric", "numeric" };
for (int i = 0; i < m_titlename.getlength(0); i++) m_cmdstr += m_titlename【i】+ " " + m_valuetype【i】+ ",";
m_cmdstr = "create table " + m_sheetname + " (" + m_cmdstr.remove(m_cmdstr.length - 1) + ")";
m_odbcmd.commandtext = m_cmdstr;//创建表
try { m_odbcmd.executenonquery(); }//判断表是否已经存在？
catch
{
m_odbcmd.commandtext = "drop table " + m_sheetname; m_odbcmd.executenonquery();//如存在，则先删除
m_odbcmd.commandtext = m_cmdstr; m_odbcmd.executenonquery();//重新创建表
}
//向表中写入数据（增加记录）
for (int i = 0; i < 6; i++)
{
m_cmdstr = "insert into " + m_sheetname + " (" + m_titlename[0] + "," + m_titlename[1] + "," + m_titlename[3] + ") values(";
m_cmdstr += i.tostring() + "," + (i + 1).tostring() + "," + (i + 2).tostring() + ")";
m_odbcmd.commandtext = m_cmdstr;//指定sql语句
m_odbcmd.executenonquery();//执行sql语句，无返回值
}
//修改表中数据（修改记录）
oledbdataadapter m_odbda = new oledbdataadapter("select * from [" + m_sheetname + "$]", m_odbc);//读excel文件的一个表
dataset m_ds = new dataset();
m_odbda.fill(m_ds);//把表数据填入dataset中
system.data.datatable m_dt = m_ds.tables[0];//获取表格所对应的datatable对象
for (int i = 1; i < 6; i++)
{
m_cmdstr = "update " + m_sheetname + " set " + m_titlename[2] + "=";
m_cmdstr += ((convert.todouble(m_dt.rows[i - 1][1]) + convert.todouble(m_dt.rows[1])) * 3.0 / 2).tostring("0.00");
m_cmdstr += "," + m_titlename[4] + "=";
m_cmdstr += ((convert.todouble(m_dt.rows[i - 1][3]) + convert.todouble(m_dt.rows[3])) * 3.0 / 2).tostring("0.00");
m_cmdstr += " where " + m_titlename[0] + "=" + i.tostring();
m_odbcmd.commandtext = m_cmdstr;//指定sql语句
m_odbcmd.executenonquery();//执行sql语句，无返回值
}
m_dt.dispose(); m_dt = null;
m_ds.dispose(); m_ds = null;
m_odbda.dispose(); m_odbda = null;
m_odbcmd.dispose(); m_odbcmd = null;
m_odbc.close();//关闭数据库
#endregion
#region 从excel表中读取数据
m_odbda = new oledbdataadapter("select * from [" + m_sheetname + "$]", m_odbc);//读excel文件的一个表
m_ds = new dataset();
m_odbda.fill(m_ds);//把数据填入dataset中
m_dt = m_ds.tables[0];//获取表格所对应的datatable对象，这样才能进行相关的操作

string m_str = "";
for (int i = 0; i < m_dt.columns.count; i++) m_str += m_dt.columns.caption.padleft(8,'-');
m_ed.writemessage("{0}", m_str);//显示表头（excel工作表中第一行数据）

for (int i = 0; i < m_dt.rows.count; i++)
{
m_str = "";
m_ed.writemessage("\n");
for (int j = 0; j < m_dt.columns.count; j++)
m_str += m_dt.rows[j].tostring().padleft(8,'-');
m_ed.writemessage("{0}", m_str);//显示每行数据
}
m_dt.dispose(); m_dt = null;
m_ds.dispose(); m_ds = null;
m_odbda.dispose(); m_odbda = null;
#endregion
m_odbc.dispose(); m_odbc = null;
#endregion
}
复制代码
各种操作均用sql查询语言，本人对sql了解很少，网上查了很多资料，也算是给自己扫盲，呵呵！
这种方法创建的excel表中的单元格数据好像没有公式和格式，只有数据值，不知谁有好的办法？
[ ]
不错，好文章，加精了
c#最适合开发autocad，因为它拥有vb容易的特点，却具有vc++的强大功能。
正需要，感谢楼主。