DEV Community

mohamed Tayel
mohamed Tayel

Posted on

Import from excel to Array using C#

"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

  1. How to create and write data to an Excel file programmatically using EPPlus.
  2. How to read and parse data from an Excel sheet.
  3. How to initialize and populate arrays dynamically.
  4. How to format output for better readability.

Prerequisites

  1. Install the EPPlus Library Add the EPPlus NuGet package to your project: 
 Install-Package EPPlus
Enter fullscreen mode Exit fullscreen mode
  1. 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; } }
Enter fullscreen mode Exit fullscreen mode

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; } }
Enter fullscreen mode Exit fullscreen mode

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 } }
Enter fullscreen mode Exit fullscreen mode

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)}"); } } } }
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)