dataGridView 粘贴数据并更新到数据库及发送邮件

直接上代码，下注释掉的代码为导出Excel代码，因远程机器上未安装excel，所以后改为csv。
dataGridView能直接粘贴数据到excel,同样也可以由excel粘贴到dataGridView，
利用exe粘贴数据直接更新能避免人为因素而导致的错误

using System;using System.Data;using System.Text;using System.Windows.Forms;using System.Data.SqlClient;using System.Reflection;using Microsoft.Office.Core;using Microsoft.Office.Interop.Excel;using System.Configuration;using System.Linq;using System.IO;using System.Net.Mail;namespace Test07{    public partial class Form1 : Form    {        public static int effectedRows = 0;        public static int effectedRowsFirstAndTwo = 0;        public Form1()        {            InitializeComponent();        }        private void Form1_loading(object sender, EventArgs e)        {            log.Items.Add("=====loading data=====");            dataGridView1.DataSource = null;        }        private void button1_Click(object sender, EventArgs e)        {            //创建命令重建对象            // SqlCommandBuilder scb = new SqlCommandBuilder(adapter);            //更新数据            try            {                //update                log.Items.Add("=====update 3,4====");                string str = "server=10.1.66.111;database=DB_wdl_Test;uid=PEAdmin;pwd=11111";                SqlConnection conn = new SqlConnection(str);                string update34 = File.ReadAllText(GetPath("update34.sql"));                log.Items.Add(update34);                string code = File.ReadAllText(GetPath("code.txt"));                log.Items.Add(code);                update34 = update34.Replace("{code}", code);                log.Items.Add(update34);                conn.Open();                SqlCommand cmd = new SqlCommand(update34, conn);                effectedRows = cmd.ExecuteNonQuery();                log.Items.Add("effect row : " + effectedRows + "row");                log.Items.Add("=====update 1,2====");                string update12 = File.ReadAllText(GetPath("update12.sql"));                update12 = update12.Replace("{code}", code);                log.Items.Add(update12);                SqlCommand cmdFirstAndTwo = new SqlCommand(update12, conn);                effectedRowsFirstAndTwo = cmdFirstAndTwo.ExecuteNonQuery();                log.Items.Add("effect row : " + effectedRowsFirstAndTwo + "row");                //query                log.Items.Add("=====query result====");                string query = File.ReadAllText(GetPath("query.sql"));                query = query.Replace("{code}", code);                log.Items.Add(query);                SqlCommand sqlcmd = new SqlCommand(query, conn);                SqlDataAdapter readData = new SqlDataAdapter(sqlcmd);                DataSet dtSetEnd = new DataSet();                readData.Fill(dtSetEnd);                System.Data.DataTable Table = dtSetEnd.Tables[0];                log.Items.Add("=====query end====");                log.Items.Add("export excel");                string fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "update" + DateTime.Now.ToString("yyyy-MM-dd") + ".xlsx");                File.AppendAllText(fileName, "");                dataGridView1.DataSource = Table;                SaveCSV(Table, fileName);                sendMail(fileName);                log.Items.Add("=====End All=====");            }            catch (SqlException ex)            {                MessageBox.Show(ex.Message);            }        }        //send Mail        public static void sendMail(string fileName)        {            System.Net.Mail.MailMessage msg = new System.Net.Mail.MailMessage();            string Form = "[e-mail]";            string To = "[e-mail],[e-mail],[e-mail]";            //string To = "[e-mail],[e-mail]";            string cc = "[e-mail]";            msg.To.Add(To);            msg.CC.Add(cc);            msg.From = new MailAddress(Form, "wangdenglin", System.Text.Encoding.UTF8);            msg.Subject = "促销券" + DateTime.Now.ToString("yyyy-MM-dd");//title                msg.SubjectEncoding = System.Text.Encoding.UTF8;//title encode                msg.Body = "<div>Hi Alex</div><p>所有券已更新</p>已使用券共" + effectedRows + "用DominosMarkRepeat-" + DateTime.Now.ToString("yyyyMMdd") + "标记<br/>未使用券共" + effectedRowsFirstAndTwo + "用DominosMarkOLOUsed-" + DateTime.Now.ToString("yyyyMMdd") + "标记<br/>BR<p>---Denglin</p>";//body                msg.BodyEncoding = System.Text.Encoding.UTF8;//contentencode                msg.IsBodyHtml = true;// Is html?              msg.Priority = MailPriority.Normal;//priority             msg.Attachments.Add(new Attachment(fileName));            SmtpClient client = new SmtpClient();            client.Credentials = new System.Net.NetworkCredential("[e-mail]", "111111");//regMail and password               client.Host = "smtp.exmail.qq.com";            object userState = msg;            try            {                //client.SendAsync(msg, userState);                client.Send(msg);                MessageBox.Show("mail send success");            }            catch (System.Net.Mail.SmtpException ex)            {                MessageBox.Show(ex.Message);            }        }        private static void ExportLog(string msg)        {            //log.Items.Add("update end");        }        private static string GetPath(string fileName)        {            return Path.Combine(AppDomain.CurrentDomain.BaseDirectory, fileName);        }        public static void SaveCSV(System.Data.DataTable dt, string fileName)        {            FileStream fs = new FileStream(fileName, System.IO.FileMode.Create, System.IO.FileAccess.Write);//读取文件，fileMode类型为Create即先删除后新建，fileAccess权限为写            StreamWriter sw = new StreamWriter(fs, System.Text.Encoding.Default);//转下编码            string data = "";            //write colData            for (int i = 0; i < dt.Columns.Count; i++)            {                data += dt.Columns[i].ColumnName.ToString();                if (i < dt.Columns.Count - 1)                {                    data += ",";                }            }            sw.WriteLine(data);            //weite RowData            if (dt.Rows.Count > 0)            {                Console.WriteLine("start write rows data");                for (int i = 0; i < dt.Rows.Count; i++)                {                    data = "";                    for (int j = 0; j < dt.Columns.Count; j++)                    {                        data += dt.Rows[i][j].ToString() == "" ? "null" : dt.Rows[i][j].ToString();                        if (j < dt.Columns.Count - 1)                        {                            data += ",";                        }                    }                    sw.WriteLine(data);                }            }            else            {                Console.WriteLine("data empty");            }            sw.Close();            fs.Close();        }        //public static void DataTabletoExcel(System.Data.DataTable tmpDataTable, string strFileName,int index)        //{        //    if (tmpDataTable == null)        //    {        //        return;        //    }        //    int rowNum = tmpDataTable.Rows.Count;        //    int columnNum = tmpDataTable.Columns.Count;        //    int rowIndex = 1;        //    int columnIndex = 0;        //    Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();        //    if (xlApp == null)        //    {        //        Console.WriteLine("don't create excel, don't install excel");        //        return;        //    }        //    xlApp.DefaultFilePath = "";        //    xlApp.DisplayAlerts = true;        //    xlApp.SheetsInNewWorkbook = 1;        //    Workbook xlBook = xlApp.Workbooks.Add(true);        //    Worksheet sheet = (Worksheet)xlBook.Worksheets[1];        //    //sheet.Copy(Type.Missing, (Worksheet)xlBook.Worksheets[1]);        //    //sheet.Name = "update before";        //    Worksheet sheet2 = (Worksheet)xlBook.Worksheets[index];        //    sheet2.Name = "coupon update";        //    foreach (DataColumn dc in tmpDataTable.Columns)  //将DataTable的列名导入Excel表第一行          //    {        //        columnIndex++;        //        sheet2.Cells[rowIndex, columnIndex] = dc.ColumnName;        //    }            //将DataTable中的数据导入Excel中         //    for (int i = 0; i < rowNum; i++)        //    {        //        rowIndex++; columnIndex = 0;        //        for (int j = 0; j < columnNum; j++)        //        {        //            columnIndex++;        //            sheet2.Cells[rowIndex, columnIndex] = tmpDataTable.Rows[i][j].ToString();        //        }        //    }          //xlBook.SaveCopyAs(HttpUtility.UrlDecode(strFileName, System.Text.Encoding.UTF8));        //    ExportLog("export fileurl" + strFileName);        //    xlBook.SaveCopyAs(strFileName);        //}        public void DataGridViewEnablePaste(DataGridView p_Data)        {            if (p_Data == null)                return;            p_Data.KeyDown += new KeyEventHandler(p_Data_KeyDown);        }        public static void DataGirdViewCellPaste(DataGridView DBGrid)        {            try            {                // 获取剪切板的内容，并按行分割                  string pasteText = "";                pasteText = Clipboard.GetText();                MessageBox.Show(pasteText);                if (string.IsNullOrEmpty(pasteText))                    return;                if (pasteText == "pasteText")                {                    return;                }                int tnum = 0;                int nnum = 0;                //获得当前剪贴板内容的行、列数搜索                for (int i = 0; i < pasteText.Length; i++)                {                    if (pasteText.Substring(i, 1) == "\t")                    {                        tnum++;                    }                    if (pasteText.Substring(i, 1) == "\n")                    {                        nnum++;                    }                }                Object[,] data;                //粘贴板上的数据来自于EXCEL时，每行末都有\n，在DATAGRIDVIEW内复制时，最后一行末没有\n                if (pasteText.Substring(pasteText.Length - 1, 1) == "\n")                {                    nnum = nnum - 1;                }                tnum = tnum / (nnum + 1);                data = new object[nnum + 1, tnum + 1];//定义一个二维数组                String rowstr;                rowstr = "";                //MessageBox.Show(pasteText.IndexOf("B").ToString());                //对数组赋值                for (int i = 0; i < (nnum + 1); i++)                {                    for (int colIndex = 0; colIndex < (tnum + 1); colIndex++)                    {                        //一行中的最后一列                        if (colIndex == tnum && pasteText.IndexOf("\r") != -1)                        {                            rowstr = pasteText.Substring(0, pasteText.IndexOf("\r"));                        }                        //最后一行的最后一列                        if (colIndex == tnum && pasteText.IndexOf("\r") == -1)                        {                            rowstr = pasteText.Substring(0);                        }                        //其他行列                        if (colIndex != tnum)                        {                            rowstr = pasteText.Substring(0, pasteText.IndexOf("\t"));                            pasteText = pasteText.Substring(pasteText.IndexOf("\t") + 1);                        }                        data[i, colIndex] = rowstr;                    }                    //截取下一行数据                    pasteText = pasteText.Substring(pasteText.IndexOf("\n") + 1);                }                //获取当前选中单元格所在的列序号                int curntindex = DBGrid.CurrentRow.Cells.IndexOf(DBGrid.CurrentCell);                //获取获取当前选中单元格所在的行序号                int rowindex = DBGrid.CurrentRow.Index;                //MessageBox.Show(curntindex.ToString ());                for (int j = 0; j < (nnum + 1); j++)                {                    for (int colIndex = 0; colIndex < (tnum + 1); colIndex++)                    {                        if (!DBGrid.Columns[colIndex + curntindex].Visible)                        {                            continue;                        }                        if (!DBGrid.Rows[j + rowindex].Cells[colIndex + curntindex].ReadOnly)                        {                            DBGrid.Rows[j + rowindex].Cells[colIndex + curntindex].Value = data[j, colIndex];                        }                    }                }                Clipboard.Clear();            }            catch            {                Clipboard.Clear();                //MessageBox.Show("粘贴区域大小不一致");                return;            }        }        public void p_Data_KeyDown(object sender, KeyEventArgs e)        {            if (Control.ModifierKeys == Keys.Control && e.KeyCode == Keys.V)            {                if (sender != null && sender.GetType() == typeof(DataGridView))                    // 调用上面的粘贴代码                    DataGirdViewCellPaste((DataGridView)sender);            }            else            {                Clipboard.SetDataObject(dataGridView1.GetClipboardContent().GetData(DataFormats.Text).ToString());            };        }        public void p_Data_KeyUp(object sender, KeyEventArgs e)        {            if (e.KeyCode == Keys.C && e.Modifiers == Keys.Control)            {                Clipboard.SetText(this.dataGridView1.GetClipboardContent().GetData(DataFormats.Text).ToString());            }        }        private void listBox1_SelectedIndexChanged(object sender, EventArgs e)        {        }        //http://www.cnblogs.com/hoosway/archive/2014/06/18/DataGridView.html    }}

以上为主要代码

如果报超时错误加上 sqlcmd.CommandTimeout = 1000000;就可以了，这句话是设置sql请求超时时间