/// <summary>
/// Convert the specified words to the specified column types and return their values.
/// </summary>
/// <param name="words">The words.</param>
/// <param name="columnTypes">The column types.</param>
/// <returns></returns>
private object[] ConvertWordsToObjects(StringCollection words, Type[] columnTypes)
{
object[] values = new object[words.Count];
for (int w = 0; w != words.Count; w++)
{
try
{
words[w] = words[w].Trim();
if (words[w] == "?" || words[w] == "*" || words[w] == "")
{
values[w] = DBNull.Value;
}
else if (columnTypes[w] == typeof(DateTime))
{
// Need to get a sanitised date e.g. d/M/yyyy
string DateFormat = Units[w].ToLower();
DateFormat = StringUtilities.SplitOffBracketedValue(ref DateFormat, '(', ')');
DateFormat = DateFormat.Replace("mmm", "MMM");
DateFormat = DateFormat.Replace("mm", "m");
DateFormat = DateFormat.Replace("dd", "d");
DateFormat = DateFormat.Replace("m", "M");
if (DateFormat == "")
{
DateFormat = "yyyy-MM-dd";
}
DateTime Value = DateTime.ParseExact(words[w], DateFormat, CultureInfo.InvariantCulture);
values[w] = Value;
}
else if (columnTypes[w] == typeof(float))
{
double Value;
if (double.TryParse(words[w], NumberStyles.Float, CultureInfo.InvariantCulture, out Value))
{
values[w] = Value;
}
else
{
values[w] = DBNull.Value;
}
}
else
{
values[w] = words[w];
}
}
catch (Exception)
{
values[w] = DBNull.Value;
}
}
return(values);
}
/// <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));
}
}
/// <summary>
/// Read in the APSIM header - headings/units and constants.
/// </summary>
/// <param name="inData">The in.</param>
/// <exception cref="System.Exception">The number of headings and units doesn't match in file: + _FileName</exception>
private void ReadApsimHeader(StreamReaderRandomAccess inData)
{
StringCollection ConstantLines = new StringCollection();
StringCollection HeadingLines = new StringCollection();
ReadApsimHeaderLines(inData, ref ConstantLines, ref HeadingLines);
bool TitleFound = false;
foreach (string ConstantLine in ConstantLines)
{
string Line = ConstantLine;
string Comment = StringUtilities.SplitOffAfterDelimiter(ref Line, "!");
Comment.Trim();
int PosEquals = Line.IndexOf('=');
if (PosEquals != -1)
{
string Name = Line.Substring(0, PosEquals).Trim();
if (Name.ToLower() == "title")
{
TitleFound = true;
Name = "Title";
}
string Value = Line.Substring(PosEquals + 1).Trim();
string Unit = string.Empty;
if (Name != "Title")
{
Unit = StringUtilities.SplitOffBracketedValue(ref Value, '(', ')');
}
_Constants.Add(new ApsimConstant(Name, Value, Unit, Comment));
}
}
if (HeadingLines.Count >= 1)
{
if (IsCSVFile)
{
HeadingLines[0] = HeadingLines[0].TrimEnd(',');
Headings = new StringCollection();
Units = new StringCollection();
Headings.AddRange(HeadingLines[0].Split(",".ToCharArray()));
for (int i = 0; i < Headings.Count; i++)
{
Headings[i] = Headings[i].Trim();
Headings[i] = Headings[i].Trim('"');
Units.Add("()");
}
}
else
{
Headings = StringUtilities.SplitStringHonouringQuotes(HeadingLines[0], " \t");
Units = StringUtilities.SplitStringHonouringQuotes(HeadingLines[1], " \t");
}
TitleFound = TitleFound || StringUtilities.IndexOfCaseInsensitive(Headings, "title") != -1;
if (Headings.Count != Units.Count)
{
throw new Exception("The number of headings and units doesn't match in file: " + _FileName);
}
}
if (!TitleFound)
{
_Constants.Add(new ApsimConstant("Title", System.IO.Path.GetFileNameWithoutExtension(_FileName), "", ""));
}
}