// filename contains absolute path
public ExcelResolver(string[] filenameArray, double rangeParam)
{
rangeDelta = rangeParam;
// get data from files
inputResult = ReadByObjectLibrary(filenameArray);
if (!inputResult)
{
// input error
MessageBox.Show("source Excel file has format error!!");
return;
}
// merge all fileContainer
resultFileContainer = fileContainers[0];
for (long fileIndex = 1; fileIndex < filenameArray.Length; fileIndex++)
{
resultFileContainer = resultFileContainer.MergeFileContainer(fileContainers[fileIndex], rangeDelta);
fileContainers[fileIndex] = null;
GC.Collect();
}
// need to output the result in two style: detail and simple
long resultCount = resultFileContainer.recordList.Count;
Console.WriteLine(resultCount);
SaveByObjectLibrary();
MessageBox.Show("Excel file created!!");
}
private SingleFileContainer ReadByObjectLibrary(string path)
{
string tempPathname = path;
SingleFileContainer tempFileContainer = new SingleFileContainer();
string tempTypename = "";
string[] headerArray;
long primaryIndex = -3;
long secondIndex = -3;
long targetIndex = -3;
string tempValue;
long tempRowIndex;
long tempColumnIndex;
long tempRowCount = 0;
long tempColumnCount = 0;
Microsoft.Office.Interop.Excel.Application xlApp;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
Microsoft.Office.Interop.Excel.Range range;
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlWorkBook = xlApp.Workbooks.Open(tempPathname, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
range = xlWorkSheet.UsedRange;
tempRowCount = range.Rows.Count;
tempColumnCount = range.Columns.Count;
if (tempRowCount == 0 || tempColumnCount == 0)
{
// no data
return(null);
}
// get header row first
tempTypename = (range.Cells[1, 1] as Microsoft.Office.Interop.Excel.Range).Value2.ToString();
headerArray = new string[tempColumnCount - 1];
for (tempColumnIndex = 2; tempColumnIndex <= tempColumnCount; tempColumnIndex++)
{
if ((range.Cells[1, tempColumnIndex] as Microsoft.Office.Interop.Excel.Range).Value2 == null)
{
tempValue = SingleFileContainer.DEFAULT_VALUE;
}
else
{
tempValue = (range.Cells[1, tempColumnIndex] as Microsoft.Office.Interop.Excel.Range).Value2.ToString();
}
//tempValue = (range.Cells[1, tempColumnIndex] as Microsoft.Office.Interop.Excel.Range).Value2.ToString();
headerArray[tempColumnIndex - 2] = tempValue;
//Console.WriteLine(headerArray[headerIndex]);
if (tempValue == primaryKeyName)
{
primaryIndex = tempColumnIndex - 2;
}
else if (tempValue == secondKeyName)
{
secondIndex = tempColumnIndex - 2;
}
else if (tempValue == targetKeyName)
{
targetIndex = tempColumnIndex - 2;
}
}
if ((primaryIndex == -3) || (secondIndex == -3))
{
// not found time column
return(null);
}
// get data
for (tempRowIndex = 2; tempRowIndex <= tempRowCount; tempRowIndex++)
{
SingleDataRecord tempRecord = new SingleDataRecord();
if (targetIndex == -3)
{
// merge all column
for (tempColumnIndex = 2; tempColumnIndex <= tempColumnCount; tempColumnIndex++)
{
if ((range.Cells[tempRowIndex, tempColumnIndex] as Microsoft.Office.Interop.Excel.Range).Value2 == null)
{
tempValue = SingleFileContainer.DEFAULT_VALUE;
}
else
{
tempValue = (range.Cells[tempRowIndex, tempColumnIndex] as Microsoft.Office.Interop.Excel.Range).Value2.ToString();
}
if ((tempColumnIndex - 2) == primaryIndex)
{
tempRecord.ID = tempValue;
}
else if ((tempColumnIndex - 2) == secondIndex)
{
tempRecord.time = double.Parse(tempValue);
}
else
{
if (!tempRecord.AddProperty(headerArray[tempColumnIndex - 2], tempValue))
{
// add property failed
return(null);
}
}
}
}
else
{
// only add Column: primary, second, Aera
tempRecord.ID = (range.Cells[tempRowIndex, primaryIndex + 2] as Microsoft.Office.Interop.Excel.Range).Value2.ToString();
tempRecord.time = double.Parse((range.Cells[tempRowIndex, secondIndex + 2] as Microsoft.Office.Interop.Excel.Range).Value2.ToString());
if (!tempRecord.AddProperty(targetKeyName, (range.Cells[tempRowIndex, targetIndex + 2] as Microsoft.Office.Interop.Excel.Range).Value2.ToString(), tempTypename))
{
// add property failed
return(null);
}
}
tempFileContainer.InsertRecord(tempRecord);
}
xlWorkBook.Close(true, null, null);
xlApp.Quit();
Marshal.ReleaseComObject(xlWorkSheet);
Marshal.ReleaseComObject(xlWorkBook);
Marshal.ReleaseComObject(xlApp);
return(tempFileContainer);
}
public SingleFileContainer MergeFileContainer(SingleFileContainer newContainer, double rangeParam)
{
double rangeDelta = rangeParam;
SingleFileContainer resultContainer = new SingleFileContainer();
for (int outerIndex = 0; outerIndex < this.recordList.Count; outerIndex++)
{
SingleDataRecord tempOuterRecord = (SingleDataRecord)recordList[outerIndex];
if (tempOuterRecord.isMerged)
{
// has merged
continue;
}
//bool isMatch = false;
for (int innerIndex = 0; innerIndex < newContainer.recordList.Count; innerIndex++)
{
// compare the ID first, then compare the time
SingleDataRecord tempInnerRecord = (SingleDataRecord)newContainer.recordList[innerIndex];
if (tempInnerRecord.isMerged)
{
// has merged
continue;
}
if ((tempOuterRecord.ID == tempInnerRecord.ID) && (Math.Abs(tempOuterRecord.time - tempInnerRecord.time) <= rangeDelta))
{
double averageTime = (tempOuterRecord.time + tempInnerRecord.time) / 2;
SingleDataRecord tempResultRecord = new SingleDataRecord(tempOuterRecord.ID, averageTime);
// copy other key-value from inner and outter
for (int keyIndex = 0; keyIndex < tempOuterRecord.keyArray.Count; keyIndex++)
{
string tempKey = (string)tempOuterRecord.keyArray[keyIndex];
string tempValue = tempOuterRecord.propertyMap[tempKey];
if (!tempResultRecord.AddProperty(tempKey, tempValue))
{
// add property failed
return(null);
}
}
for (int keyIndex = 0; keyIndex < tempInnerRecord.keyArray.Count; keyIndex++)
{
string tempKey = (string)tempInnerRecord.keyArray[keyIndex];
string tempValue = tempInnerRecord.propertyMap[tempKey];
if (!tempResultRecord.AddProperty(tempKey, tempValue))
{
// add property failed
return(null);
}
}
// add to result, remove previous data
resultContainer.InsertRecord(tempResultRecord);
((SingleDataRecord)this.recordList[outerIndex]).isMerged = true;
((SingleDataRecord)newContainer.recordList[innerIndex]).isMerged = true;
break;
}
}
}
// deal with the rest this data
for (int recordIndex = 0; recordIndex < this.recordList.Count; recordIndex++)
{
SingleDataRecord rawDataRecord = (SingleDataRecord)this.recordList[recordIndex];
if (rawDataRecord.isMerged)
{
// has merged
continue;
}
SingleDataRecord tempResultRecord = new SingleDataRecord(rawDataRecord.ID, rawDataRecord.time);
for (int newIndex = 0; newIndex < rawDataRecord.keyArray.Count; newIndex++)
{
string tempKey = (string)rawDataRecord.keyArray[newIndex];
string tempValue = rawDataRecord.propertyMap[tempKey];
if (!tempResultRecord.AddProperty(tempKey, tempValue))
{
// add property failed
return(null);
}
}
for (int newIndex = 0; newIndex < newContainer.keyArray.Count; newIndex++)
{
string tempKey = (string)newContainer.keyArray[newIndex];
string tempValue = DEFAULT_VALUE;
if (!tempResultRecord.AddProperty(tempKey, tempValue))
{
// add property failed
return(null);
}
}
resultContainer.InsertRecord(tempResultRecord);
rawDataRecord.isMerged = true;
}
// deal with the rest new data
for (int recordIndex = 0; recordIndex < newContainer.recordList.Count; recordIndex++)
{
SingleDataRecord rawDataRecord = (SingleDataRecord)newContainer.recordList[recordIndex];
if (rawDataRecord.isMerged)
{
// has merged
continue;
}
SingleDataRecord tempResultRecord = new SingleDataRecord(rawDataRecord.ID, rawDataRecord.time);
for (int newIndex = 0; newIndex < rawDataRecord.keyArray.Count; newIndex++)
{
string tempKey = (string)rawDataRecord.keyArray[newIndex];
string tempValue = rawDataRecord.propertyMap[tempKey];
if (!tempResultRecord.AddProperty(tempKey, tempValue))
{
// add property failed
return(null);
}
}
for (int newIndex = 0; newIndex < this.keyArray.Count; newIndex++)
{
string tempKey = (string)this.keyArray[newIndex];
string tempValue = DEFAULT_VALUE;
if (!tempResultRecord.AddProperty(tempKey, tempValue))
{
// add property failed
return(null);
}
}
resultContainer.InsertRecord(tempResultRecord);
rawDataRecord.isMerged = true;
}
for (int i = 0; i < this.recordList.Count; i++)
{
if (!((SingleDataRecord)this.recordList[i]).isMerged)
{
Console.WriteLine("Error");
}
}
for (int i = 0; i < newContainer.recordList.Count; i++)
{
if (!((SingleDataRecord)newContainer.recordList[i]).isMerged)
{
Console.WriteLine("Error");
}
}
return(resultContainer);
}