using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

using System.Data.Sql;
using System.Data.SqlClient;

namespace WindowsApplication3
{
    public partial class Form1 : Form
    {
        SqlDataAdapter myda;
        DataSet myds;
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {

            String cmdtext="select * from yourtable  ";


            String connstr ="Data Source=192.168.1.3;Initial Catalog=yourdb;User ID=sa;Password=yourpwd";
            
            SqlConnection sqlcon = new SqlConnection();
            sqlcon.ConnectionString = connstr;
            sqlcon.Open();

            myda = new SqlDataAdapter(cmdtext, sqlcon);
            myds = new DataSet();
  
            myda.Fill(myds);
            sqlcon.Close();
            dataGridView1.DataSource = myds.Tables[0];

        }

        private void button1_Click(object sender, EventArgs e)
        {
            ExportDataGridview(dataGridView1, true);
        }


          public bool ExportDataGridview(DataGridView dgv, bool isShowExcle)
        {
            if (dgv.Rows.Count == 0)
                return false;
            //建立Excel对象
            Excel.Application excel = new Excel.Application();
            excel.Application.Workbooks.Add(true);
            excel.Visible = isShowExcle;
            //生成字段名称
            for (int i = 0; i < dgv.ColumnCount; i++)
            {
                excel.Cells[1, i + 1] = dgv.Columns[i].HeaderText;
            }
            //填充数据
            for (int i = 0; i < dgv.RowCount - 1; i++)
            {
                for (int j = 0; j < dgv.ColumnCount; j++)
                {
                    if (dgv[j, i].ValueType == typeof(string))
                    {
                        excel.Cells[i + 2, j + 1] = "'" + dgv[j, i].Value.ToString();
                    }
                    else
                    {
                        excel.Cells[i + 2, j + 1] = dgv[j, i].Value.ToString();
                    }
                }
            }
            return true;
        }


    }
  
}