Spire.XLS for .NET is a professional Excel .NET library that can be used to any type of .NET( C#, VB.NET, ASP.NET, .NET Core) application and Java (J2SE and J2EE) application.

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: 3067
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 469 times
Demo_SpireExcel_Image2.PNG
This result I need
Demo_SpireExcel_Image2.PNG (17.87 KiB) Viewed 469 times
Demo_SpireExcel_Image1.PNG
Orignal Excel
Demo_SpireExcel_Image1.PNG (17.38 KiB) Viewed 469 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: 3067
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: 3067
Joined: Tue Sep 06, 2016 8:30 am

Return to Spire.XLS