Used Range refers to a bounding rectangle of used cells that returns the IRange object of the used range on the specified worksheet.

DsExcel Java enables users to work with the already used range of cells in a worksheet in the following ways:

• Work with worksheet's used range
• Work with feature related used range
• Work with used range in selected range

Work with worksheet's used range

While working with the worksheet's used range, the first step is to get the used range by using the getUsedRange method of the IWorksheet interface. As a second step, you can use the methods of the IRange interface to further customize the used range as per your preferences.

In order to get used range and customize it, refer to the following example code.

worksheet.getRange("H6:M7").setValue(1);
worksheet.getRange("J9:J10").merge();

// UsedRange is "H6:M10"
String usedrange = worksheet.getUsedRange().toString();
        
// Customize the used range 
usedRange.getInterior().setColor(Color.GetLightBlue());

Work with feature related used range

While working with the feature related used range, the first step is to get the feature related used range by using the getUsedRange method of the IWorksheet interface. As a second step, you can customize the feature related used range using the methods of the IRange interface.

In order to get feature related used range and customize it, refer to the following example code.

IComment commentA1 = worksheet.getRange("A1").addComment("Range A1's comment");
IComment commentA2 = worksheet.getRange("A2").addComment("Range A2's comment");
        
// Comment used range is "A1:D5", contains comment shape plot area
EnumSet<UsedRangeType> usedRangeTypes = EnumSet.of(UsedRangeType.Comment);
String commentsUsedRange = worksheet.getUsedRange(usedRangeTypes).toString();
System.out.println(commentsUsedRange);
        
worksheet.getRange("A1:B2").setValue(new Object[][] { { 1, 2 }, { "aaa", "bbb" } });
worksheet.getRange("A2:C3").getInterior().setColor(Color.GetGreen());
        
// Customize the feature related used range by applying style - used range is A2:C3.
IRange usedRange_style = worksheet.getUsedRange(EnumSet.of(UsedRangeType.Style));
usedRange_style.getInterior().setColor(Color.GetLightBlue());
System.out.println(usedRange_style);

Work with used range in selected range

To work with?used range in a selected range, you can use getUsedRange method and getRange method of the IRange interface. Once fetched, you can customize the used range using the methods of the IRange interface.

// Init data.
IRange range = worksheet.getRange("B2:D4");
range.setValue("Unused");

// Select range.
IRange selectedRange = worksheet.getRange("C3:E5");
selectedRange.select();

// Get the used range from selectedRange.
IRange usedRange = selectedRange.getUsedRange();
usedRange.setValue("Used");
usedRange.getInterior().setColor(Color.GetLightBlue());

To view the code in action, see Used Range in selected range demo sample.

After getting the used range of cells with the help of any of the above two methods, you can customize the used range as per your preferences. For example- you can set the row height and column width; change the row hidden and column hidden settings; execute essential tasks like group and merge operations; insert values, formulas and comments to the used range in your spreadsheet, as and when required.