In order to display and print data, we need to export it from database to files, for example, Excel. Actually, Excel is the most frequently used file format to save data and it is convenient for printing data.

In this post, I want to talk something about how to export data to Excel. Generally speaking, every company owns a database to save data. If workers want to get some data, they should export to Excel firstly.

In this method which I will show, we get a datatable from database firstly, and then export to Excel. As is known, one Excel workbook has several worksheets. Now, there are two datatables and we need to export data to two worksheets according to datatables.

There is a component used, Spire.DataExport, which specializes in exporting data to various format files. Therefore, download and install this component firstly.

Create a new Windows Form project and add Spire.DataExport DLL file as reference in this project.

Design the form. Add a label to explain what we will do and a button to run.

The following code shows how to export data to multiple worksheets with C#.

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 cellExport1

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

    Spire.DataExport.XLS.WorkSheet workSheet1 = new Spire.DataExport.XLS.WorkSheet();

    Spire.DataExport.XLS.StripStyle stripStyle1 = new Spire.DataExport.XLS.StripStyle();

    Spire.DataExport.XLS.StripStyle stripStyle2 = new Spire.DataExport.XLS.StripStyle();

    Spire.DataExport.XLS.WorkSheet workSheet2 = new Spire.DataExport.XLS.WorkSheet();

    Spire.DataExport.XLS.StripStyle stripStyle3 = new Spire.DataExport.XLS.StripStyle();

    Spire.DataExport.XLS.StripStyle stripStyle4 = new Spire.DataExport.XLS.StripStyle();

    cellExport1.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;

    cellExport1.AutoFitColWidth = true;

    cellExport1.AutoFormula = true;

    cellExport1.DataFormats.CultureName = "zh-CN";

    cellExport1.DataFormats.Currency = "$#,###,##0.00";

    cellExport1.DataFormats.DateTime = "yyyy-M-d H:mm";

    cellExport1.DataFormats.Float = "#,###,##0.00";

    cellExport1.DataFormats.Integer = "#,###,##0";

    cellExport1.DataFormats.Time = "H:mm";

    cellExport1.FileName = "Sheets.xls";

    cellExport1.SheetOptions.AggregateFormat.Font.Name = "Arial";

    cellExport1.SheetOptions.CustomDataFormat.Font.Name = "Arial";

    cellExport1.SheetOptions.DefaultFont.Name = "Arial";

    cellExport1.SheetOptions.FooterFormat.Font.Name = "Arial";

    cellExport1.SheetOptions.HeaderFormat.Font.Name = "Arial";

    cellExport1.SheetOptions.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue;

    cellExport1.SheetOptions.HyperlinkFormat.Font.Name = "Arial";

    cellExport1.SheetOptions.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.Single;

    cellExport1.SheetOptions.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left;

    cellExport1.SheetOptions.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top;

    cellExport1.SheetOptions.NoteFormat.Font.Bold = true;

    cellExport1.SheetOptions.NoteFormat.Font.Name = "Tahoma";

    cellExport1.SheetOptions.NoteFormat.Font.Size = 8F;

    cellExport1.SheetOptions.TitlesFormat.Font.Bold = true;

    cellExport1.SheetOptions.TitlesFormat.Font.Name = "Arial";

    workSheet1.AutoFitColWidth = true;

    workSheet1.FormatsExport.CultureName = "zh-CN";

    workSheet1.FormatsExport.Currency = "￥#,###,##0.00";

    workSheet1.FormatsExport.DateTime = "yyyy-M-d H:mm";

    workSheet1.FormatsExport.Float = "#,###,##0.00";

    workSheet1.FormatsExport.Integer = "#,###,##0";

    workSheet1.FormatsExport.Time = "H:mm";

    stripStyle1.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;

    stripStyle1.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;

    stripStyle1.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;

    stripStyle1.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;

    stripStyle1.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightGreen;

    stripStyle1.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid;

    stripStyle1.Font.Name = "Arial";

    stripStyle2.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;

    stripStyle2.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;

    stripStyle2.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;

    stripStyle2.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;

    stripStyle2.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightTurquoise;

    stripStyle2.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid;

    stripStyle2.Font.Name = "Arial";

    workSheet1.ItemStyles.Add(stripStyle1);

    workSheet1.ItemStyles.Add(stripStyle2);

    workSheet1.ItemType = Spire.DataExport.XLS.CellItemType.Col;

    workSheet1.Options.AggregateFormat.Font.Name = "Arial";

    workSheet1.Options.CustomDataFormat.Font.Name = "Arial";

    workSheet1.Options.DefaultFont.Name = "Arial";

    workSheet1.Options.FooterFormat.Font.Name = "Arial";

    workSheet1.Options.HeaderFormat.Font.Bold = true;

    workSheet1.Options.HeaderFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue;

    workSheet1.Options.HeaderFormat.Font.Name = "Arial";

    workSheet1.Options.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue;

    workSheet1.Options.HyperlinkFormat.Font.Name = "Arial";

    workSheet1.Options.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.Single;

    workSheet1.Options.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left;

    workSheet1.Options.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top;

    workSheet1.Options.NoteFormat.Font.Bold = true;

    workSheet1.Options.NoteFormat.Font.Name = "Tahoma";

    workSheet1.Options.NoteFormat.Font.Size = 8F;

    workSheet1.Options.TitlesFormat.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;

    workSheet1.Options.TitlesFormat.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;

    workSheet1.Options.TitlesFormat.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;

    workSheet1.Options.TitlesFormat.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;

    workSheet1.Options.TitlesFormat.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightYellow;

    workSheet1.Options.TitlesFormat.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid;

    workSheet1.Options.TitlesFormat.Font.Bold = true;

    workSheet1.Options.TitlesFormat.Font.Name = "Arial";

    workSheet1.SheetName = "parts";

    workSheet1.SQLCommand = oleDbCommand1;

    workSheet1.StartDataCol = ((System.Byte)(0));

    workSheet2.AutoFitColWidth = true;

    workSheet2.FormatsExport.CultureName = "zh-CN";

    workSheet2.FormatsExport.Currency = "￥#,###,##0.00";

    workSheet2.FormatsExport.DateTime = "yyyy-M-d H:mm";

    workSheet2.FormatsExport.Float = "#,###,##0.00";

    workSheet2.FormatsExport.Integer = "#,###,##0";

    workSheet2.FormatsExport.Time = "H:mm";

    stripStyle3.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;

    stripStyle3.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;

    stripStyle3.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;

    stripStyle3.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;

    stripStyle3.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightGreen;

    stripStyle3.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid;

    stripStyle3.Font.Name = "Arial";

    stripStyle4.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;

    stripStyle4.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;

    stripStyle4.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;

    stripStyle4.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;

    stripStyle4.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightTurquoise;

    stripStyle4.FillStyle.Foreground = Spire.DataExport.XLS.CellColor.White;

    stripStyle4.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid;

    stripStyle4.Font.Name = "Arial";

    workSheet2.ItemStyles.Add(stripStyle3);

    workSheet2.ItemStyles.Add(stripStyle4);

    workSheet2.ItemType = Spire.DataExport.XLS.CellItemType.Col;

    workSheet2.Options.AggregateFormat.Font.Name = "Arial";

    workSheet2.Options.CustomDataFormat.Font.Name = "Arial";

    workSheet2.Options.DefaultFont.Name = "Arial";

    workSheet2.Options.FooterFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue;

    workSheet2.Options.FooterFormat.Font.Name = "Arial";

    workSheet2.Options.HeaderFormat.Font.Bold = true;

    workSheet2.Options.HeaderFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue;

    workSheet2.Options.HeaderFormat.Font.Name = "Arial";

    workSheet2.Options.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue;

    workSheet2.Options.HyperlinkFormat.Font.Name = "Arial";

    workSheet2.Options.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.Single;

    workSheet2.Options.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left;

    workSheet2.Options.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top;

    workSheet2.Options.NoteFormat.Font.Bold = true;

    workSheet2.Options.NoteFormat.Font.Name = "Tahoma";

    workSheet2.Options.NoteFormat.Font.Size = 8F;

    workSheet2.Options.TitlesFormat.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;

    workSheet2.Options.TitlesFormat.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;

    workSheet2.Options.TitlesFormat.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;

    workSheet2.Options.TitlesFormat.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;

    workSheet2.Options.TitlesFormat.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightYellow;

    workSheet2.Options.TitlesFormat.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid;

    workSheet2.Options.TitlesFormat.Font.Bold = true;

    workSheet2.Options.TitlesFormat.Font.Name = "Arial";

    workSheet2.SheetName = "country";

    workSheet2.SQLCommand = oleDbCommand2;

    workSheet2.StartDataCol = ((System.Byte)(0));

    cellExport1.Sheets.Add(workSheet1);

    cellExport1.Sheets.Add(workSheet2);

    cellExport1.SQLCommand = oleDbCommand1;

    cellExport1.GetDataParams += new Spire.DataExport.Delegates.DataParamsEventHandler(cellExport1_GetDataParams);

    oleDbConnection1.Open();

    try

    {

        cellExport1.SaveToFile();

    }

    finally

    {

        oleDbConnection1.Close();

    }

}

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

    }

}