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>
/// 导出数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private async void BtnExport_ClickAsync(object sender, RoutedEventArgs e)
{
Button CurBtn = sender as Button;
List <Lib.MemberInfoEntity> MemberList = await DataMemberRepository.ReadMembers(new MemberSearch()
{
});
if (MemberList == null)
{
return;
}
System.Windows.Forms.SaveFileDialog fileDialog = new System.Windows.Forms.SaveFileDialog();
fileDialog.Filter = "Excel|*.xls";
if (fileDialog.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
{
return;
}
try
{
CurBtn.IsEnabled = false;
OperationMsg = "正在导出人员信息,请稍候....";
await Task.Run(() =>
{
NpoiExcel.ExportExcels(fileDialog.FileName, "员工信息", MemberList);
});
OperationMsg = $"人员信息导出....完成，文件为[{fileDialog.FileName}]。";
CurBtn.IsEnabled = true;
}
catch (Exception Ex)
{
AppFuns.ShowMessage(Ex.Message, Caption: "失败");
CurBtn.IsEnabled = true;
}
}
private static string GetSheetName(NpoiExcel xls)
{
string sheetName = "summary";
if (!xls.SheetExists(sheetName))
{
foreach (var item in xls.SheetNames())
{
if (item.ToLower().IndexOf("summary") == 0) //SUMMARY SHEET-EASW-WY98
{
return(item);
}
}
if (xls.SheetExists("sheet1"))
{
sheetName = "sheet1";
}
else
{
sheetName = "";
}
}
return(sheetName);
}
/// <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);
}
}
}
}
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]);
}
}
}
/// <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 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();
}
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 PrintPreview()
{
NpoiExcel xls = new NpoiExcel(xlsFilename);
xls.SetCellText(0, "D7", Hdb.Instance.Server.ServiceName);
xls.SetCellText(0, "D8", DateTime.Now.ToString());
xls.InsertDataTable(0, "A9", msDataTable);
// format excel according to POET format.
for (int c = 1; c < msDataTable.DisplayFormat.Length; c++)
{
string fmt = ExcelFormat(msDataTable.DisplayFormat[c]);
xls.FormatRange(0, 10, c, msDataTable.Rows.Count + 10, c, fmt);
}
xls.Save(xlsFilename);
System.Diagnostics.Process.Start(xlsFilename);
}
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 void Test2()
{
var fn = Path.Combine(Globals.TestDataPath, "hdb_report.xls");
NpoiExcel xls = new NpoiExcel(fn);
xls.SetCellText(0, "D7", "oracle.service.name");
xls.SetCellText(0, "D8", DateTime.Now.ToString());
var tbl = CreateTestTable(4);
xls.InsertDataTable(0, "A9", tbl);
int lastRow = 10 + tbl.Rows.Count;
string strRange = "A10:A" + (lastRow + 5);
// CellStyle
xls.FormatRange(0, "B10:B12", "0.00");
xls.FormatRange(0, "C10:C13", "0.0");
xls.FormatRange(0, "D10:D14", "0");
string[] formats = new string[] { "", "" };
// IRange rng= ws.Cells[strRange];
// rng = rng.Offset(0, 1);
// for (int i = 0; i < formats.Length; i++)
// {
// rng.NumberFormat = ExcelFormat(formats[i]);
// rng = rng.Offset(0, 1);
// }
string fnk = FileUtility.GetTempFileName(".xls");// @"c:\temp\karl.xls";
xls.Save(fnk);
//System.Diagnostics.Process.Start(fnk);
}
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>
/// 导出数据。
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_Export_ClickAsync(object sender, RoutedEventArgs e)
{
List <Lib.MemberInfoEntity> EntityList = _PageViewModel.EntityList.ToList();
if (EntityList == null || EntityList.Count < 1)
{
return;
}
System.Windows.Forms.SaveFileDialog fileDialog = new System.Windows.Forms.SaveFileDialog();
fileDialog.Filter = "Excel|*.xls";
if (fileDialog.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
{
return;
}
try
{
NpoiExcel.ExportExcels(fileDialog.FileName, "员工信息", EntityList);
AppFuns.ShowMessage("数据导出成功！", Caption: "完成");
}
catch (Exception Ex)
{
AppFuns.ShowMessage(Ex.Message, Caption: "失败");
}
}
/// <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();
}
}
private async void BtnSelectFile_Click(object sender, RoutedEventArgs e)
{
Button CurBtn = sender as Button;
int AddCount = 0;
// 在WPF中， OpenFileDialog位于Microsoft.Win32名称空间
Microsoft.Win32.OpenFileDialog dialog = new Microsoft.Win32.OpenFileDialog
{
Filter = "Excel Files|*.xls;*.xlsx"
};
if (dialog.ShowDialog() == true)
{
CurBtn.IsEnabled = false;
try
{
System.IO.FileInfo theExcelFile = new System.IO.FileInfo(dialog.FileName);
OperationMsg = $"正在从文件[{theExcelFile.FullName}]中导入人员信息，请稍候....";
await Task.Run(async() =>
{
DataTable UserTable = NpoiExcel.ReadStreamToDataTable(theExcelFile.OpenRead(), null, true);
if (UserTable != null && UserTable.Rows.Count > 0)
{
Lib.MemberInfoEntity tempMember = new Lib.MemberInfoEntity();
PropertyInfo[] Attri = tempMember.GetType().GetProperties();
for (int i = 0; i < UserTable.Rows.Count; i++)
{
tempMember = new Lib.MemberInfoEntity();
foreach (PropertyInfo item in Attri)
{
//获取该属性的描述特性值（比如：[Description("身份证号")]中的 "身份证号" ）
object[] objs = item.GetCustomAttributes(typeof(DescriptionAttribute), true);
string PropDescription = ((DescriptionAttribute)objs[0]).Description;
if (UserTable.Columns.Contains(PropDescription) && UserTable.Rows[i][PropDescription] != null)
{
StringBuilder dateStr = new StringBuilder(UserTable.Rows[i][PropDescription].ToString());
switch (item.PropertyType.FullName)
{
case "System.DateTime":
if (!DateTime.TryParse(dateStr.ToString(), out DateTime tempDateTime))
{
//处理Excel中数据不规范情况。
dateStr = dateStr.Replace('.', '/');
dateStr = dateStr.Replace('-', '/');
dateStr = dateStr.Replace('—', '/');
dateStr = dateStr.Replace('。', '/');
if (dateStr.Length < 8)
{
dateStr.Append("/01");
}
}
if (DateTime.TryParse(dateStr.ToString(), out tempDateTime))
{
item.SetValue(tempMember, tempDateTime);
}
break;
case "System.Int32":
if (int.TryParse(dateStr.ToString(), out int tempIntValue))
{
item.SetValue(tempMember, tempIntValue);
}
break;
case "System.String":
item.SetValue(tempMember, UserTable.Rows[i][PropDescription].ToString());
break;
default:
continue;
}
}
}
if (!string.IsNullOrEmpty(tempMember.Id))
{
tempMember.UserId = AppSet.LoginUser.Id;
ExcuteResult excuteResult = await DataMemberRepository.AddOrUpdate(tempMember).ConfigureAwait(false);
if (excuteResult.State == 0)
{
AddCount++;
OperationMsg = $"正在从文件[{theExcelFile.FullName}]中导入人员信息，已导入[{AddCount}]条信息。";
}
}
}
}
});//Task Finish
OperationMsg = $"人员信息导入成功，共导入[{AddCount}]条信息。";
CurBtn.IsEnabled = true;
}
catch (Exception VE)
{
AppFuns.ShowMessage("出现错误：" + VE.Message, Caption: "错误", isErr: true);
CurBtn.IsEnabled = true;
return;
}
}
}
