Excel with Chart - Part 2

Excel with Chart - Part 2

When the Excel worksheet is not enough to connect the data arrays, data analysts cannot build reports. At that time, the emergence of service tools illustrating data using statistical charts as a god with all the power possible from free to preferential. In this article, we will introduce to you some essential things for data illustration, methods of data illustration, and correct analysis, as well as useful tools for updating data in real-time without the need for a technician.

Why is data illustration necessary?

  • Want your Facebook post to attract more readers? What will you do?
  • Usually, adding illustrations will create a good effect. In the reports, this method is also applicable.
  • The images that directly illustrate the data will look more attractive, easy to understand, and quickly convey the message to the customers. With the help of graphs and graphical interfaces, even complex data becomes clear and easy to understand.
  • How is this possible?
  • Most people absorb information through the image. So you may want your partners, colleagues, and even your customers to interact with your data.
  • It would be best if you can turn boring, looking at charts into beautiful illustrations.

The importance of data illustration

  • The following is the evidence indicating the importance of illustrating data, all based on research:
  • Humans absorb 90% of the information from their surroundings through their eyes.
  • 50% of the brain's neurons are involved in data processing into illustrations
  • Images that increase the desire to read content by up to 80%
  • People will remember 10% of what they hear, 20% of what they read, and 80% of what they see.
  • If you inserted a large amount of information without illustrations, the viewer would probably remember 70% of the information. But when there are illustrations, viewers will retain 95%. Besides, the appropriate data illustration will also help a lot for the business:
  • Quick decision making: Summarizing data easily and quickly with the graphs that illustrated.
  • You will easily compare columns and data points against each other.
  • It is much more efficient than looking through the statistical data pages from Google Sheets or Excel.
  • Reach many more people. They will tend to absorb quickly and memorize through data illustrations.
  • Strong connection with the viewer. Beautiful and bright graphs illustrate the message better.
  • Values ​​are not just for data analysts, technicians, but also Marketing Directors and Managing Directors.
  • It can assist each employee in deciding within their work.

Principles for illustrating data success

  • The first step to illustrating data is to clean them up to ensure formality and accuracy.
  • For example:
  • The balance value is 800%; while the average value is 120-130%, you should check this number. These are probably peripheral values that you should delete to avoid distorting the overall picture. Because 800% is too big and will reduce the difference between 120-130%.
  • Peripheral values of this type will lead to wrong decisions. We are accustomed to delivering the right message to the right person at the right time. There are also three principles in data illustration:
  • Define goals to choose the proper graphical illustration.
  • Understand customers to select the right message.
  • Use the appropriate design interface for illustrative charts.
  • If the message is timely, but the graphs are not intuitive and vivid, or the design is difficult to see. You will still not get the results you want.

Excel Office Component with Bar Chart

  • We will now start by integrating the ability to create chart formats into our Excel document. The Bar Chart will be an excellent option for beginners.

This document will guide you in detail how to integrate the ability to create bar charts into Excel documents on your application.

Library

  • First, we will need to integrate the necessary libraries into our application.
  • The methods used in this example are fully implemented in two libraries: OfficeComponent.Excel and OfficeComponent.Excel.Charts
  • C# Version:
using OfficeComponent.Excel;
using OfficeComponent.Excel.Charts;
  • VB Version:
 Imports OfficeComponent.Excel
 Imports OfficeComponent.Excel.Charts

Create a new WorkbookManager

  • In this example, we take the advance of the WorkbookManager() class. This class allows us to create a Workbook - a new blank Excel file. It can be a blank document, where we can add more sheets later.
  • C# Version: WorkbookManager manager = new WorkbookManager();
  • VB Version: Dim manager As New WorkbookManager()

Add Workbooks and Sheets

  • In the next step, we can add new WorkBooks and Sheets to the manager we just created. WorkbookManager supports the add() method:
  • C# Version:
 Workbook workbook = manager.Workbooks.Add();
 IWorksheet sheet = workbook.Worksheets[0];
  • VB Version:
 Dim workbook As Workbook = manager.Workbooks.Add()
 Dim sheet As IWorksheet = workbook.Worksheets(0)

Create data for our Chart

  • Next and essential, it is confident that we will need to have enough data to be able to represent a complete graph. To accomplish this task, we will need to use the CreateChartData() function. The input data is arbitrary, so you don't necessarily have to do exactly like this example.
  • C# Version:
 void CreateChartData(IWorksheet sheet)
 {
    //Put values for rows in column A
    sheet.Range["B3"].Text = "Precipitation,in.";
    sheet.Range["C3"].Text = "Temperature,deg.F";
    sheet.Range["A4"].Text = "Jan";
    sheet.Range["A5"].Text = "Feb";
    sheet.Range["A6"].Text = "March";
    sheet.Range["A7"].Text = "Apr";
    sheet.Range["A8"].Text = "May";
    sheet.Range["A9"].Text = "June";
    sheet.Range["A10"].Text = "July";
    sheet.Range["A11"].Text = "Aug";
    sheet.Range["A12"].Text = "Sept";
    sheet.Range["A13"].Text = "Oct";
    sheet.Range["A14"].Text = "Nov";
    sheet.Range["A15"].Text = "Dec";
    sheet.Range["B4"].Number = 10.9;
    sheet.Range["B5"].Number = 8.9;
    sheet.Range["B6"].Number = 8.6;
    sheet.Range["B7"].Number = 4.8;
    sheet.Range["B8"].Number = 3.2;
    sheet.Range["B9"].Number = 1.4;
    sheet.Range["B10"].Number = 0.6;
    sheet.Range["B11"].Number = 0.7;
    sheet.Range["B12"].Number = 1.7;
    sheet.Range["B13"].Number = 5.4;
    sheet.Range["B14"].Number = 9.0;
    sheet.Range["B15"].Number = 10.4;
    sheet.Range["C4"].Number = 47.5;
    sheet.Range["C5"].Number = 48.7;
    sheet.Range["C6"].Number = 48.9;
    sheet.Range["C7"].Number = 50.2;
    sheet.Range["C8"].Number = 53.1;
    sheet.Range["C9"].Number = 56.3;
    sheet.Range["C10"].Number = 58.1;
    sheet.Range["C11"].Number = 59.0;
    sheet.Range["C12"].Number = 58.5;
    sheet.Range["C13"].Number = 55.4;
    sheet.Range["C14"].Number = 51.1;
    sheet.Range["C15"].Number = 47.8;
    sheet.UsedRange.AutofitColumns();
 }
  • VB Version:
Private Sub CreateChartData(ByVal sheet As IWorksheet)
   'Put values for rows in column A
   sheet.Range("B3").Text = "Precipitation,in."
   sheet.Range("C3").Text = "Temperature,deg.F"
   sheet.Range("A4").Text = "Jan"
   sheet.Range("A5").Text = "Feb"
   sheet.Range("A6").Text = "March"
   sheet.Range("A7").Text = "Apr"
   sheet.Range("A8").Text = "May"
   sheet.Range("A9").Text = "June"
   sheet.Range("A10").Text = "July"
   sheet.Range("A11").Text = "Aug"
   sheet.Range("A12").Text = "Sept"
   sheet.Range("A13").Text = "Oct"
   sheet.Range("A14").Text = "Nov"
   sheet.Range("A15").Text = "Dec"
   sheet.Range("B4").Number = 10.9
   sheet.Range("B5").Number = 8.9
   sheet.Range("B6").Number = 8.6
   sheet.Range("B7").Number = 4.8
   sheet.Range("B8").Number = 3.2
   sheet.Range("B9").Number = 1.4
   sheet.Range("B10").Number = 0.6
   sheet.Range("B11").Number = 0.7
   sheet.Range("B12").Number = 1.7
   sheet.Range("B13").Number = 5.4
   sheet.Range("B14").Number = 9.0
   sheet.Range("B15").Number = 10.4
   sheet.Range("C4").Number = 47.5
   sheet.Range("C5").Number = 48.7
   sheet.Range("C6").Number = 48.9
   sheet.Range("C7").Number = 50.2
   sheet.Range("C8").Number = 53.1
   sheet.Range("C9").Number = 56.3
   sheet.Range("C10").Number = 58.1
   sheet.Range("C11").Number = 59.0
   sheet.Range("C12").Number = 58.5
   sheet.Range("C13").Number = 55.4
   sheet.Range("C14").Number = 51.1
   sheet.Range("C15").Number = 47.8
   sheet.UsedRange.AutofitColumns()
End Sub

Generate Bar Chart

  • To generate a Bar Chart, first, we need to create a variable of type IChart. IChart is a new specified data type in the Excel Office Component library.
  • C# Version: IChart chart = workbook.Charts.Add();
  • VB Version: Dim chart As IChart = workbook.Charts.Add()
  • You can then specify some separate properties for your document.
  • C# Version:
 chart.Name = "Bar Chart";
 chart.ChartTitle = "Bar Chart - Precipitation & Tempature in Nashville, TN";
 chart.DataRange = sheet.Range["A3:C15"];
 chart.IsSeriesInRows = false;
  • VB Version:
 chart.Name = "Bar Chart"
 chart.ChartTitle = "Bar Chart - Precipitation & Tempature in Nashville, TN"
 chart.DataRange = sheet.Range("A3:C15")
 chart.IsSeriesInRows = False
  • You will need to use some Enum concepts when wanting you to use different types of charts at the same time ( 2D and 3D)
  • C# Version:
 if (Chart3D)
 {
    switch (Type)
    {
        case 0:
            chart.ChartType = ChartType.Bar_Clustered_3D;
            break;
        case 1:
            chart.ChartType = ChartType.Bar_Stacked_3D;
            break;
        case 2:
            chart.ChartType = ChartType.Bar_Stacked_100_3D;
            break;
    }
 }
 else
 {
    switch (Type)
    {
        case 0:
            chart.ChartType = ChartType.Bar_Clustered;
            break;
        case 1:
            chart.ChartType = ChartType.Bar_Stacked;
            break;
        case 2:
            chart.ChartType = ChartType.Bar_Stacked_100;
            break;
    }
 }
  • VB Version:
 If Chart3D Then
    Select Case Type
        Case 0
            chart.ChartType = ChartType.Bar_Clustered_3D
        Case 1
            chart.ChartType = ChartType.Bar_Stacked_3D
        Case 2
            chart.ChartType = ChartType.Bar_Stacked_100_3D
    End Select
 Else
    Select Case Type
         Case 0
            chart.ChartType = ChartType.Bar_Clustered
        Case 1
            chart.ChartType = ChartType.Bar_Stacked
        Case 2
            chart.ChartType = ChartType.Bar_Stacked_100
    End Select
 End If
  • Create a Legend for our document.
  • C# Version:
 chart.Legend.Position = ExcelLegendPosition.Bottom;
 chart.Legend.IsVerticalLegend = false;
  • VB Version:
 chart.Legend.Position = ExcelLegendPosition.Bottom
 chart.Legend.IsVerticalLegend = False
  • Finally, with the Activate() method, we have finished creating the Bar Chart for our document. We can then Save and Close our document for sure.
  • C# Version:
chart.Activate();
string fileName = OutputDir + "\\" + this.GetType().Name + "_" + Guid.NewGuid().ToString() + GetExtension(SaveAsFormat);
// Save the document.
workbook.SaveAs(fileName);
// Close the document.
workbook.Close();
> - **VB Version:**
chart.Activate()
Dim fileName As String = OutputDir & "\" & Me.GetType().Name & "_" & Guid.NewGuid().ToString() & GetExtension(SaveAsFormat)
' Save the document.
workbook.SaveAs(fileName)
' Close the document.
workbook.Close()

So we have just shown you how to create a Bar Chart for your Excel document. In the following articles, we will show you how to use Excel Office Component to create more advanced charts. Stay tuned for more ;)

45-Day Money Back Guarantee

We will refund your full money in 45 days
if you are not satisfied with our products

Buy Now
You have successfully subcribed to our mailing list.
Dont miss out Get update on new articles and other opportunities Subscribe