/// <summary>
/// Determine and return the data types of the specfied words.
/// </summary>
/// <param name="inData">The in.</param>
/// <param name="words">The words.</param>
/// <returns></returns>
private Type[] DetermineColumnTypes(StreamReaderRandomAccess inData, StringCollection words)
{
Type[] Types = new Type[words.Count];
for (int w = 0; w != words.Count; w++)
{
if (words[w] == "?" || words[w] == "*" || words[w] == "")
{
Types[w] = StringUtilities.DetermineType(LookAheadForNonMissingValue(inData, w), Units[w]);
}
else
{
Types[w] = StringUtilities.DetermineType(words[w], Units[w]);
}
}
return(Types);
}
/// <summary>
/// Determine and return the data types of the specfied words.
/// </summary>
/// <param name="inData">The in.</param>
/// <param name="words">The words.</param>
/// <returns></returns>
private Type[] DetermineColumnTypes(StreamReaderRandomAccess inData, StringCollection words)
{
Type[] Types = new Type[words.Count];
for (int w = 0; w != words.Count; w++)
{
if (words[w] == "?" || words[w] == "*" || words[w] == "")
{
Types[w] = StringUtilities.DetermineType(LookAheadForNonMissingValue(inData, w), Units[w]);
}
else
{
Types[w] = StringUtilities.DetermineType(words[w], Units[w]);
}
// If we can parse as a DateTime, but don't yet have an explicit format, try to determine
// the correct format and make it explicit.
if (Types[w] == typeof(DateTime) && (Units[w] == "" || Units[w] == "()"))
{
// First try our traditional default format
DateTime dtValue;
if (DateTime.TryParseExact(words[w], "yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, out dtValue))
{
Units[w] = "(yyyy-MM-dd)";
}
else
{
// We know something in the current culture works. Step through the patterns until we find it.
string[] dateFormats = System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.GetAllDateTimePatterns();
foreach (string dateFormat in dateFormats)
{
if (DateTime.TryParseExact(words[w], dateFormat, System.Globalization.CultureInfo.CurrentCulture, System.Globalization.DateTimeStyles.None, out dtValue))
{
Units[w] = "(" + dateFormat + ")";
break;
}
}
}
}
}
return(Types);
}
/// <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>
/// Convert this file to a DataTable.
/// </summary>
/// <returns></returns>
public DataTable ToTable(List <string> addConsts = null)
{
System.Data.DataTable data = new System.Data.DataTable();
data.TableName = "Data";
if (IsExcelFile == true)
{
data = ToTableFromExcel(addConsts);
}
else
{
List <ApsimConstant> addedConstants = new List <ApsimConstant>();
StringCollection words = new StringCollection();
bool checkHeadingsExist = true;
while (GetNextLine(inStreamReader, ref words))
{
if (checkHeadingsExist)
{
for (int w = 0; w != ColumnTypes.Length; w++)
{
data.Columns.Add(new DataColumn(Headings[w], ColumnTypes[w]));
}
if (addConsts != null)
{
foreach (ApsimConstant constant in Constants)
{
if (addConsts.Contains(constant.Name, StringComparer.OrdinalIgnoreCase) && data.Columns.IndexOf(constant.Name) == -1)
{
Type ColumnType = StringUtilities.DetermineType(constant.Value, constant.Units);
data.Columns.Add(new DataColumn(constant.Name, ColumnType));
addedConstants.Add(new ApsimConstant(constant.Name, constant.Value, constant.Units, ColumnType.ToString()));
}
}
}
}
DataRow newMetRow = data.NewRow();
object[] values = null;
try
{
values = ConvertWordsToObjects(words, ColumnTypes);
}
catch (Exception err)
{
throw new Exception("Error while parsing file " + _FileName + ": " + err.Message);
}
for (int w = 0; w != words.Count; w++)
{
int TableColumnNumber = newMetRow.Table.Columns.IndexOf(Headings[w]);
if (!Convert.IsDBNull(values[TableColumnNumber]))
{
newMetRow[TableColumnNumber] = values[TableColumnNumber];
}
}
foreach (ApsimConstant constant in addedConstants)
{
if (constant.Comment == typeof(Single).ToString() || constant.Comment == typeof(Double).ToString())
{
newMetRow[constant.Name] = Double.Parse(constant.Value, CultureInfo.InvariantCulture);
}
else
{
newMetRow[constant.Name] = constant.Value;
}
}
data.Rows.Add(newMetRow);
checkHeadingsExist = false;
}
}
return(data);
}