Issue of trying to capture an image of excel sheet

Aspose.Cells Product Family
1

I am trying to capture an image of a excel sheet within the OLE object in a Powerpoint slide using the code below :

using Aspose.Cells.Rendering;

ImageOrPrintOptions iop = new ImageOrPrintOptions();
iop.IsCellAutoFit = false;
iop.ImageFormat = ImageFormat.Jpeg;
iop.IsImageFitToPage = true;
iop.VerticalResolution = 320;
iop.HorizontalResolution = 320;
iop.Quality = 100;
iop.OnePagePerSheet = true;
SheetRender sr = new SheetRender(Workbook.Worksheets[“Sheet1”], iop);
Bitmap imgOleObject = sr.ToImage(0);
Rectangle rect = new Rectangle(10, 5, imgOleObject.Width - 10,imgOleObject.Height - 5);
return imgOleObject.Clone(rect, imgOleObject.PixelFormat);

The problem is : In the excel sheet I’m using Conditional Formatting Data Bar with negative values. Problem is the captured image shows only the data bar of positive values, not the data bar of negative values correctly (in fact in image the data bars of negative values are converted to positive values). But, I need data bars with negative values very urgently. Need help urgently…

N.B. We are using Microsoft Office 2010 and Data bar with negative value isn’t supported in Microsoft Excel 2007.
2

Hi Mandy,

Thanks for your posting and using Aspose.Cells.

It seems to be a new feature. MS-Excel 2007 does not support it. We have logged a New Feature Request in our database to support this feature. We will look into it and implement it if possible. Once, there is some fix or update for you, we will let you know asap.

This issue has been logged as CELLSNET-42230.

C#


Workbook workbook = new Workbook(path);


Worksheet worksheet = workbook.Worksheets[0];


Aspose.Cells.Rendering.ImageOrPrintOptions options = new Aspose.Cells.Rendering.ImageOrPrintOptions();

options.OnePagePerSheet = true;

options.ImageFormat = ImageFormat.Png;


SheetRender sr = new SheetRender(worksheet, options);

sr.ToImage(0, path + “.out.png”);


3

Hi,

Thanks for using Aspose.Cells for .NET.

We have fixed this issue.

Please download and try this fix: Aspose.Cells for .NET v7.7.0.2 and let us know your feedback.

4

Hi,


Thanks for your reply and with your new Aspose.Cells.dll, it works fine for the negative value databar.

But, there is a problem. We try to give positive bar’s color as Green and try to negative bar’s color as Red. With new dll, DataBar definitely goes to the negative side, but for the negative bar’s color, it is just as same as positive bar’s color. Would you help me with these ? Is it the right way to color negative databar or is there any other workaround ?
Our sample code snippet is here :

int index = Workbook.Worksheets[0].ConditionalFormattings.Add();
FormatConditionCollection conditionalFormat = Workbook.Worksheets[0].ConditionalFormattings[index];
Range rng = Workbook.Worksheets[0].Cells.CreateRange(“A1:A10”);
CellArea ca = new CellArea()
{
StartRow = rng.FirstRow,
StartColumn = rng.FirstColumn,
EndRow = rng.FirstRow + rng.RowCount - 1,
EndColumn = rng.FirstColumn + rng.ColumnCount - 1
};
int idx = conditionalFormat.AddCondition(FormatConditionType.DataBar);
conditionalFormat.AddArea(ca);
FormatCondition cond = conditionalFormat[idx];

DataBar databar = cond.DataBar;
databar.NegativeBarFormat.Color = Color.Red;
databar.NegativeBarFormat.BorderColor = Color.Red;
databar.Color = Color.Green;
databar.ShowValue = true;
databar.BarBorder.Type = DataBarBorderType.DataBarBorderNone;
databar.BarFillType = DataBarFillType.DataBarFillSolid;

databar.AxisPosition = DataBarAxisPosition.DataBarAxisAutomatic;
databar.AxisColor = Color.Gray;

Waiting for your reply …
5

Hi Mandy,

Thanks for your feedback and using Aspose.Cells.

Please add the following line of code to make it work correctly.

databar.NegativeBarFormat.ColorType = DataBarNegativeColorType.DataBarColor;

Here is the complete code. I have also attached the output xlsx file and the screenshot for your reference.

C#


Workbook Workbook = new Workbook();


int index = Workbook.Worksheets[0].ConditionalFormattings.Add();

FormatConditionCollection conditionalFormat = Workbook.Worksheets[0].ConditionalFormattings[index];

Range rng = Workbook.Worksheets[0].Cells.CreateRange(“A1:A10”);


CellArea ca = new CellArea()

{

StartRow = rng.FirstRow,

StartColumn = rng.FirstColumn,

EndRow = rng.FirstRow + rng.RowCount - 1,

EndColumn = rng.FirstColumn + rng.ColumnCount - 1

};

int idx = conditionalFormat.AddCondition(FormatConditionType.DataBar);

conditionalFormat.AddArea(ca);

FormatCondition cond = conditionalFormat[idx];


DataBar databar = cond.DataBar;

databar.NegativeBarFormat.Color = Color.Red;

databar.NegativeBarFormat.ColorType = DataBarNegativeColorType.DataBarColor;

databar.NegativeBarFormat.BorderColor = Color.Red;


databar.Color = Color.Green;

databar.ShowValue = true;

databar.BarBorder.Type = DataBarBorderType.DataBarBorderNone;

databar.BarFillType = DataBarFillType.DataBarFillSolid;


databar.AxisPosition = DataBarAxisPosition.DataBarAxisAutomatic;

databar.AxisColor = Color.Gray;


Workbook.Save(“output2.xlsx”);
Screenshot:
6

Hi,


It works perfectly. Thanks for your quick help and reply.
7

The issues you have found earlier (filed as CELLSNET-42230) have been fixed in this update.

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