Export Customized Format Data to Excel without Microsoft Office Installed

This article is from Dotnetspider.com. I viewed it by chance and I thought that it was a helpful article for beginners. So I contacted with its author and get the permission to share this article in my blog. However, the following words I show is not all the same with original article but change something according to my own requirements.

Original Article: http://www.dotnetspider.com/resources/42388-Export-Customized-Format-Data-Excel-without.aspx

Introduction

This article focuses on how to export data from database to Excel with customized formatting by using C#, including font style, cell fill and borders.

Background

Generally speaking, when we export data from database to Excel, there is no formatting for any cells. While, this article introduces a simple method to export data with customized formatting. This method is based on a component, Spire.DataExport. Therefore, we need to add its dll file as reference before coding.

Steps

1.  Connect to database and get the needed datatable. Select necessary information columns and export to Excel. By default, the data will be put in the first sheet in Excel.

2. Set title format, including font style, cell fill and borders.

3. Set format for others cells. In this step, I set two styles. The two styles have different background colors. After formatting, autofit columns. 

4. Set sheet name and apply styles into worksheet. Then save and launch file.

Code in Details

Step 1, Connect Database and Get Data

            OleDbConnection oleDbConnection = new System.Data.OleDb.OleDbConnection();

            oleDbConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=VIP.mdb";

            OleDbCommand oleDbCommand = new System.Data.OleDb.OleDbCommand();

            oleDbCommand.CommandText = "select Name,Gender,Birthday,Email,Number,Country from VIP";

            oleDbCommand.Connection = oleDbConnection;

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

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

Step 2, Customize Data Formatting 

Firstly, set title format, including font name, size, color, background color and borders. The other format I need to set is data type. Because there are some data about date, so set date time format for data information at the beginning.

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

            cellExport.FileName = "VIP.xls";

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

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

            workSheet1.Options.TitlesFormat.Font.Color = Spire.DataExport.XLS.CellColor.White;

            workSheet1.Options.TitlesFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Center;

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

            workSheet1.Options.TitlesFormat.Font.Size = 13.5F;

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

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

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

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

Secondly, define two styles and set different format for them.

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

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

stripStyle 1

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

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

            stripStyle1.Font.Name = "Calibri";

            stripStyle1.Font.Size = 11.5F;

            stripStyle1.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Center

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

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

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

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

stripStyle 2

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

            stripStyle2.Font.Name = "Calibri";

            stripStyle2.Font.Size = 11.5F;

            stripStyle2.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Center;

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

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

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

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

Thirdly, auto fit columns.

            workSheet1.AutoFitColWidth = true;

Step 3, Name Worksheet and Apply Style

            workSheet1.SheetName = "VIP";

            workSheet1.SQLCommand = oleDbCommand;

            cellExport.Sheets.Add(workSheet1);

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

            workSheet1.ItemStyles.Add(stripStyle1);

            workSheet1.ItemStyles.Add(stripStyle2);

Step 4, Save and Launch

            oleDbConnection.Open();

            cellExport.SaveToFile(@"result.xls");

            oleDbConnection.Close();

            System.Diagnostics.Process.Start(@"result.xls");

Result shown as following:

Conclusion

This method is used to export data to Excel with customized style. When we open the result file, we can find that there are three different styles has been set, title format, stripStyle 1 and stripStyle 2. 

For downloading component used in method, please click  here.

Advertisements

Author: janewdaisy

.NET Program Beginner. Share methods about how to use C#/VB.NET to export data, operate Word, Excel, PDF and other useful skills.

1 thought on “Export Customized Format Data to Excel without Microsoft Office Installed”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s