Charts with floar value < 1

[Semjasa]

Hi i have found an issue

in version 10.5.7 of Spire XLS you cannot generate charts with float values ​​less than 1

Code: Select all

class Program
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();

            //Initailize worksheet
            workbook.CreateEmptySheets(1);
            Worksheet sheet = workbook.Worksheets[0];
            sheet.Name = "Chart data";
            sheet.GridLinesVisible = false;

            //Writes chart data
            CreateChartData(sheet);
            CreateChartData1(sheet);

            //Add a new  chart worsheet to workbook
            Chart chart = sheet.Charts.Add();
            chart.ChartType = ExcelChartType.Line;

            //Set region of chart data
            chart.DataRange = sheet.Range["A1:E3"];

            //Set position of chart
            chart.LeftColumn = 1;
            chart.TopRow = 6;
            chart.RightColumn = 11;
            chart.BottomRow = 29;

            //chart.PrimaryCategoryAxis.Font.IsBold = true;
            //chart.PrimaryCategoryAxis.TitleArea.IsBold = true;

            chart.PrimaryValueAxis.HasMajorGridLines = false;
            //chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;
            chart.PrimaryValueAxis.MinValue = 0;
            //chart.PrimaryValueAxis.TitleArea.IsBold = true;

            foreach (var cs in chart.Series)
            {
                cs.Format.Options.IsVaryColor = true;
                cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;
            }

            chart.Legend.Delete();

            //Add a new  chart worsheet to workbook
            Chart chart1 = sheet.Charts.Add();
            chart1.ChartType = ExcelChartType.BarClustered;

            //Set region of chart data
            chart1.DataRange = sheet.Range["L1:P3"];

            //Set position of chart
            chart1.LeftColumn = 12;
            chart1.TopRow = 6;
            chart1.RightColumn = 22;
            chart1.BottomRow = 29;

            //chart1.PrimaryCategoryAxis.Font.IsBold = true;
            //chart1.PrimaryCategoryAxis.TitleArea.IsBold = true;

            chart1.PrimaryValueAxis.HasMajorGridLines = false;
            //chart1.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;
            chart1.PrimaryValueAxis.MinValue = 0;
            //chart1.PrimaryValueAxis.TitleArea.IsBold = true;

            foreach (var cs in chart1.Series)
            {
                cs.Format.Options.IsVaryColor = true;
                cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;
            }

            chart1.PlotArea.Fill.Visible = false;

            chart1.Legend.Delete();

            workbook.SaveToFile("Sample.xls", ExcelVersion.Version97to2003);
            ExcelDocViewer(workbook.FileName);
        }
        private static void CreateChartData(Worksheet sheet)
        {
            sheet.Range["A1"].Value = "";
            sheet.Range["A2"].Value = "Test1";
            sheet.Range["A3"].Value = "Test2";

            sheet.Range["B1"].Value = "Jan";
            sheet.Range["B2"].Value = "0,33";
            sheet.Range["B3"].Value = "0,23";

            sheet.Range["C1"].Value = "Feb";
            sheet.Range["C2"].Value = "0,75";
            sheet.Range["C3"].Value = "0,29";

            sheet.Range["D1"].Value = "Mar";
            sheet.Range["D2"].Value = "0,77";
            sheet.Range["D3"].Value = "0,69";

            sheet.Range["E1"].Value = "Apr";
            sheet.Range["E2"].Value = "0,80";
            sheet.Range["E3"].Value = "#N/A";

            //Border
            sheet.Range["A1:E3"].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
            sheet.Range["A1:E3"].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
            sheet.Range["A1:E3"].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
            sheet.Range["A1:E3"].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
        }

        private static void CreateChartData1(Worksheet sheet)
        {
            sheet.Range["L1"].Value = "";
            sheet.Range["L2"].Value = "Test2";
            sheet.Range["L3"].Value = "Test1";

            sheet.Range["M1"].Value = "Jan";
            sheet.Range["M2"].Value = "";
            sheet.Range["M3"].Value = "0,23";

            sheet.Range["N1"].Value = "Feb";
            sheet.Range["N2"].Value = "0,75";
            sheet.Range["N3"].Value = "0,29";

            sheet.Range["O1"].Value = "Mar";
            sheet.Range["O2"].Value = "0,77";
            sheet.Range["O3"].Value = "0,69";

            sheet.Range["P1"].Value = "Apr";
            sheet.Range["P2"].Value = "";
            sheet.Range["P3"].Value = "0,72";

            //Border
            sheet.Range["L1:P3"].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
            sheet.Range["L1:P3"].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
            sheet.Range["L1:P3"].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
            sheet.Range["L1:P3"].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
        }

        private static void ExcelDocViewer(string fileName)
        {
            try
            {
                System.Diagnostics.Process.Start(fileName);
            }
            catch { }
        }
    }


Thank you for your work
Thomas

[rachel.lei]

Hello,

Thanks for your inquiry.
Please refer to the following modified code to generate charts with float values ​​less than 1. If there is still any issue, please tell us your OS information (E.g. Windows 7, 64 bit) and region setting (E.g. China, Chinese) for further investigation. Thanks in advance.

Code: Select all

        static void Main(string[] args)
        {
            /************set CultureInfo****************/
            //If the decimal symbol of your system is "," not ".", set the CurrentCulture to "InvariantCulture"
            CultureInfo cc = Thread.CurrentThread.CurrentCulture;
            Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
            /************set CultureInfo****************/

            Workbook workbook = new Workbook();

            //Initailize worksheet
            workbook.CreateEmptySheets(1);
            Worksheet sheet = workbook.Worksheets[0];
            sheet.Name = "Chart data";
            sheet.GridLinesVisible = false;

            //Writes chart data
            CreateChartData(sheet);
            CreateChartData1(sheet);

            //Add a new  chart worsheet to workbook
            Chart chart = sheet.Charts.Add();
            chart.ChartType = ExcelChartType.Line;

            //Set region of chart data
            chart.DataRange = sheet.Range["A1:E3"];

            //Set position of chart
            chart.LeftColumn = 1;
            chart.TopRow = 6;
            chart.RightColumn = 11;
            chart.BottomRow = 29;

            chart.PrimaryValueAxis.HasMajorGridLines = false;
            chart.PrimaryValueAxis.MinValue = 0;

            foreach (var cs in chart.Series)
            {
                cs.Format.Options.IsVaryColor = true;
                cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;
            }

            chart.Legend.Delete();

            //Add a new  chart worsheet to workbook
            Chart chart1 = sheet.Charts.Add();
            chart1.ChartType = ExcelChartType.BarClustered;

            //Set region of chart data
            chart1.DataRange = sheet.Range["L1:P3"];

            //Set position of chart
            chart1.LeftColumn = 12;
            chart1.TopRow = 6;
            chart1.RightColumn = 22;
            chart1.BottomRow = 29;

            chart1.PrimaryValueAxis.HasMajorGridLines = false;
            chart1.PrimaryValueAxis.MinValue = 0;

            foreach (var cs in chart1.Series)
            {
                cs.Format.Options.IsVaryColor = true;
                cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;
            }

            chart1.PlotArea.Fill.Visible = false;

            chart1.Legend.Delete();

            workbook.SaveToFile("Sample.xls", ExcelVersion.Version97to2003);
            ExcelDocViewer(workbook.FileName);

            Thread.CurrentThread.CurrentCulture = cc;
        }
        private static void CreateChartData(Worksheet sheet)
        {
            sheet.Range["A1"].Value = "";
            sheet.Range["A2"].Value = "Test1";
            sheet.Range["A3"].Value = "Test2";

            //!!!Use "." instead of "," as the decimal symbol
            sheet.Range["B1"].Value = "Jan";
            sheet.Range["B2"].Value = "0.33";
            sheet.Range["B3"].Value = "0.23";

            sheet.Range["C1"].Value = "Feb";
            sheet.Range["C2"].Value = "0.75";
            sheet.Range["C3"].Value = "0.29";

            sheet.Range["D1"].Value = "Mar";
            sheet.Range["D2"].Value = "0.77";
            sheet.Range["D3"].Value = "0.69";

            sheet.Range["E1"].Value = "Apr";
            sheet.Range["E2"].Value = "0.80";
            sheet.Range["E3"].Value = "#N/A";
            //!!!Use "." instead of "," as the decimal symbol

            //Border
            sheet.Range["A1:E3"].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
            sheet.Range["A1:E3"].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
            sheet.Range["A1:E3"].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
            sheet.Range["A1:E3"].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
        }

        private static void CreateChartData1(Worksheet sheet)
        {
            sheet.Range["L1"].Value = "";
            sheet.Range["L2"].Value = "Test2";
            sheet.Range["L3"].Value = "Test1";

            //!!!Use "." instead of "," as the decimal symbol
            sheet.Range["M1"].Value = "Jan";
            sheet.Range["M2"].Value = "";
            sheet.Range["M3"].Value = "0.23";

            sheet.Range["N1"].Value = "Feb";
            sheet.Range["N2"].Value = "0.75";
            sheet.Range["N3"].Value = "0.29";

            sheet.Range["O1"].Value = "Mar";
            sheet.Range["O2"].Value = "0.77";
            sheet.Range["O3"].Value = "0.69";

            sheet.Range["P1"].Value = "Apr";
            sheet.Range["P2"].Value = "";
            sheet.Range["P3"].Value = "0.72";
            //!!!Use "." instead of "," as the decimal symbol

            //Border
            sheet.Range["L1:P3"].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
            sheet.Range["L1:P3"].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
            sheet.Range["L1:P3"].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
            sheet.Range["L1:P3"].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
        }

        private static void ExcelDocViewer(string fileName)
        {
            try
            {
                System.Diagnostics.Process.Start(fileName);
            }
            catch { }
        }

Sincerely,
Rachel
E-iceblue support team

[Semjasa]

*facepalm... in version 9.7 it was possible to show values less 1 with this code. sorry. thank you for your changes

Thomas

[rachel.lei]

Hi Thomas,

Thanks for your prompt response.
Have you tested the code I provided with the latest Spire.XLS v10.5.7? What is the result now? Looking forward to your further feedback.

Sincerely,
Rachel
E-iceblue support team

[Semjasa]

Hello,
yes the code works with one change. A cast from float to string with CultureInfo.CreateSpecificCulture ("de-DE").
but this change is only required in our real system.

[rachel.lei]

Hello,

Thanks for your valuable feedback.
If you have further questions, please feel free to contact us. Wish you all the best!

Sincerely,
Rachel
E-iceblue support team

[Semjasa]

Unfortunately, this problem has not yet been resolved. The settings described above work in Excel but in PDF I don't get any formatting in German format

[rachel.lei]

Hello,

Thanks for your post.
You mean that you converted the generated Excel file to PDF, but the data format of the PDF file was incorrect, right? To help us further look into your issue, could you please provide the full code you are using now and your output files? You could send them to us(support@e-iceblue.com) via email. Thanks in advance.

Sincerely,
Rachel
E-iceblue support team

[Semjasa]

it's the code from above. with one change

System: Windows 10
Language: German

Code: Select all

workbook.SaveToFile("Sample.pdf", FileFormat.PDF);
workbook.SaveToFile("Sample.xlsx", FileFormat.Version2016);


[rachel.lei]

Hello,

Thanks for your more information.
I tested the code I provided above (set the CurrentCulture to "InvariantCulture") in an environment similar to yours, then saved the workbook object as a PDF file and found the decimal symbol in the generated PDF document is "." instead of ",", as shown in the attached picture. I will pass this issue to our Dev team for further investigation and fixing. If there is any update, we will let you know.
Or if there is any misunderstanding, please get back to us.

Sincerely,
Rachel
E-iceblue support team

[Semjasa]

thank you very much for your quick reply

[rachel.lei]

Hello,

You are welcome.
Once there is any good news, we will inform you immediately.
Have a nice day!

Sincerely,
Rachel
E-iceblue support team

[rachel.lei]

Hello,

Thanks for your patient waiting.
Regarding the format issue, our developers have adjusted the internal code of our product. Now you can use either "," or "." as the decimal symbol in your code.
Please download the newly released Spire.XLS Pack(Hotfix) Version:10.8.3 from the following links for testing. Attached is my test code for your reference.
Our website: https://www.e-iceblue.com/Download/down ... t-now.html
Nuget: https://www.nuget.org/packages/Spire.XLS/10.8.3

Sincerely,
Rachel
E-iceblue support team

[rachel.lei]

Hello,

Greetings from E-iceblue!
Have you tested the hotfix? Does it resolve your issue?
Any feedback will be greatly appreciated!

Sincerely,
Rachel
E-iceblue support team

[Semjasa]

Hey
Thank you for your reply. I will test the code today and give you feedback