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