private static void ConfirmEvaluate(MySheet ms, String cellRefText, double expectedValue)
{
ValueEval v = ms.EvaluateCell(cellRefText);
Assert.AreEqual(typeof(NumberEval), v.GetType());
Assert.AreEqual(expectedValue, ((NumberEval)v).NumberValue, 0.0);
}
private static MySheet CreateMediumComplex()
{
MySheet ms = new MySheet();
// plain data in D1:F3
ms.SetCellValue("D1", 12);
ms.SetCellValue("E1", 13);
ms.SetCellValue("D2", 14);
ms.SetCellValue("E2", 15);
ms.SetCellValue("D3", 16);
ms.SetCellValue("E3", 17);
ms.SetCellFormula("C1", "SUM(D1:E2)");
ms.SetCellFormula("C2", "SUM(D2:E3)");
ms.SetCellFormula("C3", "SUM(D3:E4)");
ms.SetCellFormula("B1", "C2-C1");
ms.SetCellFormula("B2", "B3*C1-C2");
ms.SetCellFormula("B3", "2");
ms.SetCellFormula("A1", "MAX(B1:B2)");
ms.SetCellFormula("A2", "MIN(B3,D2:F2)");
ms.SetCellFormula("A3", "B3*C3");
// Clear all the logging from the above initialisation
ms.GetAndClearLog();
ms.ClearAllCachedResultValues();
return(ms);
}
public void TestBlankCellChangedToValueCell()
{
TestCases.CultureShim.SetCurrentCulture("en-US");
MySheet ms = new MySheet();
ms.SetCellFormula("A1", "B1+2.2");
ms.SetCellValue("B1", 1.5);
ms.ClearAllCachedResultValues();
ms.ClearCell("B1");
ms.GetAndClearLog();
ConfirmEvaluate(ms, "A1", 2.2);
ConfirmLog(ms, new String[] {
"start A1 B1+2.2",
"end A1 2.2",
});
ms.SetCellValue("B1", 0.4);
ConfirmLog(ms, new String[] {
"changeFromBlank B1 0.4",
"clear A1",
});
ConfirmEvaluate(ms, "A1", 2.6);
ConfirmLog(ms, new String[] {
"start A1 B1+2.2",
"hit B1 0.4",
"end A1 2.6",
});
}
public void TestBlankCellChangedToValueCell()
{
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US");
MySheet ms = new MySheet();
ms.SetCellFormula("A1", "B1+2.2");
ms.SetCellValue("B1", 1.5);
ms.ClearAllCachedResultValues();
ms.ClearCell("B1");
ms.GetAndClearLog();
ConfirmEvaluate(ms, "A1", 2.2);
ConfirmLog(ms, new String[] {
"start A1 B1+2.2",
"end A1 2.2",
});
ms.SetCellValue("B1", 0.4);
ConfirmLog(ms, new String[] {
"changeFromBlank B1 0.4",
"clear A1",
});
ConfirmEvaluate(ms, "A1", 2.6);
ConfirmLog(ms, new String[] {
"start A1 B1+2.2",
"hit B1 0.4",
"end A1 2.6",
});
}
public void TestMediumComplexWithDependencyChange()
{
// Changing an intermediate formula
MySheet ms = CreateMediumComplex();
ConfirmEvaluate(ms, "A1", 46);
ms.GetAndClearLog();
ms.SetCellFormula("B2", "B3*C2-C3"); // used to be "B3*C1-C2"
ConfirmLog(ms, new String[] {
"Clear B2 46",
"Clear1 A1 46",
});
ConfirmEvaluate(ms, "A1", 91);
ConfirmLog(ms, new String[] {
"start A1 MAX(B1:B2)",
"hit B1 8",
"start B2 B3*C2-C3",
"hit B3 2",
"hit C2 62",
"start C3 SUM(D3:E4)",
"hit D3 16", "hit E3 17",
// "value D4 #BLANK#", "value E4 #BLANK#",
"end C3 33",
"end B2 91",
"end A1 91",
});
//----------------
// Note - From now On the demonstrated POI behaviour is not optimal
//----------------
// Now change a value that should no longer affect B2
ms.SetCellValue("D1", 11);
ConfirmLog(ms, new String[] {
"Clear D1 11",
"Clear1 C1 54",
// note there is no "Clear2 B2 91" here because B2 doesn't depend On C1 anymore
"Clear2 B1 8",
"Clear3 A1 91",
});
ConfirmEvaluate(ms, "B2", 91);
ConfirmLog(ms, new String[] {
"hit B2 91", // further Confirmation that B2 was not Cleared due to changing D1 above
});
// things should be back to normal now
ms.SetCellValue("D1", 11);
ConfirmLog(ms, new String[] { });
ConfirmEvaluate(ms, "B2", 91);
ConfirmLog(ms, new String[] {
"hit B2 91",
});
}
public ActionResult UploadOrder(IEnumerable <HttpPostedFileBase> UploadFile)
{
ViewBag.PageName = "批次上傳";
// 儲存檔案
foreach (var file in UploadFile)
{
if (file == null)
{
ViewBag.Error = "請選擇檔案!";
return(View());
}
if (Path.GetExtension(file.FileName) != ".xlsx")
{
ViewBag.Error = "副檔名錯誤，請下載樣板並上傳資料!";
return(View());
}
MemoryStream ms = new MemoryStream();
file.InputStream.CopyTo(ms);
string FilePath = @"D:\";
file.InputStream.Position = 0;
string FileName = DateTime.Now.ToString("yyyyMMddhhmmssfff") + Path.GetExtension(file.FileName);
file.SaveAs(FilePath + FileName);
ms.Dispose();
ms.Close();
// NPOI讀取
XSSFWorkbook wb;
using (FileStream fs = new FileStream(FilePath + FileName, FileMode.Open, FileAccess.ReadWrite))
{
wb = new XSSFWorkbook(fs);
XSSFSheet MySheet;
MySheet = (XSSFSheet)wb.GetSheetAt(0);
// 迴圈讀每筆資料，從1開始(跳過標題列)
for (int i = 1; i <= MySheet.LastRowNum; i++)
{
XSSFRow Row = (XSSFRow)MySheet.GetRow(i);
// 讀取每欄資料
for (int k = 0; i < Row.Cells.Count; i++)
{
string MyTemp = Row.GetCell(k).ToString();
}
}
}
}
return(View());
}
public void TestSimpleWithDependencyChange()
{
MySheet ms = new MySheet();
ms.SetCellFormula("A1", "INDEX(C1:E1,1,B1)");
ms.SetCellValue("B1", 1);
ms.SetCellValue("C1", 17);
ms.SetCellValue("D1", 18);
ms.SetCellValue("E1", 19);
ms.ClearAllCachedResultValues();
ms.GetAndClearLog();
ConfirmEvaluate(ms, "A1", 17);
ConfirmLog(ms, new String[] {
"start A1 INDEX(C1:E1,1,B1)",
"value B1 1",
"value C1 17",
"end A1 17",
});
ms.SetCellValue("B1", 2);
ms.GetAndClearLog();
ConfirmEvaluate(ms, "A1", 18);
ConfirmLog(ms, new String[] {
"start A1 INDEX(C1:E1,1,B1)",
"hit B1 2",
"value D1 18",
"end A1 18",
});
// change C1. Note - last time A1 Evaluated C1 was not used
ms.SetCellValue("C1", 15);
ms.GetAndClearLog();
ConfirmEvaluate(ms, "A1", 18);
ConfirmLog(ms, new String[] {
"hit A1 18",
});
// but A1 still uses D1, so if it changes...
ms.SetCellValue("D1", 25);
ms.GetAndClearLog();
ConfirmEvaluate(ms, "A1", 25);
ConfirmLog(ms, new String[] {
"start A1 INDEX(C1:E1,1,B1)",
"hit B1 2",
"hit D1 25",
"end A1 25",
});
}
public void TestBlankCells()
{
MySheet ms = new MySheet();
ms.SetCellFormula("A1", "sum(B1:D4,B5:E6)");
ms.SetCellValue("B1", 12);
ms.ClearAllCachedResultValues();
ms.GetAndClearLog();
ConfirmEvaluate(ms, "A1", 12);
ConfirmLog(ms, new String[] {
"start A1 SUM(B1:D4,B5:E6)",
"value B1 12",
"end A1 12",
});
ms.SetCellValue("B6", 2);
ms.GetAndClearLog();
ConfirmEvaluate(ms, "A1", 14);
ConfirmLog(ms, new String[] {
"start A1 SUM(B1:D4,B5:E6)",
"hit B1 12",
"hit B6 2",
"end A1 14",
});
ms.SetCellValue("E4", 2);
ms.GetAndClearLog();
ConfirmEvaluate(ms, "A1", 14);
ConfirmLog(ms, new String[] {
"hit A1 14",
});
ms.SetCellValue("D1", 1);
ms.GetAndClearLog();
ConfirmEvaluate(ms, "A1", 15);
ConfirmLog(ms, new String[] {
"start A1 SUM(B1:D4,B5:E6)",
"hit B1 12",
"hit D1 1",
"hit B6 2",
"end A1 15",
});
}
private static void ConfirmLog(MySheet ms, String[] expectedLog)
{
String[] actualLog = ms.GetAndClearLog();
int endIx = actualLog.Length;
if (endIx != expectedLog.Length)
{
Console.Error.WriteLine("Log Lengths mismatch");
DumpCompare(expectedLog, actualLog);
throw new AssertFailedException("Log Lengths mismatch");
}
for (int i = 0; i < endIx; i++)
{
if (!actualLog[i].Equals(expectedLog[i]))
{
String msg = "Log entry mismatch at index " + i;
Console.Error.WriteLine(msg);
DumpCompare(expectedLog, actualLog);
throw new AssertFailedException(msg);
}
}
}
public void TestRedundantUpdate()
{
MySheet ms = new MySheet();
ms.SetCellValue("B1", 12);
ms.SetCellValue("C1", 13);
ms.SetCellFormula("A1", "B1+C1");
// Evaluate twice to Confirm caching looks OK
ms.EvaluateCell("A1");
ms.GetAndClearLog();
ConfirmEvaluate(ms, "A1", 25);
ConfirmLog(ms, new String[] {
"hit A1 25",
});
// Make redundant update, and check re-evaluation
ms.SetCellValue("B1", 12); // value didn't change
ConfirmLog(ms, new String[] { });
ConfirmEvaluate(ms, "A1", 25);
ConfirmLog(ms, new String[] {
"hit A1 25",
});
ms.SetCellValue("B1", 11); // value changing
ConfirmLog(ms, new String[] {
"Clear B1 11",
"Clear1 A1 25", // expect consuming formula cached result to Get Cleared
});
ConfirmEvaluate(ms, "A1", 24);
ConfirmLog(ms, new String[] {
"start A1 B1+C1",
"hit B1 11",
"hit C1 13",
"end A1 24",
});
}
public void TestMediumComplex()
{
MySheet ms = CreateMediumComplex();
// completely fresh evaluation
ConfirmEvaluate(ms, "A1", 46);
ConfirmLog(ms, new String[] {
"start A1 MAX(B1:B2)",
"start B1 C2-C1",
"start C2 SUM(D2:E3)",
"value D2 14", "value E2 15", "value D3 16", "value E3 17",
"end C2 62",
"start C1 SUM(D1:E2)",
"value D1 12", "value E1 13", "hit D2 14", "hit E2 15",
"end C1 54",
"end B1 8",
"start B2 B3*C1-C2",
"value B3 2",
"hit C1 54",
"hit C2 62",
"end B2 46",
"end A1 46",
});
// simple cache hit - immediate re-evaluation with no changes
ConfirmEvaluate(ms, "A1", 46);
ConfirmLog(ms, new String[] { "hit A1 46", });
// change a low level cell
ms.SetCellValue("D1", 10);
ConfirmLog(ms, new String[] {
"Clear D1 10",
"Clear1 C1 54",
"Clear2 B1 8",
"Clear3 A1 46",
"Clear2 B2 46",
});
ConfirmEvaluate(ms, "A1", 42);
ConfirmLog(ms, new String[] {
"start A1 MAX(B1:B2)",
"start B1 C2-C1",
"hit C2 62",
"start C1 SUM(D1:E2)",
"hit D1 10", "hit E1 13", "hit D2 14", "hit E2 15",
"end C1 52",
"end B1 10",
"start B2 B3*C1-C2",
"hit B3 2",
"hit C1 52",
"hit C2 62",
"end B2 42",
"end A1 42",
});
// Reset and try changing an intermediate value
ms = CreateMediumComplex();
ConfirmEvaluate(ms, "A1", 46);
ms.GetAndClearLog();
ms.SetCellValue("B3", 3); // B3 is in the middle of the dependency tree
ConfirmLog(ms, new String[] {
"Clear B3 3",
"Clear1 B2 46",
"Clear2 A1 46",
});
ConfirmEvaluate(ms, "A1", 100);
ConfirmLog(ms, new String[] {
"start A1 MAX(B1:B2)",
"hit B1 8",
"start B2 B3*C1-C2",
"hit B3 3",
"hit C1 54",
"hit C2 62",
"end B2 100",
"end A1 100",
});
}
