Click here to Skip to main content
15,906,467 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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");
}
Posted
Updated 15-Jun-16 11:50am

1 solution

I haven't tested this myself but my understanding is;
a) Get the Worksheet Used Range
C#
curWorkBook = app.Workbooks.Open("c:\\SyntBotsExecutionReport.xlsm");
workSheet = (Worksheet)curWorkBook.Sheets[2];
Excel.Range sourceRange = workSheet.UsedRange;

b) Apply the filter - Refer MSDN - Range.AutoFilter Method (Excel)[^]
C#
sourceRange.AutoFilter(<columnnumber1>, <crteriastring1>, <operator>, <columnnumber2>, <criteriastring2>);</criteriastring2></columnnumber2></operator></crteriastring1></columnnumber1>

c) Get the filtered range - refer MSDN - Range.SpecialCells Method (Excel)[^]
C#
Excel.Range filteredRange = sourceRange.SpecialCells(XLCellType.xlCellTypeVisible, XLSpecialCellsValue);


And then you will have an uncle named Bob

Kind Regards
 
Share this answer
 
Comments
Member 10628830 26-Mar-18 1:01am    
Hi thank your for your suggestion as well as question. I have also worked on same its working fine.

Let me tell please how the data from source file is copied in destination without making anew sheets. mean while source file contain 2 sheets & I want to copy all data from these files to new destination file in single sheet only one below one

Thanks in advance

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900