Epplus使用案例1：最基本的例子

本系列学习代码从GitHub上下载，下载地址：

一、代码如下：

 1   using (var package = new ExcelPackage())
 2             {
 3                 //Add a new worksheet to the empty workbook
 4                 var worksheet = package.Workbook.Worksheets.Add("Inventory");
 5                 //Add the headers
 6                 worksheet.Cells[1, 1].Value = "ID";
 7                 worksheet.Cells[1, 2].Value = "Product";
 8                 worksheet.Cells[1, 3].Value = "Quantity";
 9                 worksheet.Cells[1, 4].Value = "Price";
10                 worksheet.Cells[1, 5].Value = "Value";
11 
12                 //Add some items...
13                 worksheet.Cells["A2"].Value = 12001;
14                 worksheet.Cells["B2"].Value = "Nails";
15                 worksheet.Cells["C2"].Value = 37;
16                 worksheet.Cells["D2"].Value = 3.99;
17 
18                 worksheet.Cells["A3"].Value = 12002;
19                 worksheet.Cells["B3"].Value = "Hammer";
20                 worksheet.Cells["C3"].Value = 5;
21                 worksheet.Cells["D3"].Value = 12.10;
22 
23                 worksheet.Cells["A4"].Value = 12003;
24                 worksheet.Cells["B4"].Value = "Saw";
25                 worksheet.Cells["C4"].Value = 12;
26                 worksheet.Cells["D4"].Value = 15.37;
27 
28                 //Add a formula for the value-column
29                 worksheet.Cells["E2:E4"].Formula = "C2*D2";
30 
31                 //Ok now format the values;
32                 //注意Cells[1, 1, 1, 5]表示一个范围：第一行第一列到第一行第五列
33                 using (var range = worksheet.Cells[1, 1, 1, 5])
34                 {
35                     range.Style.Font.Bold = true;
36                     range.Style.Fill.PatternType = ExcelFillStyle.Solid;
37                     range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue);
38                     range.Style.Font.Color.SetColor(Color.White);
39                 }
40 
41                 worksheet.Cells["A5:E5"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
42                 worksheet.Cells["A5:E5"].Style.Font.Bold = true;
43 
44                 //以下这句代码的意思是第五行第三列到第五行第五列的计算公式是求和。求和是类似于第二行第三列到第4行第三列的相加？
45                 worksheet.Cells[5, 3, 5, 5].Formula = string.Format("SUBTOTAL(9,{0})", new ExcelAddress(2, 3, 4, 3).Address);
46                 worksheet.Cells["C2:C5"].Style.Numberformat.Format = "#,##0";
47                 worksheet.Cells["D2:E5"].Style.Numberformat.Format = "#,##0.00";
48 
49                 //Create an autofilter for the range
50                 worksheet.Cells["A1:E4"].AutoFilter = true;
51 
52                 worksheet.Cells["A2:A4"].Style.Numberformat.Format = "@";   //Format as text
53 
54                 //There is actually no need to calculate, Excel will do it for you, but in some cases it might be useful. 
55                 //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 
56                 //you want to use the result of a formula in your program.
57                 worksheet.Calculate();
58 
59                 worksheet.Cells.AutoFitColumns(0);  //Autofit columns for all cells
60 
61                 // Lets set the header text 
62                 //设置头部标题
63                 worksheet.HeaderFooter.OddHeader.CenteredText = "&24&U&\"Arial,Regular Bold\" Inventory";
64                 // Add the page number to the footer plus the total number of pages
65                 worksheet.HeaderFooter.OddFooter.RightAlignedText =
66                     string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);
67                 // Add the sheet name to the footer
68                 worksheet.HeaderFooter.OddFooter.CenteredText = ExcelHeaderFooter.SheetName;
69                 // Add the file path to the footer
70                 worksheet.HeaderFooter.OddFooter.LeftAlignedText = ExcelHeaderFooter.FilePath + ExcelHeaderFooter.FileName;
71 
72                 worksheet.PrinterSettings.RepeatRows = worksheet.Cells["1:2"];
73                 worksheet.PrinterSettings.RepeatColumns = worksheet.Cells["A:G"];
74 
75                 // Change the sheet view to show it in page layout mode
76                 worksheet.View.PageLayoutView = true;
77 
78                 // Set some document properties
79                 package.Workbook.Properties.Title = "Invertory";
80                 package.Workbook.Properties.Author = "Jan K?llman";
81                 package.Workbook.Properties.Comments = "This sample demonstrates how to create an Excel workbook using EPPlus";
82 
83                 // Set some extended property values
84                 package.Workbook.Properties.Company = "EPPlus Software AB";
85 
86                 // Set some custom property values
87                 package.Workbook.Properties.SetCustomPropertyValue("Checked by", "Jan K?llman");
88                 package.Workbook.Properties.SetCustomPropertyValue("AssemblyName", "EPPlus");
89 
90                 var xlFile = FileOutputUtil.GetFileInfo("01-GettingStarted.xlsx");
91 
92                 // Save our new workbook in the output directory and we are done!
93                 package.SaveAs(xlFile);
94                 return View(xlFile.FullName);
95             }

二、运行效果如下：

1. 主体：

2. 尾部：

三、主要代码备注：

1. Value这一列的计算方式：

2. 第一行表头的格式设置：

允许过滤：

3. 最后一行统计的计算方式和数据格式：

 数据格式设置为text的写法如下：

 4. 尾部页码、标题等请见上面源代码