예제 #1
0
        /// <summary>
        /// 这个方法,用来生成数据。
        /// </summary>
        /// <param name="wS"></param>
        /// <param name="dtToPrint"></param>
        public static void generate_data_to_print(Worksheet wS, System.Data.DataTable dtToPrint, ProgressBar pb)
        {
            Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(wS);
            Range theAllRange           = wS.UsedRange;

            pb.Visible = true;
            pb.Maximum = dtToPrint.Rows.Count * dtToPrint.Columns.Count;
            pb.Value   = 0;
            //1.模板修正操作.
            //依据列字段名来确定
            //2.赋值操作.
            for (int i = 0; i <= dtToPrint.Rows.Count - 1; i++)
            {
                //1.替换 Company
                //2.先找出uEHelper中有没有Companyi
                for (int j = 0; j <= dtToPrint.Columns.Count - 1; j++)
                {
                    string columnName = dtToPrint.Columns[j].ColumnName;
                    string searchStr  = "$" + columnName + (i + 1).ToString() + "$";
                    uEHelper.replace_Str_in_the_range(theAllRange, searchStr, dtToPrint.Rows[i][columnName].ToString());
                    pb.Value++;
                }
            }
            pb.Visible = false;
            pb.Maximum = 0;
            pb.Value   = 0;
        }
 /// <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);
 }
        /// <summary>
        /// 判断Products_Cost 原始数据中,具体工序是否为空!
        /// </summary>
        /// <param name="xlsPath"></param>
        /// <returns></returns>
        public bool check_P_C_Template(string xlsPath, out string msg)
        {
            MyExcel myExcel = new MyExcel(xlsPath);

            myExcel.open();
            Worksheet          wS       = myExcel.getFirstWorkSheetAfterOpen();
            Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(wS);
            int rowMaxIndex             = wS.UsedRange.Rows.Count;
            int colMaxIndex             = wS.UsedRange.Columns.Count;
            //具体 工序在第四列
            string specificProcess = string.Empty;

            for (int rowIndex = rowMaxIndex; rowIndex >= 1; rowIndex--)
            {
                specificProcess = uEHelper.getSpecificCellValue("D" + rowIndex);
                if (string.IsNullOrEmpty(specificProcess))
                {
                    myExcel.close();
                    msg = "第" + rowIndex + "行,具体工序不能为空!";
                    return(false);
                }
            }
            myExcel.close();
            msg = "";
            return(true);
        }
예제 #4
0
        /// <summary>
        /// 写模板
        /// </summary>
        /// <returns></returns>
        public MyExcel writeTemplate()
        {
            path = System.Windows.Forms.Application.StartupPath + "\\Template\\成衣报价.xls";
            V_New_Excel        v_New_Excel = new V_New_Excel(path);
            Worksheet          wS          = v_New_Excel.WS;
            Usual_Excel_Helper uEHelper    = new Usual_Excel_Helper(wS);

            uEHelper.setSpecificCellValue("A1", "请在此填写成品名称", 18);
            uEHelper.setSpecificCellValue("A2", "序号", 16);
            uEHelper.setSpecificCellValue("B2", "工序", 16);
            uEHelper.setSpecificCellValue("F2", "序号", 16);
            uEHelper.setSpecificCellValue("G2", "工序", 16);
            uEHelper.merge("A1", "I1");
            //设置列宽
            uEHelper.setColumnWidth("A", 5.5M);
            uEHelper.setColumnWidth("B", 32.75M);
            uEHelper.setColumnWidth("C", 4.15M);
            uEHelper.setColumnWidth("D", 5.38M);
            uEHelper.setColumnWidth("E", 0.15M);
            uEHelper.setColumnWidth("F", 5.5M);
            uEHelper.setColumnWidth("G", 32.75M);
            uEHelper.setColumnWidth("H", 4.15M);
            uEHelper.setColumnWidth("I", 5.38M);

            v_New_Excel.saveWithoutAutoFit();
            v_New_Excel.closeTheApp();
            return(new MyExcel(path));
        }
예제 #5
0
        /// <summary>
        /// 获取第四行中,为天数的最大列索引
        /// </summary>
        /// <param name="wS"></param>
        /// <returns></returns>
        public static int getMaxColIndexOfThe4thRowOfAR(Worksheet wS)
        {
            Stack <int> sDate = new Stack <int>();

            sDate.Push(0);
            int aDate       = 0;
            int maxColIndex = wS.UsedRange.Columns.Count;

            for (int colIndex = 1; colIndex <= maxColIndex; colIndex++)
            {
                Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(wS);
                string             tempStr  = uEHelper.getCellContentByRowAndColIndex(4, colIndex);
                if (string.IsNullOrEmpty(tempStr))
                {
                    return(colIndex - 1);
                }
                aDate = int.Parse(tempStr);
                //判断新增的日期是否大于上一个.
                if (aDate <= sDate.Peek())
                {
                    return(colIndex - 1);
                }
                sDate.Push(aDate);
                //取其中的最小值。
            }
            return(maxColIndex);
        }
예제 #6
0
        /// <summary>
        /// 这个方法,用来确定模板。
        /// </summary>
        /// <param name="wS"></param>
        /// <param name="dt"></param>
        public static void defineExcelTemplate(Worksheet wS, List <TemplateProperty> templatePropertyList, ProgressBar pb)
        {
            //以下依据DataTable 来  确定 模板内容
            Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(wS);

            pb.Visible = true;
            pb.Maximum = templatePropertyList.Count;
            pb.Value   = 0;
            //打开该文档.
            for (int i = 0; i <= templatePropertyList.Count - 1; i++)
            {
                string  property_name = templatePropertyList[i].Property_name;
                decimal visible       = templatePropertyList[i].Visible;
                bool    isVisible     = (1 == visible) ? true : false;
                if (isVisible)
                {
                    pb.Value++;
                    continue;
                }
                //获取该属性对应的行号.
                int[] rowIndexArray = uEHelper.getRowIndexArrayWhichContainContent(property_name);
                for (int j = 0; j <= rowIndexArray.Length - 1; j++)
                {
                    uEHelper.delTheSpecificRowShiftToUp(rowIndexArray[j] - j);
                }
                pb.Value++;
            }
            pb.Visible = false;
            pb.Maximum = 0;
            pb.Value   = 0;
        }
예제 #7
0
 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);
        }
예제 #9
0
        /// <summary>
        ///
        /// </summary>
        /// <returns></returns>
        public MyExcel write_Template_For_Record_P_C()
        {
            path = System.Windows.Forms.Application.StartupPath + "\\模板\\成衣报价.xls";
            string default_dir = System.Windows.Forms.Application.StartupPath + "\\模板";

            DirectoryHelper.createDirecotry(default_dir);
            System.Data.DataTable dt = OracleDaoHelper.getDTBySql("select * from v_p_c_template");
            ExcelHelper.saveDtToExcel(dt, path);
            MyExcel myExcel = new MyExcel(path);

            myExcel.open();
            Worksheet          wS       = myExcel.getFirstWorkSheetAfterOpen();
            Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(wS);

            uEHelper.insertAboveTheSpecificRow(1);
            uEHelper.setSpecificCellValue("A1", "请在此填写成品名称", 18);
            uEHelper.merge("A1", "E1");
            uEHelper.setSpecificCellValue("A3", "1", 12, true);
            myExcel.save();
            myExcel.close();
            return(myExcel);

            /*V_New_Excel v_New_Excel = new V_New_Excel(path);
             * Worksheet wS = v_New_Excel.WS;
             * Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(wS);
             * uEHelper.setSpecificCellValue("A1", "请在此填写成品名称", 18);
             * uEHelper.setSpecificCellValue("A2", "序号", 16);
             * uEHelper.setSpecificCellValue("B2", "工序", 16);
             * uEHelper.setSpecificCellValue("F2", "序号", 16);
             * uEHelper.setSpecificCellValue("G2", "工序", 16);
             * uEHelper.merge("A1", "I1");
             * //设置列宽
             * uEHelper.setColumnWidth("A", 5.5M);
             * uEHelper.setColumnWidth("B", 32.75M);
             * uEHelper.setColumnWidth("C", 4.15M);
             * uEHelper.setColumnWidth("D", 5.38M);
             * uEHelper.setColumnWidth("E", 0.15M);
             * uEHelper.setColumnWidth("F", 5.5M);
             * uEHelper.setColumnWidth("G", 32.75M);
             * uEHelper.setColumnWidth("H", 4.15M);
             * uEHelper.setColumnWidth("I", 5.38M);
             *
             * v_New_Excel.saveWithoutAutoFit();
             * v_New_Excel.closeTheApp();
             * return new MyExcel(path);
             */
        }
예제 #10
0
 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();
     }
 }
        /// <summary>
        /// 保存一份文件到Prepared中。
        /// </summary>
        /// <param name="xlsFilePath"></param>
        private void saveTheExcel(string xlsFilePath)
        {
            //获取该记录表所对应的月份。
            MyExcel srcExceFile = new MyExcel(xlsFilePath);

            srcExceFile.openWithoutAlerts();
            Worksheet firstSheet_Src         = srcExceFile.getFirstWorkSheetAfterOpen();
            string    year_and_month_src_str = new Usual_Excel_Helper(firstSheet_Src).getCellContentByRowAndColIndex(3, 3).Substring(0, 7);
            string    fileNameWithoutSuffix  = DirectoryHelper.getFileNameWithoutSuffix(xlsFilePath);
            string    attendanceMachineFlag  = fileNameWithoutSuffix.Substring(fileNameWithoutSuffix.Length - 1);

            year_and_month_src_str += string.Format(@"_{0}", attendanceMachineFlag);
            srcExceFile.close();
            //year_and_month_src_str  即为文件名

            //string fileName = DirectoryHelper.getFileName(xlsFilePath);
            //1.复制该excel 到 Prepared中。
            Tools.CmdHelper.copyFileToDestDir(xlsFilePath, System.Windows.Forms.Application.StartupPath + "\\prepared\\" + year_and_month_src_str + ".xls");
            string destFilePath = System.Windows.Forms.Application.StartupPath + "\\prepared\\" + year_and_month_src_str + ".xls";
            //打开该文件
            MyExcel myExcel = new MyExcel(destFilePath);

            myExcel.openWithoutAlerts();
            //新增一个sheet.
            Worksheet          firstSheet = myExcel.getFirstWorkSheetAfterOpen();
            Usual_Excel_Helper uHelper    = new Usual_Excel_Helper(firstSheet);
            //获取月份.
            string    C3ContentStr       = uHelper.getCellContentByRowAndColIndex(3, 3);
            string    year_and_month_str = C3ContentStr.Substring(0, 7);
            Worksheet theLastestExcel    = myExcel.AddSheetToLastIndex(year_and_month_str);

            myExcel.copyRangeFromOneToAnotherSheet(firstSheet, theLastestExcel);
            int sheetsCount = myExcel.getCountsOfAllSheet();

            //删掉之前的表格,保留最后一个。
            for (int i = 1; i <= sheetsCount - 1; i++)
            {
                myExcel.delTheSheet(1);
            }
            //一定要保存否则,无效。
            myExcel.saveAndColumnsAutoFit();
            //关闭该文件
            myExcel.close();
        }
예제 #12
0
        /// <summary>
        /// 判断某行是否都为数字。
        /// </summary>
        /// <param name="wS"></param>
        /// <param name="rowIndex"></param>
        /// <returns></returns>
        public static bool isAllDigit(Worksheet wS, int rowIndex, out int checkedColIndex)
        {
            Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(wS);
            int  maxColIndex            = wS.UsedRange.Columns.Count;
            bool flag = false;
            int  num  = 0;

            for (int colIndex = 1; colIndex <= maxColIndex; colIndex++)
            {
                string tempStr = uEHelper.getCellContentByRowAndColIndex(4, colIndex);
                flag = int.TryParse(tempStr, out num);
                if (!flag)
                {
                    checkedColIndex = colIndex;
                    return(false);
                }
            }
            checkedColIndex = maxColIndex;
            return(true);
        }
        /// <summary>
        /// 这个方法,用来确定模板。
        /// </summary>
        /// <param name="wS"></param>
        /// <param name="dt"></param>
        public static void defineExcelTemplate(Worksheet wS, List <TemplateProperty> templatePropertyList, ProgressBar pb)
        {
            //以下依据DataTable 来  确定 模板内容
            Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(wS);

            pb.Visible = true;
            pb.Maximum = templatePropertyList.Count;
            pb.Value   = 0;
            //打开该文档.
            //每删除一行。Excel 对应的行索引都要减少。
            int j = 0;

            for (int i = 0; i <= templatePropertyList.Count - 1; i++)
            {
                string  property_name         = templatePropertyList[i].Property_name;
                string  property_chinese_name = templatePropertyList[i].Property_chinese_name;
                decimal visible   = templatePropertyList[i].Visible;
                bool    isVisible = (1 == visible) ? true : false;
                if (isVisible)
                {
                    pb.Value++;
                    continue;
                }
                //若此属性不显示
                //获取该属性对应的行号.
                HashSet <int> hashSetRownIndex = uEHelper.getRowIndexArrayWhichContainContent(property_chinese_name);

                foreach (int rowIndex in hashSetRownIndex)
                {
                    //不显示的属性,进行删除。
                    uEHelper.delTheSpecificRowShiftToUp(rowIndex - j);
                    j++;
                }
                pb.Value++;
            }
            pb.Visible = false;
            pb.Maximum = 0;
            pb.Value   = 0;
        }
예제 #14
0
        /// <summary>
        ///
        /// </summary>
        public static void clearSheet(Worksheet firstWS)
        {
            Queue <Range> rangeToDelQueue = new Queue <Range>();
            int           rowsMaxCount;

            rowsMaxCount = firstWS.UsedRange.Rows.Count;
            Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(firstWS);
            //获取最大列
            int maxColIndex = getMaxColIndexOfThe4thRowOfAR(firstWS);

            //判断是否有空行。
            for (int i = 5; i <= rowsMaxCount; i++)
            {
                if (uEHelper.isBlankRangeTheSpecificRow(i, 1, maxColIndex))
                {
                    //只要上一列不是
                    //删除掉此行。
                    //判断上一行中的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);
            }
        }
        /// <summary>
        /// 导入
        /// </summary>
        public void import_P_C_Record_Template_previous()
        {
            string xlsFilePath = FileNameDialog.getSelectedFilePathWithDefaultDir("请选择成衣成本记录:", "*.xls,*.xlsx|*.xls;*.xlsx", defaultDir);

            if (string.IsNullOrEmpty(xlsFilePath))
            {
                return;
            }
            if (!File.Exists(xlsFilePath))
            {
                return;
            }
            tbPath.Text = xlsFilePath;
            MyExcel myExcel = new MyExcel(xlsFilePath);

            myExcel.open();
            Tools.AppManagement.add(myExcel.HwndOfApp);
            List <Worksheet> wSList       = myExcel.getVisualWS();
            int notProductsCostFile_Count = 0;

            for (int i = 0; i <= wSList.Count - 1; i++)
            {
                Worksheet wS  = wSList[i];
                MSG       msg = null;
                //非成本文件,则忽略。
                if (!(msg = isProductsCostRecord(wS)).Flag)
                {
                    notProductsCostFile_Count++;
                    continue;
                }
                Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(wS);
                string             PN       = uEHelper.getSpecificCellValue("A1");
                if ("请在此填写成品名称".Equals(PN))
                {
                    MessageBox.Show(wS.Index + "." + wS.Name + ":  请填写成品名称!", "提示:", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    continue;
                }
                if (string.IsNullOrEmpty(PN))
                {
                    MessageBox.Show(wS.Index + "." + wS.Name + ": 产品名称不能为空!", "提示:", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    continue;
                }
                //判断是否存在该成品的记录
                string sqlStr            = string.Format(@"SELECT 1 FROM Products_Cost WHERE Product_Name = '{0}'", PN);
                System.Data.DataTable dt = OracleDaoHelper.getDTBySql(sqlStr);
                if (dt.Rows.Count > 0)
                {
                    //删除文档
                    sqlStr = string.Format(@"DELETE FROM Products_Cost where Product_Name = '{0}'", PN);
                    OracleDaoHelper.executeSQL(sqlStr);
                }
                //先导入概要工序。
                int rowMaxIndex = uEHelper.getTheMaxRowIndexUntilBlankRow();
                //int rowMaxIndex = wS.UsedRange.Rows.Count;
                string seq_p_c_record   = string.Empty;
                string summary_process  = string.Empty;
                string specific_process = string.Empty;
                string man_hours        = string.Empty;
                string labour_cost      = string.Empty;
                pb.Maximum        = rowMaxIndex - 2;
                pb.Value          = 0;
                pb.Visible        = true;
                lblResult.Visible = false;
                OracleConnection theConnForTransaction = new OracleConnection(OracleDaoHelper.conn_str);
                theConnForTransaction.Open();
                OracleTransaction tran = theConnForTransaction.BeginTransaction();
                for (int rowIndex = 3; rowIndex <= rowMaxIndex; rowIndex++)
                {
                    seq_p_c_record = uEHelper.getSpecificCellValue("A" + rowIndex);
                    string prepared_Summary_process = uEHelper.getSpecificCellValue("B" + rowIndex).Trim();
                    specific_process = uEHelper.getSpecificCellValue("C" + rowIndex);
                    man_hours        = uEHelper.getSpecificCellValue("D" + rowIndex);
                    labour_cost      = uEHelper.getSpecificCellValue("E" + rowIndex);
                    if (string.IsNullOrEmpty(seq_p_c_record))
                    {
                        break;
                    }
                    if (string.IsNullOrEmpty(specific_process))
                    {
                        break;
                    }
                    ;
                    if (string.IsNullOrEmpty(man_hours))
                    {
                        break;
                    }
                    ;
                    if (string.IsNullOrEmpty(labour_cost))
                    {
                        break;
                    }
                    ;
                    //若主工序不为空
                    if (!"".Equals(prepared_Summary_process))
                    {
                        summary_process = prepared_Summary_process;
                    }
                    //插入该大类中的第一行数据。
                    sqlStr = string.Format(@"INSERT INTO Products_Cost(SEQ,SEQ_P_C_RECORD,Product_Name,Summary_Process,Specific_Process,Man_hours,Labour_cost,Supplier)
                                            VALUES(SEQ_Products_Cost.nextVal,'{0}','{1}','{2}','{3}','{4}','{5}','{6}')",
                                           seq_p_c_record,
                                           PN,
                                           summary_process,
                                           specific_process,
                                           man_hours,
                                           labour_cost,
                                           Program._userInfo.User_Name);
                    try
                    {
                        OracleDaoHelper.executeSQLThrowExceptioin(sqlStr, theConnForTransaction);
                    }
                    catch (Exception ex)
                    {
                        if (ex.ToString().Contains("ORA-00001"))
                        {
                            MessageBox.Show(wS.Index + "." + wS.Name + ": 第" + rowIndex + "行为重复工序,请检查成本表格,请重新导入 . ", "提示:", MessageBoxButtons.OK, MessageBoxIcon.Information);
                            tran.Rollback();
                            theConnForTransaction.Close();
                            lblPromptForPB.Visible = false;
                            pb.Visible             = false;
                            myExcel.close();
                            break;
                        }
                        MessageBox.Show(wS.Index + "." + wS.Name + ": 第" + rowIndex + "行,数据导入时发生异常,请检查成本表格,重新导入。" + ex.ToString(), "提示:", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        tran.Rollback();
                        theConnForTransaction.Close();
                        lblPromptForPB.Visible = false;
                        pb.Visible             = false;
                        myExcel.close();
                        break;
                    }
                    lblPromptForPB.Visible = true;
                    lblPromptForPB.Text    = wS.Index + "." + wS.Name + ": ";
                    pb.Value++;
                    continue;
                }
                tran.Commit();
                theConnForTransaction.Close();
                pb.Visible             = false;
                lblPromptForPB.Visible = false;
                lblPromptForPB.Text    = "";
                lblResult.Visible      = true;
                IShowProductsCostDetail showProductsCostDetailImpl = new ShowProductsCostDetailImpl();
                this.dgv.DataSource = showProductsCostDetailImpl.getProductsDetail(PN);
                DGVHelper.AutoSizeForDGV(dgv);
            }
            myExcel.close();
            if (notProductsCostFile_Count == wSList.Count)
            {
                ShowResult.show(lblResult, "   共" + wSList.Count + "个表格均非成衣成本文件!依据:第3行4列和第3行5列均非数字!", false);
                timerRestoreLblResult.Enabled = true;
                lblPromptForPB.Visible        = false;
            }
        }
예제 #16
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>
        private void importProductsCostRecord()
        {
            string xlsFilePath = FileNameDialog.getSelectedFilePathWithDefaultDir("请选择考勤记录:", "*.xls|*.xls", defaultDir);

            if (string.IsNullOrEmpty(xlsFilePath))
            {
                return;
            }
            if (!File.Exists(xlsFilePath))
            {
                return;
            }
            tbPath.Text = xlsFilePath;
            MyExcel myExcel = new MyExcel(xlsFilePath);

            myExcel.open();
            Tools.AppManagement.add(myExcel.HwndOfApp);
            Worksheet          wS       = myExcel.getFirstWorkSheetAfterOpen();
            Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(wS);
            string             PN       = uEHelper.getSpecificCellValue("A1");

            if ("请在此填写成品名称".Equals(PN))
            {
                MessageBox.Show("请填写成品名称!", "提示:", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            string sqlStr = string.Format(@"SELECT 1 FROM Products_Cost_Header WHERE Product_Name = '{0}'", PN);

            System.Data.DataTable dt = OracleDaoHelper.getDTBySql(sqlStr);
            if (dt.Rows.Count > 0)
            {
                sqlStr = string.Format(@"DELETE FROM Products_Cost_Header WHERE Product_Name = '{0}'", PN);
                OracleDaoHelper.executeSQL(sqlStr);
            }
            //判断是否存在该成品的记录
            sqlStr = string.Format(@"SELECT 1 FROM Products_Cost WHERE Product_Name = '{0}'", PN);
            dt     = OracleDaoHelper.getDTBySql(sqlStr);
            if (dt.Rows.Count > 0)
            {
                //删除文档
                sqlStr = string.Format(@"DELETE FROM Products_Cost where Product_Name = '{0}'", PN);
                OracleDaoHelper.executeSQL(sqlStr);
            }
            //先导入概要工序。
            int    rowStartIndex    = 3;
            int    rowMaxIndex      = wS.UsedRange.Rows.Count;
            string summary_process  = string.Empty;
            string specific_process = string.Empty;

            //summary_process不允许空开。
            //先检查第1至4列.
            pb.Visible        = true;
            lblResult.Visible = false;
            pb.Maximum        = 4 * (rowMaxIndex - rowStartIndex + 1);
            pb.Value          = 0;
            for (int rowIndex = rowStartIndex; rowIndex < rowMaxIndex; rowIndex++)
            {
                string seq_p_c_record = uEHelper.getSpecificCellValue("A" + rowIndex);
                string man_hour       = uEHelper.getSpecificCellValue("C" + rowIndex);
                string temp           = uEHelper.getSpecificCellValue("B" + rowIndex);
                if (string.IsNullOrEmpty(seq_p_c_record))
                {
                    break;
                }
                if (string.IsNullOrEmpty(temp))
                {
                    //左边到头。
                    break;
                }
                if (string.IsNullOrEmpty(man_hour) || "0".Equals(man_hour))
                {
                    //为该要工序.
                    //开始保存
                    //则process为概要工序
                    summary_process = uEHelper.getSpecificCellValue("B" + rowIndex);
                    pb.Value++;
                    continue;
                }
                //具体工序时先保存概要工序。
                sqlStr = string.Format(@"insert into Products_Cost_Header(SEQ,Product_Name,Summary_Process)
                                                values('{0}','{1}','{2}')", seq_p_c_record, PN, summary_process);
                OracleDaoHelper.executeSQL(sqlStr);
                pb.Value++;
            }
            //再检查第6至第9列  F,G,H,I
            for (int rowIndex = rowStartIndex; rowIndex < rowMaxIndex; rowIndex++)
            {
                string seq_p_c_record = uEHelper.getSpecificCellValue("F" + rowIndex);
                string man_hour       = uEHelper.getSpecificCellValue("H" + rowIndex);
                string temp           = uEHelper.getSpecificCellValue("G" + rowIndex);
                if (string.IsNullOrEmpty(seq_p_c_record))
                {
                    break;
                }
                if (string.IsNullOrEmpty(temp))
                {
                    //右边到头
                    break;
                }
                if (string.IsNullOrEmpty(man_hour) || "0".Equals(man_hour))
                {
                    //为该要工序.
                    //开始保存
                    //则process为概要工序
                    summary_process = uEHelper.getSpecificCellValue("G" + rowIndex);
                    pb.Value++;
                    continue;
                }
                //具体工序时先保存概要工序。
                sqlStr = string.Format(@"insert into Products_Cost_Header(SEQ,Product_Name,Summary_Process)
                                                values('{0}','{1}','{2}')", seq_p_c_record, PN, summary_process);
                OracleDaoHelper.executeSQL(sqlStr);
                pb.Value++;
            }
            #region 开始保存 第1至第4列的数据。
            for (int rowIndex = rowStartIndex; rowIndex < rowMaxIndex; rowIndex++)
            {
                string seq_p_c_record = uEHelper.getSpecificCellValue("A" + rowIndex);
                string temp           = uEHelper.getSpecificCellValue("B" + rowIndex);
                string man_hours      = uEHelper.getSpecificCellValue("C" + rowIndex);
                string labour_cost    = uEHelper.getSpecificCellValue("D" + rowIndex);
                if (string.IsNullOrEmpty(seq_p_c_record))
                {
                    break;
                }
                if (string.IsNullOrEmpty(temp))
                {
                    //右边到头
                    break;
                }
                if (!string.IsNullOrEmpty(man_hours) && !"0".Equals(man_hours))
                {
                    //为该要工序.
                    //开始保存
                    //则temp为具体工序。
                    specific_process = temp;
                    sqlStr           = string.Format(@"INSERT INTO PRODUCTS_Cost(
                                                                       SEQ,
                                                                       SEQ_P_C_Record,    
                                                                       Product_Name,
                                                                       Summary_Process,
                                                                       Specific_Process,
                                                                       Man_Hours,
                                                                       Labour_Cost,
                                                                       Supplier,
                                                                       UPDATE_Time
                                                                )
                                                                SELECT  SEQ_PRODUCTS_COST.Nextval,
                                                                        '{1}',      --- Seq_P_C_Record
                                                                        '{0}',
                                                                        Summary_Process,
                                                                        '{2}',     --- specific_process
                                                                        '{3}',     --- man_hours
                                                                        '{4}',      --- labour_cost
                                                                        '{5}',      --- supplier
                                                                        SYSDATE
                                                                FROM Products_Cost_Header
                                                                WHERE Product_Name = '{0}'
                                                                AND SEQ = '{1}'", PN,
                                                     seq_p_c_record,
                                                     specific_process,
                                                     man_hours,
                                                     labour_cost,
                                                     Program._userInfo.User_Name
                                                     );
                    OracleDaoHelper.executeSQL(sqlStr);
                    pb.Value++;
                }
            }
            #endregion
            #region 开始保存 第6至第9列的数据。
            for (int rowIndex = rowStartIndex; rowIndex < rowMaxIndex; rowIndex++)
            {
                string seq_p_c_record = uEHelper.getSpecificCellValue("F" + rowIndex);
                string temp           = uEHelper.getSpecificCellValue("G" + rowIndex);
                string man_hours      = uEHelper.getSpecificCellValue("H" + rowIndex);
                string labour_cost    = uEHelper.getSpecificCellValue("I" + rowIndex);
                if (string.IsNullOrEmpty(seq_p_c_record))
                {
                    break;
                }
                if (string.IsNullOrEmpty(temp))
                {
                    //右边到头
                    break;
                }
                if (!string.IsNullOrEmpty(man_hours) && !"0".Equals(man_hours))
                {
                    //为该要工序.
                    //开始保存
                    //则temp为具体工序。
                    specific_process = temp;
                    sqlStr           = string.Format(@"INSERT INTO PRODUCTS_Cost(
                                                                       SEQ,
                                                                        SEQ_P_C_Record,
                                                                       Product_Name,
                                                                       Summary_Process,
                                                                       Specific_Process,
                                                                       Man_Hours,
                                                                       Labour_Cost,
                                                                       Supplier,
                                                                       UPDATE_Time
                                                                )
                                                                SELECT  SEQ_PRODUCTS_COST.Nextval,
                                                                        '{1}',      ---  SEQ_P_C_Record
                                                                        '{0}',
                                                                        Summary_Process,
                                                                        '{2}',     --- specific_process
                                                                        '{3}',     --- man_hours
                                                                        '{4}',      --- labour_cost
                                                                        '{5}',      --- supplier
                                                                        SYSDATE
                                                                FROM Products_Cost_Header
                                                                WHERE Product_Name = '{0}'
                                                                AND SEQ = '{1}'", PN,
                                                     seq_p_c_record,
                                                     specific_process,
                                                     man_hours,
                                                     labour_cost,
                                                     Program._userInfo.User_Name);
                    OracleDaoHelper.executeSQL(sqlStr);
                    pb.Value++;
                }
            }
            #endregion
            myExcel.close();
            IShowProductsCostDetail showProductsCostDetailImpl = new ShowProductsCostDetailImpl();
            this.dgv.DataSource = showProductsCostDetailImpl.getProductsDetail(PN);
            DGVHelper.AutoSizeForDGV(dgv);
            pb.Visible        = false;
            lblResult.Visible = true;
        }
예제 #18
0
        /// <summary>
        //  导出至Excel.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void ExportToExceltoolStripMenuItem_Click(object sender, EventArgs e)
        {
            DataGridViewRowCollection drRows = this.dgv.Rows;

            if (drRows.Count == 0)
            {
                return;
            }


            //1. 先确定 image path
            if (CmdHelper.ifExistsTheProcessByName("excel") || CmdHelper.ifExistsTheProcessByName("et"))
            {
                Tools.FrmPrompt frmPrompt = new Tools.FrmPrompt("excel.exe|et.exe");
                frmPrompt.ShowDialog();
            }
            //2. 打开Excel
            //先获取时间字符串
            string currTimeStr  = TimeHelper.getCurrentTimeStr();
            string xlsFileName  = "成衣成本汇总_" + currTimeStr + ".xls";
            string srcFilePath  = Application.StartupPath + "\\成本汇总\\ProductsCostSummaryTemplate.xls";
            string destDir      = Application.StartupPath + "\\成本汇总\\";
            string destFileName = xlsFileName;

            CmdHelper.copyFileToDestDirWithNewFileName(srcFilePath, destDir, destFileName);
            //目的文件名为:
            destFilePath = destDir + xlsFileName;
            MyExcel myExcel = new MyExcel(destFilePath);

            myExcel.open(true);
            Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(myExcel.getFirstWorkSheetAfterOpen());

            for (int index = 0; index <= drRows.Count - 1; index++)
            {
                DataGridViewRow currRow = drRows[index];

                string product_name = currRow.Cells["Product_Name"].Value.ToString();
                uEHelper.setSpecificCellValue("A" + (2 + index).ToString(), product_name);
                //(drRow.Cells["product_name"]
                uEHelper.setSpecificCellValue("B" + (2 + index).ToString(), currRow.Cells["total_man_hours"].Value.ToString());
                uEHelper.setSpecificCellValue("C" + (2 + index).ToString(), currRow.Cells["total_labour_cost"].Value.ToString());
                uEHelper.setSpecificCellValue("D" + (2 + index).ToString(), currRow.Cells["supplier"].Value.ToString());
                uEHelper.setSpecificCellValue("E" + (2 + index).ToString(), currRow.Cells["latest_update_time"].Value.ToString());
                //picture
                //保存此图片
                //Image image = ((Image)(currRow.Cells["picture"].Value));
                byte[] pictureByteArray = (byte[])currRow.Cells["picture"].Value;
                Image  image            = PictureHelper.ReturnPhoto(pictureByteArray);

                string picPath = string.Format(@"{0}\{1}.jpg", destDir, product_name);
                image.Save(picPath, image.RawFormat);

                Microsoft.Office.Interop.Excel.Range range = uEHelper.getRange("F" + (2 + index).ToString(), "F" + (2 + index).ToString());
                uEHelper.pastePicture(range, picPath);
            }
            myExcel.saveWithoutAutoFit();
            myExcel.close();

            ShowResult.show(lblResult, string.Format(@"导出完毕,存于:{0}", destFilePath), true);
            timerRestoreLabel.Start();
        }
        /// <summary>
        /// 导入
        /// </summary>
        public void import_P_C_Record_Template()
        {
            string xlsFilePath = FileNameDialog.getSelectedFilePathWithDefaultDir("请选择成衣成本记录:", "*.xls,*.xlsx|*.xls;*.xlsx", defaultDir);

            if (string.IsNullOrEmpty(xlsFilePath))
            {
                return;
            }
            if (!File.Exists(xlsFilePath))
            {
                return;
            }
            tbPath.Text = xlsFilePath;
            MyExcel myExcel = new MyExcel(xlsFilePath);

            myExcel.open();
            Tools.AppManagement.add(myExcel.HwndOfApp);
            Worksheet firstWS = myExcel.getFirstWorkSheetAfterOpen();
            int       notProductsCostFile_Count = 0;

            //非成本文件,则忽略。
            if (!isProductsCostRecordOfGroup1(firstWS))
            {
                notProductsCostFile_Count++;
                lblPromptForPB.Visible = false;
                ShowResult.show(lblResult, "   第1个表格非成衣成本文件!依据:第4行3列和第4行4列均非数字!", false);
                myExcel.close();
                return;
            }
            Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(firstWS);
            string             PN       = uEHelper.getSpecificCellValue("A1");

            if ("请在此填写成品名称".Equals(PN))
            {
                MessageBox.Show(firstWS.Index + "." + firstWS.Name + ":  请填写成品名称!", "提示:", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            if (string.IsNullOrEmpty(PN))
            {
                MessageBox.Show(firstWS.Index + "." + firstWS.Name + ": 产品名称不能为空!", "提示:", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            //判断是否存在该成品的记录
            string sqlStr = string.Format(@"SELECT 1 FROM Products_Cost WHERE Product_Name = '{0}'", PN);

            System.Data.DataTable dt = OracleDaoHelper.getDTBySql(sqlStr);
            if (dt.Rows.Count > 0)
            {
                //删除文档
                sqlStr = string.Format(@"DELETE FROM Products_Cost where Product_Name = '{0}'", PN);
                OracleDaoHelper.executeSQL(sqlStr);
            }
            //先导入概要工序。
            int rowMaxIndexOfGroup1 = uEHelper.getTheMaxRowIndexUntilBlankCellOfTheSpecificCol(1);
            int rowMaxIndexOfGroup2 = 0;
            //判断是否存在=
            bool ifExistsGroup2 = isProductsCostRecordOfGroup2(firstWS);

            if (ifExistsGroup2)
            {
                rowMaxIndexOfGroup2 = uEHelper.getTheMaxRowIndexUntilBlankCellOfTheSpecificCol(6);
            }
            //获取第3行
            //int rowMaxIndex = firstWS.UsedRange.RofirstWS.Count;
            pb.Maximum        = rowMaxIndexOfGroup1 - 2 + rowMaxIndexOfGroup2 - 2;
            pb.Value          = 0;
            pb.Visible        = true;
            lblResult.Visible = false;
            OracleConnection theConnForTransaction = new OracleConnection(OracleDaoHelper.conn_str);

            theConnForTransaction.Open();
            OracleTransaction tran = theConnForTransaction.BeginTransaction();
            string            prepared_Summary_process = string.Empty;

            //先读取第4列的内容。
            for (int rowIndex = 3; rowIndex <= rowMaxIndexOfGroup1; rowIndex++)
            {
                string seq_p_c_record = uEHelper.getSpecificCellValue("A" + rowIndex);
                //序号为空跳出循环。
                if (string.IsNullOrEmpty(seq_p_c_record))
                {
                    break;
                }
                string content_B_Col = uEHelper.getSpecificCellValue("B" + rowIndex).Trim();
                //1.读取C列内容,如果为空,说明是主工序。
                string content_C_Col = uEHelper.getSpecificCellValue("C" + rowIndex);
                if (string.IsNullOrEmpty(content_C_Col))
                {
                    //执行下一次循环。
                    prepared_Summary_process = content_B_Col;
                    continue;
                }
                string specific_process = content_B_Col;
                //2. C列不为空。
                string man_hours   = content_C_Col;
                string labour_cost = uEHelper.getSpecificCellValue("D" + rowIndex);

                if (string.IsNullOrEmpty(specific_process))
                {
                    break;
                }
                ;
                if (string.IsNullOrEmpty(man_hours))
                {
                    break;
                }
                ;
                if (string.IsNullOrEmpty(labour_cost))
                {
                    break;
                }
                ;
                //插入该大类中的第一行数据。
                sqlStr = string.Format(@"INSERT INTO Products_Cost(SEQ,SEQ_P_C_RECORD,Product_Name,Summary_Process,Specific_Process,Man_hours,Labour_cost,Supplier)
                                        VALUES(SEQ_Products_Cost.nextVal,'{0}','{1}','{2}','{3}','{4}','{5}','{6}')",
                                       seq_p_c_record,
                                       PN,
                                       prepared_Summary_process,
                                       specific_process,
                                       man_hours,
                                       labour_cost,
                                       Program._userInfo.User_Name);
                try
                {
                    OracleDaoHelper.executeSQLThrowExceptioin(sqlStr, theConnForTransaction);
                }
                catch (Exception ex)
                {
                    if (ex.ToString().Contains("ORA-00001"))
                    {
                        MessageBox.Show(firstWS.Index + "." + firstWS.Name + ": 第" + rowIndex + "行为重复工序,请检查成本表格,请重新导入 . ", "提示:", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        tran.Rollback();
                        theConnForTransaction.Close();
                        lblPromptForPB.Visible = false;
                        pb.Visible             = false;
                        myExcel.close();
                        return;
                    }
                    MessageBox.Show(firstWS.Index + "." + firstWS.Name + ": 第" + rowIndex + "行,数据导入时发生异常,请检查成本表格,重新导入。" + ex.ToString(), "提示:", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    tran.Rollback();
                    theConnForTransaction.Close();
                    lblPromptForPB.Visible = false;
                    pb.Visible             = false;
                    myExcel.close();
                    return;
                }
                lblPromptForPB.Visible = true;
                lblPromptForPB.Text    = firstWS.Index + "." + firstWS.Name + ": ";
                pb.Value++;
                continue;
            }
            //先读取第9列的内容。
            for (int rowIndex = 3; rowIndex <= rowMaxIndexOfGroup2; rowIndex++)
            {
                string seq_p_c_record = uEHelper.getSpecificCellValue("F" + rowIndex);
                //序号为空跳出循环。
                if (string.IsNullOrEmpty(seq_p_c_record))
                {
                    break;
                }
                string content_G_Col = uEHelper.getSpecificCellValue("G" + rowIndex).Trim();
                //1.读取C列内容,如果为空,说明是主工序。
                string content_H_Col = uEHelper.getSpecificCellValue("H" + rowIndex);
                if (string.IsNullOrEmpty(content_H_Col))
                {
                    //执行下一次循环。
                    prepared_Summary_process = content_G_Col;
                    continue;
                }
                string specific_process = content_G_Col;
                //2. C列不为空。
                string man_hours   = content_H_Col;
                string labour_cost = uEHelper.getSpecificCellValue("I" + rowIndex);

                if (string.IsNullOrEmpty(specific_process))
                {
                    break;
                }
                ;
                if (string.IsNullOrEmpty(man_hours))
                {
                    break;
                }
                ;
                if (string.IsNullOrEmpty(labour_cost))
                {
                    break;
                }
                ;
                //插入该大类中的第一行数据。
                sqlStr = string.Format(@"INSERT INTO Products_Cost(SEQ,SEQ_P_C_RECORD,Product_Name,Summary_Process,Specific_Process,Man_hours,Labour_cost,Supplier)
                                        VALUES(SEQ_Products_Cost.nextVal,'{0}','{1}','{2}','{3}','{4}','{5}','{6}')",
                                       seq_p_c_record,
                                       PN,
                                       prepared_Summary_process,
                                       specific_process,
                                       man_hours,
                                       labour_cost,
                                       Program._userInfo.User_Name);
                try
                {
                    OracleDaoHelper.executeSQLThrowExceptioin(sqlStr, theConnForTransaction);
                }
                catch (Exception ex)
                {
                    if (ex.ToString().Contains("ORA-00001"))
                    {
                        MessageBox.Show(firstWS.Index + "." + firstWS.Name + ": 第" + rowIndex + "行为重复工序,请检查成本表格,请重新导入 . ", "提示:", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        tran.Rollback();
                        theConnForTransaction.Close();
                        lblPromptForPB.Visible = false;
                        pb.Visible             = false;
                        myExcel.close();
                        return;
                    }
                    MessageBox.Show(firstWS.Index + "." + firstWS.Name + ": 第" + rowIndex + "行,数据导入时发生异常,请检查成本表格,重新导入。" + ex.ToString(), "提示:", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    tran.Rollback();
                    theConnForTransaction.Close();
                    lblPromptForPB.Visible = false;
                    pb.Visible             = false;
                    myExcel.close();
                    return;
                }
                lblPromptForPB.Visible = true;
                lblPromptForPB.Text    = firstWS.Index + "." + firstWS.Name + ": ";
                pb.Value++;
                continue;
            }
            tran.Commit();
            theConnForTransaction.Close();

            pb.Visible             = false;
            lblPromptForPB.Visible = false;
            lblPromptForPB.Text    = "";
            lblResult.Visible      = true;
            IShowProductsCostDetail showProductsCostDetailImpl = new ShowProductsCostDetailImpl();

            this.dgv.DataSource = showProductsCostDetailImpl.getProductsDetail(PN);
            DGVHelper.AutoSizeForDGV(dgv);
            myExcel.close();
            //ShowResult.show(lblResult, "   第1个表格非成衣成本文件!依据:第3行4列和第3行5列均非数字!", false);
            ShowResult.show(lblResult, "   导入完成!", true);
            timerRestoreLblResult.Enabled = true;
        }
        /// <summary>
        ///
        /// </summary>
        private void separateOutputAR()
        {
            string _defaultDir = System.Windows.Forms.Application.StartupPath + "\\考勤汇总";

            killHwndOfXls();
            Queue <int> prefix_Of_Staffs_Queue = V_AR_RESULT.get_Prefix_Staffs(YearAndMonthStr);

            if (prefix_Of_Staffs_Queue.Count == 0)
            {
                ShowResult.show(lblResult, "尚未导入本月的考勤记录!", false);
                timerRestoreTheLblResult.Enabled = true;
                return;
            }
            string prefix_Of_Staffs = string.Empty;

            //分几个工作表储存。
            while (prefix_Of_Staffs_Queue.Count > 0)
            {
                prefix_Of_Staffs = prefix_Of_Staffs_Queue.Dequeue().ToString();
                string _fileName = YearAndMonthStr + "_考勤汇总" + prefix_Of_Staffs.Substring(0, 1) + ".xls";
                if (!xlsFilePath.Contains(":"))
                {
                    //导出到Excel中
                    xlsFilePath = FileNameDialog.getSaveFileNameWithDefaultDir("考勤汇总:", "*.xls|*.xls", _defaultDir, _fileName);
                    if (!xlsFilePath.Contains(@"\"))
                    {
                        return;
                    }
                }
                else
                {
                    int index = xlsFilePath.LastIndexOf(string.Format(@"{0}_考勤汇总", YearAndMonthStr));
                    xlsFilePath = xlsFilePath.Remove(index) + _fileName;
                }
                //int index = xlsFilePath.LastIndexOf(string.Format(@"{0}_考勤汇总", YearAndMonthStr));
                //创建文件
                //DirectoryHelper.createFile(xlsFilePath);
                //xlsFilePath = xlsFilePath.Remove(index) + _fileName;
                MyExcel myExcel = new MyExcel(xlsFilePath);
                myExcel.create();
                myExcel.openWithoutAlerts();
                //追加Hwnd到队列中.
                hwndOfXls_Queue.Enqueue(myExcel.HwndOfApp);
                MSG msg = new MSG();
                //prefix_Of_Staffs = prefix_Of_Staffs_Queue.Dequeue().ToString();
                //依据前缀和月份获取列表。
                //获取第一张表
                Worksheet          _firstWS = myExcel.getFirstWorkSheetAfterOpen();
                Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(_firstWS);
                uEHelper.clearAllContents(_firstWS.UsedRange);
                //清空该文档中的内容。
                Worksheet _secondWS = myExcel.getSecondWorksheetAfterOpen();
                uEHelper = new Usual_Excel_Helper(_secondWS);
                uEHelper.clearAllContents(_secondWS.UsedRange);
                Worksheet _thirdWS = myExcel.getThirdWorksheetAfterOpen();
                uEHelper = new Usual_Excel_Helper(_thirdWS);
                uEHelper.clearAllContents(_thirdWS.UsedRange);
                int machine_no = int.Parse(prefix_Of_Staffs.Substring(0, 1));
                //获取一个月内,某考勤机的考勤天数
                int nums_of_ar_days = GetARSummary.GetARSummary.get_nums_of_ar_days(machine_no, YearAndMonthStr);
                //考勤机的考勤天数
                int nums_of_staffs = GetARSummary.GetARSummary.get_nums_of_staffs(machine_no, YearAndMonthStr);
                System.Data.DataTable dt_Staff_Info       = GetARSummary.GetARSummary.get_Staff_info(YearAndMonthStr, machine_no);
                System.Data.DataTable dt_AR_Of_Each_Staff = GetARSummary.GetARSummary.get_AR_Of_Each_Staff(YearAndMonthStr, machine_no);
                System.Data.DataTable dt_AR_Summary       = GetARSummary.GetARSummary.Get_AR_Summary(YearAndMonthStr, machine_no);
                //隐藏 结果 label;
                lblResult.Visible = false;
                lblPrompt.Visible = true;
                lblPrompt.Text    = "考勤机" + prefix_Of_Staffs.Substring(0, 1) + ": 员工信息汇总中...";
                ExcelHelper.saveDtToExcelWithProgressBar(dt_Staff_Info, _firstWS, pb);
                lblPrompt.Text = "考勤机" + prefix_Of_Staffs.Substring(0, 1) + ": 考勤记录汇总中...";
                ExcelHelper.saveDtToExcelWithProgressBar(dt_AR_Of_Each_Staff, _secondWS, pb);
                lblPrompt.Text = "考勤机" + prefix_Of_Staffs.Substring(0, 1) + ": 汇总中...";
                ExcelHelper.saveDtToExcelWithProgressBar(dt_AR_Summary, _thirdWS, pb);
                Microsoft.Office.Interop.Excel.Range range_Src_AR;
                //目标 区域
                Microsoft.Office.Interop.Excel.Range range_desc_AR;
                //AR_Time 在D列存放
                Usual_Excel_Helper uEHelper_firstWS = new Usual_Excel_Helper(_firstWS);
                for (int i = 1; i <= nums_of_staffs; i++)
                {
                    range_Src_AR = ((Microsoft.Office.Interop.Excel.Range)_secondWS.Range[_secondWS.Cells[(i - 1) * (nums_of_ar_days) + 2, 4], _secondWS.Cells[i * nums_of_ar_days + 1]]);
                    range_Src_AR.Copy();
                    //第一张sheet,第4列
                    range_desc_AR = ((Microsoft.Office.Interop.Excel.Range)_firstWS.Cells[i + 1, 4]);
                    uEHelper_firstWS.pasteByTranspose(range_desc_AR);
                }
                //关闭excel
                myExcel.save();
                myExcel.close();
            }
            lblResult.Visible = true;
            lblPrompt.Visible = false;
        }
        /// <summary>
        /// 导出到Excel
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnExport_Click(object sender, EventArgs e)
        {
            OracleHelper oH = OracleHelper.getBaseDao();

            if (string.IsNullOrEmpty(cbPN.Text.Trim()))
            {
                return;
            }
            if (CmdHelper.ifExistsTheProcessByName("EXCEL"))
            {
                return;
            }
            //获取该成品对应的工序。
            System.Data.DataTable dt       = null;
            OracleParameter       paramPN  = new OracleParameter("v_PN", OracleDbType.NVarchar2, 20);
            OracleParameter       paramCur = new OracleParameter("retureCur", OracleDbType.RefCursor);

            paramPN.Direction  = ParameterDirection.Input;
            paramCur.Direction = ParameterDirection.ReturnValue;
            paramPN.Value      = cbPN.Text.Trim();
            OracleParameter[] parameters = new OracleParameter[2] {
                paramCur, paramPN
            };
            string procName = "Show_Products_Cost.get_P_C_Info_To_Piecework";

            dt        = oH.getDT(procName, parameters);
            _fileName = cbPN.Text.Trim() + ".xls";
            DirectoryHelper.createDirecotry(_defaultDir);
            xlsFilePath = _defaultDir + _fileName;
            //获取一个Excel
            V_New_Excel        v_Excel  = new V_New_Excel(xlsFilePath);
            Worksheet          wS       = v_Excel.WS;
            Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(wS);

            uEHelper.setSpecificCellValue(1, 1, cbPN.Text.Trim() + "报工单", 11, true);
            //写序号标题
            uEHelper.setSpecificCellValue(2, 3, "姓名");
            uEHelper.setSpecificCellValue(3, 1, "序号");
            uEHelper.setSpecificCellValue(3, 2, "部位");
            uEHelper.setSpecificCellValue(3, 3, "工序");
            //姓名占据两列
            //uEHelper.merge("C2", "D2");
            //uEHelper.merge("C3","D3");
            //Range range = wS.Range[wS.Cells[2, 3], wS.Cells[2, 4]];
            //uEHelper.textAlighRight(range);
            //工序名称,单据时间靠右
            //range = wS.Range[wS.Cells[3, 4], wS.Cells[3, 5]];
            //uEHelper.textAlighRight(range);
            //设置第三列宽度
            uEHelper.setColumnWidth("C", 42.25M);
            uEHelper.setColumnWidth("A", 5.13M);
            uEHelper.setRowHeight(1, 31);
            uEHelper.setRowHeight(2, 31);
            pb.Visible        = true;
            pb.Value          = 0;
            pb.Maximum        = dt.Rows.Count;
            lblResult.Visible = false;
            lblPrompt.Visible = true;
            lblPrompt.Text    = "请等待!";
            //开始写信息
            for (int i = 0; i <= dt.Rows.Count - 1; i++)
            {
                uEHelper.setSpecificCellValue(i + 4, 1, dt.Rows[i]["row_num"].ToString());
                uEHelper.setSpecificCellValue(i + 4, 2, dt.Rows[i]["Summary_Process"].ToString());
                uEHelper.setSpecificCellValue(i + 4, 3, dt.Rows[i]["specific_Process"].ToString());
                pb.Value++;
            }
            pb.Visible        = false;
            pb.Value          = 0;
            lblResult.Visible = true;
            lblPrompt.Visible = false;
            lblPrompt.Text    = "";
            for (int columnIndex = 4; columnIndex <= 30; columnIndex++)
            {
                uEHelper.setColumnWidthByColumnIndex(columnIndex, 4.5M);
            }
            Range range = wS.Range[wS.Cells[2 + dt.Rows.Count, 30], wS.Cells[2 + dt.Rows.Count, 30]];

            uEHelper.setAllTheBoxLine(range);
            uEHelper.merge("A1", "AD1");
            v_Excel.displayAlert();
            uEHelper.MergeTheSpecificColumnWithoutBlankContent(2);
            //设置边框。
            uEHelper.setAllTheBoxLine();
            v_Excel.saveWithoutAutoFit();
            v_Excel.closeTheApp();
            ShowResult.show(lblResult, cbPN.Text.Trim() + " 产量统计模板保存于: " + xlsFilePath, true);
            timerRestoreLblResult.Start();
        }
        /// <summary>
        /// 后台处理
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void readDataToDB_bgWorker_DoWork_Handler(object sender, DoWorkEventArgs e)
        {
            this.myExcel = new MyExcel(xlsFilePath);
            myExcel.open(false);
            this.percentProgress = 0;
            //开始读取Excel中的内容。
            //1. 产量记录从第三行,第六列开始。
            //2. 产量记录到第几列结束了?
            this._firstSheet = myExcel.getFirstWorkSheetAfterOpen();
            this.uEHelper    = new Usual_Excel_Helper(_firstSheet);
            //获取最大列
            this.validMaxColIndex = uEHelper.getColIndexOfSpecificContentInSpecificRow(2, 1, uEHelper.getMaxColIndex(), "终止标识") - 1;
            //获取最大行从第三行起为序号行,当第1列,第一次出现为空的行时,即最大行。
            this.validMaxRowIndex = uEHelper.getMaxRowIndexBeforeBlankCell(1);
            //********定义局部变量******
            string pn, report_year_month_str, lineName, reportTeamName;
            //判断是否已经存在该线体的月报表
            string contentOfA1 = uEHelper.getCellContentByRowAndColIndex(1, 1);
            //1.判断产品名_组_月份格式是否正确。
            bool flag = StringHelper.checkPN_Team_Month(contentOfA1, out pn, out report_year_month_str, out lineName, out reportTeamName);

            if (!flag)
            {
                msg.Flag = false;
                msg.Msg  = "报表中A1单元格内容格式应为:产品名_衬衣1组_3月";
                this.readDataToDB_bgWorker.ReportProgress(0, msg);
                myExcel.close();
                return;
            }
            //2.判断线体,组名是否存在
            msg = isValid_TheLineName_Or_TheTeamName(reportTeamName, lineName);
            if (!msg.Flag)
            {
                this.readDataToDB_bgWorker.ReportProgress(0, msg);
                myExcel.close();
                return;
            }
            //2.判断该组,在某月,某线体,所作的某产品 在数据库中是否已经有记录?
            System.Data.DataTable dt = Line_Each_One_Quantities.getAllQuantitiesOfTheLine_team_pn_report(lineName, reportTeamName, pn, report_year_month_str);
            if (dt.Rows.Count > 0)
            {
                msg.Flag = false;
                msg.Msg  = string.Format(@"{0}: 线体(地点):{1},组名:{2},月份: {3}  已经存在!", pn, lineName, reportTeamName, report_year_month_str);
                this.readDataToDB_bgWorker.ReportProgress(0, msg);
                myExcel.close();
                return;
            }
            //3.先检查工序列有没有工序为空的
            msg = checkValidityOfAllProcesses();
            if (!msg.Flag)
            {
                this.readDataToDB_bgWorker.ReportProgress(0, msg);
                myExcel.close();
                return;
            }
            //4.检查金额列有无空或者为0.00的数值
            msg = checkValidityOfAmountOfMoneyOfAllProcesses();
            if (!msg.Flag)
            {
                this.readDataToDB_bgWorker.ReportProgress(0, msg);
                myExcel.close();
                return;
            }
            List <NameAndRealTeam> nameAndRealTeamList = null;

            //5. 检查所有的姓名是否符合格式
            msg = checkValidityOfAllNameOfLine_Yields_Report(out nameAndRealTeamList);
            if (!msg.Flag)
            {
                this.readDataToDB_bgWorker.ReportProgress(0, msg);
                myExcel.close();
                return;
            }
            List <Line_Each_One_Quantities> line_each_one_quantities_list = new List <Line_Each_One_Quantities>();
            int maxmium = (validMaxRowIndex - 3 + 1) * (validMaxColIndex - 6 + 1);
            int count   = 0;

            msg.Flag = true;
            msg.Msg  = "准备读取数据...";
            readDataToDB_bgWorker.ReportProgress(0, msg);
            //自第(3,6)开始
            for (int currColIndex = 6; currColIndex <= validMaxColIndex; currColIndex++)
            {
                for (int currRowIndex = 3; currRowIndex <= validMaxRowIndex; currRowIndex++)
                {
                    count++;
                    string contentStr = uEHelper.getCellContentByRowAndColIndex(currRowIndex, currColIndex);
                    //为空,定位到下一个单元格。
                    if (string.IsNullOrEmpty(contentStr))
                    {
                        readDataToDB_bgWorker.ReportProgress((count * 100 / maxmium));
                        continue;
                    }
                    //判断是否为数字
                    int quantities = 0;
                    flag = int.TryParse(contentStr, out quantities);
                    //若不为数字
                    if (!flag)
                    {
                        msg.Flag = false;
                        msg.Msg  = string.Format(@"第{0}行,第{1}列,应为整数!", currRowIndex, currColIndex);
                        readDataToDB_bgWorker.ReportProgress((count / maxmium) * 100, msg);
                        myExcel.close();
                        return;
                    }
                    //判断是否为整数
                    Line_Each_One_Quantities each_One_Quantities = new Line_Each_One_Quantities();
                    each_One_Quantities.Line_Name             = lineName;
                    each_One_Quantities.Report_team_name      = reportTeamName;
                    each_One_Quantities.Products_name         = pn;
                    each_One_Quantities.Report_year_month_str = report_year_month_str;
                    each_One_Quantities.Quantities            = quantities;
                    //第二列为部位
                    each_One_Quantities.Summary_process = uEHelper.getCellContentByRowAndColIndex(currRowIndex, 2);
                    //第三列为工序
                    each_One_Quantities.Specific_process = uEHelper.getCellContentByRowAndColIndex(currRowIndex, 3);
                    each_One_Quantities.Man_hour         = int.Parse(uEHelper.getCellContentByRowAndColIndex(currRowIndex, 4));
                    each_One_Quantities.Amount_of_money  = decimal.Parse(uEHelper.getCellContentByRowAndColIndex(currRowIndex, 5));
                    //记录姓名,姓名,自第6列开始。
                    string realTeamName = nameAndRealTeamList[currColIndex - 6].Real_team_name;
                    if (!string.IsNullOrEmpty(realTeamName))
                    {
                        each_One_Quantities.Real_team_name = realTeamName;
                    }
                    else
                    {
                        each_One_Quantities.Real_team_name = reportTeamName;
                    }
                    each_One_Quantities.Emp_name = nameAndRealTeamList[currColIndex - 6].Emp_name;
                    line_each_one_quantities_list.Add(each_One_Quantities);
                    readDataToDB_bgWorker.ReportProgress((count * 100 / maxmium));
                }
            }
            msg.Flag = true;
            msg.Msg  = "提交数据中...";
            readDataToDB_bgWorker.ReportProgress(0, msg);
            count = 0;
            //开始提交数据.
            maxmium = line_each_one_quantities_list.Count;
            for (int i = 0; i < maxmium; i++)
            {
                count++;
                readDataToDB_bgWorker.ReportProgress((count * 100 / maxmium));
                line_each_one_quantities_list[i].save();
            }
            msg.Msg = "提交完成。";
            readDataToDB_bgWorker.ReportProgress((count * 100 / maxmium), msg);
            myExcel.close();
        }
        /// <summary>
        /// 导入
        /// </summary>
        public void import_P_C_Record_Of_First_Sheet()
        {
            //确认关闭已经打开的Excel
            if (CmdHelper.ifExistsTheProcessByName("EXCEL"))
            {
                FrmCloseExcel frmCloseExcel = new FrmCloseExcel();
                frmCloseExcel.ShowDialog();
            }
            string xlsFilePath = FileNameDialog.getSelectedFilePathWithDefaultDir("请选择成衣成本记录:", "*.xls,*.xlsx|*.xls;*.xlsx", defaultDir);

            if (string.IsNullOrEmpty(xlsFilePath))
            {
                return;
            }
            if (!File.Exists(xlsFilePath))
            {
                return;
            }
            tbPath.Text = xlsFilePath;
            MyExcel myExcel = new MyExcel(xlsFilePath);

            myExcel.open();
            Tools.AppManagement.add(myExcel.HwndOfApp);
            Worksheet firstWS = myExcel.getFirstWorkSheetAfterOpen();
            MSG       msg     = null;

            //非成本文件,则忽略。
            if (!(msg = isProductsCostRecord(firstWS)).Flag)
            {
                lblPromptForPB.Visible = false;
                ShowResult.show(lblResult, msg.Msg, false);
                myExcel.close();
                return;
            }
            Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(firstWS);
            string             PN       = uEHelper.getSpecificCellValue("A1");

            if ("请在此填写成品名称".Equals(PN))
            {
                MessageBox.Show(firstWS.Index + "." + firstWS.Name + ":  请填写成品名称!", "提示:", MessageBoxButtons.OK, MessageBoxIcon.Information);
                myExcel.close();
                return;
            }
            if (string.IsNullOrEmpty(PN))
            {
                MessageBox.Show(firstWS.Index + "." + firstWS.Name + ": 产品名称不能为空!", "提示:", MessageBoxButtons.OK, MessageBoxIcon.Information);
                myExcel.close();
                return;
            }
            //判断是否存在该成品的记录
            string sqlStr = string.Format(@"SELECT 1 FROM Products_Cost WHERE Product_Name = '{0}'", PN);

            System.Data.DataTable dt = OracleDaoHelper.getDTBySql(sqlStr);
            if (dt.Rows.Count > 0)
            {
                //删除文档
                sqlStr = string.Format(@"DELETE FROM Products_Cost where Product_Name = '{0}'", PN);
                OracleDaoHelper.executeSQL(sqlStr);
            }
            //先导入概要工序。
            //最大行,取
            //从第二行开始,取第三列连续出现工序的最大行
            int rowMaxIndex = uEHelper.getTheMaxRowIndexUntilBlankCellOfTheSpecificCol(2, 3);
            //int rowMaxIndex = firstWS.UsedRange.RofirstWS.Count;
            string seq_p_c_record   = string.Empty;
            string summary_process  = string.Empty;
            string specific_process = string.Empty;
            string man_hours        = string.Empty;
            string labour_cost      = string.Empty;

            pb.Maximum        = rowMaxIndex - 2;
            pb.Value          = 0;
            pb.Visible        = true;
            lblResult.Visible = false;
            OracleConnection theConnForTransaction = new OracleConnection(OracleDaoHelper.conn_str);

            theConnForTransaction.Open();
            OracleTransaction tran = theConnForTransaction.BeginTransaction();

            //先删除Check_Products_Cost_Doc中的所有记录。
            CheckProductsCostDoc.deleteAllRecord();
            //检查工时数据,成本数据是否正确。
            for (int rowIndex = 3; rowIndex <= rowMaxIndex; rowIndex++)
            {
                seq_p_c_record = uEHelper.getSpecificCellValue("A" + rowIndex);
                string prepared_Summary_process = uEHelper.getSpecificCellValue("B" + rowIndex).Trim();
                specific_process = uEHelper.getSpecificCellValue("C" + rowIndex);
                man_hours        = uEHelper.getSpecificCellValue("D" + rowIndex);
                labour_cost      = uEHelper.getSpecificCellValue("E" + rowIndex);
                if (string.IsNullOrEmpty(seq_p_c_record))
                {
                    break;
                }
                if (string.IsNullOrEmpty(specific_process))
                {
                    break;
                }
                ;
                if (string.IsNullOrEmpty(man_hours))
                {
                    break;
                }
                ;
                if (string.IsNullOrEmpty(labour_cost))
                {
                    break;
                }
                //判断man_hours是否为decimal;
                decimal _man_hours_d;
                decimal _labour_cost_d;
                if (!decimal.TryParse(man_hours, out _man_hours_d))
                {
                    lblPromptForPB.Visible = false;
                    pb.Visible             = false;
                    ShowResult.show(lblResult, string.Format(@"第{0}行第4列,非工时数据,请检查!", rowIndex), false);
                    timerRestoreLblResult.Enabled = true;
                    myExcel.close();
                    return;
                }
                if (!decimal.TryParse(labour_cost, out _labour_cost_d))
                {
                    lblPromptForPB.Visible = false;
                    pb.Visible             = false;
                    ShowResult.show(lblResult, string.Format(@"第{0}行第5列,非单价数据,请检查!", rowIndex), false);
                    timerRestoreLblResult.Enabled = true;
                    myExcel.close();
                    return;
                }
                //若主工序不为空
                if (!"".Equals(prepared_Summary_process))
                {
                    summary_process = prepared_Summary_process;
                }
                CheckProductsCostDoc checkProductsCostDoc = new CheckProductsCostDoc(rowIndex, summary_process, specific_process);
                msg = checkProductsCostDoc.ifExistsSameProcess();
                if (msg.Flag)
                {
                    lblPromptForPB.Visible = false;
                    pb.Visible             = false;
                    ShowResult.show(lblResult, string.Format(@"序号:{0}行与{1},工序相同,请检查。", seq_p_c_record, msg.Msg), false);
                    timerRestoreLblResult.Enabled = true;
                    myExcel.close();
                    return;
                }
                checkProductsCostDoc.addProcess();
                lblPromptForPB.Visible = true;
                lblPromptForPB.Text    = firstWS.Index + "." + firstWS.Name + " 检查中: ";
                pb.Value++;
            }
            pb.Value = 0;
            //保存工序数据到后台。
            for (int rowIndex = 3; rowIndex <= rowMaxIndex; rowIndex++)
            {
                seq_p_c_record = uEHelper.getSpecificCellValue("A" + rowIndex);
                string prepared_Summary_process = uEHelper.getSpecificCellValue("B" + rowIndex).Trim();
                specific_process = uEHelper.getSpecificCellValue("C" + rowIndex);
                man_hours        = uEHelper.getSpecificCellValue("D" + rowIndex);
                labour_cost      = uEHelper.getSpecificCellValue("E" + rowIndex);
                if (string.IsNullOrEmpty(seq_p_c_record))
                {
                    break;
                }
                if (string.IsNullOrEmpty(specific_process))
                {
                    break;
                }
                ;
                if (string.IsNullOrEmpty(man_hours))
                {
                    break;
                }
                ;
                if (string.IsNullOrEmpty(labour_cost))
                {
                    break;
                }
                //若主工序不为空
                if (!"".Equals(prepared_Summary_process))
                {
                    summary_process = prepared_Summary_process;
                }
                //插入该大类中的第一行数据。
                sqlStr = string.Format(@"INSERT INTO Products_Cost(SEQ,SEQ_P_C_RECORD,Product_Name,Summary_Process,Specific_Process,Man_hours,Labour_cost,Supplier)
                                        VALUES(SEQ_Products_Cost.nextVal,'{0}','{1}','{2}','{3}','{4}','{5}','{6}')",
                                       seq_p_c_record,
                                       PN,
                                       summary_process,
                                       specific_process,
                                       man_hours,
                                       labour_cost,
                                       Program._userInfo.User_Name);
                try
                {
                    OracleDaoHelper.executeSQLThrowExceptioin(sqlStr, theConnForTransaction);
                }
                catch (Exception ex)
                {
                    if (ex.ToString().Contains("ORA-00001"))
                    {
                        MessageBox.Show(firstWS.Index + "." + firstWS.Name + ": 第" + rowIndex + "行为重复工序,请检查成本表格,请重新导入 . ", "提示:", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        tran.Rollback();
                        theConnForTransaction.Close();
                        lblPromptForPB.Visible = false;
                        pb.Visible             = false;
                        myExcel.close();
                        return;
                    }
                    MessageBox.Show(firstWS.Index + "." + firstWS.Name + ": 第" + rowIndex + "行,数据导入时发生异常,请检查成本表格,重新导入。" + ex.ToString(), "提示:", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    tran.Rollback();
                    theConnForTransaction.Close();
                    lblPromptForPB.Visible = false;
                    pb.Visible             = false;
                    myExcel.close();
                    return;
                }
                lblPromptForPB.Visible = true;
                lblPromptForPB.Text    = firstWS.Index + "." + firstWS.Name + " 导入中: ";
                pb.Value++;
                continue;
            }
            tran.Commit();
            theConnForTransaction.Close();
            pb.Visible             = false;
            lblPromptForPB.Visible = false;
            lblPromptForPB.Text    = "";
            lblResult.Visible      = true;
            IShowProductsCostDetail showProductsCostDetailImpl = new ShowProductsCostDetailImpl();

            this.dgv.DataSource = showProductsCostDetailImpl.getProductsDetail(PN);
            DGVHelper.AutoSizeForDGV(dgv);

            myExcel.close();
            ShowResult.show(lblResult, "已经提交!", true);
            timerRestoreLblResult.Enabled = true;
            lblPromptForPB.Visible        = false;
            //默认提交 pictures下的 waiting.jpg图片。
            //保存或更新其对应的图片。
            if (!FrmProductsCostSummary.ifExistsRecordOfProducts_Picture(PN))
            {
                FrmProductsCostSummary.addPictureOfProduct(PN, System.Windows.Forms.Application.StartupPath + "\\pictures\\waiting.jpg");
            }
            else
            {
                FrmProductsCostSummary.updatePictureOfProduct(PN, System.Windows.Forms.Application.StartupPath + "\\pictures\\waiting.jpg");
            }
        }
        /// <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();
        }
        /// <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);
        }
        /// <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);
        }
예제 #27
0
        /// <summary>
        /// 导出到Excel
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnExport_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(cbPN.Text.Trim()))
            {
                MessageBox.Show("您未选中成衣名称!", "提示:", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            //加载图片
            cbPN_SelectedIndexChanged(sender, e);
            if (CmdHelper.ifExistsTheProcessByName("EXCEL") || CmdHelper.ifExistsTheProcessByName("et"))
            {
                Tools.FrmPrompt frmCloseExcel = new Tools.FrmPrompt("excel.exe|et.exe");
                frmCloseExcel.ShowDialog();
            }
            //导出到Excel中
            string _defaultDir = System.Windows.Forms.Application.StartupPath + "\\成本汇总";
            string _fileName   = cbPN.Text.Trim() + "_成本汇总.xls";

            xlsFilePath = FileNameDialog.getSaveFileNameWithDefaultDir("成衣汇总:", "*.xls|*.xls", _defaultDir, _fileName);
            if (!xlsFilePath.Contains(@"\"))
            {
                return;
            }
            //将图片存于粘贴板中。
            Clipboard.SetImage(this.pictureBox.Image);
            System.Data.DataTable dt = (System.Data.DataTable)dgv.DataSource;
            string PN = dt.Rows[0]["成衣名称"].ToString();

            ExcelHelper.saveDtToExcelWithProgressBar((System.Data.DataTable)dgv.DataSource, xlsFilePath, pb);
            MyExcel myExcel = new MyExcel(xlsFilePath);

            myExcel.open();
            Worksheet wS = myExcel.getFirstWorkSheetAfterOpen();


            //获取第二个Sheet.
            myExcel.App.Visible = false;
            Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(wS);
            int maxRowCount             = wS.UsedRange.Rows.Count;

            uEHelper.insertAboveTheSpecificRow(1);
            uEHelper.setSpecificCellValue("A1", PN);
            Range range = (wS.Range)[wS.Cells[2, 2], wS.Cells[maxRowCount, 2]];

            uEHelper.delTheSpecificContentShiftToLeft(range);
            myExcel.App.DisplayAlerts = false;
            uEHelper.merge("A1", "G1");
            uEHelper.MergeTheSpecificColumnWithoutBlankContent(2);

            //保存图片到I1.
            Range rangeI1 = uEHelper.getRange("I1", "I1");

            /*
             *    ActiveSheet.Range("A1").PasteSpecial(
             *   Excel.Enums.XlPasteType.xlPasteAll,
             *   Excel.Enums.Xl‌​PasteSpecialOperation.xlPasteSpecialOperationNone,
             *   false, false);
             */
            rangeI1.Select();
            wS.PasteSpecial();

            //sheet2中写入文件.
            IShowProductsCostSummary iShowProductsCostSummary = new ShowProductsCostSummaryImpl();

            dt = iShowProductsCostSummary.get_P_C_Each_Port(cbPN.Text.Trim());

            //序号, 成衣名称,  概要工序,  指定工序, 指定每部位工序, 工时, 件数, 单价, 最终单价.

            /*
             *  seq_p_c_record,
             *  product_name,
             *  summary_process,
             *  specific_process,
             *  specific_each_process,
             *  man_hours,
             *  amount,
             *  each_cost,
             *  final_labour_cost
             */
            //第二个表格.
            myExcel.AddSheetToLastIndex("每部件成本汇总");
            Worksheet secondWS = myExcel.getSecondWorksheetAfterOpen();

            //先写标题。
            secondWS.Cells[1, 1] = "序号";
            secondWS.Cells[1, 2] = "成衣名称";
            secondWS.Cells[1, 3] = "部位";
            secondWS.Cells[1, 4] = "工序";
            secondWS.Cells[1, 5] = "单部件";
            secondWS.Cells[1, 6] = "单部件_件数";
            //secondWS.Cells[1, 6] = "工时";
            secondWS.Cells[1, 7] = "单部件_工价";
            //secondWS.Cells[1, 7] = "件数";
            secondWS.Cells[1, 8] = "整部位_工时";
            //secondWS.Cells[1, 8] = "单价";
            secondWS.Cells[1, 9]  = "整部位_工价";
            secondWS.Cells[1, 10] = "最终_工价";

            for (int i = 0; i <= dt.Rows.Count - 1; i++)
            {
                secondWS.Cells[2 + i, 1]  = dt.Rows[i]["seq_p_c_record"].ToString();
                secondWS.Cells[2 + i, 2]  = dt.Rows[i]["product_name"].ToString();
                secondWS.Cells[2 + i, 3]  = dt.Rows[i]["summary_process"].ToString();
                secondWS.Cells[2 + i, 4]  = dt.Rows[i]["specific_process"].ToString();
                secondWS.Cells[2 + i, 5]  = dt.Rows[i]["specific_each_process"].ToString();
                secondWS.Cells[2 + i, 6]  = dt.Rows[i]["amount"].ToString();
                secondWS.Cells[2 + i, 7]  = dt.Rows[i]["each_cost"].ToString();
                secondWS.Cells[2 + i, 8]  = dt.Rows[i]["man_hours"].ToString();
                secondWS.Cells[2 + i, 9]  = dt.Rows[i]["labour_cost"].ToString();
                secondWS.Cells[2 + i, 10] = dt.Rows[i]["final_labour_cost"].ToString();
            }
            //获取最终单价的区域.
            Range _range_final_labour_cost;

            uEHelper = new Usual_Excel_Helper(secondWS);
            _range_final_labour_cost = uEHelper.getRange("J2", "J" + secondWS.UsedRange.Rows.Count);
            uEHelper.setFormulaR1C1ForRange(_range_final_labour_cost, "=IF(OR(ISBLANK(RC[-4]),ISBLANK(RC[-3])),RC[-1],RC[-4]*RC[-3])");

            secondWS.UsedRange.EntireColumn.AutoFit();
            myExcel.save();
            myExcel.close();
            //((FrmMainOfProductsCost)this.ParentForm).notifyIcon.ShowBalloonTip(7000, "提示:","汇总保存于: " + xlsFilePath, ToolTipIcon.Info);
            ShowResult.show(lblResult, "汇总保存于: " + xlsFilePath, true);
            timerRestoreLblResult.Start();
        }
예제 #28
0
        private void doNextAfterCheckName()
        {
            #region  打开4个考勤文件
            for (int i = 1; i <= xlsFilePathList.Count; i++)
            {
                switch (i)
                {
                case 1:
                    _1th_my_excel = new MyExcel(xlsFilePathList[0]);
                    _1th_my_excel.openWithoutAlerts();
                    _1th_Sheet = _1th_my_excel.getFirstWorkSheetAfterOpen();
                    break;

                case 2:
                    _2nd_my_excel = new MyExcel(xlsFilePathList[1]);
                    _2nd_my_excel.openWithoutAlerts();
                    _2nd_Sheet = _2nd_my_excel.getFirstWorkSheetAfterOpen();
                    break;

                case 3:
                    _3rd_my_excel = new MyExcel(xlsFilePathList[2]);
                    _3rd_my_excel.openWithoutAlerts();
                    _3rd_Sheet = _3rd_my_excel.getFirstWorkSheetAfterOpen();
                    break;

                case 4:
                    _4th_my_excel = new MyExcel(xlsFilePathList[3]);
                    _4th_my_excel.openWithoutAlerts();
                    _4th_Sheet = _4th_my_excel.getFirstWorkSheetAfterOpen();
                    break;
                }
            }
            #endregion
            #region 创建 _uncertain_myExcel;
            MyExcel uncertainRecordExcel = null;
            uncertainRecordExcel = new MyExcel(_uncertainWSPath);
            uncertainRecordExcel.create();
            uncertainRecordExcel.openWithoutAlerts();
            uncertainWS = uncertainRecordExcel.getFirstWorkSheetAfterOpen();
            //先写,日期行。
            Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(uncertainWS);
            uEHelper.writeToSpecificRow(1, 1, maxColIndexOfCheckedNameOfExcel);
            #endregion
            System.Data.DataTable dt = getSamePinYinButName();
            int  amountOfGroupOfSamePinYinButName = getAmountOfGroupOfSamePinYinButName();
            bool have_same_pinyin_flag            = false;
            if (dt != null && dt.Rows.Count > 0)
            {
                have_same_pinyin_flag = true;
            }
            //*************判断是否拼音相同 开始********************8
            if (have_same_pinyin_flag)
            {
                ShowResult.show(lblResult, "存在姓名拼音相同的记录!", false);
                this.lblPrompt.Visible           = false;
                timerRestoreTheLblResult.Enabled = true;
                #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:
                        //获取源区域
                        //替换源文件的工号为  工号位于第三列
                        _1th_Sheet.Cells[theRowIndex, 3] = "'111111111" + ((Range)(_1th_Sheet.Cells[theRowIndex, 3])).Text.ToString().PadLeft(3, '0');
                        srcRange = _1th_Sheet.Range[_1th_Sheet.Cells[theRowIndex, 1], _1th_Sheet.Cells[theRowIndex + 1, maxColIndexOfCheckedNameOfExcel]];
                        srcRange.Copy(Type.Missing);
                        //向目标复制。
                        //或取目标单元格。
                        currentRow = uncertainWS.UsedRange.Rows.Count;

                        destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndexOfCheckedNameOfExcel]];
                        //destRange.Select();
                        uncertainWS.Paste(destRange, false);
                        //保存一下。
                        break;

                    case 2:
                        _2nd_Sheet.Cells[theRowIndex, 3] = "'222222222" + ((Range)(_2nd_Sheet.Cells[theRowIndex, 3])).Text.ToString().PadLeft(3, '0');
                        srcRange = _2nd_Sheet.Range[_2nd_Sheet.Cells[theRowIndex, 1], _2nd_Sheet.Cells[theRowIndex + 1, maxColIndexOfCheckedNameOfExcel]];
                        srcRange.Cells.Copy(Type.Missing);
                        //向目标复制。
                        //或取目标单元格。
                        currentRow = uncertainWS.UsedRange.Rows.Count;

                        destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndexOfCheckedNameOfExcel]];
                        //destRange.Select();
                        uncertainWS.Paste(destRange, false);
                        break;

                    case 3:
                        _3rd_Sheet.Cells[theRowIndex, 3] = "'333333333" + ((Range)(_3rd_Sheet.Cells[theRowIndex, 3])).Text.ToString().PadLeft(3, '0');
                        srcRange = _3rd_Sheet.Range[_3rd_Sheet.Cells[theRowIndex, 1], _3rd_Sheet.Cells[theRowIndex + 1, maxColIndexOfCheckedNameOfExcel]];
                        srcRange.Cells.Copy(Type.Missing);
                        //向目标复制。
                        //或取目标单元格。
                        currentRow = uncertainWS.UsedRange.Rows.Count;
                        destRange  = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndexOfCheckedNameOfExcel]];
                        //destRange.Select();
                        uncertainWS.Paste(destRange, false);
                        break;

                    case 4:
                        _4th_Sheet.Cells[theRowIndex, 3] = "'444444444" + ((Range)(_4th_Sheet.Cells[theRowIndex, 3])).Text.ToString().PadLeft(3, '0');
                        srcRange = _4th_Sheet.Range[_4th_Sheet.Cells[theRowIndex, 1], _4th_Sheet.Cells[theRowIndex + 1, maxColIndexOfCheckedNameOfExcel]];
                        srcRange.Cells.Copy(Type.Missing);
                        //向目标复制。
                        //或取目标单元格。
                        currentRow = uncertainWS.UsedRange.Rows.Count;
                        destRange  = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndexOfCheckedNameOfExcel]];
                        //destRange.Select();
                        uncertainWS.Paste(destRange, false);
                        break;
                    }
                }
                //设置列宽
                uncertainWS.UsedRange.ColumnWidth = 3.75;
                //显示该文档对应的图片
                #endregion
                string promptStr = string.Format(@" 存在姓名拼音相同但书写不同的记录:{1}组;{0}
确定: 将视为不同员工;   取消: 取消本次导入;", "\r\n", amountOfGroupOfSamePinYinButName);
                if (DialogResult.Cancel.Equals(MessageBox.Show(promptStr, "提示:", MessageBoxButtons.OKCancel, MessageBoxIcon.Information)))
                {
                    closeThe4ARExcels();
                    uncertainWS.UsedRange.ColumnWidth = 3.75M;
                    uncertainRecordExcel.saveWithoutAutoFit();

                    uncertainRecordExcel.close();
                    //显示该文档。
                    uncertainRecordExcel = new MyExcel(_uncertainWSPath);
                    uncertainRecordExcel.open(true);
                    btnViewTheUncertaiRecordInExcel.Enabled = true;
                    return;
                }
                if (!btnViewTheUncertaiRecordInExcel.Enabled)
                {
                    btnViewTheUncertaiRecordInExcel.Enabled = true;
                }
            }

            //*************判断是否拼音相同  结束*****************88
            //1.h
            dt = getSameNameInfo();
            //获取汉字相同的组的数目。
            int    amountOfGroupOfSameName = getAmountOfGroupOfSameName();
            string prompt = string.Empty;
            if (dt.Rows.Count != 0)
            {
                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:
                        _1th_Sheet.Cells[theRowIndex, 3] = "'111111111" + ((Range)(_1th_Sheet.Cells[theRowIndex, 3])).Text.ToString().PadLeft(3, '0');
                        //获取源区域
                        srcRange = _1th_Sheet.Range[_1th_Sheet.Cells[theRowIndex, 1], _1th_Sheet.Cells[theRowIndex + 1, maxColIndexOfCheckedNameOfExcel]];
                        srcRange.Copy(Type.Missing);
                        //向目标复制。
                        //或取目标单元格。
                        currentRow = uncertainWS.UsedRange.Rows.Count;
                        destRange  = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndexOfCheckedNameOfExcel]];
                        //destRange.Select();
                        uncertainWS.Paste(destRange, false);
                        //保存一下。
                        break;

                    case 2:
                        _2nd_Sheet.Cells[theRowIndex, 3] = "'222222222" + ((Range)(_2nd_Sheet.Cells[theRowIndex, 3])).Text.ToString().PadLeft(3, '0');
                        srcRange = _2nd_Sheet.Range[_2nd_Sheet.Cells[theRowIndex, 1], _2nd_Sheet.Cells[theRowIndex + 1, maxColIndexOfCheckedNameOfExcel]];
                        srcRange.Cells.Copy(Type.Missing);
                        //向目标复制。
                        //或取目标单元格。
                        currentRow = uncertainWS.UsedRange.Rows.Count;
                        destRange  = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndexOfCheckedNameOfExcel]];
                        //destRange.Select();
                        uncertainWS.Paste(destRange, false);
                        break;

                    case 3:
                        _3rd_Sheet.Cells[theRowIndex, 3] = "'333333333" + ((Range)(_3rd_Sheet.Cells[theRowIndex, 3])).Text.ToString().PadLeft(3, '0');
                        srcRange = _3rd_Sheet.Range[_3rd_Sheet.Cells[theRowIndex, 1], _3rd_Sheet.Cells[theRowIndex + 1, maxColIndexOfCheckedNameOfExcel]];
                        srcRange.Cells.Copy(Type.Missing);
                        //向目标复制。
                        //或取目标单元格。
                        currentRow = uncertainWS.UsedRange.Rows.Count;
                        destRange  = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndexOfCheckedNameOfExcel]];
                        //destRange.Select();
                        uncertainWS.Paste(destRange, false);
                        break;

                    case 4:
                        _4th_Sheet.Cells[theRowIndex, 3] = "'444444444" + ((Range)(_4th_Sheet.Cells[theRowIndex, 3])).Text.ToString().PadLeft(3, '0');
                        srcRange = _4th_Sheet.Range[_4th_Sheet.Cells[theRowIndex, 1], _4th_Sheet.Cells[theRowIndex + 1, maxColIndexOfCheckedNameOfExcel]];
                        srcRange.Cells.Copy(Type.Missing);
                        //向目标复制。
                        //或取目标单元格。
                        currentRow = uncertainWS.UsedRange.Rows.Count;
                        destRange  = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndexOfCheckedNameOfExcel]];
                        //destRange.Select();
                        uncertainWS.Paste(destRange, false);
                        break;
                    }
                }
                #endregion
                prompt = string.Format(@"  存在同名的记录:{1}组;{0}
确定: 将视为同一员工;   取消: 取消本次导入;", "\r\n", amountOfGroupOfSameName);
                if (DialogResult.Cancel.Equals(MessageBox.Show(prompt, "提示:", MessageBoxButtons.OKCancel, MessageBoxIcon.Information)))
                {
                    uEHelper.setAllColumnsWidth(3.75M);
                    uncertainRecordExcel.saveWithoutAutoFit();
                    uncertainRecordExcel.close();
                    //显示该文档。
                    uncertainRecordExcel = new MyExcel(_uncertainWSPath);
                    uncertainRecordExcel.open(true);
                    closeThe4ARExcels();
                    if (!btnViewTheUncertaiRecordInExcel.Enabled)
                    {
                        btnViewTheUncertaiRecordInExcel.Enabled = true;
                    }
                    return;
                }
                if (!btnViewTheUncertaiRecordInExcel.Enabled)
                {
                    btnViewTheUncertaiRecordInExcel.Enabled = true;
                }
            }
            //关闭不确定文档。
            uEHelper.setAllColumnsWidth(3.75M);
            uncertainRecordExcel.saveWithoutAutoFit();
            uncertainRecordExcel.close();
            closeThe4ARExcels();

            xlsFilePathList.Sort();

            //直接删除
            MyExcel myExcel = new MyExcel(xlsFilePath);
            myExcel.openWithoutAlerts();
            Worksheet          firstSheet         = myExcel.getFirstWorkSheetAfterOpen();
            Usual_Excel_Helper uEHelperTemp       = new Usual_Excel_Helper(firstSheet);
            string             year_and_month_str = uEHelperTemp.getCellContentByRowAndColIndex(3, 3);
            year_and_month_str = year_and_month_str.Substring(0, 7);
            myExcel.close();
            delARDetailInfoByYearAndMonth(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;
            this.btnImportEmpsInfo.Enabled = false;
            readDataFromExcelBGWorker.RunWorkerAsync();
        }