/// <summary>
/// Open the file ready for reading.
/// </summary>
/// <param name="fileName">Name of the file.</param>
/// <param name="sheetName">Name of excel worksheet, if applicable</param>
/// <exception cref="System.Exception">Cannot find file: + FileName</exception>
public void Open(string fileName, string sheetName = "")
{
if (fileName == "")
{
return;
}
if (!File.Exists(fileName))
{
throw new Exception("Cannot find file: " + fileName);
}
_FileName = fileName;
_SheetName = sheetName;
IsCSVFile = Path.GetExtension(fileName).ToLower() == ".csv";
IsExcelFile = ExcelUtilities.IsExcelFile(fileName);
if (IsExcelFile)
{
OpenExcelReader();
}
else
{
inStreamReader = new StreamReaderRandomAccess(_FileName);
Open();
}
}
/// <summary>
/// This is used to read an excel file, extracting header, unit and constant information
/// </summary>
public void OpenExcelReader()
{
_excelData = new DataTable();
if ((_FileName.Length <= 0) || (_SheetName.Length <= 0))
{
return;
}
try
{
Units = new StringCollection();
Headings = new StringCollection();
DataTable resultDt = ExcelUtilities.ReadExcelFileData(_FileName, _SheetName);
if (resultDt == null)
{
throw new Exception("There does not appear to be any data.");
}
int posEquals, rowCount = -1;
string coltext1, coltext2, coltext3, coltext4;
string unit, name, value, comment;
bool titleFound = false;
bool dataFound = false;
bool unitsFound = false;
while (dataFound == false)
{
rowCount++;
coltext1 = resultDt.Rows[rowCount][0].ToString().Trim();
coltext2 = resultDt.Rows[rowCount][1].ToString().Trim();
coltext3 = resultDt.Rows[rowCount][2].ToString().Trim();
coltext4 = resultDt.Rows[rowCount][3].ToString().Trim();
//Assumptions are made here about the data, based on the values in the first two columns:
// If they are both blank, then this is a blank line.
// If there is data in first column, but not second, then this is the header/constant details line
// If there is data in both columns, then this is the first of our data for the datatable, with
// the first line being the column titles, and if the second line starts with a '(' then this is
// a measurement line, otherwise we are looking at the actual data values.
//if no data only in columns 1 and 2, then it is a blank line
//if data only in columns 1, then it could be a comments line, and is ignored
//if data only in columns 1 & 2, then it is a constants row
//if data on 3 or more columns then is actual data
//all measurements (in both constants and data headings) are in brackets after name (title)
unit = string.Empty;
name = string.Empty;
value = string.Empty;
comment = string.Empty;
posEquals = coltext1.IndexOf('!');
if (posEquals == 0)
{
//this is a comment line, and can be ignored
resultDt.Rows[rowCount].Delete();
}
else if (coltext1.Length == 0)
{
//if no data in column 1, then this is a blank row, need to make sure we remove these
resultDt.Rows[rowCount].Delete();
}
// Check for and handle "old style" constants
else if ((coltext1.Length > 0) && coltext2.Length == 0)
{
comment = StringUtilities.SplitOffAfterDelimiter(ref coltext1, "!").Trim();
posEquals = coltext1.IndexOf('=');
if (posEquals != -1)
{
name = coltext1.Substring(0, posEquals).Trim();
if (name.ToLower() == "title")
{
titleFound = true;
name = "Title";
}
value = coltext1.Substring(posEquals + 1).Trim();
if (name != "Title")
{
unit = StringUtilities.SplitOffBracketedValue(ref value, '(', ')');
}
_Constants.Add(new ApsimConstant(name, value, unit, comment));
}
resultDt.Rows[rowCount].Delete();
}
else if ((coltext1.Length > 0) && (coltext2.Length > 0) && (coltext4.Length == 0))
{
//the unit, if it exists, is after the title (name) of the constant
unit = StringUtilities.SplitOffBracketedValue(ref coltext1, '(', ')');
name = coltext1.Trim();
if (name.ToLower() == "title")
{
titleFound = true;
name = "Title";
}
//now look at what is left in the first row
value = coltext2.Trim();
//comments are in column three - need to strip out any '!' at the start
if (coltext3.Length > 0)
{
comment = StringUtilities.SplitOffAfterDelimiter(ref coltext3, "!");
comment.Trim();
}
_Constants.Add(new ApsimConstant(name, value, unit, comment));
resultDt.Rows[rowCount].Delete();
}
//the first line that has data in the first 4 columns
else if ((coltext1.Length > 0) && (coltext2.Length > 0) && (coltext3.Length > 0) && (coltext4.Length > 0))
{
for (int i = 0; i < resultDt.Columns.Count; i++)
{
value = resultDt.Rows[rowCount][i].ToString();
if (value.Length > 0)
{
//extract the measurment if it exists, else need to create blank, and add to Units collection
unit = StringUtilities.SplitOffBracketedValue(ref value, '(', ')');
if (unit.Length <= 0)
{
unit = "()";
}
else
{
unitsFound = true;
}
Units.Add(unit.Trim());
//add the title(name to Units collection
Headings.Add(value.Trim());
}
}
resultDt.Rows[rowCount].Delete();
//we have got both headings and measurements, so we can exit the while loop
dataFound = true;
}
//to ensure that we never get stuck on infinite loop;
if (rowCount >= resultDt.Rows.Count - 1)
{
dataFound = true;
}
}
//make sure that the next row doesn't have '()' measurements in it
coltext1 = resultDt.Rows[rowCount + 1][0].ToString().Trim();
posEquals = coltext1.IndexOf('(');
if (posEquals == 0)
{
//this line contains brackets, SHOULD be DATA
if (unitsFound)
{
throw new Exception();
}
// but if we haven't already seen units,
// read units from this line
// (to support "old style" layouts)
else
{
for (int i = 0; i < resultDt.Columns.Count; i++)
{
Units[i] = resultDt.Rows[rowCount + 1][i].ToString();
}
resultDt.Rows[rowCount + 1].Delete();
}
}
//this will actually delete all of the rows that we flagged for delete (above)
resultDt.AcceptChanges();
//this is where we clone the current datatable, so that we can set the datatypes to what they should be,
//based on the first row of actual data (Need to do this as cannot change datatype once a column as data).
_excelData = resultDt.Clone();
for (int i = 0; i < resultDt.Columns.Count; i++)
{
_excelData.Columns[i].DataType = StringUtilities.DetermineType(resultDt.Rows[0][i].ToString(), Units[i]);
}
_excelData.Load(resultDt.CreateDataReader());
//now do the column names, need to have data loaded before we rename columns, else the above won't work.
for (int i = 0; i < resultDt.Columns.Count; i++)
{
_excelData.Columns[i].ColumnName = Headings[i];
}
_FirstDate = GetDateFromValues(_excelData, 0);
_LastDate = GetDateFromValues(_excelData, _excelData.Rows.Count - 1);
if (!titleFound)
{
_Constants.Add(new ApsimConstant("Title", System.IO.Path.GetFileNameWithoutExtension(_FileName), "", ""));
}
}
catch (Exception e)
{
throw new Exception(string.Format("The excel Sheet {0} is not in a recognised Weather file format." + e.Message.ToString(), _SheetName));
}
}
