Perhaps I’m not fully understanding the usage of DeleteBlankRows but in my sample project I have a few rows filled with data and some not. When inspecting the rows there are counted 5, and several are marked as IsBlank = true and some are marked as IsBlank = false.
However by calling the DeleteBlankRows no row is being deleted. What am I missing here? If I’m correct the formatting of the rows should not be an issue for the DeleteBlankRows.
AsposeCells_DeleteBlankRows.zip (88.6 KB)
I tested your scenario/case using your template Excel file and Aspose.Cells is giving correct results. There are 5 initialized rows (1-5) and 7 initialized columns (A-G). Please note there are no blank rows in the sheet. If you think row3 and row5 are blank, you are wrong because some cells in those rows are merged (e.g., A3:B3, A5:B5), so these will not be counted to blank rows list and DeleteBlankRows won’t remove them. Similarly, column B is not blank because it is has merged cells, so it will not be counted to blank columns list and DeleteBlankColumns won’t remove it and rightly so.
Let us know if you still have any confusion or issue.
@hro_jordy
If a row contains merged area, it’s not a blank row.
Please remove merged area as the following codes:
Workbook workbook = new Workbook(dir +"Book3.xlsx");
workbook.Worksheets[0].Cells.RemoveFormulas();
workbook.Worksheets[0].Cells.ClearMergedCells();
···@amjad.sahi & @simon.zhao thank you both for the quick replies.
@amjad.sahi: When inspecting row3, it is marked as IsBlank = true, is this an bug perhaps or does the property IsBlank have nothing to do with the DeleteBlankRows method?
Also I’m not quite sure what the definition of a initialized row/column is. For example what is the difference with the following sheet?
Book1.xlsx.zip (7.8 KB)
@simon.zhao thank you for the suggestion. Calling ClearMergedCells seems to help DeletingBlankRows. However it’s unclear to me why these rows are marked as IsBlank = true, while they appear not to be?
Please see my (updated) reply (I posted previously) for your reference.
I tested using the following sample code with your newer file and it works as expected. See the comments with the lines for your reference:
e.g.
Sample code:
Workbook workbook = new Workbook("g:\\test2\\Book1.xlsx");
Console.WriteLine(workbook.Worksheets[0].Cells.Rows[0].IsBlank);//False - Ok
Console.WriteLine(workbook.Worksheets[0].Cells.Rows[1].IsBlank);//True - Ok
Console.WriteLine(workbook.Worksheets[0].Cells.Rows[2].IsBlank);//False - Ok
Console.WriteLine(workbook.Worksheets[0].Cells.Rows[3].IsBlank);//True - Ok
Console.WriteLine(workbook.Worksheets[0].Cells.Rows[4].IsBlank);//False - Ok
Int32 countRows = workbook.Worksheets[0].Cells.Rows.Count;
Console.WriteLine($"Initial row count: {countRows}");//Initial row count: 5
workbook.Worksheets[0].Cells.DeleteBlankRows();
countRows = workbook.Worksheets[0].Cells.Rows.Count;
Console.WriteLine($"After delete blanks row count: {countRows}");//After delete blanks row count: 3 (since there are two blank rows, after deleting those rows, count would 3
@amjad.sahi I should have mentioned that Book1 was working as expected, however Book3 (from the first post) is not. It seems the issue is the MergedCells as @simon.zhao mentioned, however also in Book3 the rows are marked IsBlank = true and IsBlank = false, while I should expect that they should all be marked IsBlank = false, as they are not removed because of the merged cells.
Book3
Console.WriteLine(workbook.Worksheets[0].Cells.Rows[0].IsBlank);//False - Ok
Console.WriteLine(workbook.Worksheets[0].Cells.Rows[1].IsBlank);//True - Ok
Console.WriteLine(workbook.Worksheets[0].Cells.Rows[2].IsBlank);//False - not ok, as it has merged cells
Console.WriteLine(workbook.Worksheets[0].Cells.Rows[3].IsBlank);//True - Ok
Console.WriteLine(workbook.Worksheets[0].Cells.Rows[4].IsBlank);//False - not ok, as it has merged cells
You are right. It seems IsBlank is not affected by merged cells while DeleteBlankRows method is affected. Anyways, we will evaluate it further and get back to you.
We will remove the check of merged cells for DeleteBlankRows method so that it can take one row with merged cells but without data as blank. However, for Row.IsBlank and DeleteBlankRows method, there are still some differences that need different logic.
For Row.IsBlank property, it only denotes whether one row has data(cell) or not, no matter whether there are some other objects.
For DeleteBlankRows method, it needs to take more scenarios into consideration. For example, if one row has no data but has some relation to other objects, such as a Comment for one cell in it, or it is part of the pivot table, then this row should not be deleted like one blank row.
As we told you we will include enhancements regarding DeleteBlankRows especially. 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-53333
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.
We are pleased to inform you that your issue has been resolved. The fix will be included in our upcoming release (Aspose.Cells v23.6) that we plan to release in the first half of June 2023. You will be notified when the next version is released.
@amjad.sahi & @johnson.shi thank you for describing the differences and updating the code. As both the property name and the method name are kind of similar, I would suggest to add these explanations to the documentation Delete Blank Rows and Columns in a Worksheet|Documentation or Cells.DeleteBlankRows | Aspose.Cells for .NET API Reference as it currently does not mention this. This would have helped me understand it earlier.
Thanks again for the support.
Thanks for your your suggestion.
We have noted it down and we will update the respective docs and APIs reference page(s) accordingly.
The issues you have found earlier (filed as CELLSNET-53333) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi