"Learn how to create a powerful Excel-driven C# console application. This step-by-step guide demonstrates using EPPlus to generate, populate, and read Excel data for the 50 most populous countries. Perfect for developers looking to master C# arrays, Excel integration, and data formatting. #CSharp #ExcelProgramming"
In this article, we will build a console application in C# to handle and display population data for the 50 most populous countries. The application will use the EPPlus library to create and manage an Excel file programmatically. We’ll populate the Excel sheet with sample data, read it into a structured format, and display the data with proper formatting.
What You'll Learn
- How to create and write data to an Excel file programmatically using EPPlus.
- How to read and parse data from an Excel sheet.
- How to initialize and populate arrays dynamically.
- How to format output for better readability.
Prerequisites
- Install the EPPlus Library Add the EPPlus NuGet package to your project:
Install-Package EPPlus
- Create a Console Application Create a new C# console application in your preferred IDE.
Step 1: Define the Country Class
The Country class represents a single country, including its name, code, region, and population.
public class Country
{
public string Name { get; }
public string Code { get; }
public string Region { get; }
public int Population { get; }
public Country(string name, string code, string region, int population)
{
Name = name;
Code = code;
Region = region;
Population = population;
}
}
Step 2: Create the ExcelReaderWriter Class
This class will manage creating the Excel sheet, populating it with sample data, and reading data back from it.
using OfficeOpenXml;
using System.Collections.Generic;
using System.IO;
public class ExcelReaderWriter
{
private readonly string _filePath;
public ExcelReaderWriter(string filePath)
{
_filePath = filePath;
}
// Method to create an Excel sheet and populate it with sample data
public void CreateSampleData()
{
using (var package = new ExcelPackage())
{
var worksheet = package.Workbook.Worksheets.Add("Countries");
// Add headers
worksheet.Cells[1, 1].Value = "Name";
worksheet.Cells[1, 2].Value = "Code";
worksheet.Cells[1, 3].Value = "Region";
worksheet.Cells[1, 4].Value = "Population";
// Add sample data
var sampleData = new List<Country>
{
new Country("China", "CN", "Asia", 1412600000),
new Country("India", "IN", "Asia", 1366417754),
new Country("United States", "US", "Americas", 331883986),
new Country("Indonesia", "ID", "Asia", 273523621),
new Country("Pakistan", "PK", "Asia", 220892331),
new Country("Brazil", "BR", "Americas", 212559409),
new Country("Nigeria", "NG", "Africa", 206139587),
new Country("Bangladesh", "BD", "Asia", 164689383),
new Country("Russia", "RU", "Europe", 144104080),
new Country("Mexico", "MX", "Americas", 128932753),
new Country("Japan", "JP", "Asia", 125960000),
new Country("Ethiopia", "ET", "Africa", 114963588),
new Country("Philippines", "PH", "Asia", 109581085),
new Country("Egypt", "EG", "Africa", 102334404),
new Country("Vietnam", "VN", "Asia", 97338583),
new Country("DR Congo", "CD", "Africa", 89561403),
new Country("Turkey", "TR", "Europe", 84339067),
new Country("Iran", "IR", "Asia", 83992949),
new Country("Germany", "DE", "Europe", 83240525),
new Country("Thailand", "TH", "Asia", 69799978),
new Country("United Kingdom", "GB", "Europe", 68116730),
new Country("France", "FR", "Europe", 65273511),
new Country("Italy", "IT", "Europe", 60317116),
new Country("South Africa", "ZA", "Africa", 59308690),
new Country("Tanzania", "TZ", "Africa", 59734218),
new Country("Myanmar", "MM", "Asia", 54817919),
new Country("South Korea", "KR", "Asia", 51780579),
new Country("Colombia", "CO", "Americas", 50882891),
new Country("Kenya", "KE", "Africa", 53771296),
new Country("Spain", "ES", "Europe", 46754778),
new Country("Argentina", "AR", "Americas", 45195774),
new Country("Uganda", "UG", "Africa", 45741007),
new Country("Ukraine", "UA", "Europe", 41723998),
new Country("Sudan", "SD", "Africa", 43849260),
new Country("Algeria", "DZ", "Africa", 43851044),
new Country("Poland", "PL", "Europe", 38386000),
new Country("Canada", "CA", "Americas", 38005238),
new Country("Morocco", "MA", "Africa", 36910560),
new Country("Saudi Arabia", "SA", "Asia", 34813871),
new Country("Uzbekistan", "UZ", "Asia", 34574118),
new Country("Peru", "PE", "Americas", 33050325),
new Country("Venezuela", "VE", "Americas", 28435943),
new Country("Malaysia", "MY", "Asia", 32365999),
new Country("Afghanistan", "AF", "Asia", 38928346),
new Country("Ghana", "GH", "Africa", 31072940),
new Country("Angola", "AO", "Africa", 32866272),
new Country("Mozambique", "MZ", "Africa", 31255435),
new Country("Nepal", "NP", "Asia", 29136808),
new Country("Australia", "AU", "Oceania", 25687041),
};
for (int i = 0; i < sampleData.Count; i++)
{
worksheet.Cells[i + 2, 1].Value = sampleData[i].Name;
worksheet.Cells[i + 2, 2].Value = sampleData[i].Code;
worksheet.Cells[i + 2, 3].Value = sampleData[i].Region;
worksheet.Cells[i + 2, 4].Value = sampleData[i].Population;
}
// Save the file
var fileInfo = new FileInfo(_filePath);
package.SaveAs(fileInfo);
}
}
// Method to read the first N countries from the Excel sheet
public Country[] ReadFirstNCountries(int nCountries)
{
Country[] countries = new Country[nCountries];
using (var package = new ExcelPackage(new FileInfo(_filePath)))
{
var worksheet = package.Workbook.Worksheets["Countries"];
for (int i = 0; i < nCountries; i++)
{
var row = i + 2; // Data starts from the second row
var name = worksheet.Cells[row, 1].Value?.ToString();
var code = worksheet.Cells[row, 2].Value?.ToString();
var region = worksheet.Cells[row, 3].Value?.ToString();
var population = int.Parse(worksheet.Cells[row, 4].Value?.ToString() ?? "0");
countries[i] = new Country(name, code, region, population);
}
}
return countries;
}
}
Step 3: Add the PopulationFormatter Class
To display population data in a readable format, we'll use a helper class:
public static class PopulationFormatter
{
public static string FormatPopulation(int population)
{
return population.ToString("N0"); // Adds commas for thousands
}
}
Step 4: Update the Main Method
Here’s the Main method to tie everything together:
using System;
class Program
{
static void Main(string[] args)
{
string filePath = "Countries.xlsx";
var excelHandler = new ExcelReaderWriter(filePath);
// Step 1: Create Excel with Sample Data
Console.WriteLine("Creating sample data...");
excelHandler.CreateSampleData();
Console.WriteLine($"Sample data created at {filePath}");
// Step 2: Read the Top 10 Countries from Excel
Console.WriteLine("Reading data from Excel...");
var countries = excelHandler.ReadFirstNCountries(10);
// Step 3: Display the Countries
Console.WriteLine("\nTop 10 Most Populated Countries:");
foreach (var country in countries)
{
if (country != null)
{
Console.WriteLine($"{country.Name.PadRight(20)} {PopulationFormatter.FormatPopulation(country.Population).PadLeft(15)}");
}
}
}
}
Expected Output
When you run the program, you’ll see:
Creating sample data...
Sample data created at Countries.xlsx
Reading data from Excel...
Top 10 Most Populated Countries:
China 1,412,600,000
India 1,366,417,754
United States 331,883,986
Indonesia 273,523,621
Pakistan 220,892,331
Brazil 212,559,409
Nigeria 206,139,587
Bangladesh 164,689,383
Russia 144,104,080
Mexico 128,932,753
Conclusion
This example demonstrates how to:
- Create an Excel file programmatically.
- Populate it with sample data.
- Read and display the top 10 countries in a formatted manner.
Top comments (0)