Charts
Charts (3)
TThe sample demonstrates how to insert SparkLine into an excel workbook.
using Spire.Xls;
using System.Drawing;
namespace SparkLine
{
class Program
{
static void Main(string[] args)
{
Workbook workbook = new Workbook();
workbook.Version = ExcelVersion.Version2010;
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
//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 = 0.23;
sheet.Range["B3"].NumberValue = 0.37;
sheet.Range["B4"].NumberValue = 0.15;
sheet.Range["B5"].NumberValue = 0.25;
//Jul
sheet.Range["C1"].Value = "Jul";
sheet.Range["C2"].NumberValue = 0.1;
sheet.Range["C3"].NumberValue = 0.35;
sheet.Range["C4"].NumberValue = 0.22;
sheet.Range["C5"].NumberValue = 0.33;
//Aug
sheet.Range["D1"].Value = "Aug";
sheet.Range["D2"].NumberValue = 0.14;
sheet.Range["D3"].NumberValue = 0.36;
sheet.Range["D4"].NumberValue = 0.25;
sheet.Range["D5"].NumberValue = 0.25;
//Aug
sheet.Range["E1"].Value = "Sep";
sheet.Range["E2"].NumberValue = 0.17;
sheet.Range["E3"].NumberValue = 0.28;
sheet.Range["E4"].NumberValue = 0.39;
sheet.Range["E5"].NumberValue = 0.32;
//Style
sheet.Range["A1:E1"].Style.Font.IsBold = true;
sheet.Range["A2:E2"].Style.KnownColor = ExcelColors.LightYellow;
sheet.Range["A3:E3"].Style.KnownColor = ExcelColors.LightGreen1;
sheet.Range["A4:E4"].Style.KnownColor = ExcelColors.LightOrange;
sheet.Range["A5:E5"].Style.KnownColor = ExcelColors.LightTurquoise;
//Border
sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeTop].Color = Color.FromArgb(0, 0, 128);
sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeBottom].Color = Color.FromArgb(0, 0, 128);
sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeLeft].Color = Color.FromArgb(0, 0, 128);
sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeRight].Color = Color.FromArgb(0, 0, 128);
sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
sheet.Range["B2:D5"].Style.NumberFormatIndex = 9;
SparklineGroup sparklineGroup
= sheet.SparklineGroups.AddGroup(SparklineType.Line);
SparklineCollection sparklines = sparklineGroup.Add();
sparklines.Add(sheet["B2:E2"], sheet["F2"]);
sparklines.Add(sheet["B3:E3"], sheet["F3"]);
sparklines.Add(sheet["B4:E4"], sheet["F4"]);
sparklines.Add(sheet["B5:E5"], sheet["F5"]);
workbook.SaveToFile("Sample.xlsx");
System.Diagnostics.Process.Start(workbook.FileName);
}
}
}
Imports Spire.Xls
Imports System.Drawing
Module Module1
Sub Main()
Dim workbook As New Workbook()
workbook.Version = ExcelVersion.Version2010
workbook.CreateEmptySheets(1)
Dim sheet As Worksheet = workbook.Worksheets(0)
'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 = 0.23
sheet.Range("B3").NumberValue = 0.37
sheet.Range("B4").NumberValue = 0.15
sheet.Range("B5").NumberValue = 0.25
'Jul
sheet.Range("C1").Value = "Jul"
sheet.Range("C2").NumberValue = 0.1
sheet.Range("C3").NumberValue = 0.35
sheet.Range("C4").NumberValue = 0.22
sheet.Range("C5").NumberValue = 0.33
'Aug
sheet.Range("D1").Value = "Aug"
sheet.Range("D2").NumberValue = 0.14
sheet.Range("D3").NumberValue = 0.36
sheet.Range("D4").NumberValue = 0.25
sheet.Range("D5").NumberValue = 0.25
'Aug
sheet.Range("E1").Value = "Sep"
sheet.Range("E2").NumberValue = 0.17
sheet.Range("E3").NumberValue = 0.28
sheet.Range("E4").NumberValue = 0.39
sheet.Range("E5").NumberValue = 0.32
'Style
sheet.Range("A1:E1").Style.Font.IsBold = True
sheet.Range("A2:E2").Style.KnownColor = ExcelColors.LightYellow
sheet.Range("A3:E3").Style.KnownColor = ExcelColors.LightGreen1
sheet.Range("A4:E4").Style.KnownColor = ExcelColors.LightOrange
sheet.Range("A5:E5").Style.KnownColor = ExcelColors.LightTurquoise
'Border
sheet.Range("A1:E5").Style.Borders(BordersLineType.EdgeTop).Color = Color.FromArgb(0, 0, 128)
sheet.Range("A1:E5").Style.Borders(BordersLineType.EdgeTop).LineStyle = LineStyleType.Thin
sheet.Range("A1:E5").Style.Borders(BordersLineType.EdgeBottom).Color = Color.FromArgb(0, 0, 128)
sheet.Range("A1:E5").Style.Borders(BordersLineType.EdgeBottom).LineStyle = LineStyleType.Thin
sheet.Range("A1:E5").Style.Borders(BordersLineType.EdgeLeft).Color = Color.FromArgb(0, 0, 128)
sheet.Range("A1:E5").Style.Borders(BordersLineType.EdgeLeft).LineStyle = LineStyleType.Thin
sheet.Range("A1:E5").Style.Borders(BordersLineType.EdgeRight).Color = Color.FromArgb(0, 0, 128)
sheet.Range("A1:E5").Style.Borders(BordersLineType.EdgeRight).LineStyle = LineStyleType.Thin
sheet.Range("B2:D5").Style.NumberFormatIndex = 9
Dim sparklineGroup As SparklineGroup = sheet.SparklineGroups.AddGroup(SparklineType.Line)
Dim sparklines As SparklineCollection = sparklineGroup.Add()
sparklines.Add(sheet("B2:E2"), sheet("F2"))
sparklines.Add(sheet("B3:E3"), sheet("F3"))
sparklines.Add(sheet("B4:E4"), sheet("F4"))
sparklines.Add(sheet("B5:E5"), sheet("F5"))
workbook.SaveToFile("Sample.xlsx")
System.Diagnostics.Process.Start(workbook.FileName)
End Sub
End Module
The sample demonstrates how to edit chart worksheet in an excel workbook.
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
//Initialize worksheet
workbook.LoadFromFile("../../../../../../Data/EditChartSample.xls",true);
Worksheet sheet = workbook.Worksheets[0];
//Writes chart data
CreateChartData(sheet);
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
}
private void CreateChartData(Worksheet sheet)
{
//Jun
sheet.Range["F6"].NumberValue = 6000;
sheet.Range["F7"].NumberValue = 8000;
sheet.Range["F8"].NumberValue = 9000;
sheet.Range["F9"].NumberValue = 8500;
//Aug
sheet.Range["G6"].NumberValue = 4000;
sheet.Range["G7"].NumberValue = 7000;
sheet.Range["G8"].NumberValue = 2000;
sheet.Range["G9"].NumberValue = 5000;
sheet.Range["F6:F9"].Style.NumberFormat = "\"$\"#,##0";
sheet.Range["G6:G9"].Style.NumberFormat = "\"$\"#,##0";
}
private void ExcelDocViewer( string fileName )
{
try
{
System.Diagnostics.Process.Start(fileName);
}
catch{}
}
Private Sub btnRun_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRun.Click
Dim workbook As Workbook = New Workbook()
'Initialize worksheet
workbook.LoadFromFile("../../../../../../Data/EditChartSample.xls",True)
Dim sheet As Worksheet = workbook.Worksheets(0)
'Writes chart data
CreateChartData(sheet)
workbook.SaveToFile("Sample.xls")
ExcelDocViewer(workbook.FileName)
End Sub
Private Sub CreateChartData(ByVal sheet As Worksheet)
'Jun
sheet.Range("F6").NumberValue = 6000
sheet.Range("F7").NumberValue = 8000
sheet.Range("F8").NumberValue = 9000
sheet.Range("F9").NumberValue = 8500
'Aug
sheet.Range("G6").NumberValue = 4000
sheet.Range("G7").NumberValue = 7000
sheet.Range("G8").NumberValue = 2000
sheet.Range("G9").NumberValue = 5000
sheet.Range("F6:F9").Style.NumberFormat = """$""#,##0"
sheet.Range("G6:G9").Style.NumberFormat = """$""#,##0"
End Sub
Private Sub ExcelDocViewer(ByVal fileName As String)
Try
System.Diagnostics.Process.Start(fileName)
Catch
End Try
End Sub
Published in
Charts
Tagged under
The sample demonstrates how to create a pie chart in an excel workbook.
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
//Initialize worksheet
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Chart data";
sheet.GridLinesVisible = false;
//Add a new chart worsheet to workbook
Chart chart = null;
if (checkBox1.Checked)
{
chart = sheet.Charts.Add(ExcelChartType.Pie3D);
}
else
{
chart = sheet.Charts.Add(ExcelChartType.Pie);
}
CreateChartData(sheet);
CreateChart(sheet, chart);
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
}
private void CreateChart(Worksheet sheet, Chart chart)
{
//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;
Charts.ChartSerie cs = chart.Series[0];
cs.CategoryLabels = sheet.Range["A2:A5"];
cs.Values = sheet.Range["B2:B5"];
cs.DataFormat.ShowActiveValue = true;
}
private void CreateChartData(Worksheet sheet)
{
//Country
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";
//Jun
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;
sheet.Range["B2:C5"].Style.NumberFormat = "\"$\"#,##0";
}
private void ExcelDocViewer( string fileName )
{
try
{
System.Diagnostics.Process.Start(fileName);
}
catch{}
}
Private Sub btnRun_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRun.Click
Dim workbook As Workbook = New Workbook()
'Initialize worksheet
workbook.CreateEmptySheets(1)
Dim sheet As Worksheet = workbook.Worksheets(0)
sheet.Name = "Chart data"
sheet.GridLinesVisible = False
'Add a new chart worsheet to workbook
Dim chart As Chart = Nothing
If checkBox1.Checked Then
chart = sheet.Charts.Add(ExcelChartType.Pie3D)
Else
chart = sheet.Charts.Add(ExcelChartType.Pie)
End If
CreateChartData(sheet)
CreateChart(sheet, chart)
workbook.SaveToFile("Sample.xls")
ExcelDocViewer(workbook.FileName)
End Sub
Private Sub CreateChart(ByVal sheet As Worksheet, ByVal chart As Chart)
'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
Dim cs As Charts.ChartSerie = chart.Series(0)
cs.CategoryLabels = sheet.Range("A2:A5")
cs.Values = sheet.Range("B2:B5")
cs.DataFormat.ShowActiveValue = True
End Sub
Private Sub CreateChartData(ByVal sheet As Worksheet)
'Country
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"
'Jun
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
sheet.Range("B2:C5").Style.NumberFormat = """$""#,##0"
End Sub
Private Sub ExcelDocViewer(ByVal fileName As String)
Try
System.Diagnostics.Process.Start(fileName)
Catch
End Try
End Sub