Skip to content

ClosedXML/ClosedXML-wiki

Folders and files

NameName
Last commit message
Last commit date

Latest commit

77cc170 · Mar 3, 2021
Jul 19, 2019
Oct 22, 2016
Feb 26, 2018
Feb 26, 2018
Feb 26, 2018
Feb 26, 2018
Feb 26, 2018
Oct 22, 2016
Feb 26, 2018
Feb 26, 2018
Oct 22, 2016
Feb 26, 2018
Feb 26, 2018
Feb 26, 2018
Feb 26, 2018
Feb 26, 2018
Feb 26, 2018
Feb 26, 2018
Feb 26, 2018
Feb 26, 2018
Feb 26, 2018
Feb 26, 2018
Feb 26, 2018
Feb 26, 2018
Feb 26, 2018
Oct 22, 2016
Oct 22, 2016
Feb 26, 2018
Feb 26, 2018
Feb 26, 2018
Jul 19, 2019
Feb 26, 2018
Feb 26, 2018
Oct 22, 2016
Jan 25, 2017
Aug 22, 2017
Oct 22, 2016
Nov 11, 2016
Feb 26, 2018
Feb 26, 2018
Oct 22, 2016
Feb 26, 2018
Feb 26, 2018
Feb 26, 2018
Oct 22, 2016
Apr 13, 2020
Mar 3, 2021
Aug 22, 2017
Feb 26, 2018
Feb 26, 2018
Feb 26, 2018
Feb 26, 2018
Oct 22, 2016
Oct 22, 2016
Feb 26, 2018
Feb 26, 2018
Feb 26, 2018
Feb 26, 2018
Feb 26, 2018
Oct 22, 2016
Feb 26, 2018
Oct 22, 2016
Feb 26, 2018
Oct 22, 2016
Mar 2, 2021
Feb 26, 2018
Feb 26, 2018
Feb 26, 2018
Feb 26, 2018
Feb 26, 2018
Feb 26, 2018
Oct 22, 2016
Feb 26, 2018
Oct 22, 2016
Feb 26, 2018
Jan 9, 2017
Feb 26, 2018
Jul 19, 2019
Feb 26, 2018
Feb 26, 2018
Feb 26, 2018
Feb 26, 2018
Feb 26, 2018
Feb 26, 2018
Jul 19, 2019
Feb 26, 2018
Aug 22, 2017
Feb 26, 2018
Oct 22, 2016
Feb 26, 2018
Oct 22, 2016
Feb 26, 2018
Feb 26, 2018
Feb 26, 2018
Feb 26, 2018
Feb 26, 2018
Feb 26, 2018
Oct 22, 2016
Feb 26, 2018
Jul 19, 2019

Repository files navigation

This example shows how to insert data into a worksheet by setting cell.Value = collection

Collections.jpg

public void Create()
{
  var wb = new XLWorkbook();
  var ws = wb.Worksheets.Add("Collections");

  // From a list of strings
  var listOfStrings = new List<String>();
  listOfStrings.Add("House");
  listOfStrings.Add("Car");
  ws.Cell(1, 1).Value = "Strings";
  ws.Cell(1, 1).AsRange().AddToNamed("Titles");
  ws.Cell(2, 1).Value = listOfStrings;

  // From a list of arrays
  var listOfArr = new List<Int32[]>();
  listOfArr.Add(new Int32[] { 1, 2, 3 });
  listOfArr.Add(new Int32[] { 1 });
  listOfArr.Add(new Int32[] { 1, 2, 3, 4, 5, 6 });
  ws.Cell(1, 3).Value = "Arrays";
  ws.Range(1, 3, 1, 8).Merge().AddToNamed("Titles");
  ws.Cell(2, 3).Value = listOfArr;

  // From a DataTable
  var dataTable = GetTable();
  ws.Cell(6, 1).Value = "DataTable";
  ws.Range(6, 1, 6, 4).Merge().AddToNamed("Titles");
  ws.Cell(7, 1).Value = dataTable.AsEnumerable();

  // From a query
  var list = new List<Person>();
  list.Add(new Person() { Name = "John", Age = 30, House = "On Elm St."   });
  list.Add(new Person() { Name = "Mary", Age = 15, House = "On Main St."  });
  list.Add(new Person() { Name = "Luis", Age = 21, House = "On 23rd St."  });
  list.Add(new Person() { Name = "Henry", Age = 45, House = "On 5th Ave." });

  var people = from p in list
    where p.Age >= 21
    select new { p.Name, p.House, p.Age };

  ws.Cell(6, 6).Value = "Query";
  ws.Range(6, 6, 6, 8).Merge().AddToNamed("Titles");
  ws.Cell(7, 6).Value = people.AsEnumerable();    // Very Important to call the AsEnumerable method
  // otherwise it won't be copied.

  // Prepare the style for the titles
  var titlesStyle = wb.Style;
  titlesStyle.Font.Bold = true;
  titlesStyle.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
  titlesStyle.Fill.BackgroundColor = XLColor.Cyan;

  // Format all titles in one shot
  wb.NamedRanges.NamedRange("Titles").Ranges.Style = titlesStyle;

  ws.Columns().AdjustToContents();

  wb.SaveAs("Collections.xlsx");
}

class Person
{
  public String House { get; set; }
  public String Name { get; set; }
  public Int32 Age { get; set; }
}

private DataTable GetTable()
{
  DataTable table = new DataTable();
  table.Columns.Add("Dosage", typeof(int));
  table.Columns.Add("Drug", typeof(string));
  table.Columns.Add("Patient", typeof(string));
  table.Columns.Add("Date", typeof(DateTime));

  table.Rows.Add(25, "Indocin", "David", DateTime.Now);
  table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
  table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
  table.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
  table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);
  return table;
}