如何比较excel表？ [英] How to compare excel sheets?

问题描述

我有代码来读取excel文件并将它们保存到数据集中。

现在我需要阅读3个excel文件像这样，我需要比较3个Excel文件中的消息ID，以获取常见的消息ID。



我附加了一个图像来显示我的数据如何在数据集中看起来像



将消息ID表转换为列表< t>是否更好？在做之前？

Hi,

I have code to read excel file and save them into dataset.
Now I need to read 3 excel files like this and I need to compare the message ID in the 3 excel files,for the common message ID.

I am attaching a image to show how my data in dataset looks like

Is it better to convert the table of message ID to list<t> before doing it?

using System;

using System.Data;
using System.Data.OleDb;
using System.Collections.Generic;
using System.Text;


namespace Excelcalling1
{
	class Program
	{
		static void Main(string[] args)
		{
			string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
					Data Source=E:\SheetForTest.xls;Integrated Security=SSPI;
					Extended Properties=Microsoft Excel 11.0 Object Library";

			//Create the connection

			System.Data.OleDb.OleDbConnection ExcelConnection =
			new System.Data.OleDb.OleDbConnection
							 (ConnectionString);

			//create a string for the query

			string ExcelQuery;

			//Sheet1 is the sheet name
			//create the query:
			//read column with heading A from the Excel file

			ExcelQuery = "Select A from [Sheet1$]"; // from Sheet1";

			//use "Select * ... " to select the entire sheet
			//create the command

			System.Data.OleDb.OleDbCommand ExcelCommand = new System.Data.OleDb.OleDbCommand(ExcelQuery, ExcelConnection);

			//Open the connection

			//ExcelConnection.Open();
			try
			{
				ExcelConnection.Open();
			}
			catch (OleDbException e)
			{
				string errorMessages = "";

				for (int i = 0; i < e.Errors.Count; i++)
				{
					errorMessages += "Index #" + i + "\n" +
									 "Message: " + e.Errors[i].Message + "\n" +
									 "NativeError: " + e.Errors[i].NativeError + "\n" +
									 "Source: " + e.Errors[i].Source + "\n" +
									 "SQLState: " + e.Errors[i].SQLState + "\n";
				}

				System.Diagnostics.EventLog log = new System.Diagnostics.EventLog();
				log.Source = "My Application";
				log.WriteEntry(errorMessages);
				Console.WriteLine("An exception occurred. Please contact your system administrator.");
			}

			//Create a reader

			System.Data.OleDb.OleDbDataReader ExcelReader;
			ExcelReader = ExcelCommand.ExecuteReader();

			//For each row after the first
			//Message box the values in the first column i.e. column 0

			while (ExcelReader.Read())
			{
				//MessageBox.Show((ExcelReader.GetValue(0)).ToString());
				Console.WriteLine("value retrieved!!");
			}
			ExcelConnection.Close();

			
		}
	}
}

谢谢

John

Thanks
John

推荐答案

; // 来自Sheet1;

// 使用选择* ...选择整个工作表
// 创建命令

System.Data.OleDb.OleDbCommand ExcelCommand = new System.Data.OleDb.OleDbCommand（ExcelQuery，ExcelConnection）;

// 打开连接

< span class =code-comment> // ExcelConnection.Open（）;
尝试
{
ExcelConnection.Open（）;
}
catch （OleDbException e）
{
string errorMessages = ;

for （ int i = 0 ; i < e.Errors.Count; i ++）
{
errorMessages + = 索引＃ + i + \ n +
消息： + e.Errors [i]。消息+ \ n +
NativeError： + e.Errors [i] .NativeError + \ n +
来源： + e.Errors [i] .Source + \ n +
SQLState： + e.Errors [i] .SQLState + \ n;
}

System.Diagnostics.EventLog log = new System.Diagnostics.EventLog（）;
log.Source = 我的应用程序;
log.WriteEntry（errorMessages）;
Console.WriteLine（ 发生异常。请联系您的系统管理员。）;
}

// 创建阅读器

System.Data.OleDb.OleDbDataReader ExcelReader;
ExcelReader = ExcelCommand.ExecuteReader（）;

// 对于第一个
// 消息框第一列中的值即第0列

while （ExcelReader.Read（））
{
// MessageBox.Show（（ExcelReader.GetValue（0））。ToString（））;
Console.WriteLine（ < span class =code-string>检索到的值!!）;
}
ExcelConnection.Close（）;


}
}
}

"; // from Sheet1"; //use "Select * ... " to select the entire sheet //create the command System.Data.OleDb.OleDbCommand ExcelCommand = new System.Data.OleDb.OleDbCommand(ExcelQuery, ExcelConnection); //Open the connection //ExcelConnection.Open(); try { ExcelConnection.Open(); } catch (OleDbException e) { string errorMessages = ""; for (int i = 0; i < e.Errors.Count; i++) { errorMessages += "Index #" + i + "\n" + "Message: " + e.Errors[i].Message + "\n" + "NativeError: " + e.Errors[i].NativeError + "\n" + "Source: " + e.Errors[i].Source + "\n" + "SQLState: " + e.Errors[i].SQLState + "\n"; } System.Diagnostics.EventLog log = new System.Diagnostics.EventLog(); log.Source = "My Application"; log.WriteEntry(errorMessages); Console.WriteLine("An exception occurred. Please contact your system administrator."); } //Create a reader System.Data.OleDb.OleDbDataReader ExcelReader; ExcelReader = ExcelCommand.ExecuteReader(); //For each row after the first //Message box the values in the first column i.e. column 0 while (ExcelReader.Read()) { //MessageBox.Show((ExcelReader.GetValue(0)).ToString()); Console.WriteLine("value retrieved!!"); } ExcelConnection.Close(); } } }

谢谢

John

Thanks
John

要做的步骤：

Steps to do:

1. 定义文件集合
2. 循环遍历文件集合
3. 创建目标 DataTable [ ^ ] object



• 在循环中创建临时DataTable并使用载入 [ ^ ]方法用数据填充DataTable对象
• 最后，使用合并 [ ^ ]将临时DataTable与目标DataTable合并的方法

1. define the collection of files
2. loop through the collection of files
3. create destination DataTable[^] object


• inside a loop create temporary DataTable and use Load[^] method to fullfill DataTable object with data
• finally, use Merge[^] method to merge temporary DataTable with the destination DataTable

这就是全部;）

That's all ;)

这篇关于如何比较excel表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！