Excel从另一个Excel文件中用户自定义区域(UsedRange)拷贝数据和行高、列宽
实现环境: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; } } }
相关资源:http://download.csdn.net/detail/tx_officedev/3900007
征诛志异,三让两家王朝;功同开辟,一桮万古江南。