Programmatically Create a Data Table in Excel Using C# .NET & Java

As is said: “Data is King,” but without easy ways to utilize data, it can be difficult to interpret, especially with the quantity of data available in today’s world. One way to easily help users understand data is to create visual representations of data. For example, suppose a customer needs to create an Excel chart programmatically for sales data that comes from a data object, as shown below. It would be best to include the raw data in the spreadsheet and one or multiple visualizations to assist with analyzing the data.

Ready to create your own Excel Charts? Download Document Solutions today!

Let's start with the data in the object below to demonstrate how this is done programmatically using several common web-based programming languages.

One way to achieve the requirement is to tie the Chart with a Table or Range.

However, locating the related Table or Range in Excel slows down the data analysis process if the chart is moved to a different location or a different sheet.

Another alternative is the Data Labels, which can be difficult to read within some charts, such as a 3D or multi-series Area chart.

In this situation, a Data Table in Chart can be helpful. It is a grid beneath the graphical display showing the exact values used to create the chart. They help users to collect and analyze essential information quickly and conveniently.

This blog demonstrates a programmatic approach to working with Data Table in Charts using GcExcel API for C# .NET and Java by following two simple steps:

• Step 1 - Enable/Disable a Data Table for the Chart
• Step 2 - Configure a Data Table

Enable/Disable a Data Table for the Chart

Enabling or disabling a Data Table in the chart using the IChart.HasDataTable or IChart.setHasDataTable properties in .NET and Java, respectively, are simple because it is a Boolean property that, when set to True, adds a data table to the chart with default font and other styling configurations. See below for examples using various languages:

C# .NET

//add data table to a chart
IShape chartCol = worksheet.Shapes.AddChart(ChartType.ColumnClustered, 100, 100, 400, 250);
chartCol.Chart.SeriesCollection.Add(worksheet.Range["A1:E5"]);
chartCol.Chart.ChartTitle.Text = "Quaterly Sales";

//enable data table
chartCol.Chart.HasDataTable = true;

Java

//Create chart.
IShape chartCol = worksheet.getShapes().addChart(ChartType.ColumnClustered, 250, 0, 350, 250);
chartCol.getChart().getSeriesCollection().add(worksheet.getRange("A1:E5"));
chartCol.getChart().getChartTitle().setText("Quaterly Sales");

//enable data table
chartCol.getChart().setHasDataTable(true);

And when set to false, it deletes the data table.

C# .NET

//delete data table
chartShape.Chart.HasDataTable = false;

Java

//delete data table
chartShape.getChart().setHasDataTable(false);

This property works for specific chart types only - Column, Bar, Line, and Area. For other chart types such as Pie, Scatter, Funnel, etc., enabling this property will throw an error (so best to put some error handling in place in case this happens!). This is because a data table makes no sense for them as they have data that is easily analyzed without it, and the chart itself is self-explanatory.

Configure a Data Table

Once a Data Table is enabled in the chart, modifications may be necessary depending on formatting requirements. The default settings for font or other configurations to match the style in the chart or company’s standards. This can be done by using the IDataTable interface. Apart from properties to enable/disable the borders or legend key in the data table, it provides options to format the font or border lines as depicted in the code snippet below:

C# .NET

//Configure the data table.
IDataTable dataTable = chartCol.Chart.DataTable;
dataTable.ShowLegendKey = true;
dataTable.HasBorderHorizontal = false;
dataTable.Format.Line.Color.ObjectThemeColor = ThemeColor.Accent6;
dataTable.Font.Color.ObjectThemeColor = ThemeColor.Accent2;
dataTable.Font.Size = 10;
dataTable.Font.Italic = true;

Java

//Configure the data table.
IDataTable datatable = chartCol.getChart().getDataTable();
datatable.setShowLegendKey(true);
datatable.setHasBorderHorizontal(false);
datatable.getFormat().getLine().getColor().setObjectThemeColor(ThemeColor.Accent6);
datatable.getFont().getColor().setObjectThemeColor(ThemeColor.Accent2);
datatable.getFont().setSize(9);
datatable.getFont().setItalic(true);

Note that IDataTable works only for specific chart types and when HasDataTable is set to true.

Feel free to experiment with these settings and comment below to let us know about any cool examples you may come up with! For now, check out the full versions of this example below!

Download the complete .NET sample!

Download the Java sample!

Check out .NET Help | .NET Demo | Java Help | Java Demo

Ready to create your own Excel Charts? Download Document Solutions today!