private void WriteRefDataSheets(Dictionary <string, ImportReferencedataList> refs, IWorkbook workbook) { foreach (var keyValuePair in refs) { var refData = keyValuePair.Value; var worksheet = workbook.AddWorksheet(refData.ReferenceDataType.Name); var list = refData.ReferenceData.ConvertList <ReferenceData>(); var endIndex = list.Count + 1; worksheet.Cells[1, 1].Value = "Description"; worksheet.Cells[1, 2].Value = "Id"; for (int i = 2; i <= endIndex; i++) { worksheet.Cells[i, 1].Value = list[i - 2].Description; worksheet.Cells[i, 2].Value = list[i - 2].Id; } } }
public RSWorkbook(IWorkbook workbook) { this.workbook = workbook; this["worksheets"] = new ExternalProperty(() => { if (worksheetCollection == null) { worksheetCollection = new RSWorksheetCollection(workbook); } return(worksheetCollection); }); this["currentWorksheet"] = new ExternalProperty(() => { if (ControlInstance != null) { var rsWorksheet = ControlInstance.CurrentWorksheet.worksheetObj; if (rsWorksheet == null) { rsWorksheet = new RSWorksheet(ControlInstance.CurrentWorksheet); ControlInstance.CurrentWorksheet.worksheetObj = rsWorksheet; } return(rsWorksheet); } else { return(null); } }); this["createWorksheet"] = new NativeFunctionObject("createWorksheet", (ctx, owner, args) => { return(new RSWorksheet(workbook.CreateWorksheet(args.Length > 0 ? ScriptRunningMachine.ConvertToString(args[0]) : null))); }); this["addWorksheet"] = new NativeFunctionObject("createWorksheet", (ctx, owner, args) => { if (args.Length < 1) { return(null); } workbook.AddWorksheet(RSWorksheet.Unbox(args[0])); return(null); }); this["insertWorksheet"] = new NativeFunctionObject("createWorksheet", (ctx, owner, args) => { if (args.Length < 2) { return(null); } workbook.InsertWorksheet(ScriptRunningMachine.GetIntValue(args[0]), RSWorksheet.Unbox(args[1])); return(null); }); this["removeWorksheet"] = new NativeFunctionObject("createWorksheet", (ctx, owner, args) => { if (args.Length < 1) { return(null); } Worksheet sheet = RSWorksheet.Unbox(args[0]); if (sheet != null) { return(workbook.RemoveWorksheet(sheet)); } else { return(workbook.RemoveWorksheet(ScriptRunningMachine.GetIntValue(args[0]))); } }); }
public async Task OnNew(IWorkbook workbook) { this.CanWriteCellStyle = new Style(Color.LightBlue, Color.Black); this.CanNotWriteCellStyle = new Style(Color.MistyRose, Color.Black); this.ChangedCellStyle = new Style(Color.DeepSkyBlue, Color.Black); var sheet = workbook.AddWorksheet(); sheet.Name = $"{workbook.Worksheets.Length}"; for (var i = 0; i < 50; i++) { for (var j = 0; j < 10; j++) { sheet[i, j].Value = decimal.Parse($"{i},{j}"); if (j == 0 || j == 2) { sheet[i, j].Style = this.CanWriteCellStyle; sheet[i, j].NumberFormat = "#.###,00"; } else { sheet[i, j].Style = this.CanNotWriteCellStyle; } } } var style = new Style(Color.Red, Color.White); sheet[3, 3].Style = style; sheet[3, 5].Style = style; sheet[4, 4].Style = style; sheet[5, 3].Style = style; sheet[5, 5].Style = style; await sheet.Flush(); sheet[0, 0].Value = "Whoppa!"; sheet[0, 0].Comment = "De Poppa!"; sheet[10, 2].Style = this.CanNotWriteCellStyle; sheet[3, 12].Value = "Walter"; sheet[3, 13].Value = "Martien"; sheet[3, 14].Value = "Koen"; sheet[2, 11].Value = "Person:"; sheet[2, 12].Options = new Range(row: 3, column: 12, columns: 3, worksheet: sheet); if (!binderByWorksheet.TryGetValue(sheet, out var binder)) { binder = new Binder(sheet); binderByWorksheet.Add(sheet, binder); } var binding = new Binding(toDomain: cell => { string message = $"Binder toDomain: {cell.Row}:{cell.Column}"; }); binder.Set(5, 12, binding); await sheet.Flush(); sheet.CellsChanged += (sender, v) => { foreach (var cell in v.Cells) { cell.Style = this.ChangedCellStyle; } ((IWorksheet)sender).Flush(); string message = $"Cells changed: {string.Join(",", v.Cells.Select(w => $"{w.Row}:{w.Column}"))}"; this.ServiceLocator.Alerter.Alert(message); }; }
public async Task OnNew(IWorkbook workbook) { CanWriteCellStyle = new Style(Color.LightBlue, Color.Black); CanNotWriteCellStyle = new Style(Color.MistyRose, Color.Black); ChangedCellStyle = new Style(Color.DeepSkyBlue, Color.Black); var sheet = workbook.AddWorksheet(); sheet.Name = $"{++counter}"; for (var i = 0; i < 50; i++) { for (var j = 0; j < 10; j++) { sheet[i, j].Value = decimal.Parse($"{i},{j}"); if (j == 0 || j == 2) { sheet[i, j].Style = CanWriteCellStyle; sheet[i, j].NumberFormat = "#.###,00"; } else { sheet[i, j].Style = CanNotWriteCellStyle; } } } var style = new Style(Color.Red, Color.White); sheet[3, 3].Style = style; sheet[3, 5].Style = style; sheet[4, 4].Style = style; sheet[5, 3].Style = style; sheet[5, 5].Style = style; await sheet.Flush(); sheet[0, 0].Value = "Whoppa!"; sheet[0, 0].Comment = "De Poppa!"; sheet[10, 2].Style = CanNotWriteCellStyle; sheet[3, 12].Value = "Walter"; sheet[3, 13].Value = "Martien"; sheet[3, 14].Value = "Koen"; sheet[2, 11].Value = "Person:"; sheet[2, 12].Options = new Range(row: 3, column: 12, columns: 3, worksheet: sheet); if (!binderByWorksheet.TryGetValue(sheet, out var binder)) { binder = new Binder(sheet); binderByWorksheet.Add(sheet, binder); } var binding = new Binding(toDomain: cell => { var message = $"Binder toDomain: {cell.Row}:{cell.Column}"; }); binder.Set(5, 12, binding); for (var day = 1; day <= 31; ++day) { sheet[day + 5, 10].NumberFormat = CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern; sheet[day + 5, 10].Value = new DateTime(2020, 7, day); } await sheet.Flush(); sheet.AutoFit(); //sheet.CellsChanged += (sender, v) => //{ // foreach (var cell in v.Cells) // { // cell.Style = this.ChangedCellStyle; // } // ((IWorksheet)sender).Flush(); // string message = $"Cells changed: {string.Join(",", v.Cells.Select(w => $"{w.Row}:{w.Column}"))}"; // this.ServiceLocator.Alerter.Alert(message); //}; }