VSTO Excel单元格填充的速度和效率问题

VSTO Excel单元格填充的速度和效率问题

测试方法:填写10000个单元格内容为"asd"

序号	方法	时间（毫秒）	备注
1	this.Sheets["Sheet1"].Cells(i, 1).Value = "das";
2	sheet.Cells[i, 1].Value= "das";
3	this.Sheets["Sheet1"].Range("A"+i).Value = "das";
4	String[] x = new String[10000];             for (int i = 0; i < 10000; i++) { x[i] = "das"; }             this.Sheets[1].Range("A1: A10000").Value = x.ToArray();;

 

 

 

 

 

 

 

 

 

 

 

代码：

    /// <summary>
    /// 辅助类（对vsto的操作）
    /// </summary>
    public class ExcelHelper
    {
        /// <summary>
        /// 整个应用程序
        /// </summary>
        public Excel.Application ExcelApp;
        /// <summary>
        /// 工作簿
        /// </summary>
        public Excel.Workbook Workbook;
        /// <summary>
        /// 工作表
        /// </summary>
        public Excel.Worksheet ExcelSheet;


        public string TempFilePath = string.Empty;//临时文件路径
        public string FilePath = string.Empty;//文件路径

        public ExcelHelper()
        {
            ExcelApp = Globals.ThisAddIn.Application;
            Workbook = ExcelApp.ActiveWorkbook;
            ExcelSheet = (Excel.Worksheet)ExcelApp.ActiveSheet;

            FilePath = Workbook.FullName;
        }
        /// <summary>
        /// 填充数据(以行为单位)
        /// </summary>
        /// <param name="addVal">要填充的数据（从第一行开始）</param>
        public void AddVal(IEnumerable<string> addVal, int ran = 1)
        {
            string cellStr = $"A{ran}:{ExcelHelper.ColumnToABC(addVal.Count())}{ran}";
            ExcelSheet.Range[cellStr].Value = addVal.ToArray();

        }
 /// <summary>
        /// 数字转字符
        /// </summary>
        /// <param name="iNumber"></param>
        /// <returns></returns>
        public static string ColumnToABC(int iNumber)
        {
            if (iNumber < 1 || iNumber > 702)
                throw new Exception("转为26进制可用10进制范围为1-702");

            string sLetters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
            int iUnits = 26;
            int iDivisor = (int)(iNumber / iUnits);
            int iResidue = iNumber % iUnits;
            if (iDivisor == 1 && iResidue == 0)
            {
                iDivisor = 0;
                iResidue += iUnits;
            }
            else
            {
                if (iResidue == 0)
                {
                    iDivisor -= 1;
                    iResidue += iUnits;
                }
            }

            if (iDivisor == 0)
                return sLetters.Substring(iResidue - 1, 1);
            else
                return sLetters.Substring(iDivisor - 1, 1) + sLetters.Substring(iResidue - 1, 1);

        }
    }

　　

 

转载+修改：https://www.cnblogs.com/tigerlove/p/3151483.html