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