C# 在office2007 gridview导出到excel

浪费了一个多小时来做个工作 现在把过程整理一下 大家以后遇到就不用麻烦了

我过一会把源代码整理一份发到我的共享里边 有用的自己去找吧 不要分的 还是要一分吧

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data.SqlClient;


namespace DataToExcel
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        class ExportExcel
        {
            /// <summary>
            /// 将DataGridView中的数据导出到Excel中，并加载显示出来(无加载模板)
            /// 只用于一般的导出Excel
            /// </summary>
            /// <param name="caption">要显示的页头</param>
            /// <param name="date">打印日期</param>
            /// <param name="dgv">要进行导出的DataGridView</param>
            public void ExportToExcel(string caption, string date, DataGridView dgv)
            {
                //DataGridView可见列数
                int visiblecolumncount = 0;
                for (int i = 0; i < dgv.Columns.Count; i++)
                {
                    if (dgv.Columns[i].Visible == true && (dgv.Columns[i] is DataGridViewTextBoxColumn))
                    {
                        visiblecolumncount++;
                    }
                }

                try
                {
                    //当前操作列的索引
                    int currentcolumnindex = 1;
                    //当前操作行的索引
                    Microsoft.Office.Interop.Excel.ApplicationClass Mylxls = new Excel.ApplicationClass();
                    Mylxls.Application.Workbooks.Add(true);
                    //Mylxls.Cells.Font.Size = 10.5;   //设置默认字体大小
                    //设置标头
                    Mylxls.Caption = caption;
                    //显示表头
                    Mylxls.Cells[1, 1] = caption;
                    //显示时间
                    Mylxls.Cells[2, 1] = date;
                    for (int i = 0; i < dgv.Columns.Count; i++)
                    {
                        if (dgv.Columns[i].Visible == true && (dgv.Columns[i] is DataGridViewTextBoxColumn))   //如果显示
                        {
                            Mylxls.Cells[3, currentcolumnindex] = dgv.Columns[i].HeaderText;
                            Mylxls.get_Range(Mylxls.Cells[3, currentcolumnindex], Mylxls.Cells[3, currentcolumnindex]).Cells.Borders.LineStyle = 1; //设置边框
                            Mylxls.get_Range(Mylxls.Cells[3, currentcolumnindex], Mylxls.Cells[3, currentcolumnindex]).ColumnWidth = dgv.Columns[i].Width / 8;
                            //Mylxls.get_Range(Mylxls.Cells[3, currentcolumnindex], Mylxls.Cells[3, currentcolumnindex]).Font.Bold = true; //粗体
                            //Mylxls.get_Range(Mylxls.Cells[3, currentcolumnindex], Mylxls.Cells[3, currentcolumnindex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //居中显示
                            currentcolumnindex++;
                        }
                    }
                    Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, visiblecolumncount]).MergeCells = true; //合并单元格
                    Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, 1]).RowHeight = 30;   //行高
                    //Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, 1]).Font.Name = "黑体";
                    //Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, 1]).Font.Size = 14;   //字体大小
                    Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, visiblecolumncount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //居中显示
                    Mylxls.get_Range(Mylxls.Cells[2, 1], Mylxls.Cells[2, 2]).MergeCells = true; //合并
                    Mylxls.get_Range(Mylxls.Cells[2, 1], Mylxls.Cells[2, 2]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; //左边显示
                    //Mylxls.get_Range(Mylxls.Cells[1, 2], Mylxls.Cells[1, 2]).ColumnWidth = 12;  //列宽度

                    object[,] dataArray = new object[dgv.Rows.Count, visiblecolumncount];

                    //当前操作列的索引
                    //int currentcolumnindex = 1;
                    //当前操作行的索引
                    for (int i = 0; i < dgv.Rows.Count; i++)   //循环填充数据
                    {
                        currentcolumnindex = 1;
                        for (int j = 0; j < dgv.Columns.Count; j++)
                        {
                            if (dgv.Columns[j].Visible == true && (dgv.Columns[j] is DataGridViewTextBoxColumn))
                            {
                                if (dgv[j, i].Value != null)  //如果单元格内容不为空
                                {
                                    dataArray[i, currentcolumnindex - 1] = dgv[j, i].Value.ToString();
                                }
                                currentcolumnindex++;
                            }
                        }
                    }
                    Mylxls.get_Range(Mylxls.Cells[4, 1], Mylxls.Cells[dgv.Rows.Count + 3, visiblecolumncount]).Value2 = dataArray; //设置边框
                    Mylxls.get_Range(Mylxls.Cells[4, 1], Mylxls.Cells[dgv.Rows.Count + 3, visiblecolumncount]).Cells.Borders.LineStyle = 1; //设置边框
                    Mylxls.Visible = true;

                }
                catch
                {
                    MessageBox.Show("信息导出失败，请确认你的机器上装有Microsoft Office Excel 2003！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                finally
                {

                }
            }
        }




       

        private void Form1_Load(object sender, EventArgs e)
        {
            //select v.sell_user_manager_name as 管理单位,v.sell_user_area_name as 地区,v.sell_user_name as 销售用户名,v.sell_station_name as 场站名,v.sell_user_name_line as 管道用户名,isnull(s.press_avg,0) as 平均压力,isnull(s.sell_day_data,0) as 日实际供气量,isnull(s.sell_month_data,0) as 月累计供气量,isnull(s.sell_year_data,0) as 年累计供气量 ,isnull(s.sell_data_get_type,'手工录入') as 数据采集方式,s.condition as 计量状态,s.remarks as 备注 from sell_user_info_detail_view v left join sell_data s on v.sell_line_id =s.sell_line_id_d and datediff(dd,s.sell_date_time_Start,getdate())=1 order by v.sell_user_manager_name";
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = "server=10.18.2.87,81;database=Natural_Gas_Monitor;user id =xnyqfgs;password=xnyqfgs";
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "select v.sell_user_manager_name as 管理单位,v.sell_user_area_name as 地区,v.sell_user_name as 销售用户名,v.sell_station_name as 场站名,v.sell_user_name_line as 管道用户名,isnull(s.press_avg,0) as 平均压力,isnull(s.sell_day_data,0) as 日实际供气量,isnull(s.sell_month_data,0) as 月累计供气量,isnull(s.sell_year_data,0) as 年累计供气量 ,isnull(s.sell_data_get_type,'手工录入') as 数据采集方式,s.condition as 计量状态,s.remarks as 备注 from sell_user_info_detail_view v left join sell_data s on v.sell_line_id =s.sell_line_id_d and datediff(dd,s.sell_date_time_Start,getdate())=1 order by v.sell_user_manager_name";
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                DataSet ds = new System.Data.DataSet();
                sda.Fill(ds);
                this.dataGridView1.DataSource = ds.Tables[0];

            }
            catch (Exception ex)
            {

                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            
        }

        private void button1_Click(object sender, EventArgs e)
        {
            ExportExcel ee = new ExportExcel();

            ee.ExportToExcel("数据导出",DateTime.Now.ToString("yyyy-MM-dd"),this.dataGridView1);
        }
    }
}

还有一个问题需要补充一下 在office 2007下可能会有

using Excel = Microsoft.Office.Interop.Excel; 使用问题

这个时候需要在vs下更改excel的属性  把可交互性改成false