对Excel表的查询、插入和更新操作

为什么80%的码农都做不了架构师？>>>   

本代码主要实现对Excel表的查询、插入和更新操作，而特别指出：Excel表的删除语句操作不支持，只能用更新来替代。

using UnityEngine;
using System.Collections;
using System;
using
System.Data;
using System.Data.Odbc;

public class ExcelHandler : MonoBehaviour {
private DataTable dt =
new DataTable("MyDataTable");
private string conStr;
private
OdbcConnection odbcConn;
public string excelFileName;
public
string path;
private string queryStr;
private string
insertStr;
private string updateStr;
private string
deleteStr;
public string[] insertInfo=new string[6];
public
string tochangedStr;
// Use this for initialization
void Start
()
{
path = Application.dataPath + "/" + excelFileName +
"";
// Must be saved as excel 2003 workbook, not 2007, mono issue
really
conStr = "Driver={Microsoft Excel Driver (*.xls)};
DriverId=790;Dbq=" + path + ";ReadOnly=0;";//excel
odbc连接默认只读，加了ReadOnly=0表示连接可更新
// our odbc
connector
odbcConn = new OdbcConnection
(conStr);
readFromXLS
();
//查询
//insertIntoXLS
();
//插入
//updateXLS();
//更新

}

// Update is called once per
frame
void Update ()
{
Debug.Log
(dt.Rows.Count);
Debug.Log (dt.Columns.Count);
for
(int i=0; i
for (int j=0;
j
Debug.Log (dt.Rows [i] [j] + "--" + dt.Rows [i]
[j].GetType ());
//查询后，打印后每行每列的数据
}
}

}
void
OnGUI ()
{

}
void insertIntoXLS
()
//插入
{
//DateTime d = Convert.ToDateTime
(insertInfo [4]);

insertStr = "insert into
[Sheet1$] values('" + insertInfo [0]+ "','" + insertInfo [1] + "','" +
insertInfo [2] + "')";
OdbcCommand oCmd = new OdbcCommand
(insertStr, odbcConn);
odbcConn.Open ();
try
{
oCmd.ExecuteNonQuery ();
} catch
(Exception ex) {
print
(ex.Message);
}
odbcConn.Close
();
}
void updateXLS ()
{
//string
os=""+;
updateStr = "update [Sheet1$] set [pub]=
'"+tochangedStr+"' where [id]= '"+1+"'
";
print(updateStr);
OdbcCommand oCmd = new
OdbcCommand (updateStr, odbcConn);
odbcConn.Open
();
try {
oCmd.ExecuteNonQuery
();
} catch (Exception ex) {
print
(ex.Message);
}
odbcConn.Close
();
}

void readFromXLS
()
{
queryStr = "SELECT * FROM
[Sheet1$]";
// our command object
OdbcCommand oCmd
= new OdbcCommand (queryStr, odbcConn);
// open the
connection
odbcConn.Open ();
// lets use a
datareader to fill that table!
OdbcDataReader rData =
oCmd.ExecuteReader ();

// now lets blast that
into the table by sheer man power!
dt.Load
(rData);
// close that reader!
rData.Close
();
// close your connection to the
spreadsheet!
odbcConn.Close
();

}

}

转载于:https://my.oschina.net/jieshu/blog/108182