本文转自：http://www.cnblogs.com/benbenfishfish/archive/2011/07/28/2119662.html
using System; |
using System.Collections.Generic; |
using System.Text; |
using System.Data.OleDb; |
using System.Data.SqlClient; |
using System.Data; |
using System.Windows.Forms; |
using System.Data.Odbc; |
|
namespace QZMDB.Function |
{ |
|
/// <summary> |
/// Excel文件的读取 |
/// </summary> |
public class ExcelLoadFunction |
{ |
public static string ConStr = Properties.Settings.Default.QZMDBConnectionString; |
DataSet ds = new DataSet(); |
/// <summary> |
/// 读取Excel |
/// </summary> |
/// <param name="fileName"></param> |
/// <returns></returns> |
public int LoadExcel(string fileName) |
{ |
// return BlacklistQuChong(fileName); ////如果需要对黑名单进行去重处理则放行此代码段.注掉"ds-return0;"之间代码段 |
ds = CSV_Getds(fileName); |
if (ds.Tables[0].Rows.Count != 0) |
{ |
|
try |
{ |
|
using (SqlBulkCopy sbc = new SqlBulkCopy(Properties.Settings.Default.QZMDBConnectionString)) |
{ |
sbc.DestinationTableName = "tbBlackList";//对应的数据库表名 |
sbc.ColumnMappings.Clear(); |
sbc.ColumnMappings.Add("黑名单号码", "mobile");//关联ds.tables[0]中的列和数据库中的列明 |
sbc.BatchSize = 1000; //设置每次放入条数 |
sbc.WriteToServer(ds.Tables[0]); |
} |
return ds.Tables[0].Rows.Count; |
} |
catch (Exception ex) |
{ |
System.Windows.Forms.MessageBox.Show(ex.Message); |
return 0; |
} |
|
} |
return 0; |
|
} |
/// <summary> |
/// 黑名单导入(带去重处理) |
/// </summary> |
/// <param name="fileName"></param> |
/// <returns></returns> |
private int BlacklistQuChong(string fileName) |
{ |
try |
{ |
SqlConnection sConn = new SqlConnection(ConStr); |
string[] name = GetTablesFromOleDb(fileName); |
string a = name[0].Replace("'", ""); |
string OLEDBConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + |
"Extended Properties=\"Excel 8.0;HDR=YES;IMEX=0\";" + // 指定扩展属性为 Microsoft Excel 8.0 (97) 9.0 (2000) 10.0 (2002)，并且第一行作为数据返回，且以文本方式读取 |
"data source=" + fileName; |
sConn.Open(); |
OleDbDataAdapter oda = new OleDbDataAdapter("select * from [" + a + "]", OLEDBConnStr); |
DataTable sourceDataTable = new DataTable(); |
oda.Fill(sourceDataTable); |
oda.Dispose(); |
|
int result = 0; |
foreach (DataRow item in sourceDataTable.Rows) |
{ |
string ids = Convert.ToString(item["黑名单号码"]); |
if (delete(ids)) |
{ |
string sql = "insert into tbBlackList values(@mobile)"; |
SqlParameter[] param = new SqlParameter[] |
{ |
new SqlParameter("@mobile",ids), |
}; |
result += SqlHelper.SqlHelper.ExecuteNonQuery(sConn, System.Data.CommandType.Text, sql, param); |
} |
} |
|
sConn.Close(); |
return result; |
} |
catch (Exception ex) |
{ |
MessageBox.Show(ex.Message); |
throw; |
} |
} |
|
|
public static string[] GetTablesFromOleDb(string path) |
{ |
|
string[] result = null; |
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + |
"Extended Properties=\"Excel 8.0;HDR=YES;IMEX=0\";" + // 指定扩展属性为 Microsoft Excel 8.0 (97) 9.0 (2000) 10.0 (2002)，并且第一行作为数据返回，且以文本方式读取 |
"data source=" + path; |
OleDbConnection conn = null; |
DataTable tblSchema = null; |
|
|
// 初始化连接，并打开 |
conn = new OleDbConnection(connStr); |
try |
{ |
conn.Open(); |
tblSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); |
result = new string[tblSchema.Rows.Count]; |
for (int i = 0; i < tblSchema.Rows.Count; i++) |
{ |
result[i] = tblSchema.Rows[i][2].ToString(); |
} |
} |
catch (Exception) |
{ |
return null; |
} |
|
finally |
{ |
// 关闭连接 |
conn.Close(); |
} |
return result; |
} |
/* |
* 此处代码段进行了修改 |
* 原有:先判断是否存在数据行有删除后添加 |
* 更改:如果有重复的数据行则保持不执行添加操作 |
* 时间:20110711 |
* 备注;详情参见注掉的代码片段 |
* 修改人:Roni |
* **/ |
private bool delete(string ids) |
{ |
SqlConnection sConn = new SqlConnection(ConStr); |
|
string select = "select * from tbBlackList where mobile='" + ids + "'"; |
|
using (SqlDataReader reader = SqlHelper.SqlHelper.ExecuteReader(ConStr, CommandType.Text, select, null)) |
{ |
|
if (reader.HasRows) |
{ |
//string s = "delete from tbBlackList where mobile='" + ids + "'"; |
//SqlHelper.SqlHelper.ExecuteNonQuery(sConn, System.Data.CommandType.Text, s, null); |
return false; |
} |
|
} |
sConn.Close(); |
return true; |
} |
|
internal static bool CreateExcelByCondition(string filePath, string ExcelName) |
{ |
throw new NotImplementedException(); |
} |
/// <summary> |
/// 查找手机号码是否重复 |
/// </summary> |
/// <param name="ids"></param> |
/// <returns></returns> |
private bool deleteByMobile(string ids) |
{ |
SqlConnection sConn = new SqlConnection(ConStr); |
|
string select = "select * from tbDialResultHis where mobile='" + ids + "'"; |
|
using (SqlDataReader reader = SqlHelper.SqlHelper.ExecuteReader(ConStr, CommandType.Text, select, null)) |
{ |
|
if (reader.HasRows) |
{ |
return false; |
} |
|
} |
sConn.Close(); |
return true; |
} |
|
|
|
/// <summary> |
/// Excel |
/// </summary> |
/// <param name="fileName"></param> |
/// <returns></returns> |
public int LoadExcelByFileName(string fileName, int projectId, int productID, string rev_file_name, string input_date) |
{ |
ds = CSV_Getds(fileName); |
|
if (ds.Tables[0].Rows.Count != 0) |
{ |
try |
{ |
if (!ds.Tables[0].Columns.Contains("项目编号")) |
{ |
ds.Tables[0].Columns.Add(new DataColumn("项目编号", typeof(int))); //临时表datatable中没有此列则创建 |
} |
if (!ds.Tables[0].Columns.Contains("产品编号")) |
{ |
ds.Tables[0].Columns.Add(new DataColumn("产品编号", typeof(int))); |
|
} |
if (!ds.Tables[0].Columns.Contains("导入时间")) |
{ |
ds.Tables[0].Columns.Add(new DataColumn("导入时间", typeof(DateTime))); |
|
} |
if (!ds.Tables[0].Columns.Contains("导入文件名")) |
{ |
ds.Tables[0].Columns.Add(new DataColumn("导入文件名", typeof(string))); |
|
} |
|
//if (!ds.Tables[0].Columns.Contains("手机1")) |
//{ |
// ds.Tables[0].Columns.Add(new DataColumn("手机1", typeof(string))); |
|
//} |
string errmsg = ""; |
foreach (DataRow dr in ds.Tables[0].Rows) |
{ |
|
dr["项目编号"] = projectId; |
dr["产品编号"] = productID; |
dr["导入时间"] = input_date; |
dr["导入文件名"] = rev_file_name; |
if (dr["手机"].ToString().Length > 11) |
{ |
errmsg += dr["手机"].ToString()+" 手机长度超过11位\r\n"; |
} |
|
} |
|
if(errmsg.Length!=0) |
{ |
MessageBox.Show(errmsg); |
return 0; |
} |
using (SqlBulkCopy sbc = new SqlBulkCopy(Properties.Settings.Default.QZMDBConnectionString)) |
{ |
sbc.DestinationTableName = "tbDialResultHis";//对应的数据库表名 |
sbc.ColumnMappings.Clear(); |
sbc.ColumnMappings.Add("项目编号", "project_id");//关联ds.tables[0]中的列和数据库中的列明 |
sbc.ColumnMappings.Add("产品编号", "product_id"); |
sbc.ColumnMappings.Add("导入时间", "input_dt"); |
sbc.ColumnMappings.Add("导入文件名", "rev_file_name"); |
|
|
sbc.ColumnMappings.Add("手机", "mobile"); |
sbc.ColumnMappings.Add("姓名", "name"); |
sbc.ColumnMappings.Add("性别", "sex"); |
sbc.ColumnMappings.Add("年龄", "age"); |
sbc.ColumnMappings.Add("营销是否成功", "marketing_result"); |
sbc.ColumnMappings.Add("已接通结果", "dial_result"); |
sbc.ColumnMappings.Add("备注", "dial_remark"); |
sbc.ColumnMappings.Add("电话经理", "dial_user"); |
sbc.BatchSize = 1000; //设置每次放入条数 |
sbc.WriteToServer(ds.Tables[0]); |
} |
return ds.Tables[0].Rows.Count; |
} |
catch (Exception ex) |
{ |
System.Windows.Forms.MessageBox.Show(ex.Message); |
return 0; |
} |
|
} |
return 0; |
|
} |
/// <summary> |
/// 拨打清单导入 |
/// </summary> |
/// <param name="fileName"></param> |
/// <param name="projectId"></param> |
/// <param name="productID"></param> |
/// <param name="rev_file_name"></param> |
/// <param name="input_date"></param> |
/// <returns></returns> |
public int LoadExcelByFileName_CallList(string fileName, int projectId, int productID, string rev_file_name, string input_date) |
{ |
ds = CSV_Getds(fileName); |
if (ds.Tables[0].Rows.Count != 0) |
{ |
try |
{ |
if (!ds.Tables[0].Columns.Contains("项目编号")) |
{ |
ds.Tables[0].Columns.Add(new DataColumn("项目编号", typeof(int))); //临时表datatable中没有此列则创建 |
} |
if (!ds.Tables[0].Columns.Contains("产品编号")) |
{ |
ds.Tables[0].Columns.Add(new DataColumn("产品编号", typeof(int))); |
|
} |
if (!ds.Tables[0].Columns.Contains("导入时间")) |
{ |
ds.Tables[0].Columns.Add(new DataColumn("导入时间", typeof(DateTime))); |
|
} |
if (!ds.Tables[0].Columns.Contains("导入文件名")) |
{ |
ds.Tables[0].Columns.Add(new DataColumn("导入文件名", typeof(string))); |
|
} |
foreach (DataRow dr in ds.Tables[0].Rows) |
{ |
dr["项目编号"] = projectId; |
dr["产品编号"] = productID; |
dr["导入时间"] = input_date; |
dr["导入文件名"] = rev_file_name; |
} |
|
using (SqlBulkCopy sbc = new SqlBulkCopy(Properties.Settings.Default.QZMDBConnectionString)) |
{ |
sbc.DestinationTableName = "tbDialHis";//对应的数据库表名 |
sbc.ColumnMappings.Clear(); |
sbc.ColumnMappings.Add("项目编号", "project_id");//关联ds.tables[0]中的列和数据库中的列明 |
sbc.ColumnMappings.Add("产品编号", "product_id"); |
sbc.ColumnMappings.Add("导入时间", "input_dt"); |
//sbc.ColumnMappings.Add("导入文件名", "rev_file_name"); |
|
|
sbc.ColumnMappings.Add("被叫", "mobile"); |
sbc.ColumnMappings.Add("呼出时间", "dial_dt"); |
sbc.ColumnMappings.Add("通话时长(秒)", "dial_duration"); |
sbc.BatchSize = 1000; //设置每次放入条数 |
sbc.WriteToServer(ds.Tables[0]); |
} |
return ds.Tables[0].Rows.Count; |
} |
catch (Exception ex) |
{ |
System.Windows.Forms.MessageBox.Show(ex.Message); |
return 0; |
} |
|
} |
return 0; |
|
|
} |
|
/// <summary> |
/// QC数据导入 |
/// </summary> |
/// <param name="projectID"></param> |
/// <param name="productID"></param> |
/// <param name="filename"></param> |
/// <param name="time"></param> |
/// <returns></returns> |
internal int LoadExcelByFileName_QCData(int projectID, int productID, string filename, string time) |
{ |
ds = CSV_Getds(filename); |
if (ds.Tables[0].Rows.Count != 0) |
{ |
try |
{ |
if (!ds.Tables[0].Columns.Contains("项目编号")) |
{ |
ds.Tables[0].Columns.Add(new DataColumn("项目编号", typeof(int))); //临时表datatable中没有此列则创建 |
} |
if (!ds.Tables[0].Columns.Contains("产品编号")) |
{ |
ds.Tables[0].Columns.Add(new DataColumn("产品编号", typeof(int))); |
|
} |
if (!ds.Tables[0].Columns.Contains("导入时间")) |
{ |
ds.Tables[0].Columns.Add(new DataColumn("导入时间", typeof(DateTime))); |
|
} |
if (!ds.Tables[0].Columns.Contains("导入文件名")) |
{ |
ds.Tables[0].Columns.Add(new DataColumn("导入文件名", typeof(string))); |
|
} |
foreach (DataRow dr in ds.Tables[0].Rows) |
{ |
dr["项目编号"] = projectID; |
dr["产品编号"] = productID; |
dr["导入时间"] = time; |
dr["导入文件名"] = filename; |
} |
|
using (SqlBulkCopy sbc = new SqlBulkCopy(Properties.Settings.Default.QZMDBConnectionString)) |
{ |
sbc.DestinationTableName = "tbQCHis";//对应的数据库表名 |
sbc.ColumnMappings.Clear(); |
sbc.ColumnMappings.Add("项目编号", "project_id");//关联ds.tables[0]中的列和数据库中的列明 |
sbc.ColumnMappings.Add("产品编号", "product_id"); |
sbc.ColumnMappings.Add("导入时间", "input_dt"); |
sbc.ColumnMappings.Add("导入文件名", "rev_file_name"); |
|
|
sbc.ColumnMappings.Add("用户号码", "mobile"); |
sbc.ColumnMappings.Add("开通时间", "qc_order_dt"); |
sbc.BatchSize = 1000; //设置每次放入条数 |
sbc.WriteToServer(ds.Tables[0]); |
} |
return ds.Tables[0].Rows.Count; |
} |
catch (Exception ex) |
{ |
System.Windows.Forms.MessageBox.Show(ex.Message); |
return 0; |
} |
|
} |
return 0; |
|
} |
|
/// <summary> |
/// 电信反馈表 |
/// </summary> |
/// <param name="FilePath"></param> |
/// <param name="projectID"></param> |
/// <param name="productID"></param> |
/// <param name="input_date"></param> |
/// <returns></returns> |
public int LoadExcelByFileName_tbFeedBack(string FilePath, int projectID, int productID, string input_date) |
{ |
ds = CSV_Getds(FilePath); |
if (ds.Tables[0].Rows.Count != 0) |
{ |
try |
{ |
if (!ds.Tables[0].Columns.Contains("项目编号")) |
{ |
ds.Tables[0].Columns.Add(new DataColumn("项目编号", typeof(int))); //临时表datatable中没有此列则创建 |
} |
if (!ds.Tables[0].Columns.Contains("产品编号")) |
{ |
ds.Tables[0].Columns.Add(new DataColumn("产品编号", typeof(int))); |
|
} |
if (!ds.Tables[0].Columns.Contains("电信反馈时间")) |
{ |
ds.Tables[0].Columns.Add(new DataColumn("电信反馈时间", typeof(DateTime))); |
|
} |
if (!ds.Tables[0].Columns.Contains("文件导入日期")) |
{ |
ds.Tables[0].Columns.Add(new DataColumn("文件导入日期", typeof(DateTime))); |
|
} |
foreach (DataRow dr in ds.Tables[0].Rows) |
{ |
dr["项目编号"] = projectID; |
dr["产品编号"] = productID; |
dr["电信反馈时间"] = input_date; |
dr["文件导入日期"] = input_date; |
} |
|
using (SqlBulkCopy sbc = new SqlBulkCopy(Properties.Settings.Default.QZMDBConnectionString)) |
{ |
sbc.DestinationTableName = "tbFeedBack";//对应的数据库表名 |
sbc.ColumnMappings.Clear(); |
sbc.ColumnMappings.Add("项目编号", "project_id");//关联ds.tables[0]中的列和数据库中的列明 |
sbc.ColumnMappings.Add("产品编号", "product_id"); |
sbc.ColumnMappings.Add("电信反馈时间", "fb_dt"); |
sbc.ColumnMappings.Add("文件导入日期", "input_dt"); |
|
|
sbc.ColumnMappings.Add("手机号码", "mobile"); |
sbc.ColumnMappings.Add("在网情况", "fb_online"); |
sbc.ColumnMappings.Add("订购是否成功", "fb_order_result"); |
sbc.ColumnMappings.Add("离网日期", "fb_leave_dt"); |
sbc.BatchSize = 1000; //设置每次放入条数 |
sbc.WriteToServer(ds.Tables[0]); |
} |
return ds.Tables[0].Rows.Count; |
} |
catch (Exception ex) |
{ |
System.Windows.Forms.MessageBox.Show(ex.Message); |
return 0; |
} |
|
} |
return 0; |
} |
public DataSet CSV_Getds(string filePath) |
{ |
|
try |
{ |
string[] name = GetTablesFromOleDb(filePath); |
string a = name[0].Replace("'", ""); |
string OLEDBConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + |
"Extended Properties=\"Excel 8.0;HDR=YES;IMEX=0\";" + // 指定扩展属性为 Microsoft Excel 8.0 (97) 9.0 (2000) 10.0 (2002)，并且第一行作为数据返回，且以文本方式读取 |
"data source=" + filePath; |
OleDbDataAdapter oda = new OleDbDataAdapter("select * from [" + a + "]", OLEDBConnStr); |
DataSet sourceDataTable = new DataSet(); |
oda.Fill(sourceDataTable); |
oda.Dispose(); |
return sourceDataTable; |
} |
catch (Exception ex) |
{ |
System.Windows.Forms.MessageBox.Show(ex.Message); |
return null; |
} |
|
} |
|
} |
|
} |
浙公网安备 33010602011771号