Excel从另一个Excel文件中用户自定义区域(UsedRange)拷贝数据和行高、列宽_$usedrange.copy($worksheetout.range('a1'));-优快云博客

实现环境：Visual Studio 2010, Office 2010, VSTO 4.0, .NET Framework 4.0

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Tools.Ribbon;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;

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

        }
        
        /// <summary>
        /// 在下面的动作中，将从选中的文件中的用户自定义区域（UsedRange）中拷贝
        /// 数据到现在这个WorkSheet中并根据选中文件中UsedRange的行高列宽来设定被
        /// 粘贴数据区域的行高列宽。
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button1_Click(object sender, RibbonControlEventArgs e)
        {
            var missing = Type.Missing;
            string SourcePath = string.Empty;
            Excel.Application objApplication = Globals.ThisAddIn.Application;
            Excel.Application objSourceApplication = new Excel.Application();
            Excel.Workbook objTargetWorkbook = objApplication.ActiveWorkbook;
            OpenFileDialog objOpenFileDialog = new OpenFileDialog();
            objOpenFileDialog.Filter = "Excel Document(.xlsx)|*.xlsx";
            objOpenFileDialog.ShowDialog();
            SourcePath = objOpenFileDialog.FileName;
            if (SourcePath.Length > 0)
            {
                Excel.Workbook objSourceWorkbook = objSourceApplication
                    .Workbooks.Open(SourcePath, missing, missing, missing,
                    missing,missing,missing,missing, missing, missing, missing, 
                        missing, missing,missing, missing);
                Excel.Worksheet objWorkSheet = objSourceWorkbook.Worksheets[1];
                objWorkSheet.UsedRange.Copy(missing);
                
                Excel.Worksheet objTargetSheet =
                    objTargetWorkbook.ActiveSheet;
                if (objTargetSheet != null)
                {
                    objTargetSheet.Paste();
                    Excel.Range objTargetRange = objApplication.ActiveCell;
                    //以下代码用来重新设定Range区域，使之与源文件中的UsedRange范
                    //围相当。
                    objTargetRange = objTargetSheet.Range[objTargetSheet.Cells
                        [objTargetRange.Row, objTargetRange.Column],
                        objTargetSheet.Cells[objTargetRange.Row 
                            + objWorkSheet.UsedRange.Rows.Count-1,
                           objTargetRange.Column + 
                           objWorkSheet.UsedRange.Columns.Count]];
                    //根据UsedRange的列宽来设置选定区域的列宽。
                    objTargetRange.ColumnWidth = objWorkSheet.UsedRange
                        .ColumnWidth;
                    //根据UsedRange的行高来设置选定区域的行高。
                    objTargetRange.RowHeight = objWorkSheet.UsedRange.RowHeight;
                }
            }
            ((Excel._Application)objSourceApplication).Quit();
            objSourceApplication = null;
        }
    }
}