private IXLColumn CreateColumn(IXLWorksheet sheet, string name)
{
var newColumn = sheet.FirstColumn();
newColumn.FirstCell().SetValue(name);
return(newColumn);
}
private static void AddOrdersWorksheet(IXLWorksheet sheet, IList <Order> orders)
{
var cell = 1;
sheet.Row(1).Cell(cell++).Value = "Compte";
sheet.Row(1).Cell(cell++).Value = "Nom";
sheet.Row(1).Cell(cell++).Value = "Prénom";
sheet.Row(1).Cell(cell++).Value = "Email";
sheet.Row(1).Cell(cell++).Value = "N° commande";
sheet.Row(1).Cell(cell++).Value = "Date commande";
sheet.Row(1).Cell(cell++).Value = "Date envoi";
sheet.Row(1).Cell(cell++).Value = "Prix";
sheet.Row(1).Cell(cell++).Value = "Localisation";
sheet.Row(1).Cell(cell++).Value = "Type Doc";
sheet.Row(1).Cell(cell++).Value = "Périodique";
sheet.Row(1).Cell(cell++).Value = "Année";
sheet.Row(1).Cell(cell++).Value = "Vol";
sheet.Row(1).Cell(cell++).Value = "pp.";
var index = 2;
sheet.ColumnWidth = 11;
foreach (var order in orders)
{
sheet.Row(index).Cell(1).Value = order.InvoiceAccount;
sheet.Row(index).Cell(2).Value = order.Name;
sheet.Row(index).Cell(3).Value = order.FirstName;
sheet.Row(index).Cell(4).Value = order.EMail;
sheet.Row(index).Cell(5).Value = order.IlLinkId;
sheet.Row(index).Cell(6).Style.DateFormat.Format = "dd.MM.yyyy";
sheet.Row(index).Cell(6).Value = order.OrderDate;
sheet.Row(index).Cell(7).Style.DateFormat.Format = "dd.MM.yyyy";
sheet.Row(index).Cell(7).Value = order.SendDate;
sheet.Row(index).Cell(8).Style.NumberFormat.Format = "#,##0.00";
sheet.Row(index).Cell(8).Value = order.Price;
sheet.Row(index).Cell(9).Value = order.Localisation;
sheet.Row(index).Cell(10).Value = order.DocumentType;
sheet.Row(index).Cell(11).Value = order.Title;
sheet.Row(index).Cell(12).Value = order.Year;
sheet.Row(index).Cell(13).Value = order.Volume;
sheet.Row(index).Cell(14).Value = order.Pages;
index++;
}
var tablename = $"Table_{sheet.Name}";
var table = sheet.Range(sheet.FirstColumn().FirstCell(), sheet.LastRowUsed().LastCellUsed()).CreateTable(tablename);
table.ShowAutoFilter = true;
table.Theme = XLTableTheme.TableStyleMedium2;
}
public void CopyColumn()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Sheet1");
ws.FirstCell().SetValue("Test").Style.Font.SetBold();
ws.FirstColumn().CopyTo(ws.Column(2));
Assert.IsTrue(ws.Cell("B1").Style.Font.Bold);
}
public void Validation_5()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add("Sheet1");
ws.Cell("A1").SetValue("A");
ws.Cell("B1").SetDataValidation().Custom("A1");
ws.FirstColumn().InsertColumnsBefore(1);
Assert.AreEqual("B1", ws.Cell("C1").DataValidation.Value);
}
public void Preview()
{
const int columnsMaxCount = 50;
const int rowsMaxCount = 50;
var firstColumn = _sheet.FirstColumn();
var lastColumn = _sheet.LastColumnUsed().ColumnNumber() < columnsMaxCount?
_sheet.LastColumnUsed() :
_sheet.Column(columnsMaxCount);
var firstRow = _sheet.FirstRow();
var lastRow = _sheet.LastRowUsed().RowNumber() < rowsMaxCount?
_sheet.LastRowUsed() :
_sheet.Row(rowsMaxCount);
var previewData = new string[lastColumn.ColumnNumber(), lastRow.RowNumber()];
for (var row = firstRow.RowNumber(); row <= lastRow.RowNumber(); row++)
{
for (int col = firstColumn.ColumnNumber(); col <= lastColumn.ColumnNumber(); col++)
{
previewData[col - 1, row - 1] = _sheet.Row(row).Cell(col).GetString();
Console.Write(previewData[col - 1, row - 1] + " \t");
}
Console.WriteLine();
}
Console.WriteLine();
var rowsUsed = _sheet.LastRowUsed().RowNumber();
int rowsRoundedCount = rowsUsed < 100 ?
lastRow.RowNumber() :
(int)Math.Round((double)(rowsUsed - rowsUsed / 20) / 10) * 10;
Console.WriteLine("\n=== SUMMARY ===\n");
Console.WriteLine($"Примерное кол-во строк: {rowsRoundedCount}");
}
