示例#1
0
 public bool InsertRowofManufacture(FingerData fingerData)
 {
     try
     {
         string sqlQuerry = "";
         sqlQuerry += "insert into m_FingerData (Dept, IDName,Name, DateTime, TimeIn, TimeOut,DayWorkingTime,NightWorkingtime,ApproveTimeAbsent,Update_datetime ) values( '";
         sqlQuerry += fingerData.Dept + "', '" + fingerData.ID + "', '" + fingerData.Name + "', '" + fingerData.DateTime + "', '" + fingerData.TimeIn + "', '" + fingerData.TimeOut + "', '" + fingerData.DayWorkingTime + "', '" + fingerData.NightWorkingTime + "', '" + fingerData.AproveTimeabsent + "',GETDATE() )";
         sqlCON sqlCON = new sqlCON();
         return(sqlCON.sqlExecuteNonQuery(sqlQuerry, false));
     }
     catch (Exception ex)
     {
         return(false);
     }
 }
        public List <FingerData> GetFingerDatas(string pathExcelFile)
        {
            List <FingerData> specialLists = new List <FingerData>();

            try
            {
                //Create COM Objects. Create a COM object for everything that is referenced
                Excel.Application xlApp       = new Excel.Application();
                Excel.Workbook    xlWorkbook  = xlApp.Workbooks.Open(pathExcelFile);
                Excel._Worksheet  xlWorksheet = xlWorkbook.Sheets[1];
                Excel.Range       xlRange     = xlWorksheet.UsedRange;

                int rowCount = xlRange.Rows.Count;
                int colCount = xlRange.Columns.Count;

                //iterate over the rows and columns and print to the console as it appears in the file
                //excel is not zero based!!
                for (int i = 5; i <= rowCount; i++)
                {
                    FingerData data = new FingerData();
                    data.Dept = (xlRange.Cells[i, 3].Value != null) ? xlRange.Cells[i, 3].Value.ToString() :"";
                    data.ID   = (xlRange.Cells[i, 4].Value != null) ? xlRange.Cells[i, 4].Value.ToString():"";
                    data.Name = (xlRange.Cells[i, 5].Value != null) ? xlRange.Cells[i, 5].Value.ToString():"";
                    try
                    {
                        data.DateTime = (xlRange.Cells[i, 6].Value != null) ? xlRange.Cells[i, 6].Value.ToString() : "";
                    }
                    catch (Exception)
                    {
                        data.DateTime = "";
                    }
                    //  data.DateTime = (xlRange.Cells[i, 6].Value != null) ?(DateTime) xlRange.Cells[i, 6].Value: DateTime.MinValue;
                    data.TimeIn           = (xlRange.Cells[i, 9].Value != null) ? xlRange.Cells[i, 9].Value.ToString():"";
                    data.TimeOut          = (xlRange.Cells[i, 10].Value != null) ? xlRange.Cells[i, 10].Value.ToString():"";
                    data.DayWorkingTime   = (xlRange.Cells[i, 13].Value != null) ? xlRange.Cells[i, 13].Value.ToString():"";
                    data.NightWorkingTime = (xlRange.Cells[i, 14].Value != null) ? xlRange.Cells[i, 14].Value.ToString():"";
                    data.AproveTimeabsent = (xlRange.Cells[i, 17].Value != null) ? xlRange.Cells[i, 17].Value.ToString():"";

                    if (data.ID.Trim() != "")
                    {
                        DataControl dataControl = new DataControl();
                        dataControl.InsertRowofManufacture(data);
                        specialLists.Add(data);
                    }
                    else
                    {
                        ;
                    }
                }

                //cleanup
                GC.Collect();
                GC.WaitForPendingFinalizers();

                //rule of thumb for releasing com objects:
                //  never use two dots, all COM objects must be referenced and released individually
                //  ex: [somthing].[something].[something] is bad

                //release com objects to fully kill excel process from running in the background
                Marshal.ReleaseComObject(xlRange);
                Marshal.ReleaseComObject(xlWorksheet);

                //close and release
                xlWorkbook.Close();
                Marshal.ReleaseComObject(xlWorkbook);

                //quit and release
                xlApp.Quit();
                Marshal.ReleaseComObject(xlApp);
            }
            catch (Exception ex)
            {
                Logfile.Output(StatusLog.Error, "List<FingerData> GetFingerDatas(string pathExcelFile)", ex.Message);
            }
            return(specialLists);
        }