/// <summary>
        /// 将考勤记录导入数据库.
        /// </summary>
        /// <param name="xlsFilePath"></param>
        /// <param name="randomStr"></param>
        /// <param name="pb"></param>
        /// <returns></returns>
        public static MSG  ImportARToDB(string xlsFilePath)
        {
            int          pbLength = 0;
            MSG          msg      = new MSG();
            ProgressInfo pI       = new ProgressInfo("ConsoleCollectAR");

            if (pI.ifExists())
            {
                pI.delete();
            }
            pI.add();
            //用于确定本月最后一天.
            Stack <int>         sDate        = new Stack <int>();
            Queue <AttendanceR> qAttendanceR = new Queue <AttendanceR>();
            //按指纹日期
            string fingerPrintDate = String.Empty;
            //导入数据的行数.
            int affectedCount = 0;
            //行最大值.
            int     rowsMaxCount = 0;
            int     colsMaxCount = 0;
            MyExcel myExcel      = new MyExcel(xlsFilePath);

            //打开该文档。
            myExcel.open();
            #region 获取该工作簿中可视的工作表列表
            List <Worksheet>     listVisualWS       = myExcel.getVisualWS();
            List <AR_Sheet_Info> list_AR_Sheet_Info = new List <AR_Sheet_Info>();
            Usual_Excel_Helper   uEHelper           = null;
            foreach (Worksheet wS in listVisualWS)
            {
                int sheet_index = wS.Index;
                int max_rows    = wS.UsedRange.Rows.Count;
                uEHelper = new Usual_Excel_Helper(wS);
                //获取A1内容
                string contentOfA3 = uEHelper.getSpecificCellValue("A3");
                //考勤时间为 空,忽略
                if (!"考勤时间".Equals(contentOfA3))
                {
                    continue;
                }
                int maxColumns = wS.UsedRange.Columns.Count;
                if (maxColumns <= 27 || maxColumns >= 32)
                {
                    continue;
                }
                //列的宽度28-31.
                AR_Sheet_Info ar_sheet_info = new AR_Sheet_Info(sheet_index, max_rows);
                list_AR_Sheet_Info.Add(ar_sheet_info);
            }
            #endregion
            //获取list_AR_Sheet_Info 中 rows 最少的那个Sheet
            //先对list_AR_Sheet_Info进行排序。
            list_AR_Sheet_Info.Sort((a, b) => a.Max_rows.CompareTo(b.Max_rows));
            int       sheetIndex = list_AR_Sheet_Info[0].SheetIndex;
            Worksheet ws         = listVisualWS[sheetIndex - 1];
            //行;列最大值 赋值.
            rowsMaxCount           = ws.UsedRange.Rows.Count;
            colsMaxCount           = ws.UsedRange.Columns.Count;
            AttendanceR.Sheet_name = ws.Name;
            //判断首行是否为 考勤记录表;以此判断此表是否为考勤记录表.
            string A1Str = ((Range)ws.Cells[1, 1]).Text.ToString().Trim().Replace("\n", "").Replace("\r", "").Replace(" ", "");
            if (String.IsNullOrEmpty(A1Str))
            {
                msg.Msg  = "工作表的A1单元格不能为空!";
                msg.Flag = false;
                myExcel.close();
                return(msg);
            }
            //如果A1Str的内容不包含"考勤记录表"5个字。
            if (!A1Str.Contains("考勤记录表"))
            {
                msg.Msg  = "A1内容未包含'考勤记录表'";
                msg.Flag = false;
                myExcel.close();
                return(msg);
            }
            #region 判断名称中是否区分了考勤记录。
            string Seq_Attendance_Record = string.Empty;
            int    indexOfFullStop       = xlsFilePath.LastIndexOf(".");
            Seq_Attendance_Record = xlsFilePath.Substring(indexOfFullStop - 1, 1);
            if (!CheckPattern.CheckNumber(Seq_Attendance_Record))
            {
                msg.Msg  = "考勤记录表名称请以数字结尾!";
                msg.Flag = false;
                myExcel.close();
                return(msg);
            }
            #endregion
            string excelName = Usual_Excel_Helper.getExcelName(xlsFilePath);
            AttendanceR.Prefix_Job_Number = excelName.Substring(excelName.Length - 1, 1).ToCharArray()[0];
            string C3Str = ((Range)ws.Cells[3, 3]).Text.ToString().Trim();
            //  \0: 表空字符.
            if (String.IsNullOrEmpty(C3Str))
            {
                msg.Msg  = "异常: 考勤时间为空!";
                msg.Flag = false;
                myExcel.close();
                return(msg);
            }
            //
            string[] ArrayC3 = C3Str.Split('~');
            if (ArrayC3.Length == 0)
            {
                msg.Msg  = "异常: 考勤时间格式变更!";
                msg.Flag = false;
                myExcel.close();
                return(msg);
            }
            AttendanceR.Start_date = ArrayC3[0].ToString().Trim();
            AttendanceR.End_date   = ArrayC3[1].ToString().Trim();
            //制表时间:  L3 3行12列.
            string L3Str = ((Range)ws.Cells[3, 12]).Text.ToString().Trim();
            if (String.IsNullOrEmpty(L3Str))
            {
                msg.Msg  = "异常: 制表时间为空!";
                msg.Flag = false;
                myExcel.close();
                return(msg);
            }
            //制表时间.
            AttendanceR.Tabulation_time = L3Str;
            //检查第4行是否为;考勤时间:
            string A4Str = ((Range)ws.Cells[4, 1]).Text.ToString().Trim();
            if (!"1".Equals(A4Str, StringComparison.CurrentCultureIgnoreCase))
            {
                msg.Msg  = "异常: 第四行已变更!";
                msg.Flag = false;
                myExcel.close();
                return(msg);
            }
            uEHelper = new Usual_Excel_Helper(ws);
            //此刻不能删除,只是获取行号。
            Queue <Range> rangeToDelQueue = new Queue <Range>();
            //判断是否有空行。
            for (int i = 5; i <= rowsMaxCount; i++)
            {
                if (uEHelper.isBlankRow(i))
                {
                    //只要上一列不是
                    //删除掉此行。
                    //判断上一行中的A列是否为工号。
                    string temp = uEHelper.getSpecificCellValue("A" + (i - 1).ToString());
                    if ("工号:".Equals(temp))
                    {
                        continue;
                    }
                    //获取该行。
                    Range rangeToDel = (Microsoft.Office.Interop.Excel.Range)uEHelper.WS.Rows[i, System.Type.Missing];
                    //不为工号
                    rangeToDelQueue.Enqueue(rangeToDel);
                }
                ;
            }
            Range rangeToDelete;
            //开始删除空行。
            while (rangeToDelQueue.Count > 0)
            {
                rangeToDelete = rangeToDelQueue.Dequeue();
                rangeToDelete.Delete(XlDeleteShiftDirection.xlShiftUp);
            }
            ;
            rowsMaxCount = ws.UsedRange.Rows.Count;
            //进度条长度增加。
            pbLength        += colsMaxCount;
            pbLength        += (colsMaxCount * (rowsMaxCount - 5 + 1));
            pI.Max_value     = pbLength;
            pI.Current_value = 0;
            pI.Msg           = excelName + ",正在读取:";
            pI.update();
            Console.WriteLine(excelName + ",正在读取:");
            //入队列值0
            sDate.Push(0);
            int actualMaxDay = 0;
            //开始循环
            for (int i = 1; i <= colsMaxCount; i++)
            {
                A4Str = ((Range)ws.Cells[4, i]).Text.ToString();
                //碰到第4行某列为空,退出循环。
                if (String.IsNullOrEmpty(A4Str))
                {
                    break;
                }
                int aDate = 0;
                //对A4Str进行分析.
                if (!Int32.TryParse(A4Str, out aDate))
                {
                    msg.Msg  = String.Format(@"异常: 考勤日期行第{0}列出现非数字内容!", aDate);
                    msg.Flag = false;
                    myExcel.close();
                    return(msg);
                }
                pI.Current_value++;
                pI.update();
                //判断新增的日期是否大于上一个.
                if (aDate <= sDate.Peek())
                {
                    //跳出循环.
                    break;
                }
                actualMaxDay++;
                sDate.Push(aDate);
            }
            //取其中的最小值。
            colsMaxCount = Math.Min(sDate.Count - 1, actualMaxDay);
            //考勤日期
            fingerPrintDate = AttendanceR.Start_date.Substring(0, 7).Replace('/', '-');
            string tempStr = string.Empty;
            //开始循环
            for (int colIndex = 1; colIndex <= colsMaxCount; colIndex++)
            {
                //从第5行开始.
                //奇数;偶数行共用一个对象.
                AttendanceR AR = null;
                for (int rowIndex = 5; rowIndex <= rowsMaxCount; rowIndex++)
                {
                    //如果行数为奇数则为工号行.
                    if (rowIndex % 2 == 1)
                    {
                        //工号行.
                        //取工号
                        AR            = new AttendanceR();
                        AR.Job_number = ((Range)ws.Cells[rowIndex, 3]).Text.ToString().Trim();
                        //自行拼凑AR.
                        AR.combine_Job_Number();
                        //取姓名:  K5
                        AR.Name = ((Range)ws.Cells[rowIndex, Usual_Excel_Helper.getColIndexByStr("K")]).Text.ToString().Trim();
                        //取部门: U5
                        AR.Dept = ((Range)ws.Cells[rowIndex, Usual_Excel_Helper.getColIndexByStr("U")]).Text.ToString().Trim();
                        //部门为空,则填充为NULL;
                        AR.Dept = !String.IsNullOrEmpty(AR.Dept) ? AR.Dept : "NULL";
                        //取日期.填充0;
                        AR.Fingerprint_Date = fingerPrintDate + "-" + colIndex.ToString().PadLeft(2, '0');
                    }
                    else
                    {
                        //偶数行取考勤结果.
                        //上班时间. 如B10;
                        tempStr = ((Range)ws.Cells[rowIndex, colIndex]).Text.ToString().Trim();
                        string tempFirstTime = String.Empty;
                        string tempLastTime  = String.Empty;
                        if (!getFPTime(tempStr, out tempFirstTime, out tempLastTime))
                        {
                            msg.Msg  = string.Format(@"导入失败:表中第{0}行{1}列的按指纹时间格式不对!", rowIndex, colIndex);
                            msg.Flag = false;
                            return(msg);
                        }
                        ;
                        AR.FPT_Fisrt_Time = String.IsNullOrEmpty(tempFirstTime) ? String.Empty : AR.Fingerprint_Date + " " + tempFirstTime;
                        AR.FPT_Last_Time  = String.IsNullOrEmpty(tempLastTime) ? String.Empty : AR.Fingerprint_Date + " " + tempLastTime;
                        qAttendanceR.Enqueue(AR);
                    }
                    pI.Current_value++;
                    pI.update();
                }
            }
            Console.WriteLine("提交数据: ");
            pI.Max_value     = qAttendanceR.Count;
            pI.Current_value = 0;
            pI.Msg           = "提交数据: ";
            pI.update();
            System.Threading.Thread.Sleep(2000);
            #region
            OracleDaoHelper.noLogging("Attendance_Record");
            //保存对象
            while (qAttendanceR.Count > 0)
            {
                try
                {
                    affectedCount += qAttendanceR.Dequeue().saveDataToDB();
                    pI.Current_value++;
                    pI.update();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString(), "提示:", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    msg.Msg  = ex.ToString();
                    msg.Flag = false;
                    OracleConnHelper.closeConn();
                    return(msg);
                }
            }
            #endregion
            OracleDaoHelper.logging("Attendance_Record");
            //释放对象
            myExcel.close();
            Console.WriteLine(String.Format(@"导入完成;计{0}条.", affectedCount.ToString()));
            //隐藏进度条。
            pI.Msg         = String.Format(@"导入完成;计{0}条.", affectedCount.ToString());
            pI.Finish_flag = true;
            pI.update();
            return(msg);
        }
예제 #2
0
        /// <summary>
        /// 将考勤记录导入数据库.
        /// </summary>
        /// <param name="xlsFilePath"></param>
        /// <param name="randomStr"></param>
        /// <param name="pb"></param>
        /// <returns></returns>
        public static MSG  ImportAttendanceRecordToDB(string xlsFilePath, string randomStr, BackgroundWorker bgWork)
        {
            string excelName = Usual_Excel_Helper.getExcelName(xlsFilePath);

            bgWork.ReportProgress(0, string.Format(@"lblPrompt.Text = {0},准备读取:", excelName));
            int pbLength = 0;

            bgWork.ReportProgress(pbLength, "pb.Maximum");
            int pbValue = 0;

            bgWork.ReportProgress(pbValue, "pb.Value");
            MSG msg = new MSG();

            //用于确定本月最后一天.
            Stack <int> sDate = new Stack <int>();
            //Queue<AttendanceR> qAttendanceR = new Queue<AttendanceR>();
            Queue <AttendanceRecordDetail> qARDetail = new Queue <AttendanceRecordDetail>();

            AttendanceRecordDetail._random_str = randomStr;
            //按指纹日期
            string fingerPrintDate = String.Empty;

            //行最大值.
            int rowsMaxCount            = 0;
            int colsMaxCount            = 0;
            Usual_Excel_Helper uEHelper = null;

            MyExcel myExcel = new MyExcel(xlsFilePath);

            //打开该文档。
            myExcel.openWithoutAlerts();
            //只获取第一个表格。
            Worksheet ws = myExcel.getFirstWorkSheetAfterOpen();

            bgWork.ReportProgress(0, string.Format(@"lblPrompt.Text = {0},正在读取:", excelName));
            AttendanceRecordDetail._file_path = xlsFilePath;
            //行;列最大值 赋值.
            rowsMaxCount = ws.UsedRange.Rows.Count;
            colsMaxCount = ws.UsedRange.Columns.Count;


            AttendanceRecordDetail._sheet_name = ws.Name;
            //判断首行是否为 考勤记录表;以此判断此表是否为考勤记录表.
            string A1Str = ((Range)ws.Cells[1, 1]).Text.ToString().Trim().Replace("\n", "").Replace("\r", "").Replace(" ", "");

            if (String.IsNullOrEmpty(A1Str))
            {
                msg.Msg  = "工作表的A1单元格不能为空!";
                msg.Flag = false;
                myExcel.close();
                return(msg);
            }
            //如果A1Str的内容不包含"考勤记录表"5个字。
            if (!A1Str.Contains("考勤记录表"))
            {
                msg.Msg  = "A1内容未包含'考勤记录表'";
                msg.Flag = false;
                myExcel.close();
                return(msg);
            }
            #region 判断名称中是否区分了考勤记录。
            string Seq_Attendance_Record = string.Empty;
            int    indexOfFullStop       = xlsFilePath.LastIndexOf(".");
            Seq_Attendance_Record = xlsFilePath.Substring(indexOfFullStop - 1, 1);
            if (!CheckPattern.CheckNumber(Seq_Attendance_Record))
            {
                msg.Msg  = "考勤记录表名称请以数字结尾!";
                msg.Flag = false;
                myExcel.close();
                return(msg);
            }
            #endregion

            AttendanceRecordDetail._prefix_Job_Number = excelName.Substring(excelName.Length - 1, 1).ToCharArray()[0];
            string C3Str = ((Range)ws.Cells[3, 3]).Text.ToString().Trim();
            //  \0: 表空字符.
            if (String.IsNullOrEmpty(C3Str))
            {
                msg.Msg  = "异常: 考勤时间为空!";
                msg.Flag = false;
                myExcel.close();
                return(msg);
            }
            //
            string[] ArrayC3 = C3Str.Split('~');
            if (ArrayC3.Length == 0)
            {
                msg.Msg  = "异常: 考勤时间格式变更!";
                msg.Flag = false;
                myExcel.close();
                return(msg);
            }
            AttendanceRecordDetail._start_date = ArrayC3[0].ToString().Trim().Replace('/', '-');
            AttendanceRecordDetail._end_date   = ArrayC3[1].ToString().Trim().Replace('/', '-');
            //制表时间:  L3 3行12列.
            string L3Str = ((Range)ws.Cells[3, 12]).Text.ToString().Trim().Replace('/', '-');
            if (String.IsNullOrEmpty(L3Str))
            {
                msg.Msg  = "异常: 制表时间为空!";
                msg.Flag = false;
                myExcel.close();
                return(msg);
            }
            //制表时间.
            AttendanceRecordDetail._tabulation_time = L3Str;
            //检查第4行是否为;考勤时间:
            string A4Str = ((Range)ws.Cells[4, 1]).Text.ToString().Trim();
            if (!"1".Equals(A4Str, StringComparison.CurrentCultureIgnoreCase))
            {
                msg.Msg  = "异常: 第四行已变更!";
                msg.Flag = false;
                myExcel.close();
                return(msg);
            }
            uEHelper = new Usual_Excel_Helper(ws);
            //此刻不能删除,只是获取行号。
            Queue <Range> rangeToDelQueue = new Queue <Range>();
            //判断是否有空行。
            for (int i = 5; i <= rowsMaxCount; i++)
            {
                if (uEHelper.isBlankRow(i))
                {
                    //只要上一列不是
                    //删除掉此行。
                    //判断上一行中的A列是否为工号。
                    string temp = uEHelper.getSpecificCellValue("A" + (i - 1).ToString());
                    if ("工号:".Equals(temp))
                    {
                        //本行为空,上一行为工号行,则也统计。
                        continue;
                    }
                    //本行,为空,上一行非工号行。则删除本行。
                    Range rangeToDel = (Microsoft.Office.Interop.Excel.Range)uEHelper.WS.Rows[i, System.Type.Missing];
                    //不为工号
                    rangeToDelQueue.Enqueue(rangeToDel);
                }
                ;
            }
            Range rangeToDelete;
            //开始删除空行。
            while (rangeToDelQueue.Count > 0)
            {
                rangeToDelete = rangeToDelQueue.Dequeue();
                rangeToDelete.Delete(XlDeleteShiftDirection.xlShiftUp);
            }
            ;
            rowsMaxCount = ws.UsedRange.Rows.Count;
            //进度条长度增加。
            pbLength += colsMaxCount;
            pbLength += (colsMaxCount * (rowsMaxCount - 5 + 1));
            bgWork.ReportProgress(pbLength, "pb.Maximum");
            //入队列值0
            sDate.Push(0);
            //显示进度条。
            //考勤表中第4行,某月的最大考勤天数。
            //lblPrompt.Text = excelName + ",正在读取:";

            int actualMaxDay = 0;
            //开始循环
            for (int i = 1; i <= colsMaxCount; i++)
            {
                A4Str = ((Range)ws.Cells[4, i]).Text.ToString();
                //碰到第4行某列为空,退出循环。
                if (String.IsNullOrEmpty(A4Str))
                {
                    break;
                }
                int aDate = 0;
                //对A4Str进行分析.
                if (!Int32.TryParse(A4Str, out aDate))
                {
                    msg.Msg  = String.Format(@"异常: 考勤日期行第{0}列出现非数字内容!", aDate);
                    msg.Flag = false;
                    myExcel.close();
                    return(msg);
                }
                //判断新增的日期是否大于上一个.
                if (aDate <= sDate.Peek())
                {
                    //跳出循环.
                    break;
                }
                actualMaxDay++;
                sDate.Push(aDate);
                //pb.Value++;
                bgWork.ReportProgress(pbValue++, "pb.Value");
            }
            //取其中的最小值。
            colsMaxCount = Math.Min(sDate.Count - 1, actualMaxDay);
            //考勤日期
            fingerPrintDate = AttendanceRecordDetail._start_date.Substring(0, 7).Replace('/', '-');
            string tempStr = string.Empty;
            //开始循环
            for (int colIndex = 1; colIndex <= colsMaxCount; colIndex++)
            {
                //从第5行开始.
                //奇数;偶数行共用一个对象.
                AttendanceRecordDetail ARDetail = null;
                //设定用于填充的对象
                AttendanceRecordDetail._prefix_Job_Number = Seq_Attendance_Record.ToCharArray()[0];
                for (int rowIndex = 5; rowIndex <= rowsMaxCount; rowIndex++)
                {
                    //如果行数为奇数则为工号行.
                    if (rowIndex % 2 == 1)
                    {
                        //工号行.
                        //取工号
                        ARDetail            = new AttendanceRecordDetail();
                        ARDetail.Job_number = ((Range)ws.Cells[rowIndex, 3]).Text.ToString().Trim();
                        //自行拼凑AR.
                        ARDetail.combine_Job_Number();
                        //取姓名:  K5
                        ARDetail.Name = ((Range)ws.Cells[rowIndex, Usual_Excel_Helper.getColIndexByStr("K")]).Text.ToString().Trim();
                        //取部门: U5
                        ARDetail.Dept = ((Range)ws.Cells[rowIndex, Usual_Excel_Helper.getColIndexByStr("U")]).Text.ToString().Trim();
                        //部门为空,则填充为NULL;
                        ARDetail.Dept = !String.IsNullOrEmpty(ARDetail.Dept) ? ARDetail.Dept : "NULL";
                        //取日期.填充0;
                        ARDetail.Fingerprint_date = fingerPrintDate + "-" + colIndex.ToString().PadLeft(2, '0');
                    }
                    else
                    {
                        //偶数行取考勤结果.
                        //上班时间. 如B10;
                        tempStr = ((Range)ws.Cells[rowIndex, colIndex]).Text.ToString().Trim();
                        string        tempFirstTime = String.Empty;
                        string        tempLastTime  = String.Empty;
                        List <string> strTimeList   = null;
                        msg = getFPTimeReturnMSG(tempStr, out strTimeList);
                        if (!msg.Flag)
                        {
                            msg.Msg = string.Format(@"导入失败,提交数据尚未开始:第{0}行{1}列,{1}!", rowIndex, colIndex, msg.Msg);
                            myExcel.close();
                            return(msg);
                        }
                        ;
                        //无打卡记录,不提交
                        if (strTimeList.Count == 0)
                        {
                            qARDetail.Enqueue(ARDetail);
                        }
                        //有打卡记录
                        for (int i = 0; i < strTimeList.Count; i++)
                        {
                            AttendanceRecordDetail ARDetailTemp = (AttendanceRecordDetail)CloneObject.Clone(ARDetail);
                            ARDetailTemp.Finger_print_time = ARDetailTemp.Fingerprint_date + " " + strTimeList[i].ToString();
                            qARDetail.Enqueue(ARDetailTemp);
                        }
                    }
                    //pb.Value++;
                    bgWork.ReportProgress(pbValue++, "pb.Value");
                }
            }
            //释放对象
            myExcel.close();
            System.Threading.Thread.Sleep(2000);
            GC.Collect();
            GC.WaitForPendingFinalizers();
            //lblResult.Text = "";
            bgWork.ReportProgress(0, "lblResult.Text = ''");
            //lblPrompt.Text = "提交数据: ";
            bgWork.ReportProgress(0, string.Format(@"lblPrompt.Text = {0}, 提交数据:", excelName));
            //
            bgWork.ReportProgress(qARDetail.Count, "pb.Maximum");
            //*******/
            pbValue = 0;
            bgWork.ReportProgress(pbValue, "pb.Value");
            #region
            //OracleDaoHelper.noLogging("Attendance_Record");
            OracleDaoHelper.noLogging("Attendance_Record_Detail");
            OracleConnection  conn = OracleConnHelper.getConn();
            OracleTransaction tran = conn.BeginTransaction();
            //保存对象
            while (qARDetail.Count > 0)
            {
                try
                {
                    AttendanceRecordDetail aRDetail = qARDetail.Dequeue();

                    affectedCount += aRDetail.saveBySpecificConn(conn);
                    //pb.Value++;
                    bgWork.ReportProgress(pbValue++, "pb.Value");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString(), "提示:", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    msg.Msg  = DirectoryHelper.getFileName(xlsFilePath) + ":导入失败; " + ex.ToString();
                    msg.Flag = false;
                    tran.Rollback();
                    conn.Close();
                    conn.Dispose();
                    return(msg);

                    throw;
                }
            }
            tran.Commit();
            conn.Close();
            conn.Dispose();
            #endregion
            //OracleDaoHelper.logging("Attendance_Record");
            OracleDaoHelper.logging("Attendance_Record_Detail");
            msg.Flag = true;
            msg.Msg  = String.Format(@"导入完成;总计{0}条.", affectedCount.ToString());
            return(msg);
        }
        /// <summary>
        /// 将考勤记录导入预备表中.
        /// </summary>
        /// <param name="xlsFilePath"></param>
        /// <param name="randomStr"></param>
        /// <param name="pb"></param>
        /// <returns></returns>
        public static MSG Import_Record_To_Preparative_Table(string xlsFilePath, string randomStr, System.Windows.Forms.Label lblPrompt, ProgressBar pb, System.Windows.Forms.Label lblResult)
        {
            int pbLength = 0;
            MSG msg      = new MSG();

            //用于确定本月最后一天.
            Stack <int>         sDate        = new Stack <int>();
            Queue <AttendanceR> qAttendanceR = new Queue <AttendanceR>();

            AttendanceR.Random_Str = randomStr;
            //按指纹日期
            string fingerPrintDate = String.Empty;
            //导入数据的行数.
            int affectedCount = 0;
            //行最大值.
            int rowsMaxCount            = 0;
            int colsMaxCount            = 0;
            Usual_Excel_Helper uEHelper = null;
            MyExcel            myExcel  = new MyExcel(xlsFilePath);

            //打开该文档。
            myExcel.open();

            //只获取第一个表格。
            Worksheet ws = myExcel.getFirstWorkSheetAfterOpen();

            AttendanceR.File_path = xlsFilePath;
            //行;列最大值 赋值.
            rowsMaxCount           = ws.UsedRange.Rows.Count;
            colsMaxCount           = ws.UsedRange.Columns.Count;
            pb.Value               = 0;
            pb.Visible             = true;
            lblPrompt.Visible      = true;
            AttendanceR.Sheet_name = ws.Name;
            //判断首行是否为 考勤记录表;以此判断此表是否为考勤记录表.
            string A1Str = ((Range)ws.Cells[1, 1]).Text.ToString().Trim().Replace("\n", "").Replace("\r", "").Replace(" ", "");

            if (String.IsNullOrEmpty(A1Str))
            {
                msg.Msg  = "工作表的A1单元格不能为空!";
                msg.Flag = false;
                myExcel.close();
                return(msg);
            }
            //如果A1Str的内容不包含"考勤记录表"5个字。
            if (!A1Str.Contains("考勤记录表"))
            {
                msg.Msg  = "A1内容未包含'考勤记录表'";
                msg.Flag = false;
                myExcel.close();
                return(msg);
            }
            #region 判断名称中是否区分了考勤记录。
            string Seq_Attendance_Record = string.Empty;
            int    indexOfFullStop       = xlsFilePath.LastIndexOf(".");
            Seq_Attendance_Record = xlsFilePath.Substring(indexOfFullStop - 1, 1);
            if (!CheckPattern.CheckNumber(Seq_Attendance_Record))
            {
                msg.Msg  = "考勤记录表名称请以数字结尾!";
                msg.Flag = false;
                myExcel.close();
                return(msg);
            }
            #endregion
            string excelName = Usual_Excel_Helper.getExcelName(xlsFilePath);
            AttendanceR.Prefix_Job_Number = excelName.Substring(excelName.Length - 1, 1).ToCharArray()[0];
            string C3Str = ((Range)ws.Cells[3, 3]).Text.ToString().Trim();
            //  \0: 表空字符.
            if (String.IsNullOrEmpty(C3Str))
            {
                msg.Msg  = "异常: 考勤时间为空!";
                msg.Flag = false;
                myExcel.close();
                return(msg);
            }
            //
            string[] ArrayC3 = C3Str.Split('~');
            if (ArrayC3.Length == 0)
            {
                msg.Msg  = "异常: 考勤时间格式变更!";
                msg.Flag = false;
                myExcel.close();
                return(msg);
            }
            AttendanceR.Start_date = ArrayC3[0].ToString().Trim();
            AttendanceR.End_date   = ArrayC3[1].ToString().Trim();
            //制表时间:  L3 3行12列.
            string L3Str = ((Range)ws.Cells[3, 12]).Text.ToString().Trim();
            if (String.IsNullOrEmpty(L3Str))
            {
                msg.Msg  = "异常: 制表时间为空!";
                msg.Flag = false;
                myExcel.close();
                return(msg);
            }
            //制表时间.
            AttendanceR.Tabulation_time = L3Str;
            //检查第4行是否为;考勤时间:
            string A4Str = ((Range)ws.Cells[4, 1]).Text.ToString().Trim();
            if (!"1".Equals(A4Str, StringComparison.CurrentCultureIgnoreCase))
            {
                msg.Msg  = "异常: 第四行已变更!";
                msg.Flag = false;
                myExcel.close();
                return(msg);
            }
            uEHelper = new Usual_Excel_Helper(ws);
            //此刻不能删除,只是获取行号。
            Queue <Range> rangeToDelQueue = new Queue <Range>();
            //判断是否有空行。
            for (int i = 5; i <= rowsMaxCount; i++)
            {
                if (uEHelper.isBlankRow(i))
                {
                    //只要上一列不是
                    //删除掉此行。
                    //判断上一行中的A列是否为工号。
                    string temp = uEHelper.getSpecificCellValue("A" + (i - 1).ToString());
                    if ("工号:".Equals(temp))
                    {
                        //本行为空,上一行为工号行,则也统计。
                        continue;
                    }
                    //本行,为空,上一行非工号行。则删除本行。
                    Range rangeToDel = (Microsoft.Office.Interop.Excel.Range)uEHelper.WS.Rows[i, System.Type.Missing];
                    //不为工号
                    rangeToDelQueue.Enqueue(rangeToDel);
                }
                ;
            }
            Range rangeToDelete;
            //开始删除空行。
            while (rangeToDelQueue.Count > 0)
            {
                rangeToDelete = rangeToDelQueue.Dequeue();
                rangeToDelete.Delete(XlDeleteShiftDirection.xlShiftUp);
            }
            ;
            rowsMaxCount = ws.UsedRange.Rows.Count;
            //进度条长度增加。
            pbLength  += colsMaxCount;
            pbLength  += (colsMaxCount * (rowsMaxCount - 5 + 1));
            pb.Maximum = pbLength;
            //入队列值0
            sDate.Push(0);
            //显示进度条。
            //考勤表中第4行,某月的最大考勤天数。
            lblPrompt.Text = excelName + ",正在读取:";
            int actualMaxDay = 0;
            //开始循环
            for (int i = 1; i <= colsMaxCount; i++)
            {
                A4Str = ((Range)ws.Cells[4, i]).Text.ToString();
                //碰到第4行某列为空,退出循环。
                if (String.IsNullOrEmpty(A4Str))
                {
                    break;
                }
                int aDate = 0;
                //对A4Str进行分析.
                if (!Int32.TryParse(A4Str, out aDate))
                {
                    msg.Msg  = String.Format(@"异常: 考勤日期行第{0}列出现非数字内容!", aDate);
                    msg.Flag = false;
                    myExcel.close();
                    return(msg);
                }
                pb.Value++;
                //判断新增的日期是否大于上一个.
                if (aDate <= sDate.Peek())
                {
                    //跳出循环.
                    break;
                }
                actualMaxDay++;
                sDate.Push(aDate);
            }
            //取其中的最小值。
            colsMaxCount = Math.Min(sDate.Count - 1, actualMaxDay);
            //考勤日期
            fingerPrintDate = AttendanceR.Start_date.Substring(0, 7).Replace('/', '-');
            string tempStr = string.Empty;
            //开始循环
            for (int colIndex = 1; colIndex <= colsMaxCount; colIndex++)
            {
                //从第5行开始.
                //奇数;偶数行共用一个对象.
                AttendanceR AR = null;
                for (int rowIndex = 5; rowIndex <= rowsMaxCount; rowIndex++)
                {
                    //如果行数为奇数则为工号行.
                    if (rowIndex % 2 == 1)
                    {
                        //工号行.
                        //取工号
                        AR            = new AttendanceR();
                        AR.Job_number = ((Range)ws.Cells[rowIndex, 3]).Text.ToString().Trim();
                        //自行拼凑AR.
                        AR.combine_Job_Number();
                        //取姓名:  K5
                        AR.Name = ((Range)ws.Cells[rowIndex, Usual_Excel_Helper.getColIndexByStr("K")]).Text.ToString().Trim();
                        //取部门: U5
                        AR.Dept = ((Range)ws.Cells[rowIndex, Usual_Excel_Helper.getColIndexByStr("U")]).Text.ToString().Trim();
                        //部门为空,则填充为NULL;
                        AR.Dept = !String.IsNullOrEmpty(AR.Dept) ? AR.Dept : "NULL";
                        //取日期.填充0;
                        AR.Fingerprint_Date = fingerPrintDate + "-" + colIndex.ToString().PadLeft(2, '0');
                    }
                    else
                    {
                        //偶数行取考勤结果.
                        //上班时间. 如B10;
                        tempStr = ((Range)ws.Cells[rowIndex, colIndex]).Text.ToString().Trim();
                        string tempFirstTime = String.Empty;
                        string tempLastTime  = String.Empty;
                        if (!getFPTime(tempStr, out tempFirstTime, out tempLastTime))
                        {
                            msg.Msg  = string.Format(@"导入失败:表中第{0}行{1}列的按指纹时间格式不对!", rowIndex, colIndex);
                            msg.Flag = false;
                            myExcel.close();
                            return(msg);
                        }
                        ;
                        AR.FPT_Fisrt_Time = String.IsNullOrEmpty(tempFirstTime) ? String.Empty : AR.Fingerprint_Date + " " + tempFirstTime;
                        AR.FPT_Last_Time  = String.IsNullOrEmpty(tempLastTime) ? String.Empty : AR.Fingerprint_Date + " " + tempLastTime;
                        qAttendanceR.Enqueue(AR);
                    }
                    pb.Value++;
                }
            }
            //释放对象
            myExcel.close();
            System.Threading.Thread.Sleep(2000);
            GC.Collect();
            GC.WaitForPendingFinalizers();
            lblResult.Text    = "";
            lblResult.Visible = false;
            lblPrompt.Visible = false;
            lblPrompt.Text    = "";
            lblPrompt.Text    = "提交数据: ";
            lblPrompt.Focus();
            lblPrompt.Visible = true;
            pb.Maximum        = qAttendanceR.Count;
            pb.Value          = 0;
            #region
            //保存对象
            while (qAttendanceR.Count > 0)
            {
                try
                {
                    AttendanceR aR = qAttendanceR.Dequeue();
                    affectedCount += aR.import_AR_To_Preparative_Table();
                    pb.Value++;
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString(), "提示:", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    msg.Msg  = DirectoryHelper.getFileName(xlsFilePath) + ":导入失败; " + ex.ToString();
                    msg.Flag = false;
                    return(msg);
                }
            }
            #endregion
            lblPrompt.Visible = false;
            //隐藏进度条。
            pb.Visible = false;
            msg.Flag   = true;
            msg.Msg    = String.Format(@"导入完成;计{0}条.", affectedCount.ToString());
            return(msg);
        }