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