Data Export Bar Chart in C#, VB.NET

2010-08-01 16:21:13 Written by Administrator

font size decrease font size increase font size
Print
E-mail

Rate this item

(14 votes)

Demo
C# source
VB.Net source

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.

private void button1_Click(object sender, EventArgs e)
{
    System.Data.OleDb.OleDbConnection oleDbConnection1
         = new System.Data.OleDb.OleDbConnection();
    oleDbConnection1.ConnectionString
        = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\..\..\..\Database\demo.mdb";

    System.Data.OleDb.OleDbCommand oleDbCommand1
        = new System.Data.OleDb.OleDbCommand();
    oleDbCommand1.CommandText = "select * from parts";
    oleDbCommand1.Connection = oleDbConnection1;

    System.Data.OleDb.OleDbCommand oleDbCommand2
        = new System.Data.OleDb.OleDbCommand();
    oleDbCommand2.CommandText = "select * from country";
    oleDbCommand2.Connection = oleDbConnection1;

    Spire.DataExport.XLS.CellExport cellExport4
        = new Spire.DataExport.XLS.CellExport();

    Spire.DataExport.XLS.WorkSheet workSheet4 = new Spire.DataExport.XLS.WorkSheet();
    Spire.DataExport.XLS.Chart chart2 = new Spire.DataExport.XLS.Chart();
    Spire.DataExport.XLS.ChartSeries chartSeries2 = new Spire.DataExport.XLS.ChartSeries();
    Spire.DataExport.XLS.WorkSheet workSheet5 = new Spire.DataExport.XLS.WorkSheet();
    Spire.DataExport.XLS.StripStyle stripStyle7 = new Spire.DataExport.XLS.StripStyle();
    Spire.DataExport.XLS.StripStyle stripStyle8 = new Spire.DataExport.XLS.StripStyle();

    cellExport4.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;
    cellExport4.AutoFitColWidth = true;
    cellExport4.DataFormats.CultureName = "zh-CN";
    cellExport4.DataFormats.Currency = "￥#,###,##0.00";
    cellExport4.DataFormats.DateTime = "yyyy-M-d H:mm";
    cellExport4.DataFormats.Float = "#,###,##0.00";
    cellExport4.DataFormats.Integer = "#,###,##0";
    cellExport4.DataFormats.Time = "H:mm";
    cellExport4.FileName = "chart2.xls";
    cellExport4.SheetOptions.AggregateFormat.Font.Name = "Arial";
    cellExport4.SheetOptions.CustomDataFormat.Font.Name = "Arial";
    cellExport4.SheetOptions.DefaultFont.Name = "Arial";
    cellExport4.SheetOptions.FooterFormat.Font.Name = "Arial";
    cellExport4.SheetOptions.HeaderFormat.Font.Name = "Arial";
    cellExport4.SheetOptions.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue;
    cellExport4.SheetOptions.HyperlinkFormat.Font.Name = "Arial";
    cellExport4.SheetOptions.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.Single;
    cellExport4.SheetOptions.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left;
    cellExport4.SheetOptions.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top;
    cellExport4.SheetOptions.NoteFormat.Font.Bold = true;
    cellExport4.SheetOptions.NoteFormat.Font.Name = "Tahoma";
    cellExport4.SheetOptions.NoteFormat.Font.Size = 8F;
    cellExport4.SheetOptions.TitlesFormat.Font.Bold = true;
    cellExport4.SheetOptions.TitlesFormat.Font.Name = "Arial";
    chart2.AutoColor = false;
    chart2.CategoryLabels.ColX = ((System.Byte)(1));
    chart2.CategoryLabels.ColY = ((System.Byte)(1));
    chart2.CategoryLabels.RowX = 1;
    chart2.CategoryLabels.RowY = 9;
    chart2.CategoryLabelsColumn = "Name";
    chart2.DataRangeSheet = "Sheet 2";
    chart2.Position.AutoPosition.Height = 23;
    chart2.Position.AutoPosition.Left = 1;
    chart2.Position.AutoPosition.Top = 1;
    chart2.Position.AutoPosition.Width = 11;
    chart2.Position.CustomPosition.X1 = ((System.Byte)(1));
    chart2.Position.CustomPosition.X2 = ((System.Byte)(15));
    chart2.Position.CustomPosition.Y1 = 1;
    chart2.Position.CustomPosition.Y2 = 30;
    chartSeries2.Color = Spire.DataExport.XLS.CellColor.Tan;
    chartSeries2.DataColumn = "Area";
    chartSeries2.DataRangeSheet = "Sheet 2";
    chartSeries2.Title = "Population";
    chart2.Series.Add(chartSeries2);
    chart2.Style = Spire.DataExport.XLS.ChartStyle.Bar;
    chart2.Title = "Chart demo";
    workSheet4.Charts.Add(chart2);
    workSheet4.DataExported = false;
    workSheet4.FormatsExport.CultureName = "zh-CN";
    workSheet4.FormatsExport.Currency = "￥#,###,##0.00";
    workSheet4.FormatsExport.DateTime = "yyyy-M-d H:mm";
    workSheet4.FormatsExport.Float = "#,###,##0.00";
    workSheet4.FormatsExport.Integer = "#,###,##0";
    workSheet4.FormatsExport.Time = "H:mm";
    workSheet4.Options.AggregateFormat.Font.Name = "Arial";
    workSheet4.Options.CustomDataFormat.Font.Name = "Arial";
    workSheet4.Options.DefaultFont.Name = "Arial";
    workSheet4.Options.FooterFormat.Font.Name = "Arial";
    workSheet4.Options.HeaderFormat.Font.Bold = true;
    workSheet4.Options.HeaderFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue;
    workSheet4.Options.HeaderFormat.Font.Name = "Arial";
    workSheet4.Options.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue;
    workSheet4.Options.HyperlinkFormat.Font.Name = "Arial";
    workSheet4.Options.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.Single;
    workSheet4.Options.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left;
    workSheet4.Options.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top;
    workSheet4.Options.NoteFormat.Font.Bold = true;
    workSheet4.Options.NoteFormat.Font.Name = "Tahoma";
    workSheet4.Options.NoteFormat.Font.Size = 8F;
    workSheet4.Options.TitlesFormat.Font.Bold = true;
    workSheet4.Options.TitlesFormat.Font.Name = "Times New Roman";
    workSheet4.Options.TitlesFormat.Font.Size = 13F;
    workSheet4.SheetName = "charts";
    workSheet4.StartDataCol = ((System.Byte)(0));
    workSheet5.AutoFitColWidth = true;
    workSheet5.FormatsExport.CultureName = "zh-CN";
    workSheet5.FormatsExport.Currency = "￥#,###,##0.00";
    workSheet5.FormatsExport.DateTime = "yyyy-M-d H:mm";
    workSheet5.FormatsExport.Float = "#,###,##0.00";
    workSheet5.FormatsExport.Integer = "#,###,##0";
    workSheet5.FormatsExport.Time = "H:mm";
    stripStyle7.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
    stripStyle7.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
    stripStyle7.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
    stripStyle7.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
    stripStyle7.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightGreen;
    stripStyle7.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid;
    stripStyle7.Font.Name = "Arial";
    stripStyle8.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
    stripStyle8.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
    stripStyle8.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
    stripStyle8.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
    stripStyle8.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightTurquoise;
    stripStyle8.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid;
    stripStyle8.Font.Name = "Arial";
    workSheet5.ItemStyles.Add(stripStyle7);
    workSheet5.ItemStyles.Add(stripStyle8);
    workSheet5.ItemType = Spire.DataExport.XLS.CellItemType.Col;
    workSheet5.Options.AggregateFormat.Font.Name = "Arial";
    workSheet5.Options.CustomDataFormat.Font.Name = "Arial";
    workSheet5.Options.DefaultFont.Name = "Arial";
    workSheet5.Options.FooterFormat.Font.Name = "Arial";
    workSheet5.Options.HeaderFormat.Font.Name = "Arial";
    workSheet5.Options.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue;
    workSheet5.Options.HyperlinkFormat.Font.Name = "Arial";
    workSheet5.Options.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.Single;
    workSheet5.Options.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left;
    workSheet5.Options.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top;
    workSheet5.Options.NoteFormat.Font.Bold = true;
    workSheet5.Options.NoteFormat.Font.Name = "Tahoma";
    workSheet5.Options.NoteFormat.Font.Size = 8F;
    workSheet5.Options.TitlesFormat.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
    workSheet5.Options.TitlesFormat.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
    workSheet5.Options.TitlesFormat.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
    workSheet5.Options.TitlesFormat.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
    workSheet5.Options.TitlesFormat.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightYellow;
    workSheet5.Options.TitlesFormat.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid;
    workSheet5.Options.TitlesFormat.Font.Bold = true;
    workSheet5.Options.TitlesFormat.Font.Name = "Arial";
    workSheet5.SheetName = "Sheet 2";
    workSheet5.SQLCommand = oleDbCommand2;
    workSheet5.StartDataCol = ((System.Byte)(0));
    cellExport4.Sheets.Add(workSheet4);
    cellExport4.Sheets.Add(workSheet5);
    cellExport4.SQLCommand = oleDbCommand1;

    oleDbConnection1.Open();
    try
    {
        cellExport4.SaveToFile();
    }
    finally
    {
        oleDbConnection1.Close();
    }
}

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim oleDbConnection1 As New System.Data.OleDb.OleDbConnection()
    oleDbConnection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\..\..\..\Database\demo.mdb"

    Dim oleDbCommand1 As New System.Data.OleDb.OleDbCommand()
    oleDbCommand1.CommandText = "select * from parts"
    oleDbCommand1.Connection = oleDbConnection1

    Dim oleDbCommand2 As New System.Data.OleDb.OleDbCommand()
    oleDbCommand2.CommandText = "select * from country"
    oleDbCommand2.Connection = oleDbConnection1

    Dim cellExport4 As New Spire.DataExport.XLS.CellExport()

    Dim workSheet4 As New Spire.DataExport.XLS.WorkSheet()
    Dim chart2 As New Spire.DataExport.XLS.Chart()
    Dim chartSeries2 As New Spire.DataExport.XLS.ChartSeries()
    Dim workSheet5 As New Spire.DataExport.XLS.WorkSheet()
    Dim stripStyle7 As New Spire.DataExport.XLS.StripStyle()
    Dim stripStyle8 As New Spire.DataExport.XLS.StripStyle()

    cellExport4.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView
    cellExport4.AutoFitColWidth = True
    cellExport4.DataFormats.CultureName = "zh-CN"
    cellExport4.DataFormats.Currency = "￥#,###,##0.00"
    cellExport4.DataFormats.DateTime = "yyyy-M-d H:mm"
    cellExport4.DataFormats.Float = "#,###,##0.00"
    cellExport4.DataFormats.[Integer] = "#,###,##0"
    cellExport4.DataFormats.Time = "H:mm"
    cellExport4.FileName = "chart2.xls"
    cellExport4.SheetOptions.AggregateFormat.Font.Name = "Arial"
    cellExport4.SheetOptions.CustomDataFormat.Font.Name = "Arial"
    cellExport4.SheetOptions.DefaultFont.Name = "Arial"
    cellExport4.SheetOptions.FooterFormat.Font.Name = "Arial"
    cellExport4.SheetOptions.HeaderFormat.Font.Name = "Arial"
    cellExport4.SheetOptions.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue
    cellExport4.SheetOptions.HyperlinkFormat.Font.Name = "Arial"
    cellExport4.SheetOptions.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.[Single]
    cellExport4.SheetOptions.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left
    cellExport4.SheetOptions.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top
    cellExport4.SheetOptions.NoteFormat.Font.Bold = True
    cellExport4.SheetOptions.NoteFormat.Font.Name = "Tahoma"
    cellExport4.SheetOptions.NoteFormat.Font.Size = 8.0F
    cellExport4.SheetOptions.TitlesFormat.Font.Bold = True
    cellExport4.SheetOptions.TitlesFormat.Font.Name = "Arial"
    chart2.AutoColor = False
    chart2.CategoryLabels.ColX = CByte(1)
    chart2.CategoryLabels.ColY = CByte(1)
    chart2.CategoryLabels.RowX = 1
    chart2.CategoryLabels.RowY = 9
    chart2.CategoryLabelsColumn = "Name"
    chart2.DataRangeSheet = "Sheet 2"
    chart2.Position.AutoPosition.Height = 23
    chart2.Position.AutoPosition.Left = 1
    chart2.Position.AutoPosition.Top = 1
    chart2.Position.AutoPosition.Width = 11
    chart2.Position.CustomPosition.X1 = CByte(1)
    chart2.Position.CustomPosition.X2 = CByte(15)
    chart2.Position.CustomPosition.Y1 = 1
    chart2.Position.CustomPosition.Y2 = 30
    chartSeries2.Color = Spire.DataExport.XLS.CellColor.Tan
    chartSeries2.DataColumn = "Area"
    chartSeries2.DataRangeSheet = "Sheet 2"
    chartSeries2.Title = "Population"
    chart2.Series.Add(chartSeries2)
    chart2.Style = Spire.DataExport.XLS.ChartStyle.Bar
    chart2.Title = "Chart demo"
    workSheet4.Charts.Add(chart2)
    workSheet4.DataExported = False
    workSheet4.FormatsExport.CultureName = "zh-CN"
    workSheet4.FormatsExport.Currency = "￥#,###,##0.00"
    workSheet4.FormatsExport.DateTime = "yyyy-M-d H:mm"
    workSheet4.FormatsExport.Float = "#,###,##0.00"
    workSheet4.FormatsExport.[Integer] = "#,###,##0"
    workSheet4.FormatsExport.Time = "H:mm"
    workSheet4.Options.AggregateFormat.Font.Name = "Arial"
    workSheet4.Options.CustomDataFormat.Font.Name = "Arial"
    workSheet4.Options.DefaultFont.Name = "Arial"
    workSheet4.Options.FooterFormat.Font.Name = "Arial"
    workSheet4.Options.HeaderFormat.Font.Bold = True
    workSheet4.Options.HeaderFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue
    workSheet4.Options.HeaderFormat.Font.Name = "Arial"
    workSheet4.Options.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue
    workSheet4.Options.HyperlinkFormat.Font.Name = "Arial"
    workSheet4.Options.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.[Single]
    workSheet4.Options.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left
    workSheet4.Options.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top
    workSheet4.Options.NoteFormat.Font.Bold = True
    workSheet4.Options.NoteFormat.Font.Name = "Tahoma"
    workSheet4.Options.NoteFormat.Font.Size = 8.0F
    workSheet4.Options.TitlesFormat.Font.Bold = True
    workSheet4.Options.TitlesFormat.Font.Name = "Times New Roman"
    workSheet4.Options.TitlesFormat.Font.Size = 13.0F
    workSheet4.SheetName = "charts"
    workSheet4.StartDataCol = CByte(0)
    workSheet5.AutoFitColWidth = True
    workSheet5.FormatsExport.CultureName = "zh-CN"
    workSheet5.FormatsExport.Currency = "￥#,###,##0.00"
    workSheet5.FormatsExport.DateTime = "yyyy-M-d H:mm"
    workSheet5.FormatsExport.Float = "#,###,##0.00"
    workSheet5.FormatsExport.[Integer] = "#,###,##0"
    workSheet5.FormatsExport.Time = "H:mm"
    stripStyle7.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
    stripStyle7.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
    stripStyle7.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
    stripStyle7.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
    stripStyle7.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightGreen
    stripStyle7.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid
    stripStyle7.Font.Name = "Arial"
    stripStyle8.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
    stripStyle8.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
    stripStyle8.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
    stripStyle8.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
    stripStyle8.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightTurquoise
    stripStyle8.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid
    stripStyle8.Font.Name = "Arial"
    workSheet5.ItemStyles.Add(stripStyle7)
    workSheet5.ItemStyles.Add(stripStyle8)
    workSheet5.ItemType = Spire.DataExport.XLS.CellItemType.Col
    workSheet5.Options.AggregateFormat.Font.Name = "Arial"
    workSheet5.Options.CustomDataFormat.Font.Name = "Arial"
    workSheet5.Options.DefaultFont.Name = "Arial"
    workSheet5.Options.FooterFormat.Font.Name = "Arial"
    workSheet5.Options.HeaderFormat.Font.Name = "Arial"
    workSheet5.Options.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue
    workSheet5.Options.HyperlinkFormat.Font.Name = "Arial"
    workSheet5.Options.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.[Single]
    workSheet5.Options.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left
    workSheet5.Options.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top
    workSheet5.Options.NoteFormat.Font.Bold = True
    workSheet5.Options.NoteFormat.Font.Name = "Tahoma"
    workSheet5.Options.NoteFormat.Font.Size = 8.0F
    workSheet5.Options.TitlesFormat.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
    workSheet5.Options.TitlesFormat.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
    workSheet5.Options.TitlesFormat.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
    workSheet5.Options.TitlesFormat.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
    workSheet5.Options.TitlesFormat.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightYellow
    workSheet5.Options.TitlesFormat.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid
    workSheet5.Options.TitlesFormat.Font.Bold = True
    workSheet5.Options.TitlesFormat.Font.Name = "Arial"
    workSheet5.SheetName = "Sheet 2"
    workSheet5.SQLCommand = oleDbCommand2
    workSheet5.StartDataCol = CByte(0)
    cellExport4.Sheets.Add(workSheet4)
    cellExport4.Sheets.Add(workSheet5)
    cellExport4.SQLCommand = oleDbCommand1

    oleDbConnection1.Open()
    Try
        cellExport4.SaveToFile()
    Finally
        oleDbConnection1.Close()
    End Try
End Sub

Last modified on Friday, 04 July 2014 03:21

Read 7522 times

Published in Cell

Tagged under

Social sharing

News Category

Data Export Bar Chart in C#, VB.NET

Administrator

See Also