page 347

		private void btnRun_Click(object sender, System.EventArgs e)
		{
			Workbook workbook = new Workbook();
			
			//Initialize worksheet
			Worksheet sheet = workbook.Worksheets[0];

			sheet.InsertDataTable((DataTable)this.dataGrid1.DataSource,true,2,1,-1,-1);

			//Sets body style
			CellStyle oddStyle = workbook.Styles.Add("oddStyle");
			oddStyle.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
			oddStyle.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
			oddStyle.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
			oddStyle.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
			oddStyle.KnownColor = ExcelColors.LightGreen1;

			CellStyle evenStyle = workbook.Styles.Add("evenStyle");
			evenStyle.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
			evenStyle.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
			evenStyle.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
			evenStyle.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
			evenStyle.KnownColor = ExcelColors.LightTurquoise;

			foreach( CellRange range in  sheet.AllocatedRange.Rows)
			{
				if (range.Row % 2 == 0)
					range.CellStyleName = evenStyle.Name;
			    else
					range.CellStyleName = oddStyle.Name;
			}

			//Sets header style
			CellStyle styleHeader = sheet.Rows[0].Style;
			styleHeader.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
			styleHeader.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
			styleHeader.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
			styleHeader.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
			styleHeader.VerticalAlignment = VerticalAlignType.Center;
			styleHeader.KnownColor = ExcelColors.Green;
			styleHeader.Font.KnownColor = ExcelColors.White;
			styleHeader.Font.IsBold = true;

			sheet.Columns[sheet.AllocatedRange.LastColumn - 1].Style.NumberFormat = "\"$\"#,##0";
			sheet.Columns[sheet.AllocatedRange.LastColumn - 2].Style.NumberFormat =  "\"$\"#,##0";

			sheet.AllocatedRange.AutoFitColumns();
			sheet.AllocatedRange.AutoFitRows();

			sheet.Rows[0].RowHeight = 20;

			workbook.SaveToFile("sample.xls");
			ExcelDocViewer( workbook.FileName );
		}



		private void Form1_Load(object sender, System.EventArgs e)
		{
			Workbook workbook = new Workbook();
			
			workbook.LoadFromFile(@"..\..\..\..\..\..\Data\DataTableSample.xls");
			//Initialize worksheet
			Worksheet sheet = workbook.Worksheets[0];

			this.dataGrid1.DataSource =  sheet.ExportDataTable();
		}


		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
			Dim sheet As Worksheet = workbook.Worksheets(0)

			sheet.InsertDataTable(CType(Me.dataGrid1.DataSource, DataTable),True,2,1,-1,-1)

			'Sets body style
			Dim oddStyle As CellStyle = workbook.Styles.Add("oddStyle")
			oddStyle.Borders(BordersLineType.EdgeLeft).LineStyle = LineStyleType.Thin
			oddStyle.Borders(BordersLineType.EdgeRight).LineStyle = LineStyleType.Thin
			oddStyle.Borders(BordersLineType.EdgeTop).LineStyle = LineStyleType.Thin
			oddStyle.Borders(BordersLineType.EdgeBottom).LineStyle = LineStyleType.Thin
			oddStyle.KnownColor = ExcelColors.LightGreen1

			Dim evenStyle As CellStyle = workbook.Styles.Add("evenStyle")
			evenStyle.Borders(BordersLineType.EdgeLeft).LineStyle = LineStyleType.Thin
			evenStyle.Borders(BordersLineType.EdgeRight).LineStyle = LineStyleType.Thin
			evenStyle.Borders(BordersLineType.EdgeTop).LineStyle = LineStyleType.Thin
			evenStyle.Borders(BordersLineType.EdgeBottom).LineStyle = LineStyleType.Thin
			evenStyle.KnownColor = ExcelColors.LightTurquoise

			For Each range As CellRange In sheet.AllocatedRange.Rows
				If range.Row Mod 2 = 0 Then
					range.CellStyleName = evenStyle.Name
				Else
					range.CellStyleName = oddStyle.Name
				End If
			Next range

			'Sets header style
			Dim styleHeader As CellStyle = sheet.Rows(0).Style
			styleHeader.Borders(BordersLineType.EdgeLeft).LineStyle = LineStyleType.Thin
			styleHeader.Borders(BordersLineType.EdgeRight).LineStyle = LineStyleType.Thin
			styleHeader.Borders(BordersLineType.EdgeTop).LineStyle = LineStyleType.Thin
			styleHeader.Borders(BordersLineType.EdgeBottom).LineStyle = LineStyleType.Thin
			styleHeader.VerticalAlignment = VerticalAlignType.Center
			styleHeader.KnownColor = ExcelColors.Green
			styleHeader.Font.KnownColor = ExcelColors.White
			styleHeader.Font.IsBold = True

			sheet.Columns(sheet.AllocatedRange.LastColumn - 1).Style.NumberFormat = """$""#,##0"
			sheet.Columns(sheet.AllocatedRange.LastColumn - 2).Style.NumberFormat = """$""#,##0"

			sheet.AllocatedRange.AutoFitColumns()
			sheet.AllocatedRange.AutoFitRows()

			sheet.Rows(0).RowHeight = 20

			workbook.SaveToFile("sample.xls")
			ExcelDocViewer(workbook.FileName)
		End Sub


		Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
			Dim workbook As Workbook = New Workbook()

			workbook.LoadFromFile("..\..\..\..\..\..\Data\DataTableSample.xls")
			'Initailize worksheet
			Dim sheet As Worksheet = workbook.Worksheets(0)

			Me.dataGrid1.DataSource = sheet.ExportDataTable()
		End Sub


		Private Sub ExcelDocViewer(ByVal fileName As String)
			Try
				System.Diagnostics.Process.Start(fileName)
			Catch
			End Try
		End Sub

Published in DataTable

Tagged under

Demo
C# source
VB.Net source

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

Demo
C# source
VB.Net source

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

Published in Charts

Tagged under

EXCEL Write Images in C#, VB.NET

EXCEL Read Images in C#, VB.NET

Excel TextAlign in C#, VB.NET

EXCEL Number Format in C#, VB.NET

Start «341 342 343 344 345 346347348 »End

Page 347 of 348

News Category

Knowledgebase (2432)

Children categories

Purchase (7)

Licensing (7)

Benchmark (1)

Java (485)

.NET (1327)

Cloud (13)

CPP (76)

Python (365)

AI (4)

JavaScript (125)

Spire.OfficeJs (6)

Spire.Agent.Office (15)

EXCEL Data Import in C#, VB.NET

EXCEL Edit Chart Sheet in C#, VB.NET

EXCEL Pie Charts in C#, VB.NET

More...

EXCEL Write Images in C#, VB.NET

EXCEL Read Images in C#, VB.NET

Excel TextAlign in C#, VB.NET

EXCEL Number Format in C#, VB.NET