C#读取Excel数据方式

精选转载

270jia40 2014-08-07 23:16:07 博主文章分类：C#基础

文章标签 C# Excel 文章分类 办公效率

直接把Excel当作数据库，查询Excel的数据，代码如下：

String source = null;
OdbcConnection conn = null;
string sql = "select * from [Sheet1$]";
try
{
source = "Driver={Microsoft Excel Driver (*.xls)};DBQ=" + tbExcelFilePath.Text;
conn = new OdbcConnection(source);
conn.Open();
}
catch (OdbcException e)
{
try
{
source = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" + tbExcelFilePath.Text;
conn = new OdbcConnection(source);
conn.Open();
}
catch (OdbcException e1)
{
MessageBox.Show("请确认此文件没有被其它程序打开！");
}
}
try
{
OdbcCommand cmd = new OdbcCommand(sql, conn);
OdbcCommand cmd1 = new OdbcCommand("select count(*) from [Sheet1$]", conn);
OdbcDataReader read = cmd.ExecuteReader();
int count = int.Parse(cmd1.ExecuteScalar().ToString());
int rn = 1;
while (read.Read())
{
try
{
if (m_stop) break;
rn++;
string lv_strSql;
string lv_strSqlOne = "insert into user (";
string lv_strSqlTwo = " value(";
String[] row = new String[read.FieldCount];
for (int i = 0; i < read.FieldCount; i++)
{
row[i] = read.GetValue(i).ToString();
if (read.GetName(i) == "会员姓名" && read.GetValue(i).ToString().Trim() != "")
{
lv_strSqlOne += "name,";
lv_strSqlTwo += "'" + read.GetValue(i).ToString() + "',";
}
............//Excel可能有多列
else if (read.GetName(i) == "累计积分" && read.GetValue(i).ToString().Trim() != "")
{
lv_strSqlOne += "score,";
lv_strSqlTwo += "'" + read.GetValue(i).ToString() + "',";
}
}
lv_strSqlOne += "create_date,sync_flag)";
lv_strSqlTwo += "'" + Date
Time.Now + "',0)";
lv_strSql = lv_strSqlOne + lv_strSqlTwo;
Console.WriteLine("lv_strSql:" + lv_strSql);
int lv_ret = m_db.RunNoQuery(lv_strSql);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
read.Close();
conn.Close();
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}