Content Sheet Example

sample61.jpg
Let’s look at some code from the sample. (This is from the version 2.8.0.1)
First we create a new workbook and add a worksheet...

            FileInfo newFile = new FileInfo(outputDir.FullName + @"\sample6.xlsx");

            ExcelPackage pck = new ExcelPackage(newFile);
            //Add the Content sheet
            var ws = pck.Workbook.Worksheets.Add("Content");
            ws.View.ShowGridLines = false;

Then create an outline for column 4 and 5 and hide them.

            ws.Column(4).OutlineLevel = 1;
            ws.Column(4).Collapsed = true;
            ws.Column(5).OutlineLevel = 1;
            ws.Column(5).Collapsed = true;
            ws.OutLineSummaryRight = true;

Set the header values and set font style to bold.

            //Headers
            ws.Cells["B1"].Value = "Name";
            ws.Cells["C1"].Value = "Size";
            ws.Cells["D1"].Value = "Created";
            ws.Cells["E1"].Value = "Last modified";
            ws.Cells["B1:E1"].Style.Font.Bold = true;

Here is an example of how the bitmaps are added. You can add most types of image files using the AddPicture method.
The SetPosition method sets the top left corner of the image, keeping its original size. 

                Bitmap icon = GetIcon(dir.FullName);

                ws.Row(row).Height = height;
                //Add the icon as a picture
                if (icon != null)
                {
                    ExcelPicture pic = ws.Drawings.AddPicture("pic" + (row).ToString(), icon);
                    pic.SetPosition((int)20 * (row - 1) + 2, 0);
                }

Add the subtotal formula...

                ws.Cells[prevRow, 3].Formula = string.Format("SUBTOTAL(9, {0})", ExcelCellBase.GetAddress(prevRow + 1, 3, row - 1, 3));

Now we add the shape containing the text and sets a bunch of properties.

            //Add the textbox
            var shape = ws.Drawings.AddShape("txtDesc", eShapeStyle.Rect);
            shape.SetPosition(0, 5, 5, 5);
            shape.SetSize(400, 200);

            shape.Text = "This example demonstrates how to create various drawing objects like Pictures, Shapes and charts.\n\r\n\rThe first sheet...";
            shape.Fill.Style = eFillStyle.SolidFill;
            shape.Fill.Color = Color.DarkSlateGray;
            shape.Fill.Transparancy = 20;
            shape.Border.Fill.Style = eFillStyle.SolidFill;
            shape.Border.LineStyle = eLineStyle.LongDash;
            shape.Border.Width = 1;
            shape.Border.Fill.Color = Color.Black;
            shape.Border.LineCap = eLineCap.Round;
            shape.TextAnchoring = eTextAnchoringType.Top;
            shape.TextVertical = eTextVerticalType.Horizontal;
            shape.TextAnchoringControl=false;

And last create the hyperlink. We create a named style matching Excels hyperlink style.

            //Add a HyperLink to the statistics sheet. 
            var namedStyle = pck.Workbook.Styles.CreateNamedStyle("HyperLink");   //This one is language dependent
            namedStyle.Style.Font.UnderLine = true;
            namedStyle.Style.Font.Color.SetColor(Color.Blue);
            ws.Cells["K12"].Hyperlink = new ExcelHyperLink("Statistics!A1", "Statistics");
            ws.Cells["K12"].StyleName = "HyperLink";

Last edited Dec 23, 2010 at 7:36 AM by akronite, version 2

Comments

dtakis Mar 12 at 8:38 AM 
@morilog it seems that you are not building your project targeting .net framework 4.0. Select framework 4.0 and recompile.

wuwenjie Jul 12, 2013 at 6:30 AM 
that's great! How can I make the width of the column fit to the text ?

morilog Jun 3, 2013 at 11:33 PM 
when is run my application i got this warning :
Warning 1 The referenced assembly "EPPlus" could not be resolved because it has a dependency on "System.Web, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" which is not in the currently targeted framework ".NETFramework,Version=v4.0,Profile=Client". Please remove references to assemblies not in the targeted framework or consider retargeting your project.

pls help me

sparida May 29, 2012 at 9:10 AM 
I need to look into how you created the groupings. Can I see the entire code how to create this content sheet.

Thanks
Sangram

vikasbhandari2 Aug 12, 2011 at 12:11 PM 
Excellent.

Does it support Multiple thread calls? Suppose I am willing to use this code to use on my server to read a single Excel File and then display it on user's computer. I will check it though.. Great great initiative :)

Thanks,
Vikas

akronite Jan 23, 2011 at 10:50 PM 
Please post your questions in our discussion or issue tracker.
thank you

vladimirbc Jan 19, 2011 at 6:37 PM 
Excelent. But I m in dificult to use it. Please, how do I get a shape or chart already existing in a template for changes?
Can you Help me?
Thanks