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.

Mon Feb 04, 2019 12:59 pm

Hi

I want to get the current range of excel and want to clear all cells with updated data.

I'm using below code while using Microsoft.Office.Interop.Excel
Excel.Range xlRange = xlWorksheet.Range["A1"].CurrentRegion;

Can you help me in this case.

Ravinderchauhan
 
Posts: 4
Joined: Mon Feb 04, 2019 6:32 am

Tue Feb 05, 2019 5:28 am

Hi,

Thanks for your inquiry.
Please refer to following code:
Code: Select all
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"E:\sample.xlsx");
            Worksheet sheet = workbook.Worksheets[0];
            //get all ranges in the worksheet
            CellRange cr = sheet.AllocatedRange;
            //clear all objects
            cr.ClearAll();
            workbook.SaveToFile("result.xlsx", FileFormat.Version2010);

If I misunderstand, please share more detailed information, such as input document and the desired file.

Sincerely,
Betsy
E-iceblue support team
User avatar

Betsy.jiang
 
Posts: 3099
Joined: Tue Sep 06, 2016 8:30 am

Tue Feb 05, 2019 5:58 am

Hi,

Thanks for your quick response.

Below code is work as UsedRange function of Interop.Excel
//get all ranges in the worksheet
CellRange cr = sheet.AllocatedRange;

But I want to work with CurrentRegion of the Range like this => Excel.Range xlRange = xlWorksheet.Range["A1"].CurrentRegion;

Ravinderchauhan
 
Posts: 4
Joined: Mon Feb 04, 2019 6:32 am

Tue Feb 05, 2019 6:18 am

Hi,

I have attached three image.

first image1 is my actual excel file.

second image2 is what I need.

third image3 is the result after I used AllocatedRange function
Attachments
Demo_SpireExcel_Image3.PNG
I got this result after using AllocatedRange
Demo_SpireExcel_Image3.PNG (18.76 KiB) Viewed 1404 times
Demo_SpireExcel_Image2.PNG
This result I need
Demo_SpireExcel_Image2.PNG (17.87 KiB) Viewed 1404 times
Demo_SpireExcel_Image1.PNG
Orignal Excel
Demo_SpireExcel_Image1.PNG (17.38 KiB) Viewed 1404 times

Ravinderchauhan
 
Posts: 4
Joined: Mon Feb 04, 2019 6:32 am

Tue Feb 05, 2019 7:13 am

Hi,

Thanks for your detailed information. Now I have understood your meaning.
Your requirement has been posted to our Dev team. Once there is any update, we will let you know.

Sincerely,
Betsy
E-iceblue support team
User avatar

Betsy.jiang
 
Posts: 3099
Joined: Tue Sep 06, 2016 8:30 am

Tue Feb 05, 2019 7:23 am

Okay Betsy,

Thanks again.

I hope you guys will find out my solution soon :) .

Ravinderchauhan
 
Posts: 4
Joined: Mon Feb 04, 2019 6:32 am

Tue Feb 05, 2019 7:34 am

Hi,

At present, we are on Chinese New Year Holiday(from 04/02/2019 to 10/02/2019). Once our Dev team are back to office, they will investigate your question.
If there is any news, we will let you know. Thanks for your understanding.

Sincerely,
Betsy
E-iceblue support team
User avatar

Betsy.jiang
 
Posts: 3099
Joined: Tue Sep 06, 2016 8:30 am

Wed Aug 17, 2022 9:38 am

Hi,

I'm glad to inform you that your requirement has been implemented, please download the hotfix Spire.XLS Pack(Hotfix) Version:12.8.1 from the following link and use the code below to have a test.
Code: Select all
sheet.Range["A1"].CurrentRegion;

Website download link: https://www.e-iceblue.com/Download/download-excel-for-net-now.html
Nuget download link: https://www.nuget.org/packages/Spire.XLS/12.8.1

Sincerely,
Nina
E-iceblue support team
User avatar

Nina.Tang
 
Posts: 1182
Joined: Tue Sep 27, 2016 1:06 am

Return to Spire.XLS