Create and Format Chart Using Free Spire.Xls

Spire.Xls

The Spire.XLS Free Edition is a versatile Excel library designed for software developers to do a wide range of Excel processing tasks on the .NET Platform, that doesn't need to install Microsoft Excel or Office.

You can download it from: https://freenetexcel.codeplex.com/

Getting Started

Start Visual Studio and create a new console application and add the following Spire.Xls DLLS.

• Spire.Common.dll
• Spire.License.dll
• Spire.Pdf.dll
• Spire.XLS.dll

Now let's simulate some data and save them into a DataTable.

private static DataTable CreateTable()
{
    DataTable dt = new DataTable();
    dt.Columns.Add("Month", typeof(string));
    dt.Columns.Add("Count", typeof(int));
    dt.Rows.Add("January", 10);
    dt.Rows.Add("February", 20);
    dt.Rows.Add("March", 30);
    dt.Rows.Add("April", 40);
    dt.Rows.Add("May", 50);
    return dt;
}

 

Next is to insert the DataTable into Excel and create the chart with the above data using Spire.Xls that supports the creation of various charts, such as column, pie, line and so on. This article uses a column chart as an example.

public static void CreateChart()
 {
     Workbook workbook = new Workbook();
     workbook.Worksheets.Clear();
     var sheet = workbook.Worksheets.Add("Sheet1");
     DataTable dt = CreateTable();
     sheet.InsertDataTable(dt, false, 1, 1);
     Chart chart = sheet.Charts.Add();
     chart.ChartType = ExcelChartType.ColumnClustered;
     chart.DataRange = sheet.AllocatedRange;
     chart.SeriesDataFromRange = false;
     chart.Height = 220;
     chart.Width = 450;
     chart.Left = 0;
     chart.Top = 100;
     workbook.SaveToFile("CreateChart.xlsx", ExcelVersion.Version2007);
 }

Press F5 and see the output:

 

The process is very simple and when we create the chart, we can set the layout of the chart, such as chart title, chart area, legend, x-axis, and so on. These might be not important for us, so here I have skipped them, but then you'll love it.

Next, I will guide you through the labeling of the data points and coloring the data points that are a useful way to see precise data about the values of the underlying data alongside the graph itself.

The next segment of code is used to label and color the data points. The process is simple enough.

Color[] arrColors = new Color[]
 {
     Color.Red,
     Color.Green,
     Color.GreenYellow,
     Color.Pink,
         Color.Purple
 };
 for (int i = 0; i < chart.Series.Count; i++)
 {
     chart.Series[i].DataPoints.DefaultDataPoint.DataLabels.HasValue = true;
     chart.Series[i].DataPoints.DefaultDataPoint.DataLabels.Color = Color.Blue;
     for (int j = 0; j < chart.Series[i].PointNumber; j++)
     {
         chart.Series[0].DataPoints[j].DataFormat.Fill.FillType = ShapeFillType.SolidColor;
         chart.Series[0].DataPoints[j].DataFormat.Fill.ForeColor = arrColors[j];
     }
 }

Effect screenshot:

 

I hope you enjoyed this article. I'll write some features of Spire.Xls in separate articles soon.