Issues with OLE embedding Excel into Word

[leofoxr1]

I am using a simple program to evaluate OLE embed functionality, and having some issues with the behavior of the embedded Excel.
Screenshot 1 demonstrates the generated docx file, which looks ok, however double clicking the embed to edit brings up an incorrectly sized OLE demonstrated by Screenshot 2. And then pressing escape or clicking out results in a messed up image in Screenshot 3.
Is there a way to fix the OLE "popup" size so it displays the correct part of the spreadsheet?
The code I am using to test below:

static void Main(string[] args)
{
using var file = System.IO.File.Open(@"C:\Projects\WordTest\sample.xlsx", FileMode.Open, FileAccess.Read);
var workbook = new Workbook();
workbook.LoadFromStream(file);

var sheet = workbook.Worksheets[0];
sheet.PageSetup.TopMargin = 0;
sheet.PageSetup.RightMargin = 0;
sheet.PageSetup.BottomMargin = 0;
sheet.PageSetup.LeftMargin = 0;

using var imgstream = new MemoryStream();
sheet.ToEMFStream(imgstream, 0, 0, sheet.LastRow, sheet.LastColumn);

var doc = new Document();
var section = doc.AddSection();
var paragraph = section.AddParagraph();
var pic = new DocPicture(doc);
pic.LoadImage(imgstream);
pic.Width = 490f;
using var outputStream = new MemoryStream();
var ole = doc.LastParagraph.AppendOleObject(file, pic, Spire.Doc.Documents.OleObjectType.ExcelWorksheet);

doc.SaveToFile(@"C:\Projects\WordTest\sample.docx", Spire.Doc.FileFormat.Docx);
}

[Brian.Li]

Hello,

Thanks for your inquiry.
In fact, our Spire.XLS provides a method "workbook.SetOleSize();" to set the ole size. Please refer to the following code to meet your needs.

Code: Select all

            var file = System.IO.File.Open(@"C:\Projects\WordTest\sample.xlsx", FileMode.Open, FileAccess.Read);
            var workbook = new Workbook();
            workbook.LoadFromStream(file);

            var sheet = workbook.Worksheets[0];
            sheet.PageSetup.TopMargin = 0;
            sheet.PageSetup.RightMargin = 0;
            sheet.PageSetup.BottomMargin = 0;
            sheet.PageSetup.LeftMargin = 0;

            var imgstream = new MemoryStream();
            sheet.ToEMFStream(imgstream, 0, 0, sheet.LastRow, sheet.LastColumn);
            workbook.SetOleSize(sheet.FirstRow, sheet.FirstColumn, sheet.LastRow, sheet.LastColumn);
            var outputStream = new MemoryStream();
            workbook.SaveToStream(outputStream);

            var doc = new Document();
            var section = doc.AddSection();
            var paragraph = section.AddParagraph();
            var pic = new DocPicture(doc);
            pic.LoadImage(imgstream);
            pic.Width = 490f;
           
            var ole = doc.LastParagraph.AppendOleObject(outputStream, pic, Spire.Doc.Documents.OleObjectType.ExcelWorksheet);

            doc.SaveToFile(@"C:\Projects\WordTest\sample.docx", Spire.Doc.FileFormat.Docx);

If this cannot help you, please provide your input file and your desired output. You can send them to us (support@e-iceblue.com) via email. Thanks in advance for your assistance.

Sincerely,
Brian
E-iceblue support team

[Brian.Li]

Hello,

Greetings from E-iceblue!
Did the code we provided work for you? Any feedback will be greatly appreciated.

Sincerely,
Brian
E-iceblue support team

[leofoxr1]

Well using
workbook.SetOleSize(sheet.FirstRow, sheet.FirstColumn, sheet.LastRow, sheet.LastColumn);
seems to do something, but I can't see the result because the evaluation version creates a new sheet for any workbook that it processes, so I can't say that it works correctly. I've attached a video demonstrating the issue. Any suggestions?

[Brian.Li]

Hello,

Thanks for your feedback.
For the commercial version of Spire.XLS, if you do not apply a valid license in the application, a worksheet with warning messages will be automatically generated. And my colleague has sent a temporary license (one month free) to your register email to help you evaluate our commercial edition better and remove the warning message. Please refer to this tutorial to apply the license: How to Apply the License by license key.

Sincerely,
Brian
E-iceblue support team

[leofoxr1]

Thanks for the quick response regarding a trial license. I am still having some issues with the sizing of the OLE object when opened vs the size of the initial embed. Attached is a video of the behavior. How can I make the initial embedded image be consistent with what's shown when the OLE object is opened?

Here is my current code:
static void TestSpire()
{
using var file = System.IO.File.Open(@"C:\Projects\WordTest\sample.xlsx", FileMode.Open, FileAccess.Read);
var workbook = new Workbook();
workbook.LoadFromStream(file);

var sheet = workbook.Worksheets[0];
sheet.PageSetup.TopMargin = 0;
sheet.PageSetup.RightMargin = 0;
sheet.PageSetup.BottomMargin = 0;
sheet.PageSetup.LeftMargin = 0;

using var imgstream = new MemoryStream();
sheet.ToEMFStream(imgstream, 0, 0, sheet.LastRow, sheet.LastColumn, System.Drawing.Imaging.EmfType.EmfPlusDual);
workbook.SetOleSize(sheet.FirstRow, sheet.FirstColumn, sheet.LastRow, sheet.LastColumn);
using var outputStream = new MemoryStream();
workbook.SaveToStream(outputStream);

var doc = new Document();
var section = doc.AddSection();
var paragraph = section.AddParagraph();
var pic = new DocPicture(doc);
pic.LoadImage(imgstream);
pic.Width = 490f;

var ole = doc.LastParagraph.AppendOleObject(outputStream, pic, Spire.Doc.Documents.OleObjectType.ExcelWorksheet);

doc.SaveToFile(@"C:\Projects\WordTest\sample.docx", Spire.Doc.FileFormat.Docx);
}

[Brian.Li]

Hello,

Thanks for your feedback.
Please set the size of OLE with the following code.

Code: Select all

            ole.Height = pic.Height;
            ole.Width = pic.Width;

If this cannot meet your needs. Please provide your input file and your desired output for further investigation. You can send them to us (support@e-iceblue.com) via email. Thanks in advance.

Sincerely,
Brian
E-iceblue support team

[leofoxr1]

I've added
ole.Height = pic.Height;
ole.Width = pic.Width;
the result however is the same as before, demonstrated in the video I provided. Here is the full code:

Code: Select all

static void TestSpire()
        {
            using var file = System.IO.File.Open(@"C:\Projects\WordTest\sample.xlsx", FileMode.Open, FileAccess.Read);
            var workbook = new Workbook();
            workbook.LoadFromStream(file);

            var sheet = workbook.Worksheets[0];
            sheet.PageSetup.TopMargin = 0;
            sheet.PageSetup.RightMargin = 0;
            sheet.PageSetup.BottomMargin = 0;
            sheet.PageSetup.LeftMargin = 0;

            using var imgstream = new MemoryStream();
            sheet.ToEMFStream(imgstream, 0, 0, sheet.LastRow, sheet.LastColumn, System.Drawing.Imaging.EmfType.EmfPlusDual);
            workbook.SetOleSize(sheet.FirstRow, sheet.FirstColumn, sheet.LastRow, sheet.LastColumn);
            using var outputStream = new MemoryStream();
            workbook.SaveToStream(outputStream);

            var doc = new Document();
            var section = doc.AddSection();
            var paragraph = section.AddParagraph();
            var pic = new DocPicture(doc);
            pic.LoadImage(imgstream);
            pic.Width = 490f;

            var ole = doc.LastParagraph.AppendOleObject(outputStream, pic, Spire.Doc.Documents.OleObjectType.ExcelWorksheet);
            ole.Height = pic.Height;
            ole.Width = pic.Width;

            doc.SaveToFile(@"C:\Projects\WordTest\sample.docx", Spire.Doc.FileFormat.Docx);
        }

[Brian.Li]

Hello,

Thanks for your response.
So you want the window that opens after clicking on the ole object to be exactly the same size as the ole image? I'm sorry our Spire.Doc does not support that. If you have further questions, just feel free to contact us.

Sincerely,
Brian
E-iceblue support team

[leofoxr1]

I've been looking into ways to work around this and it seems that the issue is the zoom level of the sheet when it's opened via OLE from Word.
I have added the following to my code:
sheet.ZoomScaleNormal = 80;

What happens now is that when the OLE is opened the zoom level is briefly set to 80% as expected, but then resets back to 100% a split second later. Is there a way to lock/persist the zoom level when the OLE object is opened?

[Brian.Li]

Hello,

Thanks for your response.
Our product is based on Microsoft Office. I tried to create an Excel file using MS Excel and set its zoom level to 80%, and then use MS Word to insert it to a Word file, but the zoom level cannot be locked. Sorry our product cannot do it either. Hope you can understand.

Sincerely,
Brian
E-iceblue support team

[leofoxr1]

That's not the scenario that I am talking about.

If you manually resize the image of an OLE Excel embedded in a Word document, when you double click the image to open the Excel, it will open at the appropriate zoom level. So clearly there is a way to specify the zoom level in the OOXML.

[Brian.Li]

Hello,

Thanks for your inquiry.
We are sorry that our Spire.Doc do not support this feature you describe. But I will post this requirement to our Dev team, and they will further investigate whether this feature can be implemented. If there is any progress, we will notify you.

Sincerely,
Brian
E-iceblue support team