public void Import(string filename, TimeSeriesDatabase db)
{
xls = new NpoiExcel(filename);
m_db = db;
var siteMapping = xls.ReadDataTable("siteMapping",true,true);
Merge(siteMapping,false, MissingSchemaAction.Ignore);
var scenarioMapping = xls.ReadDataTable("scenarioMapping",true,true);
Merge(scenarioMapping, false, MissingSchemaAction.Ignore);
ImportToPisces();
}
/// <summary>
/// Organizing VIC climate unregulated data from one large VIC Pisces database
/// into 20 Monthly MODSIM compatible *.PDB.
/// </summary>
/// <param name="args"></param>
static void Main(string[] args)
{
if (args.Length != 2)
{
PrintUsage();
return;
}
var fileNamePdb = args[0];
if (!File.Exists(fileNamePdb) || !fileNamePdb.EndsWith(".pdb"))
{
PrintUsage();
return;
}
var fileNameExcel = args[1];
if (!File.Exists(fileNameExcel) || (!fileNameExcel.EndsWith(".xls") &&
!fileNameExcel.EndsWith(".xlsx")))
{
PrintUsage();
return;
}
//inputs should be good, get to work
SQLiteServer svrVic = new SQLiteServer(fileNamePdb);
Console.WriteLine("opening " + fileNamePdb);
TimeSeriesDatabase dbVic = new TimeSeriesDatabase(svrVic);
var xls = new NpoiExcel(fileNameExcel);
Console.WriteLine("reading " + fileNameExcel);
var correlation = xls.ReadDataTable("Locals");
var forecasts = xls.ReadDataTable("Forecasts");
var period = new string[] { "2020", "2040", "2060", "2080" };
var scenario = new string[] { "Median", "MoreWarmingDry", "MoreWarmingWet", "LessWarmingDry", "LessWarmingWet" };
CreatePiscesDatabaseWithModsimNodeNames(dbVic, correlation, "", "Baseline");
AddForecastsToPiscesDatabase(dbVic, forecasts, "", "Baseline");
for (int i = 0; i < period.Length; i++)
{
for (int j = 0; j < scenario.Length; j++)
{
CreatePiscesDatabaseWithModsimNodeNames(dbVic, correlation, period[i],
scenario[j]);
AddForecastsToPiscesDatabase(dbVic, forecasts, period[i], scenario[j]);
}
}
}
public void Import(string filename, TimeSeriesDatabase db)
{
xls = new NpoiExcel(filename);
m_db = db;
var siteMapping = xls.ReadDataTable("siteMapping", true, true);
Merge(siteMapping, false, MissingSchemaAction.Ignore);
var scenarioMapping = xls.ReadDataTable("scenarioMapping", true, true);
Merge(scenarioMapping, false, MissingSchemaAction.Ignore);
ImportToPisces();
}
private void buttonOpen_Click(object sender, EventArgs e)
{
this.buttonSave.Enabled = false;
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
var fn = openFileDialog1.FileName;
this.textBoxFilename.Text = fn;
DataTable tbl = null;
if (Path.GetExtension(fn).IndexOf("xls") >= 0)
{// xls or xlsx (Excel)
NpoiExcel xls = new NpoiExcel(fn);
DataTable template = new DataTable("watertemp");
template.Columns.Add("DateTime", typeof(DateTime));
template.Columns.Add("val", typeof(double));
tbl = xls.ReadDataTable(0, template, true);
}
else if (Path.GetExtension(fn).IndexOf("csv") >= 0)
{ // csv
//tbl = new CsvFile(fn, CsvFile.FieldTypes.AllText);
var s = new TextSeries(fn);
s.Read();
tbl = s.Table;
}
m_series = CreateSeries(tbl);
this.dataGridView1.DataSource = m_series.Table;
this.timeSeriesTeeChartGraph1.Series.Clear();
this.timeSeriesTeeChartGraph1.Series.Add(m_series);
this.timeSeriesTeeChartGraph1.Draw(true);
this.comboBoxPcode.SelectedIndex = -1;
}
}
/// <summary>
/// Reads Yakima Excel sheets that contain flow measurements
/// </summary>
public static void FillMeasurementTable(string filename, HydrographyDataSet.measurementDataTable table)
{
var xls = new NpoiExcel(filename);
var cbtt = GetCbtt(filename);
var sheetName = GetSheetName(xls);
if (sheetName == "")
{
Console.WriteLine("Did not find a sheet to read from " + Path.GetFileNameWithoutExtension(filename));
return;
}
else
{
Logger.WriteLine("Reading from sheet " + sheetName);
}
var tbl = xls.ReadDataTable(sheetName, false, true);
Logger.WriteLine(filename + " contains " + tbl.Rows.Count + " rows ");
tbl.TableName = Path.GetFileNameWithoutExtension(filename);
if (tbl.TableName == "")
{
tbl.TableName = cbtt;
}
FixColumnNames(tbl);
// load data into measurement table
if (tbl.Columns.Contains("stage") && tbl.Columns.Contains("discharge"))
{
for (int i = 0; i < tbl.Rows.Count; i++)
{
var newRow = table.NewmeasurementRow();
string siteid = cbtt;
DateTime?date_measured = TryGetDateTime(tbl.Rows[i], "date_measured");
double? stage = TryGetDouble(tbl.Rows[i], "stage");
double? discharge = TryGetDouble(tbl.Rows[i], "discharge");
string quality = TryGetString(tbl.Rows[i], "quality");
string party = TryGetString(tbl.Rows[i], "party");
string notes = TryGetString(tbl.Rows[i], "notes");
if (stage.HasValue && discharge.HasValue)
{
table.AddmeasurementRow(table.NextID(), siteid, date_measured.GetValueOrDefault(), stage.Value,
discharge.GetValueOrDefault(), quality, party, notes);
}
}
}
}
private void AddSeries(TimeSeriesDatabase db, string scenarioName, string scenarioNumber)
{
DataTable scenarioSheet = xls.ReadDataTable(scenarioNumber, true, true);
int count = 0;
foreach (DataRow row in scenarioSheet.Rows)
{
var externalSiteID = row["ExternalSiteID"].ToString();
var internalSiteID = LookupInternalSiteID(externalSiteID);
string basin = LookupBasin(externalSiteID);
var parent = db.GetOrCreateFolder(basin);
string filename = row["FilePath"].ToString();
if (!File.Exists(filename))
{
if (filename != "")
{
errors.Add("Missing File: " + filename);
}
continue;
}
Series s = ReadExternalSeriesData(scenarioName, filename, externalSiteID);
s.Name = internalSiteID;
if (row.Table.Columns.IndexOf("units") >= 0)
{
s.Units = row["units"].ToString();
}
s.ConnectionString = "ScenarioName=" + scenarioName;
var id = -1;
if (db.GetSeriesFromName(internalSiteID) == null)
{
id = db.AddSeries(s, parent);
var sc = db.GetSeriesCatalog("id =" + id);
// alter entry in database to remove scenario postfix from table name
sc.Rows[0]["tablename"] = internalSiteID.ToLower();
db.Server.SaveTable(sc);
}
else
{ // if this series already exists (for another scenario)
// only save the TableData
s.Table.Columns[0].ColumnName = "datetime";
s.Table.Columns[1].ColumnName = "value";
db.CreateSeriesTable(s.Table.TableName, false);
db.Server.InsertTable(s.Table);
}
if (OnProgress != null)
{
OnProgress(this,
new ProgressEventArgs(
"saving " + internalSiteID + " " + scenarioName, count / scenarioSheet.Rows.Count * 100));
}
count++;
}
}
/// <summary>
/// Imports multiple series using an excel control file.
/// the control file has one entry per row(series)
/// and specifies file format and other details for
/// the series.
/// </summary>
/// <param name="db"></param>
/// <param name="excelFilename"></param>
public static void Import(TimeSeriesDatabase db, string excelFilename)
{
NpoiExcel xls = new NpoiExcel(excelFilename);
var tbl = xls.ReadDataTable(0, true, true);
for (int i = 0; i < tbl.Rows.Count; i++)
{
var row = tbl.Rows[i];
var format = ReadString(row, "format");
var units = ReadString(row, "units");
var folderName = ReadString(row, "folder");
var filename = ReadString(row, "filename");
if (!Path.IsPathRooted(filename))
{
string dir = Path.GetDirectoryName(db.DataSource);
filename = Path.Combine(dir, filename);
}
var siteID = ReadString(row, "siteid");
var name = ReadString(row, "name");
var sheetName = ReadString(row, "sheet");
Series s = null;
if (format == "csv" || format == "txt")
{
s = new TextSeries(filename);
s.Read();
}
// else if( format == "xls-monthly-wateryear")
// {
// throw new NotImplementedException("oops the programmer forgot to finish up some work");
// }
else if (format == "xls-daily-yearlysheets")
{
s = ImportMultiSheetDailySeriesExcel.ImportSpreadsheet(filename);
}
s.Units = units;
s.Name = name;
s.SiteID = siteID;
s.Table.TableName = "ts_" + s.Name.ToLower();
var folder = db.RootFolder;
if (folderName != "")
{
folder = db.GetOrCreateFolder(folder.Name, folderName);
}
db.AddSeries(s, folder);
}
}
public void NpoiReadIntoDataTable()
{
var fn = Path.Combine(Globals.TestDataPath, "RunningFDR 5ft 2005 Present.xlsx");
DataTable template = new DataTable("watertemp");
template.Columns.Add("DateTime", typeof(DateTime));
template.Columns.Add("temp", typeof(double));
NpoiExcel xls = new NpoiExcel(fn);
var tbl = xls.ReadDataTable(0, template, true);
Assert.AreEqual(63367, tbl.Rows.Count);
}
private static void UpdateGPSiteInfo(TimeSeriesDatabaseDataSet.sitecatalogDataTable sc)
{
Console.WriteLine("reading gp excel");
var fn = @"U:\water.usbr.gov\data\GPsitesTESSELAPP_kt.xlsx";
var xls = new NpoiExcel(fn);
var tbl = xls.ReadDataTable(0);
//var tbl = ExcelDB.Read(fn, 0);
for (int i = 0; i < tbl.Rows.Count; i++)
{
var desc = tbl.Rows[i]["Description"].ToString();
var lat = tbl.Rows[i]["Latitude"].ToString().Trim();
var lon = tbl.Rows[i]["Longitude"].ToString().Trim();
var state = tbl.Rows[i]["State"].ToString().Trim();
if (lon[0] != '-')
{
lon = "-" + lon;
}
//var office = tbl.Rows[i]["office"].ToString();
var siteid = tbl.Rows[i]["Site Name"].ToString().ToLower().Trim();
var siteType = tbl.Rows[i]["type"].ToString().ToLower().Trim();
var siteRow = sc.FindBysiteid(siteid);
if (siteRow == null)
{
Console.WriteLine("new site : " + siteid);
siteRow = sc.NewsitecatalogRow();
siteRow.siteid = siteid;
sc.AddsitecatalogRow(siteRow);
}
else
{
Console.WriteLine("existing site : " + siteRow.siteid);
}
siteRow.description = desc;
siteRow.state = state;
siteRow.latitude = lat;
siteRow.longitude = lon;
//siteRow.responsibility = office;
siteRow.type = siteType;
siteRow.agency_region = "GP";
}
}
public void Test1()
{
DataTable tbl = CreateTestTable();
NpoiExcel xls = new NpoiExcel();
xls.SaveDataTable(tbl, "newsheet");
string fn = FileUtility.GetTempFileName(".xls");// @"c:\temp\karl.xls";
File.Delete(fn);
xls.Save(fn);
DataTable tbl2 = xls.ReadDataTable("newsheet");
Assert.AreEqual(12, tbl2.Rows.Count);
xls.SaveDataTable(tbl2, "sheet1");
xls.Save(fn);
}
public void TestDataTypesToString()
{
var fn = Path.Combine(Globals.TestDataPath, "npoi", "datatypes.xlsx");
NpoiExcel x = new NpoiExcel(fn);
var tbl = x.ReadDataTable("Sheet1", true, true);
//Console.WriteLine(DataTableOutput.ToJson(tbl));
Assert.AreEqual("text entry", tbl.Rows[0][0].ToString());
Assert.AreEqual("1", tbl.Rows[1][0].ToString());
Assert.AreEqual("3.15", tbl.Rows[2][0].ToString());
Assert.AreEqual(Convert.ToDateTime("7/8/2016"),
Convert.ToDateTime(tbl.Rows[3][0].ToString()));
var d = Convert.ToDouble(tbl.Rows[4][0].ToString());
Assert.AreEqual(Convert.ToDateTime("7/8/2016"),
DateTime.FromOADate(d));
Assert.AreEqual(9, Convert.ToInt32(tbl.Rows[5][0].ToString()));
}
public static Series ImportSpreadsheet(string fileName)
{
Console.WriteLine("Reading " + fileName);
Series s = new Series("series1", TimeInterval.Daily);
var xls = new NpoiExcel(fileName);
var sheetNames = xls.SheetNames();
//ExcelUtility xls = new ExcelUtility(fileName);
//var sheetNames = ExcelUtility.SheetNames(fileName);
foreach (string sheet in sheetNames)
{
if (Regex.IsMatch(sheet.Trim(), "[0-9]{4}$")) // is 4 digit year
{
int yr = int.Parse(sheet);
Console.WriteLine("Reading sheet:" + sheet);
//var tbl = ExcelUtility.Read(fileName, sheet, false);
var tbl = xls.ReadDataTable(sheet, false, true);
ReadTable(s, yr, tbl);
}
}
return(s);
}
/// <summary>
/// Organizing VIC climate unregulated data from one large VIC Pisces database
/// into 20 Monthly MODSIM compatible *.PDB.
/// </summary>
/// <param name="args"></param>
static void Main(string[] args)
{
if (args.Length != 2)
{
PrintUsage();
return;
}
var fileNamePdb = args[0];
if (!File.Exists(fileNamePdb) || !fileNamePdb.EndsWith(".pdb"))
{
PrintUsage();
return;
}
var fileNameExcel = args[1];
if (!File.Exists(fileNameExcel) || (!fileNameExcel.EndsWith(".xls")
&& !fileNameExcel.EndsWith(".xlsx")))
{
PrintUsage();
return;
}
//inputs should be good, get to work
SQLiteServer svrVic = new SQLiteServer(fileNamePdb);
Console.WriteLine("opening " + fileNamePdb);
TimeSeriesDatabase dbVic = new TimeSeriesDatabase(svrVic);
var xls = new NpoiExcel(fileNameExcel);
Console.WriteLine("reading " + fileNameExcel);
var correlation = xls.ReadDataTable("Locals");
var forecasts = xls.ReadDataTable("Forecasts");
var period = new string[] { "2020", "2040", "2060", "2080" };
var scenario = new string[] { "Median", "MoreWarmingDry", "MoreWarmingWet", "LessWarmingDry", "LessWarmingWet" };
CreatePiscesDatabaseWithModsimNodeNames(dbVic, correlation, "", "Baseline");
AddForecastsToPiscesDatabase(dbVic, forecasts, "", "Baseline");
for (int i = 0; i < period.Length; i++)
{
for (int j = 0; j < scenario.Length; j++)
{
CreatePiscesDatabaseWithModsimNodeNames(dbVic, correlation, period[i],
scenario[j]);
AddForecastsToPiscesDatabase(dbVic, forecasts, period[i], scenario[j]);
}
}
}
/// <summary>
/// Imports multiple series using an excel control file.
/// the control file has one entry per row(series)
/// and specifies file format and other details for
/// the series.
/// </summary>
/// <param name="db"></param>
/// <param name="excelFilename"></param>
public static void Import(TimeSeriesDatabase db, string excelFilename)
{
NpoiExcel xls = new NpoiExcel(excelFilename);
var tbl = xls.ReadDataTable(0, true, true);
for (int i = 0; i < tbl.Rows.Count; i++)
{
var row = tbl.Rows[i];
var format = ReadString(row, "format");
var path = ReadString(row, "path").Split(new char[] { ',', '/', '\\' },
StringSplitOptions.RemoveEmptyEntries);
var filename = ReadString(row, "filename");
var tablename = ReadString(row, "tablename");
if (filename != "" && !Path.IsPathRooted(filename))
{
string dir = Path.GetDirectoryName(db.DataSource);
filename = Path.Combine(dir, filename);
}
var sheetName = ReadString(row, "sheet");
var interval = TimeInterval.Daily;
var timeinterval = ReadString(row, "timeinterval").ToLower();
if (timeinterval.IndexOf("instant") >= 0)
{
interval = TimeInterval.Irregular;
}
if (timeinterval.IndexOf("month") >= 0)
{
interval = TimeInterval.Monthly;
}
Series s = new Series("", interval);
s.Name = ReadString(row, "name");
s.SiteID = ReadString(row, "siteid");
s.Units = ReadString(row, "units");
s.Notes = ReadString(row, "notes");
s.Expression = ReadString(row, "expression");
if (s.Expression != "")
{
s.Provider = "CalculationSeries";
}
if (format == "csv" || format == "txt")
{
if (File.Exists(filename))
{
s = new TextSeries(filename);
s.Read();
}
else
{
if (filename.Trim() != "")
{
Logger.WriteLine("File not found: '" + filename + "'");
}
}
}
// else if( format == "xls-monthly-wateryear")
// {
// throw new NotImplementedException("oops the programmer forgot to finish up some work");
// }
else if (format == "xls-daily-yearlysheets")
{
s = ImportMultiSheetDailySeriesExcel.ImportSpreadsheet(filename);
}
if (tablename != "")
{
s.Table.TableName = tablename;
}
else
{
s.Table.TableName = "ts_" + s.Name.ToLower();
}
var folder = db.RootFolder;
if (path.Length > 0)
{
folder = db.GetOrCreateFolder(path);
}
int id = db.AddSeries(s, folder);
var prop = ReadString(row, "properties").Split(new char[] { ',' },
StringSplitOptions.RemoveEmptyEntries);
for (int p = 0; p < prop.Length; p++)
{
var tokens = prop[p].Split(':');
if (tokens.Length == 2)
{
s.Properties.Set(tokens[0], tokens[1]);
}
}
s.Properties.Save();
}
}