C# Worksheet.get_Range方法代码示例

        private void button1_Click(object sender, EventArgs e)
        {
            MyApp = new Excel.Application();
            MyApp.Visible = false;
            MyBook = MyApp.Workbooks.Open(path);
            MySheet = (Excel.Worksheet)MyBook.Sheets[1];
            lastrow = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;

            BindingList<Dompet> DompetList = new BindingList<Dompet>();

            for (int index = 2; index <= lastrow; index++)
            {
                System.Array MyValues = 
                    (System.Array)MySheet.get_Range
                    ("A" + index.ToString(),"F" + index.ToString()).Cells.Value;
                DompetList.Add(new Dompet {
                    JmlPemesanan = MyValues.GetValue(1,1).ToString(),
                    JmlPekerja = MyValues.GetValue(1,2).ToString(),
                    Peralatan = MyValues.GetValue(1,3).ToString(),
                    JenisKulit = MyValues.GetValue(1,4).ToString(),
                    ModelDompet = MyValues.GetValue(1,5).ToString(),
                    Prediksi = MyValues.GetValue(1,6).ToString()
                });
            }
            dataGridView1.DataSource = (BindingList<Dompet>)DompetList;
            dataGridView1.AutoResizeColumns();
        }

        //private void wrtBySht(List<string> workList)
        private void wrtBySht(string shtName)
        {

            xlWorkSht = xlWorkBookTar.Worksheets.get_Item(shtName);
            xlWorkSht.Activate();

            string idx = Util.TaskInfo.TaskSetting.insertPtInstData; // start point index
            Excel.Range rng = xlWorkSht.get_Range(idx, idx);

            DataTable dt;
            if (shtName == "InstrumentClassData")
                dt = Util.DbConn.SqlTsk.GetTable("procGetInstData");
            else
                dt = Util.DbConn.SqlTsk.GetTable("procGetSymData " + shtName);


            int j = 1;
            int i = 0;
            foreach (DataRow row in dt.Rows)
            {
                for (i = 0; i < dt.Columns.Count; i++)
                {
                    rng[j + 1, i + 1].Value = row[i].ToString();

                }
                j++;
                if (j > dt.Rows.Count)
                {
                    break;
                }
            }
            rng[j + 1, 1].Value = "end";

        }

        private void drawGraph()
        {

            Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

            try
            {
                xlWorkBook = xlApp.Workbooks.Open("C:\\Users\\DELL\\Desktop\\ReadFromSerial_DrawGraphic\\csharp-Excel.xls", 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); //Get all the sheets in the workbook

                while (thread1.IsAlive)
                {
                    //son satır bulunuyor excel dosyasındaki
                    Excel.Range last = xlWorkSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
                    Excel.Range range = xlWorkSheet.get_Range("A1", last);
                    int lastUsedRow = last.Row;
                    int lastUsedColumn = last.Column;

                    string ReceiveData = myport.ReadLine(); // comdan degeri okuyuruz

                    // alınan degerdeki stringleri temizleyerek sadece double değeri yakalıyor
                    string[] HeatingData = ReceiveData.Split(':');
                    string[] HeatingData2 = HeatingData[1].Split('D');
                    var result = HeatingData2[0];
                    double heating = Convert.ToDouble(result);

                    theTime = DateTime.Now; // anlik olarak zamani ogreniyoruz!
                    string zaman = theTime.ToString("yyyy/MM/dd HH:mm:ss");

                    Thread.Sleep(1000); // ilk threadi anlik olarak durduruyor ve Invoke ile GUI threadini ulasip cizdiriyor! 
                    this.Invoke((MethodInvoker)delegate
                       {
                           chart1.Series["Series1"].Points.AddY(result);
                           // excel dosyasındaki son yazılan satırdan bir sonraki satıra sıcaklığı yazdırıyor
                           xlWorkSheet.Cells[lastUsedRow+1, 2] = (heating / 100);
                           xlWorkSheet.Cells[lastUsedRow + 1, 1] = zaman;
                       });
                }
            }
            catch
            {
                // MessageBox.Show("Dosya bulunamadı");
                xlWorkBook = xlApp.Workbooks.Add(misValue);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

                xlWorkSheet.Cells[1, 1] = "Zaman";
                xlWorkSheet.Cells[1, 2] = "Sıcaklık Celcius";

                xlWorkBook.SaveAs("C:\\Users\\DELL\\Desktop\\ReadFromSerial_DrawGraphic\\csharp-Excel.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                xlWorkBook.Close(true, misValue, misValue);
                xlApp.Quit();

                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);

                MessageBox.Show("Dosya oluşturuldu , proje klasörünüzde bulunmaktadır");
            }
        }

        private void btnFetch_Click(object sender, RibbonControlEventArgs e)
        {
            activeWorksheet = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet);
            firstRow = activeWorksheet.get_Range("A1");
            //firstRow.EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown);
            newFirstRow = activeWorksheet.get_Range("A1");

            try
            {
                firstRow.Value2 = "Fetching Data Please Wait...";
                string url = "http://localhost:5000/sample/getdata";
                WebClient wc = new WebClient();
                wc.DownloadStringCompleted += HttpsCompleted;
                wc.DownloadStringAsync(new Uri(url));

            }
            catch(Exception ex)
            {
                firstRow.Value2 = "An Error occured while accessing the service";
            }
        }

       public void WriteTest()
       {
           xlBook = xlApp.ActiveWorkbook;
           xlSheet = xlBook.Worksheets.get_Item(1);
           Excel.Range last = xlSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
           Excel.Range range = xlSheet.get_Range("A1", last);
           int lastUsedRow = last.Row + 1;
           int lastUsedColumn = last.Column + 1;
           Console.WriteLine("Rows used {0}  Columns used {1}",lastUsedRow,lastUsedColumn );

           for(int row = 1;row<10;row++)
               for (int col = 1; col < 10; col++)
           xlSheet.Cells[row, col] = "Test col:"+col+" row:"+row ;
          

       
       }

        private xlTradeOrder ReadLastOrder()
        {
            xlBook = xlApp.ActiveWorkbook;
            xlSheet = xlBook.Worksheets.get_Item(2);
            Excel.Range last = xlSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
            Excel.Range range = xlSheet.get_Range("A1", last);
            int lastUsedRow = last.Row;
            int lastUsedColumn = last.Column + 1;
            Debug.WriteLine("Rows used " + lastUsedRow);
            var Order = new xlTradeOrder();

            Order.TimeStamp = DateTime.UtcNow.AddHours(2);
            Order.Contract = xlSheet.Cells[lastUsedRow, 1].Value;
            Order.BS = (xlSheet.Cells[lastUsedRow, 2].Value == "B") ? Trade.BuySell.Buy : Trade.BuySell.Sell;
            Order.Volume = (int)xlSheet.Cells[lastUsedRow, 3].Value;
            Order.Price = (long)xlSheet.Cells[lastUsedRow, 4].Value;
            Order.Status = StringToOrder(xlSheet.Cells[lastUsedRow, 12].Value);
            Order.GetReference();
            return Order;
        }

        public void WriteOrder(xlTradeOrder Order)
        {
            xlBook = xlApp.ActiveWorkbook;
            xlSheet = xlBook.Worksheets.get_Item(2);
            Excel.Range last = xlSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
            Excel.Range range = xlSheet.get_Range("A1", last);
            int lastUsedRow = last.Row + 1;
            int lastUsedColumn = last.Column + 1;
            //Debug.WriteLine("Rows used " + lastUsedRow);

            xlSheet.Cells[lastUsedRow, 1] = Order.Contract;
            xlSheet.Cells[lastUsedRow, 2] = (Order.BS == Trade.BuySell.Buy) ? "B" : "S";
            xlSheet.Cells[lastUsedRow, 3] = Order.Volume;
            xlSheet.Cells[lastUsedRow, 4] = Order.Price;
            xlSheet.Cells[lastUsedRow, 5] = Order.Principle;
            xlSheet.Cells[lastUsedRow, 6] = Order.Dealer;
            xlSheet.Cells[lastUsedRow, 9] = Order.Member;
            xlSheet.Cells[lastUsedRow, 10] = Order.Type;
            xlSheet.Cells[lastUsedRow, 11] = Order.Exchange;
            xlSheet.Cells[lastUsedRow, 12] = OrderToString(xlTradeOrder.orderStatus.Ready);

            _lastOrderMatched = false;
        }

       public List<string> ReadTest()
       {
           xlBook = xlApp.ActiveWorkbook;
           xlSheet = xlBook.Worksheets.get_Item(1);
           Excel.Range last = xlSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
           Excel.Range range = xlSheet.get_Range("A1", last);
           int lastUsedRow = last.Row;
           int lastUsedColumn = last.Column + 1;
           Console.WriteLine("Rows used {0}  Columns used {1}", lastUsedRow, lastUsedColumn);

           var lines = new List<string>();
           for (int row = 1; row <lastUsedRow; row++)
           {
               
               var cols = new StringBuilder();
               for (int col = 1; col < lastUsedColumn; col++)
               {                  
                   cols.Append(xlSheet.Cells[row, col].Value+",");
               }
               lines.Add(cols.ToString());
           }
           return lines;
       }

        //Obtiene el archivo y comienza a procesarlo
        private void procesarBtn_Click(object sender, EventArgs e)
        {
            //Crea el documento
            XmlDocument doc = new XmlDocument();
            //Crea la raiz y le da formato
            XmlElement rootElement = (XmlElement)doc.AppendChild(doc.CreateElement("informacionAFIP"));
            rootElement.SetAttribute("xmlns:xsi", "http://www.w3.org/2001/XMLSchema-instance");

            //Agrega el periodo de información
            DateTime dt = Convert.ToDateTime(dateText.Text);
            rootElement.AppendChild(doc.CreateElement("periodoInformacion")).InnerText = dt.ToString("yyyyMMdd");

            //Agrega el rut y dígito de la entidad
            rootElement.AppendChild(doc.CreateElement("rutEntidad")).InnerText = rutText.Text;
            rootElement.AppendChild(doc.CreateElement("digitoEntidad")).InnerText = dvText.Text;

            //Agrega los fondos como elemento
            XmlElement fondosRootElement = (XmlElement)rootElement.AppendChild(doc.CreateElement("fondos"));

            //Procesa el archivo Excel para
            //1) Crear los fondos de inversión privados
            //2) Agregar aportantes a esos fondos
            this.MyApp = new Excel.Application();
            this.MyApp.Visible = false;
            this.MyBook = MyApp.Workbooks.Open(archivoText.Text);
            this.MySheet = (Excel.Worksheet)MyBook.Sheets[1];

            int lastRow = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;

            //El primer proceso es para crear un list con los fondos de inversión privada
            for (int index = 2; index <= lastRow - 1; index++)
            {
                //Obtiene la fila
                System.Array MyValues = (System.Array)MySheet.get_Range("A" + index.ToString(), "R" + index.ToString()).Cells.Value;

                //Obtiene los datos del Excel
                string fipRut = MyValues.GetValue(1, 4).ToString();
                string digitoFondo = MyValues.GetValue(1, 5).ToString();
                string denominacionFondo = MyValues.GetValue(1, 6).ToString();
                string valorActivos = MyValues.GetValue(1, 7).ToString();
                string valorPasivos = MyValues.GetValue(1, 8).ToString();
                string criteriosContables = MyValues.GetValue(1, 9).ToString();

                //Comprueba si existe el FIP para agregarlo a la lista
                if (!fipList.Any(obj => obj.rutFondo == fipRut))
                {
                    fipList.Add(
                        new Fip
                        {
                            rutFondo = fipRut,
                            digitoFondo = digitoFondo,
                            denominacionFondo = denominacionFondo,
                            valorActivos = valorActivos,
                            valorPasivos = valorPasivos,
                            criteriosContables = criteriosContables
                        }
                    );
                }

            }
            //El segundo proceso es para agregar aportantes a la lista de fondos de inversión privada
            foreach(Fip fip in fipList)
            {
                //Agrega los fondos de inversión al XML
                XmlElement newFipElement = (XmlElement)fondosRootElement.AppendChild(doc.CreateElement("fondoInversionPrivado"));
                //Agrega el contenido de esos elementos
                newFipElement.AppendChild(doc.CreateElement("rutFondo")).InnerText = fip.rutFondo;
                newFipElement.AppendChild(doc.CreateElement("digitoFondo")).InnerText = fip.digitoFondo;
                newFipElement.AppendChild(doc.CreateElement("denominacionFondo")).InnerText = fip.denominacionFondo;
                newFipElement.AppendChild(doc.CreateElement("valorActivos")).InnerText = fip.valorActivos;
                newFipElement.AppendChild(doc.CreateElement("valorPasivos")).InnerText = fip.valorPasivos;
                newFipElement.AppendChild(doc.CreateElement("criteriosContables")).InnerText = fip.criteriosContables;

                //Agrega un nodo nuevo para los participes
                newFipElement.AppendChild(doc.CreateElement("participes"));
            }

            //El tercer proceso es para agregar a todos los aportantes a los fondos de inversión privada
            XmlNodeList fipNodeList = doc.SelectNodes("informacionAFIP/fondos/fondoInversionPrivado");
            foreach (XmlNode fipNode in fipNodeList)
            {
                //Recorre todos los registros nuevamente para agregarlos al fondo correspondiente
                for (int index = 2; index <= lastRow - 1; index++)
                {
                    //Obtiene la fila
                    System.Array MyValues = (System.Array)MySheet.get_Range("A" + index.ToString(), "R" + index.ToString()).Cells.Value;
                    //Si es igual lo agrega
                    if (fipNode["rutFondo"].InnerText == MyValues.GetValue(1, 4).ToString())
                    {
                        //Valores null
                        string apellidoPaterno;
                        string apellidoMaterno;
                        string primerNombre;
                        string segundoNombre;

                        if (MyValues.GetValue(1, 13) == null){ apellidoPaterno = "";}else{ apellidoPaterno = MyValues.GetValue(1, 13).ToString();}
                        if (MyValues.GetValue(1, 14) == null) { apellidoMaterno = ""; } else {  apellidoMaterno = MyValues.GetValue(1, 14).ToString(); }
                        if (MyValues.GetValue(1, 15) == null) { primerNombre = ""; } else { primerNombre = MyValues.GetValue(1, 15).ToString(); }
                        if (MyValues.GetValue(1, 16) == null) { segundoNombre = ""; } else { segundoNombre = MyValues.GetValue(1, 16).ToString(); }
//.........这里部分代码省略.........

        public string[,] CzytajDane()
        {
            m_xlApp = new Excel.Application();
            m_xlApp.DisplayAlerts = false;

            m_xlWorkbook = m_xlApp.Workbooks.Open(m_xlFileName,
                m_xx, m_xx, m_xx, m_xx, m_xx, m_xx, m_xx,
                m_xx, m_xx, m_xx, m_xx, m_xx, m_xx, m_xx);

            m_xlWorksheet = (Excel.Worksheet)m_xlWorkbook.Worksheets[1];   // 0 wskazuje na pierwszy arkusz

            string startCell = "A1";  // zakres danych do wczytania
            string endCell = "F6";
            m_projectRange = m_xlWorksheet.get_Range(startCell, endCell);

            Array projectCells = (Array)m_projectRange.Cells.Value2;

            int col = m_projectRange.Columns.Count;
            int row = m_projectRange.Rows.Count;
            string[,] tab1 = new string[col, row];
            for (int i = 0; i < col; i++)
            {
                for (int j = 0; j < row; j++)
                {
                    tab1[i, j] = " " + projectCells.GetValue(i + 1, j + 1);
                }
            }
            m_xlApp.Quit();

            Console.Write("Wczytana tablica z pliku Excela   BIJACZ \n");

            for (int i = 0; i < col; i++)
            {
                for (int j = 0; j < row; j++)
                {
                    Console.Write(tab1[i, j] + "\t");
                }
                Console.WriteLine();
            }

            return tab1;
        }

        /// <summary>
        /// 根据10月数据import
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static List<OTItem> LoadOTOctober(string filePath)
        {
            List<OTItem> ots = new List<OTItem>();
               int sheetNo =1;
               if (MyApp == null) MyApp=new Microsoft.Office.Interop.Excel.Application();
               MyApp.Visible = false;
               MyBook = MyApp.Workbooks.Open(filePath);
               MySheet = (Worksheet)MyBook.Sheets[sheetNo]; // Explict cast is not required here
               int lastRow = MySheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell).Row;
               for (int index = 4; index <= lastRow; index++)
               {
               System.Array MyValues = (System.Array)MySheet.get_Range("A" + index.ToString(), "P" + index.ToString()).Cells.Value;
               if ((MyValues.GetValue(1, 1) == null && MyValues.GetValue(1, 2) == null) || MyValues.GetValue(1, 1).ToString().Trim()=="")
               {
                   Console.WriteLine("OTItem no value on row " + index.ToString());
                   WriteErrorOLEDB(MyValues, string.Format("原{0},数据不完整",index),true);
                   continue;
               }
               RowIndex = 1;

               ots.Add(new OTItem
               {
                   OriginalRow=index,
                   OT_ApplyNumber = "",
                   Worker_Number = GetValue(MyValues,1), //A
                   Worker_CnName = GetValue(MyValues,2), //B
                   Worker_Dept = GetValue(MyValues,3), //C
                   Worker_Group = GetValue(MyValues,4), //D

                   Cycle_StartEd = GetValue(MyValues,5), //E
                   Cycle_EndEd = GetValue(MyValues,6), //F
                   Create_Ed = GetValue(MyValues,7), //G

                   OT_StartEd = GetValue(MyValues,8), //H
                   OT_StartTime = GetValue(MyValues,9,true), //I
                   OT_EndEd = GetValue(MyValues,8), //H
                   OT_EndTime = GetValue(MyValues,10,true),//J

                   Statistic_Date = "",
                   OT_Hours = GetValue(MyValues,11),//K
                   Pay_Hours = GetValue(MyValues,12),//L
                   Offset_Hours = GetValue(MyValues,13), //M
                   LeftOffset_Hour = "",
                   LeftChange_Hour = "",

                   //需要为导入数据确定一个加班种类,the value is -1
                   OT_WorkType ="-1",
                   Reason = GetValue(MyValues,14), //N
                   Comment = "",

                   App1 = "",
                   App2 = "",

                   //审批状态 固定为“审批完成”旧数据请考虑都走完审批再导入到新系统
                   Status = "审批完成",
                   Apply_Type ="",
                   //加班号,唯一标识一次加班的号码 "OD"
                   OT_Number ="",
                   Attendance_StartEd = "",
                   Attendance_EndEd = "",

                   Shift_Id = GetValue(MyValues,15), //o
                   Compensate_Rate = GetValue(MyValues,16) //P
               });

               }

               return ots;
        }

        public static List<OTItem> LoadOT(string filePath)
        {
            List<OTItem> ots = new List<OTItem>();
               int sheetNo = 2;

               MyApp.Visible = false;

               MyBook = MyApp.Workbooks.Open(filePath);
               MySheet = (Worksheet)MyBook.Sheets[sheetNo]; // Explict cast is not required here
               int lastRow = MySheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell).Row;
               for (int index = 1; index <= lastRow; index++)
               {
               System.Array MyValues = (System.Array)MySheet.get_Range("A" + index.ToString(), "AD" + index.ToString()).Cells.Value;
               if (MyValues.GetValue(1, 2) == null && MyValues.GetValue(1, 3)==null)
               {
                   Console.WriteLine("OTItem no value on row " + index.ToString());
                   continue;
               }
               RowIndex = 1;

               ots.Add(new OTItem
               {
                   OT_ApplyNumber = GetValue(MyValues),
                   Worker_Number = GetValue(MyValues),
                   Worker_CnName = GetValue(MyValues),
                   Worker_Dept = GetValue(MyValues),
                   Worker_Group = GetValue(MyValues),

                   Cycle_StartEd = GetValue(MyValues),
                   Cycle_EndEd = GetValue(MyValues),
                   Create_Ed = GetValue(MyValues),

                   OT_StartEd = GetValue(MyValues),
                   OT_StartTime = GetValue(MyValues),
                   OT_EndEd = GetValue(MyValues),
                   OT_EndTime = GetValue(MyValues),

                   Statistic_Date = GetValue(MyValues),
                   OT_Hours = GetValue(MyValues),
                   Pay_Hours = GetValue(MyValues),
                   Offset_Hours = GetValue(MyValues),
                   LeftOffset_Hour = GetValue(MyValues),
                   LeftChange_Hour = GetValue(MyValues),

                   //需要为导入数据确定一个加班种类,the value is -1
                   OT_WorkType = GetValue(MyValues),
                   Reason = GetValue(MyValues),
                   Comment = GetValue(MyValues),

                   App1 = GetValue(MyValues),
                   App2 = GetValue(MyValues),

                   //审批状态 固定为“审批完成”旧数据请考虑都走完审批再导入到新系统
                   Status = GetValue(MyValues),
                   Apply_Type = GetValue(MyValues),
                   //加班号,唯一标识一次加班的号码
                   OT_Number = GetValue(MyValues),
                   Attendance_StartEd = GetValue(MyValues),
                   Attendance_EndEd = GetValue(MyValues),

                   Shift_Id = GetValue(MyValues),
                   Compensate_Rate = GetValue(MyValues)
               });

               }

               return ots;
        }

        /// <summary>
        /// 根据10月数据
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static List<OffsetItem> LoadOffsetOctober(string filePath)
        {
            List<OffsetItem> ots = new List<OffsetItem>();
               int sheetNo = 1;
               if (MyApp == null) MyApp = new Microsoft.Office.Interop.Excel.Application();
               ExcelTool.MyApp.Visible = false;

               ExcelTool.MyBook = MyApp.Workbooks.Open(filePath);
               ExcelTool.MySheet = (Worksheet)MyBook.Sheets[sheetNo]; // Explict cast is not required here
               int lastRow = MySheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell).Row;
               for (int index = 2; index <= lastRow; index++)
               {
               System.Array MyValues = (System.Array)MySheet.get_Range("A" + index.ToString(), "V" + index.ToString()).Cells.Value;
               if (MyValues.GetValue(1, 2) == null && MyValues.GetValue(1, 3) == null)
               {
                   Console.WriteLine("OffsetItem no value on row " + index.ToString());
                   WriteErrorOLEDB(MyValues, string.Format("原{0},数据不完整",index), false);
                   continue;
               }

               ots.Add(new OffsetItem
                {
                    //根据10月数据import
                    OriginalRow=index,
                    Apply_Number = "",
                    Worker_Number = GetValue(MyValues,2), //B
                    Worker_CnName = GetValue(MyValues,3), //C
                    Worker_Dept = GetValue(MyValues, 19), //S
                    Worker_Group = GetValue(MyValues,1), //A
                    CreateEd = GetValue(MyValues,13), //M column
                    Offset_StartEd = GetValue(MyValues,13), //M
                    Offset_StartTime = GetValue(MyValues,14),//N
                    Offset_EndEd = GetValue(MyValues,15), //M
                    Offset_EndTime = GetValue(MyValues,16),//P
                    RemoveOffset_Hours = GetValue(MyValues, 12), //L
                    Offset_Hours = GetValue(MyValues,18), //R
                    Offset_Days = "0",
                    App1 = "",
                    App2 = "",

                    OT_Numbers = "", //no value

                    //如果没有，可以为空，系统导入时自动生成
                    Offset_Number =GetValue(MyValues,11), //K column

                    Shift_Id=GetValue(MyValues,4),//D column
                    OT_WorkEd=GetValue(MyValues,5),//E
                    OT_Work_StartTime = GetValue(MyValues, 6), //F
                    OT_Work_EndTime=GetValue(MyValues,7), //G
                    OT_Work_Comment=GetValue(MyValues,8), //H
                    OT_Hours = GetValue(MyValues, 9), //I
                    OT_CompensateRate = GetValue(MyValues, 10), //J
                    Status = GetValue(MyValues,17) //Q column,已获批准 待审 草稿
                });

               }

               return ots;
        }

        /// <summary>
        /// 复制列（在指定WorkSheet指定列右边复制指定数量列）
        /// </summary>
        /// <param name="sheetIndex"></param>
        /// <param name="columnIndex"></param>
        /// <param name="count"></param>
        public void CopyColumns(int sheetIndex, int columnIndex, int count)
        {
            if (sheetIndex > this.WorkSheetCount)
            {
                this.KillExcelProcess();
                throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
            }

            try
            {
                workSheet = (Excel.Worksheet)workBook.Worksheets[sheetIndex];
                //    range1 = (Excel.Range)workSheet.Columns[Type.Missing,columnIndex];
                range1 = (Excel.Range)workSheet.get_Range(this.IntToLetter(columnIndex) + "1", this.IntToLetter(columnIndex) + "10000");

                for (int i = 1; i <= count; i++)
                {
                    //     range2 = (Excel.Range)workSheet.Columns[Type.Missing,columnIndex + i];
                    range2 = (Excel.Range)workSheet.get_Range(this.IntToLetter(columnIndex + i) + "1", this.IntToLetter(columnIndex + i) + "10000");
                    range1.Copy(range2);
                }
            }
            catch (Exception e)
            {
                this.KillExcelProcess();
                throw e;
            }
        }

        private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
        {
            var Oldcul = System.Threading.Thread.CurrentThread.CurrentCulture;

            System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
            System.Threading.Thread.CurrentThread.CurrentCulture.DateTimeFormat = Oldcul.DateTimeFormat;

            DataGridView DgvResult = dgvSummarySaleMonthProduct;
            DataGridView DgvResult2 = dgvSummarySaleMonthProductMonth;
            misValue = System.Reflection.Missing.Value;
            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            try
            {
                int c = DgvResult.Columns.Count;
                int r = DgvResult.Rows.Count;
                for (int i = 0; i < c; i++)
                {
                    xlWorkSheet.Cells[1, i + 1] = DgvResult.Columns[i].HeaderText.ToString();
                }

                for (int i = 0; i < r; i++)
                {
                    if (backgroundWorker1.CancellationPending)
                    {
                        e.Cancel = true;
                    }
                    else
                    {
                        for (int j = 0; j < c; j++)
                        {
                            xlWorkSheet.Cells[i + 2, j + 1] = DgvResult.Rows[i].Cells[j].Value.ToString();
                        }
                        backgroundWorker1.ReportProgress(98 * i / (r - 1));
                    }
                }
                c = DgvResult2.Columns.Count;
                xlWorkSheet.Cells[r + 2, 1] = DgvResult2.Rows[0].Cells[0].Value.ToString();
                for (int j = 1; j < c; j++)
                {
                    xlWorkSheet.Cells[r + 2, j + 2] = DgvResult2.Rows[0].Cells[j].Value.ToString();
                }
                backgroundWorker1.ReportProgress(99);
                if (backgroundWorker1.CancellationPending)
                {
                    e.Cancel = true;
                    backgroundWorker1.ReportProgress(0);
                }
                else
                {
                    xlWorkSheet.get_Range(xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, c + 1]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.PeachPuff);
                    xlWorkSheet.get_Range(xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, c + 1]).Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
                    xlWorkSheet.get_Range(xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, c + 1]).Borders.Weight = 2d;

                    xlWorkSheet.get_Range(xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[r + 1 + 1, c + 1]).Font.Name = "Arial";
                    xlWorkSheet.get_Range(xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[r + 1 + 1, c + 1]).Font.Size = 10;

                    xlWorkSheet.get_Range(xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[r + 1 + 1, c + 1]).EntireColumn.AutoFit();

                    xlWorkSheet.get_Range(xlWorkSheet.Cells[r + 1 + 1, 1], xlWorkSheet.Cells[r + 1 + 1, 2]).Merge(Type.Missing);

                    xlWorkSheet.get_Range(xlWorkSheet.Cells[r + 1 + 1, 1], xlWorkSheet.Cells[r + 1 + 1, c + 1]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.PeachPuff);
                    xlWorkSheet.get_Range(xlWorkSheet.Cells[r + 1 + 1, 1], xlWorkSheet.Cells[r + 1 + 1, c + 1]).Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
                    xlWorkSheet.get_Range(xlWorkSheet.Cells[r + 1 + 1, 1], xlWorkSheet.Cells[r + 1 + 1, c + 1]).Borders.Weight = 2d;
                    /////////// End Create file Excel ///////////

                }
            }
            catch
            {
                MessageBox.Show("ผิดพลาด");
                e.Cancel = true;
            }
            finally
            {
                System.Threading.Thread.CurrentThread.CurrentCulture = Oldcul;
                //this.Cursor = Cursors.Default;
            }
        }