虽然挺简单,但是从中学到不少,写程序没用多长时间,代码也简单,从excel文件加载下数据,然后就把加载的数据作为数据库进行sql查询就可以了,但中途是问题百出啊,各种错啊,笔者是C#初学,算了,码吧,太乱啦
View Code
1 using System; 2 using System.Collections.Generic; 3 using System.ComponentModel; 4 using System.Data; 5 using System.Drawing; 6 using System.Linq; 7 using System.Text; 8 using System.Windows.Forms; 9 using System.Data.OleDb; 10 11 12 13 namespace GradeStatistics 14 { 15 public partial class Form1 : Form 16 { 17 int obset = 0; 18 int s_p = 0; 19 int s_e = 0; 20 int obset_2 = 0; 21 int s_p_2 = 0; 22 int s_e_2 = 0; 23 string fileName = ""; 24 string sheetName = ""; 25 public Form1() 26 { 27 InitializeComponent(); 28 } 29 30 private void button1_Click(object sender, EventArgs e) 31 { 32 if (fileName == "" || sheetName == "") 33 { 34 MessageBox.Show("请从右侧输入数据源,并确认!"); 35 } 36 else 37 { 38 if (obset == 0 || this.comboBox1.Text == "") 39 { 40 MessageBox.Show("请选择统计科目!"); 41 } 42 if (s_p == 0) 43 { 44 MessageBox.Show("请填写起始分数!"); 45 } 46 if (s_e == 0) 47 { 48 MessageBox.Show("请填写结束分数!"); 49 } 50 if (obset == 1 && s_p == 1 && s_e == 1) 51 { 52 if (int.Parse(this.textBox1.Text) > int.Parse(this.textBox2.Text)) 53 { 54 MessageBox.Show("请填写正确的统计区间!"); 55 } 56 } 57 if (this.comboBox1.Text != "" && obset == 1 && s_p == 1 && s_e == 1 && int.Parse(this.textBox1.Text) < int.Parse(this.textBox2.Text)) 58 { 59 string strOdbcCon = @"Provider=Microsoft.ACE.OLEDB.12.0; Persist Security Info=False;Data Source="+fileName+"; Extended Properties=Excel 8.0"; 60 OleDbConnection excelCon = new OleDbConnection(strOdbcCon); 61 string selectSql = @"select 班级,count(" + this.comboBox1.Text + ") as " + this.comboBox1.Text + "统计人数 from [" + sheetName + "$] where " + this.comboBox1.Text + ">=" + textBox1.Text + " and " + this.comboBox1.Text + "<=" + textBox2.Text + " group by 班级"; 62 this.label5.Text = this.comboBox1.Text; 63 OleDbDataAdapter OleDat = new OleDbDataAdapter(selectSql, excelCon); 64 DataTable dt = new DataTable(); 65 OleDat.Fill(dt); 66 this.dataGridView1.DataSource = dt.DefaultView; 67 } 68 } 69 } 70 71 private void comboBox1_SelectedIndexChanged(object sender, EventArgs e) 72 { 73 obset = 1; 74 } 75 76 private void textBox1_TextChanged(object sender, EventArgs e) 77 { 78 s_p = 1; 79 } 80 81 private void textBox2_TextChanged(object sender, EventArgs e) 82 { 83 s_e = 1; 84 } 85 86 private void button2_Click(object sender, EventArgs e) 87 { 88 if (fileName == "" || sheetName == "") 89 { 90 MessageBox.Show("请从右侧输入数据源,并确认!"); 91 } 92 else 93 { 94 if (obset_2 == 0 || this.comboBox2.Text == "") 95 { 96 MessageBox.Show("请选择统计科目!"); 97 } 98 if (s_p_2 == 0) 99 { 100 MessageBox.Show("请填写起始分数!"); 101 } 102 if (s_e_2 == 0) 103 { 104 MessageBox.Show("请填写结束分数!"); 105 } 106 if (obset_2 == 1 && s_p_2 == 1 && s_e_2 == 1) 107 { 108 if (int.Parse(this.textBox3.Text) < int.Parse(this.textBox4.Text)) 109 { 110 MessageBox.Show("请填写正确的统计区间!"); 111 } 112 } 113 if (this.comboBox2.Text != "" && obset_2 == 1 && s_p_2 == 1 && s_e_2 == 1 && int.Parse(this.textBox3.Text) > int.Parse(this.textBox4.Text)) 114 { 115 string strOdbcCon_2 = @"Provider=Microsoft.ACE.OLEDB.12.0; Persist Security Info=False;Data Source="+fileName+"; Extended Properties=Excel 8.0"; 116 OleDbConnection excelCon_2 = new OleDbConnection(strOdbcCon_2); 117 string selectSql_2 = @"select 班级,count(" + this.comboBox2.Text + ") as " + this.comboBox2.Text + "统计人数 from ["+sheetName+"$] where " + this.comboBox2.Text + ">=" + textBox4.Text + " and " + this.comboBox2.Text + "<=" + textBox3.Text + " group by 班级"; 118 this.label11.Text = this.comboBox2.Text; 119 OleDbDataAdapter OleDat_2 = new OleDbDataAdapter(selectSql_2, excelCon_2); 120 DataTable dt_2 = new DataTable(); 121 OleDat_2.Fill(dt_2); 122 this.dataGridView2.DataSource = dt_2.DefaultView; 123 } 124 } 125 } 126 127 private void comboBox2_SelectedIndexChanged(object sender, EventArgs e) 128 { 129 obset_2 = 1; 130 } 131 132 private void textBox4_TextChanged(object sender, EventArgs e) 133 { 134 s_p_2 = 1; 135 } 136 137 private void textBox3_TextChanged(object sender, EventArgs e) 138 { 139 s_e_2 = 1; 140 } 141 142 private void button3_Click(object sender, EventArgs e) 143 { 144 string fileName_che = this.textBox5.Text; 145 string sheetName_che = this.textBox6.Text; 146 string strOdbcCon_che = @"Provider=Microsoft.ACE.OLEDB.12.0; Persist Security Info=False;Data Source="+fileName_che+"; Extended Properties=Excel 8.0"; 147 OleDbConnection excelCon_che = new OleDbConnection(strOdbcCon_che); 148 string selectSql_che = @"select * from [" + sheetName_che + "$]"; 149 //this.label5.Text = this.comboBox1.Text; 150 OleDbDataAdapter OleDat_che = new OleDbDataAdapter(selectSql_che, excelCon_che); 151 DataTable dt_che = new DataTable(); 152 OleDat_che.Fill(dt_che); 153 this.dataGridView3.DataSource = dt_che.DefaultView; 154 MessageBox.Show("请在下方查看数据源是否正确,正确请点击确认数据源!"); 155 } 156 157 private void button4_Click(object sender, EventArgs e) 158 { 159 fileName = this.textBox5.Text; 160 sheetName = this.textBox6.Text; 161 MessageBox.Show("已经确认选择数据源!"); 162 } 163 } 164 }