ClosedXML – an easier way of using Open XML

ClosedXML is a library which provides an easy way to manipulate Open XML Excel files. It offers VBA like usability, and allows developers to concentrate on developing a solution rather than writing libraries to manipulate XML files. ClosedXML can be used from any .Net language.

ClosedXML requires Open XML SDK 2.0, which you can download from Microsoft. It doesn’t work with Open XML SDK 2.5. Once you have installed the SDK, download ClosedXML and you are set to go.

To use it, add reference to DocumentFormat.OpenXml and ClosedXML libraries to your project. ClosedXML site provides many coding examples and they are all very easy to follow and understand.

I’ve written a simple example which shows you how to create a file, do basic manipulation and then stream the file through the browser.

        private void GenerateDoc()
        {
            XLWorkbook wb = new XLWorkbook();
            IXLWorksheet ws = wb.Worksheets.Add("Test");

            IXLCell a1 = ws.Cell("A1");
            a1.Value = "Basic cell";

            IXLCell a2 = ws.Cell("A2");
            a2.Value = "Bold text";
            a2.Style.Font.Bold = true;

            IXLCell a3 = ws.Cell("A3");
            a3.Value = "Red text";
            a3.Style.Font.FontColor = XLColor.Red;

            IXLCell a4 = ws.Cell("A4");
            a4.Value = "Font size";
            a4.Style.Font.FontSize = 8;

            IXLCell a5 = ws.Cell("A5");
            a5.Value = "Font change";
            a5.Style.Font.FontName = "Bauhaus 93";

            IXLCell c1 = ws.Cell("C1");
            c1.Value = "Border";
            c1.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;

            IXLCell c3 = ws.Cell("C3");
            c3.Value = "Color Border";
            c3.Style.Border.OutsideBorder = XLBorderStyleValues.Thick;
            c3.Style.Border.OutsideBorderColor = XLColor.Yellow;

            IXLCell e1 = ws.Cell("E1");
            e1.Value = "Comment";
            e1.Comment.AddText("This is a comment.").AddNewLine().AddText("And more text after new line.");

            IXLCell g1 = ws.Cell("G1");
            g1.Value = "Color fill";
            g1.Style.Fill.BackgroundColor = XLColor.Green;

            IXLCell g3 = ws.Cell("G3");
            g3.Value = "Merged rows";
            ws.Range("G3:I3").Row(1).Merge();

            IXLCell g5 = ws.Cell("G5");
            g5.Value = "Merged column";
            ws.Range("G5:G7").Column(1).Merge();

            IXLColumn colJ = ws.Column("J");
            colJ.Width = 2;
            
            //Stream output
            HttpResponse httpResponse = Response;
            httpResponse.Clear();
            httpResponse.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            httpResponse.AddHeader("content-disposition", "attachment;filename=\"HelloWorld.xlsx\"");

            using (MemoryStream ms = new MemoryStream())
            {
                wb.SaveAs(ms);
                ms.WriteTo(httpResponse.OutputStream);
                ms.Close();
            }

            httpResponse.End();
        }
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s