学步园

这几天公司要写C/S模式的项目，要写个Excel与Sql数据导入导出的模块．

需要引用 com里的Microsoft Excel 11.0 Object Library

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.SqlClient;

namespace ww
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            GridBind();

        }
        private void GridBind()
        {
            SqlConnection conn = new SqlConnection("data source=.;integrated security=sspi;initial catalog=news");
            conn.Open();
            SqlDataAdapter da = new SqlDataAdapter("select * from Article", conn);
            DataSet ds = new DataSet();
            da.Fill(ds, "Article");
           dataGridView1.DataSource = ds.Tables["Article"];
            conn.Close();
        }
        public bool ExportDataGridview(DataGridView gridView, bool isShowExcele)
        {
            if(gridView.Rows.Count==0)
            {
                return false;
            }
            //建立Excel对象
            Excel.Application excel = new Excel.Application();
            excel.Application.Workbooks.Add(true);
            excel.Visible = isShowExcele;
            //生成字段名称
            for (int i = 0; i < gridView.ColumnCount; i++)
            {
                excel.Cells[1, i + 1] = gridView.Columns[i].HeaderText;
            }
            //填充数据
            for (int i = 0; i < gridView.RowCount - 1; i++)
            {
                for (int j = 0; j < gridView.ColumnCount; j++)
                {
                    if (gridView[j, i].Value == typeof(string))
                    {
                        excel.Cells[i + 2, j + 1] = "" + gridView[i, j].Value.ToString();
                    }
                    else
                    {
                        excel.Cells[i + 2, j + 1] = gridView[j, i].Value.ToString();
                    }
                }
            }
            return true;
        }

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