/// <summary>
/// Create Series that references data in Excel
/// </summary>
public SpreadsheetGearSeries(string filename, string sheetName,
string dateColumn, string valueColumn)
: base()
{
workbook = SpreadsheetGearExcel.GetWorkbookReference(filename);
Init(workbook, sheetName, dateColumn, valueColumn, false, "", "", this.units);
}
/// <summary>
/// Create Series that references data in Excel
/// </summary>
public SpreadsheetGearSeries(string filename, string sheetName,
string dateColumn, string valueColumn, bool waterYearFormat, string siteColumn = "",
string siteFilter = "", string units = "")
: base()
{
workbook = SpreadsheetGearExcel.GetWorkbookReference(filename);
Init(workbook, sheetName, dateColumn, valueColumn, waterYearFormat, siteColumn, siteFilter, units);
}
/// <summary>
/// loads spreadsheet from disk or gets a referecne to cached refererence
/// </summary>
/// <param name="filename"></param>
/// <returns></returns>
public static IWorkbook GetWorkbookReference(string filename)
{
IWorkbook workbook = null;
SpreadsheetGearExcel xls = null;
if (s_cache.ContainsKey(filename))
{ // allready in memory.
Logger.WriteLine("Found file in cache: " + Path.GetFileName(filename));
xls = s_cache[filename];
workbook = xls.Workbook;
FileInfo fi = new FileInfo(filename);
if (fi.LastWriteTime > xls.LastWriteTime)
{
Logger.WriteLine("xls File has changed... updating cache");
xls = new SpreadsheetGearExcel(filename);
workbook = xls.Workbook;
s_cache[filename] = xls;
}
}
else
{
Logger.WriteLine("reading file from disk: " + Path.GetFileName(filename));
if (!File.Exists(filename))
{
Logger.WriteLine("File does not exist: '" + filename + "'");
}
xls = new SpreadsheetGearExcel(filename);
workbook = xls.Workbook;
int max_cache_size = 10;
if (s_cache.Count >= max_cache_size)
{
s_cache.Clear();
Logger.WriteLine(" s_cache.Clear()");
}
s_cache.Add(filename, xls);
}
return(workbook);
}
private void ReadWaterYearsFromExcel(DateTime t1, DateTime t2)
{
if (idxDate < 0 || idxValue < 0)
{
return;
}
errorCount = 0;
IRange rng = worksheet.UsedRange.Range;
IValues values = (IValues)worksheet;
for (int i = beginningRowIndex; i < rng.RowCount; i++)
{
if (values[i, idxDate] == null || values[i, idxValue] == null)
{
errorCount++;
if (errorCount > 100)
{
Logger.WriteLine("date or value is null ;skipping row at index = " + i);
}
continue;
}
double dyear = -1;
if (!SpreadsheetGearExcel.TryReadingValue(values[i, idxDate], out dyear))
{
errorCount++;
if (errorCount > 100)
{
Logger.WriteLine("error reading water year");
}
continue;
}
int wy = Convert.ToInt32(dyear);
for (int monthIndex = idxValue; monthIndex < idxValue + 12; monthIndex++)
{
DateTime t = GetDate(wy, monthIndex, idxValue);
if (values[i, monthIndex] == null)
{
AddMissing(t);
}
else
{
double d;
if (!SpreadsheetGearExcel.TryReadingValue(values[i, monthIndex], out d))
{
errorCount++;
Logger.WriteLine("error reading value");
throw new InvalidCastException("Could not read value");
}
Add(t, d);
}
}
}
if (errorCount > 100)
{
Logger.WriteLine("Skipped " + (errorCount - 100) + " messages");
}
}
private void ReadFromExcel(DateTime t1, DateTime t2)
{
errorCount = 0;
if (idxDate < 0 || idxValue < 0)
{
return;
}
IRange rng = worksheet.UsedRange.Range;
IValues values = (IValues)worksheet;
for (int i = beginningRowIndex; i < rng.RowCount; i++)
{
//Console.WriteLine("row index "+i);
if (idxSiteColumn >= 0)
{// filtering enabled.
var v = values[i, idxSiteColumn];
if (v == null)
{
continue;
}
if (v.Type == SpreadsheetGear.Advanced.Cells.ValueType.Number)
{
if (v.Number.ToString() != siteFilter)
{
continue;
}
}
if (v.Type == SpreadsheetGear.Advanced.Cells.ValueType.Text)
{
if (v.Text != siteFilter)
{
continue;
}
}
}
if (values[i, idxDate] == null)
{
errorCount++;
if (errorCount > 100)
{
continue;
}
Logger.WriteLine("date is null ;skipping row at index = " + i);
continue;
}
DateTime t;
if (!SpreadsheetGearExcel.TryReadingDate(workbook, values[i, idxDate], out t))
{
errorCount++;
if (errorCount > 100)
{
continue;
}
Logger.WriteLine(filename + ": can't read date; row at index = " + i);
continue;
}
double d = Point.MissingValueFlag;
if (values[i, idxValue] != null &&
!SpreadsheetGearExcel.TryReadingValue(values[i, idxValue], out d))
{
errorCount++;
if (errorCount < 100)
{
Logger.WriteLine(filename + ": can't read value ; row at index = " + i);
}
d = Point.MissingValueFlag;
//continue;
}
if (double.IsNaN(d))
{
d = Point.MissingValueFlag;
}
int idxTime = this.IndexOf(t);
if (idxTime >= 0)
{
errorCount++;
if (errorCount > 100)
{
continue;
}
string msg = "duplicate date found in row " + i + " date = '" + t.ToString() + "' value =" + d;
Logger.WriteLine(msg);
Messages.Add(msg);
msg = "previously imported value = " + this[idxTime].Value;
Logger.WriteLine(msg);
Messages.Add(msg);
}
else if (t >= t1 && t <= t2)
{
Add(t, d);
}
}
if (errorCount > 100)
{
Logger.WriteLine("Skipped " + (errorCount - 100) + " messages");
}
}
/// <summary>
/// loads spreadsheet from disk or gets a referecne to cached refererence
/// </summary>
/// <param name="filename"></param>
/// <returns></returns>
public static IWorkbook GetWorkbookReference(string filename)
{
IWorkbook workbook = null;
SpreadsheetGearExcel xls = null;
if (s_cache.ContainsKey(filename))
{ // allready in memory.
Logger.WriteLine("Found file in cache: " + Path.GetFileName(filename));
xls = s_cache[filename];
workbook = xls.Workbook;
FileInfo fi = new FileInfo(filename);
if (fi.LastWriteTime > xls.LastWriteTime)
{
Logger.WriteLine("xls File has changed... updating cache");
xls = new SpreadsheetGearExcel(filename);
workbook = xls.Workbook;
s_cache[filename] = xls;
}
}
else
{
Logger.WriteLine("reading file from disk: " + Path.GetFileName(filename));
if (!File.Exists(filename))
{
Logger.WriteLine("File does not exist: '" + filename + "'");
}
xls = new SpreadsheetGearExcel(filename);
workbook = xls.Workbook;
int max_cache_size = 10;
if (s_cache.Count >= max_cache_size)
{
s_cache.Clear();
Logger.WriteLine(" s_cache.Clear()");
}
s_cache.Add(filename, xls);
}
return workbook;
}