namespace UClthg.Public.ImportData
{
/// <summary>
/// 单据导入
/// <remarks>
/// 1,打开单据
/// 2,选择待导入Excel文件
/// 3,显示映射关系,点确定,保存到映射表中。
/// 4,导入到内存表中,有错误提示出来,
/// 5,导入完毕显示日志。
/// 6,用户点保存时,提交变更到数据表。
/// </remarks>
/// </summary>
public class ImportDataFromExcel_F
{
/// <summary>
/// 映射关系设置
/// </summary>
/// <param name="businessFormID"></param>
/// <param name="tbFromTable"></param>
/// <param name="tbToTable"></param>
public void Setting(string businessFormID, DataTable tbFromTable, DataTable tbToTable)
{
PubImportMapSettingFrm.ShowImportMapSettingFrm(businessFormID, tbFromTable, tbToTable);
}
}
/// <summary>
/// 导入Excel列表
/// </summary>
public class ImportExcelData_List
{
private DataSet dsExcel = null;
/// <summary>
/// 需要更新数据的行id
/// </summary>
private string Ids;
/// <summary>
/// 更新到数据库表名,对应物理数据表
/// </summary>
public string DbTableName { get; set; }
/// <summary>
/// 载入Excel数据
/// </summary>
public void LoadExcelData()
{
///OpenFileDialog
///开启对话框
OpenFileDialog fileDialog = new OpenFileDialog();
fileDialog.Title = "Excel";
fileDialog.FileName = "";
fileDialog.InitialDirectory =
Environment.GetFolderPath(Environment.SpecialFolder.MyComputer);//为了获取特定的系统文件夹
fileDialog.Filter = "Excel文件(*.xls)|*.xls;*.xlsx";
fileDialog.ValidateNames = true; //文件有效性验证ValidateNames 验证用户输入是否是一个有效的Windows文件名
fileDialog.CheckFileExists = true; //验证路径有效性
fileDialog.CheckPathExists = true; //验证文件有效性
///获取有效路径
string path = string.Empty;
if (fileDialog.ShowDialog() == DialogResult.OK)
{
path = fileDialog.FileName;
}
if (path == "")
{
dsExcel = null;
return;
}
dsExcel = new DataSet();
///创建连接路径和相关配置
bool IS_EXCEL_2007 = false;
string name = "Page1{1}quot;;
string connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + path + ";Extended Properties = 'Excel 8.0;HDR=NO;IMEX=1'";
///对比后缀名的大写形式是否对应
if (System.IO.Path.GetExtension(path).ToUpper() == ".XLSX")
{
connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=YES\"";
IS_EXCEL_2007 = true;
}
OleDbConnection connExcel = new OleDbConnection(connstr);
///获取对应栏目信息,并保存在指定的数据集中
connExcel.Open();
DataTable sheetNames = connExcel.GetOleDbSchemaTable(
System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
OleDbDataAdapter myCommand = null;
string sqlExcel = string.Format(" select * from [" + sheetNames.Rows[0]["TABLE_NAME"] + "]");
myCommand = new OleDbDataAdapter(sqlExcel, connstr);
myCommand.Fill(dsExcel, this.DbTableName);
connExcel.Close();
//Excel2007和2003读取的数据有所不同,需要分开处理
DataTable dtExcel = dsExcel.Tables[0];
if (!IS_EXCEL_2007)
{
dtExcel.Rows.RemoveAt(0);
}
//删除空行
for (int idx = dtExcel.Rows.Count - 1; idx >= 0; idx--)
{
DataRow dr = dtExcel.Rows[idx];
for (int i = 0; i < dtExcel.Columns.Count; i++)
{
if (dr[i].ToString().Trim() != string.Empty)
{
break;
}
if (dr[dtExcel.Columns.Count - 1].ToString().Trim() == string.Empty)
{
dtExcel.Rows.Remove(dr);
}
}
}
if (dtExcel.Rows.Count == 0)
{
throw new Exception("存在非法格式的Excel标签.这样的标签将被忽略导入.");
//PubMessageBox.Information("存在非法格式的Excel标签.这样的标签将被忽略导入.");
//return;
}
}
/// <summary>
/// 更新更改到数据库
/// </summary>
/// <param name="htMap">映射关系 key:数据表列 value:excel表列 </param>
/// <param name="xlsIdFieldName">excel中的ID字段名</param>
/// <param name="dbIdFieldName">数据库表中ID字段名</param>
/// <param name="tabIndex">DataSet中要提交更新表的索引</param>
public int Updata2DB(Hashtable htMap, string xlsIdFieldName, string dbIdFieldName, int tabIndex)
{
int result = -1;
if (dsExcel == null)
{
return -1;
}
//过滤条件
DataTable dtExcel = dsExcel.Tables[tabIndex];
string filterstring = "";
foreach (DataRow dr in dtExcel.Rows)
{
string id = dr[xlsIdFieldName].ToString();
if (id != "")
{
if (filterstring == "")
filterstring = id;
else
filterstring += "," + id;
}
else
{
continue;
}
}
filterstring = dbIdFieldName + " in (" + filterstring + ") "; // id in (1,2,2,3,3)
if (!htMap.ContainsKey(dbIdFieldName))
{
htMap.Add(dbIdFieldName, xlsIdFieldName); //必须要有
}
//提交更新
if (dtExcel != null)
{
DBUpdater updater = new DBUpdater();
result = updater.Update2DB(htMap, dtExcel, filterstring);
}
return result;
}
///<summary>
///??excel??????????????
///</summary>
///<paramname="Path">Excel?????</param>
private void GetDataFromExcelWithAppointSheetName(string Path)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "DataSource=" + Path + ";" + "ExtendedProperties=Excel8.0;";
if (System.IO.Path.GetExtension(Path).ToUpper() == ".XLSX")
{
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path + ";Extended Properties=\"Excel 12.0;HDR=YES\"";
}
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
string[] strTableNames = new string[dtSheetName.Rows.Count];
for (int k = 0; k < dtSheetName.Rows.Count; k++)
{
strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
}
OleDbDataAdapter myCommand = null;
DataTable dt = new DataTable();
string strExcel = "select * from [" + strTableNames[0] + "]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
dt = new DataTable();
myCommand.Fill(dt);
}
}
}
namespace UClthg.Public.ImportData
{
public class DBUpdater
{
private string tableName;
private System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter();
private System.Data.SqlClient.SqlConnection cn = null;
private System.Data.SqlClient.SqlCommandBuilder cmdbuilder = null;
/// <summary>
/// 更新到数据库
/// </summary>
/// <param name="htMap">映射关系 key:数据表列 value:excel表列 </param>
/// <param name="dtExcel">Excel数据表</param>
public int Update2DB(Hashtable htMap, DataTable dtExcel, string filterString)
{
this.tableName = dtExcel.TableName;
//修改Excel表的列头对应到数据库中的表列
foreach (System.Collections.DictionaryEntry en in htMap)
{
string xlsField = en.Value.ToString();
string dbfield = en.Key.ToString();
dtExcel.Columns[xlsField].ColumnName = dbfield;
}
//需要更新的数据表列串
string cols1 = "";
foreach (System.Collections.DictionaryEntry map in htMap)
{
if (cols1 == "")
cols1 += map.Key.ToString();
else
cols1 += "," + map.Key.ToString();
}
string sql = "select " + cols1 + " from [" + this.tableName + "]";
if (filterString != "")
{
sql += " where 1=1 and " + filterString;
}
//载入需要更新的数据
DataSet ds = new DataSet();
PubDataAccess.GetConnection(out cn);
SqlDataAdapter myAdapter = new SqlDataAdapter();
myAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; //dataAdapter
SqlCommand myCommand = new SqlCommand(sql, cn);
myAdapter.SelectCommand = myCommand;
SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myAdapter); //CommandBuilder
myAdapter.InsertCommand = myCommandBuilder.GetInsertCommand();
myAdapter.UpdateCommand = myCommandBuilder.GetUpdateCommand();
myAdapter.DeleteCommand = myCommandBuilder.GetDeleteCommand();
myAdapter.Fill(ds, this.tableName);
DataTable dtDB = ds.Tables[this.tableName];
//逐条提交更新到数据库
int EffectRowCount = 0; //更新数据行数
DataTable dtTmp = dtDB.Clone();
//Excel表的导入结果,失败原因
DataColumn newcol = new DataColumn("结果");
dtExcel.Columns.Add(newcol);
newcol = new DataColumn("失败原因");
dtExcel.Columns.Add(newcol);
foreach (DataRow dr in dtExcel.Rows)
{
try
{
dtTmp = dtDB.Clone();
DataRow drTmp = dtTmp.NewRow();
System.Data.DataColumn[] pk = dtTmp.PrimaryKey;
string pkIdColName = pk[0].ColumnName;
#region MyRegion
for (int i = 0; i < dtExcel.Columns.Count; i++)
{
string colName = dtExcel.Columns[i].ColumnName;
for (int j = 0; j < dtTmp.Columns.Count; j++)
{
DataColumn col = dtTmp.Columns[j];
if (col.ColumnName.ToUpper() == pkIdColName.ToUpper())
{
if (dr[col.ColumnName].ToString() == "")
{
drTmp[j] = -1;
break;
}
}
if (col.ColumnName == colName)
{
if (col.DataType.ToString() == "System.DateTime")
{
DateTime dtVal = Convert.ToDateTime(dr[colName].ToString());
if (dtVal.Year < 1999)
drTmp[j] = DBNull.Value;
else
drTmp[j] = dtVal;
}
else
{
drTmp[j] = dr[colName].ToString();
}
}
}
}
#endregion
dtTmp.Rows.Add(drTmp);
myAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
ds.Merge(dtTmp,false,MissingSchemaAction.Add);
DataSet dsChanged = ds.GetChanges();
if (myAdapter.Update(dsChanged, this.tableName) > 0)
{
EffectRowCount++;
}
dr["结果"] = "OK ,导入成功!";
ds.AcceptChanges();
}
catch (Exception ex)
{
dr.RowError = "ERROR:" + ex.Message;
dr["失败原因"] = ex.Message;
dr["结果"] = "导入失败";
}
}
if (MessageBox.Show("本次导入数据" + EffectRowCount.ToString() + "行,是否查看日志?", "导入过程完毕",
MessageBoxButtons.YesNo, MessageBoxIcon.Information)
== DialogResult.Yes)
{
using (ImportResultFrm frm = new ImportResultFrm(dtExcel))
{
frm.Text = "导入数据日志";
frm.ShowDialog();
}
}
return EffectRowCount;
}
public static DataSet GetDataTable(string sql,string dbtableName)
{
System.Data.SqlClient.SqlConnection cn = null;
DataSet ds = new DataSet();
PubDataAccess.GetConnection(out cn);
SqlDataAdapter myAdapter = new SqlDataAdapter();
myAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; //dataAdapter
SqlCommand myCommand = new SqlCommand(sql, cn);
myAdapter.SelectCommand = myCommand;
SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myAdapter); //CommandBuilder
myAdapter.Fill(ds, dbtableName);
return ds;
}
public static int UpdateDB(string sql, DataTable dt, string dbTableName)
{
DataTable dtCopy = dt.Copy();
dtCopy.TableName = dbTableName;
System.Data.SqlClient.SqlConnection cn = null;
//载入需要更新的数据
DataSet ds = new DataSet();
PubDataAccess.GetConnection(out cn);
SqlDataAdapter myAdapter = new SqlDataAdapter();
myAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; //dataAdapter
SqlCommand myCommand = new SqlCommand(sql, cn);
myAdapter.SelectCommand = myCommand;
SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myAdapter); //CommandBuilder
myAdapter.InsertCommand = myCommandBuilder.GetInsertCommand();
myAdapter.UpdateCommand = myCommandBuilder.GetUpdateCommand();
myAdapter.DeleteCommand = myCommandBuilder.GetDeleteCommand();
myAdapter.Fill(ds, dbTableName);
int EffectRowCount = 0;
ds.Merge(dtCopy, false, MissingSchemaAction.AddWithKey);
DataSet dsChanged = ds.GetChanges();
if (dsChanged != null && dsChanged.Tables.Count > 0 && dsChanged.Tables[0].Rows.Count > 0)
{
EffectRowCount = myAdapter.Update(dsChanged, dbTableName);
}
return EffectRowCount;
}
}
}