直接上代码,下注释掉的代码为导出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 = "denglin.wang@edaysoft.cn";
string To = "alex.cheng@dominos.com.cn,yunying.liu@edaysoft.cn,andy.qiu@dominos.com.cn";
//string To = "denglin.wang@edaysoft.cn,denglin.wang@edaysoft.cn";
string cc = "denglin.wang@edaysoft.cn";
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("denglin.wang@edaysoft.cn", "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请求超时时间