Hey Aspose Team,
I’ve been noticing a weird behavior in my code when I capture the picture of range. The image aspose captures look nothing like the excel chart when I open the excel chart and view it.
public virtual Stream ProcessOleObject(string fileName, string imgCoordinates, Shape oldShape)
{
string ProcessName = "Convert link to image";
string ExceptionType = "P";
string StartCell = string.Empty;
string EndCell = string.Empty;
try
{
string[] CoordinateParts = imgCoordinates.Trim().Split('!', ':');
// Here's an example of what an imgCoordinates looks like RespRate!R5C1:R8C2
string WSName = CoordinateParts[0]; //RespRate
int WSIdx; // Worksheet index
string StartAddress = CoordinateParts[1]; //R5C1
string EndAddress = CoordinateParts[2]; //R8C2
//Processing Start Address
string[] StartDigits = StartAddress.Split('R', 'C').Where(x => !string.IsNullOrEmpty(x)).ToArray();
int.TryParse(StartDigits[0], out int startRow);
int.TryParse(StartDigits[1], out int startCol);
//Process End Address
string[] EndDigits = EndAddress.Split('R', 'C').Where(x => !string.IsNullOrEmpty(x)).ToArray();
int.TryParse(EndDigits[0], out int endRow);
int.TryParse(EndDigits[1], out int endCol);
// Coordinates cannot be zero by any chance
if (startRow != 0 || startCol != 0 || endRow != 0 || endRow != 0)
{
// Convert the starting coordinates to cell address
StartCell = CellsHelper.CellIndexToName(startRow - 1, startCol - 1);
// Convert the ending coordinates to cell address
EndCell = CellsHelper.CellIndexToName(endRow - 1, endCol - 1);
}
else
{
throw new Exception($"Linked coordinate contain 0 as a coordinate.\n Cannot convert the link -\n {fileName + imgCoordinates}\n to an image");
}
//Open the source workbook
using (Workbook sourceWB = new Workbook(fileName))
{
sourceWB.CalculateFormula();
Worksheet sourceWS = sourceWB.Worksheets[WSName];
WSIdx = sourceWS.Index;
//Set print area
sourceWS.PageSetup.PrintArea = $"{StartCell}:{EndCell}";
//Set all margins as 0
sourceWS.PageSetup.LeftMargin = 0;
sourceWS.PageSetup.RightMargin = 0;
sourceWS.PageSetup.TopMargin = 0;
sourceWS.PageSetup.BottomMargin = 0;
// Clear any header/footer as they'll be captured when converting the worksheet to image
sourceWS.PageSetup.ClearHeaderFooter();
// Set OnePagePerSheet option as true
ImageOrPrintOptions options = new ImageOrPrintOptions
{
OnePagePerSheet = true,
ImageType = Aspose.Cells.Drawing.ImageType.Jpeg,
HorizontalResolution = 300 /*(int)oldShape.ImageData.ImageSize.HorizontalResolution*/,
VerticalResolution = 300 /*(int)oldShape.ImageData.ImageSize.VerticalResolution*/
};
// Take the image of your worksheet
SheetRender Render = new SheetRender(sourceWS, options);
// Create a stream to save the image in
MemoryStream Stream = new MemoryStream();
Render.ToImage(0, Stream);
return Stream;
}
}
catch (Exception ex)
{
this.HelperToolKit.AppendToErrorTable(this.RepEntity, this.ModuleID, ProcessName, this.ErrorTableRecords, ex, ExceptionType);
return Stream.Null;
}
}
public virtual bool BreakOLELink(string docPath, string populatedLinkWB)
{
bool RetVal = true, TempRes;
StringComparison IgnoreCase = StringComparison.OrdinalIgnoreCase;
string msg;
string MsgActivityLevel;
string ProcessName = "Unlink charts";
string ExceptionType = "P";
string OlePathUNC;
try
{
// Validate input and exit immediately if validation fails
if (!File.Exists(docPath))
{
msg = $@"The text report file {docPath} does not exist";
throw new Exception(msg);
}
//Determine if the specified workbook exists in links in the text report file
var OLELinks = this.GetLinkPaths(docPath);
if (OLELinks.Count == 0)
{
//There are no OLE links in the Word document
MsgActivityLevel = "There are no links the word document";
this.PublishActivity(MsgActivityLevel);
return true;
}
if (string.IsNullOrWhiteSpace(populatedLinkWB))
{
msg = $@"Empty Excel link";
throw new Exception(msg);
}
if (!File.Exists(populatedLinkWB))
{
msg = $@"The Excel link workbook {populatedLinkWB} does not exist";
throw new Exception(msg);
}
MsgActivityLevel = "Unlinking Excel charts...";
this.PublishActivity(MsgActivityLevel);
Document oDoc = new Document(docPath);
DocumentBuilder oDocBuilder = new DocumentBuilder(oDoc);
// Get all shapes in document
NodeCollection oDocShapes = oDoc.GetChildNodes(NodeType.Shape, true);
// TempRes keeps track if any link had failed to convert to an image
TempRes = true;
foreach (Shape oldShape in oDocShapes)
{
if (oldShape.OleFormat != null && oldShape.OleFormat.IsLink)
{
// Move Doucment Builder to the shape
oDocBuilder.MoveTo(oldShape);
// Find the source file of the link
string SourceName = oldShape.OleFormat.SourceFullName;
// Find the coordinates of the linked object from the source
string SourceItem = oldShape.OleFormat.SourceItem;
// Convert the link to an image and saves it into a stream
Stream ImgStream = ProcessOleObject(SourceName, SourceItem, oldShape);
if (ImgStream != Stream.Null)
{
// Insert this image into the place of the old shape.
Shape newOleShape = oDocBuilder.InsertImage(ImgStream);
// Just to be safe we apply the sizing of the old shape to the new shape
if (!oldShape.AspectRatioLocked)
{
// If the old linked object's aspect ratio was not locked, set the same to the new inserted object as well
// By default the aspect ratio of any image inserted using document builder InsertImage method is set to true
newOleShape.AspectRatioLocked = false;
// No we can set the height of the new image as the aspect ratio is not locked
newOleShape.Height = oldShape.Height;
}
newOleShape.Width = oldShape.Width;
//Remove the old OLE Shape
oldShape.Remove();
}
else
TempRes = false;
}
}
oDoc.UpdateFields();
oDoc.Save(docPath);
// Overall success or fail is determined by if all links were successfully converted or not
RetVal = TempRes;
}
catch (Exception ex)
{
RetVal = false;
TempRes = this.HelperToolKit.AppendToErrorTable(this.RepEntity, this.ModuleID, ProcessName, this.ErrorTableRecords, ex, ExceptionType);
}
finally
{
MsgActivityLevel = $"Unlinking Excel charts...{(RetVal ? "Done" : "Failed")}";
this.PublishActivity(MsgActivityLevel);
}
return RetVal;
}
What I’ve tried to do here is break the links in a word document by converting the linked range in excel sheet into an image and storing it in memory stream and pasting it at the location of the linked ole object.
Here are all the files that I’ve used:
Excel_Charts.zip (8.2 MB)
Word_Outputs.zip (2.4 MB)