When deleting a named range and shifting cells up, the width of the is changing. Is there a way to fix this?

Aspose.Cells Product Family
1

Hi,
I’m trying to delete a named range in excel and shifting the cells up. When this is done, the height of the row is reducing and the text become partially visible. Is there a way to prevent this from happening.

Here’s what it looks like
Screenshot 2023-11-02 155110.png (8.4 KB)

The original template looks like this:
Screenshot 2023-11-02 155311.png (37.6 KB)

After removing the first table on top, and pushing the remaining up, this happens.

2

@JThomas98
By creating sample files and testing with Aspose.Cells 23.10, we can obtain the correct results. Please refer to the attachment (12.6 KB).

The sample code as follows:

Workbook book = new Workbook(filePath + "sample.xlsx");
Worksheet sheet = book.Worksheets[0];
Cells cells = sheet.Cells;
Range r = book.Worksheets.GetNamedRanges()[0];
cells.DeleteRange(r.FirstRow, r.FirstColumn, r.RowCount + r.FirstRow - 1, r.ColumnCount + r.FirstColumn -1, ShiftType.Up);

book.Save(filePath + "out_net.xlsx");

If you still have any questions or confusion, please provide your sample files and test code, which will be very helpful for us to locate the issue.

3

@JThomas98
In the example file given earlier, the row height of the two rows in the deleted area is automatic, so deleting the area will change the row height. If the row height is not automatic, the row height will not change after deleting the area.

When deleting a row, all information about the deleted row will be deleted, and subsequent rows will replace the current row. When deleting an area, if the height of the deleted row is automatic, the height of the replaced cells will change the height of the current row. In short, deleting an area only moves data and does not copy row height.

After testing, when deleting rows and regions, regardless of whether the row height is automatic or not, the results of Aspose.Cells are consistent with the operation results in Excel.

In addition, you can directly delete the area in Excel and check if it matches the results deleted through the program. You can also call the Worksheet.AutoFitRow method to autofit the row height.

4

Hi @John.He,
Thank you for the instant reply and sorry about my late reply.
What I’m trying to do is this:
Here’s a template that I’m using:
Template&Output.zip (61.5 KB)

The above attachment has both the template file called AAtemplate_ReportCards_Practice and the output called Output.

What I’ve done is delete the name range PCP_ReportCard using my function:

public virtual bool DeleteRange(Workbook excelWorkBook, Worksheet excelWorkSheet, string excelNamedRange, bool checkWB = false)
{
bool RetVal = false;
try
{
Cells cells = excelWorkSheet.Cells;
var result = checkWB ? this.RangeExists(excelWorkBook, excelNamedRange) : this.RangeExists(excelWorkSheet, excelNamedRange);
if (result)
{
Range ExcelRange = checkWB ? excelWorkBook.Worksheets.GetRangeByName(excelNamedRange) : excelWorkSheet.Workbook.Worksheets.GetRangeByName(excelWorkSheet.Name + “!” + excelNamedRange);
cells.DeleteRange(ExcelRange.FirstRow, ExcelRange.FirstColumn, ExcelRange.FirstRow + ExcelRange.RowCount-1, excelWorkSheet.Cells.Columns.Count -1, ShiftType.Up);
RetVal = true;
}
}
catch (Exception ex)
{
throw ex;
}
return RetVal;
}

5

@JThomas98,

Thanks for the sample files and further details.

I tested your scenario/case manually (I did select the range (of cells) and “Delete” it with shift cells up option) in MS Excel and found the same issue as you mentioned. Since there are some merged ranges of cells, so you need to autofit-rows including/with merged cells areas (enabled) option. See the lines of code (especially the lines in bold) for your reference. I tested the sample code and it works fine:
e.g.
Sample code:

Workbook book = new Workbook(“g:\test2\AAtemplate_ReportCards_Practice.xlsx”);
Worksheet sheet = book.Worksheets[0];
Cells cells = sheet.Cells;
Aspose.Cells.Range r = book.Worksheets.GetNamedRanges()[1];//PCP_ReportCard
cells.DeleteRange(r.FirstRow, r.FirstColumn, r.RowCount + r.FirstRow - 1, r.ColumnCount + r.FirstColumn -1, ShiftType.Up);

AutoFitterOptions options = new AutoFitterOptions();
options.AutoFitMergedCellsType = AutoFitMergedCellsType.EachLine;
sheet.AutoFitRows(options);

book.Save(“g:\test2\out1.xlsx”);

Hope, this helps a bit.

6

@amjad.sahi ,
Thank you, this has fixed it, I just had to adjust the range to which AutoFitRows was applied to. Thanks again for the quick reply!

7

@JThomas98
You are welcome. Thank you for your feedback. I’m glad your issue has been resolved. If you have any questions, please feel free to contact us.