如何在Excel工作表上应用过滤器并将过滤后的数据复制到另一个Excel工作表中 [英] How to apply filter on excel sheet and copy the filtered data in another excel sheet

问题描述

我成功地将数据从一个excel复制到另一个excel但是无法应用过滤器n复制过滤后的数据



我尝试了什么：



使用System;

使用System.Collections.Generic;

使用System.ComponentModel;

使用System.Data;

使用System.Drawing;

使用System.Linq;

使用System.Text ;

使用Microsoft.Office.Interop.Excel;





使用System.Windows.Forms;

名称空间Excelcopy

{



公共部分类Form1：表格

{< br $>




public Form1（）

{



InitializeComponent（）;

}



private void button1_Click（object sender，EventArgs e）

{

//Microsoft.Office.Interop.Excel.Application excel = new Micro soft.Office.Interop.Excel.Application（）;

Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application（）;

工作簿curWorkBook = null;

工作簿destWorkbook = null;

工作表workSheet = null;

//工作表newWorksheet = null;

对象defaultArg = Type.Missing;



尝试

{

//复制源表



curWorkBook = app.Workbooks.Open（c：\\SyntBotsExecutionReport.xlsm）;

workSheet = （工作表）curWorkBook.Sheets [2];

workSheet.UsedRange.Copy（defaultArg）;



//粘贴到目标表< br $>
destWorkbook = app.Workbooks.Open（c：\\Output.xlsx）;

workSheet =（工作表）curWorkBook.ActiveSheet;

workSheet =（工作表）destWorkbook.Worksheets.Add（defaultArg，defaultArg，defaultArg，defaultArg）;

workSheet.UsedRange.PasteSpecial（XlPasteType.xlPasteValues，XlPasteSpecialOperation.xlPasteSpecialOperationNone，false，false）;

}

catch（Exception exc）

{

System.Windows.Forms.MessageBox.Show（exc.Message）;

}

curWorkBook.Save（）; < br $>
destWorkbook.Save（）;

curWorkBook.Close（defaultArg，defaultArg，defaultArg）;

destWorkbook.Close（defaultArg，defaultArg，defaultArg） ;

//最后

// {

// if（curWorkBook！= null）

// {

// curWorkBook.Save（） ;

// curWorkBook.Close（defaultArg，defaultArg，defaultArg）;

//}



/ / if（destWorkbook！= null）

// {

// destWorkbook.Save（）;

// destWorkbook.Close（defaultArg ，defaultArg，defaultArg）;

//}

//}

app.Quit（）;

MessageBox.Show（复制完成）;

}

I was succefull in copying the data from one excel to another but couldnt apply filter n copy the filtered data

What I have tried:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;


using System.Windows.Forms;
namespace Excelcopy
{

public partial class Form1 : Form
{


public Form1()
{

InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
//Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Workbook curWorkBook = null;
Workbook destWorkbook = null;
Worksheet workSheet = null;
//Worksheet newWorksheet = null;
Object defaultArg = Type.Missing;

try
{
// Copy the source sheet

curWorkBook = app.Workbooks.Open("c:\\SyntBotsExecutionReport.xlsm");
workSheet = (Worksheet)curWorkBook.Sheets[2];
workSheet.UsedRange.Copy(defaultArg);

// Paste on destination sheet
destWorkbook = app.Workbooks.Open("c:\\Output.xlsx");
workSheet = (Worksheet)curWorkBook.ActiveSheet;
workSheet = (Worksheet)destWorkbook.Worksheets.Add(defaultArg, defaultArg, defaultArg, defaultArg);
workSheet.UsedRange.PasteSpecial(XlPasteType.xlPasteValues, XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
}
catch (Exception exc)
{
System.Windows.Forms.MessageBox.Show(exc.Message);
}
curWorkBook.Save();
destWorkbook.Save();
curWorkBook.Close(defaultArg, defaultArg, defaultArg);
destWorkbook.Close(defaultArg, defaultArg, defaultArg);
//finally
//{
// if (curWorkBook != null)
// {
// curWorkBook.Save();
// curWorkBook.Close(defaultArg, defaultArg, defaultArg);
// }

// if (destWorkbook != null)
// {
// destWorkbook.Save();
// destWorkbook.Close(defaultArg, defaultArg, defaultArg);
// }
//}
app.Quit();
MessageBox.Show("Copy Completed");
}

推荐答案

我自己没有测试过这个，但我的理解是;

a）获取工作表使用范围

I haven't tested this myself but my understanding is;
a) Get the Worksheet Used Range

curWorkBook = app.Workbooks.Open("c:\\SyntBotsExecutionReport.xlsm");
workSheet = (Worksheet)curWorkBook.Sheets[2];
Excel.Range sourceRange = workSheet.UsedRange;

b）应用过滤器 - 请参阅MSDN - Range.AutoFilter Method（Excel） [ ^ ]

b) Apply the filter - Refer MSDN - Range.AutoFilter Method (Excel)[^]

sourceRange.AutoFilter(<columnnumber1>, <crteriastring1>, <operator>, <columnnumber2>, <criteriastring2>);</criteriastring2></columnnumber2></operator></crteriastring1></columnnumber1>

c）获取过滤范围 - 参考MSDN - Range.SpecialCells Method（Excel） [ ^ ]

c) Get the filtered range - refer MSDN - Range.SpecialCells Method (Excel)[^]

Excel.Range filteredRange = sourceRange.SpecialCells(XLCellType.xlCellTypeVisible, XLSpecialCellsValue);

然后你会有一个名叫鲍勃的叔叔



亲切的问候

And then you will have an uncle named Bob

Kind Regards

这篇关于如何在Excel工作表上应用过滤器并将过滤后的数据复制到另一个Excel工作表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！