public static int CompareExcel(string oldFile, string newFile, string outputFile)
{
Excel.Application objExcel;
Excel.Workbook objWorkbook1, objWorkbook2;
Excel.Worksheet objWorksheet1, objWorksheet2;
objExcel = new Excel.Application();
int WScount1, WScount2;
objExcel.Visible = false;
objExcel.DisplayAlerts = false;
int status = 0;
if (!File.Exists(oldFile))
{
status = 1;
MessageBox.Show("Please select old Excel File.\n " + oldFile + " Not Found");
return status;
}
if (!File.Exists(newFile))
{
status = 2;
MessageBox.Show("Please select new Excel File.\n " + newFile + " Not Found");
return status;
}
if (oldFile == newFile)
{
status = 5;
MessageBox.Show("You Crazy! Please select different files");
return status;
}
oldFile = Path.GetFullPath(oldFile);
newFile = Path.GetFullPath(newFile);
try
{
objWorkbook1 = objExcel.Workbooks.Open(newFile);
objWorkbook2 = objExcel.Workbooks.Open(oldFile);
}
catch (Exception ex)
{
status=100;
MessageBox.Show("This application need MS office to be installed.\nIf Office is already installed please mail below file to us.\n"+outputFile);
using (StreamWriter sw = new StreamWriter(outputFile))
{
sw.WriteLine( DateTime.Now.ToString("MM-dd-yyyy HH:mm ss tt :") + ex);
}
return status;
}
try
{
using (StreamWriter sw = new StreamWriter(outputFile))
{
// Excel.Range range ;
if (mode == 1) sw.WriteLine("<!DOCTYPE HTML><html><head><title>Excel Compare Reslt</title><style>th,td{word-wrap: break-word;max-width: 300px;} body{margin:0px;font:12px verdana;} table,tr,th,td{font:12px verdana;border:1px solid black;border-collapse:collapse;padding:5px;text-align:center} th{color:white;background:green;}</style></head><body><center>");
if (mode == 2)
{
sw.WriteLine("<?xml version=\"1.0\"?>");
sw.WriteLine("<ExcelCompare>");
sw.WriteLine(" <info>");
sw.WriteLine(" <date>" + DateTime.Now.ToString("MM-dd-yyyy HH:mm ss tt :") + "</date>");
sw.WriteLine(" <NewExcelPath>" + newFile + "</NewExcelPath>");
sw.WriteLine(" <OldExcelPath>" + oldFile + "</OldExcelPath>");
sw.WriteLine(" <OutputMode>XML</OutputMode>");
sw.WriteLine(" <OutputPath>" + outputFile + "</OutputPath>");
sw.WriteLine(" </info>");
}
WScount1 = objWorkbook1.Worksheets.Count;
WScount2 = objWorkbook2.Worksheets.Count;
if (!sheetIgnore)
{
if (WScount1 != WScount2)
{
status = 100;
objWorkbook1.Close(true, null, null);
objWorkbook2.Close(true, null, null);
objExcel.Quit();
MessageBox.Show("Similar sheets can only be compared.\nIf you want to compare anyway, change ignore sheet count option in settings");
return status;
}
}
else
{
WScount1 = (WScount1 < WScount2) ? WScount1 : WScount2;
}
for (int i = 1, id = 1; i <= WScount1; i++)
{
string Name = objWorkbook1.Worksheets.get_Item(i).Name;
if (mode == 1) sw.WriteLine("<h1>" + Name + "</h1><table border='1'><tr><th>Location</th><th>Old File <br>" + Path.GetFileName(oldFile) + "</th><th>New File <br>" + Path.GetFileName(newFile) + "</th></tr>");
objWorksheet1 = objWorkbook1.Worksheets.get_Item(i);
objWorksheet2 = objWorkbook2.Worksheets.get_Item(i);
//int rows = (objWorksheet1.Rows.Count >= objWorksheet2.Rows.Count) ? objWorksheet1.Rows.Count : objWorksheet2.Rows.Count;
//int cols = (objWorksheet1.Columns.Count >= objWorksheet2.Columns.Count) ? objWorksheet1.Columns.Count : objWorksheet2.Columns.Count;
//Excel.Range cells = objWorksheet1.get_Range(objWorksheet1.Cells[1, 1], objWorksheet1.Cells[rows,cols]);
//object excelObject1,excelObect2;
//if (objWorksheet1.UsedRange.Rows.Count * objWorksheet1.UsedRange.Columns.Count > objWorksheet1.UsedRange.Rows.Count * objWorksheet1.UsedRange.Columns.Count) { }
foreach (Excel.Range cell in objWorksheet1.UsedRange)
{
string changedCell = cell.get_Address();
bool result = false;
if (trim && ignore) result = !(String.Equals(cell.Text.Trim(), objWorksheet2.get_Range(changedCell).Text.Trim(), StringComparison.OrdinalIgnoreCase));
else if (trim) result = !(String.Equals(cell.Text.Trim(), objWorksheet2.get_Range(changedCell).Text.Trim()));
else if (ignore) result = !(String.Equals(cell.Text, objWorksheet2.get_Range(changedCell).Text, StringComparison.OrdinalIgnoreCase));
else result = !(String.Equals(cell.Text, objWorksheet2.get_Range(changedCell).Text));
if (result)
{
if(highlight) cell.Interior.ColorIndex = 3;
if (mode == 2)
{
sw.WriteLine(" <result id=\"" + id + "\">");
sw.WriteLine(" <newFleSheetName>"+objWorkbook1.Worksheets.get_Item(i).Name+"</newFleSheetName>");
sw.WriteLine(" <oldFleSheetName>"+objWorkbook2.Worksheets.get_Item(i).Name+"</oldFleSheetName>");
sw.WriteLine(" <row>" + cell.Row + "</row>");
sw.WriteLine(" <column>" + cell.Column + "</column>");
sw.WriteLine(" <cell>" + cell.get_Address() + "</cell>");
sw.WriteLine(" <newValue><![CDATA[" + cell.Text + "]]></newValue>");
sw.WriteLine(" <oldValue><![CDATA[" + objWorksheet2.get_Range(changedCell).Text + "]]></oldValue>");
sw.WriteLine(" </result>");
id++;
}
if (mode == 1) sw.WriteLine("<tr><td>" + changedCell + "</td><td>" + cell.Text + "</td><td>" + objWorksheet2.get_Range(changedCell).Text + "</td></tr>");
}
//else if(highlight)
//{
// cell.Interior.ColorIndex = 0;
//}
}
if (mode == 1) sw.WriteLine("</table>");
}
if (mode == 1) sw.WriteLine("</cener></body></html>");
if (mode == 2) sw.WriteLine("</ExcelCompare>");
if (highlight)
{
objWorkbook1.Save();
objWorkbook2.Save();
}
else
{
objWorkbook1.Close(true, null, null);
objWorkbook2.Close(true, null, null);
}
objExcel.Quit();
}
}
catch (System.IO.IOException Ex)
{
status = 55;
//string exception = "";
//Match m = Regex.Match(Ex.ToString(), "^(.*)", RegexOptions.Multiline);
//if (m.Success)
// exception = m.Groups[0].Value;
//Console.WriteLine("IO Exceception." + exception);
MessageBox.Show("IO Exceception." + Ex.Message);
objExcel.Quit();
}
catch (Exception Ex)
{
status = 99;
//string exception = "";
//Match m = Regex.Match(Ex.ToString(), "^(.*)", RegexOptions.Multiline);
//if (m.Success)
// exception = m.Groups[0].Value;
//MessageBox.Show("Unknow Exceception." + exception);
MessageBox.Show("Unknow Exceception." + Ex.Message);
objExcel.Quit();
}
objExcel.Quit();
return status;
}
Excel对比代码(项目中的一部分)
最新推荐文章于 2023-09-09 20:19:00 发布