Checkboxes preventing save as PDF - how to remove them?

Aspose.Cells Product Family
1

Hi


If I try to do a simple open and:

wb.Save(stream, Aspose.Cells.SaveFormat.Pdf);

On the attached it hangs, I’ve worked out the checkboxes are the issue.

So I’m trying to nuke all checkboxes. Only other way to get the save to work currently is to run:

ws.RemoveAllDrawingObjects();

before the save.

However I may want to keep images.

I tried:

for (int cb = 0; cb < ws.CheckBoxes.Count(); cb++)
{
ws.CheckBoxes.RemoveAt(cb);
}

But that didn’t work either, if I open up the spreadsheet in Excel and run the following VBA (I found online):

Sub RemoveCheckboxes()
On Error Resume Next
ActiveSheet.CheckBoxes.Delete
Selection.FormatConditions.Delete
End Sub

Then save and try again the save as pdf works but I know we can’t run VBA in Aspose Cells.

So basically trying to find out how to properly delete the check boxes so the attached spreadsheet will save as a pdf.

Thanks

Simon
2

Hi,

Thanks for your posting and using Aspose.Cells.

We were able to replicate this issue by using the following sample code with the latest version:
Aspose.Cells for .NET 8.7.1. The code throws exception while converting excel file into pdf.

We have logged this issue in our database for investigation. We will look into it and fix this issue. Once the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as

  • CELLSNET-44265 - Source excel file is failing to convert to pdf and throwing out of memory exception

I have also shown the exception for a reference.

C#
string filePath = @“F:\Shak-Data-RW\Downloads\failingspreadsheet.xlsx”;

Workbook workbook = new Workbook(filePath);
workbook.Save(filePath + “.out.pdf”);

Exception:
Aspose.Cells.CellsException was unhandled
Message=“Exception of type ‘System.OutOfMemoryException’ was thrown.”
Source=“Aspose.Cells”
StackTrace:
at Aspose.Cells.Workbook.Save(String fileName, SaveOptions saveOptions)
at Aspose.Cells.Workbook.Save(String fileName)
at AsposeCells.Program.f1() in F:\Shak-Data-RW\Aspose\Libs\Projects\AsposeCells\Program.cs:line 50
at AsposeCells.Program.Main(String[] args) in F:\Shak-Data-RW\Aspose\Libs\Projects\AsposeCells\Program.cs:line 29
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException:

3

Hi


Thanks, assume there is no way programatically with the API to remove the check boxes?

Simon
4

Hi Simon,


You can certainly remove the Check Boxes using the CheckBoxCollection.RemoveAt method, however, for this particular spreadsheet, the save operation is taking too much time & memory even after removing the Check Boxes therefore it isn’t the solution. I am afraid, you have to wait till we complete the analysis of the scenario to suggest a workaround or solution for it.
5

Hi


Interesting given that the VBA to remove check boxes does solve the issue and the new check box free spreadsheet processes without issue.

I look forward to a fix :slight_smile:

Thanks

Simon
6

Hi,

Thanks for your valuable feedback and using Aspose.Cells.

It is good to know that you have found the temporary solution. We will keep looking into main issue and provide you a fix for it asap. Also we have logged your comment in our database for product team consideration. Have a good day.

7

Hi,

Thanks for using Aspose.Cells.

Please try the following code to remove all Check Boxes and see if your issue is resolved or not.

C#

Workbook wb = new Workbook(srcFile);

ShapeCollection shapes = wb.Worksheets[0].Shapes;

for (int i = shapes.Count -1; i >= 0; i–)
{
if (shapes[i] is CheckBox)
{
shapes.RemoveAt(i);
}
}

8

Hi


Thanks, this appears to work and removes the checkboxes as desired, many thanks.

Simon
9

Hi,


Good to know that your issue is figured out by the suggested code. I think you may try to use this workaround to sort out your issue for now and we will continue to work on your original issue logged as “CELLSNET-44265”.

Thank you.
10

Hi,

Thanks for using Aspose.Cells.

There are total of 37,213,122 pages in excel Print Preview if checkboxes are not removed. These are too many pages for rendering. It will cost too much memory and cause out of memory exception while rendering. So it is not a bug but a normal behavior.

Please see the attached screenshot for your reference that shows the number of pages in Excel Print Preview.