public void WithSubtotals()
{
XlTemplateTest("Subranges_WithSubtotals_tMD2.xlsx",
tpl =>
{
using (var db = new DbDemos())
{
var items = db.items.ToList().GroupBy(i => i.OrderNo).ToDictionary(x => x.Key);
var parts = db.parts.ToList().ToDictionary(x => x.PartNo);
customer[] custs = db.customers.LoadWith(x => x.Orders).OrderBy(x => x.CustNo).ToArray();
foreach (var customer in custs)
{
customer.Orders.Sort((x, y) => x.OrderNo.CompareTo(y.OrderNo));
foreach (var o in customer.Orders)
{
var order = o;
o.Items = items[order.OrderNo].ToList();
o.Items.Sort((x, y) => x.ItemNo.Value.CompareTo(y.ItemNo));
foreach (var item in o.Items)
{
item.Part = parts[item.PartNo.Value];
}
}
}
//var cust = db.Customers.Include(x => x.Orders.Select(o=>o.Items.Select(i=>i.Part)));
tpl.AddVariable("Customers", custs);
}
},
wb =>
{
//wb.SaveAs("Subranges_WithSubtotals_tMD2.xlsx");
CompareWithGauge(wb, "Subranges_WithSubtotals_tMD2.xlsx");
});
}
public void MultipleSubRanges()
{
var random = new Random(1234);
var templateFile = "Subranges_Multiple.xlsx";
XlTemplateTest(templateFile,
tpl =>
{
using (var db = new DbDemos())
{
var custs = GetCustomers(db).Select(cust =>
new {
CustNo = cust.CustNo,
Company = cust.Company,
Orders = cust.Orders,
Visitors = new List <dynamic>
{
new { Name = "Alice", Age = random.Next(0, 100), Gender = "F" },
new { Name = "Bob", Age = random.Next(0, 100), Gender = "M" },
new { Name = "John", Age = random.Next(0, 100), Gender = "M" },
}
});
tpl.AddVariable("Customers", custs);
tpl.AddVariable("user", "John Doe");
}
},
wb =>
{
CompareWithGauge(wb, templateFile);
});
}
public void Orders_should_not_be_empty()
{
using (var db = new DbDemos())
{
db.orders.ToList().Should().NotBeEmpty();
}
}
public void CreatePivot()
{
using (var db = new DbDemos())
{
var rows = from o in db.orders
select new { o.Customer.Company, o.PaymentMethod, o.OrderNo, o.ShipDate, o.ItemsTotal, o.TaxRate, o.AmountPaid };
using (var workbook = new XLWorkbook())
{
var sheet = workbook.Worksheets.Add("PastrySalesData");
// Insert our list of pastry data into the "PastrySalesData" sheet at cell 1,1
var source = sheet.Cell(1, 1).InsertTable(rows, "PastrySalesData", true);
// Create a range that includes our table, including the header row
var range = source.DataRange;
var header = sheet.Range(1, 1, 1, 3);
var dataRange = sheet.Range(header.FirstCell(), range.LastCell());
// Add a new sheet for our pivot table
var ptSheet = workbook.Worksheets.Add("PivotTable");
// Create the pivot table, using the data from the "PastrySalesData" table
var pt = ptSheet.PivotTables.Add("PivotTable", ptSheet.Cell(8, 2), dataRange);
pt.MergeAndCenterWithLabels = true;
pt.ShowExpandCollapseButtons = false;
pt.ClassicPivotTableLayout = true;
pt.ShowGrandTotalsColumns = false;
pt.SortFieldsAtoZ = true;
var pf = pt.RowLabels.Add("PaymentMethod");
pf.AddSubtotal(XLSubtotalFunction.Sum);
pf.AddSubtotal(XLSubtotalFunction.Average);
pt.RowLabels.Add("OrderNo");
pt.RowLabels.Add("ShipDate");
// The rows in our pivot table will be the names of the pastries
/*pt.RowLabels.Add("Company");
* pt.RowLabels.Add("PaymentMethod", "Payment Method");
* pt.RowLabels.Add("OrderNo");*/
// The columns will be the months
pt.ColumnLabels.Add("TaxRate");
// The values in our table will come from the "NumberOfOrders" field
// The default calculation setting is a total of each row/column
pt.Values.Add("AmountPaid", "Amount paid");
pt.Values.Add("ItemsTotal", "Items Total");
workbook.SaveAs("pivot_example.xlsx");
}
using (var wb = XLWorkbook.OpenFromTemplate("pivot_example.xlsx"))
{
wb.SaveAs("pivot_example1.xlsx");
}
}
}
public void WithHeader()
{
XlTemplateTest("GroupTagTests_WithHeader.xlsx",
tpl =>
{
using (var db = new DbDemos())
tpl.AddVariable("Orders", db.orders.LoadWith(x => x.Customer).OrderBy(c => c.OrderNo).ToArray());
},
wb =>
{
CompareWithGauge(wb, "GroupTagTests_WithHeader.xlsx");
});
}
public void Customers(string templateFile)
{
XlTemplateTest(templateFile,
tpl =>
{
using (var db = new DbDemos())
{
var orders = db.orders.LoadWith(x => x.Customer);
tpl.AddVariable("Orders", orders);
}
},
wb =>
{
CompareWithGauge(wb, templateFile);
});
}
public void SumWithoutGroup()
{
XlTemplateTest("tLists2_sum.xlsx",
tpl =>
{
using (var db = new DbDemos())
{
var cust = db.customers.LoadWith(x => x.Orders).OrderBy(c => c.CustNo).First();
tpl.AddVariable(cust);
}
},
wb =>
{
CompareWithGauge(wb, "tLists2_sum.xlsx");
});
}
public void WithCollapseOption()
{
XlTemplateTest("GroupTagTests_Collapse.xlsx",
tpl =>
{
using (var db = new DbDemos())
{
var cust = db.customers.LoadWith(x => x.Orders).OrderBy(c => c.CustNo).First();
tpl.AddVariable(cust);
}
},
wb =>
{
CompareWithGauge(wb, "GroupTagTests_Collapse.xlsx");
});
}
public void Simple(string templateFile)
{
XlTemplateTest(templateFile,
tpl =>
{
using (var db = new DbDemos())
{
var custs = GetCustomers(db);
tpl.AddVariable("Customers", custs);
}
},
wb =>
{
CompareWithGauge(wb, templateFile);
});
}
public void Simple(string templateFile)
{
XlTemplateTest(templateFile,
tpl =>
{
using (var db = new DbDemos())
{
var cust = db.customers.LoadWith(x => x.Orders).OrderBy(c => c.CustNo).First(x => x.CustNo == 1356);
tpl.AddVariable(cust);
}
},
wb =>
{
CompareWithGauge(wb, templateFile);
});
}
public void WithHeader()
{
XlTemplateTest("GroupTagTests_WithHeader.xlsx",
tpl =>
{
using (var db = new DbDemos())
tpl.AddVariable("Orders", db.orders.LoadWith(x => x.Customer).OrderBy(c => c.OrderNo).ToArray());
},
wb =>
{
using (var ms = new MemoryStream())
wb.SaveAs(ms); // as conditional formats are consolidated on saving
//wb.SaveAs("GroupTagTests_WithHeader.xlsx");
CompareWithGauge(wb, "GroupTagTests_WithHeader.xlsx");
});
}
public void Simple(string templateFile)
{
XlTemplateTest(templateFile,
tpl =>
{
using (var db = new DbDemos())
{
var rows = from o in db.orders
select new { o.Customer.Company, o.PaymentMethod, OrderNo = o.OrderNo.ToString(), o.ShipDate, o.ItemsTotal, o.TaxRate, o.AmountPaid };
tpl.AddVariable("Orders", rows);
}
},
wb =>
{
CompareWithGauge(wb, templateFile);
});
}
public void Static()
{
XlTemplateTest("tPivot5_Static.xlsx",
tpl =>
{
using (var db = new DbDemos())
{
var rows = from o in db.orders
select new { o.Customer.Company, o.PaymentMethod, OrderNo = o.OrderNo.ToString(), o.ShipDate, o.ItemsTotal, o.TaxRate, o.AmountPaid };
tpl.AddVariable("Orders", rows);
}
},
wb =>
{
//wb.SaveAs("tPivot5_Static.xlsx");
CompareWithGauge(wb, "tPivot5_Static.xlsx");
});
}
public void SumWithoutGroup()
{
XlTemplateTest("tLists2_sum.xlsx",
tpl =>
{
using (var db = new DbDemos())
{
var cust = db.customers.LoadWith(x => x.Orders).OrderBy(c => c.CustNo).First();
tpl.AddVariable(cust);
}
},
wb =>
{
using (var ms = new MemoryStream())
wb.SaveAs(ms); // as conditional formats are consolidated on saving
//wb.SaveAs("tLists2_sum.xlsx");
CompareWithGauge(wb, "tLists2_sum.xlsx");
});
}
public void Simple(string templateFile)
{
XlTemplateTest(templateFile,
tpl =>
{
using (var db = new DbDemos())
{
var cust = db.customers.LoadWith(x => x.Orders.First().Items).OrderBy(c => c.CustNo).First(x => x.CustNo == 1356);
cust.Logo = Resource.toms_diving_center;
tpl.AddVariable("MoreOrders", cust.Orders.Take(5));
tpl.AddVariable(cust);
tpl.AddVariable("ItemsHeader", Enumerable.Range(1, cust.Orders.Max(x => x.Items.Count)).Select(x => $"Item {x}"));
}
tpl.AddVariable("Tax", 13);
},
wb =>
{
CompareWithGauge(wb, templateFile);
});
}
private static customer[] GetCustomers(DbDemos db)
{
var items = db.items.ToList().GroupBy(i => i.OrderNo).ToDictionary(x => x.Key);
var parts = db.parts.ToList().ToDictionary(x => x.PartNo);
customer[] custs = db.customers.LoadWith(x => x.Orders).OrderBy(x => x.CustNo).ToArray();
foreach (var customer in custs)
{
customer.Orders.Sort((x, y) => x.OrderNo.CompareTo(y.OrderNo));
foreach (var o in customer.Orders)
{
var order = o;
o.Items = items[order.OrderNo].ToList();
o.Items.Sort((x, y) => x.ItemNo.Value.CompareTo(y.ItemNo));
foreach (var item in o.Items)
{
item.Part = parts[item.PartNo.Value];
}
}
}
//var cust = db.Customers.Include(x => x.Orders.Select(o=>o.Items.Select(i=>i.Part)));
return(custs);
}