首页 > 代码库 > WPF-两份excel文档列自动匹配导入工具-技术&分享
WPF-两份excel文档列自动匹配导入工具-技术&分享
WPF-两份excel文档列自动匹配导入工具-技术&分享
A文档中包含两列x，y（x与y对应）；B文档包含一列y，需要将A文档的y匹配B文档的y，将A文档的x内容匹配到B文档中，与B文档中的y列对应。
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Windows;
using System.Windows.Forms;
using Mysoft.Common.Multithread;
using System.Xml;
using Aspose.Cells;
using NPOI.SS.UserModel;
namespace 导入BUG编号_Excel2013
{
/// <summary>
/// MainWindow.xaml 的交互逻辑
/// </summary>
public partial class MainWindow : Window
{
public MainWindow()
{
InitializeComponent();
}
private void btnOpenFile1_Click(object sender, RoutedEventArgs e)
{
System.Windows.Forms.OpenFileDialog openFile = new System.Windows.Forms.OpenFileDialog();
openFile.Filter = "*.xlsx|*.xlsx";
openFile.Title = "选择标准版路径";
openFile.ShowDialog();
txtBaseExcelPath.Text = openFile.FileName;
}
private void btnOpenFile2_Click(object sender, RoutedEventArgs e)
{
FolderBrowserDialog openFolder = new FolderBrowserDialog();
openFolder.ShowDialog();
txtImportExcelPath.Text = openFolder.SelectedPath;
}
private void btnOutputBugNo_Click(object sender, RoutedEventArgs e)
{
if (txtBaseExcelPath.Text == string.Empty)
{
System.Windows.Forms.MessageBox.Show("请选择含BUG编号的文档！");
System.Windows.Forms.OpenFileDialog openFile = new System.Windows.Forms.OpenFileDialog();
openFile.Filter = "*.xlsx|*.xlsx";
openFile.Title = "选择标准版路径";
openFile.ShowDialog();
return;
}
if (txtOutputItemColumn.Text=="")
{
System.Windows.Forms.MessageBox.Show("请填写主题列名称！");
return;
}
if (txtOutputItemColumn.Text.Split(‘;‘).Length>2)
{
System.Windows.Forms.MessageBox.Show("最多填写两个主题列名称！");
return;
}
if (txtBugNoColumn.Text == "")
{
System.Windows.Forms.MessageBox.Show("请填写编号列名称！");
return;
}
OutputBugNo outputBugNo = new OutputBugNo();
if (outputBugNo.Build(txtBaseExcelPath.Text, txtOutputItemColumn.Text, txtBugNoColumn.Text))
{
System.Windows.Forms.MessageBox.Show("导出BUG编号成功！");
}
}
private void btnImportBugNo_Click(object sender, RoutedEventArgs e)
{
if (txtImportExcelPath.Text == string.Empty)
{
System.Windows.Forms.MessageBox.Show("请选择需导入BUG编号的文件夹！");
FolderBrowserDialog openFolder = new FolderBrowserDialog();
openFolder.ShowDialog();
return;
}
if (txtImportItemColumn.Text=="")
{
System.Windows.Forms.MessageBox.Show("请填写导入文档的主题列名称！");
return;
}
ImportBugNo ImportBugNo = new ImportBugNo();
if (ImportBugNo.Build(txtImportExcelPath.Text,txtImportItemColumn.Text))
{
System.Windows.Forms.MessageBox.Show("导入BUG编号成功！");
}
}
public class OutputBugNo : IBackgroundExecute
{
private string _errorMessage;
private string _outputItemColumn;
private string _BugNoColumn;
public string ErrorMessage
{
get { throw new NotImplementedException(); }
}
private string _filePath;
public event UpdateStepDelegate OnUpdateStep;
public event PerformStepDelegate OnPerformStep;
public bool Exec()
{
try
{
XmlDocument doc = new XmlDocument();
doc.Load(AppDomain.CurrentDomain.BaseDirectory + "BugNo.xml");
doc.SelectSingleNode("//Mysoft.Data").InnerText="";
LoadExcelToXml(doc, _filePath);
doc.Save(AppDomain.CurrentDomain.BaseDirectory + "BugNo.xml");
//OnUpdateStep(this, new UpdateStepEventArg() { StepMaxCount = diffFileNameList.Count, StepInfo = "导出BUG清单" });
////读取冲突清单
//IWorkbook workbookColide;
//using (FileStream fs = new FileStream(_colideFileName, FileMode.Open, FileAccess.Read))
//{
// workbookColide = WorkbookFactory.Create(fs);
//}
//ISheet shtColide = workbookColide.GetSheetAt(0);
}
catch (Exception ex)
{
_errorMessage = ex.Message;
return false;
}
return true;
}
public bool Build(string filePath, string outputItemColumn, string BugNoColumn)
{
try
{
_filePath = filePath;
_outputItemColumn = outputItemColumn;
_BugNoColumn = BugNoColumn;
ProgressRun progressRun = new ProgressRun();
if (!progressRun.Run(this, 1))
{
System.Windows.Forms.MessageBox.Show(_errorMessage);
return false;
}
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message);
}
return true;
}
public void LoadExcelToXml(XmlDocument doc, string fileName)
{
Array Item = _outputItemColumn.Split(‘;‘);
IWorkbook workbook;
using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
{
workbook = WorkbookFactory.Create(fs);
}
ISheet sht = (ISheet)workbook.GetSheetAt(0);
IRow shtRow;
ICell shtCell;
int problemItemIndex = -1;
int bugItemIndex = -1;
int bugNoIndex = -1;
if (sht == null)
{
return;
}
int rowCount = sht.PhysicalNumberOfRows;
shtRow = (IRow)sht.GetRow(0);
for (int k = 0; k < shtRow.PhysicalNumberOfCells; k++)
{
shtCell = (ICell) shtRow.GetCell(k);
if (shtCell == null)
{
continue;
}
if (Item.Length == 2)
{
if (Item.GetValue(0).ToString().IndexOf(shtCell.ToString()) >= 0) //问题主题
{
problemItemIndex = k;
}
if (Item.GetValue(1).ToString().IndexOf(shtCell.ToString()) >= 0) //BUG解决方案标题
{
bugItemIndex = k;
}
}
else if (Item.Length == 1)
{
if (Item.GetValue(0).ToString()==shtCell.ToString()) //问题主题"
{
problemItemIndex = k;
}
}
if (_BugNoColumn==shtCell.ToString()) //主动修复编号"
{
bugNoIndex = k;
}
if (problemItemIndex == -1 || bugNoIndex == -1 || bugItemIndex == -1)
{
continue;
}
}
if (problemItemIndex==-1 && bugItemIndex==-1)
{
_errorMessage = "主题列填写错误";
return;
}
if (bugNoIndex == -1)
{
_errorMessage = "编号列填写错误";
return;
}
for (int j = 1; j < rowCount; j++)
{
string bugItemTitel="";
bool bugItem = false;
shtRow = (IRow)sht.GetRow(j);
if (shtRow == null) { continue; }
if (shtRow.GetCell(problemItemIndex) == null || shtRow.GetCell(bugNoIndex) == null)
{
break;
}
string problemItemTitle = shtRow.GetCell(problemItemIndex).ToString();
string bugNo = shtRow.GetCell(bugNoIndex).ToString();
if (bugItemIndex != -1)
{
if (shtRow.GetCell(bugItemIndex) != null)
{
bugItemTitel = shtRow.GetCell(bugItemIndex).ToString();
bugItem = true;
}
}
if (bugNo != string.Empty)
{
XmlNode bugNoNode = doc.SelectSingleNode("//BugNo[@problemItem=‘" + problemItemTitle + "‘]");
if (bugNoNode == null)
{
XmlElement bugNoElement = doc.CreateElement("BugNo");
bugNoElement.SetAttribute("problemItemTitle", problemItemTitle);
if (bugItem)
{
bugNoElement.SetAttribute("bugItemTitel", bugItemTitel);
}
bugNoElement.InnerText = bugNo;
doc.DocumentElement.AppendChild(bugNoElement);
}
else
{
bugNoNode.InnerText = bugNo;
}
}
}
}
}
public class ImportBugNo : IBackgroundExecute
{
private string _errorMessage;
private string _folder;
private string _importItemColumn;
private int _BugNoColumn;
public string ErrorMessage
{
get { throw new NotImplementedException(); }
}
public event UpdateStepDelegate OnUpdateStep;
public event PerformStepDelegate OnPerformStep;
private XmlDocument _doc;
public bool Exec()
{
try
{
XmlDocument doc = new XmlDocument();
doc.Load(AppDomain.CurrentDomain.BaseDirectory + "BugNo.xml");
_doc = doc;
OnUpdateStep(this, new UpdateStepEventArg() { StepMaxCount = Directory.GetFiles(_folder).Count(), StepInfo = "导入BUG编号" });
if (!ImportExcelBugNo(_doc, _folder))
return false;
}
catch (Exception ex)
{
_errorMessage = ex.Message;
return false;
}
return true;
}
public bool Build(string folder,string importItemColumn)
{
try
{
_folder = folder;
_importItemColumn = importItemColumn;
ProgressRun progressRun = new ProgressRun();
if (!progressRun.Run(this, 1))
{
System.Windows.MessageBox.Show(_errorMessage);
return false;
}
return true;
}
catch (Exception ex)
{
System.Windows.MessageBox.Show(ex.Message);
return false;
}
}
public string MatchBugNo(string excelTitle)//匹配BUG编号
{
string bugNo = "无";
string xmlBUGTitle = "";
string xmlProblemTitle = "";
try
{
if (excelTitle.Trim().Equals(""))
{
return bugNo;
}
XmlNodeList nodelist = _doc.SelectNodes("//Mysoft.Data//BugNo");
foreach (XmlNode node in nodelist)
{
try
{
xmlProblemTitle = node.Attributes["problemItemTitle"].Value;
}
catch (Exception)
{
xmlProblemTitle = "";
}
try
{
xmlBUGTitle = node.Attributes["bugItemTitel"].Value;
}
catch (Exception)
{
xmlBUGTitle = "";
}
string bugNoValue = http://www.mamicode.com/node.InnerText;>
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。