Cells.DeleteBlankColumns() removes embedded-objects / office-files

kgk2000 · June 27, 2017, 11:11pm

Hello.

We are using this function on a workbook before converting it to PDF:

private static void DeleteBlankColumnsAndRows(Workbook workbook)

{

var options = new DeleteOptions

{

UpdateReference = true,

};
<span style="color:blue;">foreach</span> (<span style="color:blue;">var</span> sheet <span style="color:blue;">in</span> workbook.Worksheets)
{
    sheet.Cells.DeleteBlankColumns(options);
    sheet.Cells.DeleteBlankRows(options);
}

}

It has one problem though.

When the XLS file contains embedded objects like an embedded PowerPoint slide, this code seems to remove the cells where this object sits on.

The result is that the final PDF does not contain the view the embedded object.

Is there any workaround for that?

Note: I have tested with the latest version of Aspose Cells.

Thanks a lot,

Kostas

Amjad_Sahi · January 2, 2021, 6:48pm

Hi,

Thanks for providing sample code segment and details.

Please try our latest fix/version:

Aspose.Cells for .NET v17.5.4 (.NET 2.0) compiled in .NET Framework 2.0.

Aspose.Cells for .NET v17.5.4 (.NET 4.0) compiled in .NET Framework 4.0.

I have tested your scenario/case a bit using the following sample code with a simple template XLS file (attached) using v17.5.4, it works fine and the embedded powerpoint slide object is retained:

e.g

Sample code:

<span style=“font-size:
10.0pt;font-family:“Courier New”;mso-no-proof:yes”>Aspose.Cells.Workbook workbook = new
Aspose.Cells.Workbook(“e:\test2\Bk_deleteblankrowscols1.xls”);<o:p></o:p>

Worksheet worksheet = workbook.Worksheets[0];

DeleteOptions options = new DeleteOptions();

options.UpdateReference = true;

foreach (Worksheet sheet in workbook.Worksheets)

{

sheet.Cells.DeleteBlankColumns(options);

sheet.Cells.DeleteBlankRows(options);

}

<span style=“font-size:10.0pt;line-height:115%;font-family:“Courier New”;
mso-fareast-font-family:“MS Mincho”;mso-fareast-theme-font:minor-fareast;
mso-ansi-language:EN-SG;mso-fareast-language:JA;mso-bidi-language:AR-SA;
mso-no-proof:yes”>
workbook.Save(“e:\test2\out1.xls”);

If you still find the issue with v17.5.4, please provide us your template file and output file(s) with your sample code (runnable) to reproduce the issue on our end, we will check it soon.

Thank you.

kgk2000 · June 27, 2017, 11:11pm

Hi there!

Thanks for the quick response.

I have some new details that I hadn’t noticed before:

In order for this to happen you must hide the cells around the embedded object.

Please try again with the attached file and let me know how it goes.

Kostas

Amjad_Sahi · June 27, 2017, 11:11pm

Hi,

Thanks for the template file.

After an initial test, I observed the issue as you mentioned by using my sample code with your template file. I found that Cells.DeleteBlankColumns() removes embedded objects in the worksheets, the embedded OLE Objects are removed (in the first two worksheets) in the output file.

I have logged a ticket with an id “CELLSNET-45393” for your issue. We will look into it soon.

Once we have an update on it, we will let you know here.

Thank you.

shakeel.faiz · June 27, 2017, 11:11pm

Hi,

Thanks for using Aspose.Cells.

This is to inform you that we have fixed your issue CELLSNET-45393 now. We will soon provide the fix after performing QA and including other enhancements and fixes.

Amjad_Sahi · June 27, 2017, 11:11pm

Hi,

Please try our latest version/fix: Aspose.Cells for .NET v17.5.6 (attached):

Aspose.Cells for .NET v17.5.6 (.NET 2.0)

Aspose.Cells for .NET v17.5.6 (.NET 4.0)

(Note: please choose any of the fixes for your underlying .NET framework version)

Your issue should be fixed in it.

Let us know your feedback.

Thank you.

kgk2000 · June 27, 2017, 11:11pm

Hello.

I have tried your attached `Aspose.Cells for .NET v17.5.6 (.NET 4.0)`, but it doesn't seem to fix anything unfortunately.

Here is the code I use and the Test.xls file is attached in my previous post:

var inFile = dir + “Test.xls”;

var outFile = dir + “Test.pdf”;

var xlsopt = new Aspose.Cells.LoadOptions(Aspose.Cells.LoadFormat.Excel97To2003);

var wb = new Aspose.Cells.Workbook(inFile, xlsopt);

DeleteBlankColumnsAndRows(wb);

wb.Save(dir + “Test_new.xls”);

var xlsxpdfso = new PdfSaveOptions(SaveFormat.Pdf) { PrintingPageType = PrintingPageType.IgnoreBlank };

wb.Save(outFile, xlsxpdfso);

Process.Start(outFile);

Both the new XLS and the PDF file are missing the embedded objects.

Amjad_Sahi · June 27, 2017, 11:11pm

Hi,

Thanks for your feedback.

I confirmed that the issue is there with v17.5.6. I tested your scenario/ case using your code, the output file is missing OLE Objects. I have reopened your ticket “CELLSNET-45393” now. We will look into it soon.

We are sorry for any inconvenience caused!

Amjad_Sahi · June 27, 2017, 11:12pm

Hi,

It is to inform you that we will provide you the fix in the next few days (2-4 or so) which will fix your issue precisely.

Keep in touch.

Thank you.

Amjad_Sahi · January 2, 2021, 6:41pm

Hi,

Please try our latest version/fix: Aspose.Cells for .NET v17.5.7:

Aspose.Cells for .NET v17.5.7 (.NET 2.0)

Aspose.Cells for .NET v17.5.7 (.NET 4.0)

(Note: please choose any of the above fixes for your underlying .NET framework version).

Your issue should be fixed in it.

Let us know your feedback.

Thank you

kgk2000 · June 27, 2017, 11:12pm

Hello again.

I have tested the .NET 4.0 version of your referenced libraries.

The issue in the resulting XLS seems fixed, but the PDF conversion is not so good yet.

In the generated PDF, the embedded object from “Sheet2” does not exist.

The scaling of the embedded object from “Sheet1” is not good either. It seems that it uses only the cell text contents and not the embedded objects as well, in order to calculate the scaling to be applied.

Use the attached file in this post (Test_SCALING.xls) to see the problem with the scaling of the embedded object in “Sheet1” when converting to PDF (after you remove the blank cells). Use my code from above.

Amjad_Sahi · June 27, 2017, 11:12pm

Hi,

Thanks for the sample file and details.

After an initial test, I observed the issue as you mentioned by using the following sample code with your newly attached template file. I found that an embedded object in Sheet2 missing while the scaling is wrong for another embedded object in Sheet1 in the output PDF file format. The issue might be caused by Cells.DeleteBlankColumns() method.

e.g
Sample code:

Aspose.Cells.Workbook workbook = new
Aspose.Cells.Workbook(“e:\test2\Test_SCALING.xls”);

Worksheet
worksheet = workbook.Worksheets[0];<o:p></o:p>

DeleteOptions options = new DeleteOptions();

options.UpdateReference = true;

foreach (Worksheet sheet in workbook.Worksheets)

{

sheet.Cells.DeleteBlankColumns(options);

sheet.Cells.DeleteBlankRows(options);

}

workbook.Save(“e:\test2\out1.pdf”);

I have logged a ticket with an id “CELLSNET-45413” for your issue. We will look into it soon.

Once we have an update on it, we will let you know here.

Thank you.

Amjad_Sahi · June 27, 2017, 11:12pm

Hi,

This is to inform you that we have fixed your issue CELLSNET-45413 now. We will soon provide the fix after performing QA and including other enhancements and fixes.

Thank you.

shakeel.faiz · January 2, 2021, 6:49pm

Hi,

Thanks for using Aspose.Cells.

Please download and try the following latest fix

Aspose.Cells for .NET v17.5.9 (.NET 2.0) compiled in .NET Framework 2.0.

Aspose.Cells for .NET v17.5.9 (.NET 4.0) compiled in .NET Framework 4.0.

and let us know your feedback.

kgk2000 · June 27, 2017, 11:12pm

Hi there!

I have tested the .NET 4.0 version of the attached library and the all problems seem fixed.

Thanks.

Amjad_Sahi · June 27, 2017, 11:12pm

Hi,

Good to know that your issue is sorted out by the new fix/version. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.

aspose.notifier · June 27, 2017, 11:11pm

The issues you have found earlier (filed as CELLSNET-45413;CELLSNET-45393) have been fixed in Aspose.Cells for .NET 17.6.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.