Import Data to Excel from Database through DataGridView by Using C#

In order to display data or share data information with others, we often need to import data to Excel. For example, sales people often need to import customers’ information to Excel to be more convenient for contacting with them. In this post, I will show how to import data to Excel from database through DataGridView by using C#.

In my example, I need to get customers information from my database and import it to Excel. But I don’t get data to Excel from database directly but save data in DataGridView firstly and then import to Excel. 

Because I use a C# .NET Excel component, Spire.XLS for .NET in my method, so I add its dll file as reference before starting with the following steps.

Steps:

  1. Create a form and design it. In this form, I add a label to tell what I want to do. Then, add a DataGridView to load data from database. Finally, add a run button.
  2. Double click Run button and write code.
  3. Create new workbook and initialize worksheet.
  4. Select necessary data information and load it from DataBase to DataGridView.
  5. Save data to DataTable and insert it to Excel worksheet.
  6. Set format for Cells.
  7. Save and Launch File.

ImportData Form

        private void button1_Click(object sender, EventArgs e)
        {
            Workbook workbook = new Workbook();

            //Initialize worksheet
            workbook.CreateEmptySheets(1);
            Worksheet sheet = workbook.Worksheets[0];

            //Insert DataTable to Excel
            sheet.InsertDataTable((DataTable)this.dataGridView1.DataSource, true, 2, 1, -1, -1);

            //Set Excel Style
            CellStyle Style = workbook.Styles.Add(“Style”);
            Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
            Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
            Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
            Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
            Style.Borders.Color = Color.DarkCyan;
            Style.Color = Color.Lavender;
            Style.Font.FontName = “Calibri”;
            Style.Font.Size = 12;
            CellRange range = sheet.Range[“A3:F26”];
            range.CellStyleName = Style.Name;

            //Set Header Style
            CellStyle styleHeader = sheet.Rows[0].Style;
            styleHeader.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
            styleHeader.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
            styleHeader.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
            styleHeader.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
            styleHeader.Borders.Color = Color.DarkCyan;
            styleHeader.VerticalAlignment = VerticalAlignType.Center;
            styleHeader.HorizontalAlignment = HorizontalAlignType.Center;
            styleHeader.KnownColor = ExcelColors.Cyan;
            styleHeader.Font.FontName = “Calibri”;
            styleHeader.Font.Size = 14;
            styleHeader.Font.IsBold = true;

            //Set Row Height and Column Width
            sheet.AllocatedRange.AutoFitColumns();
            sheet.AllocatedRange.AutoFitRows();
            sheet.Range[“A3:F26”].RowHeight = 16;
            sheet.Rows[0].RowHeight = 20;

            //Save and Launch File
            workbook.SaveToFile(“DataImport.xlsx”, ExcelVersion.Version2010);
            System.Diagnostics.Process.Start(workbook.FileName);
        }

        private void Form1_Load_1(object sender, EventArgs e)
        {
            //Load Data from Database to DataGridView
            string connString = @”Provider=Microsoft.ACE.OLEDB.12.0;
                                Data Source=D:\work\VIP.mdb;Persist Security Info=False;”;
            DataTable dataTable = new DataTable();
            using (OleDbConnection conn = new OleDbConnection(connString))
            {
                conn.Open();
                string sql = “select Name,Gender,Birthday,Email,Number,Country from VIP”;
                OleDbDataAdapter dataAdapter = new OleDbDataAdapter(sql, conn);
                dataAdapter.Fill(dataTable);
            }
            this.dataGridView1.DataSource = dataTable;
        }

Result Shown as Following:

Freely Download Spire.XLS for .NET

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.

2 thoughts on “Import Data to Excel from Database through DataGridView by Using C#”

  1. Hello Dear, are you truly visiting this web page daily, if so
    afterward you will without doubt get pleasant knowledge.

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