WPF-两份excel文档列自动匹配导入工具-技术&分享
来源：互联网 发布：js屏蔽运营商广告 编辑：程序博客网 时间：2024/06/03 16:21
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 = node.InnerText; if (xmlProblemTitle!="") { if (xmlProblemTitle.Trim().Equals("")) { continue; } if (excelTitle.IndexOf("】") > -1 && excelTitle.IndexOf("【") == 0)//去掉第一个【xxx】的内容 { excelTitle = excelTitle.Substring(excelTitle.IndexOf("】"), excelTitle.Length - excelTitle.IndexOf("】") - 1); } if (xmlProblemTitle.IndexOf("】") > -1 && xmlProblemTitle.IndexOf("【") == 0) { xmlProblemTitle = xmlProblemTitle.Substring(xmlProblemTitle.IndexOf("】"), xmlProblemTitle.Length - xmlProblemTitle.IndexOf("】") - 1); } if (xmlProblemTitle.Trim().Replace(":", "").Replace("：", "").Replace("[", "").Replace("【", "").Replace("】", "").Replace("]", "").Replace("，", "").Replace("。", "") .IndexOf(excelTitle.Trim().Replace(":", "").Replace("：", "").Replace("[", "").Replace("【", "").Replace("】", "").Replace("]", "").Replace("，", "").Replace("。", "")) >= 0 || excelTitle.Trim().Replace(":", "").Replace("：", "").Replace("[", "").Replace("【", "").Replace("】", "").Replace("]", "").Replace("，", "").Replace("。", "") .IndexOf(xmlProblemTitle.Trim().Replace("：", "").Replace(":", "").Replace("[", "").Replace("【", "").Replace("】", "").Replace("]", "").Replace("，", "").Replace("。", "")) >= 0) { bugNo = bugNoValue; break; } } if (xmlBUGTitle!="") { if (xmlBUGTitle.Trim().Equals("")) { continue; } if (excelTitle.IndexOf("】") > -1 && excelTitle.IndexOf("【") == 0) { excelTitle = excelTitle.Substring(excelTitle.IndexOf("】"), excelTitle.Length - excelTitle.IndexOf("】") - 1); } if (xmlBUGTitle.IndexOf("】") > -1 && xmlBUGTitle.IndexOf("【") == 0) { xmlBUGTitle = xmlBUGTitle.Substring(xmlBUGTitle.IndexOf("】"), xmlBUGTitle.Length - xmlBUGTitle.IndexOf("】") - 1); } if (xmlBUGTitle.Trim().Replace(":", "").Replace("：", "").Replace("[", "").Replace("【", "").Replace("】", "").Replace("]", "").Replace("，", "").Replace("。", "") .IndexOf(excelTitle.Trim().Replace(":", "").Replace("：", "").Replace("[", "").Replace("【", "").Replace("】", "").Replace("]", "").Replace("，", "").Replace("。", "")) >= 0 || excelTitle.Trim().Replace(":", "").Replace("：", "").Replace("[", "").Replace("【", "").Replace("】", "").Replace("]", "").Replace("，", "").Replace("。", "") .IndexOf(xmlBUGTitle.Trim().Replace("：", "").Replace(":", "").Replace("[", "").Replace("【", "").Replace("】", "").Replace("]", "").Replace("，", "").Replace("。", "")) >= 0) { bugNo = bugNoValue; break; } } } } catch (Exception ex) { _errorMessage = ex.Message; } return bugNo; } public bool ImportExcelBugNo(XmlDocument doc,string folder) { try { if (!Directory.Exists(folder)) { _errorMessage = "所选目录不存在！请重新选择目录"; return false; } foreach(string file in Directory.GetFiles(folder)) { if(file.Substring(file.LastIndexOf(".")+1,file.Length-file.LastIndexOf(".")-1)=="xlsx"||file.Substring(file.LastIndexOf(".")+1,file.Length-file.LastIndexOf(".")-1)=="xls") { Aspose.Cells.Workbook wk = new Aspose.Cells.Workbook(); //wk.Initialize(); wk.Open(file); Worksheet sht = wk.Worksheets[0];//查看文档的sheet0内容 Cells cells = sht.Cells;//获取sheet0的所有单元格 int titleIndex = -1; int bugNoIndexEdit = -1; int bugNoIndexAdd = -1; string title; if (sht==null) { return false; } int rowCount = cells.MaxDataRow+1; int cellCount = cells.MaxDataColumn + 1; Dictionary<string, int> titleToIndex = new Dictionary<string, int>(); if (rowCount == 0) { continue; } for (int k = 0; k < cellCount; k++) { if (cells[0, k].Value == null) { continue; } else { title = cells[0, k].Value.ToString(); if (title == _importItemColumn || title == "主动修复编号") { if (title == "主动修复编号") { _BugNoColumn = k; } if (!titleToIndex.Keys.Contains(title)) { titleToIndex.Add(title, k); } } } } bool existTitle = false; foreach (var VARIABLE in titleToIndex.Keys) { if (VARIABLE.IndexOf(_importItemColumn) > -1) { existTitle = true; break; } else { continue; } } if(!existTitle) { _errorMessage = "文档" + file + "不包含" + _importItemColumn; continue; } bool existBugNo= false; foreach (var VARIABLE in titleToIndex.Keys) { if (VARIABLE.IndexOf("主动修复编号") > -1) { existBugNo = true; break; } else { continue; } } if(!existBugNo) { _BugNoColumn = cells.MaxDataColumn + 1; cells.InsertColumn(_BugNoColumn); Cell cell = cells[0, _BugNoColumn]; cell.PutValue("主动修复编号"); } for (int j = 1; j < rowCount; j++) { if (cells[j, 0] == null) { continue; } Cell cell = cells[j,titleToIndex[_importItemColumn]]; if (cell == null) { break; } if (cell.Value != string.Empty) { string BugNo = MatchBugNo(cell.ToString()); cell = cells[j, _BugNoColumn]; cell.PutValue(BugNo); } } wk.Save(file, SaveFormat.Xlsx); } } OnPerformStep(this, PerformStepEventArg.SingleStepArg); } catch(Exception ex) { _errorMessage = ex.Message; return false; } return true; } } }} 1 0
- WPF-两份excel文档列自动匹配导入工具-技术&分享
- WPF导入Excel (一)
- excel两列对比
- 技术分享：Oracle PLSQL读取(解析)Excel文档
- WPF之导入导出Excel
- WPF之导入导出Excel
- WPF之导入导出Excel
- Excel自动增长列
- EXCEL 自动匹配数据
- web导入excel文档
- JAVA 导入EXCEL文档
- JAVA 导入EXCEL文档
- SQL Server Export and Import 导出和导入工具 导入EXCEL居然限制为255列
- Excel多列匹配相加
- 技术文档分享
- excel数据自动导入
- excel比较两列不同
- excel两列交叉对比
- 数据结构及算法分析——(有序向量唯一化)笔记一
- VirtualBox虚拟机安装Linux redhat 5.4 64位版本的过程中出现的问题及解决方法
- 软件测试中有关界面测试经验总结
- Codeforces 429 A. Xor-tree
- 在 Linux 下搭建 Java 开发环境
- WPF-两份excel文档列自动匹配导入工具-技术&分享
- TDD的iOS开发初步以及Kiwi使用入门
- 从零单排PAT1006. 换个格式输出整数
- 从普通程序员到高级IT工程师必经之路----了解“高内聚，低耦合，代码复用
- iOS 单元测试
- sublime 去掉java文件行号
- Sqlite3 常用命令介绍
- 测试@代码质量
- 微信QQ的二维码登录原理浅析
