Content Sheet Example
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";