public void Should_getaddress_correctly()
{
Check.That(ExcelService.GetAddressCol(1)).IsEqualTo("A");
Check.That(ExcelService.GetAddressRow(2)).IsEqualTo("2");
Check.That(ExcelService.GetAddress(03, "A")).IsEqualTo("A3");
Check.That(ExcelService.GetAddress(04, 1)).IsEqualTo("A4");
Check.That(ExcelService.GetAddress(05, true, "A", true)).IsEqualTo("$A$5"); Check.That(ExcelService.GetAddress(05, false, "A", false)).IsEqualTo("A5");
Check.That(ExcelService.GetAddress(06, true, 1, true)).IsEqualTo("$A$6"); Check.That(ExcelService.GetAddress(06, false, 1, false)).IsEqualTo("A6");
Check.That(ExcelService.GetAddress(07, "A", true)).IsEqualTo("$A$7"); Check.That(ExcelService.GetAddress(07, "A", false)).IsEqualTo("A7");
Check.That(ExcelService.GetAddress(08, 1, true)).IsEqualTo("$A$8"); Check.That(ExcelService.GetAddress(08, 1, false)).IsEqualTo("A8");
Check.That(ExcelService.GetAddress(09, "A", 09, "A")).IsEqualTo("A9"); Check.That(ExcelService.GetAddress(09, "A", 09, "B")).IsEqualTo("A9:B9");
Check.That(ExcelService.GetAddress(10, 1, 10, 1)).IsEqualTo("A10"); Check.That(ExcelService.GetAddress(10, 1, 10, 2)).IsEqualTo("A10:B10");
Check.That(ExcelService.GetAddress(11, "A", 11, "B", true)).IsEqualTo("$A$11:$B$11");
Check.That(ExcelService.GetAddress(12, 1, 12, 2, true)).IsEqualTo("$A$12:$B$12");
Check.That(ExcelService.GetAddress(13, "A", 13, "B", true, true, true, true)).IsEqualTo("$A$13:$B$13");
Check.That(ExcelService.GetAddress(14, 1, 14, 2, true, true, true, true)).IsEqualTo("$A$14:$B$14");
Check.That(ExcelService.GetAddress(Address.Cell, 15, "A")).IsEqualTo("^17:15:1");
Check.That(ExcelService.GetAddress(Address.Cell, 16, 1)).IsEqualTo("^17:16:1");
Check.That(ExcelService.GetAddress(Address.Range, 17, "A", 17, "B")).IsEqualTo("^18:17:1:17:2");
Check.That(ExcelService.GetAddress(Address.Range, 18, 1, 18, 2)).IsEqualTo("^18:18:1:18:2");
Check.That(ExcelService.GetAddress(_excelContext, Address.Cell, 19, "A")).IsEqualTo("B20");
Check.That(ExcelService.GetAddress(_excelContext, Address.Cell, 20, 1)).IsEqualTo("B21");
Check.That(ExcelService.GetAddress(_excelContext, Address.Range, 21, "A", 21, "B")).IsEqualTo("B22:C22");
Check.That(ExcelService.GetAddress(_excelContext, Address.Range, 22, 1, 22, 2)).IsEqualTo("B23:C23");
}
public void Should_encode_without_description()
{
// given
var cmds = _simpleCmds;
// when
var doc = ExcelService.Encode(false, cmds);
// then
Check.That(doc).IsEqualTo("^q=AQAAAAJBMQEAAmYx");
}
public void Should_encode_with_description()
{
// given
var cmds = _simpleCmds;
// when
var doc = ExcelService.Encode(true, cmds);
// then
Check.That(doc).IsEqualTo(
@"^q| CellsStyle[A1]: f1
^q=AQAAAAJBMQEAAmYx".ToLocalString());
}
public void Should_decode_correctly()
{
// given
var value = "^q=AQAAAAJBMQEAAmYx";
var value2 = ExcelService.Encode(false, _simpleCmds);
// when
var cmds = ExcelService.Decode(value);
var cmds2 = ExcelService.Decode(value2);
// then
Check.That(cmds.Select(x => ((CellStyle)x).Cells)).IsEquivalentTo(_simpleCmds.Select(x => ((CellStyle)x).Cells));
Check.That(cmds2.Select(x => ((CellStyle)x).Cells)).IsEquivalentTo(_simpleCmds.Select(x => ((CellStyle)x).Cells));
}
public static void ViewAction(this IExcelContext ctx, object value, ViewActionKind actionKind)
{
var view = ((ExcelContext)ctx).WS.View;
switch (actionKind)
{
case ViewActionKind.FreezePane: ExcelService.CellToInts((string)value, out var row, out var col); view.FreezePanes(row, col); break;
case ViewActionKind.SetTabSelected: view.SetTabSelected(); break;
case ViewActionKind.UnfreezePane: view.UnFreezePanes(); break;
default: throw new ArgumentOutOfRangeException(nameof(actionKind));
}
}
static Tuple <Stream, string, string> MakeInvoiceFile(IEnumerable <TestData> myData)
{
var transform = ExcelService.Encode(new List <IExcelCommand>
{
new WorksheetsAdd("Invoice"),
new CellsStyle(Address.Range, 0, 1, 2, 1, "lc:Yellow"),
});
var s = new MemoryStream();
var w = new StreamWriter(s);
// add transform to output
w.WriteLine(transform);
// add csv file to output
CsvWriter.Write(w, myData);
w.Flush(); s.Position = 0;
var result = new Tuple <Stream, string, string>(s, "text/csv", "invoice.csv");
// optionally transform
result = ExcelService.Transform(result);
return(result);
}
public static void ColumnValue(this IExcelContext ctx, string col, object value, ColumnValueKind valueKind) => ColumnValue(ctx, ExcelService.ColToInt(col), value, valueKind);
public static void ConditionalFormatting(this IExcelContext ctx, Address r, int fromRow, int fromCol, int toRow, int toCol, object value, ConditionalFormattingKind formattingKind, int?priority, bool stopIfTrue) => ConditionalFormatting(ctx, ExcelService.GetAddress(r, fromRow, fromCol, toRow, toCol), value, formattingKind, priority, stopIfTrue);
public void Should_decodeaddress_correctly()
{
Check.That(ExcelService.DecodeAddress(_excelContext, "^17:15:1")).IsEqualTo("B16");
Check.That(ExcelService.DecodeAddress(_excelContext, "^18:17:1:17:2")).IsEqualTo("B18:C18");
}
public static void CellsStyle(this IExcelContext ctx, Address r, int fromRow, int fromCol, int toRow, int toCol, params string[] styles) => CellsStyle(ctx, ExcelService.GetAddress(r, fromRow, fromCol, toRow, toCol), styles);
public static void CellsStyle(this IExcelContext ctx, Address r, int row, int col, params string[] styles) => CellsStyle(ctx, ExcelService.GetAddress(r, row, col), styles);
public static object GetRowValue(this IExcelContext ctx, string row, RowValueKind valueKind) => GetRowValue(ctx, ExcelService.RowToInt(row), valueKind);
public static void CellsValue(this IExcelContext ctx, int row, int col, object value, CellValueKind valueKind = CellValueKind.Value) => ctx.CellsValue(ExcelService.GetAddress(row, col), value, valueKind);
public static object GetColumnValue(this IExcelContext ctx, string col, ColumnValueKind valueKind) => GetColumnValue(ctx, ExcelService.ColToInt(col), valueKind);
public static void RowValue(this IExcelContext ctx, string row, object value, RowValueKind valueKind) => RowValue(ctx, ExcelService.RowToInt(row), value, valueKind);
public static void ConditionalFormatting(this IExcelContext ctx, int row, int col, object value, ConditionalFormattingKind formattingKind, int?priority, bool stopIfTrue) => ConditionalFormatting(ctx, ExcelService.GetAddress(row, col), value, formattingKind, priority, stopIfTrue);
public static object GetCellsValue(this IExcelContext ctx, Address r, int fromRow, int fromCol, int toRow, int toCol, CellValueKind valueKind = CellValueKind.Value) => ctx.GetCellsValue(ExcelService.GetAddress(r, fromRow, fromCol, toRow, toCol), valueKind);
public static object GetCellsValue(this IExcelContext ctx, Address r, int row, int col, CellValueKind valueKind = CellValueKind.Value) => ctx.GetCellsValue(ExcelService.GetAddress(r, row, col), valueKind);
public static void CellsValue(this IExcelContext ctx, Address r, object value, CellValueKind valueKind = CellValueKind.Value) => ctx.CellsValue(ExcelService.GetAddress(r, 0, 0), value, valueKind);
public static void CellsValue(this IExcelContext ctx, int fromRow, int fromCol, int toRow, int toCol, object value, CellValueKind valueKind = CellValueKind.Value) => ctx.CellsValue(ExcelService.GetAddress(fromRow, fromCol, toRow, toCol), value, valueKind);
