C#中dategridview数据导出为excel文件

先从数据库中获取数据，绑定在datagridview中，再从dategridview中导出为excel文件

1、新建窗体，把控件datagridview和按钮设置好，如图

2、设置datagridview绑定数据表的字段

1）点击datagriview右上角，弹出编辑列，添加列

2）点击添加列，新建数据，跟数据库中数据表的列名一样

3、获取数据库中数据表的方法

SqlConnection conn = null;
        /// <summary>
        /// 查询事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnSelect_Click(object sender, EventArgs e)
        {
            conn = DBHelp.GetConnection(); //获取数据库连接，并开启连接
            string sql = "select * from UseTab";   //查询数据表的 sql语句
            DataSet ds = new DataSet();
            SqlDataAdapter sda = new SqlDataAdapter(sql,conn);
            sda.Fill(ds);
            DataTable dt = ds.Tables[0];   //获取数据表的数据
            this.dgvUseName.DataSource = dt; //把dattable绑定datagridview
            DBHelp.CloseConnection(conn);   //关闭连接
            //选择整行显示数据
            this.dgvUseName.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
           //选择是否只读
            this.dgvUseName.ReadOnly = true;
        }

4、导出为excel文件的方法

private void btnExport_Click(object sender, EventArgs e)
        {
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.Filter = "Excel files (*.xls)|*.xls";
            saveFileDialog.FilterIndex = 0;
            saveFileDialog.RestoreDirectory = true;
            saveFileDialog.CreatePrompt = true;
            saveFileDialog.Title = "导出Excel文件到";

DateTime now = DateTime.Now;
            saveFileDialog.FileName = now.Year.ToString().PadLeft(2)
            + now.Month.ToString().PadLeft(2, '0')
            + now.Day.ToString().PadLeft(2, '0') + "-"
            + now.Hour.ToString().PadLeft(2, '0')
            + now.Minute.ToString().PadLeft(2, '0')
            + now.Second.ToString().PadLeft(2, '0');
            saveFileDialog.ShowDialog();

            Stream myStream = saveFileDialog.OpenFile();
            StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding("gb2312"));
            string str = "";
            try
            {
                //写标题
                for (int i = 0; i < this.dgvUseName.ColumnCount; i++)
                {
                    if (i > 0)
                    {
                        str += "\t";
                    }
                    str += this.dgvUseName.Columns[i].HeaderText;
                }
                sw.WriteLine(str);
                 //写内容
                for (int j = 0; j < this.dgvUseName.Rows.Count; j++)
                {
                    string tempStr = "";
                    for (int k = 0; k < this.dgvUseName.Columns.Count; k++)
                    {
                        if (k > 0)
                        {
                            tempStr += "\t";
                        }
                        tempStr += this.dgvUseName.Rows[j].Cells[k].Value.ToString();
                    }
                    sw.WriteLine(tempStr);
                }
                sw.Close();
                myStream.Close();
            }
            catch (Exception ex)
            {
                //MessageBox.Show(ex.ToString());
            }
            finally
            {
                sw.Close();
                myStream.Close();
            }
        }

5、数据库辅助类

class DBHelp
    {
        //连接字符串
        static String str = "server=.;database=TestDB;uid=sa;pwd=sa";
        static SqlConnection conn = null;
        static SqlCommand cmd = null;
        static int result=0;

        /// <summary>
        /// 获取可用连接
        /// </summary>
        /// <returns>数据库连接对象</returns>
        public static SqlConnection GetConnection() {
            conn = new SqlConnection(str);
            conn.Open();
            return conn;
        }
        /// <summary>
        /// 关闭连接对象
        /// </summary>
        /// <param name="conn">连接对象</param>
        public static void CloseConnection(SqlConnection conn) {
            if (conn != null && conn.State == System.Data.ConnectionState.Open) {
                conn.Close();
            }
        }

}

6、最后导出的效果图

保存时，弹出的窗体

打开excel文件查看数据

参考的导出方法来自这个地址：http://www.cnblogs.com/hfzsjz/archive/2013/05/07/3064231.html

巴特西

C#中dategridview数据导出为excel文件

最新文章

热门文章