Ideally, according to the documentation, DeleteBlankRows() should delete only rows which are empty i.e. the rows which does not have any content, images, charts, comments etc…
But in the attached file below, Even the rows that have objects i.e. image is getting deleted.
TEstTrail.zip (243.2 KB)
@shreyap
By testing on the latest version v24.8 using sample files and the following sample code, we can obtain the correct results. Please refer to the attachment. out_net.zip (244.3 KB)
Workbook wb = new Workbook(filePath + "TEstTrail.xlsx");
wb.Worksheets[0].Cells.DeleteBlankRows();
wb.Save(filePath + "out_net.xlsx");
If you still have questions, please provide your sample code. If you can take a screenshot and highlight the error location, it will be very helpful for us to locate the issue. We will check it soon.
@John.He ,
Please compare the output file and my file. In my file, Line no. 143 has shifted to line no. 101 after delete operation. Ideally I would expect after line 100, 101-126 to get deleted(as they are empty), then 127-140 would be shifted above to 101-114.
Line 141,142 to be deleted and 143 to be shifted to 115
@shreyap
Deleting rows and deleting blank lines have different processing logic for shape and other graphic objects, resulting in different outcomes. We can reproduce the issue through testing. Found that rows covered by shapes are deleted when deleting blank rows.
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSNET-56659
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
@John.He ,
By when can I expect the fix?
As this is a major issue for us, faster resolution is appreciated.
Thanks in advance!
We kindly request a little time to thoroughly evaluate your issue. This will allow us to either resolve it promptly or provide an estimated time of resolution/completion if the issue is complex. Once we have any new information, we will update you accordingly.
For you requirement, we provide one new option for checking and deleting blank rows/columns:
DeleteBlankOptions.DrawingsAsBlank
For compatibility, by default it is true. That is, blank rows/columns covered by drawing objects will be deleted. To keep those rows/columns, please set this property as false:
...
wb.Worksheets[0].Cells.DeleteBlankRows(new DeleteBlankOptions() { DrawingsAsBlank = false });
...
Enclosed is the output by the new fix, please check whether it is the expected result. If yes, the fix will be included into our next official version 24.10 for you.
output.zip (243.7 KB)
@johnson.shi ,
Image is still at that position. When its background lines are shifted to top, I would expect the image also to be shifted to top.
I am sorry, but this option is of no help for me. I am expecting the image also to be shifted.
@shreyap Thank you for your feedback. We have implemented the requirements based on the previous description.
Based on your response, we have found that the requirements have changed. Would you like to provide your expected result file? We will further investigate your issue. We will check it soon.
Requirements haven’t changed. I was under an impression that it is an untold expectation.
I have attached a document below depicting various cases. Please refer it and let me know.
It is worth mentioning that DeleteColumns() is working exactly I wish for it to behave.
The only issue is with DeleteRows()
File.zip (203.9 KB)
Thanks!
Thanks for your feedback and sample file depicting various cases for your requirements. I have recorded with your file against your existing ticket “CELLSNET-56659” into our database. Please spare us little time and we will evaluate your requirements thoroughly and get back to you.
@amjad.sahi,
Is the issue resolved? I see that the status of the internal ticket you have created is in resolved state
Yes, the issue regarding DeleteBlankRows has been resolved sometime ago. We apologize for not informing you earlier. The feature was introduced in Aspose.Cells v24.10. We recommend trying the latest version, Aspose.Cells v25.2, as it includes additional enhancements and fixes too.
Please let us know if you encounter any issues or have further questions.
@shreyap
Sorry for any inconvenience caused to you. We can still reproduce the issue by testing on the latest version v25.2 using the following sample code. Rows covered by images are deleted when calling Cells.DeleteBlankRows(). Please refer to the attachment. out_net.zip (206.1 KB)
Workbook wb = new Workbook(filePath + "File.xlsx");
wb.Worksheets[0].Cells.DeleteBlankRows();
wb.Save(filePath + "out_net.xlsx");
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSNET-57838
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
Please change the line:
wb.Worksheets[0].Cells.DeleteBlankRows();
with:
wb.Worksheets[0].Cells.DeleteBlankRows(new DeleteBlankOptions() { DrawingsAsBlank = false });
it should work for the purpose.
@amjad.sahi ,
I used your approach to delete empty rows and columns. To some extent I was able to avoid the overlapping, but some extra rows are getting overlapped with the image. Attaching the excel file and output file for reference.
BYACCOUNT0.zip (423.2 KB)
I expect the output to be of the below format
Output.pdf (1.1 MB)
The placement type of the shape is Move and don’t size with cells.
So if rows or columns are deleted , the size of the shape should not be changed.
Please change placement type as Move and size with cells in MS Excel or try the following codes:
shape.Placement = PlacementType.MoveAndSize;
I tried this method, it does not work. My image is still overlapping few rows.
By referring to the solution provided by @simon.zhao and testing it on v25.3 using the following sample code, we can obtain the correct results. Please refer to the attachment. out_net.zip (206.0 KB)
Workbook wb = new Workbook(filePath + "File.xlsx");
ShapeCollection shapes = wb.Worksheets[0].Shapes;
int shapeCount = shapes.Count;
for (int i = 0; i < shapeCount; i++)
{
Shape shape = shapes[i];
shape.Placement = PlacementType.MoveAndSize;
}
wb.Worksheets[0].Cells.DeleteBlankRows();
wb.Save(filePath + "out_net.xlsx");
Would you like to try it on v25.3? If you still have questions, please provide your sample code and result file. We will check it soon.