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