GettingStartedSample.cs

/*************************************************************************************************
  Required Notice: Copyright (C) EPPlus Software AB. 
  This software is licensed under PolyForm Noncommercial License 1.0.0 
  and may only be used for noncommercial purposes 
  https://polyformproject.org/licenses/noncommercial/1.0.0/

  A commercial license to use this software can be purchased at https://epplussoftware.com
 *************************************************************************************************
  Date               Author                       Change
 *************************************************************************************************
  01/27/2020         EPPlus Software AB           Initial release EPPlus 5
 *************************************************************************************************/
using OfficeOpenXml;
using System.Drawing;
using OfficeOpenXml.Style;
using System;

namespace EPPlusSamples.WorkbookWorksheetAndRanges
{
	public class CreateASimpleWorkbook
    {
        /// <summary>
        /// Sample 1.1 - Simply creates a new workbook from scratch.
        /// The workbook contains one worksheet with a simple invertory list
        /// Data is loaded manually via the Cells property of the Worksheet.
        /// </summary>
        public static void Run()
        {
            Console.WriteLine("Running sample 1.1");
            using (var package = new ExcelPackage())
            {
                //Add a new worksheet to the empty workbook
                var worksheet = package.Workbook.Worksheets.Add("Inventory");
                //Add the headers
                worksheet.Cells[1, 1].Value = "ID";
                worksheet.Cells[1, 2].Value = "Product";
                worksheet.Cells[1, 3].Value = "Quantity";
                worksheet.Cells[1, 4].Value = "Price";
                worksheet.Cells[1, 5].Value = "Value";

                //Add some items...
                worksheet.Cells["A2"].Value = 12001;
                worksheet.Cells["B2"].Value = "Nails";
                worksheet.Cells["C2"].Value = 37;
                worksheet.Cells["D2"].Value = 3.99;

                worksheet.Cells["A3"].Value = 12002;
                worksheet.Cells["B3"].Value = "Hammer";
                worksheet.Cells["C3"].Value = 5;
                worksheet.Cells["D3"].Value = 12.10;

                worksheet.Cells["A4"].Value = 12003;
                worksheet.Cells["B4"].Value = "Saw";
                worksheet.Cells["C4"].Value = 12;
                worksheet.Cells["D4"].Value = 15.37;

                //Add a formula for the value-column
                worksheet.Cells["E2:E4"].Formula = "C2 * D2";

                //Ok now format the values
                using (var range = worksheet.Cells[1, 1, 1, 5]) 
                {
                    range.Style.Font.Bold = true;
                    range.Style.Fill.PatternType = ExcelFillStyle.Solid;
                    range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue);
                    range.Style.Font.Color.SetColor(Color.White);
                }

                worksheet.Cells["A5:E5"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
                worksheet.Cells["A5:E5"].Style.Font.Bold = true;

                worksheet.Cells[5, 3, 5, 5].Formula = string.Format("SUBTOTAL(9,{0})", new ExcelAddress(2,3,4,3).Address);
                worksheet.Cells["C2:C5"].Style.Numberformat.Format = "#,##0";
                worksheet.Cells["D2:E5"].Style.Numberformat.Format = "#,##0.00";
                
                //Create an autofilter for the range
                worksheet.Cells["A1:E4"].AutoFilter = true;

                worksheet.Cells["A2:A4"].Style.Numberformat.Format = "@";   //Format as text

                //There is actually no need to calculate, Excel will do it for you, but in some cases it might be useful. 
                //For example if you link to this workbook from another workbook or you will open the workbook in a program that hasn't a calculation engine or 
                //you want to use the result of a formula in your program.
                worksheet.Calculate(); 

                worksheet.Cells.AutoFitColumns(0);  //Autofit columns for all cells

                // Lets set the header text. You can either use the Left/Centered or Right properties
                var ht = worksheet.HeaderFooter.OddHeader.Centered.AddText(" Inventory");
                ht.FontName = "Arial";
                ht.FontSize = 24;
                ht.Underline = true;
                ht.Bold = true;

                //...or set the text directly via the LeftAlignedText/CenteredText or RightAlignedText. In that case you need to set the codes in the text yourself. The codes are provided as constants in the ExcelHeaderFooter class. 
                //worksheet.HeaderFooter.OddHeader.CenteredText = "&24&U&\"Arial,Regular Bold\" Inventory";

                // Add the page number to the footer plus the total number of pages
                worksheet.HeaderFooter.OddFooter.RightAligned.AddText("Page ");
                worksheet.HeaderFooter.OddFooter.RightAligned.AddPageNumber();
                worksheet.HeaderFooter.OddFooter.RightAligned.AddText(" of ");
                worksheet.HeaderFooter.OddFooter.RightAligned.AddNumberOfPages();
                
                //string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);
                
                // Add the sheet name to the footer
                worksheet.HeaderFooter.OddFooter.Centered.AddSheetName();
                // Add the file path to the footer
                worksheet.HeaderFooter.OddFooter.LeftAligned.AddFilePath();
                worksheet.HeaderFooter.OddFooter.LeftAligned.AddFileName();

                worksheet.PrinterSettings.RepeatRows = worksheet.Cells["1:2"];
                worksheet.PrinterSettings.RepeatColumns = worksheet.Cells["A:G"];

                // Change the sheet view to show it in page layout mode
                worksheet.View.PageLayoutView = true;

                // Set some document properties
                package.Workbook.Properties.Title = "Inventory";
                package.Workbook.Properties.Author = "Jan Källman";
                package.Workbook.Properties.Comments = "This sample demonstrates how to create an Excel workbook using EPPlus";

                // Set some extended property values
                package.Workbook.Properties.Company = "EPPlus Software AB";

                // Set some custom property values
                package.Workbook.Properties.SetCustomPropertyValue("Checked by", "Jan Källman");
                package.Workbook.Properties.SetCustomPropertyValue("AssemblyName", "EPPlus");

                var xlFile = FileUtil.GetCleanFileInfo("1.01-GettingStarted.xlsx");
                
                // Save our new workbook in the output directory and we are done!
                package.SaveAs(xlFile);
                Console.WriteLine("Sample 1 created: {0}", xlFile.FullName);
                Console.WriteLine();
            }
		}
	}
}