Evaluation warning

SYum · June 20, 2017, 9:57am

Hi, I am using Aspose in Evaluation version( I am coding in C# ), and it seems that evaluation version creates a few more sheets when the program compiles. Other than this issue, is there any other issue that will influence the program directly? For example, some kind of failure or instability? I am trying to color the bubbles of the bubble chart, but even though I set the color of the bubbles with the same color, sometimes the bubble colors turn out to be different.(I mean, I have compiled the program multiple times, but the output results are different)

This Topic is created by Amjad_Sahi using the Email to Topic plugin.

amjad.sahi · June 20, 2017, 10:04am

Hi,

Thanks for providing us some dettails.

Well, Aspose.Cells will keep adding evaluation watermark sheet if you save the workbook multiple times. When you use and set a valid license, the limitation would be removed and you would work in full capacity.

Other than this issue, is there any other issue that will influence the program directly? For example, some kind of failure or instability?

There is not any other significant limitation of failures in evaluation mode although you cannot open more than 100 files at a time.

I am trying to color the bubbles of the bubble chart, but even though I set the color of the bubbles with the same color, sometimes the bubble colors turn out to be different.(I mean, I have compiled the program multiple times, but the output results are different)

Could you provide us sample code (or preferably a sample console application (runnable), you may zip it prior attaching here) to show the issue with template files and complete details, we will check it soon.

Thank you.

SYum · June 20, 2017, 1:53pm

    protected virtual void StyleChart(Excel.Cells.Charts.Chart chart, string sheetName, double yAxisLargestValue, double yAxisSmallestValue, string connString, int counter, Excel.Cells.Worksheet sheet)
    {
        chart.Type = Excel.Cells.Charts.ChartType.Bubble;         
        Logger.WriteLine("Styling chart " + chart.Name);
       
        int textSizeForBubbles = TEXT_SIZE_FOR_BUBBLES;
        if (sheetName.Contains(ALL_INDUSTRIES))
        {


            for (int i = 0; i < chart.NSeries.Count; i++)
            {
                chart.NSeries[i].BubbleScale = 30;
            }

        }
        else
        {

            for (int i = 0; i < chart.NSeries.Count; i++)
            {
                chart.NSeries[i].BubbleScale = 50;
            }
            
        }



        Excel.Cells.Charts.Axis yAxis = chart.ValueAxis;
        

        switch (chart.Name)
        {
            case CHART_NAME_LEV:
            case CHART_NAME_PROJ_LEV:
                yAxis.CrossAt = Y_AXIS_CROSS_LEV_CHARTS;
                yAxis.MinValue = MIN_SCALE_LEV_CHARTS;
                yAxis.MaxValue = MAX_SCALE_LEV_CHARTS;

                break;

            case CHART_NAME_LTV:
            case CHART_NAME_PROJ_LTV:
                yAxis.CrossAt = Y_AXIS_CROSS_LTV_CHARTS;
                yAxis.MinValue = MIN_SCALE_LTV_CHARTS;
                yAxis.MaxValue = MAX_SCALE_LTV_CHARTS;

                break;

            default:
                if (yAxisLargestValue > 0)
                {
                    Excel.Cells.Charts.Axis xAxis = chart.CategoryAxis;

                    if (yAxisLargestValue > 100)
                    {
                        yAxisLargestValue += 5000;
                        yAxisSmallestValue -= 5000;
                    }
                    else
                    {
                        yAxisLargestValue += 5;
                        yAxisSmallestValue -= 5;
                    }
                    yAxisLargestValue = Math.Round(yAxisLargestValue);
                    yAxisSmallestValue = Math.Round(yAxisSmallestValue);
                    yAxis.MaxValue = yAxisLargestValue;
                    yAxis.MinValue = yAxisSmallestValue;
                    yAxis.CrossAt = (yAxisLargestValue + yAxisSmallestValue) / 2.0;
                }
                break;
        }

        Excel.Cells.Charts.Series recommendationOneSeries = chart.NSeries[0];
        Excel.Cells.Charts.Series recommendationTwoSeries = chart.NSeries[1];
        Excel.Cells.Charts.Series recommendationThreeSeries = chart.NSeries[2];
        Excel.Cells.Charts.Series recommendationFourSeries = chart.NSeries[3];
        Excel.Cells.Charts.Series recommendationFiveSeries = chart.NSeries[4];

        float transparency = .25F;

        recommendationOneSeries.Area.ForegroundColor = System.Drawing.Color.FromArgb(64,196,124);            
        recommendationOneSeries.Area.Transparency = transparency;
        recommendationOneSeries.Area.FillFormat.FillType = Excel.Cells.Drawing.FillType.Solid;

        recommendationTwoSeries.Area.ForegroundColor = System.Drawing.Color.FromArgb(191, 221, 170);
        recommendationTwoSeries.Area.Transparency = transparency;
        recommendationTwoSeries.Area.FillFormat.FillType = Excel.Cells.Drawing.FillType.Solid;

        recommendationThreeSeries.Area.ForegroundColor = System.Drawing.Color.FromArgb(255, 255, 64);
        recommendationThreeSeries.Area.Transparency = transparency;
        recommendationThreeSeries.Area.FillFormat.FillType = Excel.Cells.Drawing.FillType.Solid;

        recommendationFourSeries.Area.ForegroundColor = System.Drawing.Color.FromArgb(255, 188, 64);
        recommendationFourSeries.Area.Transparency = transparency;
        recommendationFourSeries.Area.FillFormat.FillType = Excel.Cells.Drawing.FillType.Solid;

        recommendationFiveSeries.Area.ForegroundColor = System.Drawing.Color.FromArgb(255, 64, 64);
        recommendationFiveSeries.Area.Transparency = transparency;
        recommendationFiveSeries.Area.FillFormat.FillType = Excel.Cells.Drawing.FillType.Solid;

        sheet.Workbook.CalculateFormula();

    }

Specifically,

recommendationTwoSeries.Area.ForegroundColor = System.Drawing.Color.FromArgb(191, 221, 170);

is the line that is causing the trouble that I mentioned. In some compilation, the result gives me the desired color(pale green), but sometimes it gives some random greenish color. Please, if you find any bug, let me know. Thank you.

amjad.sahi · June 20, 2017, 2:03pm

Hi,

Thanks for sharing the sample code segment.

I am afraid to fix your issue precisely, we got to first reproduce it on our end. I might not compile your code segments as I am not sure about some objects (used) and its data. Also, I don’t have your template Excel file (if any) with source data. We appreciate if you could create a simple console application using v17.6.x (latest version), zip the project and post us here with the template file, we will check it soon.

By the way, I guess the issue might be due to your own code and logic, so you should debug your code thoroughly to try to figure it out.

Thank you.

SYum · June 21, 2017, 5:10pm

OK, I am currently working on creating a sample package that will be runnable to you.(I can’t just give out the project that I am working on due to the security issue) Please keep me posted, and I will send you the package as soon as possible.

amjad.sahi · June 21, 2017, 5:27pm

Hi,

Yes, I am looking forward to get the sample project (runnable) from you to reproduce the issue on our end. Also, give us (steps) details on how to reproduce the issue on our end as you told earlier the issue was not found every time.

Thank you.

SYum · June 21, 2017, 10:21pm

Hi, I have a sample runnable project ready. However, I would like to e-mail you this project rather than posting it in here. Please check your e-mail. Thank you.

amjad.sahi · June 22, 2017, 9:37am

Hi,

I got your mail with details about your project. We appreciate if you could upload your project (runnable) with template files on some file sharing services/drives (e.g dropbox, google drive, or your own custom drive, etc.) and share the download link or credentials here (or via mail), we will download it and check it soon.

Thank you.

SYum · June 22, 2017, 12:55pm

Sorry, I think I did not attach my project in my mail. I just sent you the compressed version of my project. Please un-zip it, and follow the instruction that I have provided. Please let me know the result as soon as possible.

Thank you.

SYum · June 22, 2017, 1:04pm

Plus, for your information, the following is the instruction of running this project that I included in my previous mail to you. Please read this before you run the project.

Place ‘QuadrantAnalysisByFundLTV.xlsx’ and ‘QuadrantAnalysisByFundLeverage.xlsx’ in your C:\Temp.
Build and run Program.cs
Then you will find that ‘testQuadrant Analysis_LTV00010101.xlsx’ and ‘testQuadrant Analysis_Leverage00010101.xlsx’ in your C:\Temp.
Open ‘testQuadrant Analysis_Leverage00010101.xlsx’.
Please notice that the pale green color of left graph is significantly different from the pale green color of the right graph.

This is the issue that I am facing. If you take a close look at the code,(WriterQuadrantAnalysisBase.cs) in line 887, you will see

recommendationTwoSeries.Area.ForegroundColor = System.Drawing.Color.FromArgb(191, 221, 170);

and you will be able to observe that there is nothing that changes the color for the different graph(however, as mentioned before, the pale green color is presented differently).

I suspect this might be the evaluation version issue. Please take a very close look at the code, and let me know if you find any solution or reason for the color inconsistency.

P.S.
I have accidentally sent the mail twice with the project. Those are the same project with no difference.

amjad.sahi · June 22, 2017, 1:04pm

Hi,

I got your mail with attachment but the attachment is just a message file (.msg), i.e., “VS Projects_zip (1.04 KB)”. Please upload your attachments here or upload to some third party service and share the link here, so we could evaluate your issue.

Thank you.

SYum · June 22, 2017, 2:11pm

I am currently working on resolving this issue(I think this is related to the security). Once I solve this issue, I will send you the package as soon as possible. Please keep me posted.

Thank you.

amjad.sahi · June 22, 2017, 6:59pm

@SYum,

Thanks for sharing us the project (via your internal box) with template files.

After an initial test, I am able to reproduce the issue as you mentioned by using your sample project with your template files. I found bubble colors are changed a bit for different charts even using the same lines of code. I have logged a ticket with an id “CELLSNET-45454” for your issue. Our concerned developer from product team will investigate and analyze your code if this is an issue with the APIs or your sample code needs some tweak.

Once we have an update on it, we will let you know here.

Thank you.

SYum · June 22, 2017, 7:05pm

Thank you. Please let me know if there is any progress ASAP.

amjad.sahi · June 23, 2017, 7:49am

Sure, we will keep you posted once we have any new information.

Thanks.

SYum · June 26, 2017, 8:27pm

Hi Amjad,

I was wondering if there is any progress on this issue. Is there anything that you want to let me know?

Thanks.

amjad.sahi · June 27, 2017, 7:57am

@SYum,

Please modify “StyleChart” method to following:
e.g
Sample code:

 ....
    ....
    ...

    Excel.Cells.Charts.Series recommendationOneSeries = chart.NSeries[0];
    Excel.Cells.Charts.Series recommendationTwoSeries = chart.NSeries[1];
    Excel.Cells.Charts.Series recommendationThreeSeries = chart.NSeries[2];
    Excel.Cells.Charts.Series recommendationFourSeries = chart.NSeries[3];
    Excel.Cells.Charts.Series recommendationFiveSeries = chart.NSeries[4];

    //Set all poits of series to automatic
    for (int i = 0; i < chart.NSeries.Count; i++)
    {
        int pointCount = chart.NSeries[i].Points.Count;
        for(int j=0; j < pointCount;j++)
        {
            ChartPoint p = chart.NSeries[i].Points[j];
            p.Area.Formatting = FormattingType.Automatic;
            p.Border.FormattingType = ChartLineFormattingType.Automatic;
        }
    }


    float transparency = .25F;

    //Set area fill to automatic
    recommendationOneSeries.Area.FillFormat.FillType = FillType.Automatic;
    recommendationOneSeries.Area.FillFormat.FillType = Excel.Cells.Drawing.FillType.Solid;
    recommendationOneSeries.Area.Transparency = transparency;
    recommendationOneSeries.Area.ForegroundColor = System.Drawing.Color.FromArgb(64, 196, 124);

    recommendationTwoSeries.Area.FillFormat.FillType = Excel.Cells.Drawing.FillType.Automatic;
    recommendationTwoSeries.Area.FillFormat.FillType = Excel.Cells.Drawing.FillType.Solid;
    recommendationTwoSeries.Area.Transparency = transparency;
    recommendationTwoSeries.Area.ForegroundColor = System.Drawing.Color.FromArgb(191, 221, 170);

    recommendationThreeSeries.Area.FillFormat.FillType = Excel.Cells.Drawing.FillType.Automatic;
    recommendationThreeSeries.Area.FillFormat.FillType = Excel.Cells.Drawing.FillType.Solid;
    recommendationThreeSeries.Area.Transparency = transparency;
    recommendationThreeSeries.Area.ForegroundColor = System.Drawing.Color.FromArgb(255, 255, 64);

    recommendationFourSeries.Area.FillFormat.FillType = Excel.Cells.Drawing.FillType.Automatic;
    recommendationFourSeries.Area.FillFormat.FillType = Excel.Cells.Drawing.FillType.Solid;
    recommendationFourSeries.Area.Transparency = transparency;
    recommendationFourSeries.Area.ForegroundColor = System.Drawing.Color.FromArgb(255, 188, 64);

    recommendationFiveSeries.Area.FillFormat.FillType = Excel.Cells.Drawing.FillType.Automatic;
    recommendationFiveSeries.Area.FillFormat.FillType = Excel.Cells.Drawing.FillType.Solid;
    recommendationFiveSeries.Area.Transparency = transparency;
    recommendationFiveSeries.Area.ForegroundColor = System.Drawing.Color.FromArgb(255, 64, 64);
    
    

    sheet.Workbook.CalculateFormula();
   ...
   ...
  ...

Thank you.

amjad.sahi · June 27, 2017, 8:05am

@SYum,

By investigating the result file(testQuadrant Analysis_Leverage00010101.xlsx), we find the chart points and series have other settings of fill and border before running “StyleChart” method. When setting file of series by following code segment, the settings of points and series still exist. As the 2 charts have different other settings, the bubbles have different colors.

       recommendationOneSeries.Area.FillFormat.FillType = Excel.Cells.Drawing.FillType.Solid; 
        recommendationOneSeries.Area.Transparency = transparency; 
        recommendationOneSeries.Area.ForegroundColor = System.Drawing.Color.FromArgb(64, 196, 124);

According to the rules of MS Excel, if you change series’ properties, the chart points will be changed too. We cannot do same behavior as per MS Excel as Aspose.Cells is not a visual tool.
We suggest clearing the settings of the points, set the fill of series to automatic before setting series’s setting.

Thank you.

SYum · June 27, 2017, 9:10pm

So, am I to do

        SetSeriesLabels((Excel.Cells.Charts.Series)recommendationOneSeries, "CI",sheet);
        SetSeriesLabels((Excel.Cells.Charts.Series)recommendationTwoSeries, "CQ", sheet);
        SetSeriesLabels((Excel.Cells.Charts.Series)recommendationThreeSeries, "CY", sheet);
        SetSeriesLabels((Excel.Cells.Charts.Series)recommendationFourSeries, "DG", sheet);
        SetSeriesLabels((Excel.Cells.Charts.Series)recommendationFiveSeries, "DO", sheet);

        sheet.Charts.Clear();

?

Plus, how do I set the sill of series to automatic?

amjad.sahi · June 28, 2017, 8:07am

@SYum,

See the following complete code of StyleChart() method in WriterQuadrantAnalysisBase.cs. Only add 2 code segments. They will start with “Add 1” and "Add 2 and end with “End Add 1” and “End Add 2”.

You do not need to change other code segments much you provide,
“Add 1” is setting points to automatic.
“Add 2” is setting series to automatic.
e.g
Sample code:

protected virtual void StyleChart(Excel.Cells.Charts.Chart chart, string sheetName, double yAxisLargestValue, double yAxisSmallestValue, string connString, int counter, Excel.Cells.Worksheet sheet)
        {
            chart.Type = Excel.Cells.Charts.ChartType.Bubble;
            Console.WriteLine("Styling chart " + chart.Name);

            int textSizeForBubbles = TEXT_SIZE_FOR_BUBBLES;
            if (sheetName.Contains(ALL_INDUSTRIES))
            {


                for (int i = 0; i < chart.NSeries.Count; i++)
                {
                    chart.NSeries[i].BubbleScale = 30;
                }

            }
            else
            {

                for (int i = 0; i < chart.NSeries.Count; i++)
                {
                    chart.NSeries[i].BubbleScale = 50;
                }

            }

            SetChartValues(connString);

            Excel.Cells.Charts.Axis yAxis = chart.ValueAxis;


            switch (chart.Name)
            {
                case CHART_NAME_LEV:
                case CHART_NAME_PROJ_LEV:
                    yAxis.CrossAt = Y_AXIS_CROSS_LEV_CHARTS;
                    yAxis.MinValue = MIN_SCALE_LEV_CHARTS;
                    yAxis.MaxValue = MAX_SCALE_LEV_CHARTS;

                    break;

                case CHART_NAME_LTV:
                case CHART_NAME_PROJ_LTV:
                    yAxis.CrossAt = Y_AXIS_CROSS_LTV_CHARTS;
                    yAxis.MinValue = MIN_SCALE_LTV_CHARTS;
                    yAxis.MaxValue = MAX_SCALE_LTV_CHARTS;

                    break;

                default:
                    if (yAxisLargestValue > 0)
                    {
                        Excel.Cells.Charts.Axis xAxis = chart.CategoryAxis;

                        if (yAxisLargestValue > 100)
                        {
                            yAxisLargestValue += 5000;
                            yAxisSmallestValue -= 5000;
                        }
                        else
                        {
                            yAxisLargestValue += 5;
                            yAxisSmallestValue -= 5;
                        }
                        yAxisLargestValue = Math.Round(yAxisLargestValue);
                        yAxisSmallestValue = Math.Round(yAxisSmallestValue);
                        yAxis.MaxValue = yAxisLargestValue;
                        yAxis.MinValue = yAxisSmallestValue;
                        yAxis.CrossAt = (yAxisLargestValue + yAxisSmallestValue) / 2.0;
                    }
                    break;
            }


            Excel.Cells.Charts.Series recommendationOneSeries = chart.NSeries[0];
            Excel.Cells.Charts.Series recommendationTwoSeries = chart.NSeries[1];
            Excel.Cells.Charts.Series recommendationThreeSeries = chart.NSeries[2];
            Excel.Cells.Charts.Series recommendationFourSeries = chart.NSeries[3];
            Excel.Cells.Charts.Series recommendationFiveSeries = chart.NSeries[4];

            //Add 1
            /*********************************************/
            //Set area and border of all poits to automatic
            /*********************************/
            for (int i = 0; i < chart.NSeries.Count; i++)
            {
                int pointCount = chart.NSeries[i].Points.Count;
                for(int j=0; j < pointCount;j++)
                {
                    ChartPoint p = chart.NSeries[i].Points[j];
                    p.Area.Formatting = FormattingType.Automatic;
                    p.Border.FormattingType = ChartLineFormattingType.Automatic;
                }
            }
            /********************************************/
            //End Add 1

            //Add 2
            /*********************************************/
            //Set area of all series to automatic
            /*********************************/
            recommendationOneSeries.Area.FillFormat.FillType = FillType.Automatic;
            recommendationTwoSeries.Area.FillFormat.FillType = Excel.Cells.Drawing.FillType.Automatic;
            recommendationThreeSeries.Area.FillFormat.FillType = Excel.Cells.Drawing.FillType.Automatic;
            recommendationFourSeries.Area.FillFormat.FillType = Excel.Cells.Drawing.FillType.Automatic;
            recommendationFiveSeries.Area.FillFormat.FillType = Excel.Cells.Drawing.FillType.Automatic;
            /********************************************/
            //End Add 2

            float transparency = .25F;
            
            
            recommendationOneSeries.Area.FillFormat.FillType = Excel.Cells.Drawing.FillType.Solid;
            recommendationOneSeries.Area.Transparency = transparency;
            recommendationOneSeries.Area.ForegroundColor = System.Drawing.Color.FromArgb(64, 196, 124);
            
            recommendationTwoSeries.Area.FillFormat.FillType = Excel.Cells.Drawing.FillType.Solid;
            recommendationTwoSeries.Area.Transparency = transparency;
            recommendationTwoSeries.Area.ForegroundColor = System.Drawing.Color.FromArgb(191, 221, 170);
            
            recommendationThreeSeries.Area.FillFormat.FillType = Excel.Cells.Drawing.FillType.Solid;
            recommendationThreeSeries.Area.Transparency = transparency;
            recommendationThreeSeries.Area.ForegroundColor = System.Drawing.Color.FromArgb(255, 255, 64);
            
            recommendationFourSeries.Area.FillFormat.FillType = Excel.Cells.Drawing.FillType.Solid;
            recommendationFourSeries.Area.Transparency = transparency;
            recommendationFourSeries.Area.ForegroundColor = System.Drawing.Color.FromArgb(255, 188, 64);
            
            recommendationFiveSeries.Area.FillFormat.FillType = Excel.Cells.Drawing.FillType.Solid;
            recommendationFiveSeries.Area.Transparency = transparency;
            recommendationFiveSeries.Area.ForegroundColor = System.Drawing.Color.FromArgb(255, 64, 64);
            

            sheet.Workbook.CalculateFormula();
            
            SetSeriesLabels((Excel.Cells.Charts.Series)recommendationOneSeries, "CI", sheet);
            SetSeriesLabels((Excel.Cells.Charts.Series)recommendationTwoSeries, "CQ", sheet);
            SetSeriesLabels((Excel.Cells.Charts.Series)recommendationThreeSeries, "CY", sheet);
            SetSeriesLabels((Excel.Cells.Charts.Series)recommendationFourSeries, "DG", sheet);
            SetSeriesLabels((Excel.Cells.Charts.Series)recommendationFiveSeries, "DO", sheet);
            
        }

Hope, this helps a bit.

Thank you.