In the modern world of application development, it is often necessary to work with Excel documents. Most often these are various kinds of reports, but sometimes xls / x files are used as data storage. For example, if a user needs to be able to upload data to an application or upload it in a human-readable form, Excel is the de facto standard. Relatively friendly interface, transparent structure, coupled with its prevalence ... it's hard to name a better solution offhand.
However, many people still associate Excel with something heavy, clumsy and complex. Let's see how we, ordinary C # developers, can easily generate a simple Excel document, using the example of a tabular report.
Historical reference
The days when the proprietary .xls (Excel Binary File Format) dominated are long gone and now we only have .xlsx (Excel Workbook), within the Office Open XML. The latter is a regular .zip archive with XML files. We will not delve into its structure, I sincerely hope that you will never need it.
On github, and not only, you can find a number of libraries , free and not only. Perhaps the most popular is EPPlus. To a certain extent, it reflects the Excel concept pretty well, which is why I always use EPPlus. Version 4 is completely free, starting with version 5 you will need to purchase a license for commercial use.
Task
So, suppose the product manager got the idea that the ability to download a certain report in Excel format will increase the number of users by 100500%. The project manager decides to roll out this killer feature as a hotfix right now - after all, the work is only for a couple of hours.
, . — . — , . , - 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;What does the final version of the Generate method say?
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();
}First of all, first of all, that we have successfully coped with the task, namely, generated our first Excel report, worked with styles and even solved a couple of related problems.
Secondly, it may make sense to look for a new job, but, looking ahead , I would not be in a hurry with this ... If this publication gets 1+ views, then in the second part we will talk about how you can separate the styling from the data filling logic , simplify cell manipulations and make the code more maintainable in general.