C# Microsoft.Office.Interop.Excel.Select方法代码示例

 private static void FormatDataArea(Excel.Range range, String data)
 {
     range.Worksheet.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, range, System.Type.Missing,
         Excel.XlYesNoGuess.xlYes, System.Type.Missing).Name = data;
     range.Select();
     range.Worksheet.ListObjects[data].TableStyle = "TableStyleMedium2";
     range.EntireColumn.AutoFit();
 }

        /// <summary>
        /// 在Excel格式的LOT卡表中插入单体图图片并设置页面属性
        /// </summary>
        /// <param name="xlSheet">Excel格式的LOT卡表</param>
        /// <param name="imgName">图片全路径</param>
        /// <param name="strError">返回的错误</param>
        /// <returns></returns>
        internal static bool InsertDanTiTuImageToExcelSheet(
            Excel.Worksheet xlSheet,
            string imgName,
            out string strError
        )
        {
            //默认没有错误
            strError = string.Empty;
            try
            {
                //取得最大列数
                int maxColNum = 15;
                //检测最大行
                int maxRowNum = xlSheet.Range["A65536"].End[Excel.XlDirection.xlUp].Row;
                for (int i = 2; i < 16; i++)
                {
                    int maxRowNum2 = ((Excel.Range)xlSheet.Cells[65536, i]).End[Excel.XlDirection.xlUp].Row;
                    if (maxRowNum < maxRowNum2) { maxRowNum = maxRowNum2; }
                }
                //删除多余的行
                ((Excel.Range)xlSheet.Rows[(maxRowNum + 1) + ":65536"]).Delete(Shift: Excel.XlDirection.xlUp);
                //删除多余的行
                ((Excel.Range)xlSheet.Rows[(maxRowNum + 1) + ":65536"]).Delete(Shift: Excel.XlDirection.xlUp);
                //图片是否存在
                if (!File.Exists(imgName))
                {
                    throw new Exception(string.Format("未找到生成的单体图图片{0}!", imgName));
                }
                //插入图片
                xlSheet.Range["A" + (maxRowNum + 1)].Select();
                var pic = ((Excel.Pictures)((Excel._Worksheet)xlSheet).Pictures()).Insert(imgName);
                //取得列A-O的总宽度
                double dTotalColWidth = 0d;
                for (int i = 1; i < maxColNum + 1; i++)
                {
                    dTotalColWidth += Convert.ToDouble(((Excel.Range)xlSheet.Cells[1, i]).ColumnWidth);
                }
                //取得全部行的总高度
                double dTotalRowHeight = 0d;
                for (int i = 1; i < maxRowNum + 1; i++)
                {
                    dTotalRowHeight += Convert.ToDouble(((Excel.Range)xlSheet.Cells[i, 1]).RowHeight);
                }
                //宽度缩放比例
                int iZoomWidth = Convert.ToInt32(Math.Floor(89d / dTotalColWidth * 100));
                //高度缩放比例
                int iZoomHeight = Convert.ToInt32(Math.Floor(790d / dTotalRowHeight * 100));
                //取小的缩放比例
                int iZoom = Math.Min(iZoomWidth, iZoomHeight);
                //检测是否高度未使用完则找到第一页最后一行
                if (iZoom < iZoomHeight)
                {
                    //待插入的行数
                    int iCountRow = Convert.ToInt32(Math.Floor(790d - dTotalRowHeight * iZoom / 100) / 10) + 1;
                    //检测行数是否大于0
                    if (iCountRow > 0)
                    {
                        //需要下移的连续行字符串
                        string strRowNums = string.Format("{0}:{1}", maxRowNum + 1, maxRowNum + iCountRow - 1);
                        //插入多行
                        ((Excel.Range)xlSheet.Rows[strRowNums]).Insert(Shift: Excel.XlDirection.xlDown);
                        //每行的行高
                        double dRowHeight = 10d / (iZoom / 100d);
                        //设置插入行行高
                        ((Excel.Range)xlSheet.Rows[strRowNums]).RowHeight = dRowHeight;
                        //第一页行高
                        dTotalRowHeight += dRowHeight;
                        //最大行号
                        maxRowNum += iCountRow;
                    }
                }
                //原图片长宽
                double dPicHeight = pic.Height;
                double dPicWidth = pic.Width;
                //按第一页总高度度得到新的图片长宽
                double dPicHeightNew = dTotalRowHeight;
                double dPicWidthNew = dPicWidth / dPicHeight * dPicHeightNew;
                //宽度大于第一页宽度按照第一页总宽度得到图片长宽
                if (dPicWidthNew > dTotalColWidth)
                {
                    dPicWidthNew = dTotalColWidth;
                    dPicHeightNew = dPicHeight / dPicWidth * dPicWidthNew;
                }
                //图片按比例设置新的长宽
                pic.Height = dPicHeightNew * 6.3d;
                pic.Width = dPicWidthNew * 6.3d;
                //设置打印区域
                xlSheet.PageSetup.PrintArea = "$A:$O";
                //设置打印比例
                xlSheet.PageSetup.Zoom = iZoom;
                //设置页边距
                xlSheet.PageSetup.LeftMargin = xlSheet.Application.InchesToPoints(0.25d);
                xlSheet.PageSetup.RightMargin = xlSheet.Application.InchesToPoints(0.19d);
//.........这里部分代码省略.........

        private void PopulateStatColumn(int column, Excel.Range start, Excel.Range end)
        {
            try
            {
                // 确定需要用数据填充的范围。
                Excel.Range twoLines = start.get_Resize(2, 1);

                twoLines.Merge(System.Type.Missing);

                Excel.Range fillRange = this.worksheet.get_Range(start, end);
                end.Select();

                switch (column)
                {
                    case (int)StatHeadings.DailySales:
                        // 填充日销售额列。
                        // 获取包含标准偏差和
                        // 平均值的单元格的地址。
                        Excel.Range average = start.Previous;
                        string averageAddress = average.get_Address(false, false, Excel.XlReferenceStyle.xlA1, defaultParameter, defaultParameter);
                        Excel.Range standardDev = average.get_Offset(1, 0);
                        string standardDevAddress = standardDev.get_Address(false, false, Excel.XlReferenceStyle.xlA1, defaultParameter, defaultParameter);

                        // 设置该列的公式。
                        start.Formula = "=" + averageAddress + "+ (2*" + standardDevAddress + ")";

                        // 格式“0.00”- 两个小数位。
                        start.NumberFormat = "0.00";
                        twoLines.AutoFill(fillRange, Excel.XlAutoFillType.xlFillDefault);
                        break;

                    case (int)StatHeadings.Required:
                        // 填充所需的列。
                        // 确定包含预期销售额的
                        // 单元格的地址。
                        Excel.Range expectedSales = start.Previous;
                        string salesAddress = expectedSales.get_Address(false, false, Excel.XlReferenceStyle.xlA1, defaultParameter, defaultParameter);

                        // 确定交货前需要多少
                        // 库存。
                        // 确定交货前的天数。
                        int waitDays = this.GetDaysToDelivery();

                        start.Formula = "=" + waitDays + "*" + salesAddress;

                        // 格式“0.00”- 两个小数位。
                        start.NumberFormat = "0.00";
                        twoLines.AutoFill(fillRange, Excel.XlAutoFillType.xlFillDefault);
                        break;

                    case (int)StatHeadings.CurrentInventory:
                        // 填充当前库存列。
                        // 从日记中获取上一天的范围。
                        int count = (end.Row - start.Row + 1) / 2;
                        Excel.Range currentCell = start;

                        for (int row = 0; row < count; row += 1)
                        {
                            Excel.Range flavorCell = currentCell.get_Offset(0, 0 - 5);

                            string flavor = ExcelHelpers.GetValueAsString(flavorCell);
                            int inventory = Globals.DataSet.Sales.FindByDateFlavor(Globals.DataSet.MaxDate, flavor).Inventory;

                            currentCell.Value2 = inventory;

                            if (row != 0)
                            {
                                Excel.Range twoCells = currentCell.get_Resize(2, 1);

                                twoCells.Merge(System.Type.Missing);
                                currentCell = twoCells;
                            }

                            currentCell = currentCell.get_Offset(1, 0);
                        }

                        break;

                    case (int)StatHeadings.ProjectInventory:

                        // 获取预计销售额和
                        // 当前库存的地址。
                        Excel.Range currentInventory = start.Previous;
                        Excel.Range required = currentInventory.Previous;
                        string currentInventoryAddress = currentInventory.get_Address(false, false, Excel.XlReferenceStyle.xlA1, defaultParameter, defaultParameter);
                        string requiredAddress = required.get_Address(false, false, Excel.XlReferenceStyle.xlA1, defaultParameter, defaultParameter);

                        // 确定交货日期的
                        // 预计库存。
                        start.Formula = "=MAX(0," + currentInventoryAddress + "-" + requiredAddress + ")";

                        // 格式“0.00”- 两个小数位。
                        start.NumberFormat = "0.00";
                        twoLines.AutoFill(fillRange, Excel.XlAutoFillType.xlFillDefault);
                        break;

                    case (int)StatHeadings.OrderQuanity:
                        // 确定预计库存和
                        // 所需库存量的地址。
                        Excel.Range projectedInventory = start.Previous;
//.........这里部分代码省略.........

        public void Execute(AnalyticsService service, XML.Objects.Organisation.View view, Excel.Range currentCell)
        {
            String metrics = MetricsToString();

            Request = service.Data.Ga.Get(view.Tag, Start.ToString("yyyy-MM-dd"), End.ToString("yyyy-MM-dd"), metrics);
            SetDimensions(Request);
            SetSort(Request);
            SetFilters(Request);
            SetSegment(Request);

            Request.MaxResults = 10000;

            Request.PrettyPrint = true;

            //Send the query
            GaData result = null;
            try
            {
                result = Request.Execute();
                currentCell = Stubs.DisplayGaData(result, currentCell);
                currentCell.Select();
            }
            catch (Google.GoogleApiException exception)
            {
                MessageBox.Show(exception.Message, "Erreur", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void SelectColumnsInRange(Excel.Worksheet sheet, string columns)
        {
            if (ValidateColumnInput(sheet, columns, false))
            {
                // Make sure the provided sheet is selected
                sheet.Select();

                string[] columnArray = columns.ToUpper().Split(',');
                StringBuilder columnRangeString = new StringBuilder();

                foreach (string column in columnArray)
                {
                    // A blank column will only occur if extra commas were placed after the columns
                    if (column == "")
                        continue;

                    columnRangeString.Append(column + ":" + column + ",");
                }
                columnRangeString.Length = columnRangeString.Length - 1;

                Range range = sheet.get_Range(columnRangeString.ToString(), Type.Missing);
                range.EntireColumn.Select();
            }
        }

 public void copyRange(Excel.Worksheet source, Excel.Worksheet dest, String startCell, String endCell, String startDest)
 {
     // get the length of the range - this might need to change later, figured it would be
     // easier if this just adapted by itself.
     int end = System.Convert.ToInt32(endCell.Substring(1)) + System.Convert.ToInt32(startDest.Substring(1));
     String col = startDest.Substring(0, 1);
     String row = end.ToString();
     String destEnd = col + row;
     // select the source worksheet
     source.Select();
     // get the cell range
     Excel.Range reel = source.get_Range(startCell, endCell);
     // copy our data
     reel.Copy();
     // select the destination worksheet
     dest.Select();
     // pick our destination cell
     Excel.Range targetCell = dest.get_Range(startDest, destEnd);
     try
     {
         targetCell.Select();
     }
     catch (Exception e)
     {
         System.Windows.Forms.MessageBox.Show(e.Message.ToString(), "Error - target cells not empty", System.Windows.Forms.MessageBoxButtons.OK);
         return;
     }
     // paste our data
     dest.Paste(targetCell);
 }

 private void copyRange(Excel.Worksheet origin, Excel.Worksheet dest, String startCell, String endCell, String startDest)
 {
     // get the length of the range - this might need to change later, figured it would be
     // easier if this just adapted by itself.
     int end = System.Convert.ToInt32(endCell.Substring(1)) + System.Convert.ToInt32(startDest.Substring(1));
     String col = startDest.Substring(0, 1);
     String row = end.ToString();
     // select the source worksheet
     this.source.Activate();
     origin.Select();
     // get the cell range
     String blank = "\0";
     Excel.Range firstBlank = origin.Cells.Find(blank, origin.get_Range(startCell), Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByColumns);
     if (firstBlank != null)
     {
         String temp = firstBlank.Cells.get_Address().ToString().Replace("$", "");
         int endRow = System.Convert.ToInt32(temp.Substring(1));
         row = endRow.ToString();
     }
     int destEndRow = (8 + end) - parseRow(startCell);
     String destEnd = String.Concat(col, destEndRow);
     String sourceEnd = String.Concat(startCell.Substring(0, 1), row);
     Excel.Range reel = origin.get_Range(startCell, sourceEnd);
     // copy our data
     reel.Copy();
     // select the destination worksheet
     this.target.Activate();
     this.targetSheet.Select();
     // pick our destination cell
     Excel.Range targetCell = dest.get_Range(startDest, destEnd);
     try
     {
         targetCell.Select();
     }
     catch (Exception e)
     {
         System.Windows.Forms.MessageBox.Show("Error code:\n" + e.Message.ToString(), "Error - target cells not empty", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
         return;
     }
     // paste our data
     try
     {
         targetCell.PasteSpecial(Excel.XlPasteType.xlPasteValues);
     }
     catch (Exception pasteEx)
     {
         System.Windows.Forms.MessageBox.Show("Error code:\n" + pasteEx.Message.ToString(), "Error - Can't paste cells", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
         return;
     }
 }

 private bool checkValue(Excel.Workbook book, Excel.Worksheet sheet, String cell)
 {
     book.Activate();
     if (book.ActiveSheet != sheet)
         sheet.Select();
     Excel.Range test = sheet.get_Range(cell);
     try
     {
         sheet.Select(test);
     }
     catch (Exception e)
     {
         // apparently, an empty cell is not valid for selection.
         return true;
     }
     try
     {
         if (test.Value2 == "" || test.Value2 == null)
             return false;
         else
             return true;
     }
     catch (Exception e)
     {
         return false;
     }
 }