Hi Aspose Team!
I’ve been try to save a range from an excel worksheet into a memory stream or even a png file and I cant seem to do so at all. Everytime I try to save it. Its an empty stream or the png saved would be 0 bytes.
public virtual Stream ProcessOleObject(string fileName, string imgCoordinates, Shape oldShape)
{
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);
// Convert these indices to cell address
string StartCell = CellsHelper.CellIndexToName(startRow - 1, startCol - 1);
//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);
// Convert these indices to cell address
string EndCell = CellsHelper.CellIndexToName(endRow - 1, endCol - 1);
//Open the source workbook
Workbook sourceWB = new Workbook(fileName);
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;
// Set OnePagePerSheet option as true
ImageOrPrintOptions options = new ImageOrPrintOptions
{
OnePagePerSheet = true,
ImageType = Aspose.Cells.Drawing.ImageType.Png,
SmoothingMode = System.Drawing.Drawing2D.SmoothingMode.AntiAlias,
HorizontalResolution = (int)oldShape.ImageData.ImageSize.HorizontalResolution,
VerticalResolution = (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(WSIdx, Stream);
return Stream;
}
So what I’m trying to do is getting the sourcefullname and sourceItem from a linked OLE object in a word document and using the sourcefullname which is an excel file and sourceItem which is a range of cells represented as RespRate!R5C1:R8C2 where RespRate is the worksheet name and R5C1:R8C2
corresponds to cell address of the range. I process this information and tried to save it as a stream and a png file. In both cases, its not reading anything.
@JThomas98
We have tested your code with simple template file and find it worked fine. The code using simple data is:
string imgCoordinates = "Sheet1!R1C1:R5C5";
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);
// Convert these indices to cell address
string StartCell = CellsHelper.CellIndexToName(startRow - 1, startCol - 1);
//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);
// Convert these indices to cell address
string EndCell = CellsHelper.CellIndexToName(endRow - 1, endCol - 1);
//Open the source workbook
Workbook sourceWB = new Workbook();
Worksheet sourceWS = sourceWB.Worksheets[WSName];
for (int i = 0; i < 20; i++)
{
for (int j = 0; j < 20; j++)
{
sourceWS.Cells[i, j].PutValue(i + j);
}
}
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;
// Set OnePagePerSheet option as true
ImageOrPrintOptions options = new ImageOrPrintOptions
{
OnePagePerSheet = true,
ImageType = Aspose.Cells.Drawing.ImageType.Png,
SmoothingMode = System.Drawing.Drawing2D.SmoothingMode.AntiAlias,
HorizontalResolution = 800,
VerticalResolution = 600
};
// 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(WSIdx, Stream);
Console.WriteLine(Stream.Length);
File.WriteAllBytes("res.png", Stream.ToArray());
If you still cannot get the expected result, please provide us the runnable program with the template file to reproduce the issue, we will look into it soon. Thank you.
Hi @johnson.shi,
that’s interesting. I wonder if there’s something else going on my end. But thank you verifying that me code works.
@JThomas98,
You are welcome. Kindly debug and troubleshoot your original code step by step and attempt to resolve the issue by yourself. Should you face persistent challenges, kindly provide a standalone application (please zip it before attaching) or a functional sample code along with resource files that allow us to replicate the issue on our end. Subsequently, we will promptly investigate and work towards resolving the matter.
@amjad.sahi,
I’ve tried debugging things on my end and I can’t seem to find out why the pictures that are created using the code are all corrupted. I’ve uploaded my solution with the output and inputs along with it.
Sample.zip (88.6 KB)
@JThomas98,
Thanks for the template XLSM file and sample code segment.
I evaluated your issue using your sample code and found there is an error in your code. The following line of code is saving index (which is 3 (0 indexed based)) of the worksheet “RespRate” to WSIdx variable, since the worksheet “RespRate” is placed on fourth position.
WSIdx = sourceWS.Index;
Now I spotted you are using this variable WSIdx value (3) as page index when rendering the worksheet to image using the line of code:
Render.ToImage(WSIdx, @"M:\ProgramsDev\HRG_Software\Jthomas\LocalRepos\AsposeWordsTest\Pic1.png");
this is wrong. Please note, the above line will try to render 4th page of the worksheet “RespRate” and there is no fourth page so it will render to blank image.
Since you are rendering the whole worksheet “RespRate” to single page (as you are using OnePagePerSheet = true) which is at 0 indexed position (first page). So, please try the following line of code instead:
Render.ToImage(0, @"M:\ProgramsDev\HRG_Software\Jthomas\LocalRepos\AsposeWordsTest\Pic1.png");
I have tested by using the above line and it works fine and as expected.
Let us know if you still have any issue or confusion.
@amjad.sahi ,
Wow, this is great. All this time and I didn’t realize this at all. Can’t thank you enough!
@JThomas98,
You are welcome. If you have any further queries or issues, please feel free to write us back.