Two Methods to Export Data from DataSet to Excel in C#

Dataset is often used to save data by users because it is available even though we close database. And users would like to export data in dataset to Excel for displaying because Excel is very convenient for formatting and calculating data. This article introduces two solutions to export in C#.

Solution with Microsoft Office Installed:

Before using this solution, please make sure that MS Office has been installed on your system and add Microsoft Object library as reference in project.

        ///<summary>
        ///</summary>
        ///<param name=”DataSet”>Export DataSet</param>
        ///<param name=”isShowExcle”>Show Excel or not</param>
        ///<returns></returns>

        public bool DataSetToExcel(DataSet dataSet, bool isShowExcle, string fileName)
        {

            DataTable dataTable = dataSet.Tables[0];
            int rowNo = dataTable.Rows.Count;
            int columnNo = dataTable.Columns.Count;
            int colIndex = 0;
            if (rowNo == 0)
            {
                return false;
            }

            //Create Excel Object
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();

            //excel.Application.Workbooks.Add(true);
            Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
            worksheet.Name = DateTime.Now.ToString().Replace(“:”, “”).Replace(“-“, “”).Replace(” “, “”);
            excel.Visible = isShowExcle;

            //Microsoft.Office.Interop.Excel.Worksheet worksheet =(Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets[1];
            Microsoft.Office.Interop.Excel.Range range;

            //Generate Fields Name
            foreach (DataColumn col in dataTable.Columns)
            {
                colIndex++;
                excel.Cells[1, colIndex] = col.ColumnName;
            }
            object[,] objData = new object[rowNumber, columnNumber];
            for (int row = 0; row < rowNo; r++)
            {
                for (int col = 0; col < columnNo; c++)
                {
                    objData[row, col] = dataTable.Rows[r][c];
                }
                //Application.DoEvents();
            }
            range = worksheet.Range[excel.Cells[4, 3], excel.Cells[rowNo + 1, columnNo]];

            //Set Cell Format as Text
            range.NumberFormat = “@”;
            range.Value2 = objData;
            worksheet.Range[excel.Cells[4, 3], excel.Cells[rowNo + 1, 1]].NumberFormat = “yyyy-m-d h:mm”;
            workbook.SaveAs(example, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
            System.Reflection.Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
            System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
            workbook.Close();

            excel.Quit();
            GC.Collect();
            KillExcel();
            return true;
        }

Solution with Data Export Component

This solution is based on a Free C# .NET Data Export component, Free Spire.DataExport. So, please download and install this component and then add its dll as reference in project. Now, follow the steps to realize exporting and formatting the exported data.

Firstly, Create a Windows Form project. Design the form and add essential elements in forms as following.

Secondly, double click Run button and write the following code.

            string connStr = txtConnstr.Text.Trim();
            string cmd = txtCmd.Text.Trim();

            //Fill DataSet
            using (OleDbConnection conn = new OleDbConnection(connStr))
            {
                conn.Open();
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(cmd, conn))
                {
                    using (OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(adapter))

                    {
                        using (DataSet ds = new DataSet())
                        {
                            ds.Locale = System.Globalization.CultureInfo.InvariantCulture;
                            adapter.Fill(ds);
                            SaveFileDialog saveFileDLG = new SaveFileDialog();
                            saveFileDLG.Filter = “(*.xls)|*.xls”;
                            string currentDirectory = Directory.GetCurrentDirectory();
                            CellExport cellExport = new CellExport();
                            saveFileDLG.ShowDialog();
                            this.txtExportFile.Text = saveFileDLG.FileName;
                            if (string.IsNullOrEmpty(txtExportFile.Text))
                            {
                                MessageBox.Show(“Please input a directory!”);
                                return;
                            }
                            OleDbCommand oleDbCommand1 = new OleDbCommand(txtCmd.Text, conn);
                            WorkSheet workSheet1 = new WorkSheet();

                            StripStyle stripStyle1 = new StripStyle();
                            stripStyle1.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Thin;
                            stripStyle1.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Thin;
                            stripStyle1.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Thin;
                            stripStyle1.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Thin;
                            stripStyle1.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightGreen;

                            //Export Style
                            cellExport.ActionAfterExport = ActionType.OpenView;
                            cellExport.AutoFitColWidth = true;
                            cellExport.FileName = this.txtExportFile.Text.Trim();

                            cellExport.SheetOptions.TitlesFormat.Font.Name = “Times New Roman”;

                            workSheet1.AutoFitColWidth = true;
                            workSheet1.Options.TitlesFormat.Font.Color = Spire.DataExport.XLS.CellColor.Pink;
                            workSheet1.Options.TitlesFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Center;
                            workSheet1.Options.TitlesFormat.Font.Italic = true;
                            workSheet1.Options.TitlesFormat.Font.Bold = true;
                            workSheet1.Options.TitlesFormat.Font.Size = 12F;

                            //Export Data           
                            workSheet1.SheetName = “Demo”;
                            workSheet1.SQLCommand = oleDbCommand1;
                            cellExport.Sheets.Add(workSheet1);
                            workSheet1.ItemType = Spire.DataExport.XLS.CellItemType.Row;
                            workSheet1.ItemStyles.Add(stripStyle1);
                            workSheet1.ItemStyles.Add(stripStyle2);
                            cellExport.SaveToFile(this.txtExportFile.Text.Trim());
                        }
                    }
                }
            }

Conclusion

The two solutions can export data from dataset to Excel. The first one is based on Microsoft Office, and the second one is based a component. If you don’t want to install MS Office on your computer, the second will be a good choice to export data.

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.

Fast Export Data from Database to Excel and Generate Chart without Automation

Before I got this blog, I submitted this post to Dotnetspark.com and Programmers.com. Now, I share it in my new blog and hope that this post can be helpful for people who need to export data to Excel.

Introduction

This article introduces a Wizard which is used to export data from database and shows the guide to use it. This Wizard can used both by programmers and general users.

Background

In this example, I export the data about customers' information of 2008 from database to Excel and then generate chart based on the parts of data without Automation quickly. Before using the following method to export data, we need to install .Net Framework 2.0 and free Spire.DataExport firstly.

The following tables are the parts of customers' data.

Simple Steps

1. Select a database.

2. Select data source from database. In the example, I choose Table.

3. Select essential columns to export.

4. Select the Chart type. There are three types: Column Chart, Bar Chart or Line Chart, can be chosen.

Step in Details

Step 1: select Database.

Choose the database which saves the customers' information. Then input the connection string to connect to the database.

Step 2: Get the data source.

Select Data Source firstly. I choose Table. Then, select the table named customers.

Step 3: Select the columns I need to export. In this example, I just only need two columns, Name and Shopping Quantity.

Step 4: Select the Chart Type. In order to display the data clearly, I choose BarChart.

After clicking Finish, we can get the Excel Worksheet as following:

Conclusion

This method can be used to export data from database into Excel and generate charts efficiently. In this method, we use one small tool, named DataExportWizard to export data simply, which originates from the free data export component. You can click here to download the free component.

If you want to download this Wizard, you can visit this link: http://www.dotnetspark.com/kb/3716-fast-export-data-from-database-to-excel.aspx

The other link source: http://www.programmersheaven.com/user/Rinia09/blog/6766-Fast-Export-Data-from-Database-to-Excel-and-Generate-Chart-without-Automation/

Lijiang, City in My Dream

There is one city, not big but peaceful. There is one city, not luxuriant but beautiful. There is one city, not fashion but innocent. The people in the city is not talented but friendly. The food in the city is not abundant but delicious. Several years later, if I can give up everything I have owned. I will live in the city to enjoy its gentle warmth.

The city is called Lijiang. It locates in Yunnan Province, China.