/// <summary>
/// 检查第四行
/// </summary>
/// <returns></returns>
private bool check4thRow(List <String> excelPathList, out int maxColIndex)
{
maxColIndex = 0;
//先清除所有记录。
AR_Temp.deleteTheARTemp();
foreach (string excelPath in excelPathList)
{
//打开文档
MyExcel myExcel = new MyExcel(excelPath);
myExcel.open();
Worksheet firstWS = myExcel.getFirstWorkSheetAfterOpen();
string fileNameWithoutSuffix = DirectoryHelper.getFileNameWithoutSuffix(excelPath);
int checkedColIndex = 0;
if (!AttendanceRHelper.isAllDigit(firstWS, 4, out checkedColIndex))
{
myExcel.close();
lblPrompt.Visible = false;
ShowResult.show(lblResult, fileNameWithoutSuffix + ": 第4行" + checkedColIndex.ToString() + "列非数字; 导入取消。", false);
timerRestoreTheLblResult.Start();
return(false);
}
if (maxColIndex == 0)
{
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(firstWS);
maxColIndex = uEHelper.getMaxColIndexBeforeBlankCellInSepcificRow(4);
}
myExcel.close();
}
return(true);
}
private void saveCriticalARInfo(List <string> xlsFilePathList)
{
//先清除所有记录。
AR_Temp.deleteTheARTemp();
for (int i = 0; i <= xlsFilePathList.Count - 1; i++)
{
string excelPath = xlsFilePathList[i];
//打开文档
MyExcel myExcel = new MyExcel(excelPath);
myExcel.openWithoutAlerts();
Worksheet firstWS = myExcel.getFirstWorkSheetAfterOpen();
//删除 时间后立即为空的行。
AttendanceRHelper.clearSheet(firstWS);
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(firstWS);
string excelName = Usual_Excel_Helper.getExcelName(excelPath);
//先获取第4行的最大行列数目。
int rowMaxIndex = firstWS.UsedRange.Rows.Count;
int pbMaximum = rowMaxIndex - 4;
int pbValue = 0;
//0: 表示 lblResult.Visible
checkNameBGWorker.ReportProgress(0, "lblResult.Visible");
checkNameBGWorker.ReportProgress(pbMaximum, "pb.Maximum");
checkNameBGWorker.ReportProgress(pbValue, "pb.Value");
//lblPrompt.Text = excelName + ": 基本信息采集中...";
checkNameBGWorker.ReportProgress(0, string.Format(@"lblPrompt.Text={0}: 姓名采集中...", excelName));
for (int rowIndex = 5; rowIndex <= rowMaxIndex; rowIndex++)
{
//偶数行为 时间。
if (0 == rowIndex % 2)
{
checkNameBGWorker.ReportProgress(pbValue++, "pb.Value");
continue;
}
//姓名 存于第11列。
string name = uEHelper.getCellContentByRowAndColIndex(rowIndex, 11);
AR_Temp ar_Temp = new AR_Temp();
ar_Temp.Attendance_machine_flag = int.Parse(excelName.Substring(excelName.Length - 1, 1));
ar_Temp.Row_Index = rowIndex;
ar_Temp.Job_number = uEHelper.getCellContentByRowAndColIndex(rowIndex, 3);
ar_Temp.Name = name;
/// <summary>
/// 保存 ATTENDANCE_MACHINE_FLAG,
/// row_index,
/// job_number,
/// name
/// 至AR_Temp中
ar_Temp.saveRecord();
checkNameBGWorker.ReportProgress(pbValue++, "pb.Value");
}
myExcel.close();
}
}
/// <summary>
///
/// </summary>
/// <returns></returns>
private MSG checkExistSameStaffsButDifferentAttendanceMachine(List <String> excelPathList)
{
MSG msg = new MSG();
List <SimpleARInfo> simpleARInfoList = new List <SimpleARInfo>();
foreach (string excelPath in excelPathList)
{
//打开文档
MyExcel myExcel = new MyExcel(excelPath);
myExcel.open();
Worksheet firstWS = myExcel.getFirstWorkSheetAfterOpen();
//删除 时间为空 的行。
AttendanceRHelper.clearSheet(firstWS);
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(firstWS);
SimpleARInfo simpleARInfo = null;
string fileNameWithout = DirectoryHelper.getFileNameWithoutSuffix(excelPath);
int rowMaxIndex = firstWS.UsedRange.Rows.Count;
for (int rowIndex = 5; rowIndex <= rowMaxIndex; rowIndex++)
{
if (0 == rowIndex % 2)
{
continue;
}
//姓名 存于第11列。
string name = uEHelper.getCellContentByRowAndColIndex(rowIndex, 11);
simpleARInfo = new SimpleARInfo();
simpleARInfo.AttendanceMachineFlag = int.Parse(fileNameWithout.Substring(fileNameWithout.Length - 1, 1));
simpleARInfo.Name = name;
simpleARInfo.RowIndex = rowIndex;
SimpleARInfo sARInfo = simpleARInfoList.Find(x => x.Name == simpleARInfo.Name);
if (sARInfo != null)
{
//存在重复的员工.
msg.Msg = string.Format(@"{0} 与 {1} 同名;同一人请汇总为一行,不同人,请姓名相异。", sARInfo.toString(), simpleARInfo.toString());
myExcel.close();
return(msg);
}
simpleARInfoList.Add(simpleARInfo);
}
myExcel.save();
myExcel.close();
}
msg.Flag = true;
msg.Msg = "未发现同名用户";
return(msg);
}
private void saveCriticalARInfo(List <String> excelPathList)
{
//先清除所有记录。
AR_Temp.deleteTheARTemp();
foreach (string excelPath in excelPathList)
{
//打开文档
MyExcel myExcel = new MyExcel(excelPath);
myExcel.open();
Worksheet firstWS = myExcel.getFirstWorkSheetAfterOpen();
//删除 时间后立即为空的行。
AttendanceRHelper.clearSheet(firstWS);
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(firstWS);
string fileNameWithoutSuffix = DirectoryHelper.getFileNameWithoutSuffix(excelPath);
//先获取第4行的最大行列数目。
int rowMaxIndex = firstWS.UsedRange.Rows.Count;
pb.Value = 0;
pb.Maximum = rowMaxIndex - 4;
pb.Visible = true;
lblPrompt.Visible = true;
lblResult.Visible = false;
lblPrompt.Text = fileNameWithoutSuffix + ": 基本信息采集中...";
for (int rowIndex = 5; rowIndex <= rowMaxIndex; rowIndex++)
{
if (0 == rowIndex % 2)
{
pb.Value++;
continue;
}
//姓名 存于第11列。
string name = uEHelper.getCellContentByRowAndColIndex(rowIndex, 11);
AR_Temp ar_Temp = new AR_Temp();
ar_Temp.Attendance_machine_flag = int.Parse(fileNameWithoutSuffix.Substring(fileNameWithoutSuffix.Length - 1, 1));
ar_Temp.Row_Index = rowIndex;
ar_Temp.Job_number = uEHelper.getCellContentByRowAndColIndex(rowIndex, 3);
ar_Temp.Name = name;
ar_Temp.saveRecord();
pb.Value++;
}
lblPrompt.Visible = false;
pb.Visible = false;
myExcel.save();
myExcel.close();
}
}
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();
}
