excel导出、导入

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

namespace ExportWindows
{
    public partial class Form1 : Form
    {
        private string ExcleName = ("Excel" + DateTime.Now.Year + (DateTime.Now.Month < 10 ? "0" + DateTime.Now.Month : DateTime.Now.Month.ToString())
                            + (DateTime.Now.Day < 10 ? "0" + DateTime.Now.Day : DateTime.Now.Day.ToString())) + DateTime.Now.Hour + DateTime.Now.Minute + DateTime.Now.Second
                            + DateTime.Now.Millisecond.ToString().Replace("/", "").Replace(":", "").Replace(" ", "").Trim();
        public Form1()
        {
            InitializeComponent();

        }

        /// <summary>
        /// 读取数据库数据
        /// </summary>
        /// <returns></returns>
        private DataView BindUser()
        {
            String connStr = "Data Source =.;DataBase = AdventureWorks;uid = sa;pwd = sa";
            string sql = "select ContactID,FirstName,LastName,EmailAddress from person where ContactId <= 1";
            SqlConnection conn;
            DataSet ds = null;
            SqlDataAdapter adapter;
            conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                adapter = new SqlDataAdapter(sql, conn);
                ds = new DataSet();
                adapter.Fill(ds);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return ds == null ? null : (ds.Tables.Count == 0 ? null : ds.Tables[0].DefaultView);
        }

        /// <summary>
        /// (DataGridView)导出EXCEL
        /// </summary>
        /// <returns></returns>
        private bool ExportFile()
        {
            try
            {
                if (this.dataGridView1.Rows.Count < 1)
                    MessageBox.Show("没有数据可以导出！");
                else
                {
                    //建立Excel对象
                    Excel.Application excel = new Excel.Application();
                    if (excel == null)
                    {
                        MessageBox.Show("创建Excel失败！");
                    }
                    else
                    {
                        excel.Application.Workbooks.Add(true).SaveAs(ExcleName);
                        excel.Visible = true;
                        //生成字段
                        for (int i = 1; i <= this.dataGridView1.ColumnCount; i++)
                        {
                            excel.Cells[1, i] = this.dataGridView1.Columns[i - 1].HeaderText;
                        }
                        //填充数据
                        for (int i = 0; i < this.dataGridView1.RowCount; i++)
                        {
                            for (int j = 0; j < this.dataGridView1.ColumnCount; j++)
                            {
                                if (this.dataGridView1[j, i].ValueType == typeof(string))
                                    excel.Cells[i + 2, j + 1] = "" + this.dataGridView1[j, i].Value.ToString();
                                else
                                    excel.Cells[i + 2, j + 1] = this.dataGridView1[j, i].Value.ToString();
                            }
                        }
                    }
                }

            }
            catch (Exception ee)
            {
                MessageBox.Show(ee.Message);
            }

            return false;

        }

        /// <summary>
        /// 获取Excel数据
        /// </summary>
        /// <param name="pathName">Excel文件路径</param>
        /// <param name="sheetName">选择Sheet名称</param>
        /// <returns></returns>
        private System.Data.DataTable ExcelToDataTable(string pathName, string sheetName)
        {
            System.Data.DataTable tbContainer = new System.Data.DataTable();
            string strConn = string.Empty;
            if (string.IsNullOrEmpty(sheetName))
                sheetName = "Sheet1";
            FileInfo file = new FileInfo(pathName);
            if (!file.Exists)
                throw new Exception("文件不存在！");
            string extension = file.Extension;
            switch (extension)
            {
                case ".xls":
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName
                        + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                    break;
                case ".xlsx":
                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName
                        + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
                    break;
                default:
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName
                        + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                    break;
            }
            OleDbConnection connxls = null;
            try
            {

                //链接Excel
                connxls = new OleDbConnection(strConn);
                connxls.Open();
                //读取Excel里面有表Sheet1
                OleDbDataAdapter oda = new OleDbDataAdapter(string.Format("select * from[{0}$]", sheetName), connxls);
                DataSet ds = new DataSet();
                oda.Fill(tbContainer);
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message);
            }
            finally
            {
                if (connxls != null)
                    if (connxls.State == ConnectionState.Open)
                    {
                        connxls.Close();
                        connxls.Dispose();
                    }
            }
            return tbContainer;

        }

        /// <summary>
        /// 获取Excel中所有Sheet名称
        /// </summary>
        /// <param name="excelFile"></param>
        /// <returns></returns>
        private string[] GetExcelSheetNames(string excelFile)
        {
            OleDbConnection odConn = null;
            System.Data.DataTable dt = null;
            try
            {
                string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFile
                    + ";Extended Properties ='Excel 12.0;HDR=Yes;IMEX=1;'";
                odConn = new OleDbConnection(strConn);
                odConn.Open();
                dt = odConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (dt == null)
                    return null;
                string[] excelSheets = new string[dt.Rows.Count];
                int i = 0;
                foreach (DataRow row in dt.Rows)
                    excelSheets[i++] = row[2].ToString();
                return excelSheets;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                if (odConn.State == ConnectionState.Open)
                {
                    odConn.Close();
                    odConn.Dispose();
                }
            }
            return null;
        }

        /// <summary>
        /// 数据库查询
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button1_Click(object sender, EventArgs e)
        {
            this.dataGridView1.DataSource = this.BindUser();
            this.dataGridView1.AllowUserToAddRows = false;
        }

        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button2_Click(object sender, EventArgs e)
        {
            this.ExportFile();
        }

        /// <summary>
        /// 导入
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button3_Click(object sender, EventArgs e)
        {
            var win = new OpenFileDialog();
            win.ShowDialog();
            if (win.FileName != null)
            {
                this.dataGridView1.AllowUserToAddRows = false;
                this.dataGridView1.DataSource = this.ExcelToDataTable(win.FileName, null);
            }

        }

        private void button4_Click(object sender, EventArgs e)
        {
            var win = new OpenFileDialog();
            win.ShowDialog();
            var sheets = this.GetExcelSheetNames(win.FileName);
            if (sheets != null)
                for (int i = 0; i < sheets.Length; i++)
                    this.listBox1.Items.Add(sheets[i].ToString());
        }
    }
}