Winform导入Excel数据到数据库

 public partial class ImportExcel : Form

    {

        AceessHelpers accessHelper = new AceessHelpers();

        public ImportExcel()

        {

            InitializeComponent();

        }

        private void btn_importExcelData(object sender, EventArgs e)

        {

            openFileDialog1.Title = "打开文件";

            openFileDialog1.ShowHelp = true;

            openFileDialog1.RestoreDirectory = true;

            //openFileDialog1.Filter = "Excel文件(*.xlsx)|(*.xls)";

            if (openFileDialog1.ShowDialog() == DialogResult.OK)

            {

                this.txt_path.Text = openFileDialog1.FileName;

            }

        }

        private void btn_upload_Click(object sender, EventArgs e)

        {

            string path = openFileDialog1.FileName;

            System.Data.DataTable dt = GetDataTable(path);

            int result = ;

            for (int i = ; i < dt.Rows.Count; i++)

            {

                string sql = "insert into Products (ProductName,Area,ProductCategory,Customer,Style,StructureStyle,Width,Square,Other) values ('" +

                Convert.ToString(dt.Rows[i]["F1"]) + "','" + Convert.ToString(dt.Rows[i]["F2"]) + "','" + Convert.ToString(dt.Rows[i]["F3"]) + "','" +

                Convert.ToString(dt.Rows[i]["F4"]) + "','" + Convert.ToString(dt.Rows[i]["F5"]) + "','" + Convert.ToString(dt.Rows[i]["F6"]) + "','" +

                Convert.ToString(dt.Rows[i]["F7"]) + "','" + Convert.ToString(dt.Rows[i]["F8"]) + "','" + Convert.ToString(dt.Rows[i]["F9"]) + "')";

                int j = accessHelper.ReturnSql(sql);

                result = j++;

            }

            if (result > )

            {

                this.Close();

            }

        }

        /// <summary>

        /// 读取excel指定页中的内容

        /// </summary>

        /// <param name="strExcelFileName">excel路径</param>

        /// <returns></returns>

        protected System.Data.DataTable GetDataTable(string strExcelFileName)

        {

            //DataSet myDs = new DataSet();

            System.Data.DataTable dt = new System.Data.DataTable();

            DataRow myRow;

            object oMissing = System.Reflection.Missing.Value;

            Microsoft.Office.Interop.Excel.Application application = new Microsoft.Office.Interop.Excel.Application(); ;

            Microsoft.Office.Interop.Excel.Workbooks workbooks;

            //Microsoft.Office.Interop.Excel.Worksheet worksheet;

            Microsoft.Office.Interop.Excel.Workbook workbook;

            workbooks =  application.Workbooks;

            workbook = returnworkbook(strExcelFileName, workbooks);

            //worksheet = (Worksheet)workbook.Sheets[1];

            for (int sheetint = ; sheetint < ; sheetint++)// DateTime.Now.Day Convert.ToDateTime(TextBox4.Text.Trim()).Day;// myBook.Worksheets.Count;//能得到sheet的数量

            {

                Worksheet mySheet = (Worksheet)workbook.Worksheets[sheetint];

                int rowsint = mySheet.UsedRange.Cells.Rows.Count; //得到行数

                int columnsint = mySheet.UsedRange.Cells.Columns.Count;//得到列数

                if (sheetint == )

                {

                    for (int i = ; i <= columnsint; i++)

                    {

                        dt.Columns.Add("F" + i.ToString(), System.Type.GetType("System.String"));

                    }

                }

                for (int i = ; i <= rowsint; i++) //第一行为标题，不读取

                {

                    myRow = dt.NewRow();

                    for (int j = ; j <= columnsint; j++)

                    {

                        Range r = (Range)mySheet.Cells[i, j];

                        string strValue = r.Text.ToString();

                        string columnname = "F" + j.ToString();

                        myRow[columnname] = (strValue.Length ==  || strValue.Contains("#")) ? " " : strValue;

                    }

                    try

                    {

                        dt.Rows.Add(myRow);

                    }

                    catch { }

                }

            }

            workbook.Close(oMissing, oMissing, oMissing);

            workbooks.Close();

            application.Quit();

            return dt;

        }

        private Workbook returnworkbook(string filename, Workbooks works)  //这里是打一开一个工作表

        {

            Microsoft.Office.Interop.Excel.Workbook wk = works.Open(

             filename, Type.Missing, Type.Missing,

                     Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,

                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,

                     Type.Missing, Type.Missing);

            return wk;

        }

    }