Line chart using a specific XML structure


#1

We are evaluating Aspose.Excel for one of our major clients. The application needs the ability to export a chart to Excel. We looked at the Cost Pareto example in the Business Reports samples. It gives us a good idea of the implemenation, however it is a little simplistic. We are trying to create a line chart using the attached XML file. This is going to be the key factor in our decision to purchase the product for our client. Could you please take a look and advise as to how this can be implemented?

By going through the sample code provided, we couldnt figure out how to set the X-axis and Y-axis for a chart in excel. If you could direct us to the piece of code which achieves this functionality it will be greatly appreciated. We will probably need to build in the XML schema into the attached file for starters, but we would like some pointers from you on this.


#2

I didn't find your attached xml file. Could you post it again? And could you also post an Excel file to show your desired chart?

To set X-axis and Y-axis, you can use the following code:

chart.NSeries.CategoryData = "A2:B9"; //set x-axis labels

and you can use chart.CategoryAxis and chart.ValueAxis properties to set x-axis and y-axis.


#3

Sorry about that. I will try uploading the file again. I am uploading it as a zip file this time. e


#4

I used the following code to load data from your XML file to an Excel file. Please see the attached file.

DataSet ds = new DataSet();
ds.ReadXml("d:\\test\\sample_xml_file.xml");

Console.WriteLine(ds.Tables.Count);

Excel excel = new Excel();
excel.Worksheets[0].Cells.ImportDataTable(ds.Tables[3], false, 0, 0);

excel.Save("d:\\test\\abc.xls");

Could you manually create a chart to show your need based on this attached file? Then I can show you some sample code to serve your need.


#5

Thanks a lot for this. This is a great help. However, I need to be able to use the XML structure in the file attached. Specifically, I need to be able to use a schema based on our client’s requirements. I am not able to get the schema right. Could you please take a look at the schema and the sample data in the XML file and advise how to represent the xAxisTitle, graphTitle and hierarchy into the schema?


#6

Please check the sample code and output file:

Excel excel = new Excel();
Worksheet sheet = excel.Worksheets[0];
sheet.Cells.ImportDataTable(ds.Tables[0], false, 0, 0);

int chartIndex = sheet.Charts.Add(ChartType.LineWithDataMarkers, 10, 4, 24, 12);
Chart chart = sheet.Charts[chartIndex];
chart.NSeries.Add("F1:F3", true);
chart.NSeries.CategoryData = "E1:E3";
chart.Title.Text = "Lipitor Weekly NRx Share";
chart.CategoryAxis.Title.Text = "Date";

excel.Save("d:\\test\\abc.xls");