excel相关的代码

来源：互联网发布：洛江网络布线编辑：程序博客网时间：2024/05/01 21:48

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;
            }