C#中Excel的导入导出

1.界面

重点：如果计算机没装Excel，则必须安装AccessDatabaseEngine_32.exe或者AccessDatabaseEngine_64.exe引擎，这样电脑就安装Excel环境，导入导出就可以执行而不报错了。（我的资源有这两个文件）

2.导入导出button按钮

 //button导出
        private void btnSerch_Click(object sender, EventArgs e)
        {
            //Excalexport Excal = new Excalexport();
            //Excal.ShowDialog();

        saveFileDialog1.FileName = txtExcel.Text+DateTime.Today.ToString("yyyyMMdd") + ".xlsx";
        saveFileDialog1.ShowDialog();
        grv.ExportToXlsx(saveFileDialog1.FileName);

        //if (string.IsNullOrEmpty(this.btnSerch.Text))
        //{
        //    MessageBox.Show("请选择保存的路径！");
        //    return;
        //}
        //this.grv.ExportToXls(this.btnSerch.Text + "\\体检结果.xls");
        //MessageBox.Show("导出成功！ " + this.btnSerch.Text + "\\体检结果.xls");        
    }

    //button导入
    private void simpleButton1_Click(object sender, EventArgs e)
    {

        //grdSalemoduleListOther.DataSource = null;
        //grvSalemoduleListOther.Columns.Clear();
        //grdSalemoduleListOther.MainView.PopulateColumns();

        //OpenFileDialog openFile = new OpenFileDialog();
        //if (openFile.ShowDialog() == DialogResult.OK)
        //{
        //    DataTable dtdata = Utils.ExcalImport.GetExcelTable(openFile.FileName);
        //    grdSalemoduleListOther.DataSource = dtdata;
        //}


        //对原有数据清空
        grd.DataSource = null;
        grv.Columns.Clear();
        grd.MainView.PopulateColumns();

        OpenFileDialog openFile = new OpenFileDialog();
        if (openFile.ShowDialog() == DialogResult.OK)
        {
            DataTable dtdata = Utils.ExcalImport.GetExcelTable(openFile.FileName);
            grd.DataSource = dtdata;
        }
        
     
    }

3.Excel类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO;

namespace Utils
{
    public class ExcalImport
    {

    //根据excle的路径把第一个sheel中的内容放入datatable
    public static DataTable ReadExcelToTable(string path)//excel存放的路径
    {
        try
        {
            //连接字符串
            //string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';"; // Office 07及以上版本 不能出现多余的空格 而且分号注意
            string connstring = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';"; //Office 07以下版本 
            using (OleDbConnection conn = new OleDbConnection(connstring))
            {
                conn.Open();
                DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); //得到所有sheet的名字
                string firstSheetName = sheetsName.Rows[0][2].ToString(); //得到第一个sheet的名字
                string sql = string.Format("SELECT * FROM [{0}]", firstSheetName); //查询字符串
                //string sql = string.Format("SELECT * FROM [{0}] WHERE [日期] is not null", firstSheetName); //查询字符串

                OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring);
                DataSet set = new DataSet();
                ada.Fill(set);

                DataTable dt = new DataTable();
                if (set.Tables[0] != null && set.Tables[0].Rows.Count > 0)
                {
                    for (int i = 0; i < set.Tables[0].Columns.Count; i++)
                    {
                        dt.Columns.Add(set.Tables[0].Rows[0][i].ToString());
                    }

                    for (int i = 1; i < set.Tables[0].Rows.Count; i++)
                    {
                        DataRow dr = set.Tables[0].Rows[i];
                        DataRow drNew = dt.NewRow();
                        drNew.ItemArray = dr.ItemArray;
                        dt.Rows.Add(drNew);
                    }
                }

                return dt;
            }
        }
        catch (Exception)
        {
            return null;
        }

    }
    public const string OledbConnString = "Provider = Microsoft.ACE.OLEDB.12.0 ; Data Source = {0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'"; //Excel

    /// <summary>  
    /// 获取Excel文件中的信息，保存到一个DataTable中  
    /// </summary>  
    /// <param name="path">文件路径</param>  
    /// <returns>返回生成的DataTable</returns>  
    public static DataTable GetExcelTable(string path)
    {
        try
        {
            //获取excel数据  
            DataTable dt1 = new DataTable("excelTable");
            string strConn = string.Format(OledbConnString, path);
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            DataTable dt = conn.GetSchema("Tables");
            //判断excel的sheet页数量，查询第1页  
            if (dt.Rows.Count > 0)
            {
                string selSqlStr = string.Format("select * from [{0}]", dt.Rows[0]["TABLE_NAME"]);
                OleDbDataAdapter oleDa = new OleDbDataAdapter(selSqlStr, conn);
                oleDa.Fill(dt1);
            }
            conn.Close();
            return dt1;
        }
        catch (Exception ex)
        {
            MessageBox.Show("Excel转换DataTable出错：" + ex.Message);
            return null;
        }
    }






    /// <summary>
    /// 将Excel导入DataSet
    /// </summary>
    /// <param name="path">文件路径</param>
    /// <param name="sheetName">页名</param>
    /// <returns>如果返回null表示错误</returns>
    public static DataSet ExceltToDS(string path, string sheetName, string OfficeVersion)
    {
        string strConn = "";
        string strExcel = "";

        OleDbConnection conn = null;
        OleDbDataAdapter adapter = null;
        DataSet ds = null;

        //string strOfficeVersion = LocalConfig.IniReadValue("OfficeVersions", OfficeVersion);//配置Excel版本
        strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=Excel 8.0;";
        if ((OfficeVersion.Equals("2007")) || (Path.GetExtension(path).ToLower() == ".xlsx"))
        {
            strConn = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + path + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
        }
        strExcel = "Select * from [" + sheetName + "$]";

        try
        {
            conn = new OleDbConnection(strConn);
            conn.Open();

            adapter = new OleDbDataAdapter(strExcel, strConn);
            ds = new DataSet();
            adapter.Fill(ds, "myTable");
            return ds;
        }
        finally
        {
            conn.Close();
        }
    }



    /// <summary>
    /// 读取excle文件中的表单名称，在返回的table中字段名称为"TABLE_NAME"
    /// </summary>
    /// <param name="file">Excal文件路径</param>
    /// <param name="OfficeVersion">Exca版本号</param>
    /// <returns></returns>
    public static DataTable getExcelSheetNames(string file, string OfficeVersion)
    {
        OleDbConnection conn = null;

        string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties=Excel 8.0;";//数据库连接字符串
        if ((OfficeVersion.Equals("2007以上")) || (Path.GetExtension(file).ToLower() == ".xlsx"))
        {
            strConn = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + file + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
        }

        try
        {
            conn = new OleDbConnection(strConn);
            conn.Open();
            DataTable table = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            return table;
        }
        finally
        {
            conn.Close();
        }
    }

}
}