注意：此处为Console写法，如果要用web写法，请参考http://www.cnblogs.com/wcj1984abc/archive/2011/03/17/1986872.html
先来看导入excel:
web.config配置（连接的是mysql数据库）
<add name="ReadCRMDb" connectionString="server=localhost;user id=root;password=123123;persist security info=True;database=demo;Pooling=False;character set=utf8;Use Affected Rows=true;Old Guids=true;" providerName="MySql.Data.MySqlClient" />
c#代码如下:
string Sqlconnection = ConfigurationManager.ConnectionStrings["ReadCRMDb"].ConnectionString;
读入excel,写入datable
string FileName = System.IO.Directory.GetCurrentDirectory() + "\\shanghai.xls"; //"C:\\Users\\robot\\Desktop\\aaa.xls";
string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\"";
OleDbConnection myConn = new OleDbConnection(strCon);
string mySQLstr = " SELECT f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,f11,f12 FROM [1$]";//shanghai
//string mySQLstr = " SELECT f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,f19 FROM [1$]";//guangzhou
myConn.Open();
OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(mySQLstr, myConn);
DataSet myDS = new DataSet();
myDataAdapter.Fill(myDS, "[1$]");
DataTable dtt = myDS.Tables[0];
声明datatable,方便出错是写入excel
DataTable dt = new DataTable("datas");
dt.Columns.Add("失败原因", typeof(System.String));
dt.Columns.Add("公司全称", typeof(System.String));
dt.Columns.Add("行业1", typeof(System.String));
dt.Columns.Add("行业2", typeof(System.String));
dt.Columns.Add("联系人", typeof(System.String));
dt.Columns.Add("联系人部门职位", typeof(System.String));
dt.Columns.Add("座机1", typeof(System.String));
dt.Columns.Add("座机2", typeof(System.String));
dt.Columns.Add("手机1", typeof(System.String));
dt.Columns.Add("手机2", typeof(System.String));
dt.Columns.Add("email", typeof(System.String));
dt.Columns.Add("网址", typeof(System.String));
dt.Columns.Add("crm登陆名", typeof(System.String));
遍历datatable,写入mysql
foreach (DataRow d in dtt.Rows)
{
var customername = ""; //公司名称
var hangyeone = -1;//行业1--服务
var hangyetwo = -1;//行业2--服务
var Industry = -1;//房产，招聘，车辆
var tellone = "";//座机1
var telltwo = "";//座机2
var phoneone = "";//手机1
var phonetwo = "";//手机2
var useremail = "";//用户email
var email = "";//email
var employee_id = "";//销售id
var employee_name = "";//销售姓名
var Contact = "";//联系人
var ContactTitle = "";//联系人部门职位
var WebSite = "";//网址
Console.WriteLine("正在导入" + d[0].ToString() + d[11].ToString());
if (!string.IsNullOrEmpty(d[0].ToString()))
{
customername = d[0].ToString(); //公司名称
}
else
{
dt.Rows.Add("必填项为空(必填项客户名称为空)", "" + d[0] + "", "" + d[1] + "", "" + d[2] + "", "" + d[3] + "", "" + d[4] + "", "" + d[5] + "", "" + d[6] + "", "" + d[7] + "", "" + d[8] + "", "" + d[9] + "", "" + d[10] + "", "" + d[11] + "");
continue;
}
if (!string.IsNullOrEmpty(d[5].ToString()))
{
tellone = d[5].ToString();
}
if (!string.IsNullOrEmpty(d[6].ToString()))
{
telltwo = d[6].ToString();
}
if (!string.IsNullOrEmpty(d[7].ToString()))
{
phoneone = d[7].ToString();
}
if (!string.IsNullOrEmpty(d[8].ToString()))
{
phonetwo = d[8].ToString();
}
if (tellone == "" && telltwo == "" && phoneone == "" && phonetwo == "")
{
dt.Rows.Add("必填项为空(必填项座机和手机全都为空)", "" + d[0] + "", "" + d[1] + "", "" + d[2] + "", "" + d[3] + "", "" + d[4] + "", "" + d[5] + "", "" + d[6] + "", "" + d[7] + "", "" + d[8] + "", "" + d[9] + "", "" + d[10] + "", "" + d[11] + "");
continue;
}
if (!string.IsNullOrEmpty(d[11].ToString()))
{
useremail = d[11].ToString() + "@ganji.com";
}
else
{
dt.Rows.Add("必填项为空(必填项CRM登录邮箱为空)", "" + d[0] + "", "" + d[1] + "", "" + d[2] + "", "" + d[3] + "", "" + d[4] + "", "" + d[5] + "", "" + d[6] + "", "" + d[7] + "", "" + d[8] + "", "" + d[9] + "", "" + d[10] + "", "" + d[11] + "");
continue;
}
if (!string.IsNullOrEmpty(d[3].ToString()))
{
Contact = d[3].ToString();
}
if (!string.IsNullOrEmpty(d[4].ToString()))
{
ContactTitle = d[4].ToString();
}
if (!string.IsNullOrEmpty(d[9].ToString()))
{
email = d[9].ToString();
}
if (!string.IsNullOrEmpty(d[10].ToString()))
{
WebSite = d[10].ToString();
}
StringBuilder strSql = new StringBuilder();
strSql.Clear();
strSql.Append(" insert into opportunity( FullName,Contact,ContactTitle,Phone,Phone2,Cellphone,Cellphone2,email,STATUS,CityId,SaleGroup,SaleGroupArea,EmployeeId,EmployeeName,CreatorName,CreatedTime,WebSite,CategoryId,MajorCategoryId,Industry ) ");
strSql.Append(" values ( ");
strSql.Append(" '" + customername + "','" + Contact + "','" + ContactTitle + "','" + tellone + "','" + telltwo + "','" + phoneone + "','" + phonetwo + "','" + email + "',1,13," + salegroup + "," + salegroup_area + "," + employee_id + ",'" + employee_name + "','系统创建','" + DateTime.Now + "','" + WebSite + "'," + hangyeone + "," + hangyetwo + "," + Industry + "");
strSql.Append(" ) ");
DataRow insertdt = MySqlHelper.ExecuteDataRow(Sqlconnection, strSql.ToString());
strSql.Clear();
opporunity是数据库中的表名
判断如果dt不为空，写入新的excel
if (dt != null)
{
Export(dt, "shanghaiback", "b");
}
具体生成excel的方法，为上面代码调用的方法
public static void Export(DataTable dt, string filepath, string tablename)
{
//excel 2003格式
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
//Excel 2007格式
//string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=Excel 12.0 Xml;";
try
{
using (OleDbConnection con = new OleDbConnection(connString))
{
con.Open();
StringBuilder strSQL = new StringBuilder();
strSQL.Append("CREATE TABLE ").Append("[" + tablename + "]");
strSQL.Append("(");
for (int i = 0; i < dt.Columns.Count; i++)
{
strSQL.Append("[" + dt.Columns[i].ColumnName + "] text,");
}
strSQL = strSQL.Remove(strSQL.Length - 1, 1);
strSQL.Append(")");
OleDbCommand cmd = new OleDbCommand(strSQL.ToString(), con);
cmd.ExecuteNonQuery();
for (int i = 0; i < dt.Rows.Count; i++)
{
strSQL.Clear();
StringBuilder strfield = new StringBuilder();
StringBuilder strvalue = new StringBuilder();
for (int j = 0; j < dt.Columns.Count; j++)
{
strfield.Append("[" + dt.Columns[j].ColumnName + "]");
strvalue.Append("'" + dt.Rows[i][j].ToString() + "'");
if (j != dt.Columns.Count - 1)
{
strfield.Append(",");
strvalue.Append(",");
}
else
{
}
}
cmd.CommandText = strSQL.Append(" insert into [" + tablename + "]( ")
.Append(strfield.ToString())
.Append(") values (").Append(strvalue).Append(")").ToString();
cmd.ExecuteNonQuery();
}
con.Close();
}
Console.WriteLine("OK");
Console.Read();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
