private void readDataFromExcelBGWorker_DoWork_Handler(object sender, DoWorkEventArgs e)
{
AttendanceRHelper.affectedCount = 0;
foreach (string xlsFilePath in xlsFilePathList)
{
currentXlsFilePath = xlsFilePath;
//tb.Text = xlsFilePath;
readDataFromExcelBGWorker.ReportProgress(0, "tb.Text=" + xlsFilePath);
//lblResult.Visible = false;
//开启后台执行进程。
MSG msg = AttendanceRHelper.ImportAttendanceRecordToDB(currentXlsFilePath, randomStr, readDataFromExcelBGWorker);
//导入完成后进行保存，保存该文件至prepared目录中
//pb.Visible = false;
//lblPrompt.Visible = false;
//
readDataFromExcelBGWorker.ReportProgress(msg.Flag?1:0, string.Format(@"msg={0}", msg.Msg));
//timerRestoreTheLblResult.Enabled = true;
if (!msg.Flag)
{
return;
}
}
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnImportEmpsARInfo_Click(object sender, EventArgs e)
{
//判断是否存在Excel进程.
if (CmdHelper.ifExistsTheProcessByName("EXCEL.EXE"))
{
ShowResult.show(lblResult, "存在未关闭的Office Excel进程,请先关闭!", false);
return;
}
string _uncertainWSPath = _defaultDir + "\\uncertainRecord_" + TimeHelper.getCurrentTimeStr() + ".xls";
this.dgv.Visible = false;
this.dgv_same_pinyin_of_name.Visible = true;
this.dgv_same_name.Visible = false;
dgv_same_name.DataSource = null;
dgv_same_pinyin_of_name.DataSource = null;
dgv.DataSource = null;
lblResult.Text = "";
lblResult.BackColor = this.BackColor;
tb.Clear();
randomStr = TimeHelper.getCurrentTimeStr();
xlsFilePath = FileNameDialog.getSelectedFilePathWithDefaultDir("请选择考勤记录：", "*.xls|*.xls", defaultDir);
string dir = DirectoryHelper.getDirOfFile(xlsFilePath);
if (string.IsNullOrEmpty(dir))
{
return;
}
List <string> xlsFileList = DirectoryHelper.getXlsFileUnderThePrescribedDir(dir);
List <string> resultList = new List <string>();
foreach (string xlsFile in xlsFileList)
{
string fileName = DirectoryHelper.getFileNameWithoutSuffix(xlsFile);
if (!CheckString.CheckARName(fileName))
{
continue;
}
//格式符合: 3月考勤记录1。
resultList.Add(xlsFile);
}
#region 先判断第四行，是否全为数字。
int maxColIndex = 0;
if (!check4thRow(resultList, out maxColIndex))
{
return;
}
#endregion
if (cbCheckSameNamesButDifferentMachineNo.Checked)
{
#region 保存关键信息到后台.
saveCriticalARInfo(resultList);
#endregion
#region 打开4个考勤文件
for (int i = 1; i <= resultList.Count; i++)
{
switch (i)
{
case 1:
_1th_my_excel = new MyExcel(resultList[0]);
_1th_my_excel.open();
_1th_Sheet = _1th_my_excel.getFirstWorkSheetAfterOpen();
break;
case 2:
_2nd_my_excel = new MyExcel(resultList[1]);
_2nd_my_excel.open();
_2nd_Sheet = _2nd_my_excel.getFirstWorkSheetAfterOpen();
break;
case 3:
_3rd_my_excel = new MyExcel(resultList[2]);
_3rd_my_excel.open();
_3rd_Sheet = _3rd_my_excel.getFirstWorkSheetAfterOpen();
break;
case 4:
_4th_my_excel = new MyExcel(resultList[3]);
_4th_my_excel.open();
_4th_Sheet = _4th_my_excel.getFirstWorkSheetAfterOpen();
break;
}
}
#endregion
#region 创建 _uncertain_myExcel;
MyExcel uncertainRecordExcel = null;
uncertainRecordExcel = new MyExcel(_uncertainWSPath);
uncertainRecordExcel.create();
uncertainRecordExcel.open();
uncertainWS = uncertainRecordExcel.getFirstWorkSheetAfterOpen();
//先写，日期行。
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(uncertainWS);
uEHelper.writeToSpecificRow(1, 1, maxColIndex);
#endregion
System.Data.DataTable dt = getSamePinYinButName();
bool have_same_pinyin_flag = false;
if (dt != null && dt.Rows.Count > 0)
{
have_same_pinyin_flag = true;
}
//*************判断是否拼音相同 开始********************8
if (have_same_pinyin_flag)
{
this.dgv_same_pinyin_of_name.Visible = true;
this.dgv_same_pinyin_of_name.DataSource = dt;
DGVHelper.AutoSizeForDGV(dgv_same_pinyin_of_name);
ShowResult.show(lblResult, "存在姓名拼音相同的记录!", false);
this.lblPrompt.Visible = false;
timerRestoreTheLblResult.Enabled = true;
btnSwitch.Text = "查看拼音相同的记录";
btnSwitch_Click(sender, e);
#region 写记录到不确定文档中.
int theRowIndex = 0;
int Attendance_Machine_No = 0;
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
theRowIndex = int.Parse(dt.Rows[i]["行号"].ToString());
Attendance_Machine_No = int.Parse(dt.Rows[i]["卡机编号"].ToString());
switch (Attendance_Machine_No)
{
case 1:
//获取源区域
srcRange = _1th_Sheet.Range[_1th_Sheet.Cells[theRowIndex, 1], _1th_Sheet.Cells[theRowIndex + 1, maxColIndex]];
srcRange.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndex]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
//保存一下。
break;
case 2:
srcRange = _2nd_Sheet.Range[_2nd_Sheet.Cells[theRowIndex, 1], _2nd_Sheet.Cells[theRowIndex + 1, maxColIndex]];
srcRange.Cells.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndex]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
break;
case 3:
srcRange = _3rd_Sheet.Range[_3rd_Sheet.Cells[theRowIndex, 1], _3rd_Sheet.Cells[theRowIndex + 1, maxColIndex]];
srcRange.Cells.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndex]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
break;
case 4:
srcRange = _4th_Sheet.Range[_4th_Sheet.Cells[theRowIndex, 1], _4th_Sheet.Cells[theRowIndex + 1, maxColIndex]];
srcRange.Cells.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndex]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
break;
}
}
//显示该文档
CmdHelper.runCmd(_uncertainWSPath);
#endregion
string promptStr = string.Format(@"存在姓名拼音相同的记录。{0}
继续(OK), 取消导入(Cancel)。", "\r\n");
if (DialogResult.Cancel.Equals(MessageBox.Show(promptStr, "提示：", MessageBoxButtons.OKCancel, MessageBoxIcon.Information)))
{
uncertainWS.UsedRange.ColumnWidth = 3.75M;
uncertainRecordExcel.saveWithoutAutoFit();
uncertainRecordExcel.close();
closeThe4ARExcels();
return;
}
}
//closeThe4ARExcels();
//*************判断是否拼音相同 结束*****************88
//1.h
dt = getSameNameInfo();
string prompt = string.Empty;
if (dt.Rows.Count != 0)
{
btnSwitch.Text = "查看同名记录";
btnSwitch_Click(sender, e);
int theRowIndex = 0;
int Attendance_Machine_No = 0;
#region 记录书写结束.
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
theRowIndex = int.Parse(dt.Rows[i]["行号"].ToString());
Attendance_Machine_No = int.Parse(dt.Rows[i]["卡机编号"].ToString());
switch (Attendance_Machine_No)
{
case 1:
//获取源区域
srcRange = _1th_Sheet.Range[_1th_Sheet.Cells[theRowIndex, 1], _1th_Sheet.Cells[theRowIndex + 1, maxColIndex]];
srcRange.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndex]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
//保存一下。
break;
case 2:
srcRange = _2nd_Sheet.Range[_2nd_Sheet.Cells[theRowIndex, 1], _2nd_Sheet.Cells[theRowIndex + 1, maxColIndex]];
srcRange.Cells.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndex]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
break;
case 3:
srcRange = _3rd_Sheet.Range[_3rd_Sheet.Cells[theRowIndex, 1], _3rd_Sheet.Cells[theRowIndex + 1, maxColIndex]];
srcRange.Cells.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndex]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
break;
case 4:
srcRange = _4th_Sheet.Range[_4th_Sheet.Cells[theRowIndex, 1], _4th_Sheet.Cells[theRowIndex + 1, maxColIndex]];
srcRange.Cells.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndex]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
break;
}
}
#endregion
prompt = string.Format(@"存在同名记录，将合并;{0}
继续(OK), 取消导入(Cancel)。", "\r\n");
if (DialogResult.Cancel.Equals(MessageBox.Show(prompt, "提示：", MessageBoxButtons.OKCancel, MessageBoxIcon.Information)))
{
uEHelper.setAllColumnsWidth(3.75M);
uncertainRecordExcel.saveWithoutAutoFit();
uncertainRecordExcel.close();
closeThe4ARExcels();
return;
}
}
//关闭不确定文档。
uEHelper.setAllColumnsWidth(3.75M);
uncertainRecordExcel.saveWithoutAutoFit();
uncertainRecordExcel.close();
closeThe4ARExcels();
}
resultList.Sort();
//判断该考勤机中是否已经存在，某月的记录
foreach (string xlsFilePath in resultList)
{
string fileName = DirectoryHelper.getFileNameWithoutSuffix(xlsFilePath);
int attendanceMachineFlag = int.Parse(fileName.Substring(fileName.Length - 1, 1));
//打开文档获取考勤机，所记录的日期。
MyExcel myExcel = new MyExcel(xlsFilePath);
myExcel.open();
Worksheet firstSheet = myExcel.getFirstWorkSheetAfterOpen();
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(firstSheet);
string year_and_month_str = uEHelper.getCellContentByRowAndColIndex(3, 3);
year_and_month_str = year_and_month_str.Substring(0, 7);
myExcel.close();
System.Data.DataTable dtSubmitInfo = getSubmitInfoOfTheSpecificeMachineAndYearAndMonth(attendanceMachineFlag, year_and_month_str);
if (!"0".Equals(dtSubmitInfo.Rows[0]["nums_of_staffs"].ToString()))
{
string prompt = string.Format(@"您已经于: {1},{0}提交了{2} 考勤机{3},{0}共计{4}个用户的纪录{0} 覆盖(OK),退出(Cancel)?", "\r\n", dtSubmitInfo.Rows[0]["latest_record_time"].ToString(), year_and_month_str, attendanceMachineFlag, dtSubmitInfo.Rows[0]["nums_of_staffs"].ToString());
if (DialogResult.Cancel.Equals(MessageBox.Show(prompt, "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question)))
{
return;
}
delTheInfoOfTheSpecificeMachineAndYearAndMonth(attendanceMachineFlag, year_and_month_str);
}
}
this.dgv.DataSource = null;
//this.dgv.Columns.Clear();
lblPrompt.Visible = false;
lblPrompt.Text = "";
pb.Value = 0;
pb.Maximum = 0;
pb.Visible = false;
foreach (string xlsFilePath in resultList)
{
tb.Text = xlsFilePath;
lblResult.Visible = false;
MSG msg = AttendanceRHelper.ImportAttendanceRecordToDB(xlsFilePath, randomStr, lblPrompt, pb, lblResult);
//导入完成后进行保存，保存该文件至prepared目录中
pb.Visible = false;
lblPrompt.Visible = false;
ShowResult.show(lblResult, msg.Msg, msg.Flag);
timerRestoreTheLblResult.Enabled = true;
if (!msg.Flag)
{
return;
}
//saveTheExcel(xlsFilePath);
}
dgv_same_name.Visible = false;
dgv_same_pinyin_of_name.Visible = false;
//加载导入的数据。
this.dgv.DataSource = null;
this.dgv.DataSource = aR.getAR(randomStr);
this.dgv.Visible = true;
DGVHelper.AutoSizeForDGV(dgv);
tb.Clear();
}