vsto 显示隐藏列

该代码示例展示了如何使用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;
        }
    }
}

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值