1. Products
  2.   Spreadsheet
  3.   .NET
  4.   ClosedXML
 
  

Open Source .NET Library for Manipulating Excel Spreadsheets

ClosedXML allows you to read, manipulate and write Microsoft Excel Documents

ClosedXML is an open source C# API for reading, manipulating and writing Microsoft Excel 2007+ (.xlsx, .xlsm) documents. The API allows you to create Excel files without using the Excel application, and read files using the enhanced features.

Using the API you can style your workbooks by using background coloring and cell bordering. You can add, remove and move your worksheets and manage datatypes inside excel.

Previous Next

Getting Started with ClosedXML

The recommended way to install ClosedXML is from NuGet, Please use the following command for faster installation.

Install ClosedXML from NuGet

 Install-Package ClosedXML

Create New Workbooks for Free using C#

CLosedXML allows C# .NET developers to create new excel worksheets. You can create a blank workbook using the XLWorkbook() method. The library has included several important features for managing your worksheet documents. It allows to add worksheets in your workbook, add cells and columns, apply styles to rows and columns, delete the unwanted cells and many more.

Create New Workbooks via .NET API


using (var workbook = new XLWorkbook())
{
    var worksheet = workbook.Worksheets.Add("Sample Sheet");
    worksheet.Cell("A1").Value = "Hello World!";
    worksheet.Cell("A2").FormulaA1 = "=MID(A1, 7, 5)";
    workbook.SaveAs("HelloWorld.xlsx");
}

Style Excel Sheets using C#

The open source ClosedXML API enables software developers to apply styles to their excel sheets with just a couple of lines of C# code. The API is enriched with styling features including alignment, border, fill, font, number format, styling rows, columns, and more. The library also allows users to alter the styling according to their needs.

How to Apply Styles to Excel Sheets via C# Library


            var path = Program.BaseCreatedDirectory;
            new StyleFont().Create(Path.Combine(path, "styleFont.xlsx"));
            new StyleFill().Create(Path.Combine(path, "styleFill.xlsx"));
            new StyleBorder().Create(Path.Combine(path, "styleBorder.xlsx"));
            new StyleAlignment().Create(Path.Combine(path, "styleAlignment.xlsx"));
            new StyleNumberFormat().Create(Path.Combine(path, "styleNumberFormat.xlsx"));
            new StyleIncludeQuotePrefix().Create(Path.Combine(path, "styleIncludeQuotePrefix.xlsx"));

Using Auto Filters in Excel using C#

ClosedXML library has included complete support for applying filters inside your excel worksheets. The library has included various types of filters for the user's convenience. You can apply the filter to a specific range, apply a filter to values and create your own custom filters as well.

How to Apply Auto Filters in Excel via C#

public class DynamicAutoFilter : IXLExample
    {
        public void Create(string filePath)
        {
            var wb = new XLWorkbook();
            IXLWorksheet ws;

            #region Single Column Numbers
            String singleColumnNumbers = "Single Column Numbers";
            ws = wb.Worksheets.Add(singleColumnNumbers);

            // Add a bunch of numbers to filter
            ws.Cell("A1").SetValue("Numbers")
                         .CellBelow().SetValue(2)
                         .CellBelow().SetValue(3)
                         .CellBelow().SetValue(3)
                         .CellBelow().SetValue(5)
                         .CellBelow().SetValue(1)
                         .CellBelow().SetValue(4);

            // Add filters
            ws.RangeUsed().SetAutoFilter().Column(1).AboveAverage();

            // Sort the filtered list
            //ws.AutoFilter.Sort(1);
            #endregion

            #region Multi Column
            String multiColumn = "Multi Column";
            ws = wb.Worksheets.Add(multiColumn);

            ws.Cell("A1").SetValue("First")
             .CellBelow().SetValue("B")
             .CellBelow().SetValue("C")
             .CellBelow().SetValue("C")
             .CellBelow().SetValue("E")
             .CellBelow().SetValue("A")
             .CellBelow().SetValue("D");

            ws.Cell("B1").SetValue("Numbers")
                         .CellBelow().SetValue(2)
                         .CellBelow().SetValue(3)
                         .CellBelow().SetValue(3)
                         .CellBelow().SetValue(5)
                         .CellBelow().SetValue(1)
                         .CellBelow().SetValue(4);

            ws.Cell("C1").SetValue("Strings")
             .CellBelow().SetValue("B")
             .CellBelow().SetValue("C")
             .CellBelow().SetValue("C")
             .CellBelow().SetValue("E")
             .CellBelow().SetValue("A")
             .CellBelow().SetValue("D");

            // Add filters
            ws.RangeUsed().SetAutoFilter().Column(2).BelowAverage();

            // Sort the filtered list
            //ws.AutoFilter.Sort(3);
            #endregion

            using (var ms = new MemoryStream())
            {
                wb.SaveAs(ms);

                var workbook = new XLWorkbook(ms);

                #region Single Column Numbers
                //workbook.Worksheet(singleColumnNumbers).AutoFilter.Sort(1, XLSortOrder.Descending);
                #endregion

                #region Multi Column 
                //workbook.Worksheet(multiColumn).AutoFilter.Sort(3, XLSortOrder.Descending);
                #endregion

                workbook.SaveAs(filePath);
                ms.Close();
            }
        }
    }
 English