Thursday, 20 January 2011 10:26

Create Excel Pie Chart in 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:

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 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;

            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";

            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;

            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;

            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

            //Launch the file
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 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

        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"

        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

        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

        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.

        'Launch the MS Word file.

    End Sub
End Module

After running the demo, you may find a pie appear in the document:

Generate Excel Pie Chart

Published in Chart
Wednesday, 19 January 2011 06:50

Create Excel Group in C#, VB.NET

Excel group separate data in Excel worksheet into several groups. Each group may present information of one item. For example, there is a worksheet about sales information about several kinds of products. Users can collect one kind of product in a group to distinguish products type.

Spire.XLS for .NET, an easy-to-use component to manipulate Microsoft Excel workbooks, enables users to create Excel group by using C#, VB.NET. This guide will show the easiest method to realize this function via Spire.XLS for .NET.

Download and install Spire.XLS for .NET. Then invoke sheet.GroupByRows(int firstRow, int lastRow, bool isCollapsed) method to create Excel group by rows. If you want to create group by columns, invoke sheet.GroupByColumns() method directly. After running, you can get the following result:

Create Excel Group

using Spire.Xls;

namespace ExcelGroup
    class Group
        static void Main(string[] args)
            Workbook workbook = new Workbook();
            Worksheet sheet = workbook.Worksheets[0];

            sheet.GroupByRows(2, 9, true);

            workbook.SaveToFile("Group.xlsx", ExcelVersion.Version2010);
Imports Spire.Xls

Namespace ExcelGroup
	Friend Class Group
		Shared Sub Main(ByVal args() As String)
			Dim workbook As New Workbook()
			Dim sheet As Worksheet = workbook.Worksheets(0)

			sheet.GroupByRows(2, 9, True)

			workbook.SaveToFile("Group.xlsx", ExcelVersion.Version2010)
		End Sub
	End Class
End Namespace
Published in Data
Wednesday, 05 January 2011 08:37

Add Excel Borders in C#, VB.NET

In Excel, you can add borders to individual cells to emphasize data, mark summarized values, or separate data in cell ranges. Borders can also be formatted with different line styles and colors. This article presents how we can apply border styles to Excel cells using Spire.XLS.

Step 1: Create a Workbook instance and get the first worksheet.

Workbook wb = new Workbook();
Worksheet sheet = wb.Worksheets[0];

Step 2: Get the cell range where you want to apply border style.

CellRange range = sheet.Range["B2:D4"];

Step 3: Call BorderInside() and BorderAround() methods to apply styles to borders inside and around the range. To add a border to the top, bottom, left and right, use the Borders[BordersLineType.EdgeTop].LineStyle property, Borders[BordersLineType.EdgeBottom].LineStyle property and alike.

range.BorderInside(LineStyleType.Thin, Color.Blue);
range.BorderAround(LineStyleType.Medium, Color.Blue);

Step 4: Save to file.

wb.SaveToFile("result.xlsx", ExcelVersion.Version2013);


Add Excel Borders in C#, VB.NET

Full Code:

using Spire.Xls;
using System.Drawing;
namespace AddExcelBorder

    class Program

        static void Main(string[] args)
            Workbook wb = new Workbook();
            Worksheet sheet = wb.Worksheets[0];

            //apply border style to the range B2:D4
            CellRange range = sheet.Range["B2:D4"];
            range.BorderInside(LineStyleType.Thin, Color.Blue);
            range.BorderAround(LineStyleType.Medium, Color.Blue);

            //apply border style to the range D7:F9
            range = sheet.Range["D7:F9"];
            range.Borders.LineStyle = LineStyleType.Double;
            range.Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.None;
            range.Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;

            //apply border style to the range F12:H14
            range = sheet.Range["F12:H14"];
            range.Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.Thin;
            range.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thick;
            range.Borders.Color = Color.Red;

            wb.SaveToFile("result.xlsx", ExcelVersion.Version2013);

Imports Spire.Xls
Imports System.Drawing
Namespace AddExcelBorder

	Class Program

		Private Shared Sub Main(args As String())
			Dim wb As New Workbook()
			Dim sheet As Worksheet = wb.Worksheets(0)

			'apply border style to the range B2:D4
			Dim range As CellRange = sheet.Range("B2:D4")
			range.BorderInside(LineStyleType.Thin, Color.Blue)
			range.BorderAround(LineStyleType.Medium, Color.Blue)

			'apply border style to the range D7:F9
			range = sheet.Range("D7:F9")
			range.Borders.LineStyle = LineStyleType.[Double]
			range.Borders(BordersLineType.DiagonalDown).LineStyle = LineStyleType.None
			range.Borders(BordersLineType.DiagonalUp).LineStyle = LineStyleType.None

			'apply border style to the range F12:H14
			range = sheet.Range("F12:H14")
			range.Borders(BordersLineType.DiagonalDown).LineStyle = LineStyleType.Thin
			range.Borders(BordersLineType.EdgeBottom).LineStyle = LineStyleType.Thick
			range.Borders.Color = Color.Red

			wb.SaveToFile("result.xlsx", ExcelVersion.Version2013)

		End Sub
	End Class
End Namespace
Published in Cells
Wednesday, 05 January 2011 08:36

Set Excel Font in C#, VB.NET

Excel font makes the whole Excel colorful. Whatever you emphasize some special cell data or beautify the whole document, you can start from setting Excel font, such as font style, size, color, bold, underline, italic etc. This section will introduce a solution to set Excel font via a .NET Excel component in C#, VB.NET.

Spire.XLS for .NET, applied both in ASP.NET websites and Windows Forms application, enables users to generate, edit and covert Excel files to other files. Using this Excel component, you can set your Excel font by the class Spire.Xls.CellStyle.Font very quickly. This class enables you to set Excel font style, size, color, bold, underline and italic which are the most commonly used formats, also it can set data alignment, strikethrough, subscript, superscript etc. Please view the Excel font effect in below picture:

Set Excel Cell Font

Here you can download Spire,XLS for .NET. After installing it on system and adding Spire.Xls dll, you can begin to set Excel font by below code.

Whole Code:

using Spire.Xls;

namespace Excel_font
    class Program
        static void Main(string[] args)
            //create a new workbook and load Excel data
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"..\Excel Font.xlsx");
            Worksheet sheet = workbook.Worksheets[0];

            //Set excel font style
            sheet.Range["A3:E3"].Style.Font.FontName = "Comic Sans MS";
            sheet.Range["A4:E14"].Style.Font.FontName = "Corbel";
            //set excel font size
            sheet.Range["A2:E2"].Style.Font.Size = 50;
            sheet.Range["A3:E3"].Style.Font.Size = 25;
            sheet.Range["A4:E14"].Style.Font.Size = 12;
            //set excel cell data to be bold
            sheet.Range["A3:E3"].Style.Font.IsBold = true;
            //set excel cell data to be underline
            sheet.Range["A3:E3"].Style.Font.Underline = FontUnderlineType.Single;
            //set excel cell data color
            sheet.Range["A3:E3"].Style.Font.Color = Color.DarkOliveGreen;
            sheet.Range["A4:E14"].Style.Font.Color = Color.Firebrick;
            //set excel cell data to be italic
            sheet.Range["A4:E14"].Style.Font.IsItalic = true;

            //save and launch the file
Imports Spire.Xls

Namespace Excel_font
    Class Program
        Private Shared Sub Main(ByVal args() As String)
            'create a new workbook and load Excel data
            Dim workbook As Workbook = New Workbook
            workbook.LoadFromFile("..\Excel Font.xlsx")
            Dim sheet As Worksheet = workbook.Worksheets(0)
            'Set excel font style
            sheet.Range("A3:E3").Style.Font.FontName = "Comic Sans MS"
            sheet.Range("A4:E14").Style.Font.FontName = "Corbel"
            'set excel font size
            sheet.Range("A2:E2").Style.Font.Size = 50
            sheet.Range("A3:E3").Style.Font.Size = 25
            sheet.Range("A4:E14").Style.Font.Size = 12
            'set excel cell data to be bold
            sheet.Range("A3:E3").Style.Font.IsBold = true
            'set excel cell data to be underline
            sheet.Range("A3:E3").Style.Font.Underline = FontUnderlineType.Single
            'set excel cell data color
            sheet.Range("A3:E3").Style.Font.Color = Color.DarkOliveGreen
            sheet.Range("A4:E14").Style.Font.Color = Color.Firebrick
            'set excel cell data to be italic
            sheet.Range("A4:E14").Style.Font.IsItalic = true
            'save and launch the file
        End Sub
    End Class
End Namespace
Published in Data
Wednesday, 05 January 2011 07:49

Set Cell Fill in Excel in C#, VB.NET

Cell Fill is one kind of cell format settings in Excel, which includes cell background color and pattern style. With cell fill, the appearance of Excel file can be beautified. Also, data in cell which is set background color or pattern style can be more obvious.

Spire.XLS for .NET, a professional .NET Excel component on manipulating Excel files, enables users to set in Excel. And this guide will focuses on how to realize Excel cell fill in C#, VB.NET, including cell background color and pattern style settings. User can set style property of CellRange class which Spire.XLS for .NET provides to get wanted background color and pattern style of one specified cell or a cell range. The following screenshot shows the result- workbook without cell fill setting.

Add Excel Cell Fill

Download and install Spire.XLS for .NET and use the following code to set cell fill.

Sample Code:

   worksheet.Range["A7:F7"].Style.Color = Color.Yellow;
   worksheet.Range["A8:F8"].Style.FillPattern = ExcelPatternType.Percent125Gray;
  worksheet.Range("A7:F7").Style.Color = Color.Yellow
  worksheet.Range("A8:F8").Style.FillPattern = ExcelPatternType.Percent125Gray
Published in Cells
Wednesday, 05 January 2011 06:15

Insert Hyperlink in Excel

Excel Hyperlink guides users to get more information about data in one cell. For example, the hyperlink links text (city name) in cell to a city history webpage and can help readers learn more about a city. Besides website address, the hyperlink can be an Email address or a file. Generally speaking, if data in on cell is website address like or Email address, the hyperlink will be generated automatically.

Spire.XLS for .NET, a professional .NET Excel component to generate, open and write Excel files, enables users to insert hyperlink in Excel by using C#, VB.NET. This guide illustrates how to realize this function quickly via Spire.XLS for .NET.

Now, I will insert hyperlink for Text “Chicago” and the Email address besides “Chicago” cell. Invoke sheet.Hyperlinks.Add(cellRange) method to insert hyperlink for specified cell. Then, set TextToDisplay, Type, Address properties for this hyperlink.Download and install Spire.XLS for .NET. Use the following code to insert hyperlink in Excel with C#, VB.NET.

Insert Excel Hyperlink

using Spire.Xls;

namespace ExcelHyperlink
    class XLSLink
        static void Main(string[] args)
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"E:\Work\Documents\ExcelFiles\Student Info.xlsx");
            Worksheet sheet = workbook.Worksheets[0];

            HyperLink UrlLink= sheet.HyperLinks.Add(sheet.Range["E8"]);
            UrlLink.TextToDisplay = sheet.Range["E8"].Text;

            HyperLink MailLink = sheet.HyperLinks.Add(sheet.Range["F8"]);       
            MailLink.TextToDisplay = sheet.Range["F8"].Text;
            MailLink.Type = HyperLinkType.Url;
            MailLink.Address = "";

            workbook.SaveToFile("ExcelHyperlink.xlsx", ExcelVersion.Version2010);

Imports Spire.Xls

Namespace ExcelHyperlink
    Friend Class XLSLink
        Shared Sub Main(ByVal args() As String)
            Dim workbook As New Workbook()
            workbook.LoadFromFile("E:\Work\Documents\ExcelFiles\Student Info.xlsx")
            Dim sheet As Worksheet = workbook.Worksheets(0)

            Dim UrlLink As HyperLink = sheet.HyperLinks.Add(sheet.Range("E8"))
            UrlLink.TextToDisplay = sheet.Range("E8").Text
            UrlLink.Type = HyperLinkType.Url
            UrlLink.Address = ""

            Dim MailLink As HyperLink = sheet.HyperLinks.Add(sheet.Range("F8"))
            MailLink.TextToDisplay = sheet.Range("F8").Text
            MailLink.Type = HyperLinkType.Url
            MailLink.Address = ""

            workbook.SaveToFile("ExcelHyperlink.xlsx", ExcelVersion.Version2010)
        End Sub
    End Class
End Namespace
Published in Link
Sunday, 01 August 2010 16:42

Data Export Style in C#, VB.NET

How to export data table to Excel file and set cell style.

Published in Cell
Sunday, 01 August 2010 15:29

Data Export Multiple Sheets in C#, VB.NET

How to export multiple data tables to different worksheets.


Published in Cell
Page 2 of 2