正文

C#从Excel导入数据后添加到数据方法[原创]2013-01-04 15:29:00

【评论】 【打印】 【字体: 】 本文链接:http://blog.pfan.cn/edwardguo/54066.html

分享到:

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;?????????
??????? }
??? }
}

阅读(10675) | 评论(16)


版权声明:编程爱好者网站为此博客服务提供商,如本文牵涉到版权问题,编程爱好者网站不承担相关责任,如有版权问题请直接与本文作者联系解决。谢谢!

评论

loading...
您需要登录后才能评论,请 登录 或者 注册