C# Worksheet.Select方法代码示例

        private Worksheet objExcelWorkSheet; //定义Workbook工作表对象

        #endregion Fields

        #region Methods

        // Change the Mapping WorkFlow  @2015-7-16 By CC
        public void EditWorkFlow(string StrTestData)
        {
            objExcelApp = new Microsoft.Office.Interop.Excel.Application();
            objExcelWorkBooks = objExcelApp.Workbooks;
            objExcelWorkbook = objExcelWorkBooks.Open(StrTestData, 0, false, 5, "", "", true,
            Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);

            objExcelWorkSheet = (Worksheet)objExcelWorkbook.Worksheets["WorkFlow"]; //strSheetName is the Sheet Name of Exce，if there is no name then Defult is "1"
            objExcelWorkSheet.Select(Type.Missing);
            int rowsint = objExcelWorkSheet.UsedRange.Cells.Rows.Count; //get the Row Number
            int columnsint = objExcelWorkSheet.UsedRange.Cells.Columns.Count;//get the column number
            int rowNo = 0;
            int ParameterRowNo = 0;
            int columnNo = 0;
            int FlagColumnNo = 0;
            for (int j = 1; j <= columnsint; j++)
            {
                string strColumnsName = ((Range)objExcelWorkSheet.Cells[1, j]).Text.ToString();
                if (strColumnsName == "Flag")
                {
                    FlagColumnNo = j;
                }
                if (strColumnsName == "BusinessFlow")
                {
                    rowNo = j;
                }
                if (strColumnsName == "ParameterValue")
                {
                    ParameterRowNo = j;
                }
            }
            for (int i = 2; i <= rowsint; i++)
            {
                string strFlagValue = ((Range)objExcelWorkSheet.Cells[i, FlagColumnNo]).Text.ToString();
                if (strFlagValue =="Y")
                {
                    int RowNO = i;
                    objExcelWorkSheet.Cells[RowNO, FlagColumnNo] = "N";
                }
               string strBusinessFlow = ((Range)objExcelWorkSheet.Cells[i, rowNo]).Text.ToString();
               if (strBusinessFlow == "HeaderMapping" || strBusinessFlow == "TaxMapping" || strBusinessFlow == "LineItemsMapping")
               {
                 columnNo = i;
                 string strParameterValue = ((Range)objExcelWorkSheet.Cells[i, ParameterRowNo]).Text.ToString();
                 if (strParameterValue == "IR" || strParameterValue == "TradeBilling" || strParameterValue == "TradeCredit" || strParameterValue == "TradeDebit" || strParameterValue == "TradeReturn")
                  {
                    objExcelWorkSheet.Cells[i, 1] = "Y";
                  }
                }
            }
            objExcelWorkbook.Save();
            objExcelWorkbook.Close(false, StrTestData, false);
            objExcelApp.Quit();
            NAR(objExcelApp);
            NAR(objExcelWorkbook);
            NAR(objExcelWorkSheet);
        }

        static void Main(string[] args)
        {
            copyExcellTo = System.Configuration.ConfigurationManager.AppSettings["copyExcellTo"];
            savePDFTo = System.Configuration.ConfigurationManager.AppSettings["savePDFTo"];

            Console.WriteLine("start job: " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToShortTimeString());
            List<FilePath> filePaths = new List<FilePath>();
            FilePath filePath = new FilePath();

            LogWithoutPath("begin trying to get current id");
            documentID = GetCurrentID();
            LogWithoutPath("current id: " + documentID);
            LogWithoutPath("end trying to get current id");


            //List<HL7Data> hL7Data = new List<HL7Data>();
            using (IDbConnection db = new SqlConnection(databaseConnection))
            {
                try
                {
                    //string query = "select "
                    //string query = "select top 10000"

                    //string query = "select [ documentid] as DocumentID,documentpath as DocumentPath from donedocuments4";


                    //string query = "select documentid as DocumentID,documentpath as DocumentPath from documentbackup"
                    //    + " where documentid not in ("
                    //    + " select documentid from DOCUMENTS "
                    //    + " where DOCUMENTS.DOCUMENTTYPEID = 'xls'"
                    //    + " and DOCUMENTS.ISDELETED = '0'"
                    //    + " and DOCUMENTS.DOCUMENTGROUPID <> 2)";

                    string query = "select "
                        //D:\SRSServer\Storage
                        + " documentid as DocumentID,'D:\\SRSServer\\Storage\\' + LEFT(CAST(DOCUMENTS.PERSONID AS int),2) + '\\' + STR(DOCUMENTS.PERSONID,10,0) + '\\' + STR(DOCUMENTS.DOCUMENTID,10,0) as DocumentPath"
                        //+ " documentid as DocumentID,'\\\\hmdn-srseds\\SRSServer\\Storage\\' + LEFT(CAST(DOCUMENTS.PERSONID AS int),2) + '\\' + STR(DOCUMENTS.PERSONID,10,0) + '\\' + STR(DOCUMENTS.DOCUMENTID,10,0) as DocumentPath"
                        + " from DOCUMENTS "
                        + " join PERSON on DOCUMENTS.PERSONID = PERSON.PERSONID"
                        + " join DOCUMENTGROUP on DOCUMENTS.DOCUMENTGROUPID = DOCUMENTGROUP.DOCUMENTGROUPID"
                        + " where DOCUMENTS.DOCUMENTTYPEID = 'xls'"
                        + " and DOCUMENTS.ISDELETED = '0'"
                        + " and DOCUMENTS.DOCUMENTGROUPID <> 2"
                        //+ " and documentID > 2004108635"
                        + "   and DOCUMENTS.DOCUMENTID > 2004460939"
                        //+ "   and documentID > 2004427839"
                        //+ documentID
                        + " order by DOCUMENTS.DOCUMENTID";
                    //+ " order by DOCUMENTS.DOCUMENTID desc";

                    filePaths = db.Query<FilePath>(query, commandTimeout: 270).ToList();
                }
                catch (Exception er)
                {
                    string s1 = er.ToString();
                    //  Log.LogMessage(er.ToString(), "Labcorp Interface", "Error");
                }

                for (int i = 0; i < filePaths.Count; i++)
                {
                    try
                    {
                        LogWithoutPath("start excel");
                        app = new Application();
                        app.DisplayAlerts = false;
                        LogWithoutPath("end start excel");
                        LogWithoutPath("start get file name");
                        LogWithoutPath("ID: " + filePaths[i].DocumentID);
                        //string newfilePath = filePaths[i].DocumentPath.Replace("D:\\", "E:\\");
                        string newfilePath = filePaths[i].DocumentPath.Replace("D:\\", "\\\\192.168.190.161\\");
                       // newfilePath = newfilePath.Replace("\\\\", "\\");
                        theFileName = GetFileName(newfilePath);
                        LogWithoutPath("file path: D: " + theFileName);
                        LogWithoutPath("end get file name");
                        LogWithoutPath("start open work book");
                        //wkb = app.Workbooks.Open(copyExcellTo + theFileName + ".xls");
                        try
                        {

                            //wkb = app.Workbooks.Open(filePaths[i].DocumentPath);
                            wkb = app.Workbooks.Open(newfilePath);

                            LogWithoutPath("end open work book");
                            //Workbook wkb = app.Workbooks.Open("C:\\temp3\\2004107065.xls");
                            //\\hmdn-srseds\SRSServer\Storage\16\1627369463\2004108635

                            //Workbook wkb = app.Workbooks.Open(filePaths[i].DocumentPath);
                            LogWithoutPath("start get work sheet");
                            sheet = (Worksheet)wkb.Worksheets[1];
                            LogWithoutPath("end get work sheet");
                            //sheet.PageSetup.FitToPagesWide = 1;
                            sheet.Select(Type.Missing);
                            //wkb.set.ActiveSheet = sheet;
                            LogWithoutPath("start save to pdf");
                        }
                        catch (Exception er)
                        { 
                        string s1 = er.ToString();
                        }
                        try
//.........这里部分代码省略.........

        public Form1()
        {
            //初期化
            InitializeComponent();
            eyeCloseCount = 0;
            dgree = 0;
            BlinkCount = 0;
            distance = 0;

            firstBlink = 20;
 
            eyeClose = false;

            //初期設定
            firstDistance =600 ;
            firstDgree= 0;
            firstBlink = 21;

            listBox3.Items.Clear();
            listBox3.Items.Add("瞬き  " + firstBlink  + "　回/分");
            listBox3.Items.Add("首の傾き　" + firstDgree + "　度");
            listBox3.Items.Add("画面距離　" + firstDistance + "　mm");

            
            _eyeXHost = new FormsEyeXHost();

            //フルスクリーンにする
            this.FormBorderStyle = FormBorderStyle.None;
            this.WindowState = FormWindowState.Maximized;
            
            
            translatorApi = new TranslatorApi();


            string ExcelBookFileName = "test2";

            ExcelApp
              = new Microsoft.Office.Interop.Excel.Application();
            ExcelApp.Visible = false;
            wb = ExcelApp.Workbooks.Add();

            ws1 = wb.Sheets[1];
            ws1.Select(Type.Missing);

            Range rgn = ws1.Cells[excelCount, 1];
            rgn.Value2 = "瞬目率";
            rgn = ws1.Cells[excelCount, 2];
            rgn.Value2 = "首の傾き";
            rgn = ws1.Cells[excelCount, 3];
            rgn.Value2 = "画面距離";
            rgn = ws1.Cells[excelCount, 4];
            rgn.Value2 = "視線先の単語";
            rgn = ws1.Cells[excelCount, 5];
            rgn.Value2 = "瞬き判定";
            rgn = ws1.Cells[excelCount, 6];
            rgn.Value2 = "タイムコード";
            rgn = ws1.Cells[excelCount, 7];
            rgn.Value2 = "視線位置X座標";
            rgn = ws1.Cells[excelCount, 8];
            rgn.Value2 = "視線位置Y座標";
            rgn = ws1.Cells[excelCount, 9];
            rgn.Value2 = "主観的難易度推定";
            rgn = ws1.Cells[excelCount, 10];
            rgn.Value2 = "瞬目からの推定";
            rgn = ws1.Cells[excelCount, 11];
            rgn.Value2 = "首の傾き分散からの推定";
            rgn = ws1.Cells[excelCount, 12];
            rgn.Value2 = "画面距離分散からの推定";
            rgn = ws1.Cells[excelCount, 13];
            rgn.Value2 = "首の傾きの分散";
            rgn = ws1.Cells[excelCount, 14];
            rgn.Value2 = "画面距離の分散";

         


            this.listBox2.Visible = false;
            this.listBox3.Visible = false;
            this.label3.Visible = false;
            this.label1.Visible = false;
            this.listBox1.Visible = false;                

            


            //Form_Load呼び出し
            Load += Form1_Load;


        }

        public List<String> GetExcelValuesList(string StrTestData,string StrRuntime)
        {
            objExcelApp = new Microsoft.Office.Interop.Excel.Application();
            objExcelWorkBooks = objExcelApp.Workbooks;
            objExcelWorkbook = objExcelWorkBooks.Open(StrTestData, 0, false, 5, "", "", true,
            Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);

            //Open the Sheet
            objExcelWorkSheet = (Worksheet)objExcelWorkbook.Worksheets["Global"]; //Exce工作簿的Sheet
            objExcelWorkSheet.Select(Type.Missing);

            //取得总记录行数   (包括标题列)
            int rowsint = objExcelWorkSheet.UsedRange.Cells.Rows.Count; //得到行数
            int columnsint = objExcelWorkSheet.UsedRange.Cells.Columns.Count;//得到列数

            string temp1 = ((Range)objExcelWorkSheet.Cells[1, 1]).Text.ToString();
            //遍历得到CompanyCode和TestAsset
            int rowcolum = 0;
            int RowID = 0;
            List<String> str = new List<String>();
            List<String> strRowID = new List<String>();
            for (int i = 1; i <= columnsint; i++)
            {
                string strColumnsName = ((Range)objExcelWorkSheet.Cells[1, i]).Text.ToString();
                if (strColumnsName == "RunTime")
                {
                    rowcolum = i;//Get the columm no
                    for (int j = 1; j <= rowsint;j++ )
                    {
                        string strRowValue = ((Range)objExcelWorkSheet.Cells[j, i]).Text.ToString();
                        if(strRowValue == StrRuntime)
                        {
                            RowID = j;
                            strRowID.Add(RowID.ToString());
                        }
                    }
                }
                  if (strColumnsName == "CompanyCode")
                 {
                      rowcolum = i;//得到Column号
                      string strRowIDNo = strRowID[0];
                      int RowNo = Convert.ToInt32(strRowIDNo);
                      String temp = ((Range)objExcelWorkSheet.Cells[RowNo, i]).Text.ToString();
                      str.Add(((Range)objExcelWorkSheet.Cells[RowNo, i]).Text.ToString());
                  }
                  if (strColumnsName == "TargetBoxName")
                 {
                      rowcolum = i;//得到行号
                      string strRowIDNo = strRowID[0];
                      int RowNo = Convert.ToInt32(strRowIDNo);
                      String tempAssent = ((Range)objExcelWorkSheet.Cells[RowNo, i]).Text.ToString();
                     // string Asset = tempAssent.Substring(tempAssent.Length - 3);
                      str.Add(tempAssent);
                   }
                }
                //if (strColumnsName == "CompanyCode")
                //{
                //    rowcolum = i;//得到Column号
                //    String temp = ((Range)objExcelWorkSheet.Cells[2, i]).Text.ToString();
                //    str.Add(((Range)objExcelWorkSheet.Cells[2, i]).Text.ToString());
                //}
                //if (strColumnsName == "TargetBoxName")
                //{
                //    rowcolum = i;//得到行号
                //    String tempAssent = ((Range)objExcelWorkSheet.Cells[2, i]).Text.ToString();
                //    string Asset = tempAssent.Substring(tempAssent.Length - 3);
                //    str.Add(Asset);
                //}
            objExcelWorkbook.Close(false, StrTestData, false);
            objExcelApp.Quit();
            NAR(objExcelApp);
            NAR(objExcelWorkbook);
            NAR(objExcelWorkSheet);
            return str;
        }

        // Insert Row in the Driver.xls
        public void InsertRowsValues(string StrDriverPath, string StrCaseName, string StrTestDataPath, string StrComCode, string StrAsset, string StrRunTime)
        {
            objExcelApp = new Microsoft.Office.Interop.Excel.Application();
            objExcelWorkBooks = objExcelApp.Workbooks;
            objExcelWorkbook = objExcelWorkBooks.Open(StrDriverPath, 0, false, 5, "", "", true,
            Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);

            objExcelWorkSheet = (Worksheet)objExcelWorkbook.Worksheets["Driver"]; //strSheetName是指的Exce工作簿的Sheet名，如果没有命名则为"1"
            objExcelWorkSheet.Select(Type.Missing);
            int rowsint = objExcelWorkSheet.UsedRange.Cells.Rows.Count; //得到行数
            if (rowsint > 1)
            {
                for (int i = 2;i <= rowsint; i++)
                {
                    string strFlagValue = ((Range)objExcelWorkSheet.Cells[i, 1]).Text.ToString();
                    if (strFlagValue == "Y")
                    {
                         int RowNO = i;
                         objExcelWorkSheet.Cells[RowNO, 1] = "N";
                    }
                }
            }
            if (rowsint == 1)
            {
                rowsint = 2;
            }
            objExcelWorkSheet.Cells[rowsint, 1] = "Y";
            objExcelWorkSheet.Cells[rowsint, 2] = "fa_30_saptao_mec";
            objExcelWorkSheet.Cells[rowsint, 3] = StrRunTime;
            objExcelWorkSheet.Cells[rowsint, 4] = StrRunTime;
            objExcelWorkSheet.Cells[rowsint, 5] = StrCaseName;
            objExcelWorkSheet.Cells[rowsint, 6] = StrTestDataPath;
            objExcelWorkSheet.Cells[rowsint, 7] = "Run";
            objExcelWorkSheet.Cells[rowsint, 8] = StrComCode;
            objExcelWorkSheet.Cells[rowsint, 9] = StrAsset;
            objExcelWorkbook.Save();

            objExcelWorkbook.Close(false, StrDriverPath, false);
            objExcelApp.Quit();
            NAR(objExcelApp);
            NAR(objExcelWorkbook);
            NAR(objExcelWorkSheet);
        }

        // Get the RunTime Values for ReRun & Mapping
        public string GetRunTimeVlaue(string StrOutputPath)
        {
            objExcelApp = new Microsoft.Office.Interop.Excel.Application();
            objExcelWorkBooks = objExcelApp.Workbooks;
            objExcelWorkbook = objExcelWorkBooks.Open(StrOutputPath, 0, false, 5, "", "", true,
            Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);

            //Open the Sheet
            objExcelWorkSheet = (Worksheet)objExcelWorkbook.Worksheets["Output"]; //Exce工作簿的Sheet
            objExcelWorkSheet.Select(Type.Missing);

            //取得总记录行数   (包括标题列)
            int rowsint = objExcelWorkSheet.UsedRange.Cells.Rows.Count; //得到行数
            int columnsint = objExcelWorkSheet.UsedRange.Cells.Columns.Count;//得到列数

            string temp1 = ((Range)objExcelWorkSheet.Cells[1, 1]).Text.ToString();
            //遍历得到CompanyCode和TestAsset、Runtime
            int rowcolum = 0;
            for (int i = 1; i <= columnsint; i++)
            {
                string strColumnsName = ((Range)objExcelWorkSheet.Cells[1, i]).Text.ToString();
                if (strColumnsName == "RunTime")
                {
                    rowcolum= i;//得到Colum号
                }
            }
            String RunTimeValues = ((Range)objExcelWorkSheet.Cells[rowsint,rowcolum]).Text.ToString();
            objExcelWorkbook.Close(false, StrOutputPath, false);
            objExcelApp.Quit();
            NAR(objExcelApp);
            NAR(objExcelWorkbook);
            NAR(objExcelWorkSheet);
            return RunTimeValues;
        }

        // Get The RunTime For First Run @2015-7-16 by CC
        public List<String> GetRunTimeForRun(string StrTestData)
        {
            objExcelApp = new Microsoft.Office.Interop.Excel.Application();
            objExcelWorkBooks = objExcelApp.Workbooks;
            objExcelWorkbook = objExcelWorkBooks.Open(StrTestData, 0, false, 5, "", "", true,
            Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);

            //Open the Sheet
            objExcelWorkSheet = (Worksheet)objExcelWorkbook.Worksheets["Global"]; //Exce工作簿的Sheet
            objExcelWorkSheet.Select(Type.Missing);

            //取得总记录行数   (包括标题列)
            int rowsint = objExcelWorkSheet.UsedRange.Cells.Rows.Count; //得到行数
            int columnsint = objExcelWorkSheet.UsedRange.Cells.Columns.Count;//得到列数

            string temp1 = ((Range)objExcelWorkSheet.Cells[1, 1]).Text.ToString();
            //遍历得到RunTime
            int rowcolum = 0;
            List<String> str = new List<String>();
            for (int i = 1; i <= columnsint; i++)
            {
                string strColumnsName = ((Range)objExcelWorkSheet.Cells[1, i]).Text.ToString();
                if (strColumnsName == "RunTime")
                {
                    rowcolum = i;//Get Column No
                    for (int j = 2; j <= rowsint; j++)
                    {
                        string StrRunTimeValue = ((Range)objExcelWorkSheet.Cells[j, i]).Text.ToString();
                        str.Add(StrRunTimeValue);
                    }
                }
            }
            objExcelWorkbook.Close(false, StrTestData, false);
            objExcelApp.Quit();
            NAR(objExcelApp);
            NAR(objExcelWorkbook);
            NAR(objExcelWorkSheet);
            return str;
        }

        /// <summary>
        /// 引数indexsで指定されたシートを選択状態にします。
        /// indexは1始まりです。
        /// </summary>
        /// <param name="indexs">選択したいシートのインデックス配列</param>
        public void SelectedSheets(int[] indexs)
        {
            sheets = workbook.Worksheets;
            bool isSelectMode = true;
            for (int i = 0; i < indexs.Length; i++)
            {
                try
                {
                    sheet = sheets[indexs[i]] as Worksheet;
                    if (sheet != null)
                    {
                        sheet.Select(isSelectMode);

                        // 複数のシートを選択する場合は一枚目以降はfalseを設定する必要があるのでフラグを更新
                        if (isSelectMode)
                            isSelectMode = false;
                    }
                }
                catch
                {
                }
            }
        }