static void Main(string[] args) { const string excelPath = "c:/temp/excel/Employees.xlsx"; var excel = new ExcelQueryFactory(excelPath); var employees = from e in excel.Worksheet<Employee>("Employee") select e; Console.WriteLine("All Employees"); Console.WriteLine(""); WriteInfo(employees); Console.WriteLine(""); Console.WriteLine(""); var managers = from e in excel.Worksheet<Employee>("Employee") where e.IsManager select e; Console.WriteLine("Managers"); Console.WriteLine(""); WriteInfo(managers); Console.WriteLine(""); Console.WriteLine(""); var regularEmployees = from e in excel.Worksheet<Employee>("Employee") where e.IsManager == false select e; Console.WriteLine("Regular Employees"); Console.WriteLine(""); WriteInfo(regularEmployees); Console.WriteLine(""); Console.Read(); }
public IEnumerable<Product> GetProductsFromFile(string filename) { var excel = new ExcelQueryFactory(filename); var worksheetNames = excel.GetWorksheetNames().ToArray(); // //Mapping // for (int i = 0; i < worksheetNames.Length; i++) { var columnNames = excel.GetColumnNames(worksheetNames[i]); var titleRow = FindTitleRow(excel.WorksheetNoHeader(worksheetNames[i])); var codeIndex = 0; var priceIndex = GetColumnIndex(titleRow, "Price"); bool skip = true; foreach (var product in excel.WorksheetNoHeader(worksheetNames[i]) .Where(x => x[codeIndex] != "")) { if (skip) { skip = product[codeIndex].ToString().Trim() != "No.:"; continue; } yield return new Product { Code = product[codeIndex].ToString().Trim(), Price = product[priceIndex].Cast<decimal>() }; } } }
public void s() { _repo = new ExcelQueryFactory { FileName = _excelFileName }; _repo.AddMapping<ChineseNameColumn>(x => x.ChineseColumn, "第一列"); _repo.AddMapping<ChineseNameColumn>(x => x.DoubleColumn, "第二列(复杂的:浮点-》"); _repo.AddMapping<ChineseNameColumn>(x => x.ThirdColumn, "第3列:\"待引号的\""); }
private void btnSelectFile_Click(object sender, EventArgs e) { OpenFileDialog openFileDialog = new OpenFileDialog(); openFileDialog.InitialDirectory = Environment.CurrentDirectory; // openFileDialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Personal); openFileDialog.Filter = "Excel(*.xlsx)|*.xlsx|Excel 2000-2003(*.xls)|*.xls|CSV(*.csv)|*.csv|所有文件(*.*)|*.*"; if (openFileDialog.ShowDialog(this) == DialogResult.OK) { Cursor.Current = Cursors.WaitCursor; string FileName = openFileDialog.FileName; var execelfile = new ExcelQueryFactory(FileName); this.txtContact.Text = FileName; this.txtContact.Enabled = false; List<string> sheetNames= execelfile.GetWorksheetNames().ToList(); //星级信息 List<Row> agentStar = execelfile.Worksheet(0).ToList(); ; if (agentStar != null && agentStar.Count > 0) { this.btnImport.Enabled = true; dgAgentStar.Rows.Clear(); dgAgentStar.Columns.Clear(); foreach (String coloumn in agentStar[0].ColumnNames) { this.dgAgentStar.Columns.Add(coloumn, coloumn); } for (int i = 0; i < agentStar.Count; i++) { if (String.IsNullOrEmpty(agentStar[i][0])) { continue; } dgAgentStar.Rows.Add(); DataGridViewRow row = dgAgentStar.Rows[i]; foreach (String coloumn in agentStar[0].ColumnNames) { row.Cells[coloumn].Value = agentStar[i][coloumn]; } } } dgAgentStar.ColumnHeadersDefaultCellStyle.WrapMode = DataGridViewTriState.False; dgAgentStar.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill; dgAgentStar.AutoResizeColumns(); Cursor.Current = Cursors.Default; } }
public ActionResult Index() { foreach (string upload in Request.Files) { // if (!Request.Files[upload]) continue; string path = AppDomain.CurrentDomain.BaseDirectory + "uploads/"; string filename = Path.GetFileName(Request.Files[upload].FileName); string pathToExcelFile = Path.Combine(path, filename); string sheetName = "Sheet1"; Request.Files[upload].SaveAs(pathToExcelFile); var excelFile = new ExcelQueryFactory(pathToExcelFile); var data = from a in excelFile.Worksheet<ExcelData>(sheetName) select a; using (var ctx = new Context()) { foreach (var a in data) { ctx.ExcelData.Add(a); //Console.WriteLine(string.Format(artistInfo, a.Account, a.Account)); } ctx.SaveChanges(); } } return View(); }
public List<string> Import(string path, IEnumerable<string> regions) { var factory = new ExcelQueryFactory {FileName = path}; var message = (from region in regions let stats = (from c in factory.Worksheet<CdmaRegionStatExcel>(region) where c.StatDate > DateTime.Today.AddDays(-30) && c.StatDate <= DateTime.Today select c).ToList() let count = _regionStatRepository.Import<ICdmaRegionStatRepository, CdmaRegionStat, CdmaRegionStatExcel>(stats) select "完成导入区域:'" + region + "'的日常指标导入" + count + "条").ToList(); var topDrops = (from c in factory.Worksheet<TopDrop2GCellExcel>(TopDrop2GCellExcel.SheetName) select c).ToList(); var drops = _top2GRepository.Import<ITopDrop2GCellRepository, TopDrop2GCell, TopDrop2GCellExcel>(topDrops); message.Add("完成TOP掉话小区导入" + drops + "个"); var topConnections = (from c in factory.Worksheet<TopConnection3GCellExcel>(TopConnection3GCellExcel.SheetName) select c).ToList(); var connections = _top3GRepository.Import<ITopConnection3GRepository, TopConnection3GCell, TopConnection3GCellExcel>( topConnections); message.Add("完成TOP连接小区导入" + connections + "个"); var topConnection2Gs = (from c in factory.Worksheet<TopConnection2GExcel>(TopConnection2GExcel.SheetName) select c).ToList(); var connection2Gs = _topConnection2GRepository.Import<ITopConnection2GRepository, TopConnection2GCell, TopConnection2GExcel> (topConnection2Gs); message.Add("完成TOP呼建小区导入" + connection2Gs + "个"); return message; }
protected void btnUpload_FileSelected(object sender, EventArgs e) { if (btnUpload.HasFile) { string fileName = btnUpload.ShortFileName; if (!fileName.EndsWith(".xlsx")) { Alert.Show("只支持xlsx文件类型!"); return; } string path = ConfigHelper.GetStringProperty("DataFilePath", @"D:\root\Int\data\") + "tmp\\" + fileName; btnUpload.SaveAs(path); var execelfile = new ExcelQueryFactory(path); execelfile.AddMapping<UserInfo>(x => x.Code, "登录帐号"); execelfile.AddMapping<UserInfo>(x => x.Name, "姓名"); execelfile.AddMapping<UserInfo>(x => x.NameEn, "英文名"); execelfile.AddMapping<UserInfo>(x => x.Departments, "部门"); execelfile.AddMapping<UserInfo>(x => x.JobName, "职位"); execelfile.AddMapping<UserInfo>(x => x.Phone, "电话"); execelfile.AddMapping<UserInfo>(x => x.Email, "电子邮箱"); execelfile.AddMapping<UserInfo>(x => x.LeaderCode, "上级领导"); var list = execelfile.Worksheet<UserInfo>(0).ToList(); Import(list); this.userGrid.PageIndex = 0; GridBind(); Alert.Show("上传成功"); } }
public bool ImportStudentFromExcel(string filePath) { bool result = false; if (!string.IsNullOrEmpty(filePath)) { var excelFile = new ExcelQueryFactory(filePath); List<string> sheetnames = excelFile.GetWorksheetNames().ToList(); //excelFile.AddMapping<PayrollItemDataEntryCustom>(x => x.Remarks, PayrollItemDataEntry.Remarks.ToString()); //excelFile.AddMapping<PayrollItemDataEntryCustom>(x => x.filePath, PayrollItemDataEntry.filePath.ToString()); //excelFile.AddMapping<PayrollItemDataEntryCustom>(x => x.sheetNames, "tstSheet1");//PayrollItemDataEntry.sheetNames.ToString() excelFile.AddMapping<Student>(x => x.SID, "SID"); excelFile.AddMapping<Student>(x => x.Name,"Name"); excelFile.AddMapping<Student>(x => x.Minor, "Minor"); excelFile.AddMapping<Student>(x => x.Major, "Major"); excelFile.AddMapping<Student>(x => x.Division, "Division"); excelFile.StrictMapping = StrictMappingType.ClassStrict; excelFile.TrimSpaces = TrimSpacesType.Both; excelFile.ReadOnly = true; var AllExcellData = (from ExcelData in excelFile.Worksheet(0) select ExcelData).ToList(); } return result; }
static void Main(string[] args) { // Получение информации из Excel документа с именем SampleData.xls string pathFile = "SampleData.xls"; var excelFile = new ExcelQueryFactory(pathFile); // Вывод списка листов (Worksheet) в Excel документе foreach( var item in excelFile.GetWorksheetNames()) { Console.Write(item+" "); } Console.Write("\n"); foreach (var item in excelFile.GetWorksheetNames()) { Console.Write(item + ":"); foreach (var itemRow in excelFile.GetColumnNames(item)) { Console.Write(itemRow + " "); } Console.Write("\n"); } // установка документа "только для чтения" excelFile.ReadOnly = true; Console.ReadKey(); }
private static List<PuzzleGame> GetGamesFromPuzzleGameWorksheet(ExcelQueryFactory excel, int puzzleSubGroupId) { //Get all games for a sub group var games = from c in excel.Worksheet("PuzzleGame") select new { PuzzleGameId = c["PuzzleGameId"].Cast<int>(), PuzzleSubGroupId = c["PuzzleSubGroupId"].Cast<int>(), Word = c["Word"].Cast<string>(), Hint = c["Hint"].Cast<string>() }; var gamelist = games.ToList(); var grouped = from c in gamelist where c.PuzzleSubGroupId == puzzleSubGroupId group c by c.PuzzleGameId into g select new {PuzzleGameId = g.Key, Games = g}; var puzzleGames = new List<PuzzleGame>(); foreach (var gamesSet in grouped) { var puzzleGame = new PuzzleGame() {PuzzleGameId = gamesSet.PuzzleGameId, Words = new Dictionary<string, string>()}; foreach (var game in gamesSet.Games) { puzzleGame.Words.Add(game.Word, game.Hint); Console.WriteLine(game.Word); } puzzleGames.Add(puzzleGame); } return puzzleGames; }
public ActionResult ReconciliationUpload(Guid fileid) { var model = new List<UploadReconciliationModel>(); var uploadFilePath = ConfigurationManager.AppSettings["UploadFilePath"]; if (!Directory.Exists(uploadFilePath)) { Directory.CreateDirectory(uploadFilePath); } var fileNamePrefix = fileid.ToString(); var file = System.Web.HttpContext.Current.Request.Files[0]; if (file.ContentLength > 0) { var fileName = Path.GetFileName(file.FileName); //var fileExtension = Path.GetExtension(fileName); //var newFileName = fileNamePrefix + fileExtension; //var fileFullPath = uploadFilePath + newFileName; var fileFullPath = uploadFilePath + fileNamePrefix; try { file.SaveAs(fileFullPath); //return RedirectToAction("Reconciliation", new { filePath = uploadFilePath }); var excel = new ExcelQueryFactory(fileFullPath); model = excel.Worksheet<UploadReconciliationModel>(0).ToList(); } catch (Exception) { System.IO.File.Delete(fileFullPath); } } return View(model.ToArray()); }
public static IList<CollectionImportModel> Read(string fileName) { var data = new ExcelQueryFactory(fileName); SetupColumnMapping(data); SetupTransforms(data); return data.Worksheet<CollectionImportModel>().ToList(); }
public static IEnumerable<Route> Import(string filename, ClimbrContext context) { var excel = new ExcelQueryFactory(filename); var sheetRoutes = excel.Worksheet<SheetRoute>() .Where(r => r.Grade != "#REF!" && r.Grade != null) .ToList(); for (int i = 1; i < sheetRoutes.Count; i++) { if (sheetRoutes[i].Line == null || sheetRoutes[i].Line == "#REF!") { sheetRoutes[i].Line = sheetRoutes[i - 1].Line; } } var routes = sheetRoutes.Select( sr => new Route { Color = context.Colors.Single(c => c.Name.Equals(sr.Colour, StringComparison.InvariantCultureIgnoreCase)), Grade = context.Grades.Single(g => g.Name.Equals(sr.Grade, StringComparison.InvariantCultureIgnoreCase)), Name = sr.Line }) .ToList(); return routes; }
private void button1_Click(object sender, EventArgs e) { OpenFileDialog fileDialog = new OpenFileDialog(); if (fileDialog.ShowDialog() != System.Windows.Forms.DialogResult.OK) { return; } var sheet = new ExcelQueryFactory(); sheet.FileName = fileDialog.FileName; var dinvList = sheet.Worksheet<DINV_HDR>(0); foreach (var item in dinvList) { } var list = sheet.Worksheet(0).ToList(); foreach (var item in list) { } foreach (var item in sheet.Worksheet(0)) { } }
private void button1_Click(object sender, EventArgs e) { OpenFileDialog fdlg = new OpenFileDialog(); if (DialogResult.OK == fdlg.ShowDialog()) { var excel = new ExcelQueryFactory(fdlg.FileName); var indianaCompanies = from c in excel.Worksheet("Sheet1") select c; int ix = 0; int il = 0; foreach (var item in indianaCompanies) { TblInfor newInfo = new TblInfor(); string content = "<b>" + item[1].ToString().Trim() + "</b> <br />" + "" + item[10].ToString().Trim() + "<br>Khu vực: " + item[9].ToString().Trim() + "<br>ĐC: " + item[4].ToString().Trim() + "<br> Số Phòng: " + item[6].ToString().Trim() + "<br> ĐT: " + item[2].ToString().Trim() + " - FAX: " + item[3].ToString().Trim() + "<br> Email: " + item[5].ToString().Trim() + "<br> Chủ đầu tư: " + item[7].ToString().Trim() + "<br>Giám đốc: " + item[8].ToString().Trim(); string tag = "Khách sạn, khach san, ks, " + item[11].ToString().Trim() + ", " + item[2].ToString().Trim(); newInfo.CreateDate = DateTime.Now; newInfo.CreateUserID = Guid.Parse("b0b6997c-282b-4aff-b286-b0820cdb0100"); newInfo.ID = Guid.NewGuid(); newInfo.Category = Categories.KhachSan; newInfo.Location = item[9].ToString().Trim(); newInfo.InfoContent = HtmlRemoval.StripTagsRegexCompiled(content); newInfo.InfoContentHtml = content; newInfo.InfoTag = tag; newInfo.InfoTitle = item[1].ToString().Trim() + " " + item[9].ToString().Trim(); newInfo.InfoType = InformationType.NoLimit; newInfo.Status = InformationStatus.Approved; newInfo.UpdateDate = DateTime.Now; newInfo.UpdateUserID = Guid.Parse("b0b6997c-282b-4aff-b286-b0820cdb0100"); try { BBLInfo b = new BBLInfo(); b.InsertOrUpdate(newInfo); ix++; } catch (Exception ex) { Console.WriteLine("E - > " + ex.Message); // DevExpress.XtraEditors.XtraMessageBox.Show(DevExpress.LookAndFeel.UserLookAndFeel.Default, this.FindForm(), ex.Message, "Có sự cố", MessageBoxButtons.OK, MessageBoxIcon.Warning); il++; } } Console.WriteLine(ix + " | " + il); } }
public void bad_column_mapping_in_where_clause() { var excel = new ExcelQueryFactory(_excelFileName); excel.AddMapping<CompanyWithCity>(x => x.City, "Town"); var list = (from x in excel.Worksheet<CompanyWithCity>("Sheet1") where x.City == "Omaha" select x).ToList(); }
public void GetWorksheetNames_does_not_include_builtin_worksheets() { var excel = new ExcelQueryFactory(_excelFileWithBuiltinWorksheets); var worksheetNames = excel.GetWorksheetNames(); Assert.AreEqual( "AutoFiltered, ColumnMappings, MoreCompanies, NullCells, Paul's Worksheet, Sheet1", string.Join(", ", worksheetNames.ToArray())); }
static void Main(string[] args) { var file = @"NamedRange.xlsb"; var excel = new ExcelQueryFactory(file); var unique = excel.NamedRange("NamedRangeUniqueCell"); var multiple = excel.NamedRange("NamedRangeMultipleCells"); }
//[Test] This test is no longer passing. I believe it has something to do with my computer settings public void GetWorksheetNames_does_not_include_named_ranges() { var excel = new ExcelQueryFactory(_excelFileWithNamedRanges); var worksheetNames = excel.GetWorksheetNames(); Assert.AreEqual( "Tabelle1, Tabelle3, WS2", string.Join(", ", worksheetNames.ToArray())); }
public ManipuladorExcel(string nombreArchivo) { _excel = new ExcelQueryFactory(nombreArchivo) { ReadOnly = true, UsePersistentConnection = true }; }
private IEnumerable<ExcelRow> GetExcelRowEnumerableFromExcelFile(string filePath) { var excel = new ExcelQueryFactory(); excel.FileName = filePath; IEnumerable<ExcelRow> query = from e in excel.Worksheet<ExcelRow>(0) select e; return query; }
public void range_csv_file_throws_exception() { var csvFile = @"C:\ExcelFiles\NoHeaderRange.csv"; var excel = new ExcelQueryFactory(csvFile); var companies = (from c in excel.WorksheetRangeNoHeader("B9", "E15") select c).ToList(); }
public void bad_column_in_sum_aggregate() { var excel = new ExcelQueryFactory(_excelFileName); excel.AddMapping<CompanyWithCity>(x => x.EmployeeCount, "Employees"); var list = (from x in excel.Worksheet<CompanyWithCity>("Sheet1") select x) .Sum(x => x.EmployeeCount); }
public void ImportCdmaParameters(string path) { var repo = new ExcelQueryFactory { FileName = path }; BtsExcels = (from c in repo.Worksheet<BtsExcel>("基站级") select c).ToList(); CdmaCellExcels = (from c in repo.Worksheet<CdmaCellExcel>("小区级") select c).ToList(); }
public void use_row_no_header_where_null() { var factory = new ExcelQueryFactory(_excelFileName + "x"); var companies = from c in factory.WorksheetRangeNoHeader("A1", "D4", "NullCells") where c[2] == null select c; Assert.AreEqual(2, companies.Count(), "Count"); }
public void use_row_where_null() { var factory = new ExcelQueryFactory(_excelFileName + "x"); var companies = from c in factory.NamedRange("NullCellCompanies") where c["EmployeeCount"] == null select c; Assert.AreEqual(2, companies.Count(), "Count"); }
public void GetColumnNames_returns_column_names() { var excel = new ExcelQueryFactory(_excelFileName); var columnNames = excel.GetColumnNames("Sheet1"); Assert.AreEqual( "Name, CEO, EmployeeCount, StartDate", string.Join(", ", columnNames.ToArray())); }
private static void AddLibrariesToDataStore(ExcelQueryFactory file) { var allLibraries = from f in file.Worksheet<FlatLibrary>() select FlatLibraryToLibrary(f); var libraryList = allLibraries.ToList().Distinct(); Console.WriteLine(allLibraries.Count()); }
public void where_is_null() { var factory = new ExcelQueryFactory(_excelFileName.Replace("NoHeader.xls", "Companies.xlsx")); var oldCompanies = from c in factory.WorksheetNoHeader("NullCells") where c[2] == null select c; Assert.AreEqual(2, oldCompanies.Count(), "Company Count"); }
public void GetWorksheetNames_returns_worksheet_names() { var excel = new ExcelQueryFactory(_excelFileName); var worksheetNames = excel.GetWorksheetNames(); Assert.AreEqual( "ColumnMappings, IMEX Table, More Companies, Null Dates, Range1, Sheet1, TrimSpaces", string.Join(", ", worksheetNames.ToArray())); }
public XLSLoader(string fileName, string sheetName = "бакалавр", TeachersModelContainer context = null) { if (context == null) { _context = new ModelContext(); } else { _context = context; } _excel = new ExcelQueryFactory(fileName); SheetName = sheetName; _rawData = (from c in _excel.WorksheetNoHeader(SheetName) select c).ToList(); _moduleOffsets = new Dictionary <Module, List <int> >(); _groupOffsets = new Dictionary <Group, int>(); }
public void Null_ConstantExpression_in_where_expression_should_not_throw_exception() { // The C# 6 compiler made some changes to the expression tree output. // This changes are largely transparent to consumers if they adhere // to the Liskov Substitution Principle. This test is to specifically // address a bug in which the code didn't allow the derived TypedConstantExpression // to be substituted for a ConstantExpression. This resulted in a downstream InvalidCastException // when the LinqToExcel invoked Converter.ChangeType since TypedConstantExpression // does not implement IConvertible. var excel = new ExcelQueryFactory(_excelFileName); excel.AddMapping <Company>(x => x.IsActive, "Active"); var companies = (from c in excel.Worksheet <CompanyNullable>() where c.Name != null select c).ToList(); Assert.IsNotNull(companies); }
public void IAllowFieldTypeConversionExceptions_GathersExceptions() { var excel = new ExcelQueryFactory(_excelFileName, new LogManagerFactory()); excel.AddMapping <Company>(x => x.IsActive, "Active"); var companies = (from c in excel.Worksheet <CompanyBadWithAllowFieldTypeConversionExceptions>() where c.Name == "ACME" select c).ToList(); Assert.AreEqual(1, companies.Count); var company = companies[0]; Assert.AreEqual("ACME", company.Name); Assert.AreEqual(2, company.FieldTypeConversionExceptions.Count); Assert.AreEqual("CEO", company.FieldTypeConversionExceptions[0].ColumnName); Assert.AreEqual("EmployeeCount", company.FieldTypeConversionExceptions[1].ColumnName); }
public void WorkSheetNamesAreDecodedCorrectly() { var fileName = Path.Combine(_filesDirectory, "WorksheetNames.xlsx"); var workbook = new ExcelQueryFactory(fileName, new LogManagerFactory()); var worksheetNames = workbook.GetWorksheetNames(); CollectionAssert.AreEqual( new [] { " ' ", "$woot$", "Emb$dded", "Ends with $'\"", "Ends with a $", "Has a $ in it" }, worksheetNames ); }
public int GetRowFromEXL(string filepath, string Usertype) { //var pathToExcelFile = @"D:\pegqa\TestAutomationProject\PegasusTests\Screenshots\EXLFile\AgentImport.xlsx"; var excelFile = new ExcelQueryFactory(filepath).Worksheet(0); var artistAlbums = from a in excelFile select a; int i = 0; foreach (var a in artistAlbums) { if (a["UserType"] == Usertype) { i++; Console.WriteLine("New Check"); } } Thread.Sleep(1000); return(i); }
public void IAllowFieldTypeConversionExceptions_NoExceptionsWhenFieldsAreGood() { var excel = new ExcelQueryFactory(_excelFileName, new LogManagerFactory()); excel.AddMapping <Company>(x => x.IsActive, "Active"); var companies = (from c in excel.Worksheet <CompanyGoodWithAllowFieldTypeConversionExceptions>() where c.Name == "ACME" select c).ToList(); Assert.AreEqual(1, companies.Count); var company = companies[0]; Assert.AreEqual("ACME", company.Name); Assert.AreEqual("Bugs Bunny", company.CEO); Assert.AreEqual(25, company.EmployeeCount); Assert.AreEqual(0, company.FieldTypeConversionExceptions.Count); }
public IEnumerable <CI004_RFDS_SECTOR_IN_CSS> GetListCI004_RFDS_SECTOR_IN_CSS(string filename) { var excel = new ExcelQueryFactory(); excel.FileName = filename; var x = excel.GetWorksheetNames(); var query = (from s in excel.WorksheetRange <CI004_RFDS_SECTOR_IN_CSS>("A1", "XFD1048576", 3) select s).ToList(); //List<CI004_RFDS_SECTOR_IN_CSS> lstRFDS = new List<CI004_RFDS_SECTOR_IN_CSS>(); //foreach (CI004_RFDS_SECTOR_IN_CSS item in query) //{ // lstRFDS.Add(new CI004_RFDS_SECTOR_IN_CSS // { // USID = item.USID, // RFDS_NAME = item.RFDS_NAME, // PROGRAM_TYPE = item.PROGRAM_TYPE, // TECHNOLOGY = item.TECHNOLOGY, // CARRIER = item.CARRIER, // SECTOR = item.SECTOR, // USEID = item.USEID, // RBSID = item.RBSID, // DELETE_SOFT_SECTOR = item.DELETE_SOFT_SECTOR, // CTS_COMMONID = item.CTS_COMMONID, // SOFT_SECTORID = item.SECTOR, // CELL_NUMBER_LTE = item.CELL_NUMBER_LTE, // CID_SAC = item.CID_SAC, // RFDSID = item.RFDSID, // SITEMASTER = item.SITEMASTER, // SECTOR_LATITUDE = item.SECTOR_LATITUDE, // SECTOR_LONGITUDE = item.SECTOR_LONGITUDE, // RBSS_ISACTIVE = item.RBSS_ISACTIVE, // REMARKS = item.REMARKS, // SPECTRUM_BUCKET_1 = item.SPECTRUM_BUCKET_1, // SPECTRUM_BUCKET_2 = item.SPECTRUM_BUCKET_2, // SPECTRUM_USID = item.SPECTRUM_USID // }); //} //return lstRFDS; return(query); }
public void UpdateDepartments() { var excel = new ExcelQueryFactory(); string[] ex_names = MvcApplication.Config("excel_name").Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries); for (int ex_name = 0; ex_name < ex_names.Length; ex_name++) { excel.FileName = ex_names[ex_name]; var result_employees = from c in excel.WorksheetNoHeader(0) select c; List <Department> existing_deps = (List <Department>)GetSession.QueryOver <Department>().List(); List <string> existing_dep_list = new List <string>(); foreach (var dep in existing_deps) { if (!existing_dep_list.Contains(dep.Name)) { existing_dep_list.Add(dep.Name); } } foreach (var new_dep in result_employees) { if (!string.IsNullOrEmpty(new_dep[4].ToString().Trim()) && !existing_dep_list.Contains(new_dep[4].ToString().Trim())) { DepartmentFormModel model = new DepartmentFormModel() { Name = new_dep[4].ToString().Trim() }; var item = Mapper.Map <DepartmentFormModel, Department>(model); GetSession.Save(item); existing_dep_list.Add(new_dep[4].ToString().Trim()); } } } }
public void xls_readonly_with_Ace_DatabaseEngine_connection_string() { var excel = new ExcelQueryFactory("spreadsheet.xls") { DatabaseEngine = ExcelDatabaseEngine.Ace, ReadOnly = true }; var companies = from c in excel.Worksheet() select c; try { companies.GetEnumerator(); } catch (OleDbException) { } var expected = $@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={"spreadsheet.xls" };Extended Properties=""Excel 12.0;HDR=YES;IMEX=1;READONLY=TRUE"""; Assert.AreEqual(expected, GetConnectionString()); }
public void GetNamedRanges() { var excel = new ExcelQueryFactory(_excelFileWithNamedRanges); string sheetName = excel.GetWorksheetNames().First(); Assert.AreEqual(sheetName, "Tabelle1"); var namedRanges = excel.GetNamedRanges("NameCell"); // ExcelQueryArgs args = new ExcelQueryArgs // { // FileName = excel.FileName, // WorksheetName = "NameCell", // ReadOnly = true // }; // var namedRanges = ExcelUtilities.GetNamedRanges(args); Assert.AreEqual( "NameCell", string.Join(", ", namedRanges.ToArray())); }
public void no_header_readonly_connection_string_suppress_transactionscope() { var excel = new ExcelQueryFactory("spreadsheet.xls", new LogManagerFactory()); excel.ReadOnly = true; excel.OleDbServices = Query.OleDbServices.AllServicesExceptPoolingAndAutoEnlistment; var companies = from c in excel.WorksheetNoHeader() select c; try { companies.GetEnumerator(); } catch (OleDbException) { } string expected = string.Format( @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};OLE DB Services=-4;Extended Properties=""Excel 12.0;HDR=NO;IMEX=1;READONLY=TRUE""", "spreadsheet.xls" ); Assert.AreEqual(expected, GetConnectionString()); }
/// <summary> /// Card massive import procedure using LinqToExcel /// </summary> private void ImportCards() { var fileName = string.Concat(Server.MapPath(TmpDir), DateTime.Now.ToString("yyyyMMdd-HHmmss"), ".xls"); try { fuImportData.SaveAs(fileName); var linea = ddlBaseTarjeta.Selected > 0 ? DAOFactory.LineaDAO.FindById(ddlBaseTarjeta.Selected) : null; var empresa = ddlDistritoTarjeta.Selected > 0 ? DAOFactory.EmpresaDAO.FindById(ddlDistritoTarjeta.Selected) : linea != null ? linea.Empresa : null; var file = new ExcelQueryFactory(fileName); var data = from c in file.Worksheet <TarjetaImportador>("Importar") select c; foreach (var cardImp in data) { var card = SetCardDefaultValues(cardImp, empresa, linea); if (string.IsNullOrEmpty(card.Numero) || string.IsNullOrEmpty(card.Pin)) { continue; } var chofer = !string.IsNullOrEmpty(cardImp.Legajo) ? DAOFactory.EmpleadoDAO.GetByLegajo(ddlDistritoTarjeta.Selected, ddlBaseTarjeta.Selected, cardImp.Legajo.Trim()) : null; if (chofer != null) { chofer.Tarjeta = card; DAOFactory.EmpleadoDAO.SaveOrUpdate(chofer); } else { DAOFactory.TarjetaDAO.SaveOrUpdate(card); } } infoLabel1.Mode = InfoLabelMode.INFO; infoLabel1.Text = @"Se ha finalizado con exito la importacion de Tarjetas."; } catch (Exception e) { ShowError(new Exception(CultureManager.GetError(FileFormat), e)); } finally { File.Delete(fileName); } }
public List <Student> GetDataFromExcel() { List <Student> list = new List <Student>(); Console.WriteLine("Path to excel file :"); string pathToExcelFile = Console.ReadLine(); string ext = Path.GetExtension(pathToExcelFile); if (ext.ToLower().Equals(".xls") || ext.ToLower().Equals(".xlsx")) { Console.WriteLine("Sheet Name :"); string sheetName = Console.ReadLine(); var excelFile = new ExcelQueryFactory(pathToExcelFile); var dataExcel = from a in excelFile.Worksheet(sheetName) select a; foreach (var a in dataExcel) { try { string info = $"ID:{a[0].Value} Name: {a[1].Value}; AvgMark: {a[2].Value}; AccumulationCredit: {a[3].Value}; BirthDay{a[4].Value}"; Console.WriteLine(info); var ID = a[0].Cast <int>(); var Name = a[1].Value.ToString(); var AvgMark = a[2].Cast <float>(); var AccumulationCredit = a[3].Cast <int>(); var BirthDay = a[4].Cast <DateTime>(); Student student = new Student(ID, Name, BirthDay, AvgMark, AccumulationCredit); //tree.Insert(new Node<Student>(student)); list.Add(student); } catch (Exception ex) { Console.WriteLine("Something wrong with data !Please try again !"); } } return(list); } else { Console.WriteLine("This isn't a excel file!"); return(null); } }
private void Button_Click(object sender, EventArgs e) { string path = ""; DialogResult fileToOpenDialog = openFileDialog1.ShowDialog(); if (fileToOpenDialog == DialogResult.OK) // Test result. { path = openFileDialog1.FileName; } textBox1.Text = path; List <Player> players = new List <Player>(); string pathToExcelFile = textBox1.Text; string sheetName = "Arkusz1"; var excelFile = new ExcelQueryFactory(pathToExcelFile); var playersInExcel = from a in excelFile.Worksheet(sheetName) select a; foreach (var a in playersInExcel) { players.Add( new Player() { name = a["Name"], handicap = Convert.ToInt32(a["handicap"]), card = new Card() { scores = new int[] { Convert.ToInt32(a["h1"]), Convert.ToInt32(a["h2"]), Convert.ToInt32(a["h3"]), Convert.ToInt32(a["h4"]), Convert.ToInt32(a["h5"]), Convert.ToInt32(a["h6"]), Convert.ToInt32(a["h7"]), Convert.ToInt32(a["h8"]), Convert.ToInt32(a["h9"]), Convert.ToInt32(a["h10"]), Convert.ToInt32(a["h11"]), Convert.ToInt32(a["h12"]), Convert.ToInt32(a["h13"]), Convert.ToInt32(a["h14"]), Convert.ToInt32(a["h15"]), Convert.ToInt32(a["h16"]), Convert.ToInt32(a["h17"]), Convert.ToInt32(a["h18"]) } } } ); } Course course = new Course(); Init(course); var prog = new Calculate(); result = prog.Go(course, players); }
/********* * Function: cmGetCellNames * Parameters: none * Returns: cmCell array of cells read from allCells.xlsx file * Operation Summary: * Read the xlsx file and edit Huawei cells to cellVendorType Huawei and then return the array *********/ public static cmCell[] cmGetCellNames() { //Opening allCells.xlsx file with read-only mode and mapping to the cmCell Class var cellNamesFile = new ExcelQueryFactory("excelFiles/allCells.xlsx"); cellNamesFile.ReadOnly = true; cellNamesFile.UsePersistentConnection = true; cellNamesFile.AddMapping <cmCell>(x => x.cellName, "Cell"); cmCell[] cellNames; //the try and catch is for closing connection for allCells.xlsx file try { //Get the Huawei cell names from the file and change to array var cellNamesHuawei = (from x in cellNamesFile.Worksheet <cmCell>("HU Cells") where x.cellName != "" select x).ToArray <cmCell>(); //Get the Ericsson cell names from the file and change to array var cellNamesEricsson = (from x in cellNamesFile.Worksheet <cmCell>("ER Cells") where x.cellName != "" select x).ToArray <cmCell>(); //Change the vendor type for Huawei cells to Huawei as the default vendor type for no-parameters cmCell constructor is Ericsson for (int i = 0; i < cellNamesHuawei.Length; i++) { cellNamesHuawei[i].cellVendor = cellVendorType.Huawei; } //Make Ericsson cell names all uppercase for (int i = 0; i < cellNamesEricsson.Length; i++) { cellNamesEricsson[i].cellName = cellNamesEricsson[i].cellName.ToUpper(); } //Concatinate the two arrays cellNames = new cmCell[cellNamesEricsson.Length + cellNamesHuawei.Length]; cellNamesEricsson.CopyTo(cellNames, 0); cellNamesHuawei.CopyTo(cellNames, cellNamesEricsson.Length); } finally { //Close connection cellNamesFile.Dispose(); } return(cellNames); }
private bool ProcessFile(string securityID, FileUploadType fileType) { try { // Create connection to excel file ExcelQueryFactory connection = new ExcelQueryFactory(filePath); //Query a worksheet with a header row var rows = from row in connection.Worksheet(0) select row; // Fill stock list foreach (var row in rows) { var result = new StockPriceRepository().Add(new StockPriceVm { SecurityId = securityID, Date = Convert.ToDateTime(row[0]), Open = Convert.ToDecimal(row[1]), High = Convert.ToDecimal(row[2]), Low = Convert.ToDecimal(row[3]), Close = Convert.ToDecimal(row[4]), WAP = Convert.ToDecimal(row[5]), NoOfShares = Convert.ToInt32(row[6]), NoOfTrades = Convert.ToInt32(row[7]), TotalTurnOver = Convert.ToInt32(row[8]), DeliverableQty = Convert.ToInt32(row[9]) }); if (!result) { return(false); } } return(true); } catch { return(false); } }
/// <summary> /// 指定excel table 名稱, /// 執行EXCEL 裡面的SQL 語法。 /// </summary> /// <param name="sTable"></param> private void ExcelQueryByUpdateData(string sTable) { //設定EXCEL var exc = new ExcelQueryFactory(sPath + "\\setting.xlsx"); var sQuery = from x in exc.Worksheet <Setting>("setting") where x.Table == sTable select x; foreach (var name in sQuery) { if (name.InitialCtr == null || name.changeCtr == null) { lMessage.Text = "設定檔內容有缺無法更新"; break; } comm.Del_DBTable(name.InitialCtr); comm.Insert_SaveDB(name.Table, name.changeCtr); } lMessage.Text = "料件檔 - 更新成功\n-完成豪秒數 : " + comm.GET_Run_Timer + "\n-共" + comm.GET_Rows_Coumt + "筆"; }
/// <summary> /// Uploads the excel. /// </summary> /// <param name="excelFile">The excel file.</param> /// <returns></returns> public string UploadExcel(HttpPostedFileBase excelFile) { string result = string.Empty; var excel = new ExcelQueryFactory(); excel.FileName = excelFile.FileName; var details = from x in excel.Worksheet <LevelView>() select x; foreach (var level in details) { int companyId = (int)this.session.GetSessionValue(SessionKey.CompanyId); level.CompanyId = companyId; result = this.levelRepository.SaveLevelInfo(level); } return(result); }
private void LoadColumns() { var excel = new ExcelQueryFactory(FileName); var columns = excel.GetColumnNames(CurrentWorkSheet).OrderBy(col => col); BindColumns(cbDescripcion, columns, true, "Descripcion"); BindColumns(cbCodigo, columns, true, "Codigo"); BindColumns(cbCalle, columns, true, "Calle"); BindColumns(cbAltura, columns, true, "Altura"); BindColumns(cbEsquina, columns, true, "Esquina"); BindColumns(cbPartido, columns, true, "Partido"); BindColumns(cbProvincia, columns, true, "Provincia"); BindColumns(cbLatitud, columns, true, "Latitud"); BindColumns(cbLongitud, columns, true, "Longitud"); BindColumns(cbDesde, columns, true, "VigenciaDesde"); BindColumns(cbHasta, columns, true, "VigenciaHasta"); lblDirs.Text = string.Format(Reporte, 0, 0, "0.00", 0, "0.00", 0, "0.00", 0, excel.Worksheet(CurrentWorkSheet).Count(), 0, "0.00"); SetProgressBar(0); }
public void Test_Read_Sheet2() { _excelFileName = Path.Combine(_excelFilesDirectory, "抱怨量2.xls"); _repo = new ExcelQueryFactory { FileName = _excelFileName }; var info = (from c in _repo.Worksheet <ComplainExcel>(_worksheetName) select c).ToList(); Assert.IsNotNull(info); Assert.AreEqual(info.Count, 15); info[0].Longtitute.ShouldBe(112.89, 1E-6); info[0].Lattitute.ShouldBe(23.3457); info[1].Longtitute.ShouldBe(0); info[2].Lattitute.ShouldBe(0); info[4].Longtitute.ShouldBe(113.1872); info[4].Lattitute.ShouldBe(23.0057); info[1].CandidateDistrict.ShouldBe(""); info[2].SerialNumber.ShouldBe("2016022110007894"); }
public void Test_Read_Sheet3() { _excelFileName = Path.Combine(_excelFilesDirectory, "抱怨量3.xls"); _repo = new ExcelQueryFactory { FileName = _excelFileName }; var info = (from c in _repo.Worksheet <ComplainExcel>(_worksheetName) select c).ToList(); Assert.IsNotNull(info); Assert.AreEqual(info.Count, 12); info[0].Longtitute.ShouldBe(0); info[0].Lattitute.ShouldBe(0); info[1].Longtitute.ShouldBe(0); info[2].Lattitute.ShouldBe(0); info[3].Longtitute.ShouldBe(113.132); info[3].Lattitute.ShouldBe(22.0023); info[1].CandidateDistrict.ShouldBe(""); info[2].SerialNumber.ShouldBe("2016040710008333"); }
private void nhapttlop_Click(object sender, EventArgs e) { openFileDialog1.ShowDialog(); string pathToExcelFile = "" + @openFileDialog1.FileName; string sheetName = "Sheet1"; var excelFile = new ExcelQueryFactory(pathToExcelFile); var docexcel = from a in excelFile.Worksheet <Lop>(sheetName) select a; foreach (var a in docexcel) { Lop lop = new Lop(); lop.addlop(a.Malop, a.Tenlop, a.Makhoa); } MessageBox.Show("Đã đọc file excel thành công"); excelFile.Dispose(); }
/**************************************************************/ //CARGA DE DATOS DE CLIENTES - EMPRESA /**************************************************************/ #region CARGA DE DATOS DE CLIENTES - EMPRESA public List <Comunes.InterproteccionObjetos> CargaDatosEmpresa(string pathFichero) { var book = new ExcelQueryFactory(pathFichero); var resultado = (from row in book.Worksheet("Empresa") let item = new Comunes.InterproteccionObjetos { EmpresaNombre = row["EmpresaNombre"].Cast <string>(), EmpresaDireccion = row["EmpresaDireccion"].Cast <string>(), EmpresaTipoEmpresa = row["EmpresaTipoEmpresa"].Cast <string>(), EmpresaTipoBanco = row["EmpresaTipoBanco"].Cast <string>(), EmpresaRFC = row["EmpresaRFC"].Cast <string>(), EmpresaNombreContatoUno = row["EmpresaNombreContatoUno"].Cast <string>(), EmpresaCorreoContatoUno = row["EmpresaCorreoContatoUno"].Cast <string>(), EmpresaTelefonoContatoUno = row["EmpresaTelefonoContatoUno"].Cast <string>(), EmpresaNombreContatoDos = row["EmpresaNombreContatoDos"].Cast <string>(), EmpresaCorreoContatoDos = row["EmpresaCorreoContatoDos"].Cast <string>(), EmpresaTelefonoContatoDos = row["EmpresaTelefonoContatoDos"].Cast <string>(), EmpresaNombreContatoTres = row["EmpresaNombreContatoTres"].Cast <string>(), EmpresaCorreoContatoTres = row["EmpresaCorreoContatoTres"].Cast <string>(), EmpresaTelefonoContatoTres = row["EmpresaTelefonoContatoTres"].Cast <string>(), EmpresaNotas = row["EmpresaNotas"].Cast <string>(), // SECCION DE CLIENTES - CONSULTA EMPRESAS EmpresaCampoBusquedaEmpresa = row["EmpresaCampoBusquedaEmpresa"].Cast <string>(), EmpresaCampoBusquedaContacto = row["EmpresaCampoBusquedaContacto"].Cast <string>(), EmpresaFiltroTipoEmpresa = row["EmpresaFiltroTipoEmpresa"].Cast <string>(), EmpresaFiltroTipoBanco = row["EmpresaFiltroTipoBanco"].Cast <string>(), // SECCION GENERAR CÓDIGOS DE ACTIVACIÓN - EMPRESAS EmpresaGenerarCodigosActivacionCodigoFijo = row["CodigoFijo"].Cast <string>(), EmpresaGenerarCodigosActivacionCantidadClaves = row["CantidadClaves"].Cast <string>(), EmpresaGenerarCodigosActivacionTipoPlan = row["TipoPlan"].Cast <string>(), EmpresaGenerarCodigosActivacionServicioDesde = row["ServicioDesde"].Cast <string>(), EmpresaGenerarCodigosActivacionServicioHasta = row["ServicioHasta"].Cast <string>(), EmpresaGenerarCodigosActivacionCodigoMovil = row["CodigoMovil"].Cast <string>(), EmpresaGenerarCodigosActivacionActivarAntesDel = row["ActivarAntesDel"].Cast <string>(), EmpresaGenerarCodigosActivacionPeriodoVigencia = row["PeriodoVigencia"].Cast <string>() } select item).ToList(); book.Dispose(); return(resultado); }
public IEnumerable <LALTE> GetListLALTE(string filename) { var excel = new ExcelQueryFactory(); excel.FileName = filename; var x = excel.GetWorksheetNames(); var query = (from s in excel.WorksheetRange <LALTE>("A2", "XFD1048576", 0) select s).ToList(); return(query); //List<LALTE> lstLALTE = new List<LALTE>(); //foreach (LALTE item in query) //{ // lstLALTE.Add(new LALTE // { // _118d_5m_20_54004s = item._118d_5m_20_54004s, // Section5_E911Information = item.Section5_E911Information, // USID = item.USID, // RFDS_NAME = item.RFDS_NAME, // Program_Type = item.Program_Type, // Technology = item.Technology, // SECTOR = item.SECTOR, // PSAP_ID = item.PSAP_ID, // E911_PHASE = item.E911_PHASE, // LMU_REQUIRED = item.LMU_REQUIRED, // ESRN = item.ESRN, // DATE_LIVE_PH1 = item.DATE_LIVE_PH1, // DATE_LIVE_PH2 = item.DATE_LIVE_PH2, // RFDS_ID = item.RFDS_ID, // _188167 = item._188167, // CLL02285 = item.CLL02285, // LTE_Next_Carrier_2018 = item.LTE_Next_Carrier_2018, // EAST_MISSION_DRIVE_841 = item.EAST_MISSION_DRIVE_841, // SAN_GABRIEL = item.SAN_GABRIEL, // _91776 = item._91776, // _34d_5m25_81008s = item._34d_5m25_81008s // }); //} //return lstLALTE; }
public string ImportOnlineDemand(string path) { var factory = new ExcelQueryFactory { FileName = path }; var stats = (from c in factory.Worksheet <OnlineSustainExcel>("汇总表") select c).ToList(); var count = _onlineSustainRepository.Import <IOnlineSustainRepository, OnlineSustain, OnlineSustainExcel, Town>(stats, _towns, (towns, stat) => { if (!string.IsNullOrEmpty(stat.Town)) { var candidateTown = towns.FirstOrDefault(x => x.DistrictName == stat.District && x.TownName == stat.Town); if (candidateTown != null) { return(candidateTown.Id); } } var candidateTowns = towns.Where(x => x.DistrictName == stat.District).ToList(); if (!candidateTowns.Any()) { candidateTowns = _towns; } var town = ((string.IsNullOrEmpty(stat.Site) ? null : candidateTowns.FirstOrDefault(x => stat.Site.Contains(x.TownName))) ?? (string.IsNullOrEmpty(stat.Address) ? null : candidateTowns.FirstOrDefault(x => stat.Address.Contains(x.TownName)))) ?? (string.IsNullOrEmpty(stat.Phenomenon) ? null : candidateTowns.FirstOrDefault(x => stat.Phenomenon.Contains(x.TownName))); return(town?.Id ?? candidateTowns.First().Id); }); var count2 = _processRepository.Import <IComplainProcessRepository, ComplainProcess, OnlineSustainExcel>(stats); return("完成在线支撑信息导入" + count + "条; " + "处理记录" + count2 + "条"); }
public ActionResult Import(string filename) { ViewBag.filename = filename; var filepath = Server.MapPath("~/App_Data/Import/StudentActivity/" + User.Identity.Name + "/" + filename); if (!System.IO.File.Exists(filepath)) { Session["FlashMessage"] = "File not found."; return(View()); } try { int count = 0; var excel = new ExcelQueryFactory(filepath); var sheetnames = excel.GetWorksheetNames(); var activities = from c in excel.Worksheet <StudentActivity>(sheetnames.First()) select c; foreach (var activity in activities) { db.StudentActivities.Add(activity); count++; } db.SaveChanges(); Session["FlashMessage"] = count + " record(s) successfully imported."; //clear files uploaded after import if (Directory.Exists(Server.MapPath("~/App_Data/Import/StudentActivity/" + User.Identity.Name))) { var files = Directory.GetFiles(Server.MapPath("~/App_Data/Import/StudentActivity/" + User.Identity.Name)); foreach (var file in files) { System.IO.File.Delete(file); } } } catch (Exception e) { Session["FlashMessage"] = "Failed to import activities from excel file. <br/><br/>" + HttpUtility.JavaScriptStringEncode(e.Message); return(View()); } return(RedirectToAction("Import")); }
private ExcelDocument _parseExcelData(ExcelDocument excelDocument) { var returnValue = new ExcelDocument(); returnValue = excelDocument; returnValue.Data = new Dictionary <int, List <ExcelColData> >(); //check if excel document and had data into object if (!string.IsNullOrEmpty(excelDocument.ExcelDocumentPath) && excelDocument.Use == true) { string pathToExcelFile = excelDocument.ExcelDocumentPath; var excel = new ExcelQueryFactory(pathToExcelFile); var excelData = (from c in excel.Worksheet(excelDocument.WorkSheetName) select c); excelDocument.Data = new Dictionary <int, List <ExcelColData> >(); int rowIndex = 0; foreach (var data in excelData) { List <ExcelColData> listExcelColData = new List <ExcelColData>(); int colIndex = 0; foreach (var itemData in data) { ExcelColData excelColData = new ExcelColData(); excelColData.ColIndex = colIndex; excelColData.Data = itemData.ToString(); listExcelColData.Add(excelColData); colIndex++; } returnValue.Data.Add(rowIndex, listExcelColData); rowIndex++; } } return(returnValue); }
/// <summary> 设置xls 映射关系 /// </summary> /// <param name="userXLS"></param> /// <returns> 错误列 集合 /// </returns> private static List <string> SetXlsMapping <VMdl>(ExcelQueryFactory userXLS) { List <string> errColumns = new List <string>(); //xls现有数据字段 var firstSheet = userXLS.GetWorksheetNames().First <string>(); List <string> cnlist = userXLS.GetColumnNames(firstSheet).ToList <string>(); Type type = typeof(VMdl); var ProArr = type.GetProperties(); foreach (var pro in ProArr) { var attributeArr = pro.GetCustomAttributes(true); var title = pro.Name; var name = pro.Name; var mapAttribute = attributeArr.OfType <MapFromAttribute>().FirstOrDefault(); if (mapAttribute != null) { title = mapAttribute.ColumnName; } var requiredAttri = attributeArr.OfType <NecessaryColumnAttribute>().FirstOrDefault(); if (requiredAttri != null) { if (!cnlist.Exists(cn => cn == title)) //不存在 { errColumns.Add(title); continue; } } if (mapAttribute != null) { userXLS.AddMapping(name, title); } } return(errColumns); }
public IEnumerable <CI004_Waterfall> GetListCI004Waterfall(string filename) { var excel = new ExcelQueryFactory(); excel.FileName = filename; var x = excel.GetWorksheetNames(); var query = (from s in excel.WorksheetRange <CI004_Waterfall>("A2", "XFD1048576", 0) select s).ToList(); return(query); //List < CI004_Waterfall > lstWaterfall = new List<CI004_Waterfall>(); //foreach (CI004_Waterfall item in query) //{ // lstWaterfall.Add(new CI004_Waterfall // { // USID_SPECTRUM = item.USID_SPECTRUM, // PACE_NUMBER = item.PACE_NUMBER, // SITE_NUMBER = item.SITE_NUMBER, // PACE_NAME = item.PACE_NAME, // COUNTY = item.COUNTY, // PRODUCT_SUBGROUP = item.PRODUCT_SUBGROUP, // USID = item.USID, // CI004_FORECAST = item.CI004_FORECAST, // CI004_ACTUAL = item.CI004_ACTUAL, // CI003_FORECAST = item.CI003_FORECAST, // CI003_ACTUAL = item.CI003_ACTUAL, // SPECTRUM = item.SPECTRUM, // FUNDING_LEVEL = item.FUNDING_LEVEL, // RFDS_ID = item.RFDS_ID, // RFDS_STATE_STATUS = item.RFDS_STATE_STATUS, // SPECTRUM_BUCKET = item.SPECTRUM_BUCKET, // PLAN_YEAR = item.PLAN_YEAR, // NOT_IN_CSS = item.NOT_IN_CSS, // MISSING_COORDINATES = item.MISSING_COORDINATES, // SECTOR_IN_CSS = item.SECTOR_IN_CSS, // FINAL_REMARKS = item.FINAL_REMARKS // }); //} //return lstWaterfall; }
//Metodo para extraer la informacion del excel para los casos de prueba de empleados. public List <Comunes.InterproteccionObjetos> CargaDatosEmpleados(string pathFichero) { var book = new ExcelQueryFactory(pathFichero); var resultado = (from row in book.Worksheet("Empleados") let item = new Comunes.InterproteccionObjetos { TestCase = row["CasoPrueba"].Cast <string>(), Usuario = row["Usuario"].Cast <string>(), Contrasenia = row["Password"].Cast <string>(), Url = row["Url"].Cast <string>(), ResultadoEsperado = row["ResultadoEsperado"].Cast <string>(), Navegador = row["Browser"].Cast <string>(), NombreUsuario = row["NombreUsuario"].Cast <string>(), CorreoElectronico = row["CorreoElectronico"].Cast <string>(), Rol = row["Rol"].Cast <string>() } select item).ToList(); book.Dispose(); return(resultado); }