/// <summary> /// Creates ExcelModel from Xml /// </summary> /// <param name="xElements"></param> /// <returns>The ExcelModels</returns> public List <ExcelModel> CreateExcelModelsFromXml(IEnumerable <XElement> xElements) { if (xElements.Any() == false) { throw new ArgumentException("xElements parameter should not be empty"); } var excelModels = new List <ExcelModel>(); foreach (var xElement in xElements) { var excelModel = new ExcelModel { VatNumber = xElement.Element(_nameSpace + _vevoNode)?.Element(_nameSpace + _adoszamNode)?.Value, InvoiceNumber = xElement.Element(_nameSpace + _fejlecNode)?.Element(_nameSpace + _szlasorszamNode)?.Value, FinalNetPrice = xElement.Element(_nameSpace + _osszesitesNode)?.Element(_nameSpace + _vegosszegNode)? .Element(_nameSpace + _nettoarosszNode)?.Value, FinalVat = xElement.Element(_nameSpace + _osszesitesNode)?.Element(_nameSpace + _vegosszegNode)? .Element(_nameSpace + _afaertekosszNode)?.Value }; excelModels.Add(excelModel); } return(excelModels); }
public void ExportProtocol(string filePath, CompanyOrder order) { var excelModel = new ExcelModel(); var excelSheet = new ExcelSheetModel(); excelSheet.Name = "Sheet1"; excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "公司名", RowIndex = 2, ColumnIndex = 3, Value = order.Company.CompanyName }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "公司地址", RowIndex = 3, ColumnIndex = 3, Value = order.Company.CompanyAddress }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "体检时间", RowIndex = 10, ColumnIndex = 2, Value = XL.Utilities.StringHelper.Join(order.SubOrders.Select(x => x.StartDate.Value.ToString("yyyy-MM-dd")).ToArray(), ";") }); excelModel.Sheets.Add(excelSheet); _importExportService.ExportWithTemplate(filePath, excelModel); }
/// <summary> /// 内部TryAdd,自己封装一个方法, 个人推荐用这个.代码改的少 /// </summary> /// <param name="propModel"></param> /// <param name="source"></param> private static void AddSourceWay3_AddRange_ByFunction(ExcelModel propModel, KvSource <string, long> source) { #region dt var dt = new DataTable(); dt.Columns.Add("Id"); dt.Columns.Add("Name"); var dr = dt.NewRow(); dr["Id"] = 6; dr["Name"] = "事业6部"; dt.Rows.Add(dr); #endregion //source.AddRange(GetSource_部门(propModel, dt).Data); var prop = propModel.部门; var keyType = prop.GetKeyType(); var valueType = prop.GetValueType(); var kvsource = prop.CreateKVSource(); foreach (DataRow item in dt.Rows) { var key = SafeRow(item, "Name", keyType); var value = SafeRow(item, "Id", valueType); kvsource.TryAdd(key, value); } source.AddRange(kvsource.Data); }
/// <summary> /// TryAdd 自己创建 DataTable /// </summary> /// <param name="propModel"></param> /// <param name="source"></param> private static void AddSourceWay2_TryAdd_CreateDataTable(ExcelModel propModel, KvSource <string, long> source) { #region CreateDataTable var dt = new DataTable(); dt.Columns.Add("Id"); dt.Columns.Add("Name"); var dr1 = dt.NewRow(); var dr2 = dt.NewRow(); dr1["Id"] = 4; dr1["Name"] = "事业4部"; dr2["Id"] = 5; dr2["Name"] = "事业5部"; dt.Rows.Add(dr1); dt.Rows.Add(dr2); #endregion var prop = propModel.部门; var keyType = prop.GetKeyType(); var valueType = prop.GetValueType(); foreach (DataRow item in dt.Rows) { //确保类型是对的 var key = SafeRow(item, "Name", keyType); var value = SafeRow(item, "Id", valueType); source.TryAdd(key, value); } }
public static ExcelModel ConvertDictionaryToExcelModel(IEnumerable <Dictionary <string, object> > data, ExcelExporterOptions options) { var sheetTitle = options.SheetTitle; var excel = new ExcelModel { Sheets = new List <SheetModel>() }; var sheet = SheetModel.Create(sheetTitle); excel.Sheets.Add(sheet); var list = data.ToList(); var title = list.FirstOrDefault(); if (title == null) { return(excel); } var columns = title.Keys.Select((c, i) => new ExcelColumn { Order = i, Title = c, Type = typeof(string) }).ToList(); sheet.Columns = AttachColumns(columns, options); sheet.Rows = list; return(excel); }
/// <summary> /// Use with the xml file contains paras elements /// </summary> /// <param name="mode"></param> /// <returns></returns> public ActionResult ImportExcel(FileUploadModel model) { //Test run time upload excel //DateTime timeFinish = DateTime.UtcNow; //DateTime timeStart = DateTime.UtcNow; //TimeSpan timeSpan = new TimeSpan(); //timeStart = DateTime.Parse(DateTime.Now.ToString("h:mm:ss tt")); this.GetLanguage(); try { //--------------------GET XML CONFIG ----------------------------------------- this.SetConnectionDB(); EVNImporterServices services = new EVNImporterServices(oracleConnection, DBConnection); List <string> lstCellName = new List <string>(); string xmlFilePath = this.CONFIG_FOLDER + "\\excel_format_" + model.PermissionId.ToString() + ".xml"; ExcelModel lstColumns = services.GetXMLConfig(xmlFilePath); //************************** UPLOAD FILE TO THE UPLOAD FOLDER*********************************************** string excelfilename = BI_Project.Helpers.Utility.APIStringHelper.GenerateId() + Path.GetExtension(model.FileObj.FileName); //string excelFilePath = System.Web.Hosting.HostingEnvironment.MapPath(this.EXCEL_UPLOAD_FOLDER) + lstColumns.FolderUploadedDirectory + "/" + excelfilename; string excelFilePath = System.Web.Hosting.HostingEnvironment.MapPath(this.EXCEL_UPLOAD_FOLDER) + lstColumns.FolderUploadedDirectory + "\\" + excelfilename; model.FileObj.SaveAs(excelFilePath); //************************ INSERT DATA TO DATABASE ******************** int userid = (int)Session[this.SESSION_NAME_USERID]; int uploadRoleId = model.PermissionId; services.ImportPLDatabase(userid, excelFilePath, uploadRoleId, lstColumns, excelfilename); //services.Import2Database(userid, excelFilePath) if (services.ERROR != null) { throw new Exception(services.ERROR); } Session["msg_text"] = BlockLanguageModel.GetElementLang(this.LANGUAGE_OBJECT, "messages.block_upload_excel.success"); Session["msg_code"] = 1; //timeFinish = DateTime.Parse(DateTime.Now.ToString("h:mm:ss tt")); //timeSpan = timeFinish.Subtract(timeStart); //FileHelper.SaveFile(timeSpan, this.LOG_FOLDER + "/TIMESPAN" + this.GetType().ToString() + APIStringHelper.GenerateFileId() + ".txt"); } catch (Exception ex) { //FileHelper.SaveFile(new { ERROR = ex }, this.LOG_FOLDER + "/ERROR_" + this.GetType().ToString() + APIStringHelper.GenerateFileId() + ".txt"); ERRORS = ex.Message; Session["msg_text"] = BlockLanguageModel.GetElementLang(this.LANGUAGE_OBJECT, "messages.block_upload_excel.error") + " Lỗi " + ex.Message; Session["msg_code"] = -1; } return(RedirectToAction("Index/" + model.PermissionId)); }
public void ExportCompanyEmployees(string templateFilePath, List <ImportResultModel> data) { //将List<ImportResultModel>转成ExcelModel后导出 var excelModel = new ExcelModel(); foreach (var sheet in data) { var excelSheetModel = new ExcelSheetModel(); excelSheetModel.Name = sheet.Title; for (int row = 0; row < sheet.ErrorRows.Count; row++) { for (var col = 0; col < sheet.ErrorRows[row].Values.Count; col++) { var cell = new ExcelSheetCellModel(); cell.RowIndex = row + 2; cell.ColumnIndex = col + 1; cell.Value = sheet.ErrorRows[row].Values[col]; foreach (var error in sheet.ErrorRows[row].ErrorCols) { if (col == error.ColIndex) { cell.Errors.Add(error.Message); } } excelSheetModel.Cells.Add(cell); } } excelModel.Sheets.Add(excelSheetModel); } //_importExportService.Export(templateFilePath, data); _importExportService.ExportWithTemplate(templateFilePath, excelModel); }
public void IntegrationTest() { string sourceExcel = @"C:\Users\dhara\Downloads\Comparison\Source.xlsx"; string targetExcel = @"C:\Users\dhara\Downloads\Comparison\Target.xlsx"; //source excel var sourceDataTable = Utils.ExcelToDataTable(sourceExcel, ".xlsx"); //target excel var targetDataTable = Utils.ExcelToDataTable(targetExcel, ".xlsx"); //Creating models from excels var sourceModel = new ExcelModel(sourceDataTable); var targetModel = new ExcelModel(targetDataTable); sourceModel.ComparisonColumn = sourceModel.Columns[0];//setting primary key here targetModel.ComparisonColumn = sourceModel.ComparisonColumn; var match = sourceModel.Intersect(targetModel); match.ComparisonColumn = sourceModel.ComparisonColumn; var nonMatch = sourceModel.Difference(targetModel); var savedMatch = Utils.SaveDataTableExcel(match.SourceData, "match.xlsx"); // u get the idea rit?.. i am reading excel, converting to Datatable and querying the table //if u want more options then u have to do // Import excel , convert to DataTable, then store in Sqlite.Once u save to sqlite then u can connect to SQLITE using //one of the SQL Providers like SQLIteAdapter, then u can query the db like u do a normal db like this var savedNonMatch = Utils.SaveDataTableExcel(nonMatch.SourceData, "nonMatch.xlsx"); }
public ActionResult UploadExcel(int sessionId = 0) { ExcelModel model = new ExcelModel { SessionId = sessionId }; return(PartialView("_UploadExcel", model)); }
public ActionResult UpdateExcel(string field, int id = 0) { ExcelModel model = new ExcelModel(field); string file = "\\Users\\user1\\Downloads\\Testdocument.xlsx"; model.openExcelSheet(file); return RedirectToAction("Index"); }
public ViewGridController(string filename, bool keySelectionEnabled) { isPrimaryKeyEnabled = keySelectionEnabled; var data = Utils.ExcelToDataTable(filename, ".xlsx"); model = new ExcelModel(data); SelectedKeyIndex = 0; }
protected override void DoRun(IShellContext context) { string file = context.ResolveFile(context.Replace(File), ResolveFileMode.Input); var model = ExcelModel.OpenFile(file); model.DataFormat = DataFormat; context.SetVariable(GetExcelVariableName(context), model); }
public static void SaveToFile(string filePath, ExcelModel excel) { XmlSerializer serializer = new XmlSerializer(excel.GetType()); using (TextWriter tw = new StreamWriter(filePath, false, Encoding.UTF8)) { serializer.Serialize(tw, excel); } }
protected override void DoRun(IShellContext context) { string file = context.ResolveFile(context.Replace(File), ResolveFileMode.Output); context.Info("Writing file " + Path.GetFullPath(file)); var model = ExcelModel.CreateFile(file); model.DataFormat = DataFormat; context.SetVariable(GetExcelVariableName(context), model); }
private static void StoreCollectedData(string item, List <string> itemPrices, List <string> itemNumbers) { ExcelModel excelModel = GetExcelModel(item); StorageManager storageManager = new StorageManager(excelModel); storageManager.StoreData(excelModel, itemPrices, itemNumbers); excelModel.Close(); }
public ObservableCollection <EntityTag> CreateTags(ExcelModel location) { return(new ObservableCollection <EntityTag>() { new EntityTag { Id = $"{_site.ClientId}/{location.Column1}", Name = $"{location.Column1}" } }); }
private static KvSource <long, string> GetSource_部门评分(ExcelModel propModel) { var source = propModel.部门评分.CreateKVSource(); source.Add(1, "非常不满意", "very bad"); source.Add(2, "不满意", "bad"); source.Add(3, "一般", "just so so"); source.Add(4, "满意", "good"); source.Add(5, "非常满意", "very good"); return(source); }
ExcelModel CreateExcelModel(string[] keys, string[] values) { ExcelModel person = new ExcelModel(); var len = keys.Length; for (int i = 0; i < len; i++) { person.GetType().GetField(keys[i]).SetValue(person, values[i]); } return(person); }
public static ExcelModel FromFile(string filePath) { ExcelModel excel = new ExcelModel(); XmlSerializer serializer = new XmlSerializer(excel.GetType()); using (TextReader tr = new StreamReader(filePath, Encoding.UTF8)) { excel = (ExcelModel)serializer.Deserialize(tr); } return(excel); }
public void ExportHealthResult(string templateFilePath, List <ImportResultModel> data) { var excelModel = new ExcelModel(); foreach (var sheet in data) { var excelSheetModel = new ExcelSheetModel(); excelSheetModel.Name = sheet.Title; bool hasExtra = false; for (int row = 0; row < sheet.ErrorRows.Count; row++) { if (sheet.ErrorRows[row].RowIndex == 0) { for (var col = 0; col < sheet.ErrorRows[row].Values.Count; col++) { var cell = new ExcelSheetCellModel(); cell.RowIndex = col + 2; cell.ColumnIndex = 2; cell.Value = sheet.ErrorRows[row].Values[col]; foreach (var error in sheet.ErrorRows[row].ErrorCols) { if (col == error.ColIndex) { cell.Errors.Add(error.Message); } } excelSheetModel.Cells.Add(cell); } hasExtra = true; } else { for (var col = 0; col < sheet.ErrorRows[row].Values.Count; col++) { var cell = new ExcelSheetCellModel(); cell.RowIndex = hasExtra ? row + 8 : row + 9; cell.ColumnIndex = col + 1; cell.Value = sheet.ErrorRows[row].Values[col]; foreach (var error in sheet.ErrorRows[row].ErrorCols) { if (col == error.ColIndex) { cell.Errors.Add(error.Message); } } excelSheetModel.Cells.Add(cell); } } } excelModel.Sheets.Add(excelSheetModel); } _importExportService.ExportWithTemplate(templateFilePath, excelModel); }
public override bool Equals(object obj) { if (obj == null || !obj.GetType().Equals(this.GetType())) { return(false); } ExcelModel y = (ExcelModel)obj; return(this.a == y.a && this.b == y.b); }
public void ExportWithTemplateExtend(string templateFilePath, ExcelModel model) { FileInfo fileInfo = new FileInfo(templateFilePath); if (!fileInfo.Exists) { throw new Exception("未找到名为 " + templateFilePath + " 的模板"); } using (var xlPackage = new ExcelPackage(fileInfo)) { foreach (var sheet in model.Sheets) { var worksheet = xlPackage.Workbook.Worksheets[sheet.Name]; if (worksheet == null) { throw new Exception("模板中未包含名为 " + sheet.Name + " 的Sheet"); } List <int> templateRows = new List <int>(); foreach (var cell in sheet.Cells) { if (cell.TemplateRowIndex != -1) { if (!templateRows.Contains(cell.TemplateRowIndex)) { templateRows.Add(cell.TemplateRowIndex); } worksheet.InsertRow(cell.RowIndex, 1); worksheet.Cells[cell.TemplateRowIndex, 1, cell.TemplateRowIndex, 100].Copy(worksheet.Cells[cell.RowIndex, 1, cell.RowIndex, 100]); } worksheet.Cells[cell.RowIndex, cell.ColumnIndex].Value = cell.Value; if (cell.Errors.Count > 0) { worksheet.Cells[cell.RowIndex, cell.ColumnIndex].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[cell.RowIndex, cell.ColumnIndex].Style.Fill.BackgroundColor.SetColor(Color.Red); } } if (templateRows.Count > 0) { templateRows.Sort(); for (int i = templateRows.Count - 1; i >= 0; i--) { worksheet.DeleteRow(templateRows[i], 1, true); } } } xlPackage.Save(); } }
public static ExcelModel ConvertObjectToExcelModel <TModel>(IEnumerable <TModel> data, ExcelExporterOptions options) { var sheetTitle = options.SheetTitle; var excel = new ExcelModel { Sheets = new List <SheetModel>() }; if (string.IsNullOrWhiteSpace(sheetTitle)) { var classAttr = ExcelUtil.GetClassExportAttribute <TModel>(); sheetTitle = classAttr.Item1 == null ? sheetTitle : classAttr.Item1.Title; } var sheet = SheetModel.Create(sheetTitle); excel.Sheets.Add(sheet); var attrDict = ExcelUtil.GetPropertiesAttributesDict <TModel>(); var objKeysArray = attrDict.OrderBy(c => c.Value.ExcelTitleAttribute.Order).ToArray(); var columns = new List <ExcelColumn>(); for (var i = 0; i < objKeysArray.Length; i++) { var titleAttr = objKeysArray[i].Value.ExcelTitleAttribute; var fontAttr = objKeysArray[i].Value.ExcelColumnFontAttribute; var column = new ExcelColumn { Title = titleAttr.Title, Type = objKeysArray[i].Key.PropertyType, Order = i, Font = fontAttr == null ? null : new Font(fontAttr.FontName, fontAttr.FontHeightInPoints, fontAttr.Color, fontAttr.IsBold), }; columns.Add(column); } sheet.Columns = AttachColumns(columns, options); foreach (var item in data.Where(c => c != null)) { var row = new Dictionary <string, object>(); foreach (var column in objKeysArray) { var prop = column.Key; row[column.Value.ExcelTitleAttribute.Title] = prop.GetValue(item, null); } sheet.Rows.Add(row); } return(excel); }
private static GYMGROUP CreateGymGroup(ExcelModel _ExcelModel, EGOEntities _EGOEntities) { GYMGROUP GymGroup = new GYMGROUP(); GymGroup.GYMGROUPNAME = _ExcelModel.GROUPE; GymGroup.NUMBEROFHOURS = 0; GymGroup.GYMGROUPYEAR = Common.CurrentStartYear(); GymGroup.YEARPRICE = _ExcelModel.RGLTDUTT; _EGOEntities.GYMGROUP.Add(GymGroup); return(GymGroup); }
public override bool Equals(object obj) { if (obj == null || !obj.GetType().Equals(this.GetType())) { return(false); } ExcelModel y = (ExcelModel)obj; return(this.字 == y.字 && this.字2 == y.字2 && this.字3 == y.字3); }
public override bool Equals(object obj) { if (obj == null || !obj.GetType().Equals(this.GetType())) { return(false); } ExcelModel y = (ExcelModel)obj; return(this.序号 == y.序号 && Helper.GetEquals_KV(this.部门, y.部门) && Helper.GetEquals_KV(this.部门2, y.部门2)); }
public override bool Equals(object obj) { if (obj == null || !obj.GetType().Equals(this.GetType())) { return(false); } ExcelModel y = (ExcelModel)obj; return(this.序号 == y.序号 && this.姓名 == y.姓名 && this.JanuaryStatistics == y.JanuaryStatistics && this.FebruaryStatistics == y.FebruaryStatistics); }
public override bool Equals(object obj) { if (obj == null || !obj.GetType().Equals(this.GetType())) { return(false); } ExcelModel y = (ExcelModel)obj; return(this.序号 == y.序号 && this.部门 == y.部门 && this.部门负责人 == y.部门负责人 && this.部门负责人确认签字 == y.部门负责人确认签字); }
private static FAMILY CreateFamily(ExcelModel _ExcelModel, EGOEntities _EGOEntities) { FAMILY Family = new FAMILY(); Family.LASTNAME = _ExcelModel.NOM.ToUpper(); Family.ADDRESS = _ExcelModel.ADRESSE; Family.ZIPCODE = _ExcelModel.CP; Family.CITY = _ExcelModel.VILLE; Family.EMAIL = _ExcelModel.EMAIL; _EGOEntities.FAMILY.Add(Family); return(Family); }
public FileResult ExportExcel(ExcelModel datas) { string Path = Configer.Host + datas.ApiUrl; IDictionary <String, String> Map = new Dictionary <String, String> { { "Ids", "Id" } }; var keyValuePairs = HttpClientUtil.KeyValuePairs(datas, Map); String Result = HttpClientUtil.HttpPostAsync(Path, keyValuePairs, null, "application/x-www-form-urlencoded").Result; var dt = FileUtil.ConvertJsonToDatatable(JsonConvert.SerializeObject(JsonConvert.DeserializeObject <dynamic>(Result).data)); var bytes = FileUtil.ExportExcel(dt, "报表"); return(File(bytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "报表单.xlsx")); }
public ActionResult Excel(int seasonId, int? bettingType, int? moneyType) { ExcelModel model = new ExcelModel(); model.BetList = new List<ExcelExport>(); SeasonBets tempModel = new SeasonBets(); //ListAccess la = new ListAccess(); //model.Bets = la.NBABets(seasonId); //model.TotalGroupBetNumbers(); tempModel.VersionThree((AppStatic.Seasons)seasonId, AppStatic.BettingSetup.MoneyLine); //List<ExcelExport> eeList = new List<ExcelExport>(); bool hasWon = false; //keeps track of amount of best after loss int indexLoss = 0; foreach (Bet bet in tempModel.Bets) { hasWon = false; foreach (Game game in bet.BetGames) { if (hasWon) { break; }; ExcelExport ee = new ExcelExport(); ee.BetId = bet.BetId; ee.BetDate = game.GameDate; ee.AwayTeam = game.AwayTeamName; ee.HomeTeam = game.HomeTeamName; switch (bet.BetGames.IndexOf(game)) { case 0: ee.Current = "A"; break; case 1: ee.Current = "B"; break; case 2: ee.Current = "C"; break; case 3: ee.Current = "D"; break; case 4: ee.Current = "Loss"; break; default: ee.Current = "OVER"; break; } ee.RunningTotal = ee.RunningTotal - ee.BetAmount; if (bet.WinA) { ee.Winning = "A"; } if (bet.WinB) { ee.Winning = "B"; } if (bet.WinC) { ee.Winning = "C"; } if (bet.WinD) { ee.Winning = "D"; } if (!bet.WinA && !bet.WinB && !bet.WinC && !bet.WinD) { ee.Winning = "Loss"; } model.BetList.Add(ee); if (ee.Winning == ee.Current) { hasWon = true; } } } model.BankRoll(16, 2000, 275, 578, 1214, 2275); model.ACount = model.BetList.Where(b => b.Winning == "A" && b.Current == "A").Count(); model.BCount = model.BetList.Where(b => b.Winning == "B" && b.Current == "B").Count(); model.CCount = model.BetList.Where(b => b.Winning == "C" && b.Current == "C").Count(); model.DCount = model.BetList.Where(b => b.Winning == "D" && b.Current == "D").Count(); model.Loss = model.BetList.Where(b => b.Winning == "Loss" && b.Current == "Loss").Count(); model.TotalWins = model.ACount + model.BCount + model.CCount + model.DCount; model.TotalBets = model.BetList.Count(); // model.VersionThree((AppStatic.Seasons)seasonId, AppStatic.BettingSetup.MoneyLine); //model.Bets = model.Bets.SelectMany(b => b.BetGames).OrderBy(g => g.GameDate).ToList(); //model.MoneyLineBetsQuitWin(); //if (bettingType != null) //{ // switch (bettingType) // { // case 1: // model.Bets.SpreadBetsAll(); // break; // case 2: // model.Bets.MoneyLineBetsAll(); // break; // } //} //model.Bets.MoneyLineBetsAll(); return View("~/Views/Sports/NBA/Excel.cshtml", model); }
/// <summary> /// 导入对比模版获取 /// </summary> /// <param name="key"></param> /// <param name="jChild"></param> /// <returns></returns> public ExcelModel GetExcelModel(string key,JObject jChild) { ExcelModel excelModel = new ExcelModel(); excelModel.Title = jChild["title"].ToString(); excelModel.ColumnName = key; excelModel.IsHide = Convert.ToBoolean(jChild["hide"]); excelModel.IsFomat = Convert.ToBoolean(jChild["format"]); excelModel.Type = Convert.ToInt32(jChild["exporttype"]); excelModel.TestType = Convert.ToInt32(jChild["testexporttype"]); excelModel.DataSource = jChild["datasource"].ToString(); excelModel.DefaultText = jChild["defaulttext"].ToString(); excelModel.DataType = jChild["datatype"].ToString(); excelModel.ImportType = Convert.ToInt32(jChild["importtype"]); excelModel.ImportColumn = jChild["importcolumn"].ToString(); return excelModel; }