public FunctionOutput <string>[] FastReplace(Excel.Range com, DAG dag, InputSample original, InputSample sample, AST.Address[] outputs, bool replace_original)
{
FunctionOutput <string>[] fo_arr;
if (!_d.TryGetValue(sample, out fo_arr))
{
// replace the COM value
ReplaceExcelRange(com, sample);
// initialize array
fo_arr = new FunctionOutput <string> [outputs.Length];
// grab all outputs
for (var k = 0; k < outputs.Length; k++)
{
// save the output
fo_arr[k] = new FunctionOutput <string>(dag.readCOMValueAtAddress(outputs[k]), sample.GetExcludes());
}
// Add function values to cache
// Don't care about return value
_d.Add(sample, fo_arr);
// restore the COM value
if (replace_original)
{
ReplaceExcelRange(com, original);
}
}
return(fo_arr);
}
public static void OpenFile(string path)
{
try
{
Questions._alllist = new List <Question>();
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(path);
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = 3;
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(path);
// Access first worksheet.
Aspose.Cells.Worksheet ws = wb.Worksheets[0];
/*
* ret = new string[colCount][];
* for (int j = 1; j <= colCount; j++)
* ret[j - 1] = new string[rowCount];
* for (int i = 1; i <= rowCount; i++)
* {
*
* for (int j = 1; j <= colCount; j++)
* {
*
*
*
*
* if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Value2 != null)
* ret[j - 1][i - 1] = xlRange.Cells[i, j].Value2.ToString();
*
* }
* }
*
* int current = 1;
* MessageBox.Show(xlRange.Cells[current, 1].Value2.ToString());
* while (xlRange.Cells[current, 1].Value2.ToString() != null || xlRange.Cells[current, 1].Value2.ToString() != "")
* {
* int formerge = current + 1;
* while (xlWorksheet.Range["A" + current.ToString(), "A" + formerge.ToString()].MergeCells)
* {
* formerge++;
* }
* current = formerge + 1;
* MessageBox.Show(current.ToString());
*
* }
*/
object[] data = null;
Range objRange = null;
for (int row = 1; row < xlWorksheet.UsedRange.Cells.Rows.Count; row++)
{
data = new object[colCount - 1];
bool flag = false;
for (int col = 1; col < colCount; col++)
{
objRange = xlWorksheet.Cells[row, col];
if (objRange.MergeCells)
{
data[col - 1] = Convert.ToString(((Range)objRange.MergeArea[1, 1]).Text).Trim();
}
else
{
data[col - 1] = Convert.ToString(objRange.Text).Trim();
}
if (((Cell)ws.Cells["B" + row.ToString()]).GetStyle().Font.Color.R > 0)
{
flag = true;
}
}
AddRow(data, flag);
}
// MessageBox.Show(current.ToString());
object mergeCells = xlWorksheet.UsedRange.MergeCells;
var containsMergedCells = mergeCells == DBNull.Value || (bool)mergeCells;
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
//rule of thumb for releasing com objects:
// never use two dots, all COM objects must be referenced and released individually
// ex: [somthing].[something].[something] is bad
//release com objects to fully kill excel process from running in the background
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
//close and release
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);
//quit and release
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
}catch
(Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private static Microsoft.Office.Interop.Excel.Range GetSpecifiedRange(Microsoft.Office.Interop.Excel.Worksheet objWs)
{
//gets range of cells with certain format then return the object
object missing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Range pattern = null;
Microsoft.Office.Interop.Excel.Range ssn = null;
Microsoft.Office.Interop.Excel.Range ssnum = null;
Microsoft.Office.Interop.Excel.Range socsecnum = null;
Microsoft.Office.Interop.Excel.Range merger = null;
Excel.Range last = objWs.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
//finds the format of numbers that are ssn
pattern = objWs.get_Range("A1", last).Find("???-??-????", missing,
Microsoft.Office.Interop.Excel.XlFindLookIn.xlValues,
Microsoft.Office.Interop.Excel.XlLookAt.xlPart,
Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows,
Microsoft.Office.Interop.Excel.XlSearchDirection.xlNext, false, missing, missing);
ssn = objWs.get_Range("A1", last).Find("SSN", missing,
Microsoft.Office.Interop.Excel.XlFindLookIn.xlValues,
Microsoft.Office.Interop.Excel.XlLookAt.xlPart,
Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows,
Microsoft.Office.Interop.Excel.XlSearchDirection.xlNext, false, missing, missing);
ssnum = objWs.get_Range("A1", last).Find("ss#", missing,
Microsoft.Office.Interop.Excel.XlFindLookIn.xlValues,
Microsoft.Office.Interop.Excel.XlLookAt.xlPart,
Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows,
Microsoft.Office.Interop.Excel.XlSearchDirection.xlNext, false, missing, missing);
socsecnum = objWs.get_Range("A1", last).Find("social security number", missing,
Microsoft.Office.Interop.Excel.XlFindLookIn.xlValues,
Microsoft.Office.Interop.Excel.XlLookAt.xlPart,
Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows,
Microsoft.Office.Interop.Excel.XlSearchDirection.xlNext, false, missing, missing);
//if it finds a matching pattern it will either add or merge if there is an object that matches the above find.
if (pattern != null)
{
if (merger == null)
{
merger = pattern;
}
else
{
merger.Application.Union(merger, pattern);
}
}
if (ssn != null)
{
if (merger == null)
{
merger = ssn;
}
else
{
merger.Application.Union(merger, ssn);
}
}
if (ssnum != null)
{
if (merger == null)
{
merger = ssnum;
}
else
{
merger.Application.Union(merger, ssnum);
}
}
if (socsecnum != null)
{
if (merger == null)
{
merger = socsecnum;
}
else
{
merger.Application.Union(merger, socsecnum);
}
}
return(merger);
}
