using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Windows.Controls; using SystemSetting.SettingCommon; using System.Windows; using System.Data.OleDb; using System.Data; using System.Collections; using System.Windows.Media;namespace SystemSetting.ExternalImport { /// <summary> /// 外部导入数据 /// </summary> class ExternalImport {
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Controls;
using SystemSetting.SettingCommon;
using System.Windows;
using System.Data.OleDb;
using System.Data;
using System.Collections;
using System.Windows.Media;namespace SystemSetting.ExternalImport
{
/// <summary>
/// 外部导入数据
/// </summary>
class ExternalImport
{
#endregion #region 事件
/// <summary>
/// 浏览文件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
void btnBrowse_Click(object sender, RoutedEventArgs e)
{
resultPanel.Children.Clear();
IsInsert = true;
open = new Microsoft.Win32.OpenFileDialog();
open.Filter = "文件格式(EXCEL) | * .XLS";
if (Convert.ToBoolean(open.ShowDialog()))
{
txtPath.Text = open.FileName;
ReadExcel(txtPath.Text);
}
} /// <summary>
/// 导入
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
void btnImport_Click(object sender, RoutedEventArgs e)
{
if (IsInsert)
{
foreach (string array in arrayList)
{
TakeTableName take = new TakeTableName();
string tableName = take.GetTableName(array);
if (tableName == null)
{
MessageBox.Show("对不起,存在未识别的表\"" + array + "\"!", "友情提示", MessageBoxButton.OK, MessageBoxImage.Information);
continue;
}
AddDB(tableName, ImportDataGrid(array));
take = null;
}
MessageBox.Show("导入数据成功!", "友情提示", MessageBoxButton.OK, MessageBoxImage.Information);
resultPanel.Children.Clear();
}
else
{
MessageBox.Show("对不起,导入数据存在问题!", "友情提示", MessageBoxButton.OK, MessageBoxImage.Information);
}
}
#endregion #region 方法 /// <summary>
/// 读取EXCEL
/// </summary>
/// <param name="filePath"></param>
private void ReadExcel(string filePath)
{
EXCEL_CONN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + filePath + ";Extended Properties ='Excel 8.0;HDR=YES;IMEX=1'";
arrayList = ReadExcelTakeTableName();
foreach (var name in arrayList)
{
if (!TableViews.Contains(name.ToString()))
{
TextBlock resultMsg = CreateTextBlock("存在未识别的表\"" + name + "\"", Brushes.Red, FontWeights.Bold, new Thickness(0, 5, 0, 0));
resultPanel.Children.Add(resultMsg);
IsInsert = false;
}
else
{
CheckFormatIsRight(name.ToString());
}
} } /// <summary>
/// 读取Excel获取表名
/// </summary>
/// <returns></returns>
private ArrayList ReadExcelTakeTableName()
{
ArrayList arrayList = new ArrayList();
OleDbConnection oleConn = null;
DataTableReader dtReader = null;
try
{
oleConn = new OleDbConnection(EXCEL_CONN);
oleConn.Open();
DataTable dtOle = oleConn.GetSchema("Tables");
dtReader = new DataTableReader(dtOle);
while (dtReader.Read())
{
string tableName = dtReader["Table_Name"].ToString();
tableName = tableName.Substring(0, tableName.Length - 1);
TakeTableName take = new TakeTableName();
arrayList.Add(tableName);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
dtReader.Close();
dtReader.Dispose();
oleConn.Close();
oleConn.Dispose();
}
return arrayList;
} /// <summary>
/// 将EXCEL中的数据显示到DataGrid中
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
private DataTable ImportDataGrid(string tableName)
{
string sql = "select * from [" + tableName + "$]";
DataSet ds = new DataSet();
try
{
OleDbConnection conn = new OleDbConnection(EXCEL_CONN);
OleDbDataAdapter adapter = new OleDbDataAdapter(sql, conn);
conn.Open();
adapter.Fill(ds, "table");
conn.Close();
conn.Dispose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return ds.Tables[0];
} /// <summary>
/// 保存到数据
/// </summary>
/// <param name="tableName"></param>
/// <param name="table"></param>
private void AddDB(string tableName, DataTable table)
{
string sql = "select * from " + tableName;
int columnCount = DBHelper.GetExecuteReader(sql, CommandType.Text).FieldCount;
try
{
foreach (DataRow row in table.Rows)
{
if (CheckIsExists(tableName, row[0].ToString()))
{
sql = "insert into " + tableName + " values("; for (int j = 0; j < columnCount; j++)
{
sql += "'" + row[j] + "',";
}
sql = sql.Remove(sql.Length - 1, 1) + ")";
DBHelper.GetExecuteQuery(sql, CommandType.Text);
}
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
} /// <summary>
/// 验证表名是否正确
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
private bool CheckFormatIsRight(string tableName)
{
string sql = "select * from [" + tableName + "$]";
TakeTableName take = new TakeTableName();
try
{
OleDbConnection conn = new OleDbConnection(EXCEL_CONN);
conn.Open();
OleDbCommand command = new OleDbCommand(sql, conn);
OleDbDataReader reader = command.ExecuteReader();
string message = take.CheckExcelColumns(tableName, reader.FieldCount);
take = null;
reader.Close();
conn.Close(); if (message != string.Empty)
{
TextBlock resultMsg = CreateTextBlock(message, Brushes.Red, FontWeights.Bold, new Thickness(0, 5, 0, 0));
resultPanel.Children.Add(resultMsg);
resultMsg = null;
IsInsert = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return true;
} /// <summary>
/// 验证导入的表单是否存在
/// </summary>
/// <param name="tableName"></param>
/// <param name="Id"></param>
/// <returns></returns>
private bool CheckIsExists(string tableName, string Id)
{
string sql = "PROC_CheckPositionIsExistsById";
System.Data.SqlClient.SqlParameter[] param = new System.Data.SqlClient.SqlParameter[]
{
new System.Data.SqlClient.SqlParameter("@tableName",tableName),
new System.Data.SqlClient.SqlParameter("@id",Id)
};
int count = (int)DBHelper.GetExecuteScalar(sql, CommandType.StoredProcedure, param);
if (count == 0)
return true;
return false;
}
#endregion
}
}