/// <summary>
///
/// </summary>
/// <param name="excelWorkbook"></param>
/// <param name="dataWorksheet"></param>
/// <param name="logWorksheet"></param>
/// <param name="bmStatus"></param>
/// <returns></returns>
private static bool UpdateExcelWithZeroData(Worksheet dataWorksheet,
Worksheet logWorksheet,
IBIGMatrixStatus bmStatus)
{
int numDays = GetNumberOfDays(1, bmStatus);
int outputRowStart = 6;
int outputRowEnd = (numDays*6) + 5;
string xlFile = Globals.ThisWorkbook.FullName;
for (int sr = 1; sr <= 12; ++sr)
{
int numDaysSR = GetNumberOfDays(sr, bmStatus);
if (numDaysSR != numDays)
{
bmStatus.log("Error: SR" + sr.ToString() + " num days=" + numDaysSR +
" SR1 num days=" + numDays.ToString());
return false;
}
FileInfo fi = new FileInfo(xlFile);
string inFile = xlFile.Replace(fi.Extension, "") + "_SR" + sr.ToString() + ".in";
string outFile = xlFile.Replace(fi.Extension, "") + "_SR" + sr.ToString() + ".out";
string logFile = xlFile.Replace(fi.Extension, "") + "_SR" + sr.ToString() + ".log";
if (File.Exists(inFile)) File.Delete(inFile);
if (File.Exists(outFile)) File.Delete(outFile);
if (File.Exists(logFile)) File.Delete(logFile);
string status = "Zeroing out data";
string runTime = DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToShortTimeString();
int outputCol = 6 + ((sr - 1) * 2);
if( dataWorksheet != null ){
Microsoft.Office.Interop.Excel.Range range = dataWorksheet.get_Range(dataWorksheet.Cells[outputRowStart + 1, outputCol + 1], dataWorksheet.Cells[outputRowEnd + 1, outputCol + 1]);
range.Formula = "0";
}
if (logWorksheet != null)
{
Microsoft.Office.Interop.Excel.Range range = logWorksheet.get_Range(logWorksheet.Cells[sr + 1, 5], logWorksheet.Cells[sr + 1, 5]);
range.Formula = status;
range = logWorksheet.get_Range(logWorksheet.Cells[sr + 1, 4], logWorksheet.Cells[sr + 1, 4]);
range.Formula = runTime;
}
}
bmStatus.logInfo("zeroed out data for " + Globals.ThisWorkbook.Name + " num days=" +
numDays.ToString());
GC.Collect();
return true;
}
/// <summary>
/// Given an input file of type .in, will execute the j349 model to produce .out file of the same name.
/// </summary>
/// <param name="inFile"></param>
/// <param name="outFile"></param>
/// <returns></returns>
public static bool Simulate(string inFile, string outFile,IBIGMatrixStatus bmStatus)
{
System.DateTime start = System.DateTime.Now;
// inFile must exist, or we have an error
if (!File.Exists(inFile))
{
_lastError = "specified input file " + inFile + " does not exists.";
bmStatus.log(_lastError);
return false;
}
//bmStatus.log("debug: begin simulation");
// execute the j349 program specifying the inFile as input and outFile as output
Process process = new Process();
process.StartInfo.UseShellExecute = false;
process.StartInfo.RedirectStandardInput = true;
process.StartInfo.RedirectStandardOutput = true;
process.StartInfo.RedirectStandardError = true;
process.StartInfo.CreateNoWindow = true;
process.StartInfo.FileName = hydro.getj349ExecutableName();
FileInfo efi = new FileInfo(hydro.getj349ExecutableName());
process.StartInfo.WorkingDirectory = efi.DirectoryName;
if (!File.Exists(efi.FullName))
{
_lastError = "Error: j349 executable file: " + hydro.getj349ExecutableName() + " does not exist";
bmStatus.log("Error: j349 executable file: " + hydro.getj349ExecutableName() + " does not exist");
return false;
}
bmStatus.logInfo(" [j349 file: " + hydro.getj349ExecutableName() +"]");
// create relative filenames
string filenames = hydro.getBinDirectory() + @"\filenames";
if (File.Exists(filenames)) File.Delete(filenames);
string relIn = "tmp.in";
string relOut = "tmp.out";
if (File.Exists(hydro.getBinDirectory() + "\\tmp.in")) File.Delete(hydro.getBinDirectory() + "\\tmp.in");
if (File.Exists(hydro.getBinDirectory() + "\\tmp.out")) File.Delete(hydro.getBinDirectory() + "\\tmp.out");
File.Copy(inFile, hydro.getBinDirectory() + "\\tmp.in");
//string relIn = inFile.Replace(hydro.getDataDirectory(), @"..\data");
//string relOut = outFile.Replace(hydro.getDataDirectory(), @"..\data");
using (StreamWriter fsw = new StreamWriter(filenames))
{
fsw.WriteLine(relIn);
fsw.WriteLine(relOut);
}
bmStatus.logInfo(" starting fortran model");
process.Start();
process.WaitForExit();
//Notice that the standard output and standard error have both been redirected.
// There are two StreamReaders in the Process class that can be used to read the output:
// Process.StandardOutput and Process.StandardError. Often, the output is not read until
// after the process has finished, as in the following:
string output = process.StandardOutput.ReadToEnd();
string error = process.StandardError.ReadToEnd();
if (File.Exists(outFile)) File.Delete(outFile);
File.Copy(hydro.getBinDirectory() + "\\tmp.out", outFile);
File.Delete(hydro.getBinDirectory() + "\\tmp.out");
FileInfo fi = new FileInfo(outFile);
string logFilename = outFile.Replace(fi.Extension,".log");
using (StreamWriter sw = new StreamWriter(logFilename))
{
sw.WriteLine("output:");
sw.WriteLine(output);
sw.WriteLine("error:");
sw.WriteLine(error);
}
// If the .out file is empty, delete the relOut and logFilename
FileInfo ofi = new FileInfo(outFile);
if (ofi.Length == 0)
{
//File.Delete(inFile);
//File.Delete(outFile);
//File.Delete(logFilename);
_lastError = "specified output file " + outFile + " is empty.";
bmStatus.log(_lastError);
return false;
}
System.TimeSpan ts = System.DateTime.Now - start;
bmStatus.logInfo("debug: Simulation completed in " + ts.Seconds.ToString() + " secs");
//bmStatus.addInfo(" Simulation completed in " + ts.Seconds.ToString() + " secs");
return true;
}
private static bool UpdateExcelWithSubreachData(Worksheet j349Worksheet,
Worksheet j349LogWorksheet,
int subreach, string inFile,
string outFile, string status,
float[] outDataoutData,int nDays,
IBIGMatrixStatus bmStatus)
{
string runTime = DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToShortTimeString();
// data size 438
int outputRowStart = 6;
int outputRowEnd = nDays*6 + 5;
int outputCol = 6 + ((subreach - 1) * 2);
string[,] sValues = new string[nDays*6, 1];
float[,] fValues = new float[nDays * 6, 1];
int ri = 0;
try
{
for (int r = outputRowStart; r <= outputRowEnd; ++r)
{
ri = r - outputRowStart;
if (outDataoutData == null)
{
sValues[ri, 0] = "0";
fValues[ri, 0] = 0.0f;
}
else
{
sValues[ri, 0] = outDataoutData[ri].ToString();
fValues[ri, 0] = outDataoutData[ri];
}
}
}
catch (Exception e)
{
Debug.WriteLine(e.ToString());
}
// Log info
string[,] lsValues = new string[1, 4];
FileInfo fi = new FileInfo(inFile);
lsValues[0, 0] = fi.Name;
fi = new FileInfo(outFile);
lsValues[0, 1] = fi.Name; ;
lsValues[0, 2] = runTime;
lsValues[0, 3] = status;
// extract range from j349Worksheet
try
{
Microsoft.Office.Interop.Excel.Range range = j349Worksheet.get_Range(j349Worksheet.Cells[outputRowStart + 1, outputCol + 1], j349Worksheet.Cells[outputRowEnd + 1, outputCol + 1]);
range.Value2 = fValues;
}
catch (Exception e)
{
bmStatus.log("Error: unable to update excel worksheet with results of SR" +
subreach.ToString());
bmStatus.logInfo(e.ToString());
return false;
}
// extract range from j349LogWorksheet
try
{
Microsoft.Office.Interop.Excel.Range range = j349LogWorksheet.get_Range(j349LogWorksheet.Cells[subreach + 1, 2], j349LogWorksheet.Cells[subreach + 1, 5]);
range.Value2 = lsValues;
}
catch (Exception e)
{
bmStatus.log("Error: unable to update excel worksheet with results of SR" +
subreach.ToString());
bmStatus.logInfo(e.ToString());
return false;
}
//GC.Collect();
bmStatus.logInfo("SR" + subreach.ToString() + " updated " +
j349Worksheet.Name + " with model output.");
return true;
}
private static bool GenerateInFile(Worksheet j349Worksheet,
int subreach,
string inFilename,
IBIGMatrixStatus bmStatus)
{
System.DateTime start = System.DateTime.Now;
// Extract the remaining input data as a 2D Array
Array data2 = null;
try
{
data2 = ExtractInputParameters(subreach,bmStatus);
}
catch (Exception e)
{
Debug.WriteLine("Exception throw while extracting input parameters for sr" + subreach.ToString());
Debug.WriteLine(e.ToString());
bmStatus.log("Exception throw while extracting input parameters for sr" + subreach.ToString());
bmStatus.log(e.ToString());
return false;
}
// Extract the number of days from the data2 array row 5, col 3
string sDays = (string)(data2.GetValue(3, 2));
int nDays = System.Convert.ToInt32(sDays);
_nDays = nDays;
// Extract the input for current subreach from the Excel data
float[] inData = null;
try
{
inData = ExtractInputHydrograph(j349Worksheet, subreach, nDays,bmStatus);
}
catch (Exception e)
{
Debug.WriteLine("Exception throw while extracting input hydrograph for sr" + subreach.ToString());
Debug.WriteLine(e.ToString());
bmStatus.logInfo("Exception throw while extracting input hydrograph for sr" + subreach.ToString());
bmStatus.logInfo(e.ToString());
}
if (inData == null)
{
_lastError = "Null input hydrograph (could be all zeroes)";
bmStatus.log(_lastError);
return false;
}
// Create and assemble the input file
using (StreamWriter sw = new StreamWriter(inFilename))
{
for (int r = data2.GetLowerBound(0); r <= data2.GetUpperBound(0); ++r)
{
StringCollection sc = new StringCollection();
bool hydrograph = false;
for (int c = data2.GetLowerBound(1); c <= data2.GetUpperBound(1); ++c)
{
string sval = data2.GetValue(r, c).ToString();
if(sval.StartsWith("132.26")){
int x = 0;
}
float fval = 0.0f;
try{
fval = (float)(System.Convert.ToDouble(sval));
}
catch(Exception e)
{
}
if(fval > 0.0001f && sval.Length > 9){
sval = sval.Substring(0,9);
}
if(sval.IndexOf("<INPUT") > -1) hydrograph = true;
sc.Add(sval);
}
if (hydrograph)
{
string curLine = "";
// output hydrograph data 6 entries per row
for (int i = 0; i < inData.Length; ++i)
{
string sval = inData[i].ToString();
if (sval.IndexOf('.') == -1) sval += ".";
while (sval.Length < 10) sval = " " + sval;
curLine = curLine + sval;
if (i == inData.Length - 1)
{
while(curLine.Length < 60) curLine = curLine + sval;
}
if (curLine.Length >= 60)
{
sw.WriteLine(curLine);
curLine = "";
}
}
}
else
{
// process the StringCollection row
if (!ProcessRow(r, sc, sw))
{
bmStatus.logInfo("Error with ProcessRow method");
return false;
}
}
}
}
System.TimeSpan ts = System.DateTime.Now - start;
bmStatus.logInfo("SR" + subreach.ToString() + " # days=" + _nDays.ToString() +
" hydrograph array size=" + inData.Length.ToString());
bmStatus.logInfo(" Generated in file for SR" + subreach.ToString() +
" in " + ts.Seconds.ToString() + " secs");
return true;
}
private static float[] ExtractOutputHydrograph(string outFile,IBIGMatrixStatus bmStatus)
{
// search for line containing string "SUMMARY OF DATA AND RESULTS"
if (File.Exists(outFile))
{
float[] tmpData = new float[1600];
int index = -1;
int line = 0;
int tagLine = -1;
using (StreamReader sr = new StreamReader(outFile))
{
while (sr.Peek() >= 0)
{
string sline = sr.ReadLine();
if( sline.IndexOf("SUMMARY OF DATA AND RESULTS") > -1 ){
tagLine = line;
}
if( tagLine > 1 && line > tagLine+5 )
{
if( sline.IndexOf("---") > -1 )
{
// we are done
break;
}
else{
// extract the PREDICTED DOWNSTREAM DISCHARGE DATA
string sdata = sline.Substring(41,15);
float value = 0.0f;
try{
value = System.Convert.ToSingle(sdata);
}
catch(Exception e)
{
Debug.WriteLine(e.ToString());
}
++index;
tmpData[index] = value;
}
}
++line;
}
}
// allocate data array of exact size
float[] result = new float[index + 1];
for (int i = 0; i < index + 1; ++i)
{
result[i] = tmpData[i];
}
bmStatus.logInfo("Extracted output hydrograph with " +
result.Length.ToString() + " values." );
return result;
}
bmStatus.logInfo("Extract output hydrograph failed.");
return null;
}
private static float[] ExtractInputHydrograph(Worksheet j349Worksheet,
int subreach,int nDays,
IBIGMatrixStatus bmStatus)
{
float[] data = null;
// The workbook should have a worksheet named BIG Matrix
Worksheet ws = j349Worksheet;
if (ws == null)
{
// unable to extract j349WorkSheetName from Workbook
bmStatus.logInfo("j349Worksheet is null");
return null;
}
// data size 438
int dataSize = nDays * 6;
int inputRowStart = 6;
int inputRowEnd = dataSize + 6;
int inputCol = 5 + ((subreach - 1) * 2);
bmStatus.logInfo("Extracting input hydrograph for " +
j349Worksheet.Name + " SR" + subreach.ToString() +
" rows(" + inputRowStart.ToString() +
"-" + inputRowEnd.ToString() +
" col " + inputCol.ToString());
float sum = 0.0f;
if (inputCol > 0)
{
ExcelHelper.flush("j349");
data = new float[dataSize];
for (int r = inputRowStart; r < inputRowEnd; ++r)
{
string txt = ExcelHelper.GetWorksheetValue(ws, r, inputCol);
float value = 0.0f;
try
{
value = Convert.ToSingle(txt);
//if (value < 0.0) value = 0.0f;
if (value < 1.0) value = 1.0f;
sum += value;
data[r - 6] = value;
//bmStatus.logInfo(value.ToString());
}
catch (Exception e)
{
Debug.WriteLine("txt = " + txt);
Debug.WriteLine(e.ToString());
}
}
}
if (sum < 0.001)
{
data = null;
bmStatus.logInfo("sum of output hydrograph data is less than 0.0001");
}
if (data == null)
{
bmStatus.logInfo("null input hydrograph");
}
else
{
bmStatus.logInfo("valid input hydrograph");
}
return data;
}
/// <summary>
/// Processes a specific BIG Matrix worksheet in an Excel file.
/// </summary>
/// <returns></returns>
public static bool ProcessBigMatrix(Microsoft.Office.Interop.Excel.Worksheet bigMatrixWorksheet,
Microsoft.Office.Interop.Excel.Worksheet j349Worksheet,
Microsoft.Office.Interop.Excel.Worksheet j349LogWorksheet,
IBIGMatrixStatus bmStatus)
{
System.DateTime start = System.DateTime.Now;
/*
if (activeWorkbook == null)
{
bmStatus.log("Error: active workbook is null.");
return false;
}
*/
if (bigMatrixWorksheet == null)
{
bmStatus.log("Error: bigMatrixWorksheet is null.");
return false;
}
if (j349Worksheet == null)
{
bmStatus.log("Error: j349Worksheet is null.");
return false;
}
if (j349LogWorksheet == null)
{
bmStatus.log("Error: j349LogWorksheet is null.");
return false;
}
bmStatus.logInfo(bigMatrixWorksheet.Name + " begin processing.");
// Zero out all output values in j349 worksheet
bmStatus.setBigMatrixStatus(bigMatrixWorksheet.Name, "Zeroing data.");
if( !UpdateExcelWithZeroData(j349Worksheet,j349LogWorksheet,bmStatus) )
{
bmStatus.setBigMatrixStatus(bigMatrixWorksheet.Name, "Error: UpdateExcelWithZeroData() failed.");
bmStatus.log("Error: UpdateExcelWithZeroData() failed.");
return false;
}
// The dataDirectory is a directory used to place j349 data files.
// For an excel spreadsheet named Calibration2005.xls, the data
// directory would be Calibration2005
string dataDirectory = Globals.ThisWorkbook.FullName;
dataDirectory = dataDirectory.Replace(".xls", "");
if (!Directory.Exists(dataDirectory)) Directory.CreateDirectory(dataDirectory);
dataDirectory = dataDirectory + "\\" + bigMatrixWorksheet.Name.Replace(" ", "_");
if (!Directory.Exists(dataDirectory)) Directory.CreateDirectory(dataDirectory);
bmStatus.logInfo(" worksheet data directory: " + dataDirectory);
bool errorOccurred = false;
string errorStatus = "OK";
int lastGoodSr = 0;
bmStatus.setupBigMatrixProgress(12, 1);
// Loop through each subreach (SR), build SR#.in, produce SR#.out, place results back into worksheet j349
for (int i = 0; i < 12 && !errorOccurred; ++i)
{
System.DateTime srStart = System.DateTime.Now;
bmStatus.logInfo(bigMatrixWorksheet.Name + " SR" + (i+1).ToString() + ": processing." );
bmStatus.stepBigMatrixProgress();
lastGoodSr = i;
int sr = i + 1;
bmStatus.setBigMatrixStatus(bigMatrixWorksheet.Name, "Simulating SR" + sr.ToString());
try
{
//activeWorkbook.Application.CalculateFull();
//System.Threading.Thread.Sleep(500);
errorStatus = "OK";
float[] outData = null;
// Name the inFile and outFile to match the excel filename
string filename = Globals.ThisWorkbook.FullName;
FileInfo fi = new FileInfo(filename);
string inFile = dataDirectory + "\\SR" + sr.ToString() + ".in";
string outFile = dataDirectory + "\\SR" + sr.ToString() + ".out";
bmStatus.logInfo(" in file: " + inFile);
bmStatus.logInfo(" out file: " + outFile);
// --Generate the .in file--------
if (GenerateInFile(j349Worksheet, sr, inFile,bmStatus))
{
// Run the simulation
if (j349.Simulate(inFile, outFile, bmStatus))
{
// Extract the resulting hydrograph from outFile
outData = ExtractOutputHydrograph(outFile,bmStatus);
if (outData == null)
{
errorOccurred = true;
errorStatus = "Error: Unable to extract output hydrograph from " + outFile;
bmStatus.setBigMatrixStatus(bigMatrixWorksheet.Name, errorStatus);
bmStatus.log(errorStatus);
return false;
}
}
else
{
bmStatus.logInfo("j349 simulation failed.");
return false; // any errors should have been logged by the method that failed.
}
}
else
{
bmStatus.logInfo("GenerateInFile failed.");
return false; // any errors should have been logged by the method that failed.
}
// Update the Excel spreadsheet j349_log worksheet with what happened for current subreach
try
{
if (!UpdateExcelWithSubreachData(j349Worksheet,
j349LogWorksheet, sr, inFile,
outFile, errorStatus, outData, _nDays,
bmStatus))
{
errorOccurred = true;
bmStatus.setBigMatrixStatus(bigMatrixWorksheet.Name, "Error updating Worksheet with simulation data on SR " + sr.ToString());
bmStatus.log("Error updating Worksheet with simulation data on SR " + sr.ToString());
return false;
}
}
catch (Exception e)
{
Debug.WriteLine("Exception occurred on SR " + sr.ToString());
Debug.WriteLine(e.ToString());
errorOccurred = true;
bmStatus.setBigMatrixStatus(bigMatrixWorksheet.Name, "Exception occurred on SR " + sr.ToString());
bmStatus.log("Exception occurred on SR " + sr.ToString());
bmStatus.log(e.ToString());
return false;
}
}
catch (Exception e)
{
Debug.WriteLine("Exception occurred on SR " + sr.ToString());
Debug.WriteLine(e.ToString());
bmStatus.log("Exception occurred on SR " + sr.ToString());
bmStatus.log(e.ToString());
errorOccurred = true;
}
// SR completed
System.TimeSpan ts = System.DateTime.Now - srStart;
bmStatus.log(" SR" + sr.ToString() + " completed in " + ts.Seconds.ToString() + " secs");
// Allow workbook to update it's calculations
//activeWorkbook.RefreshAll();
}
// Format a status string
// Status will be of the form "SR1-SR7 OK, SR8: Error: ..."
string status = "OK";
if (lastGoodSr == 0)
{
// nothing ran
status = "SR1 failed to run correctly";
bmStatus.setBigMatrixStatus(bigMatrixWorksheet.Name, status);
bmStatus.log(status);
return false;
}
if (lastGoodSr == 1)
{
status = "SR1 OK, SR2 " + errorStatus;
bmStatus.setBigMatrixStatus(bigMatrixWorksheet.Name, status);
bmStatus.log(status);
return false;
}
if (lastGoodSr > 1)
{
status = "SR1-SR" + lastGoodSr.ToString() + " OK, SR" + (lastGoodSr + 1).ToString() + " " + errorStatus;
}
System.TimeSpan ts2 = System.DateTime.Now - start;
bmStatus.log(bigMatrixWorksheet.Name +
" completed in " + ts2.Seconds.ToString() + " secs.");
bmStatus.setBigMatrixStatus(bigMatrixWorksheet.Name,status);
// Allow
GC.Collect();
return true;
}