您当前的位置:首页>>编程相关>>正文
 
C#从Excel导入数据后添加到数据方法
作者: 来源:代码梦工厂 日期:2013/7/24 15:42:23  点击次数:

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


上一篇:在VB中如何使用正则表达式
下一篇:没有了
  北京总部: 4006-505-646
  天 津 部: 4006-505-646
  上 海 部: 4006-505-646
  深 圳 部: 4006-505-646
  广 州 部: 4006-505-646
  重 庆 部: 4006-505-646
  南 京 部: 4006-505-646
  其它地区: 4006-505-646
经典案例
中国石油管理局-Oracle数据库恢
中国网通-IBM EXP300磁盘阵列数
大连鸿德经贸有限责任公司-SQL
中国地质环境监测院-HP LH3000
藁城市东街百货-EFS文件解密成
工商银行某省分行-AIX删除LV数
中央电视台新闻评论部-苹果分
promise乔鼎硬盘阵列数据恢复成
麒麟童文化-苹果分区无法打开,
NAS 8100服务器数据恢复成功 
解决方案
raid磁盘阵列OFFLINE后的应急方
磁盘未被格式化,是否格式化数据
误GHOST、误一键恢复灾难应急方
误删除、误格式化数据灾难应急
LINUX FSCK数据出错灾难应急方
北亚数据恢复 - 联系我们 - 关于北亚 - 友情链接 - 网站地图 - RSS聚合 
版权所有 北亚数据恢复中心
全国统一客服热线:4006-505-646
北京总部:北京市海淀区永丰基地丰慧中路7号新材料创业大厦B座205室
i