Close





 
Subscribe to this RSS feed
Tuesday, 06 September 2011 08:03

SparkLine Chart

The sample demonstrates how to insert SparkLine into an excel workbook.

Published in Charts
Read more...
Monday, 24 January 2011 09:52

How to Create Column Chart with Spire.XLS for .NET

Have you ever found many fantastic charts in Excel prepared by others that/which can make their data more readable and noticeable? You may wonder how did they do that and think that must be someting complicated which should be learnt in university. But, that's not the fact. You also can create different kinds of charts no matter simple or embellished in no time by using Microsoft Excel.
Column chart, pie chart and line chart are 3 main types of excel charts:
  • Column Chart: Usually used for comparing data across categories
  • Pie Chart: Usually used for showing the relative shares of categories in a total
  • Line Chart: Best used for showing trends in a series over time. It is not really helpful for categorical data
For a better presentation on the chart, you can modify the data first. Charts offen select chart title, axis and category automatically, so choose short names as possible as you can is very useful. Microsoft Excel makes create these types of charts a snap. Let's take column chart as an example.

How to Create Column Chart in Microsoft Excel

Column chart is a smart method to present many different types of data and it is best used for comparing data across categories. Follow the simple steps below, you will easily learn how to create column chart.
  • Launch Microsoft Excel and open your excel file which contains your data.
  • In your spreadsheet which contains your data, highlight the cells.
  • Click "Insert: menu on the Excel toolbar and choose "Column"chart. You can select type of column chart here at your will.
  • Review the created column chart. If it's not as you want you can change the colors, location in the spreadsheet, wording of the title, and font used in the chart.
  • Save it for future use

How to Create Column Chart by Using Spire.XLS for .NET?

Spire.XLS presents you an easy way to create a pie chart in the Excel workbook. First, you should create a 3D column chart with the sheet.Charts.Add(ExcelChartType.Column3DClustered) method. You may set the titles of the chart by setting ChartTitle, chart.PrimaryCategoryAxis.Title and chart.PrimaryValueAxis.Title properties. They represent chart title, category title and value title respectively. What's more, we create an object to operate more about the chart. You may set the different colors of each serie with the properties cs.Format.Options.IsVaryColor. If you want to hide the value of the chart, you may set the cs.DataPoints.DefaultDataPoint.DataLabels.HasValue property false. In this demo, in order to reflect the effect of the chart, we set the grid lines of the worksheet invisible by assigning the sheet.GridLinesVisible false.
Use the C#/VB.NET codes of Spire.XLS for .NET below to Create column chart:
[C#]
using Spire.Xls;
using System.Drawing;

namespace Saveas
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a new workbook
            Workbook workbook = new Workbook();

            //Initialize worksheet
            Worksheet sheet = workbook.Worksheets[0];

            //Set the name of the chart
            sheet.Name = "Chart data";

            //Set whether the grid line is visible
            sheet.GridLinesVisible = false;

            //Writes chart data country
            sheet.Range["A1"].Value = "Country";
            sheet.Range["A2"].Value = "Cuba";
            sheet.Range["A3"].Value = "Mexico";
            sheet.Range["A4"].Value = "France";
            sheet.Range["A5"].Value = "German";

            //Jun
            sheet.Range["B1"].Value = "Jun";
            sheet.Range["B2"].NumberValue = 6000;
            sheet.Range["B3"].NumberValue = 8000;
            sheet.Range["B4"].NumberValue = 9000;
            sheet.Range["B5"].NumberValue = 8500;

            //Aug
            sheet.Range["C1"].Value = "Aug";
            sheet.Range["C2"].NumberValue = 3000;
            sheet.Range["C3"].NumberValue = 2000;
            sheet.Range["C4"].NumberValue = 2300;
            sheet.Range["C5"].NumberValue = 4200;

            //Style
            sheet.Range["A1:C1"].Style.Font.IsBold = true;
            sheet.Range["A2:C2"].Style.KnownColor = ExcelColors.LightYellow;
            sheet.Range["A3:C3"].Style.KnownColor = ExcelColors.LightGreen1;
            sheet.Range["A4:C4"].Style.KnownColor = ExcelColors.LightOrange;
            sheet.Range["A5:C5"].Style.KnownColor = ExcelColors.LightTurquoise;

            //Border
            sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeTop].Color = Color.FromArgb(0, 0, 128);
            sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
            sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeBottom].Color = Color.FromArgb(0, 0, 128);
            sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
            sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeLeft].Color = Color.FromArgb(0, 0, 128);
            sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
            sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeRight].Color = Color.FromArgb(0, 0, 128);
            sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;

            //Set number format
            sheet.Range["B2:C5"].Style.NumberFormat = "\"$\"#,##0";

            //Add a new chart worsheet to workbook
            Chart chart = sheet.Charts.Add(ExcelChartType.Column3DClustered);

            //Set region of chart data
            chart.DataRange = sheet.Range["A1:C5"];
            chart.SeriesDataFromRange = false;

            //Set position of chart
            chart.LeftColumn = 1;
            chart.TopRow = 6;
            chart.RightColumn = 11;
            chart.BottomRow = 29;

            //Chart title
            chart.ChartTitle = "Sales market by country";
            chart.ChartTitleArea.IsBold = true;
            chart.ChartTitleArea.Size = 12;

            //Category title
            chart.PrimaryCategoryAxis.Title = "Country";
            chart.PrimaryCategoryAxis.Font.IsBold = true;
            chart.PrimaryCategoryAxis.TitleArea.IsBold = true;

            //Value title
            chart.PrimaryValueAxis.Title = "Sales(in Dollars)";
            chart.PrimaryValueAxis.HasMajorGridLines = false;
            chart.PrimaryValueAxis.MinValue = 1000;
            chart.PrimaryValueAxis.TitleArea.IsBold = true;
            chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;

            foreach (Spire.Xls.Charts.ChartSerie cs in chart.Series)
            {
                //Set vary color of the chart
                cs.Format.Options.IsVaryColor = true;

                //Set the value is visible on the chart
                cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;
            }

            //Legend position
            chart.Legend.Position = LegendPositionType.Top;

            //Save the file
            workbook.SaveToFile("Sample.xls");

            //Launch the file
            System.Diagnostics.Process.Start("Sample.xls");
        }
    }
}
[Visual Basic]
Imports Spire.Xls
Imports System.Drawing

Module Module1

    Sub Main()
        'Create a new workbook
        Dim workbook As New Workbook()

        'Initialize worksheet
        Dim sheet As Worksheet = workbook.Worksheets(0)

        'Set the name of the chart
        sheet.Name = "Chart data"

        'Set whether the grid line is visible
        sheet.GridLinesVisible = False

        'Writes chart data country
        sheet.Range("A1").Value = "Country"
        sheet.Range("A2").Value = "Cuba"
        sheet.Range("A3").Value = "Mexico"
        sheet.Range("A4").Value = "France"
        sheet.Range("A5").Value = "German"

        'Jun
        sheet.Range("B1").Value = "Jun"
        sheet.Range("B2").NumberValue = 6000
        sheet.Range("B3").NumberValue = 8000
        sheet.Range("B4").NumberValue = 9000
        sheet.Range("B5").NumberValue = 8500

        'Aug
        sheet.Range("C1").Value = "Aug"
        sheet.Range("C2").NumberValue = 3000
        sheet.Range("C3").NumberValue = 2000
        sheet.Range("C4").NumberValue = 2300
        sheet.Range("C5").NumberValue = 4200

        'Style
        sheet.Range("A1:C1").Style.Font.IsBold = True
        sheet.Range("A2:C2").Style.KnownColor = ExcelColors.LightYellow
        sheet.Range("A3:C3").Style.KnownColor = ExcelColors.LightGreen1
        sheet.Range("A4:C4").Style.KnownColor = ExcelColors.LightOrange
        sheet.Range("A5:C5").Style.KnownColor = ExcelColors.LightTurquoise

        'Border
        sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeTop).Color = Color.FromArgb(0, 0, 128)
        sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeTop).LineStyle = LineStyleType.Thin
        sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeBottom).Color = Color.FromArgb(0, 0, 128)
        sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeBottom).LineStyle = LineStyleType.Thin
        sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeLeft).Color = Color.FromArgb(0, 0, 128)
        sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeLeft).LineStyle = LineStyleType.Thin
        sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeRight).Color = Color.FromArgb(0, 0, 128)
        sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeRight).LineStyle = LineStyleType.Thin

        'Set number format
        sheet.Range("B2:C5").Style.NumberFormat = """$""#,##0"

        'Add a new chart worsheet to workbook
        Dim chart As Chart = sheet.Charts.Add(ExcelChartType.Column3DClustered)

        'Set region of chart data
        chart.DataRange = sheet.Range("A1:C5")
        chart.SeriesDataFromRange = False

        'Set position of chart
        chart.LeftColumn = 1
        chart.TopRow = 6
        chart.RightColumn = 11
        chart.BottomRow = 29

        'Chart title
        chart.ChartTitle = "Sales market by country"
        chart.ChartTitleArea.IsBold = True
        chart.ChartTitleArea.Size = 12

        'Category title
        chart.PrimaryCategoryAxis.Title = "Country"
        chart.PrimaryCategoryAxis.Font.IsBold = True
        chart.PrimaryCategoryAxis.TitleArea.IsBold = True

        'Value title
        chart.PrimaryValueAxis.Title = "Sales(in Dollars)"
        chart.PrimaryValueAxis.HasMajorGridLines = False
        chart.PrimaryValueAxis.MinValue = 1000
        chart.PrimaryValueAxis.TitleArea.IsBold = True
        chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90

        For Each cs As Spire.Xls.Charts.ChartSerie In chart.Series
            'Set vary color of the chart
            cs.Format.Options.IsVaryColor = True

            'Set the value is visible on the chart
            cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = True
        Next

        'Legend position
        chart.Legend.Position = LegendPositionType.Top

        'Save doc file.
        workbook.SaveToFile("Sample.xls")

        'Launching the MS Word file.
        System.Diagnostics.Process.Start("Sample.xls")
    End Sub
End Module
After running the demo, you may find column chart appear in the document:
(Back to content)
Published in Program Guide
Read more...
Thursday, 20 January 2011 10:26

How to Create Excel Pie Charts for C#/VB.NET

Why We Create Excel Pie Charts?

Create excel pie charts is a simple method to display your data to other individuals or a group. Excel pie charts can easily relay your messages that may otherwise go unnoticed by your audience. Pie charts are helpful for international audiences because they are universally known and easily explained. And it's extremely easy to create and use in Excel.

Pie charts, unlike other charts in Microsoft Excel, require the data in your worksheet be contained in only one row or column. By indicating a "category", an additional row or column can be used. Relative sizes are easy to compare as each piece of data is represented as a portion of a whole.

How to Create Excel Pie Charts in Microsoft Excel?

Every one of you can create excel pie charts in no time by using Microsoft Excel through the following steps:
  • Launch Microsoft Office Excel and open your excel file which with the data that you want to base your pie chart on.
  • Select the data that you want to base your chart on. The last cell that you want to select will not be chosen but it will have bold outline borders around it.
  • Click "Insert" button to open Chart Wizard window. Click on "Pie" in the right side column of Chart Type. Several sub-types of pie charts will be offered for you to choose. Pick a proper type which can best match with your data and click "Next"
  • Prevew on this new interface. If the pie chart with wrong information or even no pie chart appears, click "Cancel" and select your data, try the above process again. If everything is OK, click "Next"
  • On the new window, enter labels and a title for your pie chart. Click "Finish" when you see the pie chart appears as you want.
  • On your spreadsheet, click the box and drag your mouse to manipulate the size of the chart. By right clicking the chart and box, you can get many options to edit the pie chart.

How to Use Spire.XLS for .NET to Creat Excel Pie Charts?

Spire.XLS presents you an easy way to create a pie chart in the Excel workbook. First, you should create a pie chart with the sheet.Charts.Add method. You may control the resource of the data and title of the chart by setting DataRange and ChartTitle properties. What's more, we create an object to operate more about the chart. You may set the label and value of the pie with the properties cs.CategoryLabels and cs.Values. If you want to hide the value of the pie, you may set the cs.DataPoints.DefaultDataPoint.DataLabels.HasValue property false. In this demo, in order to reflect the effect of the chart, we set the grid lines of the worksheet invisible by assigning the sheet.GridLinesVisible false.
Use the C#/VB.NET codes of Spire.XLS for .NET below to create excel pie charts:
[C#]
using Spire.Xls;
using System.Drawing;

namespace Saveas
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a new workbook
            Workbook workbook = new Workbook();

            //Initialize worksheet
            workbook.CreateEmptySheets(1);
            Worksheet sheet = workbook.Worksheets[0];

            //Set sheet name
            sheet.Name = "Chart data";

            //Set the grid lines invisible
            sheet.GridLinesVisible = false;

            //Create a chart
            Chart chart = sheet.Charts.Add(ExcelChartType.Pie3D);

            //Set region of chart data
            chart.DataRange = sheet.Range["B2:B5"];
            chart.SeriesDataFromRange = false;

            //Set position of chart
            chart.LeftColumn = 1;
            chart.TopRow = 6;
            chart.RightColumn = 9;
            chart.BottomRow = 25;

            //Chart title
            chart.ChartTitle = "Sales by year";
            chart.ChartTitleArea.IsBold = true;
            chart.ChartTitleArea.Size = 12;

            //Initialize the chart series
            Spire.Xls.Charts.ChartSerie cs = chart.Series[0];

            //Chart Labels resource
            cs.CategoryLabels = sheet.Range["A2:A5"];

            //Chart value resource
            cs.Values = sheet.Range["B2:B5"];

            //Set the value visible in the chart
            cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;

            //Year
            sheet.Range["A1"].Value = "Year";
            sheet.Range["A2"].Value = "2002";
            sheet.Range["A3"].Value = "2003";
            sheet.Range["A4"].Value = "2004";
            sheet.Range["A5"].Value = "2005";

            //Sales
            sheet.Range["B1"].Value = "Sales";
            sheet.Range["B2"].NumberValue = 4000;
            sheet.Range["B3"].NumberValue = 6000;
            sheet.Range["B4"].NumberValue = 7000;
            sheet.Range["B5"].NumberValue = 8500;

            //Style
            sheet.Range["A1:B1"].Style.Font.IsBold = true;
            sheet.Range["A2:B2"].Style.KnownColor = ExcelColors.LightYellow;
            sheet.Range["A3:B3"].Style.KnownColor = ExcelColors.LightGreen1;
            sheet.Range["A4:B4"].Style.KnownColor = ExcelColors.LightOrange;
            sheet.Range["A5:B5"].Style.KnownColor = ExcelColors.LightTurquoise;

            //Border
            sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeTop].Color = Color.FromArgb(0, 0, 128);
            sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
            sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeBottom].Color = Color.FromArgb(0, 0, 128);
            sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
            sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeLeft].Color = Color.FromArgb(0, 0, 128);
            sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
            sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeRight].Color = Color.FromArgb(0, 0, 128);
            sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
            
            //Number format
            sheet.Range["B2:C5"].Style.NumberFormat = "\"$\"#,##0";           
            chart.PlotArea.Fill.Visible = false;
                  
            //Save the file
            workbook.SaveToFile("Sample.xls");

            //Launch the file
            System.Diagnostics.Process.Start("Sample.xls");
        }
    }
}
[Visual Basic]
Imports Spire.Xls
Imports System.Drawing

Module Module1

    Sub Main()
        'Create a new workbook
        Dim workbook As New Workbook()

        'Initialize worksheet
        workbook.CreateEmptySheets(1)
        Dim sheet As Worksheet = workbook.Worksheets(0)

        'Set sheet name
        sheet.Name = "Chart data"

        'Set the grid lines invisible
        sheet.GridLinesVisible = False

        'Create a chart
        Dim chart As Chart = sheet.Charts.Add(ExcelChartType.Pie3D)

        'Set region of chart data
        chart.DataRange = sheet.Range("B2:B5")
        chart.SeriesDataFromRange = False

        'Set position of chart
        chart.LeftColumn = 1
        chart.TopRow = 6
        chart.RightColumn = 9
        chart.BottomRow = 25

        'Chart title
        chart.ChartTitle = "Sales by year"
        chart.ChartTitleArea.IsBold = True
        chart.ChartTitleArea.Size = 12

        'Set the chart
        Dim cs As Spire.Xls.Charts.ChartSerie = chart.Series(0)

        'Chart Labels resource
        cs.CategoryLabels = sheet.Range("A2:A5")

        'Chart value resource
        cs.Values = sheet.Range("B2:B5")

        'Set the value visible in the chart
        cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = True

        'Year
        sheet.Range("A1").Value = "Year"
        sheet.Range("A2").Value = "2002"
        sheet.Range("A3").Value = "2003"
        sheet.Range("A4").Value = "2004"
        sheet.Range("A5").Value = "2005"

        'Sales
        sheet.Range("B1").Value = "Sales"
        sheet.Range("B2").NumberValue = 4000
        sheet.Range("B3").NumberValue = 6000
        sheet.Range("B4").NumberValue = 7000
        sheet.Range("B5").NumberValue = 8500

        'Style
        sheet.Range("A1:B1").Style.Font.IsBold = True
        sheet.Range("A2:B2").Style.KnownColor = ExcelColors.LightYellow
        sheet.Range("A3:B3").Style.KnownColor = ExcelColors.LightGreen1
        sheet.Range("A4:B4").Style.KnownColor = ExcelColors.LightOrange
        sheet.Range("A5:B5").Style.KnownColor = ExcelColors.LightTurquoise

        'Border
        sheet.Range("A1:B5").Style.Borders(BordersLineType.EdgeTop).Color = Color.FromArgb(0, 0, 128)
        sheet.Range("A1:B5").Style.Borders(BordersLineType.EdgeTop).LineStyle = LineStyleType.Thin
        sheet.Range("A1:B5").Style.Borders(BordersLineType.EdgeBottom).Color = Color.FromArgb(0, 0, 128)
        sheet.Range("A1:B5").Style.Borders(BordersLineType.EdgeBottom).LineStyle = LineStyleType.Thin
        sheet.Range("A1:B5").Style.Borders(BordersLineType.EdgeLeft).Color = Color.FromArgb(0, 0, 128)
        sheet.Range("A1:B5").Style.Borders(BordersLineType.EdgeLeft).LineStyle = LineStyleType.Thin
        sheet.Range("A1:B5").Style.Borders(BordersLineType.EdgeRight).Color = Color.FromArgb(0, 0, 128)
        sheet.Range("A1:B5").Style.Borders(BordersLineType.EdgeRight).LineStyle = LineStyleType.Thin

        'Number format
        sheet.Range("B2:C5").Style.NumberFormat = """$""#,##0"
        chart.PlotArea.Fill.Visible = False

        'Save doc file.
        workbook.SaveToFile("Sample.xls")

        'Launch the MS Word file.
        System.Diagnostics.Process.Start("Sample.xls")

    End Sub
End Module
After running the demo, you may find a pie appear in the document:
(Back to content)
Published in Program Guide
Read more...
Sunday, 01 August 2010 16:21

Data Export Bar Chart for C#, VB.NET

Not needing to have Microsoft Excel installed on the machine, The Spire.DataExport can create Excel spreadsheet. This sample demonstrates how to export data table into xls and create bar chart.

  

Published in Cell
Read more...
Sunday, 01 August 2010 16:02

Data Export Pie3D Chart for C#, VB.NET

Not needing to have Microsoft Excel installed on the machine, The Spire.DataExport can create Excel spreadsheet. This sample demonstrates how to export data table into xls and create pie-3d chart.

 

Published in Cell
Read more...
Saturday, 03 July 2010 00:30

EXCEL Pie Charts for C#, VB.NET

 

The sample demonstrates how to create a pie chart in an excel workbook.

Pie.gif

Published in Charts
Read more...