using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace ReadDataFromExcel
{
    class Program
    {
        static void Main(string[] args)
        {
            string excelFile = @"excel文件路径";
            DataSet info = ImportExcel(excelFile);
            bool result = ToDataBase(info);
        }

        private static DataSet ImportExcel(string strFileName)
        {
            if (strFileName == "") return null;
            string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
                "Data Source=" + strFileName + ";" +
                "Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;\"";
            OleDbConnection myConn = new OleDbConnection(strConn);
            myConn.Open();
            OleDbDataAdapter ExcelDA = new OleDbDataAdapter(@"SELECT mobilephone,
            CtripProduct, FlightCity, HotelCity, VactionCity, HotelSelect, VactionSelect,
            FlightSelect, ServiceTime, Status, email, gender, username FROM [Sheet1$]", strConn);
            DataSet ExcelDs = new DataSet();
            try
            {
                ExcelDA.Fill(ExcelDs, "mbr_magazine_customerservicediy");
            }
            catch (Exception err)
            {
                System.Console.WriteLine(err.ToString());
            }
            finally
            {
                myConn.Close();
            }

            return ExcelDs;
        }

        private static bool ToDataBase(DataSet ds)
        {
            string strConn = "数据库连接字符串";
            string strSp = "存储过程";
            SqlConnection conn = null;

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

                if (ds.Tables.Count == 0)
                    return false;
                else
                {
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        SqlCommand sqlCmd = new SqlCommand(strSp, conn);
                        sqlCmd.CommandType = CommandType.StoredProcedure;
                        SqlParameter parm0 = new SqlParameter("col1", SqlDbType.Int);
                        SqlParameter parm1 = new SqlParameter("col2", SqlDbType.Char, 11);
                        parm1.Value = ds.Tables[0].Rows[i][0].ToString();
                        SqlParameter parm2 = new SqlParameter("col3", SqlDbType.VarChar, 20);
                        parm2.Value = ds.Tables[0].Rows[i][1].ToString();
                        SqlParameter parm3 = new SqlParameter("col4", SqlDbType.VarChar, 20);
                        parm3.Value = ds.Tables[0].Rows[i][2].ToString();

                        sqlCmd.Parameters.Add(parm0); parm0.Direction = ParameterDirection.Output;
                        sqlCmd.Parameters.Add(parm1); parm1.Direction = ParameterDirection.Input;
                        sqlCmd.Parameters.Add(parm2); parm2.Direction = ParameterDirection.Input;
                        sqlCmd.Parameters.Add(parm3); parm3.Direction = ParameterDirection.Input;

                        int result = sqlCmd.ExecuteNonQuery();
                    }
                }

            }
            catch (Exception ex)
            {

            }
            finally
            {
                conn.Close();
            }

            return false;         
        }
    }
}

评论