page 91

private void btnXML_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;

    Spire.DataExport.XML.XMLExport xmlExport1
        = new Spire.DataExport.XML.XMLExport();

    xmlExport1.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;
    xmlExport1.DataFormats.CultureName = "zh-CN";
    xmlExport1.DataFormats.Currency = "c";
    xmlExport1.DataFormats.DateTime = "yyyy-M-d H:mm";
    xmlExport1.DataFormats.Float = "g";
    xmlExport1.DataFormats.Integer = "g";
    xmlExport1.DataFormats.Time = "H:mm";
    xmlExport1.FileName = "sample.xml";
    xmlExport1.SQLCommand = oleDbCommand1;

    oleDbConnection1.Open();
    xmlExport1.SaveToFile();
}

Private Sub btnXML_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnXML.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 xmlExport1 As New Spire.DataExport.XML.XMLExport()

    xmlExport1.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView
    xmlExport1.DataFormats.CultureName = "zh-CN"
    xmlExport1.DataFormats.Currency = "c"
    xmlExport1.DataFormats.DateTime = "yyyy-M-d H:mm"
    xmlExport1.DataFormats.Float = "g"
    xmlExport1.DataFormats.[Integer] = "g"
    xmlExport1.DataFormats.Time = "H:mm"
    xmlExport1.FileName = "sample.xml"
    xmlExport1.SQLCommand = oleDbCommand1

    oleDbConnection1.Open()
    xmlExport1.SaveToFile()
End Sub

Published in Demo

Tagged under

Data Export MS Access in C#, VB.NET

2010-08-01 18:20:44 Written by Administrator

Demo
C# source
VB.Net source

This sample demonstrates how to export data table into ms-access file.

The picture above represents using Excel application to open the result ms-access file.

private void btnMSAccess_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;

    Spire.DataExport.Access.AccessExport accessExport1
        = new Spire.DataExport.Access.AccessExport();

    accessExport1.DatabaseName = "test.mdb";
    accessExport1.DataFormats.CultureName = "zh-CN";
    accessExport1.DataFormats.Currency = "c";
    accessExport1.DataFormats.DateTime = "yyyy-M-d H:mm";
    accessExport1.DataFormats.Float = "g";
    accessExport1.DataFormats.Integer = "g";
    accessExport1.DataFormats.Time = "H:mm";
    accessExport1.SQLCommand = oleDbCommand1;
    accessExport1.TableName = "ExportData";

    oleDbConnection1.Open();
    accessExport1.SaveToFile();
}

Private Sub btnMSAccess_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMSAccess.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 accessExport1 As New Spire.DataExport.Access.AccessExport()

    accessExport1.DatabaseName = "test.mdb"
    accessExport1.DataFormats.CultureName = "zh-CN"
    accessExport1.DataFormats.Currency = "c"
    accessExport1.DataFormats.DateTime = "yyyy-M-d H:mm"
    accessExport1.DataFormats.Float = "g"
    accessExport1.DataFormats.[Integer] = "g"
    accessExport1.DataFormats.Time = "H:mm"
    accessExport1.SQLCommand = oleDbCommand1
    accessExport1.TableName = "ExportData"

    oleDbConnection1.Open()
    accessExport1.SaveToFile()
End Sub

Published in Demo

Tagged under

Data Export DBF in C#, VB.NET

2010-08-01 18:05:26 Written by Administrator

Demo
C# source
VB.Net source

This sample demonstrates how to export data table into dbf file.

The picture above represents using Excel application to open the result dbf file.

private void btnDBF_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;

    Spire.DataExport.DBF.DBFExport dbfExport1
        = new Spire.DataExport.DBF.DBFExport();

    dbfExport1.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;
    dbfExport1.FileName = "sample.dbf";
    dbfExport1.SQLCommand = oleDbCommand1;

    oleDbConnection1.Open();
    dbfExport1.SaveToFile();
}

Private Sub btnDBF_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDBF.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 dbfExport1 As New Spire.DataExport.DBF.DBFExport()

    dbfExport1.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView
    dbfExport1.FileName = "sample.dbf"
    dbfExport1.SQLCommand = oleDbCommand1

    oleDbConnection1.Open()
    dbfExport1.SaveToFile()
End Sub

Published in Demo

Tagged under

Data Export Clipboard in C#, VB.NET

2010-08-01 17:53:59 Written by Administrator

Demo
C# source
VB.Net source

This sample demonstrates how to export data table into clipboard.

private void btnClipboard_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;

    Spire.DataExport.Clipboard.ClipboardExport clipboardExport1
        = new Spire.DataExport.Clipboard.ClipboardExport();

    clipboardExport1.DataFormats.CultureName = "zh-CN";
    clipboardExport1.DataFormats.Currency = "c";
    clipboardExport1.DataFormats.DateTime = "yyyy-M-d H:mm";
    clipboardExport1.DataFormats.Float = "g";
    clipboardExport1.DataFormats.Integer = "g";
    clipboardExport1.DataFormats.Time = "H:mm";
    clipboardExport1.ShowContent = true;
    clipboardExport1.SQLCommand = oleDbCommand1;

    oleDbConnection1.Open();
    clipboardExport1.SaveToFile();
}

Private Sub btnClipboard_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClipboard.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 clipboardExport1 As New Spire.DataExport.Clipboard.ClipboardExport()

    clipboardExport1.DataFormats.CultureName = "zh-CN"
    clipboardExport1.DataFormats.Currency = "c"
    clipboardExport1.DataFormats.DateTime = "yyyy-M-d H:mm"
    clipboardExport1.DataFormats.Float = "g"
    clipboardExport1.DataFormats.[Integer] = "g"
    clipboardExport1.DataFormats.Time = "H:mm"
    clipboardExport1.ShowContent = True
    clipboardExport1.SQLCommand = oleDbCommand1

    oleDbConnection1.Open()
    clipboardExport1.SaveToFile()
End Sub

Published in Demo

Tagged under

Data Export RTF in C#, VB.NET

2010-08-01 17:47:08 Written by Administrator

Demo
C# source
VB.Net source

This sample demonstrates how to export data table into rtf file.

private void btnRTF_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;

    Spire.DataExport.RTF.RTFExport rtfExport1
        = new Spire.DataExport.RTF.RTFExport();

    rtfExport1.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;
    rtfExport1.DataFormats.CultureName = "zh-CN";
    rtfExport1.DataFormats.Currency = "c";
    rtfExport1.DataFormats.DateTime = "yyyy-M-d H:mm";
    rtfExport1.DataFormats.Float = "g";
    rtfExport1.DataFormats.Integer = "g";
    rtfExport1.DataFormats.Time = "H:mm";
    rtfExport1.FileName = "sample.doc";
    rtfExport1.RTFOptions.DataStyle.Font = new System.Drawing.Font("Arial", 10F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.World);
    rtfExport1.RTFOptions.FooterStyle.Font = new System.Drawing.Font("Arial", 10F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.World);
    rtfExport1.RTFOptions.HeaderStyle.Font = new System.Drawing.Font("Arial", 10F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.World);
    rtfExport1.RTFOptions.TitleStyle.Alignment = Spire.DataExport.RTF.RtfTextAlignment.Center;
    rtfExport1.RTFOptions.TitleStyle.Font = new System.Drawing.Font("Arial", 10F, System.Drawing.FontStyle.Bold);
    rtfExport1.SQLCommand = oleDbCommand1;

    oleDbConnection1.Open();
    rtfExport1.SaveToFile();
}

Private Sub btnRTF_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRTF.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 rtfExport1 As New Spire.DataExport.RTF.RTFExport()

    rtfExport1.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView
    rtfExport1.DataFormats.CultureName = "zh-CN"
    rtfExport1.DataFormats.Currency = "c"
    rtfExport1.DataFormats.DateTime = "yyyy-M-d H:mm"
    rtfExport1.DataFormats.Float = "g"
    rtfExport1.DataFormats.[Integer] = "g"
    rtfExport1.DataFormats.Time = "H:mm"
    rtfExport1.FileName = "sample.doc"
    rtfExport1.RTFOptions.DataStyle.Font = New System.Drawing.Font("Arial", 10.0F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.World)
    rtfExport1.RTFOptions.FooterStyle.Font = New System.Drawing.Font("Arial", 10.0F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.World)
    rtfExport1.RTFOptions.HeaderStyle.Font = New System.Drawing.Font("Arial", 10.0F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.World)
    rtfExport1.RTFOptions.TitleStyle.Alignment = Spire.DataExport.RTF.RtfTextAlignment.Center
    rtfExport1.RTFOptions.TitleStyle.Font = New System.Drawing.Font("Arial", 10.0F, System.Drawing.FontStyle.Bold)
    rtfExport1.SQLCommand = oleDbCommand1

    oleDbConnection1.Open()
    rtfExport1.SaveToFile()
End Sub

Published in Demo

Tagged under

Data Export PDF in C#, VB.NET

2010-08-01 17:22:45 Written by Administrator

Demo
C# source
VB.Net source

This sample demonstrates how to export data table into pdf file.

private void btnPDF_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;

    Spire.DataExport.PDF.PDFExport pdfExport1 = new Spire.DataExport.PDF.PDFExport();
    pdfExport1.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;
    pdfExport1.DataFormats.CultureName = "zh-CN";
    pdfExport1.DataFormats.Currency = "c";
    pdfExport1.DataFormats.DateTime = "yyyy-M-d H:mm";
    pdfExport1.DataFormats.Float = "g";
    pdfExport1.DataFormats.Integer = "g";
    pdfExport1.DataFormats.Time = "H:mm";
    pdfExport1.FileName = "sample.pdf";
    pdfExport1.PDFOptions.DataFont.CustomFont = new System.Drawing.Font("Arial", 10F);
    pdfExport1.PDFOptions.FooterFont.CustomFont = new System.Drawing.Font("Arial", 10F);
    pdfExport1.PDFOptions.HeaderFont.CustomFont = new System.Drawing.Font("Arial", 10F);
    pdfExport1.PDFOptions.PageOptions.Format = Spire.DataExport.PDF.PageFormat.User;
    pdfExport1.PDFOptions.PageOptions.Height = 11.67;
    pdfExport1.PDFOptions.PageOptions.MarginBottom = 0.78;
    pdfExport1.PDFOptions.PageOptions.MarginLeft = 1.17;
    pdfExport1.PDFOptions.PageOptions.MarginRight = 0.57;
    pdfExport1.PDFOptions.PageOptions.MarginTop = 0.78;
    pdfExport1.PDFOptions.PageOptions.Width = 10.25;
    pdfExport1.PDFOptions.TitleFont.CustomFont = new System.Drawing.Font("Arial", 10F);
    pdfExport1.SQLCommand = oleDbCommand1;

    oleDbConnection1.Open();
    pdfExport1.SaveToFile();
}

Private Sub btnPDF_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPDF.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 pdfExport1 As New Spire.DataExport.PDF.PDFExport()
    pdfExport1.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView
    pdfExport1.DataFormats.CultureName = "zh-CN"
    pdfExport1.DataFormats.Currency = "c"
    pdfExport1.DataFormats.DateTime = "yyyy-M-d H:mm"
    pdfExport1.DataFormats.Float = "g"
    pdfExport1.DataFormats.[Integer] = "g"
    pdfExport1.DataFormats.Time = "H:mm"
    pdfExport1.FileName = "sample.pdf"
    pdfExport1.PDFOptions.DataFont.CustomFont = New System.Drawing.Font("Arial", 10.0F)
    pdfExport1.PDFOptions.FooterFont.CustomFont = New System.Drawing.Font("Arial", 10.0F)
    pdfExport1.PDFOptions.HeaderFont.CustomFont = New System.Drawing.Font("Arial", 10.0F)
    pdfExport1.PDFOptions.PageOptions.Format = Spire.DataExport.PDF.PageFormat.User
    pdfExport1.PDFOptions.PageOptions.Height = 11.67
    pdfExport1.PDFOptions.PageOptions.MarginBottom = 0.78
    pdfExport1.PDFOptions.PageOptions.MarginLeft = 1.17
    pdfExport1.PDFOptions.PageOptions.MarginRight = 0.57
    pdfExport1.PDFOptions.PageOptions.MarginTop = 0.78
    pdfExport1.PDFOptions.PageOptions.Width = 10.25
    pdfExport1.PDFOptions.TitleFont.CustomFont = New System.Drawing.Font("Arial", 10.0F)
    pdfExport1.SQLCommand = oleDbCommand1

    oleDbConnection1.Open()
    pdfExport1.SaveToFile()
End Sub

Published in Demo

Tagged under

Data Export Style in C#, VB.NET

2010-08-01 16:42:00 Written by Administrator

Demo
C# source
VB.Net source

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

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 cellExport3
        = new Spire.DataExport.XLS.CellExport();

    Spire.DataExport.XLS.WorkSheet workSheet6 = new Spire.DataExport.XLS.WorkSheet();
    Spire.DataExport.XLS.ColumnFormat columnFormat1 = new Spire.DataExport.XLS.ColumnFormat();
    Spire.DataExport.XLS.ColumnFormat columnFormat2 = new Spire.DataExport.XLS.ColumnFormat();
    Spire.DataExport.XLS.ColumnFormat columnFormat3 = new Spire.DataExport.XLS.ColumnFormat();
    Spire.DataExport.XLS.ColumnFormat columnFormat4 = new Spire.DataExport.XLS.ColumnFormat();
    Spire.DataExport.XLS.ColumnFormat columnFormat5 = new Spire.DataExport.XLS.ColumnFormat();

    cellExport3.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;
    cellExport3.DataFormats.CultureName = "zh-CN";
    cellExport3.DataFormats.Currency = "￥#,###,##0.00";
    cellExport3.DataFormats.DateTime = "yyyy-M-d H:mm";
    cellExport3.DataFormats.Float = "#,###,##0.00";
    cellExport3.DataFormats.Integer = "#,###,##0";
    cellExport3.DataFormats.Time = "H:mm";
    cellExport3.FileName = "misc.xls";
    cellExport3.SheetOptions.AggregateFormat.Font.Name = "Arial";
    cellExport3.SheetOptions.CustomDataFormat.Font.Name = "Arial";
    cellExport3.SheetOptions.DefaultFont.Name = "Arial";
    cellExport3.SheetOptions.FooterFormat.Font.Name = "Arial";
    cellExport3.SheetOptions.HeaderFormat.Font.Name = "Arial";
    cellExport3.SheetOptions.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue;
    cellExport3.SheetOptions.HyperlinkFormat.Font.Name = "Arial";
    cellExport3.SheetOptions.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.Single;
    cellExport3.SheetOptions.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left;
    cellExport3.SheetOptions.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top;
    cellExport3.SheetOptions.NoteFormat.Font.Bold = true;
    cellExport3.SheetOptions.NoteFormat.Font.Name = "Tahoma";
    cellExport3.SheetOptions.NoteFormat.Font.Size = 8F;
    cellExport3.SheetOptions.TitlesFormat.FillStyle.Background = Spire.DataExport.XLS.CellColor.Gray40Percent;
    cellExport3.SheetOptions.TitlesFormat.Font.Bold = true;
    cellExport3.SheetOptions.TitlesFormat.Font.Name = "Arial";
    columnFormat1.FieldName = "PartNo";
    columnFormat1.Font.Bold = true;
    columnFormat1.Font.Name = "Arial";
    columnFormat2.FieldName = "VendorNo";
    columnFormat2.Font.Color = Spire.DataExport.XLS.CellColor.Color1;
    columnFormat2.Font.Italic = true;
    columnFormat2.Font.Name = "Arial";
    columnFormat3.FieldName = "Description";
    columnFormat3.Font.Name = "Arial";
    columnFormat3.Font.Strikeout = true;
    columnFormat3.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.DoubleAccounting;
    columnFormat4.FieldName = "OnHand";
    columnFormat4.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightTurquoise;
    columnFormat4.FillStyle.Foreground = Spire.DataExport.XLS.CellColor.Pink;
    columnFormat4.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.ThinGorizontal;
    columnFormat4.Font.Name = "Arial";
    columnFormat5.FieldName = "ListPrice";
    columnFormat5.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightYellow;
    columnFormat5.FillStyle.Foreground = Spire.DataExport.XLS.CellColor.SkyBlue;
    columnFormat5.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.ThinVertical;
    columnFormat5.Font.Name = "Arial";
    workSheet6.ColumnFormats.Add(columnFormat1);
    workSheet6.ColumnFormats.Add(columnFormat2);
    workSheet6.ColumnFormats.Add(columnFormat3);
    workSheet6.ColumnFormats.Add(columnFormat4);
    workSheet6.ColumnFormats.Add(columnFormat5);
    workSheet6.FormatsExport.CultureName = "zh-CN";
    workSheet6.FormatsExport.Currency = "￥#,###,##0.00";
    workSheet6.FormatsExport.DateTime = "yyyy-M-d H:mm";
    workSheet6.FormatsExport.Float = "#,###,##0.00";
    workSheet6.FormatsExport.Integer = "#,###,##0";
    workSheet6.FormatsExport.Time = "H:mm";
    workSheet6.Options.AggregateFormat.Font.Name = "Arial";
    workSheet6.Options.CustomDataFormat.Font.Name = "Arial";
    workSheet6.Options.DefaultFont.Name = "Arial";
    workSheet6.Options.FooterFormat.Font.Name = "Arial";
    workSheet6.Options.HeaderFormat.Font.Name = "Arial";
    workSheet6.Options.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue;
    workSheet6.Options.HyperlinkFormat.Font.Name = "Arial";
    workSheet6.Options.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.Single;
    workSheet6.Options.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left;
    workSheet6.Options.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top;
    workSheet6.Options.NoteFormat.Font.Bold = true;
    workSheet6.Options.NoteFormat.Font.Name = "Tahoma";
    workSheet6.Options.NoteFormat.Font.Size = 8F;
    workSheet6.Options.TitlesFormat.Font.Bold = true;
    workSheet6.Options.TitlesFormat.Font.Name = "Arial";
    workSheet6.SheetName = "Sheet 1";
    workSheet6.SQLCommand = oleDbCommand1;
    workSheet6.StartDataCol = ((System.Byte)(0));
    cellExport3.Sheets.Add(workSheet6);
    cellExport3.GetDataParams += new Spire.DataExport.Delegates.DataParamsEventHandler(this.cellExport3_GetDataParams);

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

private void cellExport3_GetDataParams(object sender, Spire.DataExport.EventArgs.DataParamsEventArgs e)
{
    if ((e.Sheet == 0) && (e.Col == 6))
    {
        e.FormatText = (sender as Spire.DataExport.XLS.WorkSheet).ExportCell.DataFormats.Currency;
    }
}

Private WithEvents cellExport3 As Spire.DataExport.XLS.CellExport
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 workSheet6 As New Spire.DataExport.XLS.WorkSheet()
    Dim columnFormat1 As New Spire.DataExport.XLS.ColumnFormat()
    Dim columnFormat2 As New Spire.DataExport.XLS.ColumnFormat()
    Dim columnFormat3 As New Spire.DataExport.XLS.ColumnFormat()
    Dim columnFormat4 As New Spire.DataExport.XLS.ColumnFormat()
    Dim columnFormat5 As New Spire.DataExport.XLS.ColumnFormat()

    cellExport3 = New Spire.DataExport.XLS.CellExport
    cellExport3.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView
    cellExport3.DataFormats.CultureName = "zh-CN"
    cellExport3.DataFormats.Currency = "￥#,###,##0.00"
    cellExport3.DataFormats.DateTime = "yyyy-M-d H:mm"
    cellExport3.DataFormats.Float = "#,###,##0.00"
    cellExport3.DataFormats.[Integer] = "#,###,##0"
    cellExport3.DataFormats.Time = "H:mm"
    cellExport3.FileName = "misc.xls"
    cellExport3.SheetOptions.AggregateFormat.Font.Name = "Arial"
    cellExport3.SheetOptions.CustomDataFormat.Font.Name = "Arial"
    cellExport3.SheetOptions.DefaultFont.Name = "Arial"
    cellExport3.SheetOptions.FooterFormat.Font.Name = "Arial"
    cellExport3.SheetOptions.HeaderFormat.Font.Name = "Arial"
    cellExport3.SheetOptions.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue
    cellExport3.SheetOptions.HyperlinkFormat.Font.Name = "Arial"
    cellExport3.SheetOptions.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.[Single]
    cellExport3.SheetOptions.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left
    cellExport3.SheetOptions.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top
    cellExport3.SheetOptions.NoteFormat.Font.Bold = True
    cellExport3.SheetOptions.NoteFormat.Font.Name = "Tahoma"
    cellExport3.SheetOptions.NoteFormat.Font.Size = 8.0F
    cellExport3.SheetOptions.TitlesFormat.FillStyle.Background = Spire.DataExport.XLS.CellColor.Gray40Percent
    cellExport3.SheetOptions.TitlesFormat.Font.Bold = True
    cellExport3.SheetOptions.TitlesFormat.Font.Name = "Arial"
    columnFormat1.FieldName = "PartNo"
    columnFormat1.Font.Bold = True
    columnFormat1.Font.Name = "Arial"
    columnFormat2.FieldName = "VendorNo"
    columnFormat2.Font.Color = Spire.DataExport.XLS.CellColor.Color1
    columnFormat2.Font.Italic = True
    columnFormat2.Font.Name = "Arial"
    columnFormat3.FieldName = "Description"
    columnFormat3.Font.Name = "Arial"
    columnFormat3.Font.Strikeout = True
    columnFormat3.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.DoubleAccounting
    columnFormat4.FieldName = "OnHand"
    columnFormat4.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightTurquoise
    columnFormat4.FillStyle.Foreground = Spire.DataExport.XLS.CellColor.Pink
    columnFormat4.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.ThinGorizontal
    columnFormat4.Font.Name = "Arial"
    columnFormat5.FieldName = "ListPrice"
    columnFormat5.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightYellow
    columnFormat5.FillStyle.Foreground = Spire.DataExport.XLS.CellColor.SkyBlue
    columnFormat5.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.ThinVertical
    columnFormat5.Font.Name = "Arial"
    workSheet6.ColumnFormats.Add(columnFormat1)
    workSheet6.ColumnFormats.Add(columnFormat2)
    workSheet6.ColumnFormats.Add(columnFormat3)
    workSheet6.ColumnFormats.Add(columnFormat4)
    workSheet6.ColumnFormats.Add(columnFormat5)
    workSheet6.FormatsExport.CultureName = "zh-CN"
    workSheet6.FormatsExport.Currency = "￥#,###,##0.00"
    workSheet6.FormatsExport.DateTime = "yyyy-M-d H:mm"
    workSheet6.FormatsExport.Float = "#,###,##0.00"
    workSheet6.FormatsExport.[Integer] = "#,###,##0"
    workSheet6.FormatsExport.Time = "H:mm"
    workSheet6.Options.AggregateFormat.Font.Name = "Arial"
    workSheet6.Options.CustomDataFormat.Font.Name = "Arial"
    workSheet6.Options.DefaultFont.Name = "Arial"
    workSheet6.Options.FooterFormat.Font.Name = "Arial"
    workSheet6.Options.HeaderFormat.Font.Name = "Arial"
    workSheet6.Options.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue
    workSheet6.Options.HyperlinkFormat.Font.Name = "Arial"
    workSheet6.Options.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.[Single]
    workSheet6.Options.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left
    workSheet6.Options.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top
    workSheet6.Options.NoteFormat.Font.Bold = True
    workSheet6.Options.NoteFormat.Font.Name = "Tahoma"
    workSheet6.Options.NoteFormat.Font.Size = 8.0F
    workSheet6.Options.TitlesFormat.Font.Bold = True
    workSheet6.Options.TitlesFormat.Font.Name = "Arial"
    workSheet6.SheetName = "Sheet 1"
    workSheet6.SQLCommand = oleDbCommand1
    workSheet6.StartDataCol = CByte(0)
    cellExport3.Sheets.Add(workSheet6)

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

Private Sub cellExport3_GetDataParams(ByVal sender As Object, ByVal e As Spire.DataExport.EventArgs.DataParamsEventArgs) Handles cellExport3.GetDataParams
    If (e.Sheet = 0) AndAlso (e.Col = 6) Then
        e.FormatText = cellExport3.DataFormats.Currency
    End If
End Sub

Published in Cell

Tagged under

Data Export Bar Chart in C#, VB.NET

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

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

Published in Cell

Tagged under

Data Export Pie3D Chart in C#, VB.NET

2010-08-01 16:02:38 Written by Administrator

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 pie-3d 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 cellExport2
        = new Spire.DataExport.XLS.CellExport();
    Spire.DataExport.XLS.WorkSheet workSheet3 = new Spire.DataExport.XLS.WorkSheet();
    Spire.DataExport.XLS.Chart chart1 = new Spire.DataExport.XLS.Chart();
    Spire.DataExport.XLS.ChartSeries chartSeries1 = new Spire.DataExport.XLS.ChartSeries();
    Spire.DataExport.XLS.StripStyle stripStyle5 = new Spire.DataExport.XLS.StripStyle();
    Spire.DataExport.XLS.StripStyle stripStyle6 = new Spire.DataExport.XLS.StripStyle();

    cellExport2.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;
    cellExport2.AutoFitColWidth = true;
    cellExport2.DataFormats.CultureName = "zh-CN";
    cellExport2.DataFormats.Currency = "￥#,###,##0.00";
    cellExport2.DataFormats.DateTime = "yyyy-M-d H:mm";
    cellExport2.DataFormats.Float = "#,###,##0.00";
    cellExport2.DataFormats.Integer = "#,###,##0";
    cellExport2.DataFormats.Time = "H:mm";
    cellExport2.FileName = "chart1.xls";
    cellExport2.SheetOptions.AggregateFormat.Font.Name = "Arial";
    cellExport2.SheetOptions.CustomDataFormat.Font.Name = "Arial";
    cellExport2.SheetOptions.DefaultFont.Name = "Arial";
    cellExport2.SheetOptions.FooterFormat.Font.Name = "Arial";
    cellExport2.SheetOptions.HeaderFormat.Font.Name = "Arial";
    cellExport2.SheetOptions.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue;
    cellExport2.SheetOptions.HyperlinkFormat.Font.Name = "Arial";
    cellExport2.SheetOptions.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.Single;
    cellExport2.SheetOptions.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left;
    cellExport2.SheetOptions.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top;
    cellExport2.SheetOptions.NoteFormat.Font.Bold = true;
    cellExport2.SheetOptions.NoteFormat.Font.Name = "Tahoma";
    cellExport2.SheetOptions.NoteFormat.Font.Size = 8F;
    cellExport2.SheetOptions.TitlesFormat.Font.Bold = true;
    cellExport2.SheetOptions.TitlesFormat.Font.Name = "Arial";
    workSheet3.AutoFitColWidth = true;
    chart1.CategoryLabels.ColX = ((System.Byte)(1));
    chart1.CategoryLabels.ColY = ((System.Byte)(1));
    chart1.CategoryLabels.RowX = 1;
    chart1.CategoryLabels.RowY = 9;
    chart1.CategoryLabelsColumn = "Name";
    chart1.DataRangeSheet = "charts";
    chart1.Position.AutoPosition.Height = 12;
    chart1.Position.AutoPosition.Top = 1;
    chart1.Position.AutoPosition.Width = 6;
    chartSeries1.DataColumn = "Population";
    chartSeries1.DataRangeSheet = "charts";
    chartSeries1.Title = "Population";
    chart1.Series.Add(chartSeries1);
    chart1.Style = Spire.DataExport.XLS.ChartStyle.Pie3d;
    chart1.Title = "Chart demo";
    workSheet3.Charts.Add(chart1);
    workSheet3.FormatsExport.CultureName = "zh-CN";
    workSheet3.FormatsExport.Currency = "￥#,###,##0.00";
    workSheet3.FormatsExport.DateTime = "yyyy-M-d H:mm";
    workSheet3.FormatsExport.Float = "#,###,##0.00";
    workSheet3.FormatsExport.Integer = "#,###,##0";
    workSheet3.FormatsExport.Time = "H:mm";
    stripStyle5.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
    stripStyle5.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
    stripStyle5.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
    stripStyle5.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
    stripStyle5.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightGreen;
    stripStyle5.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid;
    stripStyle5.Font.Name = "Arial";
    stripStyle6.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
    stripStyle6.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
    stripStyle6.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
    stripStyle6.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
    stripStyle6.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightTurquoise;
    stripStyle6.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid;
    stripStyle6.Font.Name = "Arial";
    workSheet3.ItemStyles.Add(stripStyle5);
    workSheet3.ItemStyles.Add(stripStyle6);
    workSheet3.ItemType = Spire.DataExport.XLS.CellItemType.Col;
    workSheet3.Options.AggregateFormat.Font.Name = "Arial";
    workSheet3.Options.CustomDataFormat.Font.Name = "Arial";
    workSheet3.Options.DefaultFont.Name = "Arial";
    workSheet3.Options.FooterFormat.Font.Name = "Arial";
    workSheet3.Options.HeaderFormat.Font.Bold = true;
    workSheet3.Options.HeaderFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue;
    workSheet3.Options.HeaderFormat.Font.Name = "Arial";
    workSheet3.Options.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue;
    workSheet3.Options.HyperlinkFormat.Font.Name = "Arial";
    workSheet3.Options.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.Single;
    workSheet3.Options.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left;
    workSheet3.Options.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top;
    workSheet3.Options.NoteFormat.Font.Bold = true;
    workSheet3.Options.NoteFormat.Font.Name = "Tahoma";
    workSheet3.Options.NoteFormat.Font.Size = 8F;
    workSheet3.Options.TitlesFormat.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
    workSheet3.Options.TitlesFormat.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
    workSheet3.Options.TitlesFormat.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
    workSheet3.Options.TitlesFormat.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
    workSheet3.Options.TitlesFormat.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightYellow;
    workSheet3.Options.TitlesFormat.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid;
    workSheet3.Options.TitlesFormat.Font.Bold = true;
    workSheet3.Options.TitlesFormat.Font.Name = "Arial";
    workSheet3.SheetName = "charts";
    workSheet3.SQLCommand = oleDbCommand2;
    workSheet3.StartDataCol = ((System.Byte)(0));
    cellExport2.Sheets.Add(workSheet3);
    cellExport2.SQLCommand = oleDbCommand1;

    oleDbConnection1.Open();
    try
    {
        cellExport2.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 cellExport2 As New Spire.DataExport.XLS.CellExport()
    Dim workSheet3 As New Spire.DataExport.XLS.WorkSheet()
    Dim chart1 As New Spire.DataExport.XLS.Chart()
    Dim chartSeries1 As New Spire.DataExport.XLS.ChartSeries()
    Dim stripStyle5 As New Spire.DataExport.XLS.StripStyle()
    Dim stripStyle6 As New Spire.DataExport.XLS.StripStyle()

    cellExport2.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView
    cellExport2.AutoFitColWidth = True
    cellExport2.DataFormats.CultureName = "zh-CN"
    cellExport2.DataFormats.Currency = "￥#,###,##0.00"
    cellExport2.DataFormats.DateTime = "yyyy-M-d H:mm"
    cellExport2.DataFormats.Float = "#,###,##0.00"
    cellExport2.DataFormats.[Integer] = "#,###,##0"
    cellExport2.DataFormats.Time = "H:mm"
    cellExport2.FileName = "chart1.xls"
    cellExport2.SheetOptions.AggregateFormat.Font.Name = "Arial"
    cellExport2.SheetOptions.CustomDataFormat.Font.Name = "Arial"
    cellExport2.SheetOptions.DefaultFont.Name = "Arial"
    cellExport2.SheetOptions.FooterFormat.Font.Name = "Arial"
    cellExport2.SheetOptions.HeaderFormat.Font.Name = "Arial"
    cellExport2.SheetOptions.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue
    cellExport2.SheetOptions.HyperlinkFormat.Font.Name = "Arial"
    cellExport2.SheetOptions.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.[Single]
    cellExport2.SheetOptions.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left
    cellExport2.SheetOptions.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top
    cellExport2.SheetOptions.NoteFormat.Font.Bold = True
    cellExport2.SheetOptions.NoteFormat.Font.Name = "Tahoma"
    cellExport2.SheetOptions.NoteFormat.Font.Size = 8.0F
    cellExport2.SheetOptions.TitlesFormat.Font.Bold = True
    cellExport2.SheetOptions.TitlesFormat.Font.Name = "Arial"
    workSheet3.AutoFitColWidth = True
    chart1.CategoryLabels.ColX = CByte(1)
    chart1.CategoryLabels.ColY = CByte(1)
    chart1.CategoryLabels.RowX = 1
    chart1.CategoryLabels.RowY = 9
    chart1.CategoryLabelsColumn = "Name"
    chart1.DataRangeSheet = "charts"
    chart1.Position.AutoPosition.Height = 12
    chart1.Position.AutoPosition.Top = 1
    chart1.Position.AutoPosition.Width = 6
    chartSeries1.DataColumn = "Population"
    chartSeries1.DataRangeSheet = "charts"
    chartSeries1.Title = "Population"
    chart1.Series.Add(chartSeries1)
    chart1.Style = Spire.DataExport.XLS.ChartStyle.Pie3d
    chart1.Title = "Chart demo"
    workSheet3.Charts.Add(chart1)
    workSheet3.FormatsExport.CultureName = "zh-CN"
    workSheet3.FormatsExport.Currency = "￥#,###,##0.00"
    workSheet3.FormatsExport.DateTime = "yyyy-M-d H:mm"
    workSheet3.FormatsExport.Float = "#,###,##0.00"
    workSheet3.FormatsExport.[Integer] = "#,###,##0"
    workSheet3.FormatsExport.Time = "H:mm"
    stripStyle5.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
    stripStyle5.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
    stripStyle5.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
    stripStyle5.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
    stripStyle5.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightGreen
    stripStyle5.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid
    stripStyle5.Font.Name = "Arial"
    stripStyle6.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
    stripStyle6.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
    stripStyle6.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
    stripStyle6.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
    stripStyle6.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightTurquoise
    stripStyle6.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid
    stripStyle6.Font.Name = "Arial"
    workSheet3.ItemStyles.Add(stripStyle5)
    workSheet3.ItemStyles.Add(stripStyle6)
    workSheet3.ItemType = Spire.DataExport.XLS.CellItemType.Col
    workSheet3.Options.AggregateFormat.Font.Name = "Arial"
    workSheet3.Options.CustomDataFormat.Font.Name = "Arial"
    workSheet3.Options.DefaultFont.Name = "Arial"
    workSheet3.Options.FooterFormat.Font.Name = "Arial"
    workSheet3.Options.HeaderFormat.Font.Bold = True
    workSheet3.Options.HeaderFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue
    workSheet3.Options.HeaderFormat.Font.Name = "Arial"
    workSheet3.Options.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue
    workSheet3.Options.HyperlinkFormat.Font.Name = "Arial"
    workSheet3.Options.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.[Single]
    workSheet3.Options.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left
    workSheet3.Options.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top
    workSheet3.Options.NoteFormat.Font.Bold = True
    workSheet3.Options.NoteFormat.Font.Name = "Tahoma"
    workSheet3.Options.NoteFormat.Font.Size = 8.0F
    workSheet3.Options.TitlesFormat.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
    workSheet3.Options.TitlesFormat.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
    workSheet3.Options.TitlesFormat.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
    workSheet3.Options.TitlesFormat.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
    workSheet3.Options.TitlesFormat.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightYellow
    workSheet3.Options.TitlesFormat.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid
    workSheet3.Options.TitlesFormat.Font.Bold = True
    workSheet3.Options.TitlesFormat.Font.Name = "Arial"
    workSheet3.SheetName = "charts"
    workSheet3.SQLCommand = oleDbCommand2
    workSheet3.StartDataCol = CByte(0)
    cellExport2.Sheets.Add(workSheet3)
    cellExport2.SQLCommand = oleDbCommand1

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

Published in Cell

Tagged under

Data Export Formula in C#, VB.NET

2010-08-01 15:55:19 Written by Administrator

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 add formulas into xls.

private void button1_Click(object sender, EventArgs e)
{
    Spire.DataExport.XLS.CellExport cellExport5
        = new Spire.DataExport.XLS.CellExport();

    cellExport5.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;
    cellExport5.ColumnsWidth.AddRange(new object[] {
                                                            "100",
                                                            "200"});
    cellExport5.DataExported = false;
    cellExport5.DataFormats.CultureName = "zh-CN";
    cellExport5.DataFormats.Currency = "￥#,###,##0.00";
    cellExport5.DataFormats.DateTime = "yyyy-M-d H:mm";
    cellExport5.DataFormats.Float = "#,###,##0.00";
    cellExport5.DataFormats.Integer = "#,###,##0";
    cellExport5.DataFormats.Time = "H:mm";
    cellExport5.FileName = "formula.xls";
    cellExport5.SheetOptions.AggregateFormat.Font.Name = "Arial";
    cellExport5.SheetOptions.CustomDataFormat.Font.Name = "Arial";
    cellExport5.SheetOptions.DefaultFont.Name = "Arial";
    cellExport5.SheetOptions.FooterFormat.Font.Name = "Arial";
    cellExport5.SheetOptions.HeaderFormat.Font.Name = "Arial";
    cellExport5.SheetOptions.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue;
    cellExport5.SheetOptions.HyperlinkFormat.Font.Name = "Arial";
    cellExport5.SheetOptions.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.Single;
    cellExport5.SheetOptions.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left;
    cellExport5.SheetOptions.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top;
    cellExport5.SheetOptions.NoteFormat.Font.Bold = true;
    cellExport5.SheetOptions.NoteFormat.Font.Name = "Tahoma";
    cellExport5.SheetOptions.NoteFormat.Font.Size = 8F;
    cellExport5.SheetOptions.TitlesFormat.Font.Bold = true;
    cellExport5.SheetOptions.TitlesFormat.Font.Name = "Arial";

    ushort currentRow = 1;
    string currentFormula = string.Empty;

    Spire.DataExport.XLS.Cell cell = null;

    cellExport5.Cells.Clear();
    cellExport5.SetColumnWidth(1, 32);
    cellExport5.SetColumnWidth(2, 16);
    cellExport5.SetColumnWidth(3, 16);

    cell = cellExport5.AddString(1, currentRow++, "Examples of formulas :");
    cell.Format.Font.Bold = true;
    cell.Format.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightGreen;
    cell.Format.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid;
    cell.Format.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;

    cell = cellExport5.AddString(1, ++currentRow, "Test data:");
    cell.Format.Font.Bold = true;
    cell.Format.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightGreen;
    cell.Format.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid;
    cell.Format.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;

    //test data
    cellExport5.AddNumeric(2, currentRow, 7.3);
    cellExport5.AddNumeric(3, currentRow, 5);
    cellExport5.AddNumeric(4, currentRow, 8.2);
    cellExport5.AddNumeric(5, currentRow, 4);
    cellExport5.AddNumeric(6, currentRow, 3);
    cellExport5.AddNumeric(7, currentRow++, 11.3);


    cell = cellExport5.AddString(1, ++currentRow, "Formulas");
    cell.Format.Font.Bold = true;
    cell.Format.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightGreen;
    cell.Format.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid;
    cell.Format.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;

    cell = cellExport5.AddString(2, currentRow, "Results");
    cell.Format.Font.Bold = true;
    cell.Format.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightGreen;
    cell.Format.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid;
    cell.Format.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;

    //str.
    currentFormula = "=\"hello\"";
    cellExport5.AddString(1, ++currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow, "=\"hello\"");
    cellExport5.AddFormula(3, currentRow, "=\"" + new string(new char[] { '\u4f60', '\u597d' }) + "\"");



    //int.
    currentFormula = "=300";
    cellExport5.AddString(1, ++currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow, currentFormula);

    // float
    currentFormula = "=3389.639421";
    cellExport5.AddString(1, ++currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow, currentFormula);

    //bool.
    currentFormula = "=false";
    cellExport5.AddString(1, ++currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow, currentFormula);

    currentFormula = "=1+2+3+4+5-6-7+8-9";
    cellExport5.AddString(1, ++currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow, currentFormula);

    currentFormula = "=33*3/4-2+10";
    cellExport5.AddString(1, ++currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow, currentFormula);


    // sheet reference
    currentFormula = "=Sheet1!$B$3";
    cellExport5.AddString(1, ++currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow, currentFormula);

    // sheet area reference
    currentFormula = "=AVERAGE(Sheet1!$D$3:G$3)";
    cellExport5.AddString(1, ++currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow, currentFormula);

    // Functions
    currentFormula = "=Count(3,5,8,10,2,34)";
    cellExport5.AddString(1, ++currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow++, currentFormula);


    currentFormula = "=NOW()";
    cellExport5.AddString(1, currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow++, currentFormula);

    currentFormula = "=SECOND(11)";
    cellExport5.AddString(1, currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow++, currentFormula);

    currentFormula = "=MINUTE(12)";
    cellExport5.AddString(1, currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow++, currentFormula);

    currentFormula = "=MONTH(9)";
    cellExport5.AddString(1, currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow++, currentFormula);

    currentFormula = "=DAY(10)";
    cellExport5.AddString(1, currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow++, currentFormula);

    currentFormula = "=TIME(4,5,7)";
    cellExport5.AddString(1, currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow++, currentFormula);

    currentFormula = "=DATE(6,4,2)";
    cellExport5.AddString(1, currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow++, currentFormula);

    currentFormula = "=RAND()";
    cellExport5.AddString(1, currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow++, currentFormula);

    currentFormula = "=HOUR(12)";
    cellExport5.AddString(1, currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow++, currentFormula);

    currentFormula = "=TEXT(\"world\", \"$d\")";
    cellExport5.AddString(1, currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow++, currentFormula);

    currentFormula = "=MOD(5,3)";
    cellExport5.AddString(1, currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow++, currentFormula);

    currentFormula = "=WEEKDAY(3)";
    cellExport5.AddString(1, currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow++, currentFormula);

    currentFormula = "=YEAR(23)";
    cellExport5.AddString(1, currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow++, currentFormula);

    currentFormula = "=NOT(true)";
    cellExport5.AddString(1, currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow++, currentFormula);

    currentFormula = "=OR(true)";
    cellExport5.AddString(1, currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow++, currentFormula);

    currentFormula = "=AND(TRUE)";
    cellExport5.AddString(1, currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow++, currentFormula);

    currentFormula = "=VALUE(30)";
    cellExport5.AddString(1, currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow++, currentFormula);

    currentFormula = "=LEN(\"world\")";
    cellExport5.AddString(1, currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow++, currentFormula);

    currentFormula = "=MID(\"world\",4,2)";
    cellExport5.AddString(1, currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow++, currentFormula);

    currentFormula = "=ROUND(7,3)";
    cellExport5.AddString(1, currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow++, currentFormula);

    currentFormula = "=SIGN(4)";
    cellExport5.AddString(1, currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow++, currentFormula);

    currentFormula = "=INT(200)";
    cellExport5.AddString(1, currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow++, currentFormula);

    currentFormula = "=ABS(-1.21)";
    cellExport5.AddString(1, currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow++, currentFormula);

    currentFormula = "=LN(15)";
    cellExport5.AddString(1, currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow++, currentFormula);

    currentFormula = "=EXP(20)";
    cellExport5.AddString(1, currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow++, currentFormula);

    currentFormula = "=SQRT(40)";
    cellExport5.AddString(1, currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow++, currentFormula);

    currentFormula = "=PI()";
    cellExport5.AddString(1, currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow++, currentFormula);

    currentFormula = "=COS(9)";
    cellExport5.AddString(1, currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow++, currentFormula);

    currentFormula = "=SIN(45)";
    cellExport5.AddString(1, currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow++, currentFormula);

    currentFormula = "=MAX(10,30)";
    cellExport5.AddString(1, currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow++, currentFormula);

    currentFormula = "=MIN(5,7)";
    cellExport5.AddString(1, currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow++, currentFormula);

    currentFormula = "=AVERAGE(12,45)";
    cellExport5.AddString(1, currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow++, currentFormula);

    currentFormula = "=SUM(18,29)";
    cellExport5.AddString(1, currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow++, currentFormula);

    currentFormula = "=IF(4,2,2)";
    cellExport5.AddString(1, currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow++, currentFormula);

    currentFormula = "=SUBTOTAL(3,Sheet1!B2:E3)";
    cellExport5.AddString(1, currentRow, currentFormula);
    cellExport5.AddFormula(2, currentRow++, currentFormula);

    cellExport5.SaveToFile();
}

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim cellExport5 As Spire.DataExport.XLS.CellExport
    cellExport5 = New Spire.DataExport.XLS.CellExport

    cellExport5.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView
    cellExport5.ColumnsWidth.AddRange(New Object() {"100", "200"})
    cellExport5.DataExported = False
    cellExport5.DataFormats.CultureName = "zh-CN"
    cellExport5.DataFormats.Currency = "￥#,###,##0.00"
    cellExport5.DataFormats.DateTime = "yyyy-M-d H:mm"
    cellExport5.DataFormats.Float = "#,###,##0.00"
    cellExport5.DataFormats.Integer = "#,###,##0"
    cellExport5.DataFormats.Time = "H:mm"
    cellExport5.FileName = "formula.xls"
    cellExport5.SheetOptions.AggregateFormat.Font.Name = "Arial"
    cellExport5.SheetOptions.CustomDataFormat.Font.Name = "Arial"
    cellExport5.SheetOptions.DefaultFont.Name = "Arial"
    cellExport5.SheetOptions.FooterFormat.Font.Name = "Arial"
    cellExport5.SheetOptions.HeaderFormat.Font.Name = "Arial"
    cellExport5.SheetOptions.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue
    cellExport5.SheetOptions.HyperlinkFormat.Font.Name = "Arial"
    cellExport5.SheetOptions.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.Single
    cellExport5.SheetOptions.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left
    cellExport5.SheetOptions.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top
    cellExport5.SheetOptions.NoteFormat.Font.Bold = True
    cellExport5.SheetOptions.NoteFormat.Font.Name = "Tahoma"
    cellExport5.SheetOptions.NoteFormat.Font.Size = 8.0!
    cellExport5.SheetOptions.TitlesFormat.Font.Bold = True
    cellExport5.SheetOptions.TitlesFormat.Font.Name = "Arial"

    Dim currentRow As Integer = 1
    Dim firstCol As UInt16 = Convert.ToUInt16(1)
    Dim secondCol As UInt16 = Convert.ToUInt16(2)
    Dim currentFormula As String = String.Empty

    Dim cell As cell = Nothing

    cellExport5.Cells.Clear()
    cellExport5.SetColumnWidth(System.Convert.ToUInt16(1), System.Convert.ToUInt16(32))
    cellExport5.SetColumnWidth(System.Convert.ToUInt16(2), System.Convert.ToUInt16(16))
    cellExport5.SetColumnWidth(System.Convert.ToUInt16(3), System.Convert.ToUInt16(16))

    cell = cellExport5.AddString(System.Convert.ToUInt16(1), System.Convert.ToUInt16(currentRow), "Examples of formulas :")
    currentRow += 1
    cell.Format.Font.Bold = True
    cell.Format.FillStyle.Background = CellColor.LightGreen
    cell.Format.FillStyle.Pattern = Pattern.Solid
    cell.Format.Borders.Bottom.Style = CellBorderStyle.Medium

    cell = cellExport5.AddString(System.Convert.ToUInt16(1), System.Convert.ToUInt16(currentRow), "Test data:")
    currentRow += 1
    cell.Format.Font.Bold = True
    cell.Format.FillStyle.Background = CellColor.LightGreen
    cell.Format.FillStyle.Pattern = Pattern.Solid
    cell.Format.Borders.Bottom.Style = CellBorderStyle.Medium

    'test data
    cellExport5.AddNumeric(System.Convert.ToUInt16(2), System.Convert.ToUInt16(currentRow), 7.3)
    cellExport5.AddNumeric(System.Convert.ToUInt16(3), System.Convert.ToUInt16(currentRow), 5)
    cellExport5.AddNumeric(System.Convert.ToUInt16(4), System.Convert.ToUInt16(currentRow), 8.2)
    cellExport5.AddNumeric(System.Convert.ToUInt16(5), System.Convert.ToUInt16(currentRow), 4)
    cellExport5.AddNumeric(System.Convert.ToUInt16(6), System.Convert.ToUInt16(currentRow), 3)
    cellExport5.AddNumeric(System.Convert.ToUInt16(7), System.Convert.ToUInt16(currentRow), 11.3)
    currentRow += 1
    currentRow += 1


    cell = cellExport5.AddString(Convert.ToUInt16(1), Convert.ToUInt16(currentRow), "Formulas")
    cell.Format.Font.Bold = True
    cell.Format.FillStyle.Background = CellColor.LightGreen
    cell.Format.FillStyle.Pattern = Pattern.Solid
    cell.Format.Borders.Bottom.Style = CellBorderStyle.Medium

    cell = cellExport5.AddString(secondCol, Convert.ToUInt16(currentRow), "Results")
    currentRow += 1
    cell.Format.Font.Bold = True
    cell.Format.FillStyle.Background = CellColor.LightGreen
    cell.Format.FillStyle.Pattern = Pattern.Solid
    cell.Format.Borders.Bottom.Style = CellBorderStyle.Medium

    'str.
    currentFormula = "=""hello"""
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), "=""hello""")
    cellExport5.AddFormula(Convert.ToUInt16(3), Convert.ToUInt16(currentRow), "=""" & ChrW(20320) & ChrW(22909) + """")
    currentRow += 1



    'int.
    currentFormula = "=300"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1

    ' float
    currentFormula = "=3389.639421"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1


    'bool.
    currentFormula = "=false"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1

    currentFormula = "=1+2+3+4+5-6-7+8-9"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1

    currentFormula = "=33*3/4-2+10"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1

    ' sheet reference
    currentFormula = "=Sheet1!$B$3"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1

    ' sheet area reference
    currentFormula = "=AVERAGE(Sheet1!$D$3:G$3)"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1

    ' Functions
    currentFormula = "=Count(3,5,8,10,2,34)"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1


    currentFormula = "=NOW()"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1

    currentFormula = "=SECOND(11)"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1

    currentFormula = "=MINUTE(12)"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1

    currentFormula = "=MONTH(9)"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1

    currentFormula = "=DAY(10)"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1

    currentFormula = "=TIME(4,5,7)"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1

    currentFormula = "=DATE(6,4,2)"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1

    currentFormula = "=RAND()"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1

    currentFormula = "=HOUR(12)"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1

    currentFormula = "=TEXT(""world"", ""$d"")"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1

    currentFormula = "=MOD(5,3)"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1

    currentFormula = "=WEEKDAY(3)"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1

    currentFormula = "=YEAR(23)"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1

    currentFormula = "=NOT(true)"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1

    currentFormula = "=OR(true)"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1

    currentFormula = "=AND(TRUE)"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1

    currentFormula = "=VALUE(30)"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1

    currentFormula = "=LEN(""world"")"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1

    currentFormula = "=MID(""world"",4,2)"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1

    currentFormula = "=ROUND(7,3)"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1

    currentFormula = "=SIGN(4)"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1

    currentFormula = "=INT(200)"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1

    currentFormula = "=ABS(-1.21)"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1

    currentFormula = "=LN(15)"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1

    currentFormula = "=EXP(20)"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1

    currentFormula = "=SQRT(40)"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1

    currentFormula = "=PI()"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1

    currentFormula = "=COS(9)"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1

    currentFormula = "=SIN(45)"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1

    currentFormula = "=MAX(10,30)"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1

    currentFormula = "=MIN(5,7)"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1

    currentFormula = "=AVERAGE(12,45)"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1

    currentFormula = "=SUM(18,29)"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1

    currentFormula = "=IF(4,2,2)"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1

    currentFormula = "=SUBTOTAL(3,Sheet1!B2:E3)"
    cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
    cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
    currentRow += 1
    cellExport5.SaveToFile()
End Sub

Published in Cell

Tagged under

News Category

.NET (1326)

Children categories

Spire.XLS for .NET (389)

Spire.Doc for .NET (338)

Spire.DataExport for .NET (33)

Spire.PDF for .NET (291)

Spire.PDFViewer for .NET (19)

Spire.Barcode for .NET (16)

Spire.DocViewer for .NET (8)

Spire.Presentation for .NET (205)

Spire.Spreadsheet for .NET (1)

Spire.Email for .NET (19)

Spire.OCR for .NET (5)

Spire.PDFViewer for MAUI (1)

Data Export XML in C#, VB.NET

Data Export MS Access in C#, VB.NET

Data Export DBF in C#, VB.NET

Data Export Clipboard in C#, VB.NET

Data Export RTF in C#, VB.NET

Data Export PDF in C#, VB.NET

Data Export Style in C#, VB.NET

Data Export Bar Chart in C#, VB.NET

Data Export Pie3D Chart in C#, VB.NET

Data Export Formula in C#, VB.NET

More...

Data Export Multiple Sheets in C#, VB.NET

Data Export Html in C#, VB.NET

Data Export Cell in C#, VB.NET

Word Merge in C#, VB.NET