将Excel中的数据导入到表中

以下是一个通用的类：

 

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Reflection;
using Excel;  //次命名空间需要在COM组件中添加一个Microsoft Office 12.0(11.0)Object Library的引用

namespace ImportExportToExcel
{
    public class ImportExportToExcel
    {
        private string strConn ;
       
        private System.Windows.Forms.OpenFileDialog openFileDlg=new System.Windows.Forms.OpenFileDialog();
        private System.Windows.Forms.SaveFileDialog saveFileDlg=new System.Windows.Forms.SaveFileDialog();     
       
        public ImportExportToExcel()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
            this.openFileDlg.DefaultExt = "xls";
            this.openFileDlg.Filter = "Excel文件 (*.xls)|*.xls";

            this.saveFileDlg.DefaultExt="xls";
            this.saveFileDlg.Filter= "Excel文件 (*.xls)|*.xls";

        }

       
        #region 从Excel文件导入到DataSet
        //        /// <summary>
        //        /// 从Excel导入文件
        //        /// </summary>
        //        /// <param name="strExcelFileName">Excel文件名</param>
        //        /// <returns>返回DataSet</returns>
        //        public DataSet ImportFromExcel(string strExcelFileName)
        //        {
        //            return doImport(strExcelFileName);
        //        }
        /** <summary>
        /// 从选择的Excel文件导入
        /// </summary>
        /// <returns>DataSet</returns>
        public DataSet ImportFromExcel()
        {
            DataSet ds=new DataSet();
            if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                ds=doImport(openFileDlg.FileName);
            return ds;
        }
        /** <summary>
        /// 从指定的Excel文件导入
        /// </summary>
        /// <param name="strFileName">Excel文件名</param>
        /// <returns></returns>
        public DataSet ImportFromExcel(string strFileName)
        {
            DataSet ds=new DataSet();
            ds=doImport(strFileName);
            return ds;
        }
        /** <summary>
        /// 执行导入
        /// </summary>
        /// <param name="strFileName">文件名</param>
        /// <returns>DataSet</returns>
        private DataSet doImport(string strFileName)
        {
            if (strFileName=="") return null;
             
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                "Data Source=" + strFileName + ";" +
                "Extended Properties=Excel 8.0;";
            OleDbDataAdapter ExcelDA = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);

            DataSet ExcelDs = new DataSet();
            try
            {
                ExcelDA.Fill(ExcelDs, "ExcelInfo");
               
            }
            catch(Exception err)
            {
                System.Console.WriteLine( err.ToString() );
            }
            return ExcelDs;
           
           
       
        }
        #endregion

        #region 从DataSet到出到Excel
        /** <summary>
        /// 导出指定的Excel文件
        /// </summary>
        /// <param name="ds">要导出的DataSet</param>
        /// <param name="strExcelFileName">要导出的Excel文件名</param>
        public void ExportToExcel(DataSet ds,string strExcelFileName)
        {
            if (ds.Tables.Count==0 || strExcelFileName=="") return;
            doExport(ds,strExcelFileName);
  

        }
        /** <summary>
        /// 导出用户选择的Excel文件
        /// </summary>
        /// <param name="ds">DataSet</param>
        public void ExportToExcel(DataSet ds)
        {
            if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                doExport(ds,saveFileDlg.FileName);
           
        }
        /** <summary>
        /// 执行导出
        /// </summary>
        /// <param name="ds">要导出的DataSet</param>
        /// <param name="strExcelFileName">要导出的文件名</param>
        private void doExport(DataSet ds,string strExcelFileName)
        {
           
            Excel.Application excel= new Excel.Application();
           
            //            Excel.Workbook obj=new Excel.WorkbookClass();
            //            obj.SaveAs("c:/zn.xls",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null);

            int rowIndex=1;
            int colIndex=0;

            excel.Application.Workbooks.Add(true);
           
   
            System.Data.DataTable table=ds.Tables[0] ;
            foreach(DataColumn col in table.Columns)
            {
                colIndex++;   
                excel.Cells[1,colIndex]=col.ColumnName;               
            }

            foreach(DataRow row in table.Rows)
            {
                rowIndex++;
                colIndex=0;
                foreach(DataColumn col in table.Columns)
                {
                    colIndex++;
                    excel.Cells[rowIndex,colIndex]=row[col.ColumnName].ToString();
                }
            }
            excel.Visible=false;
           
            excel.ActiveWorkbook.SaveAs(strExcelFileName + ".XLS",
                Excel.XlFileFormat.xlExcel9795, null, null, false,
                false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null,null);
           
           
            //wkbNew.SaveAs strBookName

            //excel.Save(strExcelFileName);
            excel.Quit();
            excel=null;
           
            GC.Collect();//垃圾回收
        }
        #endregion

        #region 从XML导入到Dataset

        /** <summary>
        /// 从选择的XML文件导入
        /// </summary>
        /// <returns>DataSet</returns>
        public DataSet ImportFromXML()
        {
            DataSet ds=new DataSet();
            System.Windows.Forms.OpenFileDialog openFileDlg=new System.Windows.Forms.OpenFileDialog();
            openFileDlg.DefaultExt="xml";
            openFileDlg.Filter= "xml文件 (*.xml)|*.xml";
            if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                try{ds.ReadXml(openFileDlg.FileName,System.Data.XmlReadMode.ReadSchema);}
                catch{}
            return ds;
        }
        /** <summary>
        /// 从指定的XML文件导入
        /// </summary>
        /// <param name="strFileName">XML文件名</param>
        /// <returns></returns>
        public DataSet ImportFromXML(string strFileName)
        {
            if (strFileName=="")
                return null;
            DataSet ds=new DataSet();
            try{ds.ReadXml(strFileName,System.Data.XmlReadMode.ReadSchema);}
            catch{}
            return ds;
        }
       
        #endregion

        #region 从DataSet导出到XML
        /** <summary>
        /// 导出指定的XML文件
        /// </summary>
        /// <param name="ds">要导出的DataSet</param>
        /// <param name="strXMLFileName">要导出的XML文件名</param>
        public void ExportToXML(DataSet ds,string strXMLFileName)
        {
            if (ds.Tables.Count==0 || strXMLFileName=="") return;
            doExportXML(ds,strXMLFileName);
        }
        /** <summary>
        /// 导出用户选择的XML文件
        /// </summary>
        /// <param name="ds">DataSet</param>
        public void ExportToXML(DataSet ds)
        {
            System.Windows.Forms.SaveFileDialog saveFileDlg=new System.Windows.Forms.SaveFileDialog();
            saveFileDlg.DefaultExt="xml";
            saveFileDlg.Filter= "xml文件 (*.xml)|*.xml";
            if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                doExportXML(ds,saveFileDlg.FileName);
        }

        /** <summary>
        /// 执行导出
        /// </summary>
        /// <param name="ds">要导出的DataSet</param>
        /// <param name="strExcelFileName">要导出的XML文件名</param>
        private void doExportXML(DataSet ds,string strXMLFileName)
        {
            try
            {ds.WriteXml(strXMLFileName,System.Data.XmlWriteMode.WriteSchema );}
            catch(Exception ex)
            {System.Windows.Forms.MessageBox.Show(ex.Message,"Errol") ;}   
        }

        #endregion
    }

}

 

 

以下是我才在form页面调用的一个例子

 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Security.Cryptography;

namespace Manage
{
    public partial class ExcelStudent : Form
    {
        public ExcelStudent()
        {
            InitializeComponent(); 
        }
        //Md5加密方法
        static string UserMd5(string str)
        {
            string cl = str;
            string pwd = "";
            string temp = "";
            MD5CryptoServiceProvider md5 = new MD5CryptoServiceProvider();
            // 加密后是一个字节类型的数组，这里要注意编码UTF8/Unicode等的选择　
            byte[] s = md5.ComputeHash(Encoding.UTF8.GetBytes(cl));
            // 通过使用循环，将字节类型的数组转换为字符串，此字符串是常规字符格式化所得
            for (int i = 0; i < s.Length; i++)
            {
                // 将得到的字符串使用十六进制类型格式。格式后的字符是小写的字母，如果使用大写（X）则格式后的字符是大写字符
                temp = s[i].ToString("X");
                if (temp.Length < 2)
                    temp = "0" + temp;
                pwd = pwd + temp;
            }
            return pwd;
        }
        public static string scode;     //用于获取缓存中学生表的code;
        public static string classcode1;//用于获取缓存中学生表的classcode
        public static string classcode2;//用于获取数据库中学生表的classcode
        private void button1_Click(object sender, EventArgs e)
        {
           this.textBox1.Text = "";
           try
           {
               ImportExportToExcel.ImportExportToExcel excel = new ImportExportToExcel.ImportExportToExcel();

               DataTable dt = excel.ImportFromExcel().Tables[0];

               DataTable dt2 = BLL.TeacherBLL.QuerryStudent();
               for (int i = 0; i < dt.Rows.Count; i++)
               {
                   scode = dt.Rows[i]["Code"].ToString();
                   classcode1 = dt.Rows[i]["ClassCode"].ToString();
                   //判断Excel表中的Code主键是否为空
                   if (dt.Rows[i]["Code"].ToString() == "")
                   {
                       this.groupBox1.Visible = true;
                       this.textBox1.Text += "所选Excel不合法：第" + Convert.ToString((i + 2)) + "行为空！" + "/r/n";
                       continue;
                   }
                   //判断缓存中Excel表中的code是否有重复
                   if (dt.Select("Code='" + dt.Rows[i]["Code"] + "'").Length > 1)
                   {
                       this.groupBox1.Visible = true;
                       this.textBox1.Text += "所选Excel不合法：第" + Convert.ToString((i + 2)) + "行有重复！" + "/r/n";
                   }
                   //判断Excel表中的classcode 在数据库中是否存在，若不存在可以选择添加，取消
                   if (BLL.TeacherBLL.Querryclassname(classcode1).Rows.Count <= 0)
                   {
                       this.groupBox1.Visible = true;
                       textBox1.Text += "对不起，Excel表中的第" + Convert.ToString((i + 1)) + "行班级编号" + dt.Rows[i]["ClassCode"].ToString() + "不存在！" + "/r/n";
                       DialogResult dr = MessageBox.Show("是否添加一个新的班级？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
                       if (dr == DialogResult.Yes)
                       {
                           AddClass2 ac = new AddClass2();
                           ac.ShowDialog();
                       }
                   }
                   this.groupBox1.Visible = true;
                   //查询缓存中Excel表中的Code,作为条件查询数据库中的code ,是否重复
                   if (BLL.TeacherBLL.Querrystudentid(scode).Rows.Count > 0)
                   {
                       this.textBox2.Text += "数据库中已存在：" + "/r/n" + "第" + Convert.ToString((i + 1)) + "行学生编号！" + "/r/n";
                   }
               }
               if (this.textBox1.Text == "" && this.textBox2.Text == "")
               {
                   this.dataGridView1.DataSource = dt;
               }
           }
           catch
           {
           
           }
        }
        /// <summary>
        /// 将Excel表中的数据保存到数据库中
        /// </summary>

        string code, name, psw, ssclasscode;
        private void button2_Click(object sender, EventArgs e)
        {
            if (this.dataGridView1.Rows.Count == 0)
            {
                //MessageBox.Show("");
            }
            else
            {
                for (int m = 0; m < this.dataGridView1.Rows.Count - 1; m++)
                {
                    if (this.dataGridView1.Rows.Count > 1)
                    {
                        code = this.dataGridView1[0, m].Value.ToString();
                        name = this.dataGridView1[1, m].Value.ToString();
                        psw = UserMd5(this.dataGridView1[2, m].Value.ToString());
                        ssclasscode = this.dataGridView1[3, m].Value.ToString();
                        if (BLL.TeacherBLL.AddStudent(code, name, psw, ssclasscode))
                        {
                        }
                        else
                        { MessageBox.Show("No!"); }
                    }
                } MessageBox.Show("ok!");
            }
        }

        private void Form3_Load(object sender, EventArgs e)
        {
            this.groupBox1.Visible = false;
        }
        //关闭
        private void button3_Click(object sender, EventArgs e)
        {
            this.Close();
            Application.ExitThread();
        }
    }
}