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.

Fri Nov 19, 2021 5:12 am

I am using Spire.XLS Chart in Excel in C #

I need to create a chart like the one attached, someone who can help me know how I can generate it, I think it would be the LineMarkers

I am trying to generate the graphic with a one line using this code but it does not work :cry:

workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
Chart chart = null;
chart = sheet.Charts.Add(ExcelChartType.LineMarkers);

sheet.Name = "Chart data";
sheet.GridLinesVisible = false;
chart.DataRange = sheet.Range["B1:B4"];
chart.SeriesDataFromRange = false;
chart.TopRow = 6;
chart.BottomRow = 25;
chart.LeftColumn = 2;
chart.RightColumn = 9;
chart.ChartTitle = "Tiempo de atención";
chart.ChartTitleArea.IsBold = true;
chart.ChartTitleArea.Size = 12;
sheet.Range["A1"].Value = "Mes";
sheet.Range["A2"].Value = "mayo, 2020";
sheet.Range["A3"].Value = "junio, 2020";
sheet.Range["A4"].Value = "Julio, 2020";
sheet.Range["B1"].Value = "Tiempo de atención";
sheet.Range["B2"].Value = "00:01:02";
sheet.Range["B3"].Value = "00:00:00";
sheet.Range["B4"].Value = "00:01:38";

Spire.Xls.Charts.ChartSerie cs1 = chart.Series[0];
cs1.CategoryLabels = sheet.Range["A2:A4"];
cs1.ErrorBar(true, ErrorBarIncludeType.Plus, ErrorBarType.Percentage, 10);

stream = new MemoryStream();
workbook.SaveToStream(stream, Spire.Xls.FileFormat.Version2010);
workbook.SaveToFile(Documento.TemplatesEventReport + "S3.xlsx", ExcelVersion.Version2010);
Image image = workbook.SaveChartAsImage(sheet, 0);
Attachments
GraphicLine.PNG
Graphic that I need to create
GraphicLine.PNG (11.58 KiB) Viewed 1355 times
Last edited by AnaMacias on Fri Nov 19, 2021 7:18 am, edited 1 time in total.

AnaMacias
 
Posts: 8
Joined: Fri Feb 28, 2020 6:33 pm

Fri Nov 19, 2021 6:44 am

Hello,

Thanks for your inquiry!

Please refer to the following code to create the chart you need. If you encounter any issues related to our product in the future, just feel free to contact us.

Code: Select all
            Workbook workbook = new Workbook();
            Worksheet sheet = workbook.Worksheets[0];
 
            sheet.Range["A1"].Value = "Mes";
            sheet.Range["A2"].Value = "mayo, 2020";
            sheet.Range["A3"].Value = "junio, 2020";
            sheet.Range["A4"].Value = "Julio, 2020";
            sheet.Range["B1"].Value = "Tiempo de atención";
            sheet.Range["B2"].Value = "00:01:02";
            sheet.Range["B3"].Value = "00:00:00";
            sheet.Range["B4"].Value = "00:01:38";

            //Get the line chart
            Chart chart = sheet.Charts.Add(ExcelChartType.Line);

            chart.DataRange = sheet["A1:B4"];
            chart.SeriesDataFromRange = false;

            //set chart position
            chart.LeftColumn = 1;
            chart.TopRow = 7;
            chart.RightColumn = 9;
            chart.BottomRow = 26;

            //set series
            ChartSerie cs = chart.Series[0];
            cs.CategoryLabels = sheet.Range["A2:A4"];
            cs.Values = sheet.Range["B2:B4"];
            cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = false;

            chart.HasLegend = false;

            //Save and launch result file
            workbook.SaveToFile("S3.xlsx", ExcelVersion.Version2010);


Sincerely,
Marcia
E-iceblue support team
User avatar

Marcia.Zhou
 
Posts: 858
Joined: Wed Nov 04, 2020 2:29 am

Fri Nov 19, 2021 8:00 am

Hello Marcia,
Thanks for your help!
I already checked the code you sent me, but unfortunately it doesn't work

if I put this: sheet.Range["A2"].Value = "mayo, 2020";
In the graph I change it for this: 01/05/2020 and I don't need that, but if I put: sheet.Range["A2"].Text = "mayo, 2020"; yes, the text respect it: "Mayo, 2020";

And if I put this: sheet.Range["B2"].Value = "00:01:02";
In the graph I change it for this: 18/11/2021, I don't respect the format:
hours: minutes: seconds
Attachments
ResultGraphic.PNG
this is the result of your code
ResultGraphic.PNG (23.21 KiB) Viewed 1348 times
GraphicLineFinal.PNG
I need this graphic as a final version
GraphicLineFinal.PNG (9.01 KiB) Viewed 1348 times

AnaMacias
 
Posts: 8
Joined: Fri Feb 28, 2020 6:33 pm

Fri Nov 19, 2021 8:54 am

Hello,

Thanks for your feedback!

For the issue of "mayo, 2020" changed to "01/05/2020": According to the information you provided, your region setting is Spain, right? Kindly note that when you set the data by “CellRange.Value”, our Spire.Xls will calculate the data according to the current culture automatic, this is the reason of data changed. Here are two methods to avoid this:

1. Set the data by “CellRange.Text” just like you mentioned.
Code: Select all
sheet.Range["A2"].Text = "mayo, 2020";

2. Reset the current culture to English at the beginning, as the following code shows:
Code: Select all
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");

For the issue of “18/11/2021”: Our Spire.Xls will data format to year-month-day-hour-minute-second automatic if the data conforms to time format. Please refer to the following code to set the number format of the value to hour-minute-second.
Code: Select all
sheet.Range["B2:B4"].NumberFormat = "hh:mm:ss;@";


Here I also attached my result file.
S3.zip
(8.01 KiB) Downloaded 210 times

Sincerely,
Marcia
E-iceblue support team
User avatar

Marcia.Zhou
 
Posts: 858
Joined: Wed Nov 04, 2020 2:29 am

Fri Nov 19, 2021 7:30 pm

Buena tarde Marcia,

Yo me encuentro en la ciudad de México, realice las pruebas con los cambios que me mencionaste pero sigo sin poder hacer que funcione, si muestra en el gráfico los tiempos con el formato, pero no los ordena y revisando en el archivo S3.xlsx no llena la columna con la información de los tiempos.

Muchas gracias por el apoyo que me has brindado.
Attachments
2021-11-19_131855.PNG
Esto es lo que me da como resultado
2021-11-19_131855.PNG (82.72 KiB) Viewed 1335 times

AnaMacias
 
Posts: 8
Joined: Fri Feb 28, 2020 6:33 pm

Mon Nov 22, 2021 1:27 am

Hello,

Thanks for your reply!

Sorry that I did not put my full code in the post before. Here I attached my full code. Please refer to the following code, and set the data of B2 to B4 by “CellRange.Value”.

Code: Select all
            Workbook workbook = new Workbook();
            Worksheet sheet = workbook.Worksheets[0];
 
            sheet.Range["A1"].Value = "Mes";
            sheet.Range["A2"].Text = "mayo, 2020";
            sheet.Range["A3"].Text = "junio, 2020";
            sheet.Range["A4"].Text = "Julio, 2020";
            sheet.Range["B1"].Value = "Tiempo de atención";
            sheet.Range["B2"].Value = "00:01:02";
            sheet.Range["B3"].Value = "00:00:00";
            sheet.Range["B4"].Value = "00:01:38";
            sheet.Range["B2:B4"].NumberFormat = "hh:mm:ss;@";   //set the number format of value B2 to B4

            //Get the line chart
            Chart chart = sheet.Charts.Add(ExcelChartType.Line);

            chart.DataRange = sheet["A1:B4"];
            chart.SeriesDataFromRange = false;

            //set chart position
            chart.LeftColumn = 1;
            chart.TopRow = 7;
            chart.RightColumn = 9;
            chart.BottomRow = 26;

            //set series
            ChartSerie cs = chart.Series[0];
            cs.CategoryLabels = sheet.Range["A2:A4"];
            cs.Values = sheet.Range["B2:B4"];
            cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = false;

            chart.HasLegend = false;

            //Save and launch result file
            workbook.SaveToFile("S3.xlsx", ExcelVersion.Version2010);


Sincerely,
Marcia
E-iceblue support team
User avatar

Marcia.Zhou
 
Posts: 858
Joined: Wed Nov 04, 2020 2:29 am

Mon Nov 22, 2021 9:08 pm

Hello Marcia,

Thanks for your help!

Making the changes in the code as you indicated, it works correctly, but it takes 14 minutes to generate a graph, is that normal?
because in my report I have to present 20 graphs, and then it would take a long time! :?

Best regards
Attachments
2021-11-22_145938.PNG
This is my result
2021-11-22_145938.PNG (121.21 KiB) Viewed 1280 times

AnaMacias
 
Posts: 8
Joined: Fri Feb 28, 2020 6:33 pm

Tue Nov 23, 2021 2:23 am

Hello,

Thanks for your feedback!

I tested the code you provided, but it only took 2 seconds to generate 20 charts on my side.
time use.png
time use.png (62.68 KiB) Viewed 1265 times

Which version of Spire.Xls are you using? If you are not using our latest Spire.Xls for net version 11.10.5, I recommend that you can try it. If you are using it already, please provide us with your input files(if any), the target framework of your project(E.g. .net5.0), your system information (E.g. Win7, 64 bit) and region setting (E.g. China, Chinese) for reference.

Thanks in advance.

Sincerely,
Marcia
E-iceblue support team
User avatar

Marcia.Zhou
 
Posts: 858
Joined: Wed Nov 04, 2020 2:29 am

Wed Nov 24, 2021 12:24 am

Hi Marcia,

I am using the version of Spire.Office version 6.10.3, in microsoft visual study community 2017 with language c #, framework .NET4.8 Operating system of my machine: Windows 10 Enterprise with region setting (Mexico, Spanish).

I have been doing tests...

With the following code it takes 14 min and de result is the image: 2021-11-23_175122 resultado1.PNG:

Code: Select all
 Workbook workbook = new Workbook();
                workbook.CreateEmptySheets(1);
                Worksheet sheet = workbook.Worksheets[0];
                sheet.Name = "ChartData";
                sheet.GridLinesVisible = false;

                sheet.Range["A1"].Value = "Mes";
                sheet.Range["A2"].Text = "mayo, 2021";
                sheet.Range["A3"].Text = "junio, 2021";
                sheet.Range["A4"].Text = "Julio, 2021";
                sheet.Range["B1"].Value = "Tiempo de atención";
                sheet.Range["B2"].Value = "00:02:00";
                sheet.Range["B3"].Value = "00:00:00";
                sheet.Range["B4"].Value = "00:01:38";

                sheet.Range["B2:B4"].NumberFormat = "hh:mm:ss;@";

                //Get the line chart
                Chart chart = sheet.Charts.Add(ExcelChartType.LineMarkers);
                chart.DataRange = sheet["A1:B4"];
                //set chart position
                chart.LeftColumn = 6;
                chart.TopRow = 2;
                chart.RightColumn = 12;
                chart.BottomRow = 13;
                //Chart title
                chart.ChartTitle = "Tiempo de atención";
                chart.ChartTitleArea.IsBold = true;
                chart.ChartTitleArea.Size = 10;

                chart.PrimaryValueAxis.Title = "HH:mm:ss";
                chart.PrimaryValueAxis.TitleArea.IsBold = false;
                chart.PrimaryValueAxis.TitleArea.Size = 10;
                chart.PrimaryValueAxis.TitleArea.TextRotationAngle = -90;
                chart.Legend.Position = LegendPositionType.Bottom;

                //set series
                ChartSerie cs = chart.Series[0];
                cs.CategoryLabels = sheet.Range["A2:A4"];
                cs.Values = sheet.Range["B2:B4"];

                cs.DataFormat.MarkerStyle = ChartMarkerType.Circle;

                cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = false;
                chart.HasLegend = true;
                cs.Name = "Promedio";

                //Save and launch result file
                stream = new MemoryStream();
                workbook.SaveToStream(stream, Spire.Xls.FileFormat.Version2016);
                workbook.SaveToFile(Documento.TemplatesEventReport + "S3.xlsx", ExcelVersion.Version2016);
                Image image = workbook.SaveChartAsImage(sheet, 0);
                return image;



with this other version of code I do it through a DataTable it takes 4 min and de result is the image: 2021-11-23_181345 resultado2.PNG

Code: Select all
 private static DataTable CreateTable(GraphicLine graphicLine)
{
   DataTable dataTableInfo = new DataTable();
   dt.Columns.Add("Mes", typeof(string));
   dt.Columns.Add("Informacion", typeof(DateTime));
   dt.Rows.Add(graphicLine.monthTwoAgo, Convert.ToDateTime("00:04:00"));
   dt.Rows.Add(graphicLine.monthOneAgo, Convert.ToDateTime("00:00:00"));
   dt.Rows.Add(graphicLine.monthActual, Convert.ToDateTime("00:02:00"));
   return dataTableInfo;
}
  private Image ChartToImage(out MemoryStream stream, DataTable dataTableInfo)
      Workbook workbook = new Workbook();
                //Initailize worksheet
                workbook.Worksheets.Clear();
                var sheet = workbook.Worksheets.Add("Sheet1");
                sheet.GridLinesVisible = false;
                sheet.InsertDataTable(dataTableInfo, false, 1, 1);
                sheet.Range["B1:B4"].NumberFormat = "HH:mm:ss";

                //Get the line chart
                Chart chart = sheet.Charts.Add(ExcelChartType.LineMarkers);
                chart.DataRange = sheet.Range["A1:B3"];
                //set chart position
                chart.LeftColumn = 6;
                chart.TopRow = 2;
                chart.RightColumn = 12;
                chart.BottomRow = 13;

                //Chart title
                chart.ChartTitle = "Tiempo de atención";
                chart.ChartTitleArea.IsBold = true;
                chart.ChartTitleArea.Size = 10;

                chart.PrimaryValueAxis.Title = "HH:mm:ss";
                chart.PrimaryValueAxis.TitleArea.IsBold = false;
                chart.PrimaryValueAxis.TitleArea.Size = 10;
                chart.PrimaryValueAxis.TitleArea.TextRotationAngle = -90;
                chart.Legend.Position = LegendPositionType.Bottom;

                //set series
                ChartSerie cs = chart.Series[0];
                cs.CategoryLabels = sheet.Range["A1:A3"];
                cs.Values = sheet.Range["B1:B3"];

                cs.DataFormat.MarkerStyle = ChartMarkerType.Circle;

                cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = false;
                chart.HasLegend = true;
                cs.Name = "Promedio";

                //Save and launch result file
                stream = new MemoryStream();
                workbook.SaveToStream(stream, Spire.Xls.FileFormat.Version2016);
                workbook.SaveToFile(Documento.TemplatesEventReport + "S3.xlsx", ExcelVersion.Version2016);
               [color=#FF0000] Image image = workbook.SaveChartAsImage(sheet, 0);[/color]
                return image;
            }


in both cases, this instruction is the one that takes time to process
Code: Select all
 Image image = workbook.SaveChartAsImage(sheet, 0);



Marcia thank you very much for your support :)
Attachments
2021-11-23_181345 resultado2.PNG
2021-11-23_181345 resultado2.PNG
2021-11-23_181345 resultado2.PNG (13.44 KiB) Viewed 1223 times
2021-11-23_175122 resultado1.PNG
2021-11-23_175122 resultado1.PNG
2021-11-23_175122 resultado1.PNG (74.09 KiB) Viewed 1223 times

AnaMacias
 
Posts: 8
Joined: Fri Feb 28, 2020 6:33 pm

Wed Nov 24, 2021 3:23 am

Hello,

Thanks for sharing more information!

I have reproduced your issue of time-consuming problem when using the "SaveChartAsImage" method, and logged it in our issue tracking system with the ticket SPIREXLS-3552 for further investigation.

We will let you know if there is any update. Sorry for the inconvenience caused.

Sincerely,
Marcia
E-iceblue support team
User avatar

Marcia.Zhou
 
Posts: 858
Joined: Wed Nov 04, 2020 2:29 am

Mon Jan 24, 2022 9:39 am

Hello,

Thanks for your patience!

Glad to inform you that we just released Spire.XLS Pack Version:12.1 which fixes the issue SPIREXLS-3552.

Please download the fix version from the following links to test.

Website link: https://www.e-iceblue.com/Download/download-excel-for-net-now.html
Nuget link: https://www.nuget.org/packages/Spire.XLS/12.1.0

Sincerely,
Marcia
E-iceblue support team
User avatar

Marcia.Zhou
 
Posts: 858
Joined: Wed Nov 04, 2020 2:29 am

Mon Feb 07, 2022 9:50 am

Hello,

Hope you are doing well!

Has the issue been solved now? Could you please give us some feedback at your convenience?

Thanks in advance.

Sincerely,
Marcia
E-iceblue support team
User avatar

Marcia.Zhou
 
Posts: 858
Joined: Wed Nov 04, 2020 2:29 am

Return to Spire.XLS