//people的数据集合)
//把数据写入一个工作簿
private void writedata(peopleSet ps)
{
createExcel();
Sheets w_shs = w_wbk.Sheets; //获取excel工作簿的所有工作表
_Worksheet w_wsh = (_Worksheet)w_shs.get_Item(1); //获取第一个工作表
long i = 2;
int lineheader = 1;//定义列头开始行
int n = ps.peopledic.Count;
this.label3.Text = "正在写入汇总结果，请稍后……";
w_wsh.Cells[lineheader, ps.pID_column] = "体检号";
w_wsh.Cells[lineheader, ps.PNname_column] = "姓名";
w_wsh.Cells[lineheader, ps.pSex_column] = "性别";
w_wsh.Cells[lineheader, ps.pAge_column] = "年龄";
foreach (KeyValuePair<KeyValuePair<String, String>, int> kp in itemDic)
{
w_wsh.Cells[lineheader, kp.Value] = kp.Key.Value;
}
foreach (KeyValuePair<String,peopledata> p in ps.peopledic)
{
w_wsh.Cells[i,ps.pID_column]=p.Key;
w_wsh.Cells[i,ps.PNname_column] = p.Value.name;
w_wsh.Cells[i, ps.pSex_column] = p.Value.sex;
w_wsh.Cells[i, ps.pAge_column] = p.Value.age;
int itcolumn;
foreach (KeyValuePair<KeyValuePair<String, String>, String> it in p.Value.element)
{
itcolumn = itemDic[it.Key]; //获取行号
w_wsh.Cells[i, itcolumn] = it.Value; //写入相应的值
}
SetTextMessage((int)(100 * (i -1)/ n));
++i;
}
CloseExcel(w_wbk);
}
private void MergeData(peopleSet ps_D, peopleSet ps_S)
{
foreach (KeyValuePair<string,peopledata> kvp in ps_S.peopledic)
{
if (ps_D.peopledic.ContainsKey(kvp.Key))
{
foreach (KeyValuePair<KeyValuePair<string, string>, string> k in kvp.Value.element)
{
if (ps_D.peopledic[kvp.Key].element.ContainsKey(k.Key))
{
ps_D.peopledic[kvp.Key].element[k.Key] = k.Value;
}
}
}
}
}
private void solve()
{
peopleSet ps; //people的数据集合
DateTime startTime = DateTime.Now;
DateTime endTime;
ps = collectdata(txtboxPath.Text); //读取数据
writedata(ps); //写数据
label3.Text = "完成";
endTime = DateTime.Now;
TimeSpan costTime = endTime.Subtract(startTime);
MessageBox.Show("处理完成"+"\n"+"本次处理花费"+costTime.Minutes.ToString()+"分钟");
app.Quit(); //退出Excel
KillExcel(app);
app = null;
threadStart = true;
}
//读取一个工作簿的检查数据，并存进peoples的集合中
private peopleSet collectdata(string fileWay)
{
peopleSet ps = new peopleSet(2); //people的数据集合
openExcel(fileWay);
String curPeopleID; //存储当前的检测用户
Sheets r_shs = r_wbk.Sheets; //获取excel工作簿的所有工作表
_Worksheet r_wsh = (_Worksheet)r_shs.get_Item(1); //获取第一个工作表
long i=1; //记录行的位置
if (Convert.ToString(((Range)r_wsh.Cells[i, ps.pItemData_column]).Text) == "") //第一行为空
i = 2;
int j=ps.itemBegin_column; //数据项的起始位置
peopledata curpeople ; // 存取当前的体检人员的数据
KeyValuePair<String,String> itemset_item;
long allrow = r_wsh.UsedRange.CurrentRegion.Rows.Count;
this.label3.Text = "正在读取数据,请稍后……";
while(i <= allrow)
{
curPeopleID = Convert.ToString(((Range)r_wsh.Cells[i, ps.pID_column]).Text);
if (ps.peopledic.ContainsKey(curPeopleID))//该用户ID已经记录
{
itemset_item = new KeyValuePair<String,String>(Convert.ToString(((Range)r_wsh.Cells[i,ps.pItemSet_column]).Text),Convert.ToString(((Range)r_wsh.Cells[i,ps.pItem_column]).Text));
if (Convert.ToString(((Range)r_wsh.Cells[i, ps.pItemData_column]).Text) == "NULL")
{
ps.peopledic[curPeopleID].element.Add(itemset_item, Convert.ToString(((Range)r_wsh.Cells[i, ps.pItemData_column + 1]).Text));
}
else
{
ps.peopledic[curPeopleID].element.Add(itemset_item, Convert.ToString(((Range)r_wsh.Cells[i, ps.pItemData_column]).Text));
}
}
else //该用户没记录
{
curpeople = new peopledata();
//添加检查项目信息
itemset_item = new KeyValuePair<String, String>(Convert.ToString(((Range)r_wsh.Cells[i, ps.pItemSet_column]).Text), Convert.ToString(((Range)r_wsh.Cells[i, ps.pItem_column]).Text));
curpeople.name = Convert.ToString(((Range)r_wsh.Cells[i, ps.PNname_column]).Text);
curpeople.sex = Convert.ToString(((Range)r_wsh.Cells[i, ps.pSex_column]).Text);
curpeople.age = Convert.ToString(((Range)r_wsh.Cells[i, ps.pAge_column]).Text);
if (Convert.ToString(((Range)r_wsh.Cells[i, ps.pItemData_column]).Text) == "NULL")
{
curpeople.element.Add(itemset_item, Convert.ToString(((Range)r_wsh.Cells[i, ps.pItemData_column+1]).Text)); //增加检查项目
}
else
{
curpeople.element.Add(itemset_item, Convert.ToString(((Range)r_wsh.Cells[i,ps.pItemData_column]).Text)); //增加检查项目
}
ps.peopledic.Add(curPeopleID, curpeople);
}
if(!itemDic.ContainsKey(itemset_item))//不包含这个项目
{
itemDic.Add(itemset_item, j);
++j;
}
if (i < allrow)
{
SetTextMessage((int)(i * 100 / allrow));
}
else
{
Thread.Sleep(100);
SetTextMessage(0);
}
++i;
}
app.AlertBeforeOverwriting = false; // 屏蔽系统的alert
r_wbk.Close();
return ps;
}