public void WriteSecondWorkBook()
{
using var workbook = new XLWorkbook();
var sheet = workbook.AddWorksheet("Values");
// Heading section
var cell = new CellPosition("B2");
sheet.Cell(cell.Reference).Value = "User ID:";
sheet.Cell(cell.IncrementColumn()).Value = "03459ABCIJ";
var idRange = sheet.Range(cell.SetCellPosition("B2") + ":" + cell.IncrementColumn());
idRange.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
cell.ResetColumn();
cell.IncrementRow(2);
// Data
var headers = new string[] { "Identifier", "Firstname", "Surname", "Date of Birth", "Nationality", "Foo", "Bar" };
foreach (var header in headers)
{
sheet.Cell(cell.Reference).Value = header;
cell.IncrementColumn();
}
cell.ResetColumn();
cell.IncrementRow();
var id = 0;
var nationalities = new string[] { "GB", "DE", "FR", "IR" };
var foos = new string[] { "f1", "f2", "f3" };
var dateStart = DateTime.Now.AddYears(-18);
var dateRange = new TimeSpan(365 * 10, 0, 0, 0, 0); // 10 years
foreach (var _ in Enumerable.Range(1, 100))
{
sheet.Cell(cell.Reference).Value = ++id;
sheet.Cell(cell.IncrementColumn()).Value = RandomHelper.GenerateName(RandomHelper.GenerateNumber(3, 7));
sheet.Cell(cell.IncrementColumn()).Value = RandomHelper.GenerateName(RandomHelper.GenerateNumber(4, 10));
sheet.Cell(cell.IncrementColumn()).Value = RandomHelper.GenerateDate(dateStart, dateRange, "B");
sheet.Cell(cell.Reference).Style.NumberFormat.Format = "dd/MM/yyyy";
sheet.Cell(cell.IncrementColumn()).Value = RandomHelper.GetString(nationalities);
sheet.Cell(cell.IncrementColumn()).Value = RandomHelper.GetString(foos);
sheet.Cell(cell.IncrementColumn()).Value = RandomHelper.GenerateNumber(length: 10);
sheet.Cell(cell.Reference).Style.NumberFormat.Format = new string('0', 10); // Prevents leading zeros being omitted
cell.ResetColumn();
cell.IncrementRow();
}
foreach (var header in headers)
{
sheet.Column(cell.Column).AdjustToContents();
cell.IncrementColumn();
}
workbook.SaveAs(_secondWorkbook); //using System.IO;
}
public void WriteFirstWorkBook()
{
using (var workbook = new XLWorkbook())
{
var sheet = workbook.AddWorksheet("Foo");
var colA = sheet.Columns("A");
colA.Width = 50;
var cell = new CellPosition();
var headers = new string[] { "Identifier", "Firstname", "Surname", "Age", "Gender" };
foreach (var header in headers)
{
sheet.Cell(cell.Reference).Value = header;
cell.IncrementColumn();
}
cell.ResetColumn();
cell.IncrementRow();
var id = 0;
var rnd = new Random();
foreach (var _ in Enumerable.Range(1, 100))
{
sheet.Cell(cell.Reference).Value = ++id;
sheet.Cell(cell.IncrementColumn()).Value = id <= 50 ? "Foo" : "Bar";
sheet.Cell(cell.IncrementColumn()).Value = rnd.Next(0, 2) == 0 ? "Brown" : "Bloggs";
sheet.Cell(cell.IncrementColumn()).Value = rnd.Next(25, 61);;
sheet.Cell(cell.IncrementColumn()).Value = "M";
cell.ResetColumn();
cell.IncrementRow();
}
sheet.RangeUsed().SetAutoFilter().Column(2).AddFilter("Foo"); // Filters "Firstname" to "Foo"
sheet.AutoFilter.Sort(4, XLSortOrder.Descending); // Sorts "Age" descending
workbook.SaveAs(_firstWorkbook); //using System.IO;
}
}
