//---------------------------------
public static List <ExcelCellInfo> ToCellList(ExcelCell[,] cellsArray, int minRow, int minCol)
{
if (cellsArray == null || minRow < 1 || minCol < 1)
{
return(null);
}
List <ExcelCellInfo> cells = new List <ExcelCellInfo>();
int numRow = cellsArray.GetLength(0);
int numCol = cellsArray.GetLength(1);
for (int iR = 0; iR < numRow; iR++)
{
for (int iC = 0; iC < numCol; iC++)
{
ExcelCell cell = cellsArray [iR, iC];
if (cell.content != "" || cell.format != "" || cell.formula != "")
{
ExcelCellInfo cellInfo = new ExcelCellInfo(minRow + iR, minCol + iC, cell.content, cell.format, cell.prefix, cell.formula);
cells.Add(cellInfo);
}
}
}
return(cells);
}
public bool IsEqual(ExcelCell other)
{
if (formula != "" || other.formula != "")
{
if (formula != other.formula)
{
return(false);
}
if (format != "" || other.format != "")
{
if (format != other.format)
{
return(false);
}
}
return(true);
}
if (content != "" || other.content != "")
{
if (prefix + content != other.prefix + other.content)
{
return(false);
}
}
if (format != "" || other.format != "")
{
return(format == other.format);
}
else
{
return(true);
}
}
//---------------------------------
public static ExcelCell[,] ToCellArray(List <ExcelCellInfo> cells, out int minRow, out int minCol)
{
if (cells == null || cells.Count == 0)
{
minRow = minCol = 0;
return(null);
}
minRow = minCol = int.MaxValue;
int maxRow, maxCol;
maxRow = maxCol = 0;
foreach (ExcelCellInfo info in cells)
{
if (info.row > maxRow)
{
maxRow = info.row;
}
if (info.col > maxCol)
{
maxCol = info.col;
}
if (info.row < minRow)
{
minRow = info.row;
}
if (info.col < minCol)
{
minCol = info.col;
}
}
int numRow = maxRow - minRow + 1;
int numCol = maxCol - minCol + 1;
ExcelCell[,] CellsArray = new ExcelCell[numRow, numCol];
for (int iR = 0; iR < numRow; iR++)
{
for (int iC = 0; iC < numCol; iC++)
{
CellsArray[iR, iC] = new ExcelCell();
}
}
foreach (ExcelCellInfo info in cells)
{
int iR = info.row - minRow;
int iC = info.col - minCol;
CellsArray[iR, iC].Fill(info.content, info.format, info.prefix, info.formula);
}
return(CellsArray);
}
//-------------------------------------
public static bool CompareListAndArray(List <ExcelCellInfo> dataList, ExcelCell[,] cellsArray, int minRow, int minCol)
{
int numRow = cellsArray.GetLength(0);
int numCol = cellsArray.GetLength(1);
int maxRow = minRow + numRow - 1;
int maxCol = minCol + numCol - 1;
foreach (ExcelCellInfo cellInfo in dataList)
{
int iR = cellInfo.row;
int iC = cellInfo.col;
if (iR < minRow || maxRow < iR || iC < minCol || maxCol < iC)
{
return(false);
}
ExcelCell cell = cellsArray [iR - minRow, iC - minCol];
if (cell.content != cellInfo.content)
{
if (cell.content == "" && cellInfo.content != "" && cell.formula != "" && cell.formula == cellInfo.formula)
{
cell.content = cellInfo.content;
}
else
{
return(false);
}
}
else if (cell.format != cellInfo.format || cell.formula != cellInfo.formula || cell.prefix != cellInfo.prefix)
{
return(false);
}
}
return(true);
}
//-------------------------------------
private static void OnExcelTableChanged(NSNotification notification)
{
ExcelDataReceiver data = notification.Object as ExcelDataReceiver;
if (data.dataList.Count == 0)
{
if (printDebugOutput)
{
Console.Out.WriteLine("No data exist on Excel");
}
CreateNewSession();
return;
}
if (observExcelEmpty != null)
{
NSNotificationCenter.DefaultCenter.RemoveObserver(observExcelEmpty);
}
observExcelEmpty = null;
if (takeFromExcel) // Session data is invalid. Compare Excel data and Input data
{
takeFromExcel = false;
if (ExcelCellsArray.CompareListAndArray(data.dataList, CellsArrayInput, minRowInput, minColInput))
{
if (printDebugOutput)
{
Console.Out.WriteLine("Restore closed session");
}
UseInput();
}
else
{
if (printDebugOutput)
{
Console.Out.WriteLine("Excel and input data are different");
}
StopSessionObservers();
ClearSessionData();
DispatchQueue.MainQueue.DispatchAsync(() => {
CreateNewSession();
});
}
return;
}
if (firstRun) // First run after session data setted. Compare it with Excel data
{
timeFirstRespond = DateTime.Now.Ticks;
long elapsedTime = (timeFirstRespond - timeOpenExcel) / TimeSpan.TicksPerMillisecond;
if (printDebugOutput)
{
Console.Out.WriteLine("Session first respond time= {0} sec", elapsedTime / 1000.0);
}
firstRun = false;
if (ExcelCellsArray.CompareListAndArray(data.dataList, CellsArray, minRow, minCol) != true)
{
if (printDebugOutput)
{
Console.Out.WriteLine("Excel communication error");
}
StopSession();
}
return;
}
List <ExcelCellInfo> cellsAdded = new List <ExcelCellInfo> ();
List <ExcelCellInfo> cellsChanged = new List <ExcelCellInfo> ();
int maxRow = minRow + numRow - 1;
int maxCol = minCol + numCol - 1;
foreach (ExcelCellInfo cellInfo in data.dataList)
{
int iR = cellInfo.row;
int iC = cellInfo.col;
if (iR < minRow || maxRow < iR || iC < minCol || maxCol < iC)
{
cellsAdded.Add(cellInfo);
}
else
{
ExcelCell cell = CellsArray [iR - minRow, iC - minCol];
if (cell.content != cellInfo.content ||
cell.formula != cellInfo.formula ||
cell.format != cellInfo.format ||
cell.prefix != cellInfo.prefix)
{
if (cell.content == "" && cell.format == "" && cell.formula == "")
{
cellsAdded.Add(cellInfo);
}
else
{
cellsChanged.Add(cellInfo);
cell.content = cellInfo.content;
cell.formula = cellInfo.formula;
cell.format = cellInfo.format;
cell.prefix = cellInfo.prefix;
}
}
}
}
CellsList = null;
CellsList = ExcelCellsArray.ToCellList(CellsArray, minRow, minCol);
CellsArray = null;
if (cellsAdded.Count > 0)
{
foreach (ExcelCellInfo cellInfo in cellsAdded)
{
CellsList.Add(cellInfo);
}
}
CellsList.Sort();
CellsArray = ExcelCellsArray.RebuildSessionArray(CellsList, out minRow, out minCol, out numRow, out numCol);
if (printDebugOutput && cellsAdded.Count + cellsChanged.Count > 0)
{
System.Console.Out.WriteLine("------beg----------");
foreach (ExcelCellInfo cellInfo in cellsAdded)
{
Console.Out.Write("Added : ");
cellInfo.Print();
}
foreach (ExcelCellInfo cellInfo in cellsChanged)
{
Console.Out.Write("Changed: ");
cellInfo.Print();
}
Console.Out.WriteLine("------end---------- total {0} cells\n", cellsAdded.Count + cellsChanged.Count);
}
data.Dispose();
}
