I guess this is because he is using a different file and code snippet. However, when I tested your sample app along with the template file from the “New folder1.zip” attachment, I encountered the issue.
@shreyap
By testing on the latest version v25.3 using the following sample code, we found that there are still differences between the result file and the expected result file. Please refer to the attachment. out_net.zip (392.5 KB)
Sorry for any inconvenience caused to you. We will continue to fix the issue and notify you promptly once there are updates.
The sample code as follows:
Workbook wb = new Workbook(filePath + "Testfile.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;
}
DeleteBlankOptions options = new DeleteBlankOptions();
options.DrawingsAsBlank = false;
wb.Worksheets[0].Cells.DeleteBlankRows(options);
wb.Save(filePath + "out_net.xlsx");
wb.Save(filePath + "out_net.pdf");
We are pleased to inform you that your issue (Ticket ID: “CELLSNET-57838”) has been resolved precisely. The fix/enhancement will be included in the next release (Aspose.Cells v25.4) scheduled for release in the first half of April 2025. You will be notified when the next version is released.
@amjad.sahi,
Glad to hear the update. It was a long pending task. Looking forward to integrate it. Please let me know the release timeline and any code changes that needs to be done in specific.
We plan to release 25.4 in two weeks and you will get notified here when it is published.
We changed the default behavor of shifting shapes when deleting blank rows above them. There is no need to change the code you used currently.
The issues you have found earlier (filed as CELLSNET-57838) have been fixed in this update. This message was posted using Bugs notification tool by leoluo
@johnson.shi , @amjad.sahi , @John.He , I installed the latest version, created a separate solution and processing logic. There the image does not seem to overlap with the content. But In my application it does not work.
Can you please help my why this inconsistency ?
Please provide us the code and template file to reproduce the issue so we can look into it. Thank you.
Please find attached the solution to replicate the issue. Can you please let us know if we are doing something wrong or is it a new issue?
deleteBlankRow.zip (377.4 KB)
We can obtain the correct results by testing on the latest version v25.5 using the following sample code. Please refer to the attachment. result.zip (585.9 KB)
Workbook wb = new Workbook(filePath + "input1.xlsx");
// Delete blank rows and columns, preserving shapes
DeleteBlankOptions options = new DeleteBlankOptions
{
DrawingsAsBlank = false
};
foreach (Worksheet sheet in wb.Worksheets)
{
sheet.Cells.DeleteBlankRows(options);
}
wb.Save(filePath + "out_net.xlsx");
However, when running the project you provided, Rows covered by images are deleted after copying range and calling Cells.DeleteBlankRows().
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-58425
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.
@nraj
Please copy default style, otherwise the row height is different when copying shapes, then the end row will be different too.
// Initialize converted workbook
Workbook converted = new Workbook(workBook.FileFormat);
//converted.FileFormat = ;
converted.CopyTheme(workBook);
converted.DefaultStyle = workBook.DefaultStyle;
As you want to copy all to another workbook, please simply use Workbook.Copy() method.
If you copy a chart to another workbook, the data source will be external reference as MS Excel.
If you want to remove all formulas, please use Cells.RemoveFormulas() method.
I have tested with different file and it did not work for the new file but it worked for the older file I have shared.
Please find the solution attached below
ExcelEmptyRow.zip (3.5 MB)
I tested your scenario/case using your sample app with Aspose.Cells for .NET v25.5 and the output Excel file seems OK to me. Please find attached the output Excel and PDF files for your reference.
out_net.zip (3.5 MB)
out_net.pdf (4.6 MB)
Please open the Excel/PDF file and highlight the problematic areas with notes for different sheets, so we could understand the problems and log appropriate ticket(s) for it. Also, it would be best if you could provide an Excel file with your expected results. We will check it soon.
Please find below the screenshots and files for differences
screenshots.zip (8.2 MB)
original file/screen shot has space between the rows - sheet100
output file/screenshot is overlapping the rows - sheet100.
This is the difference, as it should not overlap the rows after deleterows() and deletecolumns() and code given in the latest console application.
@nraj
We can obtain the correct results by testing on the latest version v25.5 using the following sample code. Please refer to the attachment. out_net.zip (3.5 MB)
Workbook wb = new Workbook(filePath + @"screenshots\original file.xlsx");
// Delete blank rows and columns, preserving shapes
DeleteBlankOptions options = new DeleteBlankOptions
{
DrawingsAsBlank = false
};
foreach (Worksheet sheet in wb.Worksheets)
{
sheet.Cells.DeleteBlankRows(options);
}
wb.Save(filePath + @"screenshots\out_net.xlsx");
We recommend you to kindly try using our latest version: Aspose.Cells for .NET v25.5.
If you still find the issue, kindly do share your complete sample (runnable) code to reproduce the issue on our end, we will check it soon.
In the attached below code it is giving exception that’s why I guess it is not working as per expected for me. Please find attached the solution to debug.
processing_error.zip (7.4 MB)
Thanks for the sample app and template files.
I tested your scenario/case using the exact project and found the issue/exception “Row number or column number cannot be zero” for “input5.xlsx” template file. Then, I analyzed the file “input5.xlsx” a bit. I noticed that for “SheetEmpty1” sheet there is no data or formatting set for it and only shapes/smart art and chart(s). That’s why both Worksheet.Cells.MaxRow and Worksheet.Cells.MaxColumn attributes are evaluated as “-1” which consequently results in the error because it can’t create the range. Please note if there is no data or formatting exists on the worksheet, MaxRow and MaxColumn would be evaluated as “-1”. I think you may simply add a check in “TransferCells” method accordingly.
Thanks for the suggestion,
Actually we are having the cells.count check in our code.
I figured out we have a method PrepareWorksheet(), we are seeing the issue getting reproduced when I have updated the method as we are doing some adhustment inside it.
Please find the attached solution to reproduce the issue of overlapping cell content with image in “Sheet100”
ExcelOverlappingImage.zip (3.9 MB)
Pleasefind below the screenshpt of the output
output_screenshot.zip (1.2 MB)
Thanks for the resource files.
Let us evaluate your issue in details using your resource files and then we will get back to you.
@nraj
By analyzing the sample code, we found that after removing empty rows and columns, the sample code adjusted the row height and column width. This is the reason for the overlap. Please adjust the row height and column width first, and then delete blank rows and columns. By testing with the following code, we can obtain the correct results. Please refer to the attachment. out_net.zip (3.5 MB)
private static void PostProcessWorksheet(PreFlightInfo preFlightInfo, Worksheet targetWorksheet, Worksheet sourceWorksheet)
{
// Ensures shapes are correctly positioned when deleting blank rows/columns
//ShapeCollection shapes = targetWorksheet.Shapes;
//int shapeCount = shapes.Count;
//for (int i = 0; i < shapeCount; i++)
//{
// Shape shape = shapes[i];
// shape.Placement = PlacementType.MoveAndSize;
//}
int totalRows, totalColumns;
DeleteBlankOptions options = new DeleteBlankOptions();
//Set UpdateReference property to true;
options.UpdateReference = true;
options.DrawingsAsBlank = false;
totalRows = targetWorksheet.Cells.MaxDataRow;
totalColumns = targetWorksheet.Cells.MaxDataColumn;
if (targetWorksheet.Cells.MaxDataColumn != totalColumns)
{
preFlightInfo.HasBlankColumns = true;
}
if (targetWorksheet.Cells.MaxDataRow != totalRows)
{
preFlightInfo.HasBlankRows = true;
}
// Adjust column widths and row heights
foreach (Column sourceColumn in sourceWorksheet.Cells.Columns)
{
if (sourceColumn.Width != 0)
{
targetWorksheet.Cells.Columns[sourceColumn.Index].Width = sourceColumn.Width;
}
}
foreach (Row sourceRow in sourceWorksheet.Cells.Rows)
{
if (sourceRow.Height != 0)
{
targetWorksheet.Cells.Rows[sourceRow.Index].Height = sourceRow.Height;
}
}
//We need to remove from both so adjusting height and width is correct
targetWorksheet.Cells.DeleteBlankColumns(options);
targetWorksheet.Cells.DeleteBlankRows(options);
sourceWorksheet.Cells.DeleteBlankColumns(options);
sourceWorksheet.Cells.DeleteBlankRows(options);
}
Hope helps a bit.