本文转自: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 ; |
} |
|
} |
|
} |
|
} |