抽獎升級版 可以經表格數據導入資料庫，抽獎設置，補抽

  1 using System;
  2 using System.Collections.Generic;
  3 using System.ComponentModel;
  4 using System.Data;
  5 using System.Drawing;
  6 using System.Drawing.Imaging;
  7 using System.Linq;
  8 using System.Text;
  9 using System.Threading.Tasks;
 10 using System.Windows.Forms;
 11 using System.Data.SqlClient;
 12 using System.Collections;
 13 using System.IO;
 14 
 15 namespace Check_Ticket
 16 {
 17     public partial class Btn_UpLoadFile : Form
 18     {
 19         string conn = "server=WIN-OUD59R7EHCH;database=Xtf;uid=sa;pwd=123456";
 20         string sql = "";
 21         int CurNum = 0;//獎項人數
 22         int second = 0;//防止二次被抽取
 23         Hashtable hashtable2 = new Hashtable();//存儲編號
 24         Hashtable hashtable1 = new Hashtable();//存儲獲獎名單
 25         Hashtable hashtable = new Hashtable();//避免二次抽獎
 26         Dictionary<int, string> dict = new Dictionary<int, string>();//詞典存儲所有參賽者
 27         Random rd = new Random();
 28         int time = 1;
 29         Label[] label = new Label[100];
 30         int index = 0;//詞典key鍵值
 31         int total = 0;//label個數
 32         int num = 0;//獲獎總數
 33         string s = "";
 34         int End = 0;//抽獎結束
 35         public Btn_UpLoadFile()
 36         {
 37             InitializeComponent();
 38         }
 39         private void Form1_Load(object sender, EventArgs e)
 40         {
 41             dataGridView2.ForeColor = Color.Blue;
 42             menu.Visible = false;
 43             comboBox1.SelectedIndex = 0;
 44             this.timer1.Interval = 10;
 45             this.timer3.Interval = 100;
 46             this.timer1.Enabled = true;
 47             this.WindowState = FormWindowState.Maximized;
 48         }
 49         private void getNumber(string s)
 50         {
 51             SqlConnection myconn = new SqlConnection(conn);
 52             myconn.Open();
 53             sql = "select *from SumPrize";
 54             SqlDataAdapter da = new SqlDataAdapter(sql, myconn);
 55             DataSet ds = new DataSet();
 56             da.Fill(ds);
 57             switch (s)
 58             {
 59                 case "一等獎":
 60                     second = 1;
 61                     CurNum = Convert.ToInt32(ds.Tables[0].Rows[0]["PrizeNum"].ToString());
 62                     break;
 63                 case "二等獎":
 64                     second = 2;
 65                     CurNum = Convert.ToInt32(ds.Tables[0].Rows[1]["PrizeNum"].ToString());
 66                     break;
 67                 case "三等獎":
 68                     second = 3;
 69                     CurNum = Convert.ToInt32(ds.Tables[0].Rows[2]["PrizeNum"].ToString());
 70                     break;
 71                 case "四等獎":
 72                     second = 4;
 73                     CurNum = Convert.ToInt32(ds.Tables[0].Rows[3]["PrizeNum"].ToString());
 74                     break;
 75                 case "五等獎":
 76                     second = 5;
 77                     CurNum = Convert.ToInt32(ds.Tables[0].Rows[4]["PrizeNum"].ToString());
 78                     break;
 79                 case "六等獎":
 80                     second = 6;
 81                     CurNum = Convert.ToInt32(ds.Tables[0].Rows[5]["PrizeNum"].ToString());
 82                     break;
 83                 case "請選擇":
 84                     CurNum = 0;
 85                     break;
 86                 default:
 87                     break;
 88             }
 89         }
 90         private void button1_Click(object sender, EventArgs e)
 91         {
 92             SqlConnection myconn = new SqlConnection(conn);
 93             myconn.Open();
 94             //未選擇抽獎項
 95             if (CurNum == 0)
 96             {
 97                 MessageBox.Show("未設置該獎項或未選擇獎項，抽獎沒有意義！！！");
 98                 return;
 99             }
100             //處理已經被抽獎項
101 
102             if (!hashtable1.ContainsValue(second))
103             {
104                 comboBox1.Enabled = true;
105                 hashtable1.Add(second, second);
106             }
107             else
108             {
109                 MessageBox.Show("此獎項已經被抽過，換個獎項吧？");
110                 return;
111             }
112             string prize = comboBox1.Text.ToString();
113             string sqlCount = "Select sum(PrizeNum) as num from SumPrize";
114             SqlDataAdapter sda = new SqlDataAdapter(sqlCount, myconn);
115             DataSet ds = new DataSet();
116             sda.Fill(ds);
117             //設置有獎總人數
118             num = Convert.ToInt32(ds.Tables[0].Rows[0]["num"].ToString());
119             for (int i = 0; i < dataGridView1.RowCount-1; i++)
120             {
121                 dict.Add(index, dataGridView1.Rows[i].Cells["WorkerID"].Value.ToString() + " " + dataGridView1.Rows[i].Cells["Name"].Value.ToString());
122                 index++;
123             }
124            
125             myconn.Close();
126             timer2.Start();
127             clearLabel();
128             bornLabel();
129             timer1.Enabled = true;
130             comboBox1.Enabled = false;
131         }
132 
133         private void button3_Click(object sender, EventArgs e)
134         {
135             End = 0;
136             menu.Text = "";
137             hashtable1.Clear();
138             clearLabel();
139             button1.Enabled = true;
140             button5.Enabled = true;
141             button2.Enabled = false;
142             menu.Visible = false;
143             btnAdd.Visible = false;
144             tbNum.Visible = false;
145             ge.Visible = false;
146             addEnd.Visible = false;
147             lab1.Text = "中獎名單";
148             dataGridView2.Visible = false;
149             string sqlChecked = "delete from Checked";
150             sql = "select *from Ticket";
151             SqlConnection myconn = new SqlConnection(conn);
152             myconn.Open();
153             SqlDataAdapter sda = new SqlDataAdapter(sql, myconn);
154             DataSet ds = new DataSet();
155             sda.Fill(ds, "Ticket");
156             dataGridView1.DataSource = ds;
157             dataGridView1.DataMember = "Ticket";
158             SqlCommand mycomm = new SqlCommand(sqlChecked, myconn);
159             mycomm.ExecuteNonQuery();
160             myconn.Close();
161         }
162 
163         private void button2_Click(object sender, EventArgs e)
164         {
165             menu.Visible = false;
166             clearLabel();
167             sql = "select WorkerIDAndName,Prize from Checked order by PrizeID";
168             SqlConnection myconn = new SqlConnection(conn);
169             myconn.Open();
170             SqlDataAdapter sda = new SqlDataAdapter(sql, myconn);
171             DataSet ds1 = new DataSet();
172             sda.Fill(ds1, "Checked");
173             dataGridView2.DataSource = ds1;
174             dataGridView2.DataMember = "Checked";
175             dataGridView2.Visible = true;
176             menu.Text += "恭喜：\n";
177             for(int i=0;i<dataGridView2.RowCount-1;i++)
178             {
179                 menu.Text += dataGridView2.Rows[i].Cells["WorkerIDAndName"].Value.ToString() +"  獲得"+ dataGridView2.Rows[i].Cells["Prize"].Value.ToString() + "\n";
180             }
181             menu.Visible = true;
182             timer3.Start();
183             myconn.Close();
184         }
185 
186         private void button4_Click(object sender, EventArgs e)
187         {
188             new SetPeople().ShowDialog();
189         }
190 
191         private void comboBox1_SelectedValueChanged(object sender, EventArgs e)
192         {
193             getNumber(comboBox1.Text.ToString());
194         }
195 
196         private void timer1_Tick(object sender, EventArgs e)
197         {
198             lab1.Left = lab1.Left - 6;
199             if (lab1.Right < 0)
200             {
201                 lab1.Left = this.Width;
202             }
203         }
204         //創建labels
205         private void bornLabel()
206         {
207             int i = 0;
208             for (i = 0; i < CurNum; i++)
209             {
210                 label[i] = new Label();
211                 label[i].ForeColor = Color.Blue;
212                 label[i].Location = new System.Drawing.Point(350, 170 + (i * 50));
213                 label[i].Size = new System.Drawing.Size(320, 40);
214                 label[i].BackColor = Color.Transparent;
215                 label[i].Anchor = (AnchorStyles.Top);
216                 label[i].Font = new System.Drawing.Font("SimSun", 20, FontStyle.Bold);
217                 this.Controls.Add(label[i]);
218             }
219             total = i;
220         }
221         //清除labels
222         private void clearLabel()
223         {
224             for (int i = 0; i < total; i++)
225             {
226                 this.Controls.Remove(label[i]);
227             }
228         }
229         private void timer2_Tick(object sender, EventArgs e)
230         {
231             hashtable.Clear();
232             hashtable2.Clear();
233             SqlConnection con = new SqlConnection(conn);
234             string sql = "select count(*) from Ticket";
235             SqlCommand com = new SqlCommand(sql, con);
236             con.Open();
237             int emcount = Convert.ToInt32(com.ExecuteScalar());
238             con.Close();
239 
240             timer2.Enabled = true;
241 
242             timer2.Interval = time;
243             int i;
244             for (i = 0; i < CurNum; i++)
245             {
246                 int random = Convert.ToInt32(rd.Next(0, emcount));
247                 if (dataGridView1.Rows[random].Cells["Checked"].Value.ToString().Trim()=="1" 
248                     && !hashtable.ContainsValue(dict[random]))
249                 {
250                     hashtable2.Add(random,random);
251                     hashtable.Add(dict[random], dict[random]);
252                     label[i].Text = dict[random];
253                 }
254                 else
255                 {
256                     i--;
257                 }
258 
259             }
260         }
261 
262         private void button5_Click(object sender, EventArgs e)
263         {
264             comboBox1.Enabled = true;
265             SqlConnection con = new SqlConnection(conn);
266             con.Open();
267             string sql = "";
268             s = "";
269             timer2.Stop();
270             foreach (DictionaryEntry de in hashtable)
271             {
272                 sql = string.Format("insert into Checked(WorkerIDAndName,Prize,PrizeID) values('{0}','{1}','{2}')", de.Value.ToString(), comboBox1.Text.ToString(),second.ToString());
273                 SqlCommand com = new SqlCommand(sql, con);
274                 com.ExecuteNonQuery(); 
275                 s += de.Value.ToString() +"   "; 
276             }
277             foreach (DictionaryEntry de in hashtable2)
278             {
279                 dataGridView1.Rows[Convert.ToInt32(de.Key)].Cells["Checked"].Value = "0";
280             }
281             lab1.Text = "恭喜： " + s + "獲得" + comboBox1.Text.ToString();
282             //判斷抽獎結束
283             End = End + CurNum;
284             if (End == num)
285             {
286                 button1.Enabled = false;
287                 //button5.Enabled = false;
288                 button2.Enabled = true;
289                 if (MessageBox.Show("抽獎結束,是否進行補抽，點擊確定進行補抽，取消結束此次抽獎！", "溫馨提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
290                 {
291                     btnAdd.Visible = true;
292                     tbNum.Visible = true;
293                     ge.Visible = true;
294                     addEnd.Visible = true;
295                 }
296             }
297             
298         }
299 
300         private void timer3_Tick(object sender, EventArgs e)
301         {
302             menu.Top = menu.Top - 6 ;
303             if (menu.Bottom < 0)
304             {
305                 menu.Top = this.Height-600;
306             }
307         }
308 
309         private void 關閉視窗ToolStripMenuItem_Click(object sender, EventArgs e)
310         {
311             this.Close();
312         }
313 
314         private void btnAdd_Click(object sender, EventArgs e)
315         {
316             CurNum = Convert.ToInt32(tbNum.Text.ToString());
317             if (CurNum == 0)
318             {
319                 MessageBox.Show("請輸入補抽個數!!!", "溫馨提示");
320                 return;
321             }
322             End = End + CurNum;
323             if (End > Convert.ToInt32(dataGridView1.RowCount))
324             {
325                 button5.Enabled = false;
326                 MessageBox.Show("抽獎超過參與人數無法進行補抽!","溫馨提示");
327                 return;
328             }
329             timer2.Start();
330             clearLabel();
331             bornLabel();
332         }
333 
334         private void addEnd_Click(object sender, EventArgs e)
335         {
336             btnAdd.Visible = false;
337             addEnd.Visible = false;
338             tbNum.Visible = false;
339             ge.Visible = false;
340             menu.Visible = false;
341             clearLabel();
342             sql = "select WorkerIDAndName,Prize from Checked order by PrizeID";
343             SqlConnection myconn = new SqlConnection(conn);
344             myconn.Open();
345             SqlDataAdapter sda = new SqlDataAdapter(sql, myconn);
346             DataSet ds1 = new DataSet();
347             sda.Fill(ds1, "Checked");
348             dataGridView2.DataSource = ds1;
349             dataGridView2.DataMember = "Checked";
350             menu.Text += "恭喜：\n";
351             for (int i = 0; i < dataGridView2.RowCount - 1; i++)
352             {
353                 menu.Text += dataGridView2.Rows[i].Cells["WorkerIDAndName"].Value.ToString() + "  獲得" + dataGridView2.Rows[i].Cells["Prize"].Value.ToString() + "\n";
354             }
355             menu.Visible = true;
356             timer3.Start();
357             myconn.Close();
358         }
359 
360         //先導入到dataSet
361         public DataSet getXSLData(string filepath)
362         {
363             string strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=\"Excel 12.0;HDR=YES;\"";
364             System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon);
365             string strCom = "SELECT * FROM [Sheet1$]";
366             Conn.Open();
367             System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn);
368             DataSet ds = new DataSet();
369             myCommand.Fill(ds, "[Sheet1$]");
370             Conn.Close();
371             return ds;
372         }
373 
374         public static int errorcount = 0;//記錄錯誤信息條數
375         public static int insertcount = 0;//記錄插入成功條數
376         public static int updatecount = 0;//記錄更新信息條數
377 
378         public bool ImportXSL(string home)
379         {
380             try
381             {
382                 DataSet ds = new DataSet();
383                 //取得數據集
384                 //調用上面的函數
385                 ds = getXSLData(@home);
386 
387                 SqlConnection con = new SqlConnection(conn);
388                 con.Open();
389                 for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
390                 {
391                     string WorkerID = ds.Tables[0].Rows[i][1].ToString();
392                     string Name = ds.Tables[0].Rows[i][2].ToString();
393                     string Checked = ds.Tables[0].Rows[i][3].ToString();
394 
395                     if (WorkerID != "" && Name != "")
396                     {
397                         string sq = string.Format("select * from Ticket where WorkerID='{0}' and Name='{1}'", WorkerID, Name);
398                         SqlCommand selectcom = new SqlCommand(sq, con);
399                         int count = Convert.ToInt32(selectcom.ExecuteScalar());
400                         if (count > 0)
401                         {
402                             updatecount++;