"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 theEPPlus 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 usingEPPlus.
- 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 LibraryAdd the EPPlus NuGet package to your project:
Install-Package EPPlus- Create a Console ApplicationCreate a new C# console application in your preferred IDE.
Step 1: Define theCountry Class
TheCountry class represents a single country, including its name, code, region, and population.
publicclassCountry{publicstringName{get;}publicstringCode{get;}publicstringRegion{get;}publicintPopulation{get;}publicCountry(stringname,stringcode,stringregion,intpopulation){Name=name;Code=code;Region=region;Population=population;}}Step 2: Create theExcelReaderWriter Class
This class will manage creating the Excel sheet, populating it with sample data, and reading data back from it.
usingOfficeOpenXml;usingSystem.Collections.Generic;usingSystem.IO;publicclassExcelReaderWriter{privatereadonlystring_filePath;publicExcelReaderWriter(stringfilePath){_filePath=filePath;}// Method to create an Excel sheet and populate it with sample datapublicvoidCreateSampleData(){using(varpackage=newExcelPackage()){varworksheet=package.Workbook.Worksheets.Add("Countries");// Add headersworksheet.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 datavarsampleData=newList<Country>{newCountry("China","CN","Asia",1412600000),newCountry("India","IN","Asia",1366417754),newCountry("United States","US","Americas",331883986),newCountry("Indonesia","ID","Asia",273523621),newCountry("Pakistan","PK","Asia",220892331),newCountry("Brazil","BR","Americas",212559409),newCountry("Nigeria","NG","Africa",206139587),newCountry("Bangladesh","BD","Asia",164689383),newCountry("Russia","RU","Europe",144104080),newCountry("Mexico","MX","Americas",128932753),newCountry("Japan","JP","Asia",125960000),newCountry("Ethiopia","ET","Africa",114963588),newCountry("Philippines","PH","Asia",109581085),newCountry("Egypt","EG","Africa",102334404),newCountry("Vietnam","VN","Asia",97338583),newCountry("DR Congo","CD","Africa",89561403),newCountry("Turkey","TR","Europe",84339067),newCountry("Iran","IR","Asia",83992949),newCountry("Germany","DE","Europe",83240525),newCountry("Thailand","TH","Asia",69799978),newCountry("United Kingdom","GB","Europe",68116730),newCountry("France","FR","Europe",65273511),newCountry("Italy","IT","Europe",60317116),newCountry("South Africa","ZA","Africa",59308690),newCountry("Tanzania","TZ","Africa",59734218),newCountry("Myanmar","MM","Asia",54817919),newCountry("South Korea","KR","Asia",51780579),newCountry("Colombia","CO","Americas",50882891),newCountry("Kenya","KE","Africa",53771296),newCountry("Spain","ES","Europe",46754778),newCountry("Argentina","AR","Americas",45195774),newCountry("Uganda","UG","Africa",45741007),newCountry("Ukraine","UA","Europe",41723998),newCountry("Sudan","SD","Africa",43849260),newCountry("Algeria","DZ","Africa",43851044),newCountry("Poland","PL","Europe",38386000),newCountry("Canada","CA","Americas",38005238),newCountry("Morocco","MA","Africa",36910560),newCountry("Saudi Arabia","SA","Asia",34813871),newCountry("Uzbekistan","UZ","Asia",34574118),newCountry("Peru","PE","Americas",33050325),newCountry("Venezuela","VE","Americas",28435943),newCountry("Malaysia","MY","Asia",32365999),newCountry("Afghanistan","AF","Asia",38928346),newCountry("Ghana","GH","Africa",31072940),newCountry("Angola","AO","Africa",32866272),newCountry("Mozambique","MZ","Africa",31255435),newCountry("Nepal","NP","Asia",29136808),newCountry("Australia","AU","Oceania",25687041),};for(inti=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 filevarfileInfo=newFileInfo(_filePath);package.SaveAs(fileInfo);}}// Method to read the first N countries from the Excel sheetpublicCountry[]ReadFirstNCountries(intnCountries){Country[]countries=newCountry[nCountries];using(varpackage=newExcelPackage(newFileInfo(_filePath))){varworksheet=package.Workbook.Worksheets["Countries"];for(inti=0;i<nCountries;i++){varrow=i+2;// Data starts from the second rowvarname=worksheet.Cells[row,1].Value?.ToString();varcode=worksheet.Cells[row,2].Value?.ToString();varregion=worksheet.Cells[row,3].Value?.ToString();varpopulation=int.Parse(worksheet.Cells[row,4].Value?.ToString()??"0");countries[i]=newCountry(name,code,region,population);}}returncountries;}}Step 3: Add thePopulationFormatter Class
To display population data in a readable format, we'll use a helper class:
publicstaticclassPopulationFormatter{publicstaticstringFormatPopulation(intpopulation){returnpopulation.ToString("N0");// Adds commas for thousands}}Step 4: Update theMain Method
Here’s theMain method to tie everything together:
usingSystem;classProgram{staticvoidMain(string[]args){stringfilePath="Countries.xlsx";varexcelHandler=newExcelReaderWriter(filePath);// Step 1: Create Excel with Sample DataConsole.WriteLine("Creating sample data...");excelHandler.CreateSampleData();Console.WriteLine($"Sample data created at{filePath}");// Step 2: Read the Top 10 Countries from ExcelConsole.WriteLine("Reading data from Excel...");varcountries=excelHandler.ReadFirstNCountries(10);// Step 3: Display the CountriesConsole.WriteLine("\nTop 10 Most Populated Countries:");foreach(varcountryincountries){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.xlsxReading data from Excel...Top 10 Most Populated Countries:China 1,412,600,000India 1,366,417,754United States 331,883,986Indonesia 273,523,621Pakistan 220,892,331Brazil 212,559,409Nigeria 206,139,587Bangladesh 164,689,383Russia 144,104,080Mexico 128,932,753Conclusion
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)
Subscribe
For further actions, you may consider blocking this person and/orreporting abuse
