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> /// 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; } } }