public static bool ConvertWorksheetToIn(Worksheet ws, string outFilename)
{
bool success = true;
// If the outFilename exists, delete it
if (File.Exists(outFilename)) File.Delete(outFilename);
// Open the output file
using (StreamWriter ofs = new StreamWriter(outFilename))
{
// traverse the Worksheet row by row, translating as we go...
//for(int row = 0; row < ws.Rows; ++row)
for(int row = 0; row < ws.Rows.Count; ++row)
{
// extract the row from the Worksheet as a StringCollection
//StringCollection sc = ws.GetRow(row);
/*
StringCollection sc = (StringCollection)(ws.Rows[);
// process the StringCollection row
if (!ProcessRow(sc, ofs))
{
success = false;
break;
}
* */
}
}
if (!success) File.Delete(outFilename);
return success;
}
public static Workbook Open(string excelFilename)
{
Workbook wb = new Workbook();
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(excelFilename,
0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",
true, false, 0, true, false, false);
// Loop throught Worksheets
Microsoft.Office.Interop.Excel.Worksheet excelWorksheet = null;
for(int w = 1; w <= excelWorkbook.Worksheets.Count; ++w)
{
excelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkbook.Worksheets[w];
Worksheet ws = new Worksheet();
ws.Name = excelWorksheet.Name;
Microsoft.Office.Interop.Excel.Range usedRange = excelWorksheet.UsedRange;
// convert the range to a System.Array
System.Array array = (System.Array)usedRange.Cells.Value2;
System.Diagnostics.Debug.WriteLine("Worksheet of name " + ws.Name);
if (array != null)
{
System.Diagnostics.Debug.WriteLine("Rows range from " + array.GetLowerBound(0) + " to " + array.GetUpperBound(0));
System.Diagnostics.Debug.WriteLine("Columns range from " + array.GetLowerBound(1) + " to " + array.GetUpperBound(1));
for (int i = array.GetLowerBound(0); i <= array.GetUpperBound(0); ++i)
{
for (int j = array.GetLowerBound(1); j <= array.GetUpperBound(1); ++j)
{
if (array.GetValue(i, j) == null)
{
//System.Diagnostics.Debug.WriteLine("cell (" + i + "," + j + ") = null");
}
else
{
//System.Diagnostics.Debug.WriteLine("cell (" + i + "," + j + ") = " + array.GetValue(i, j).ToString());
}
// Note: Excel uses 1 based indices
if (array.GetValue(i, j) != null)
{
string sText = array.GetValue(i, j).ToString();
ws.SetText(i - 1, j - 1, sText);
}
}
}
wb.AddWorkSheet(ws);
}
}
// Clean up
excelWorkbook.Close(false, null, null);
excelApp.Workbooks.Close();
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorksheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
excelWorksheet = null;
excelWorkbook = null;
if (excelApp != null)
{
System.Diagnostics.Process[] pProcess;
pProcess = System.Diagnostics.Process.GetProcessesByName("Excel");
pProcess[0].Kill();
}
excelApp = null;
GC.Collect();
return wb;
}
public int AddWorkSheet(Worksheet ws)
{
_workSheets.Add(ws);
return _workSheets.Count;
}
/// <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;
}
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[] 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;
}
