Clever Geek Handbook

C＃中的Excel入门

在现代应用程序开发世界中，通常需要使用Excel文档。大多数情况下，这些报告是各种报告，但有时xls / x文件用作数据存储。例如，如果用户需要能够将数据上载到应用程序或以人类可读的形式上载数据，则Excel是事实上的标准。相对友好的界面，透明的结构以及它的普遍性……很难一眼就找到更好的解决方案。

但是，许多人仍然将Excel与沉重，笨拙和复杂的事物联系在一起。让我们看看普通的C＃开发人员如何以表格报表为例轻松生成简单的Excel文档。

历史参考

专有.xls（Excel二进制文件格式）占主导地位的日子已经一去不复返了，现在在Office Open XML中只有.xlsx（Excel工作簿）。后者是带有XML文件的常规.zip存档。我们不会深入研究它的结构，我衷心希望您永远不需要它。

不仅在github上，您还可以找到许多库，不仅免费，而且免费。也许最受欢迎的是EPPlus。在某种程度上，它很好地反映了Excel概念，这就是为什么我始终使用EPPlus。版本4是完全免费的，从版本5开始，您将需要购买商业使用许可。

任务

因此，假设产品经理想到以Excel格式下载报表的功能将使用户数量增加100500％。项目经理决定立即推出此杀手级功能作为修补程序-毕竟，这项工作仅需要几个小时。

, . — . — , . , - backend- - , id . , id , .

, ,   - " excel MarketReport". , , — :

EPPlus 4.5.3.3 .

Generate. ExcelPackage ,   . .

main , Excel . .

, exception:InvalidOperationException: The workbook must contain at least one worksheet

, Excel , . , :

var sheet = package.Workbook.Worksheets    
		.Add("Market Report");

... ! , , 2,5KB - Excel .

. Cells . , , - :

sheet.Cells["B2"].Value = "Company:";
sheet.Cells[2, 3].Value = report.Company.Name;
.
sheet.Cells["B2"].Value = "Company:";
sheet.Cells[2, 3].Value = report.Company.Name;
sheet.Cells["B3"].Value = "Location:";
sheet.Cells["C3"].Value = $"{report.Company.Address}, " +
  												$"{report.Company.City}, " +                          
  												$"{report.Company.Country}";
sheet.Cells["B4"].Value = "Sector:";
sheet.Cells["C4"].Value = report.Company.Sector;
sheet.Cells["B5"].Value = report.Company.Description;

History:

sheet.Cells[8, 2, 8, 4].LoadFromArrays(new object[][]{ new []{"Capitalization", "SharePrice", "Date"} });
var row = 9;
var column = 2;
foreach (var item in report.History)
{
  sheet.Cells[row, column].Value = item.Capitalization;
  sheet.Cells[row, column + 1].Value = item.SharePrice;
  sheet.Cells[row, column + 2].Value = item.Date;    
  row++;
}

LoadFromArrays, () . , object EPPlus ToString, .

, , .

-, , - ... ,  , " - " - .

, , , , , ... , backend , Excel Sheet!

. — , — . ?

- Excel, , . , ... , :

sheet.Cells[1, 1, row, column + 2].AutoFitColumns();
sheet.Column(2).Width = 14;
sheet.Column(3).Width = 12;

, Style. 3- . , ...

sheet.Cells[9, 4, 9 + report.History.Length, 4].Style.Numberformat.Format = "yyyy";
sheet.Cells[9, 2, 9 + report.History.Length, 2].Style.Numberformat.Format =  "### ### ### ##0";

, . , EPPlus, — ExcelRange, , 1 .

sheet.Column(2).Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
sheet.Cells[8, 3, 8 + report.History.Length, 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

, Style.Font, , , 2- , , Excel:

sheet.Cells[8, 2, 8, 4].Style.Font.Bold = true;
sheet.Cells["B2:C4"].Style.Font.Bold = true;

, . - - , ... ?

sheet.Cells[8, 2, 8 + report.History.Length, 4].Style.Border.BorderAround(ExcelBorderStyle.Double);
sheet.Cells[8, 2, 8, 4].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;

" , , ?" - , , 9-...

, EPPlus API. , :

var capitalizationChart = sheet.Drawings.AddChart("FindingsChart", OfficeOpenXml.Drawing.Chart.eChartType.Line);
capitalizationChart.Title.Text = "Capitalization";
capitalizationChart.SetPosition(7, 0, 5, 0);
capitalizationChart.SetSize(800, 400);
var capitalizationData = (ExcelChartSerie)(capitalizationChart.Series.Add(sheet.Cells["B9:B28"], sheet.Cells["D9:D28"]));
capitalizationData.Header = report.Company.Currency;

, :

sheet.Protection.IsProtected = true;

,   .

生成方法的最终版本怎么说？
public byte[] Generate(MarketReport report)
{    
  var package = new ExcelPackage();    
  
  var sheet = package.Workbook.Worksheets        
    	.Add("Market Report");      
  
  sheet.Cells["B2"].Value = "Company:";    
  sheet.Cells[2, 3].Value = report.Company.Name;    
  sheet.Cells["B3"].Value = "Location:";    
  sheet.Cells["C3"].Value = $"{report.Company.Address}, " +
    												$"{report.Company.City}, " +                             
    												$"{report.Company.Country}";    
  sheet.Cells["B4"].Value = "Sector:";    
  sheet.Cells["C4"].Value = report.Company.Sector;    
  sheet.Cells["B5"].Value = report.Company.Description;    
  
  sheet.Cells[8, 2, 8, 4].LoadFromArrays(new object[][]{ new []{"Capitalization", "SharePrice", "Date"} });    
  var row = 9;    
  var column = 2;    
  foreach (var item in report.History)    
  {        
    	sheet.Cells[row, column].Value = item.Capitalization;        
   		sheet.Cells[row, column + 1].Value = item.SharePrice;        
   		sheet.Cells[row, column + 2].Value = item.Date;        
    	row++;    
  }    
  
  sheet.Cells[1, 1, row, column + 2].AutoFitColumns();    
  sheet.Column(2).Width = 14;    
  sheet.Column(3).Width = 12;        
  
  sheet.Cells[9, 4, 9+ report.History.Length, 4].Style.Numberformat.Format = "yyyy";    
  sheet.Cells[9, 2, 9+ report.History.Length, 2].Style.Numberformat.Format =  "### ### ### ##0";    
  
  sheet.Column(2).Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;    
  sheet.Cells[8, 3, 8 + report.History.Length, 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;    
  sheet.Column(4).Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;    
  
  sheet.Cells[8, 2, 8, 4].Style.Font.Bold = true;    
  sheet.Cells["B2:C4"].Style.Font.Bold = true;
  
  sheet.Cells[8, 2, 8 + report.History.Length, 4].Style.Border.BorderAround(ExcelBorderStyle.Double);    
  sheet.Cells[8, 2, 8, 4].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;     
  
  var capitalizationChart = sheet.Drawings.AddChart("FindingsChart", OfficeOpenXml.Drawing.Chart.eChartType.Line);    
  capitalizationChart.Title.Text = "Capitalization";    
  capitalizationChart.SetPosition(7, 0, 5, 0);    
  capitalizationChart.SetSize(800, 400);    
  var capitalizationData = (ExcelChartSerie)(capitalizationChart.Series.Add(sheet.Cells["B9:B28"], sheet.Cells["D9:D28"]));    
  capitalizationData.Header = report.Company.Currency;       
  
  sheet.Protection.IsProtected = true;    
  
  return package.GetAsByteArray();
}

首先，首先，我们已经成功地完成了任务，即生成了我们的第一个Excel报告，使用了样式，甚至解决了一些相关问题。

其次，寻找一份新工作可能很有意义，但是，展望未来，我不会着急...如果此出版物获得1+的意见，那么在第二部分中，我们将讨论如何将样式与数据填充逻辑分开简化了单元操作，并且通常使代码更具可维护性。



More articles:


All Articles