Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Thu Apr 26, 2018 5:39 am

Hi,

ich use a chart type Column3D and define a series with
..
workbook.Version = ExcelVersion2010;
..
var cs1 = (ChartSerie)chart.Series[0];
..
cs1.InvertNegaColor = true;

Positive values should be rendered in LightGreen and negative values should be rendered in LightRed.

I've tried many variants and every time i became a grey or black positive or white negative Column bar if inverted.

How should i define the colors in the right way?
with best regards
falk

falk
 
Posts: 4
Joined: Wed Nov 23, 2016 10:23 am

Thu Apr 26, 2018 7:54 am

Dear falk,

Thanks for your inquiry.
Our Spire.Xls supports defining the color of positive columns and negative columns. Below is sample code for your kind reference.
Code: Select all
chart.Series[0].InvertNegaColor = true;
chart.Series[0].Format.Fill.ForeColor = Color.LightGreen;
chart.Series[0].Format.Fill.BackColor = Color.Red;

Sincerely,
Nina
E-iceblue support team
User avatar

Nina.Tang
 
Posts: 1182
Joined: Tue Sep 27, 2016 1:06 am

Thu Apr 26, 2018 9:39 am

thanks,
that works not like expected see my little screenshot and the code part

bild.PNG
bild.PNG (3.74 KiB) Viewed 3676 times


bild1.PNG
bild1.PNG (14.32 KiB) Viewed 3676 times


the version i use is the 8.4.13 from nuget
with best regards
falk

falk
 
Posts: 4
Joined: Wed Nov 23, 2016 10:23 am

Thu Apr 26, 2018 10:08 am

Hello,

Thanks for your feedback.
To help us investigate your issue accurately, please share us with your input Excel file as well as your full code ( you could send it to us via email support@e-iceblue.com ). Thanks in advance.

Sincerely,
Nina
E-iceblue support team
User avatar

Nina.Tang
 
Posts: 1182
Joined: Tue Sep 27, 2016 1:06 am

Tue May 01, 2018 8:00 am

Hi,

How is your issue going?
Thanks in advance for your valuable feedback and time.

Sincerely,
Nina
E-iceblue support team
User avatar

Nina.Tang
 
Posts: 1182
Joined: Tue Sep 27, 2016 1:06 am

Thu May 03, 2018 4:06 am

Hi Nina,

i was out of office ...

here is a testversion from the code:

Code: Select all
           
using System;
using Spire.Xls;
using Spire.Xls.Charts;
using System.Drawing;

namespace spire_test
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            workbook.Version = ExcelVersion.Version2010;
            Worksheet sheet = workbook.Worksheets[0];

            fillin_values(sheet);

            Chart chart = sheet.Charts.Add(ExcelChartType.Column3D);
            chart.DataRange = sheet.Range["A71:D81"];
            chart.ChartTitle = "Test Stand " + DateTime.Now.ToShortDateString();
            chart.ChartTitleArea.Font.Color = Color.Blue;
            chart.ChartTitleArea.Font.Size = 20;
            chart.ChartTitleArea.IsBold = true;
            chart.ChartTitleArea.Size = 20;

            chart.SeriesDataFromRange = false;

            chart.LeftColumn = 1;

            chart.TopRow = 1;

            chart.RightColumn = 25;
            chart.Width = 1740;
            chart.LeftColumnOffset = 512;
            chart.BottomRow = 70;

            chart.PrimaryCategoryAxis.Offset = 250;
            chart.PrimaryValueAxis.CrossValue = 0;
            chart.PrimaryValueAxis.CrossesAt = 0;

            var cs1 = (ChartSerie)chart.Series[0];
            cs1.Values = sheet.Range["B72:B81"];
            cs1.CategoryLabels = sheet.Range["A72:A81"];
            cs1.Format.Fill.FillType = ShapeFillType.SolidColor;
            cs1.InvertNegaColor = true;
            cs1.Format.Fill.ForeColor = Color.FromKnownColor(KnownColor.LightGreen);
            cs1.Format.Fill.BackColor = Color.FromKnownColor(KnownColor.LightSalmon);
            cs1.DataPoints.DefaultDataPoint.DataLabels.ValueFromCell = sheet.Range["B72:B81"];
            cs1.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;

            ExcelFont font = workbook.CreateFont();
            font.Size = 16.0;
            font.Color = Color.Black;

            chart.Legend.TextArea.SetFont(font);

            foreach (ChartSerie cs in chart.Series)
            {
                cs.DataPoints.DefaultDataPoint.DataLabels.TextArea.SetFont(font);
            }

            var cs2 = (ChartSerie)chart.Series[1];
            cs2.Values = sheet.Range["C72:C81"];
            cs2.Format.Fill.FillType = ShapeFillType.SolidColor;
            cs2.Format.Fill.ForeColor = Color.FromKnownColor(KnownColor.LightBlue);

            var cs3 = (ChartSerie)chart.Series[2];
            cs3.Values = sheet.Range["D72:D81"];
            cs3.Format.Fill.FillType = ShapeFillType.SolidColor;
            cs3.Format.Fill.ForeColor = Color.FromKnownColor(KnownColor.DarkOrange);
            cs3.DataPoints.DefaultDataPoint.DataLabels.ValueFromCell = sheet.Range["D72:D81"];
            cs3.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;

            chart.HasDataTable = true;

            chart.PlotArea.ForeGroundColor = Color.WhiteSmoke;
            chart.Legend.FrameFormat.Fill.ForeColor = Color.LightGray;

            sheet.PageSetup.PaperSize = PaperSizeType.A2Paper;
            sheet.PageSetup.Orientation = PageOrientationType.Landscape;
            sheet.PageSetup.PrintArea = "A1:AB70";

            string ebasename = @"\Testdaten\test-" + DateTime.Now.ToString("yyyyMMddHHmm");
            workbook.SaveToFile(ebasename + ".xlsx", ExcelVersion.Version2010);
            workbook.SaveToFile(ebasename + ".pdf", Spire.Xls.FileFormat.PDF);
            System.Diagnostics.Process.Start(ebasename + ".xlsx");
        }

        private static void fillin_values(Worksheet sheet)
        {
            int line_er = 72;
            int line_ka = 73;
            int line_ko = 74;
            int line_ei = 75;
            int line_ew = 76;
            int line_fe = 77;
            int line_mo = 78;
            int line_me = 79;
            int line_km = 80;
            int line_ke = 81;
            int line_last_data_row = line_ke;

            sheet.Range["B71"].Value = "Open";
            sheet.Range["C71"].Value = "Ist";
            sheet.Range["D71"].Value = "Planned";
            sheet.Range["A" + line_er.ToString()].Value = "Val1";
            sheet.Range["A" + line_ka.ToString()].Value = "Val2";
            sheet.Range["A" + line_ko.ToString()].Value = "Val3";
            sheet.Range["A" + line_ei.ToString()].Value = "Val4";
            sheet.Range["A" + line_ew.ToString()].Value = "Val5";
            sheet.Range["A" + line_fe.ToString()].Value = "Val6";
            sheet.Range["A" + line_mo.ToString()].Value = "Val7";
            sheet.Range["A" + line_me.ToString()].Value = "Val8";
            sheet.Range["A" + line_km.ToString()].Value = "Val10";
            sheet.Range["A" + line_ke.ToString()].Value = "Val11";

            sheet.Range["D" + line_er.ToString()].NumberValue = 1014568;
            sheet.Range["D" + line_er.ToString()].NumberFormat = "#,##0 €;[Red]-#,##0 €";
            sheet.Range["B" + line_er.ToString()].NumberValue = -710197;
            sheet.Range["B" + line_er.ToString()].NumberFormat = "#,##0 €;[Red]-#,##0 €";
            sheet.Range["C" + line_er.ToString()].NumberValue = 304371;
            sheet.Range["C" + line_er.ToString()].NumberFormat = "#,##0 €;[Red]-#,##0 €";
            sheet.Range["D" + line_ka.ToString()].NumberValue = 1000000;
            sheet.Range["D" + line_ka.ToString()].NumberFormat = "#,##0 €;[Red]-#,##0 €";
            sheet.Range["B" + line_ka.ToString()].NumberValue = 944696;
            sheet.Range["B" + line_ka.ToString()].NumberFormat = "#,##0 €;[Red]-#,##0 €";
            sheet.Range["C" + line_ka.ToString()].NumberValue = 944304;
            sheet.Range["C" + line_ka.ToString()].NumberFormat = "#,##0 €;[Red]-#,##0 €";
            sheet.Range["D" + line_ko.ToString()].NumberValue = 992456;
            sheet.Range["D" + line_ko.ToString()].NumberFormat = "#,##0 €;[Red]-#,##0 €";
            sheet.Range["B" + line_ko.ToString()].NumberValue = 48151;
            sheet.Range["B" + line_ko.ToString()].NumberFormat = "#,##0 €;[Red]-#,##0 €";
            sheet.Range["C" + line_ko.ToString()].NumberValue = 944304;
            sheet.Range["C" + line_ko.ToString()].NumberFormat = "#,##0 €;[Red]-#,##0 €";
            sheet.Range["D" + line_ei.ToString()].NumberValue = 538319.02;
            sheet.Range["D" + line_ei.ToString()].NumberFormat = "#,##0 €;[Red]-#,##0 €";
            sheet.Range["B" + line_ei.ToString()].NumberValue = -2407.25565779989;
            sheet.Range["B" + line_ei.ToString()].NumberFormat = "#,##0 €;[Red]-#,##0 €";
            sheet.Range["C" + line_ei.ToString()].NumberValue = 540294.35;
            sheet.Range["C" + line_ei.ToString()].NumberFormat = "#,##0 €;[Red]-#,##0 €";
            sheet.Range["D" + line_ew.ToString()].NumberValue = 83282.47;
            sheet.Range["D" + line_ew.ToString()].NumberFormat = "#,##0 €;[Red]-#,##0 €";
            sheet.Range["B" + line_ew.ToString()].NumberValue = 50.3062847999827;
            sheet.Range["B" + line_ew.ToString()].NumberFormat = "#,##0 €;[Red]-#,##0 €";
            sheet.Range["C" + line_ew.ToString()].NumberValue = 83241.19;
            sheet.Range["C" + line_ew.ToString()].NumberFormat = "#,##0 €;[Red]-#,##0 €";
            sheet.Range["D" + line_fe.ToString()].NumberValue = 205009.89;
            sheet.Range["D" + line_fe.ToString()].NumberFormat = "#,##0 €;[Red]-#,##0 €";
            sheet.Range["B" + line_fe.ToString()].NumberValue = 112238.3922;
            sheet.Range["B" + line_fe.ToString()].NumberFormat = "#,##0 €;[Red]-#,##0 €";
            sheet.Range["C" + line_fe.ToString()].NumberValue = 106555.16;
            sheet.Range["C" + line_fe.ToString()].NumberFormat = "#,##0 €;[Red]-#,##0 €";
            sheet.Range["D" + line_mo.ToString()].NumberValue = 1073.38;
            sheet.Range["D" + line_mo.ToString()].NumberFormat = "#,##0 €;[Red]-#,##0 €";
            sheet.Range["B" + line_mo.ToString()].NumberValue = -39834.2432;
            sheet.Range["B" + line_mo.ToString()].NumberFormat = "#,##0 €;[Red]-#,##0 €";
            sheet.Range["C" + line_mo.ToString()].NumberValue = 35883.88;
            sheet.Range["C" + line_mo.ToString()].NumberFormat = "#,##0 €;[Red]-#,##0 €";
            sheet.Range["D" + line_me.ToString()].NumberValue = 0;
            sheet.Range["D" + line_me.ToString()].NumberFormat = "#,##0 €;[Red]-#,##0 €";
            sheet.Range["B" + line_me.ToString()].NumberValue = -21210.59775;
            sheet.Range["B" + line_me.ToString()].NumberFormat = "#,##0 €;[Red]-#,##0 €";
            sheet.Range["C" + line_me.ToString()].NumberValue = 18605.7875;
            sheet.Range["C" + line_me.ToString()].NumberFormat = "#,##0 €;[Red]-#,##0 €";
            sheet.Range["D" + line_km.ToString()].NumberValue = 0;
            sheet.Range["D" + line_km.ToString()].NumberFormat = "#,##0 €;[Red]-#,##0 €";
            sheet.Range["B" + line_km.ToString()].NumberValue = -732.95;
            sheet.Range["B" + line_km.ToString()].NumberFormat = "#,##0 €;[Red]-#,##0 €";
            sheet.Range["C" + line_km.ToString()].NumberValue = 732.95;
            sheet.Range["C" + line_km.ToString()].NumberFormat = "#,##0 €;[Red]-#,##0 €";
            sheet.Range["D" + line_ke.ToString()].NumberValue = 0;
            sheet.Range["D" + line_ke.ToString()].NumberFormat = "#,##0 €;[Red]-#,##0 €";
            sheet.Range["B" + line_ke.ToString()].NumberValue = 0;
            sheet.Range["B" + line_ke.ToString()].NumberFormat = "#,##0 €;[Red]-#,##0 €";
            sheet.Range["C" + line_ke.ToString()].NumberValue = 0;
            sheet.Range["C" + line_ke.ToString()].NumberFormat = "#,##0 €;[Red]-#,##0 €";
        }
    }
}



please have also a look on the pdf export, here ist the problem the same but the color is different.
with best regards
falk

falk
 
Posts: 4
Joined: Wed Nov 23, 2016 10:23 am

Thu May 03, 2018 8:05 am

Dear Falk,

Thanks for sharing code.
After an initial test, I found that your issue is caused by specifying the data labels of cs1 from cells. If you comment out this line, you will get the correct color in resultant Excel file, however, the color is still incorrect in resultant Pdf file. I have forwarded the two issues to our Dev team for further investigation and will keep you informed regarding any available updates. So sorry for the inconvenience caused.
Code: Select all
//comment out this line
cs1.DataPoints.DefaultDataPoint.DataLabels.ValueFromCell = sheet.Range["B72:B81"];

Sincerely,
Nina
E-iceblue support team
User avatar

Nina.Tang
 
Posts: 1182
Joined: Tue Sep 27, 2016 1:06 am

Thu Jul 12, 2018 10:26 am

Dear Falk,

Thanks for your patient waiting.
Your issue has been fixed, please download the hotfix from following link. Any feedback after testing will be greatly appreciated.
Spire.XLS Pack(Hotfix) Version:8.7.2

Sincerely,
Nina
E-iceblue support team
User avatar

Nina.Tang
 
Posts: 1182
Joined: Tue Sep 27, 2016 1:06 am

Wed Jul 25, 2018 8:56 am

Hello,

Greetings from E-iceblue.
Did you test the hotfix? Could you please give me some feedback about your issue? Thanks in advance.

Sincerely,
Nina
E-iceblue support team
User avatar

Nina.Tang
 
Posts: 1182
Joined: Tue Sep 27, 2016 1:06 am

Return to Spire.XLS