public string[] GetSequencesFromMaster(string filename) { var dataProvider = new ExcelStorage(typeof(UIDataRaw)); dataProvider.FileName = filename; dataProvider.StartRow = 1; dataProvider.StartColumn = 1; var testSequences = new List <string>(); foreach (var sheetName in Utility.GetExcelSheetNames(filename)) { dataProvider.SheetName = sheetName; var data = (UIDataRaw[])dataProvider.ExtractRecords(); var props = data.FirstOrDefault().GetType().GetProperties(); for (int i = 0; i < data.Length; i++) { //get the list of test sequences for (int x = 0; x < props.Count(); x++) { object val = data[i].GetType().GetProperty(props[x].Name).GetValue(data[i], null); if (i == 0 && x > 1) { if (val != null) { testSequences.Add(val.ToString()); } } } } } return(testSequences.ToArray()); }
static void Main(string[] args) { // create some data to export MyClass[] rows = new MyClass[2] { new MyClass() { Field1 = "Apples", Field2 = 23, Field3 = "Yes" }, new MyClass() { Field1 = "Oranges", Field2 = 17, Field3 = "No" } }; ExcelStorage provider = new ExcelStorage(typeof(MyClass)); // Set the destination Excel spreadsheet provider.FileName = @"MyClass.xlsx"; // Template.xlsx contains just the column headers on row 1 provider.TemplateFile = @"Template.xlsx"; // StartRow is after the header row provider.StartRow = 2; provider.OverrideFile = true; provider.InsertRecords(rows); }
public void OrdersWrite() { FileHelperEngine engine = new FileHelperEngine(typeof(OrdersExcelType)); OrdersExcelType[] resFile = (OrdersExcelType[])Common.ReadTest(engine, @"Good\OrdersWithOutDates.txt"); ExcelStorage provider = new ExcelStorage(typeof(OrdersExcelType)); provider.StartRow = 1; provider.StartColumn = 1; provider.FileName = @"c:\tempex.xls"; provider.OverrideFile = true; provider.InsertRecords(resFile); OrdersExcelType[] res = (OrdersExcelType[])provider.ExtractRecords(); if (File.Exists(@"c:\tempex.xls")) { File.Delete(@"c:\tempex.xls"); } Assert.AreEqual(resFile.Length, res.Length); for (int i = 0; i < res.Length; i++) { Assert.AreEqual(resFile[i].CustomerID, res[i].CustomerID); Assert.AreEqual(resFile[i].EmployeeID, res[i].EmployeeID); Assert.AreEqual(resFile[i].Freight, res[i].Freight); Assert.AreEqual(resFile[i].OrderID, res[i].OrderID); Assert.AreEqual(resFile[i].ShipVia, res[i].ShipVia); } }
private void saveMyBooks_Click(object sender, RoutedEventArgs e) { ExcelStorage excel = new ExcelStorage(); // Configure save file dialog box Microsoft.Win32.SaveFileDialog dlg = new Microsoft.Win32.SaveFileDialog(); dlg.FileName = "Books"; // Default file name dlg.DefaultExt = ".xlsx"; // Default file extension dlg.Filter = "Excel Document (.xlsx)|*.xlsx"; // Filter files by extension // Show save file dialog box Nullable <bool> result = dlg.ShowDialog(); // Process save file dialog box results if (result == true) { Mouse.OverrideCursor = Cursors.Wait; try { // Save document string filename = dlg.FileName; foreach (Book book in storage.GetBooks()) { excel.AddRow(book); } excel.Save(filename); } finally { Mouse.OverrideCursor = null; } } }
public static void GetRecords(string filename) { // ExcelNPOIStorage storage = new ExcelNPOIStorage(typeof(CustomerModel), 1, 0); // // Set storage file name -> represents the excel file name we want to read // storage.FileName = filename; // // Read from excel file // CustomerModel[] customers = storage.ExtractRecords() as CustomerModel[]; var provider = new ExcelStorage(typeof(CustomerModel), filename, 2, 1); CustomerModel[] customers = provider.ExtractRecords() as CustomerModel[]; foreach (var c in customers) { string email = c.EMail != null?c.EMail.Trim() : ""; bool exists = CheckCustomer(email); if (!exists) { Customer data = MapData(c); dataManager.AddMasterData(data); } else { Customer customer = dataManager.GetCustomerByEmail(email); Customer customerupdated = UpdateValues(c, customer); dataManager.UpdateMasterData(customerupdated); } } }
public void CustomersWrite() { ExcelStorage provider = new ExcelStorage(typeof(CustomersVerticalBar)); provider.StartRow = 3; provider.StartColumn = 2; provider.FileName = @"..\data\Excel\Customers.xls"; object[] res = provider.ExtractRecords(); long start = DateTime.Now.Ticks; provider.FileName = @"salida.xls"; provider.StartRow = 10; provider.StartColumn = 5; provider.InsertRecords(res); TimeSpan ts = new TimeSpan(DateTime.Now.Ticks - start); Console.WriteLine(ts.TotalSeconds); if (File.Exists(@"salida.xls")) { File.Delete(@"salida.xls"); } }
private void Main_Load(object sender, EventArgs e) { cBox_Basins.DataSource = query.gis_names; cBox_Contams.DataSource = query.contaminatesLong; cBox_TimeFrame.DataSource = query.timeframes; cBox_Selection.DataSource = query.mytypes; save.Filter = "Shapefiles (*.shp)|*.shp"; //save.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop); lbl_Status.ForeColor = Color.Green; cBox_Basins.SelectedIndex = 83; cBox_Contams.SelectedIndex = 175; cBox_TimeFrame.SelectedIndex = 3; cBox_Selection.SelectedIndex = 2; ExcelStorage provider = new ExcelStorage(typeof(Request)); provider.StartRow = 2; provider.StartColumn = 1; provider.FileName = @"C:\Users\Arnold\Desktop\query.xls"; Request[] maps = (Request[])provider.ExtractRecords(); requests = maps.ToList(); }
public void CustomersFileError() { ExcelStorage provider = new ExcelStorage(typeof(CustomersVerticalBar)); provider.FileName = @"t:\SureThatThisCanExistInAnyMachine.xls"; provider.ExtractRecords(); }
public void NoTemplate() { ExcelStorage provider = new ExcelStorage(typeof(CustomersVerticalBar), 1, 1); provider.TemplateFile = @"..\the template is not there.xls"; provider.FileName = @"output.xls"; provider.InsertRecords(new object[] { new CustomersVerticalBar() }); }
private void ImportAction_Execute(object sender, SimpleActionExecuteEventArgs e) { fmCFJSaleDoc obj = e.CurrentObject as fmCFJSaleDoc; if (obj == null) { return; } OpenFileDialog dialog = new OpenFileDialog(); if (dialog.ShowDialog() == DialogResult.OK) { // obj.Name = dialog.FileName; ExcelStorage provider = new ExcelStorage(typeof(fmSaleRecord)); provider.ErrorManager.ErrorMode = ErrorMode.ThrowException; provider.StartRow = 3; provider.StartColumn = 1; provider.FileName = dialog.FileName; fmSaleRecord[] res = (fmSaleRecord[])provider.ExtractRecords(); using (IObjectSpace os = this.ObjectSpace.CreateNestedObjectSpace()) { foreach (fmSaleRecord sr in res) { fmCFJSaleDocLine oper = os.CreateObject <fmCFJSaleDocLine>(); oper.SaleDoc = os.GetObject <fmCFJSaleDoc>(obj); oper.SaleOperation = os.FindObject <fmCFJSaleOperation>(new BinaryOperator("Code", sr.OperCode)); oper.AVTInvoiceNumber = sr.AVTInvoiceNumber; oper.AVTInvoiceDate = sr.AVTInvoiceDate; oper.DocBaseNumber = sr.DocBaseNumber; //if (sr.DocBaseDate != null) oper.DocBaseDate = sr.DocBaseDate; oper.PartyCode = sr.PartyCode; if (sr.ContractNumber == null) { sr.ContractNumber = ""; } if (sr.ContractNumber.Trim().ToUpper() == "Ã/Ï") { oper.DealNumber = "Ã/Ï"; } else { oper.DealNumber = sr.ContractNumber; } //if (sr.ContractDate != null) oper.DealDate = sr.ContractDate; oper.OrderNumber = sr.OrderCode; oper.SummAVT = Decimal.Round(sr.SummAVT, 2); oper.SummCost = Decimal.Round(sr.SummAll, 2) - oper.SummAVT; // oper.Valuta = os.FindObject<csValuta>(new BinaryOperator("Code", sr.Valuta)); // oper.SummValuta = sr.SummValuta; oper.PayNumber = sr.PayNumber; } os.CommitChanges(); } } }
public void CustomersWithSheetNameError() { ExcelStorage provider = new ExcelStorage(typeof(CustomersVerticalBar)); provider.SheetName = "SheetNotExists"; provider.FileName = @"..\data\Excel\Customers.xls"; provider.ExtractRecords(); }
public void CustomersFileError() { ExcelStorage provider = new ExcelStorage(typeof(CustomersVerticalBar)); provider.FileName = @"t:\SureThatThisCanExistInAnyMachine.xls"; Assert.Throws <FileNotFoundException>(() => provider.ExtractRecords()); }
public void OneColumn() { ExcelStorage provider = new ExcelStorage(typeof(OneColumnType), 1, 1); provider.FileName = @"..\data\Excel\OneColumn.xls"; object[] res = provider.ExtractRecords(); Assert.AreEqual(50, res.Length); }
public void CustomersWithSheetNameError() { ExcelStorage provider = new ExcelStorage(typeof(CustomersVerticalBar)); provider.SheetName = "SheetNotExists"; provider.FileName = @"..\data\Excel\Customers.xls"; Assert.Throws <ExcelBadUsageException>(() => provider.ExtractRecords()); }
public void OrdersWithDate() { ExcelStorage provider = new ExcelStorage(typeof(OrdersExcelWithDate), 1, 1); provider.FileName = @"..\data\Excel\Orders.xls"; object[] res = provider.ExtractRecords(); Assert.AreEqual(830, res.Length); }
public List <EDFSample> GetEDFsamples(string path) { ExcelStorage provider = new ExcelStorage(typeof(EDFSample)); provider.StartRow = 2; provider.StartColumn = 1; provider.FileName = path; EDFSample[] edf = (EDFSample[])provider.ExtractRecords(); return(edf.ToList()); }
public void OrdersReadWithErrors() { ExcelStorage provider = new ExcelStorage(typeof(OrdersExcelType), 1, 1); provider.FileName = @"..\data\Excel\Orders.xls"; provider.ErrorManager.ErrorMode = ErrorMode.SaveAndContinue; object[] res = provider.ExtractRecords(); Assert.AreEqual(830, res.Length); }
public void ReadExcelStorageWithCustomSheets_ReturnsAllSheets() { var provider = new ExcelStorage(typeof(ExcelXlsType)); provider.FileName = TestCommon.GetPath("Excel", "ExcelWithCustomSheets.xlsx"); Assert.AreEqual(3, provider.Sheets.Count); Assert.AreEqual("TestSheet1", provider.Sheets[0]); Assert.AreEqual("Test-Sheet-2", provider.Sheets[1]); Assert.AreEqual("Test Sheet 3", provider.Sheets[2]); }
private static ExcelXlsType[] ReadFromExcelStorage(string fileName, int stopAfterEmptyRows) { var provider = new ExcelStorage(typeof(ExcelXlsType)); provider.FileName = TestCommon.GetPath("Excel", fileName); provider.SheetName = "Sheet1"; provider.StartRow = 1; provider.ExcelReadStopAfterEmptyRows = stopAfterEmptyRows; return((ExcelXlsType[])provider.ExtractRecords()); }
public void OneColumnIgnore() { ExcelStorage provider = new ExcelStorage(typeof(OneColumnType), 1, 1); provider.ErrorManager.ErrorMode = ErrorMode.IgnoreAndContinue; provider.FileName = @"..\data\Excel\OneColumnError.xls"; object[] res = provider.ExtractRecords(); Assert.AreEqual(36, res.Length); Assert.AreEqual(0, provider.ErrorManager.ErrorCount); }
private bool IsDataTable(string filename, string sheetname) { var dataProvider = new ExcelStorage(typeof(UIMappingValueRaw)); dataProvider.FileName = filename; dataProvider.StartRow = 1; dataProvider.StartColumn = 1; dataProvider.SheetName = sheetname; var d1 = (UIMappingValueRaw[])dataProvider.ExtractRecords(); return(d1[0].Key != "Key"); }
public void CustomersRead2() { ExcelStorage provider = new ExcelStorage(typeof(CustomersVerticalBar)); provider.StartRow = 3; provider.StartColumn = 2; provider.FileName = @"..\data\Excel\Customers2.xls"; object[] res = provider.ExtractRecords(); Assert.AreEqual(91, res.Length); }
public void EnumConverter() { ExcelStorage provider = new ExcelStorage(typeof(SmallEnumType), 1, 1); provider.FileName = @"..\data\Excel\OneColumnEnum.xls"; SmallEnumType[] res = (SmallEnumType[])provider.ExtractRecords(); Assert.AreEqual(10, res.Length); Assert.AreEqual(NetVisibility.Public, res[0].Visibility); Assert.AreEqual(NetVisibility.Private, res[1].Visibility); Assert.AreEqual(NetVisibility.Protected, res[9].Visibility); }
public void CustomersWithSheetName2() { ExcelStorage provider = new ExcelStorage(typeof(CustomersVerticalBar)); provider.SheetName = "SheetWith10"; provider.StartRow = 1; provider.StartColumn = 1; provider.FileName = @"..\data\Excel\Customers.xls"; object[] res = provider.ExtractRecords(); Assert.AreEqual(10, res.Length); }
public void OrdersDateRead() { ExcelStorage provider = new ExcelStorage(typeof(OrdersDateExcelType), 1, 1); provider.FileName = @"..\data\Excel\OrdersDate.xls"; OrdersDateExcelType[] res = (OrdersDateExcelType[])provider.ExtractRecords(); Assert.AreEqual(830, res.Length); Assert.AreEqual(new DateTime(2006, 1, 1), res[0].OrderDate); Assert.AreEqual(new DateTime(2006, 3, 21), res[79].OrderDate); Assert.AreEqual(new DateTime(2007, 2, 4), res[399].OrderDate); Assert.AreEqual(new DateTime(2008, 4, 9), res[829].OrderDate); }
public static bool ImportExcelData(string sourcePath, string tableName, out DataTable dt, out string message, Hashtable param) { dt = new DataTable(); message = string.Empty; if (string.IsNullOrEmpty(sourcePath)) { message = "数据文件路径为空"; return(false); } if (!File.Exists(sourcePath)) { message = string.Format("数据文件路径[{0}]不存在", sourcePath); return(false); } bool result; using (IntegrationEngine target = new IntegrationEngine()) { ExcelStorage sourceProvider = new ExcelStorage("sourceProvider"); target.Providers.Add(sourceProvider); DataTableStorage destinationProvider = new DataTableStorage("destinationProvider"); target.Providers.Add(destinationProvider); IDictionary state3 = param; state3["sourceProvider"] = IntegrationMode.CreateSchema | IntegrationMode.GetData; state3["destinationProvider"] = IntegrationMode.TransferData; state3["tableName"] = tableName; state3[ContextState.SourcePath] = sourcePath; try { result = target.Run(state3); dt = state3["datatable"] as DataTable; if (dt == null || dt.Rows.Count == 0) { result = false; message = "没有读取到数据。"; } } catch (Exception ex) { result = false; message = ex.Message; } } return(result); }
private void StripNonSettlementWorksheets(FileInfo NewFile) { using (ExcelStorage excelProvider = new ExcelStorage(typeof(Settlement)) { FileName = NewFile.FullName }) { foreach (string item in excelProvider.SheetNames) { if (item != "Settlement Report") { excelProvider.RemoveSheet(item); } } } }
public void OneColumnSave() { ExcelStorage provider = new ExcelStorage(typeof(OneColumnType), 1, 1); provider.ErrorManager.ErrorMode = ErrorMode.SaveAndContinue; provider.FileName = @"..\data\Excel\OneColumnError.xls"; object[] res = provider.ExtractRecords(); Assert.AreEqual(36, res.Length); Assert.AreEqual(4, provider.ErrorManager.ErrorCount); Assert.AreEqual(8, provider.ErrorManager.Errors[0].LineNumber); Assert.AreEqual(16, provider.ErrorManager.Errors[1].LineNumber); Assert.AreEqual(20, provider.ErrorManager.Errors[2].LineNumber); Assert.AreEqual(28, provider.ErrorManager.Errors[3].LineNumber); }
public void EnumConverterBad() { ExcelStorage provider = new ExcelStorage(typeof(SmallEnumType), 4, 2); provider.FileName = @"..\data\Excel\OneColumnEnumBad.xls"; provider.ErrorManager.ErrorMode = ErrorMode.SaveAndContinue; SmallEnumType[] res = (SmallEnumType[])provider.ExtractRecords(); Assert.AreEqual(9, res.Length); Assert.AreEqual(NetVisibility.Public, res[0].Visibility); Assert.AreEqual(NetVisibility.Private, res[1].Visibility); Assert.AreEqual(NetVisibility.Protected, res[8].Visibility); Assert.AreEqual(1, provider.ErrorManager.ErrorCount); Assert.AreEqual(8, provider.ErrorManager.Errors[0].LineNumber); Assert.AreEqual("BadValue", provider.ErrorManager.Errors[0].RecordString); }
public SequenceGroup GetCommandSequences(string filename, string[] sequences) { var sg = new SequenceGroup(); ExcelStorage dataProvider = new ExcelStorage(typeof(UICommand)); dataProvider.StartRow = 2; dataProvider.StartColumn = 1; dataProvider.FileName = filename; foreach (var sheetName in sequences) { dataProvider.SheetName = sheetName; var commands = (UICommand[])dataProvider.ExtractRecords(); sg.Sequences.Add(sheetName, new UICommandContainer(sheetName, commands.Where(x => x.Enabled.ToLower() == "y" || x.Enabled.ToLower() == "yes").ToArray())); } return(sg); }