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.

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.

22 thoughts on “Two Methods to Export Data from DataSet to Excel in C#”

  1. I have my data in data table. But I want to make readonly few columns in excel. Is it possible?
    Please help me to solve this.

    1. I am sorry for the late reply. Actually, I don’t find the method to export specified columns to Excel randomly right now. The moment I found the solution, I will show it in comment.

  2. certainly like your web-site however you
    need to test the spelling on quite a few of your posts. Many of them are
    rife with spelling problems and I find it very troublesome to tell the
    reality nevertheless I’ll surely come again again.

  3. I know this if off topic but I’m looking into starting my own weblog and was wondering what all is required to get setup? I’m assuming having a blog like
    yours would cost a pretty penny? I’m not very web savvy so I’m not 100% certain.
    Any tips or advice would be greatly appreciated. Many
    thanks

  4. We absolutely love your blog and find a lot of your post’s to be exactly what I’m looking for.
    Does one offer guest writers to write content available for you?
    I wouldn’t mind creating a post or elaborating on a few of the subjects you write related to here. Again, awesome web log!

  5. Although there are so mold fogger many ways that an individual can be exposed to wind or other currents of air near any healthy fruit due to
    the likelihood of it becoming infected. There mold fogger are simple ways that are effective in mold
    killing. In building locations with previous floods or leaks, the examination should also include fiber optics inspection to look
    inside your AC, they are not well sealed or kept dry. What can stir up these
    spores.

  6. I think this is one of the most significant information for me.
    And i’m glad reading your article. But want to remark on few general things, The site
    style is ideal, the articles is really great : D. Good job,
    cheers

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