Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Tue Mar 22, 2022 11:50 am

Hello Everyone,

I am trying to filter an excel sheet in c# which works fine. But I want to copy the filtered data into a new worksheet so that the unfiltered data is not included in the new worksheet.
Whenever I use the CopyFrom method or something similiar the autofilters get copied aswell into the new worksheet. Is it possible to copy only the filtered data into the new worksheet without the filters turned on? (see attachments for clarrification. First.png is the first worksheet. Second.png is the filtered data that will be copied from the first worksheet without the filters turned on)

If it is possible to delete the unfiltered data in the first worksheet then that would also be great. I already tried this with for loops but my file is simply too big and therefore it takes too much time. This is the reason why I want to use the autofilters. Any help will be much appreciated
Attachments
second.png
Second worksheet
second.png (2.02 KiB) Viewed 480 times
first.png
First worksheet
first.png (6.85 KiB) Viewed 480 times

pietjepuck
 
Posts: 1
Joined: Tue Mar 22, 2022 11:42 am

Wed Mar 23, 2022 10:07 am

Hello,

Thank you for your inquiry.
The following code could achieve your requirement, please give it a try. If there is still any question, please feel free to contact us.

Code: Select all
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(fileName);
            //Get the first worksheet
            Worksheet worksheet = workbook.Worksheets[0];
            //Loop through rows and delete hidden row
            for (int i = 1; i <= worksheet.LastRow; i++)
            {
                if (worksheet.GetRowIsHide(i))
                {
                    worksheet.DeleteRow(i);
                    i--;
                }
            }
            //Add a new worksheet
            Worksheet newSheet = workbook.Worksheets.Add("copy");
            //Copy dataranges
            worksheet.Copy(worksheet.Range["A1:C3"], newSheet, 1, 1);
            //Save document
            workbook.SaveToFile("result.xlsx", ExcelVersion.Version2013);


Sincerely,
Kylie
E-iceblue support team
User avatar

kylie.tian
 
Posts: 412
Joined: Mon Mar 07, 2022 2:30 am

Tue Apr 12, 2022 9:54 am

Hi,

Hope you are doing well!
Has your problem been effectively solved now? Can you give us some feedback at your convenience?

Sincerely,
Kylie
E-iceblue support team
User avatar

kylie.tian
 
Posts: 412
Joined: Mon Mar 07, 2022 2:30 am

Return to Spire.XLS