vsto 显示隐藏列

最新推荐文章于 2023-04-26 17:21:36 发布

BugLovers

最新推荐文章于 2023-04-26 17:21:36 发布

阅读量93

点赞数

分类专栏： vsto 文章标签： vsto Powered by 金山文档

本文链接：https://blog.csdn.net/nbspzs/article/details/129563539

版权

vsto 专栏收录该内容

108 篇文章 8 订阅

订阅专栏

该代码示例展示了如何使用C#和MicrosoftOfficeInteropExcel库在VSTO加载项中实现隐藏工作表中的奇数或偶数列功能。用户可以通过点击按钮切换奇数列或偶数列的可见性。此外，还提供了显示所有列的选项。

摘要由CSDN通过智能技术生成

using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Tools.Ribbon;
using System;

using Excel = Microsoft.Office.Interop.Excel;

namespace ExcelAddIn1
{
    public partial class Ribbon1
    {
        private void Ribbon1_Load(object sender, RibbonUIEventArgs e)
        {

        }

        //奇数
        private void button1_Click(object sender, RibbonControlEventArgs e)
        {
            HideColumn();
        }

        //偶数
        private void button2_Click(object sender, RibbonControlEventArgs e)
        {
            HideColumn(false);

        }

        /// <summary>
        /// 显示隐藏列
        /// </summary>
        /// <param name="isOdd">是否偶数列</param>
        private static void HideColumn(bool isOdd=true)
        {
            var app = Globals.ThisAddIn.Application;
            var wb = app.ActiveWorkbook;
            Worksheet sh = wb.ActiveSheet;
            var address = sh.UsedRange.Address;
            Range lastCell = sh.UsedRange.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
            int lastRow = lastCell.Row;
            int lastCol = lastCell.Column;
            for (var i = 1; i <= lastCol; i++)
            {
                var letter = ExcelColumnFromNumber(i);
                var column = string.Format("{0}:{0}", letter);
                if (isOdd)
                {
                    //奇显示
                    if (i % 2 == 0)
                    {
                        sh.Range[column].EntireColumn.Hidden = false;
                    }
                    else
                    {
                        sh.Range[column].EntireColumn.Hidden = true;
                    }
                }else
                {
                    //奇显示
                    if (i % 2 == 0)
                    {
                        sh.Range[column].EntireColumn.Hidden = true;
                    }
                    else
                    {
                        sh.Range[column].EntireColumn.Hidden = false;
                    }
                }
            }
        }

        private void button3_Click(object sender, RibbonControlEventArgs e)
        {
            var app = Globals.ThisAddIn.Application;
            var wb = app.ActiveWorkbook;
            Worksheet sh = wb.ActiveSheet;
            sh.UsedRange.EntireColumn.Hidden=false;
        }
                
        public static string ExcelColumnFromNumber(int column)
        {
            string columnString = "";
            decimal columnNumber = column;
            while (columnNumber > 0)
            {
                decimal currentLetterNumber = (columnNumber - 1) % 26;
                char currentLetter = (char)(currentLetterNumber + 65);
                columnString = currentLetter + columnString;
                columnNumber = (columnNumber - (currentLetterNumber + 1)) / 26;
            }

            return columnString;
        }

        public static int NumberFromExcelColumn(string column)
        {
            int retVal = 0;
            string col = column.ToUpper();
            for (int iChar = col.Length - 1; iChar >= 0; iChar--)
            {
                char colPiece = col[iChar];
                int colNum = colPiece - 64;
                retVal = retVal + colNum * (int)Math.Pow(26, col.Length - (iChar + 1));
            }

            return retVal;
        }
    }
}