excel相关的代码

 

string SheetName = "sheet1";
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\guanxi.xls" +
@";Extended Properties=""Excel 8.0;HDR=YES;""";
DbConnection connection = null;
DbProviderFactory factory =
DbProviderFactories.GetFactory("System.Data.OleDb");

using (connection = factory.CreateConnection())
{
connection.ConnectionString = connectionString;

using (DbCommand command = connection.CreateCommand())
{
connection.Open();
try
{
command.CommandText = "CREATE TABLE [" + SheetName + "] (" +
"[phone] text,[msg] text,[result] text,[start] text,[spname] text,[ids] text)";

command.ExecuteNonQuery();
}
catch (Exception)
{
}

string strPath = Application.StartupPath;

// 用于保存没有标注Poi的路
FileStream fileUnMarkedPois = new FileStream(strPath + @"\error.txt",
FileMode.Create | FileMode.Append, FileAccess.Write);
StreamWriter swUnMarkedPois = new StreamWriter(fileUnMarkedPois,
System.Text.Encoding.GetEncoding("GB2312"));

string str;
int n = 0; ClientResult result;
DateTime t1 = DateTime.Now;
DateTime t2 = DateTime.Now;
for (int i = start; i < cl.Count && Running; i++)
{
CaseInfo info = null;
try
{

info = cl.Get(i);
if (info == null)
continue;
string temp = info.msg;
//sms.RecvMsg(int.Parse(info.moid), info.phoneNum, info.msg);
t1 = DateTime.Now;

command.CommandText = "INSERT INTO [" + SheetName + "$] (phone,msg,result,start,spname,ids) Values(";
command.CommandText += "'" + info.phoneNum + "',";
command.CommandText += "'" + SQLEscape(temp) + "',";
command.CommandText += "'" + SQLEscape(Result) + "',";
command.CommandText += "'" + ts.TotalMilliseconds.ToString() + "',";
command.CommandText += "'" + SQLEscape(result.SpName) + "',";
command.CommandText += "'" + SQLEscape(result.IDs) + "')";

command.ExecuteNonQuery();
str = i.ToString() + "/" + cl.Count.ToString();

textBoxInfo.Invoke(new DelegateTestAll(UpdateInfo), new object[] { str });
//n++;
//if (n >= 2000)
//{
// break;
//}

}
catch (Exception exp )
{

swUnMarkedPois.WriteLine(info.msg + " " + info.phoneNum);
swUnMarkedPois.WriteLine(exp.Message + exp.Source);
swUnMarkedPois.Flush();

}
}
swUnMarkedPois.Close();
fileUnMarkedPois.Close();

connection.Close();
}

}
}
catch (Exception exp)
{
// MessageBox.Show("打开excel出错");
throw exp;
}