I am evaluating the capabilities of embedding Excel into Word and having some issues.
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?
static void TestAspose()
{
//using var file = System.IO.File.Open(@"C:\Projects\WordTest\sample.xlsx", FileMode.Open, FileAccess.Read);
var book = new Aspose.Cells.Workbook(@"C:\Projects\WordTest\sample.xlsx");
var sheet = book.Worksheets[0];
sheet.PageSetup.LeftMargin = 0;
sheet.PageSetup.RightMargin = 0;
sheet.PageSetup.TopMargin = 0;
sheet.PageSetup.BottomMargin = 0;
var options = new Aspose.Cells.Rendering.ImageOrPrintOptions
{
OnePagePerSheet = true,
ImageType = Aspose.Cells.Drawing.ImageType.Emf
};
var sr = new Aspose.Cells.Rendering.SheetRender(sheet, options);
var imgStream = new MemoryStream();
sr.ToImage(0, imgStream);
var document = new Aspose.Words.Document();
var builder = new Aspose.Words.DocumentBuilder(document);
var shape = builder.InsertOleObject(@"C:\Projects\WordTest\sample.xlsx", "Excel.Sheet.12", false, false, imgStream);
var of = shape.OleFormat;
of.AutoUpdate = true;
shape.AspectRatioLocked = true;
shape.Width = 600;
document.Save(@"C:\Projects\WordTest\sample.docx");
}
We have tested the scenario and managed to reproduce the same issue at our side. For the sake of correction, we have logged this problem in our issue tracking system as WORDSNET-22615. You will be notified via this forum thread once this issue is resolved.
We try our best to deal with every customer request in a timely fashion, we unfortunately cannot guarantee a delivery date to every customer issue. We work on issues on a first come, first served basis. We feel this is the fairest and most appropriate way to satisfy the needs of the majority of our customers.
Currently, your issue is under analysis phase. Once we complete the analysis of your issue, we will then be able to provide you an estimate.
Could you please share some more detail about your requirement?
Well the bug in this case is that the OLE object has an incorrect size. I was just asking if it’s possible to manually control its size as a potential workaround for this issue.
The OLE object is imported into Shape node. You can use Shape.Width and Shape.Height properties to change its size. However, the issue will still same after opening the OLE by double click in MS Word.
We will inform you via this forum thread once there is an update available on your issue.
It is to inform you that the issue which you are facing is actually not a bug in Aspose.Words. So, we have closed this issue (WORDSNET-22615) as ‘Not a Bug’.
Please set the proper ‘Aspose.Cells.Workbook.Worksheets.OleSize’ of the embedded XLSX file before inserting it as shown below.
using (Aspose.Cells.Workbook book = new Aspose.Cells.Workbook(MyDir + @"sample.xlsx"))
using (MemoryStream imgStream = new MemoryStream())
{
//-------------------------------------------
//book.Worksheets.SetOleSize(0, 23, 0, 3); // 'A1:D24' for licensed Aspose.Cells.
// 'A1:K26' for unlicensed Aspose.Cells, which adds an extra worksheet and makes it active.
book.Worksheets.SetOleSize(0, 25, 0, 10);
book.Save(MyDir + @"out.xlsx");
//-------------------------------------------
Aspose.Cells.Worksheet sheet = book.Worksheets[0];
sheet.PageSetup.LeftMargin = 0;
sheet.PageSetup.RightMargin = 0;
sheet.PageSetup.TopMargin = 0;
sheet.PageSetup.BottomMargin = 0;
new Aspose.Cells.Rendering.SheetRender(sheet,
new Aspose.Cells.Rendering.ImageOrPrintOptions { OnePagePerSheet = true, ImageType = Aspose.Cells.Drawing.ImageType.Emf }).ToImage(0, imgStream);
DocumentBuilder builder = new DocumentBuilder();
Shape shape = builder.InsertOleObject(MyDir + @"out.xlsx", "Excel.Sheet.12", false, false, imgStream);
builder.Document.Save(MyDir + @"21.9.docx");
}
Ok thank you that is helpful, however I am having another issue related to this. When the object is double clicked the correct row/column view is now shown, but the zoom on it is set to 100% on opening which causes the embedded image in Word to resize and be bigger than it needs to be after closing the Excel object.
I have tried to set the zoom level on the sheet, for example:
sheet.Zoom = 80;
And I can see that the sheet is at the expected zoom level for a split second after the Excel OLE is opened, but then is quickly resets to 100%. Is there a way to lock/persist the zoom level when the OLE object is opened?
It should be technically possible because if you size the object manually in Word, the zoom level will be set correctly when opening the OLE object.
Using the latest version of Aspose.Cells and Aspose.Words, we have not found the shared issue. So, please upgrade to the latest versions of Aspose.Wrods and Aspose.Cells.
I am attaching a video demonstrating the issue. problem.7z (556.2 KB)
I set the zoom level to 40%:
sheet.Zoom = 40;
Notice around second 12-13 when the embedded Excel file is opened it is briefly correctly 40% zoomed and then it resets to 100%.
What I want to accomplish is to have the Excel OLE that I am embedding into the Word document using Apsose to be opened at the appropriate zoom level so that the image of the OLE does not get resized after the initial opening.
If you manually resize the OLE image, the embedded Excel will subsequently open at the appropriate zoom level so that the OLE image does not have it’s size changed. So there must be a way to specify the zoom level in the OOXML?
Sure. Below is the code.
You can set the zoom level to different values and note that on initial Excel OLE open it flashes to the expected zoom level and then resets to 100%.
static void TestAspose()
{
var book = new Aspose.Cells.Workbook(@"C:\Projects\WordTest\sample.xlsx");
var sheet = book.Worksheets[0];
book.Worksheets.SetOleSize(0, sheet.Cells.MaxDataRow, 0, sheet.Cells.MaxDataColumn);
sheet.PageSetup.LeftMargin = 0;
sheet.PageSetup.RightMargin = 0;
sheet.PageSetup.TopMargin = 0;
sheet.PageSetup.BottomMargin = 0;
sheet.Zoom = 40;
var options = new Aspose.Cells.Rendering.ImageOrPrintOptions
{
OnePagePerSheet = true,
ImageType = Aspose.Cells.Drawing.ImageType.Emf
};
var sr = new Aspose.Cells.Rendering.SheetRender(sheet, options);
var imgStream = new MemoryStream();
sr.ToImage(0, imgStream);
imgStream.Position = 0;
var document = new Aspose.Words.Document();
var builder = new Aspose.Words.DocumentBuilder(document);
using var xlsStream = new MemoryStream();
book.Save(xlsStream, SaveFormat.Xlsx);
xlsStream.Position = 0;
var shape = builder.InsertOleObject(xlsStream, "Excel.Sheet.12", false, imgStream);
var of = shape.OleFormat;
of.AutoUpdate = false;
shape.AspectRatioLocked = true;
shape.Width = 600;
document.Save(@"C:\Projects\WordTest\sample.docx");
}
If you insert the same Excel OLE into Word document using MS Word, you will face the same behavior. So, this is not an issue with Aspose.Words.
We are moving this forum thread to Aspose.Total forum where Aspose.Cells team provides you information regarding Aspose.Cells if there is any solution available for your case.
How could you accomplish the task manually without using or involving Aspose APIs? I mean sheet’s zoom level (what you have set in Excel manually) prevails over when clicking the embedded OLE object. Please give us steps details and give expected file(s) with screenshots. I suspect this might be MS Office (application) behavior regarding embedded OLE objects and nothing to do with Aspose.Cells part.
Attaching a video demonstrating the behavior. demo.7z (1.6 MB)
The first time I open the OLE on a document generated by Aspose it resets to 100% zoom (10 seconds). I then resize the image of the OLE (21 seconds) and open the OLE again, and it is now shown with the appropriate zoom level (31 seconds).
To reiterate my point: after I manually adjust the image size at 21 seconds, a specific zoom level is applied when I open the embedded Excel. This means that the zoom level or some parameter gets set either in the OLE properties in Word or on the embedded Excel spreadsheet.
Well, you are manually setting or resizing the embedded OLE object (in Word document) after sometime. I asked you to show the your expected file when clicking the OLE object first time where the OLE object size should prevail or retain.
book.Save(xlsStream, SaveFormat.Xlsx);
Please note, this is the line where you just save the final excel file to streams after doing all the OLE object’s and PageSetup settings. Now, Aspose.Cells role is over in the task at that time and you navigate to Aspose.Words API next. But as you manually adjust the size after clicking over it afterwards, so, not sure if Aspose.Words has anything to do with it. In short, this might be the behavior of MS Office (e.g. MS Word). Anyways, we will comment and give further details from Aspose.Words API perspective (if there is something to do with it).
I am not saying that I expect Aspose to have any control of what Word or Excel do when a user starts manually adjusting things.
The point I was trying to make in the previous post is that when a user manually resizes the OLE image, a parameter gets set somewhere which controls the zoom level of the Excel spreadsheet when it is subsequently opened. This means that such a parameter potentially exists in the OOXML definition of the Word document or Excel spreadsheet. Do you agree with this assessment?
If so, then maybe the Aspose API could be enhanced to allow the setting of this parameter so that it can be set to something (other than the default 100%) when Aspose generates the document (or spreadsheet).