using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using Excel = Microsoft.Office.Interop.Excel;
namespace RYFZ
{
public partial class frmMain : Form
{
public frmMain()
{
InitializeComponent();
}
static string temp;//临时字符串来判断是点了哪个按钮
static string Path;//定义全局Path方便操作
public static DataSet ds2;//设置为全局变量从而得到导入Excel的数据，再传到frmView中的dataGridView；
private void timer1_Tick(object sender, EventArgs e)
{
this.toolStripStatusLabel1.Text = "当前时间：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
}
private void btnExport_Click(object sender, EventArgs e)
{
string str1 = "btnExport";
temp = str1;
if (cbGroupStyle.SelectedItem == null)
{
MessageBox.Show("请选择分组方式！");
}
else
{
if (txtNum.Text == "" || txtNum.Text == "0")
{
MessageBox.Show("请输入人数或组数！");
}
else
{
DataSetToExcel(ds2, true);
}
}
}
private void 选择导入的表ToolStripMenuItem_Click(object sender, EventArgs e)
{
OpenFileDialog openExcel = new OpenFileDialog();
openExcel.Filter = "Excel文件|*.xls|Excel文件|*.xlsx";
openExcel.Title = "导入Excel表";
if (openExcel.ShowDialog() == DialogResult.Cancel)//没有打开文件
{
return;
}
Path = openExcel.FileName;
DataSet ds1 = ExcelToDS(@"" + Path + "");
if (ds1 != null)
{
MessageBox.Show("导入表成功！");
}
else
{
MessageBox.Show("导入表失败！");
}
ds2 = ds1;//使全局 DataSet 得到
dataGridView1.DataSource = ds1.Tables[0].DefaultView;
}
public DataSet ExcelToDS(string Path)
{
string strConn = "Provider=Microsoft.Ace.OLEDB.12.0;" + "Data Source=" + Path + ";" + "Extended Properties='Excel 12.0;IMEX=1;hdr=yes;'";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();//打开excel连接
OleDbDataAdapter myCommand = null;
System.Data.DataTable table = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string tableName = table.Rows[0]["Table_Name"].ToString();//获取表名
string strExcel = "select * from [" + tableName + "]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
DataSet ds = new DataSet();//实例化一个DataSet
myCommand.Fill(ds);
ds2 = ds;
return ds;
}
public bool DataSetToExcel(DataSet dataSet, bool isShowExcle)
{
if (dataSet == null)
MessageBox.Show("请导入Excel表格");
else
{
System.Data.DataTable dataTable = dataSet.Tables[0];
System.Data.DataTable DT = ds2.Tables[0];
System.Data.DataTable newdt = dataTable.Clone();
foreach (DataRow row in dataTable.Rows)
{
DataRow dr = newdt.NewRow();
dr.ItemArray = row.ItemArray;
System.Threading.Thread.Sleep(50);
Random random = new Random();
newdt.Rows.InsertAt(dr, random.Next(newdt.Rows.Count));//产生随机数并填充DataTable
}
int rowNumber = newdt.Rows.Count;//行数
int columnNumber = newdt.Columns.Count;//列数
if (rowNumber == 0)
{
MessageBox.Show("请导入Excel文件！");
return false;
}
Excel.Application excel = new Excel.Application();
excel.Application.Workbooks.Add(true);
int groupNum = Int32.Parse(txtNum.Text);//每一组的人数或者分的组数
int peopleNum = newdt.Rows.Count / groupNum;//总人数/组数或人数
int h = 0;
int i = newdt.Rows.Count % groupNum;
int k = 1;
if (groupNum > newdt.Rows.Count)
{
MessageBox.Show("分组数或人数大于总人数", "出错了", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
for (int c = 0; c < rowNumber; c++)
{
for (int j = 1; j < columnNumber; j++)
{
excel.Cells[c + 2, j + 1] = newdt.Rows[c].ItemArray[j];//填充数据
}
}
switch (cbGroupStyle.Text) //按分组条件实现相应分组
{
case "按组数":
{
for (int l = 2; l <= i * (peopleNum + 1); l += peopleNum + 1)
{
k += peopleNum + 1;
h++;
excel.Cells[l, 1] = "第" + h + "组";
}
for (int g = k + 1; g <= newdt.Rows.Count; g += peopleNum)
{
h++;
excel.Cells[g, 1] = "第" + h + "组";
}
}
break;
case "按人数":
{
for (int g = k + 1; g <= newdt.Rows.Count; g += groupNum)
{
h++;
excel.Cells[g, 1] = "第" + h + "组";
}
break;
}
case "随机分":
{
for (int l = 2; l <= i * (peopleNum + 1); l += peopleNum + 1)
{
k += peopleNum + 1;
h++;
excel.Cells[l, 1] = "第" + h + "组";
}
for (int g = k + 1; g <= newdt.Rows.Count; g += peopleNum)
{
h++;
excel.Cells[g, 1] = "第" + h + "组";
}
}
break;
case "升序分":
{
for (int c = 0; c < rowNumber; c++)
{
for (int j = 1; j < columnNumber; j++)
{
excel.Cells[c + 2, j + 1] = dataTable.Rows[c].ItemArray[j];
}
}
for (int l = 2; l <= i * (peopleNum + 1); l += peopleNum + 1)
{
k += peopleNum + 1;
h++;
excel.Cells[l, 1] = "第" + h + "组";
}
for (int g = k + 1; g <= dataTable.Rows.Count; g += peopleNum)
{
h++;
excel.Cells[g, 1] = "第" + h + "组";
}
}
break;
case "降序分":
{
for (int c = rowNumber; c > 0; c--)
{
for (int j = 1; j < columnNumber; j++)
{
excel.Cells[rowNumber + 2 - c, j + 1] = dataTable.Rows[c - 1].ItemArray[j];
}
}
for (int l = 2; l <= i * (peopleNum + 1); l += peopleNum + 1)
{
k += peopleNum + 1;
h++;
excel.Cells[l, 1] = "第" + h + "组";
}
for (int g = k + 1; g <= dataTable.Rows.Count; g += peopleNum)
{
h++;
excel.Cells[g, 1] = "第" + h + "组";
}
}
break;
}
//为分组后的Excel添加列名
int m = 1;
excel.Cells[1, 1] = "组别";
for (int n = 2; n <= columnNumber; n++)
{
excel.Cells[m, n] = DT.Columns[n - 1].Caption;
}
//根据按钮来显示是导出还是打印
if (temp == "btnExport")
{
excel.Visible = true;
}
if (temp == "btnPrint")
{
excel.Visible = true;
excel.Application.Sheets.PrintOut(Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
DialogResult drst = MessageBox.Show("是否确定要删除记录？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (drst == DialogResult.Yes)
{
cbGroupStyle.Text = "";
txtNum.Text = "";
ds2.Tables[0].Rows.Clear();//清空数据
dataGridView1.DataSource = ds2.Tables[0];
dataGridView1.DataSource = null;//导出分组后将 dataGridView1 的数据源清空
}
}
}
}
return true;
}
private void 关于本工具ToolStripMenuItem_Click(object sender, EventArgs e)
{
frmAbout frm = new frmAbout();
frm.ShowDialog();
}
private void 访问作者博客ToolStripMenuItem_Click(object sender, EventArgs e)
{
System.Diagnostics.Process.Start("");
}
private void btnPrint_Click(object sender, EventArgs e)
{
string str2 = "btnPrint";
temp = str2;
DataSetToExcel(ds2, true);
}
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
switch (cbGroupStyle.Text)
{
case "按人数":
{
labPeopleSum.Text = "输入人数：";
labPeopleSum.Show();
txtNum.Show();
}
break;
case "升序分":
{
labPeopleSum.Text = "输入组数：";
labPeopleSum.Show();
txtNum.Show();
}
break;
case "降序分":
{
labPeopleSum.Text = "输入组数：";
labPeopleSum.Show();
txtNum.Show();
}
break;
case "按组数":
{
labPeopleSum.Text = "输入组数：";
labPeopleSum.Show();
txtNum.Show();
}
break;
case "随机分":
{
labPeopleSum.Text = "输入组数：";
labPeopleSum.Show();
txtNum.Show();
}
break;
}
}
private void txtNum_KeyPress(object sender, KeyPressEventArgs e)
{
//IsNumber的作用是判断输入按键是否为数字，否则不能输入其他字符
//(char)8是推个键的键值，可以允许用户敲退格键对输入的数字进行修改
if (!(Char.IsNumber(e.KeyChar)) && e.KeyChar != (char)8)
{
e.Handled = true;
}
}
private void frmMain_FormClosing(object sender, FormClosingEventArgs e)
{
DialogResult drst = MessageBox.Show("确认要人员分组工具吗？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (drst == DialogResult.Yes)
{
Application.ExitThread();
}
else
{
e.Cancel = true;
}
}
}
}
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
