ado.net excel 模版

標籤：style   ext   color   com   strong   資料   

ado.net excel 模版

private static void Excute()
        {
            while (true)
            {
                string templatePath = System.Environment.CurrentDirectory + "\\template.xls";
                string newPath = System.Environment.CurrentDirectory + "\\temp.xls";
                if (!File.Exists(templatePath))
                    return;

                if (!File.Exists(newPath))
                    File.Copy(templatePath, newPath,true);

                FileInfo fileInfo = new FileInfo(newPath);
                string s = fileInfo.Attributes.ToString();
                if (fileInfo.Attributes.ToString().ToLower().IndexOf("readonly") != -1)
                {
                    fileInfo.Attributes = FileAttributes.Normal;
                }

                string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                    "Extended Properties=\"Excel 8.0;HDR=YES;IMEX=2\";" +
                    // 指定擴充屬性為 Microsoft Excel 8.0 (97) 9.0 (2000) 10.0 (2002)，並且第一行作為資料返回，且以文本方式讀取       
                    "data source=" + newPath;

                OleDbConnection conn = new OleDbConnection(connStr);
                OleDbCommand cmd = new OleDbCommand("", conn);

                try
                {
                    conn.Open();

                    string sql = "insert into [Sheet2$] (姓名,性別,聯絡電話) values (41,51,61)";
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                    conn.Close();

                    //FileStream reader = File.OpenRead(newPath);
                    //int length = (int)reader.Length;
                    //byte[] buffer = new byte[length];
                    //int byteToRead;
                    //while (length > 0)
                    //{
                    //    //用戶端還開著
                    //    byteToRead = reader.Read(buffer, 0, length);
                    //    length -= byteToRead;
                    //    //用戶端斷開時
                    //    //length = -1;
                    //}

                    //以下為一個事務
                    File.Copy(newPath, "2011.xls", true);
                    if (File.Exists(newPath))
                        File.Delete(newPath);

                }
                catch (Exception e)
                {
                   
                }
                finally
                {
                    conn.Dispose();
                }

                Thread.Sleep(1000);
            }
        }