#region
|
|
using System;
|
using System.Collections.Generic;
|
using System.ComponentModel;
|
using System.Data;
|
using System.IO;
|
using System.Windows.Forms;
|
using CSFrameworkV5.Common;
|
using CSFrameworkV5.Library.CommonClass;
|
using CSFrameworkV5.Library.CommonForms;
|
using DevExpress.Data;
|
using DevExpress.XtraEditors;
|
using DevExpress.XtraEditors.Controls;
|
using ProgressBar = System.Windows.Forms.ProgressBar;
|
|
#endregion
|
|
namespace CSFrameworkV5.Library
|
{
|
/// <summary>
|
/// 从Excel文件导入数据通用接口
|
/// </summary>
|
public partial class frmImportExcel : XtraForm
|
{
|
/// <summary>
|
/// 数据源与目标表字段映射关系
|
/// </summary>
|
private List<FieldMapping> _FieldMapping;
|
|
/// <summary>
|
/// 连接数据源通用接口
|
/// </summary>
|
private IImporterSource _MyConn;
|
|
/// <summary>
|
/// 导入数据到目的地接口
|
/// </summary>
|
private IImporterTarget _Target;
|
|
/// <summary>
|
/// 私有构造器
|
/// </summary>
|
private frmImportExcel()
|
{
|
InitializeComponent();
|
}
|
|
private void btnFieldMapping_Click(object sender, EventArgs e)
|
{
|
if (gcPreview.DataSource != null)
|
{
|
var source = gcPreview.DataSource as DataTable;
|
_FieldMapping =
|
frmImporterFieldMapping.Execute(source, _Target,
|
_FieldMapping);
|
}
|
else
|
{
|
Msg.Warning("请先查询数据！");
|
}
|
}
|
|
private void btnImport_Click(object sender, EventArgs e)
|
{
|
if (_FieldMapping != null && GetMappingCount(_FieldMapping) > 0)
|
{
|
if (!Msg.AskQuestion("确定要导入数据吗?")) return;
|
|
//导入前先清空原有数据
|
if (chkEmptyData.Checked) _Target.ClearData();
|
|
try
|
{
|
CCursor.ShowWaitCursor();
|
btnImport.Enabled = false;
|
Application.DoEvents();
|
var source = gcPreview.DataSource as DataTable;
|
IProgressBar progess =
|
new ImportProgress(progressBar1, label1);
|
var list = _Target.Import(source, _FieldMapping, progess);
|
CCursor.ShowDefaultCursor();
|
frmOperateReport.ShowReport("导入数据", list);
|
Close();
|
}
|
finally
|
{
|
CCursor.ShowDefaultCursor();
|
btnImport.Enabled = true;
|
}
|
}
|
else
|
{
|
Msg.Warning("请先建立字段映射关系!");
|
btnFieldMapping_Click(btnFieldMapping, new EventArgs());
|
}
|
}
|
|
private void btnPreview_Click(object sender, EventArgs e)
|
{
|
if (!_MyConn.IsConnected)
|
{
|
Msg.Warning("没有选择Excel文件或者读取文件失败！");
|
txtFile.Focus();
|
return;
|
}
|
|
if (txtTables.Text != "")
|
{
|
var dt = _MyConn.GetData(txtTables.Text);
|
gvPreview.Columns.Clear();
|
gcPreview.DataSource = null;
|
gcPreview.DataSource = dt;
|
gvPreview.VisibleColumns[0].SummaryItem.SummaryType =
|
SummaryItemType.Count;
|
}
|
else
|
{
|
txtTables.Focus();
|
}
|
}
|
|
private void btnTestConnection_Click(object sender, EventArgs e)
|
{
|
//测试连接
|
string msg;
|
|
if (TestConnection(out msg))
|
{
|
var tables = _MyConn.GetTableNameList(); //取数据源的资料表
|
txtTables.Properties.Items.Clear();
|
txtTables.Properties.Items.AddRange(tables);
|
MessageBox.Show("连接数据库成功！", "提示", MessageBoxButtons.OK,
|
MessageBoxIcon.Information);
|
txtTables.Focus();
|
txtTables.ShowPopup();
|
}
|
else
|
{
|
MessageBox.Show(msg, "错误", MessageBoxButtons.OK,
|
MessageBoxIcon.Error);
|
}
|
}
|
|
private void DoOpenFile()
|
{
|
var dlg = new OpenFileDialog();
|
dlg.Filter =
|
"Microsoft Excel 97~2003 Files|*.xls|Microsoft Excel 2007~2010 Files|*.xlsx";
|
if (dlg.ShowDialog() == DialogResult.OK)
|
txtFile.Text = dlg.FileName;
|
}
|
|
/// <summary>
|
/// 执行向导的统一入口
|
/// </summary>
|
/// <param name="target">数据目地的</param>
|
public static void ExecuteImporter(IImporterTarget target,
|
List<FieldMapping> fieldMapping)
|
{
|
var form = new frmImportExcel();
|
form._Target = target;
|
form._FieldMapping = fieldMapping;
|
form.ShowDialog();
|
}
|
|
/// <summary>
|
/// 执行向导的统一入口
|
/// </summary>
|
/// <param name="target">数据目地的</param>
|
public static void ExecuteImporter(IImporterTarget target)
|
{
|
var form = new frmImportExcel();
|
form._Target = target;
|
form.ShowDialog();
|
}
|
|
private void frmImportExcel_Load(object sender, EventArgs e)
|
{
|
//
|
}
|
|
private int GetMappingCount(List<FieldMapping> fieldMapping)
|
{
|
var counter = 0;
|
foreach (var fm in fieldMapping)
|
if (fm.IsValid)
|
counter++;
|
|
return counter;
|
}
|
|
public bool TestConnection(out string msg)
|
{
|
msg = "";
|
if (txtFile.Text == "")
|
{
|
msg = "请选择文件！";
|
return false;
|
}
|
|
//Excel数据源实例，如改为SQL导入，在此建立SQL数据源实例
|
_MyConn = new ImportDataFromExcel(txtFile.Text);
|
var success = _MyConn.TestConnection(out msg);
|
return success;
|
}
|
|
private void txtFile_ButtonClick(object sender,
|
ButtonPressedEventArgs e)
|
{
|
DoOpenFile();
|
if (txtFile.Text.Trim() != "")
|
btnTestConnection_Click(btnTestConnection, new EventArgs());
|
}
|
|
private void txtFile_Validating(object sender, CancelEventArgs e)
|
{
|
//检查文件
|
|
if (txtFile.Text.Trim() == "") return;
|
|
//防止路径遍历
|
//var fileName = txtFile.Text.Replace("..", "").Replace(@"\", "").Replace("/", "");
|
|
if (!File.Exists(CodeSafeHelper.GetSafePath(txtFile.Text)))
|
{
|
Msg.Warning("文件不存在！");
|
e.Cancel = true;
|
}
|
|
var ext =
|
TrueFileFormat.GetExtension(
|
CodeSafeHelper.GetSafePath(txtFile.Text));
|
if (ext != FileExtension.XLS)
|
{
|
Msg.Warning("无效的Excel文件！");
|
e.Cancel = true;
|
}
|
}
|
}
|
|
/// <summary>
|
/// 进度条
|
/// </summary>
|
public class ImportProgress : IProgressBar
|
{
|
private Label _label;
|
private ProgressBar _progress;
|
|
public ImportProgress(ProgressBar progress, Label label)
|
{
|
_progress = progress;
|
_label = label;
|
Reset();
|
}
|
|
public int MaxValue
|
{
|
get => _progress.Maximum;
|
set => _progress.Maximum = value;
|
}
|
|
public int MinValue
|
{
|
get => _progress.Minimum;
|
set => _progress.Minimum = value;
|
}
|
|
public int Position
|
{
|
get => _progress.Value;
|
set
|
{
|
_progress.Value = value;
|
_progress.Invalidate();
|
|
if (_progress.Value >= _progress.Maximum)
|
_label.Text = string.Format("{0} / {1}，操作完成.",
|
_progress.Value, _progress.Maximum);
|
else
|
_label.Text = string.Format("{0} / {1}，正在操作，请稍候...",
|
_progress.Value, _progress.Maximum);
|
|
_label.Invalidate();
|
Application.DoEvents();
|
}
|
}
|
|
public void Reset()
|
{
|
_progress.Maximum = MaxValue;
|
_progress.Minimum = MinValue;
|
_progress.Step = 1;
|
_progress.Value = 0;
|
_progress.Invalidate();
|
_label.Text = "进度条.";
|
_label.Invalidate();
|
}
|
}
|
}
|
