Wie man Excel-Dateien in C# ohne Interop liest: Vollständiges Entwicklerhandbuch

Wie richte ich IronXL ein, um Excel-Dateien in C# zu lesen?

Das Einrichten von IronXL zum Lesen von Excel-Dateien in Ihrem C#-Projekt dauert nur Minuten. Die Bibliothek unterstützt sowohl .XLS- als auch .XLSX-Formate und macht sie vielseitig für jede Aufgabe im Zusammenhang mit Excel.

Folgen Sie diesen Schritten, um zu beginnen:

1. Laden Sie die C#-Bibliothek zum Lesen von Excel-Dateien herunter
2. Laden und lesen Sie Excel-Arbeitsmappen mit WorkBook.Load()
3. Greifen Sie auf Arbeitsblätter mit der Methode GetWorkSheet() zu
4. Lesen Sie Zellwerte mit einer intuitiven Syntax wie sheet["A1"].Value
5. Validieren und verarbeiten Sie tabellarische Daten programmatisch
6. Exportieren Sie Daten mit dem Entity Framework in Datenbanken

IronXL glänzt bei der Lektüre und Bearbeitung von Microsoft Excel-Dokumenten mit C#. Die Bibliothek arbeitet unabhängig – sie erfordert weder Microsoft Excel noch Interop, um zu funktionieren. Tatsächlich bietet IronXL ein schnelleres und intuitiveres API als Microsoft.Office.Interop.Excel.

IronXL umfasst:

• Engagierten Produktsupport von unseren .NET-Ingenieuren
• Einfache Installation über Microsoft Visual Studio
• Kostenloser Testversuch für die Entwicklung. Lizenzen von liteLicense

Das Lesen und Erstellen von Excel-Dateien in C# und VB.NET wird mit der IronXL Softwarebibliothek einfach.

:path=/static-assets/excel/content-code-examples/tutorials/how-to-read-excel-file-csharp-1.cs

using IronXL;
using System;
using System.Linq;

// Load Excel workbook from file path
WorkBook workBook = WorkBook.Load("test.xlsx");

// Access the first worksheet using LINQ
WorkSheet workSheet = workBook.WorkSheets.First();

// Read integer value from cell A2
int cellValue = workSheet["A2"].IntValue;
Console.WriteLine($"Cell A2 value: {cellValue}");

// Iterate through a range of cells
foreach (var cell in workSheet["A2:A10"])
{
    Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text);
}

// Advanced Operations with LINQ
// Calculate sum using built_in Sum() method
decimal sum = workSheet["A2:A10"].Sum();

// Find maximum value using LINQ
decimal max = workSheet["A2:A10"].Max(c => c.DecimalValue);

// Output calculated results
Console.WriteLine($"Sum of A2:A10: {sum}");
Console.WriteLine($"Maximum value: {max}");

Wie installiere ich die IronXL C# Bibliothek?

Die Installation der IronXL.Excel-Bibliothek fügt umfassende Excel-Funktionalitäten zu Ihren .NET-Framework-Projekten hinzu. Wählen Sie zwischen der NuGet-Installation oder manueller DLL-Integration.

Install-Package IronXL.Excel

Wie lade ich eine Excel-Arbeitsmappe und lese sie?

Die WorkBook-Klasse stellt eine gesamte Excel-Datei dar. Laden Sie Excel-Dateien mit der Methode WorkBook.Load(), die Dateipfade für XLS-, XLSX-, CSV- und TSV-Formate akzeptiert.

:path=/static-assets/excel/content-code-examples/tutorials/how-to-read-excel-file-csharp-2.cs

using IronXL;
using System;
using System.Linq;

// Load Excel file from specified path
WorkBook workBook = WorkBook.Load(@"Spreadsheets\GDP.xlsx");

Console.WriteLine("Workbook loaded successfully.");

// Access specific worksheet by name
WorkSheet sheet = workBook.GetWorkSheet("Sheet1");

// Read and display cell value
string cellValue = sheet["A1"].StringValue;
Console.WriteLine($"Cell A1 contains: {cellValue}");

// Perform additional operations
// Count non_empty cells in column A
int rowCount = sheet["A:A"].Count(cell => !cell.IsEmpty);
Console.WriteLine($"Column A has {rowCount} non_empty cells");

Jedes WorkBook enthält mehrere WorkSheet-Objekte, die einzelne Excel-Blätter darstellen. Greifen Sie über den Namen auf Arbeitsblätter zu, indem Sie GetWorkSheet() verwenden:

:path=/static-assets/excel/content-code-examples/tutorials/how-to-read-excel-file-csharp-3.cs

using IronXL;
using System;

// Get worksheet by name
WorkSheet workSheet = workBook.GetWorkSheet("GDPByCountry");

Console.WriteLine("Worksheet 'GDPByCountry' not found");
// List available worksheets
foreach (var sheet in workBook.WorkSheets)
{
    Console.WriteLine($"Available: {sheet.Name}");
}

Wie kann ich neue Excel-Dokumente in C# erstellen?

Erstellen Sie neue Excel-Dokumente, indem Sie ein WorkBook-Objekt mit dem gewünschten Dateiformat konstruieren. IronXL unterstützt sowohl moderne XLSX- als auch ältere XLS-Formate.

:path=/static-assets/excel/content-code-examples/tutorials/how-to-read-excel-file-csharp-4.cs

using IronXL;

// Create new XLSX workbook (recommended format)
WorkBook workBook = WorkBook.Create(ExcelFileFormat.XLSX);

// Set workbook metadata
workBook.Metadata.Author = "Your Application";
workBook.Metadata.Comments = "Generated by IronXL";

// Create new XLS workbook for legacy support
WorkBook legacyWorkBook = WorkBook.Create(ExcelFileFormat.XLS);

// Save the workbook
workBook.SaveAs("NewDocument.xlsx");

Hinweis: Verwenden Sie ExcelFileFormat.XLS nur, wenn die Kompatibilität mit Excel 2003 und früher erforderlich ist.

Wie kann ich Arbeitsblätter zu einem Excel-Dokument hinzufügen?

Ein IronXL WorkBook enthält eine Sammlung von Arbeitsblättern. Dieses Verständnis hilft beim Erstellen von Excel-Dateien mit mehreren Blättern.

Visuelle Darstellung der WorkBook-Struktur mit mehreren WorkSheet-Objekten in IronXL.

Erstellen Sie neue Arbeitsblätter mit CreateWorkSheet():

:path=/static-assets/excel/content-code-examples/tutorials/how-to-read-excel-file-csharp-5.cs

using IronXL;

// Create multiple worksheets with descriptive names
WorkSheet summarySheet = workBook.CreateWorkSheet("Summary");
WorkSheet dataSheet = workBook.CreateWorkSheet("RawData");
WorkSheet chartSheet = workBook.CreateWorkSheet("Charts");

// Set the active worksheet
workBook.SetActiveTab(0); // Makes "Summary" the active sheet

// Access default worksheet (first sheet)
WorkSheet defaultSheet = workBook.DefaultWorkSheet;

Wie lese und bearbeite ich Zellwerte?

:path=/static-assets/excel/content-code-examples/tutorials/how-to-read-excel-file-csharp-6.cs

using IronXL;
using System;
using System.Linq;

// Load workbook and get worksheet
WorkBook workBook = WorkBook.Load("test.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Access cell B1
IronXL.Cell cell = workSheet["B1"].First();

// Read cell value with type safety
string textValue = cell.StringValue;
int intValue = cell.IntValue;
decimal decimalValue = cell.DecimalValue;
DateTime? dateValue = cell.DateTimeValue;

// Check cell data type
if (cell.IsNumeric)
{
    Console.WriteLine($"Numeric value: {cell.DecimalValue}");
}
else if (cell.IsText)
{
    Console.WriteLine($"Text value: {cell.StringValue}");
}

:path=/static-assets/excel/content-code-examples/tutorials/how-to-read-excel-file-csharp-7.cs

// Write different data types to cells
workSheet["A1"].Value = "Product Name";     // String
workSheet["B1"].Value = 99.95m;             // Decimal
workSheet["C1"].Value = DateTime.Today;     // Date
workSheet["D1"].Formula = "=B1*1.2";        // Formula
                                            // Format cells
workSheet["B1"].FormatString = "$#,##0.00"; // Currency format
workSheet["C1"].FormatString = "yyyy-MM-dd";// Date format
                                            // Save changes
workBook.Save();

Wie kann ich mit Zellbereichen arbeiten?

Die Range-Klasse stellt eine Sammlung von Zellen dar, die Massenoperationen auf Excel-Daten ermöglichen.

:path=/static-assets/excel/content-code-examples/tutorials/how-to-read-excel-file-csharp-8.cs

// Select range using Excel notation
Range range = workSheet["D2:D101"];

// Alternative: Use Range class for dynamic selection
Range dynamicRange = workSheet.GetRange("D2:D101"); // Row 2_101, Column D

// Perform bulk operations
range.Value = 0; // Set all cells to 0

Bearbeiten Sie Bereiche effizient mit Schleifen, wenn die Zellanzahl bekannt ist:

// Data validation example
public class ValidationResult
{
    public int Row { get; set; }
    public string PhoneError { get; set; }
    public string EmailError { get; set; }
    public string DateError { get; set; }
    public bool IsValid => string.IsNullOrEmpty(PhoneError) && 
                           string.IsNullOrEmpty(EmailError) && 
                           string.IsNullOrEmpty(DateError);
}

// Validate data in rows 2-101
var results = new List<ValidationResult>();

for (int row = 2; row <= 101; row++)
{
    var result = new ValidationResult { Row = row };

    // Get row data efficiently
    var phoneCell = workSheet[$"B{row}"];
    var emailCell = workSheet[$"D{row}"];
    var dateCell = workSheet[$"E{row}"];

    // Validate phone number
    if (!IsValidPhoneNumber(phoneCell.StringValue))
        result.PhoneError = "Invalid phone format";

    // Validate email
    if (!IsValidEmail(emailCell.StringValue))
        result.EmailError = "Invalid email format";

    // Validate date
    if (!dateCell.IsDateTime)
        result.DateError = "Invalid date format";

    results.Add(result);
}

// Helper methods
bool IsValidPhoneNumber(string phone) => 
    System.Text.RegularExpressions.Regex.IsMatch(phone, @"^\d{3}-\d{3}-\d{4}$");

bool IsValidEmail(string email) => 
    email.Contains("@") && email.Contains(".");

// Data validation example
public class ValidationResult
{
    public int Row { get; set; }
    public string PhoneError { get; set; }
    public string EmailError { get; set; }
    public string DateError { get; set; }
    public bool IsValid => string.IsNullOrEmpty(PhoneError) && 
                           string.IsNullOrEmpty(EmailError) && 
                           string.IsNullOrEmpty(DateError);
}

// Validate data in rows 2-101
var results = new List<ValidationResult>();

for (int row = 2; row <= 101; row++)
{
    var result = new ValidationResult { Row = row };

    // Get row data efficiently
    var phoneCell = workSheet[$"B{row}"];
    var emailCell = workSheet[$"D{row}"];
    var dateCell = workSheet[$"E{row}"];

    // Validate phone number
    if (!IsValidPhoneNumber(phoneCell.StringValue))
        result.PhoneError = "Invalid phone format";

    // Validate email
    if (!IsValidEmail(emailCell.StringValue))
        result.EmailError = "Invalid email format";

    // Validate date
    if (!dateCell.IsDateTime)
        result.DateError = "Invalid date format";

    results.Add(result);
}

// Helper methods
bool IsValidPhoneNumber(string phone) => 
    System.Text.RegularExpressions.Regex.IsMatch(phone, @"^\d{3}-\d{3}-\d{4}$");

bool IsValidEmail(string email) => 
    email.Contains("@") && email.Contains(".");

Wie füge ich Formeln zu Excel-Tabellen hinzu?

Wenden Sie Excel-Formeln mit der Formula-Eigenschaft an. IronXL unterstützt die Standardsyntax für Excel-Formeln.

:path=/static-assets/excel/content-code-examples/tutorials/how-to-read-excel-file-csharp-9.cs

using IronXL;

// Add formulas to calculate percentages
int lastRow = 50;
for (int row = 2; row < lastRow; row++)
{
    // Calculate percentage: current value / total
    workSheet[$"C{row}"].Formula = $"=B{row}/B{lastRow}";
    // Format as percentage
    workSheet[$"C{row}"].FormatString = "0.00%";
}
// Add summary formulas
workSheet["B52"].Formula = "=SUM(B2:B50)";      // Sum
workSheet["B53"].Formula = "=AVERAGE(B2:B50)";   // Average
workSheet["B54"].Formula = "=MAX(B2:B50)";       // Maximum
workSheet["B55"].Formula = "=MIN(B2:B50)";       // Minimum
                                                 // Force formula evaluation
workBook.EvaluateAll();

Um vorhandene Formeln zu bearbeiten, erkunden Sie das Excel-Formeln-Tutorial.

Wie kann ich tabellarische Daten validieren?

Mit IronXL können umfassende Datenvalidierungen für Tabellen durchgeführt werden. Dieses Beispiel validiert Telefonnummern, E-Mails und Daten mithilfe externer Bibliotheken und integrierter C#-Funktionalität.

using System.Text.RegularExpressions;
using IronXL;

// Validation implementation
for (int i = 2; i <= 101; i++)
{
    var result = new PersonValidationResult { Row = i };
    results.Add(result);

    // Get cells for current person
    var cells = workSheet[$"A{i}:E{i}"].ToList();

    // Validate phone (column B)
    string phone = cells[1].StringValue;
    if (!Regex.IsMatch(phone, @"^\+?1?\d{10,14}$"))
    {
        result.PhoneNumberErrorMessage = "Invalid phone format";
    }

    // Validate email (column D)
    string email = cells[3].StringValue;
    if (!Regex.IsMatch(email, @"^[^@\s]+@[^@\s]+\.[^@\s]+$"))
    {
        result.EmailErrorMessage = "Invalid email address";
    }

    // Validate date (column E)
    if (!cells[4].IsDateTime)
    {
        result.DateErrorMessage = "Invalid date format";
    }
}

using System.Text.RegularExpressions;
using IronXL;

// Validation implementation
for (int i = 2; i <= 101; i++)
{
    var result = new PersonValidationResult { Row = i };
    results.Add(result);

    // Get cells for current person
    var cells = workSheet[$"A{i}:E{i}"].ToList();

    // Validate phone (column B)
    string phone = cells[1].StringValue;
    if (!Regex.IsMatch(phone, @"^\+?1?\d{10,14}$"))
    {
        result.PhoneNumberErrorMessage = "Invalid phone format";
    }

    // Validate email (column D)
    string email = cells[3].StringValue;
    if (!Regex.IsMatch(email, @"^[^@\s]+@[^@\s]+\.[^@\s]+$"))
    {
        result.EmailErrorMessage = "Invalid email address";
    }

    // Validate date (column E)
    if (!cells[4].IsDateTime)
    {
        result.DateErrorMessage = "Invalid date format";
    }
}

Speichern Sie die Validierungsergebnisse auf einem neuen Arbeitsblatt:

// Create results worksheet
var resultsSheet = workBook.CreateWorkSheet("ValidationResults");

// Add headers
resultsSheet["A1"].Value = "Row";
resultsSheet["B1"].Value = "Valid";
resultsSheet["C1"].Value = "Phone Error";
resultsSheet["D1"].Value = "Email Error";
resultsSheet["E1"].Value = "Date Error";

// Style headers
resultsSheet["A1:E1"].Style.Font.Bold = true;
resultsSheet["A1:E1"].Style.SetBackgroundColor("#4472C4");
resultsSheet["A1:E1"].Style.Font.Color = "#FFFFFF";

// Output validation results
for (int i = 0; i < results.Count; i++)
{
    var result = results[i];
    int outputRow = i + 2;

    resultsSheet[$"A{outputRow}"].Value = result.Row;
    resultsSheet[$"B{outputRow}"].Value = result.IsValid ? "Yes" : "No";
    resultsSheet[$"C{outputRow}"].Value = result.PhoneNumberErrorMessage ?? "";
    resultsSheet[$"D{outputRow}"].Value = result.EmailErrorMessage ?? "";
    resultsSheet[$"E{outputRow}"].Value = result.DateErrorMessage ?? "";

    // Highlight invalid rows
    if (!result.IsValid)
    {
        resultsSheet[$"A{outputRow}:E{outputRow}"].Style.SetBackgroundColor("#FFE6E6");
    }
}

// Auto-fit columns
for (int col = 0; col < 5; col++)
{
    resultsSheet.AutoSizeColumn(col);
}

// Save validated workbook
workBook.SaveAs(@"Spreadsheets\PeopleValidated.xlsx");

// Create results worksheet
var resultsSheet = workBook.CreateWorkSheet("ValidationResults");

// Add headers
resultsSheet["A1"].Value = "Row";
resultsSheet["B1"].Value = "Valid";
resultsSheet["C1"].Value = "Phone Error";
resultsSheet["D1"].Value = "Email Error";
resultsSheet["E1"].Value = "Date Error";

// Style headers
resultsSheet["A1:E1"].Style.Font.Bold = true;
resultsSheet["A1:E1"].Style.SetBackgroundColor("#4472C4");
resultsSheet["A1:E1"].Style.Font.Color = "#FFFFFF";

// Output validation results
for (int i = 0; i < results.Count; i++)
{
    var result = results[i];
    int outputRow = i + 2;

    resultsSheet[$"A{outputRow}"].Value = result.Row;
    resultsSheet[$"B{outputRow}"].Value = result.IsValid ? "Yes" : "No";
    resultsSheet[$"C{outputRow}"].Value = result.PhoneNumberErrorMessage ?? "";
    resultsSheet[$"D{outputRow}"].Value = result.EmailErrorMessage ?? "";
    resultsSheet[$"E{outputRow}"].Value = result.DateErrorMessage ?? "";

    // Highlight invalid rows
    if (!result.IsValid)
    {
        resultsSheet[$"A{outputRow}:E{outputRow}"].Style.SetBackgroundColor("#FFE6E6");
    }
}

// Auto-fit columns
for (int col = 0; col < 5; col++)
{
    resultsSheet.AutoSizeColumn(col);
}

// Save validated workbook
workBook.SaveAs(@"Spreadsheets\PeopleValidated.xlsx");

Wie exportiere ich Excel-Daten in eine Datenbank?

Verwenden Sie IronXL mit dem Entity Framework, um tabellarische Daten direkt in Datenbanken zu exportieren. Dieses Beispiel zeigt den Export von BIP-Länderdaten nach SQLite.

using System;
using System.ComponentModel.DataAnnotations;
using Microsoft.EntityFrameworkCore;
using IronXL;

// Define entity model
public class Country
{
    [Key]
    public Guid Id { get; set; } = Guid.NewGuid();

    [Required]
    [MaxLength(100)]
    public string Name { get; set; }

    [Range(0, double.MaxValue)]
    public decimal GDP { get; set; }

    public DateTime ImportedDate { get; set; } = DateTime.UtcNow;
}

using System;
using System.ComponentModel.DataAnnotations;
using Microsoft.EntityFrameworkCore;
using IronXL;

// Define entity model
public class Country
{
    [Key]
    public Guid Id { get; set; } = Guid.NewGuid();

    [Required]
    [MaxLength(100)]
    public string Name { get; set; }

    [Range(0, double.MaxValue)]
    public decimal GDP { get; set; }

    public DateTime ImportedDate { get; set; } = DateTime.UtcNow;
}

Konfigurieren Sie den Entity Framework-Kontext für Datenbankoperationen:

public class CountryContext : DbContext
{
    public DbSet<Country> Countries { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // Configure SQLite connection
        optionsBuilder.UseSqlite("Data Source=CountryGDP.db");

        // Enable sensitive data logging in development
        #if DEBUG
        optionsBuilder.EnableSensitiveDataLogging();
        #endif
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Configure decimal precision
        modelBuilder.Entity<Country>()
            .Property(c => c.GDP)
            .HasPrecision(18, 2);
    }
}

public class CountryContext : DbContext
{
    public DbSet<Country> Countries { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // Configure SQLite connection
        optionsBuilder.UseSqlite("Data Source=CountryGDP.db");

        // Enable sensitive data logging in development
        #if DEBUG
        optionsBuilder.EnableSensitiveDataLogging();
        #endif
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Configure decimal precision
        modelBuilder.Entity<Country>()
            .Property(c => c.GDP)
            .HasPrecision(18, 2);
    }
}

Importieren Sie Excel-Daten in die Datenbank:

using System.Threading.Tasks;
using IronXL;
using Microsoft.EntityFrameworkCore;

public async Task ImportGDPDataAsync()
{
    try
    {
        // Load Excel file
        var workBook = WorkBook.Load(@"Spreadsheets\GDP.xlsx");
        var workSheet = workBook.GetWorkSheet("GDPByCountry");

        using (var context = new CountryContext())
        {
            // Ensure database exists
            await context.Database.EnsureCreatedAsync();

            // Clear existing data (optional)
            await context.Database.ExecuteSqlRawAsync("DELETE FROM Countries");

            // Import data with progress tracking
            int totalRows = 213;
            for (int row = 2; row <= totalRows; row++)
            {
                // Read country data
                var countryName = workSheet[$"A{row}"].StringValue;
                var gdpValue = workSheet[$"B{row}"].DecimalValue;

                // Skip empty rows
                if (string.IsNullOrWhiteSpace(countryName))
                    continue;

                // Create and add entity
                var country = new Country
                {
                    Name = countryName.Trim(),
                    GDP = gdpValue * 1_000_000 // Convert to actual value if in millions
                };

                await context.Countries.AddAsync(country);

                // Save in batches for performance
                if (row % 50 == 0)
                {
                    await context.SaveChangesAsync();
                    Console.WriteLine($"Imported {row - 1} of {totalRows} countries");
                }
            }

            // Save remaining records
            await context.SaveChangesAsync();
            Console.WriteLine($"Successfully imported {await context.Countries.CountAsync()} countries");
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine($"Import failed: {ex.Message}");
        throw;
    }
}

using System.Threading.Tasks;
using IronXL;
using Microsoft.EntityFrameworkCore;

public async Task ImportGDPDataAsync()
{
    try
    {
        // Load Excel file
        var workBook = WorkBook.Load(@"Spreadsheets\GDP.xlsx");
        var workSheet = workBook.GetWorkSheet("GDPByCountry");

        using (var context = new CountryContext())
        {
            // Ensure database exists
            await context.Database.EnsureCreatedAsync();

            // Clear existing data (optional)
            await context.Database.ExecuteSqlRawAsync("DELETE FROM Countries");

            // Import data with progress tracking
            int totalRows = 213;
            for (int row = 2; row <= totalRows; row++)
            {
                // Read country data
                var countryName = workSheet[$"A{row}"].StringValue;
                var gdpValue = workSheet[$"B{row}"].DecimalValue;

                // Skip empty rows
                if (string.IsNullOrWhiteSpace(countryName))
                    continue;

                // Create and add entity
                var country = new Country
                {
                    Name = countryName.Trim(),
                    GDP = gdpValue * 1_000_000 // Convert to actual value if in millions
                };

                await context.Countries.AddAsync(country);

                // Save in batches for performance
                if (row % 50 == 0)
                {
                    await context.SaveChangesAsync();
                    Console.WriteLine($"Imported {row - 1} of {totalRows} countries");
                }
            }

            // Save remaining records
            await context.SaveChangesAsync();
            Console.WriteLine($"Successfully imported {await context.Countries.CountAsync()} countries");
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine($"Import failed: {ex.Message}");
        throw;
    }
}

Wie kann ich API-Daten in Excel-Tabellen importieren?

Kombinieren Sie IronXL mit HTTP-Clients, um Tabellen mit Echtzeit-API-Daten zu füllen. Dieses Beispiel verwendet RestClient.Net, um Länderdaten abzurufen.

using System;
using System.Collections.Generic;
using System.Net.Http;
using System.Threading.Tasks;
using Newtonsoft.Json;
using IronXL;

// Define data model matching API response
public class RestCountry
{
    public string Name { get; set; }
    public long Population { get; set; }
    public string Region { get; set; }
    public string NumericCode { get; set; }
    public List<Language> Languages { get; set; }
}

public class Language
{
    public string Name { get; set; }
    public string NativeName { get; set; }
}

// Fetch and process API data
public async Task ImportCountryDataAsync()
{
    using var httpClient = new HttpClient();

    try
    {
        // Call REST API
        var response = await httpClient.GetStringAsync("https://restcountries.com/v3.1/all");
        var countries = JsonConvert.DeserializeObject<List<RestCountry>>(response);

        // Create new workbook
        var workBook = WorkBook.Create(ExcelFileFormat.XLSX);
        var workSheet = workBook.CreateWorkSheet("Countries");

        // Add headers with styling
        string[] headers = { "Country", "Population", "Region", "Code", "Language 1", "Language 2", "Language 3" };
        for (int col = 0; col < headers.Length; col++)
        {
            var headerCell = workSheet[0, col];
            headerCell.Value = headers[col];
            headerCell.Style.Font.Bold = true;
            headerCell.Style.SetBackgroundColor("#366092");
            headerCell.Style.Font.Color = "#FFFFFF";
        }

        // Import country data
        await ProcessCountryData(countries, workSheet);

        // Save workbook
        workBook.SaveAs("CountriesFromAPI.xlsx");
    }
    catch (Exception ex)
    {
        Console.WriteLine($"API import failed: {ex.Message}");
    }
}

using System;
using System.Collections.Generic;
using System.Net.Http;
using System.Threading.Tasks;
using Newtonsoft.Json;
using IronXL;

// Define data model matching API response
public class RestCountry
{
    public string Name { get; set; }
    public long Population { get; set; }
    public string Region { get; set; }
    public string NumericCode { get; set; }
    public List<Language> Languages { get; set; }
}

public class Language
{
    public string Name { get; set; }
    public string NativeName { get; set; }
}

// Fetch and process API data
public async Task ImportCountryDataAsync()
{
    using var httpClient = new HttpClient();

    try
    {
        // Call REST API
        var response = await httpClient.GetStringAsync("https://restcountries.com/v3.1/all");
        var countries = JsonConvert.DeserializeObject<List<RestCountry>>(response);

        // Create new workbook
        var workBook = WorkBook.Create(ExcelFileFormat.XLSX);
        var workSheet = workBook.CreateWorkSheet("Countries");

        // Add headers with styling
        string[] headers = { "Country", "Population", "Region", "Code", "Language 1", "Language 2", "Language 3" };
        for (int col = 0; col < headers.Length; col++)
        {
            var headerCell = workSheet[0, col];
            headerCell.Value = headers[col];
            headerCell.Style.Font.Bold = true;
            headerCell.Style.SetBackgroundColor("#366092");
            headerCell.Style.Font.Color = "#FFFFFF";
        }

        // Import country data
        await ProcessCountryData(countries, workSheet);

        // Save workbook
        workBook.SaveAs("CountriesFromAPI.xlsx");
    }
    catch (Exception ex)
    {
        Console.WriteLine($"API import failed: {ex.Message}");
    }
}

Die API gibt JSON-Daten in diesem Format zurück:

Beispiel einer JSON-Antwort von der REST Countries API, die hierarchische Länderinformationen zeigt.

Verarbeiten und schreiben Sie die API-Daten nach Excel:

private async Task ProcessCountryData(List<RestCountry> countries, WorkSheet workSheet)
{
    for (int i = 0; i < countries.Count; i++)
    {
        var country = countries[i];
        int row = i + 1; // Start from row 1 (after headers)

        // Write basic country data
        workSheet[$"A{row}"].Value = country.Name;
        workSheet[$"B{row}"].Value = country.Population;
        workSheet[$"C{row}"].Value = country.Region;
        workSheet[$"D{row}"].Value = country.NumericCode;

        // Format population with thousands separator
        workSheet[$"B{row}"].FormatString = "#,##0";

        // Add up to 3 languages
        for (int langIndex = 0; langIndex < Math.Min(3, country.Languages?.Count ?? 0); langIndex++)
        {
            var language = country.Languages[langIndex];
            string columnLetter = ((char)('E' + langIndex)).ToString();
            workSheet[$"{columnLetter}{row}"].Value = language.Name;
        }

        // Add conditional formatting for regions
        if (country.Region == "Europe")
        {
            workSheet[$"C{row}"].Style.SetBackgroundColor("#E6F3FF");
        }
        else if (country.Region == "Asia")
        {
            workSheet[$"C{row}"].Style.SetBackgroundColor("#FFF2E6");
        }

        // Show progress every 50 countries
        if (i % 50 == 0)
        {
            Console.WriteLine($"Processed {i} of {countries.Count} countries");
        }
    }

    // Auto-size all columns
    for (int col = 0; col < 7; col++)
    {
        workSheet.AutoSizeColumn(col);
    }
}

private async Task ProcessCountryData(List<RestCountry> countries, WorkSheet workSheet)
{
    for (int i = 0; i < countries.Count; i++)
    {
        var country = countries[i];
        int row = i + 1; // Start from row 1 (after headers)

        // Write basic country data
        workSheet[$"A{row}"].Value = country.Name;
        workSheet[$"B{row}"].Value = country.Population;
        workSheet[$"C{row}"].Value = country.Region;
        workSheet[$"D{row}"].Value = country.NumericCode;

        // Format population with thousands separator
        workSheet[$"B{row}"].FormatString = "#,##0";

        // Add up to 3 languages
        for (int langIndex = 0; langIndex < Math.Min(3, country.Languages?.Count ?? 0); langIndex++)
        {
            var language = country.Languages[langIndex];
            string columnLetter = ((char)('E' + langIndex)).ToString();
            workSheet[$"{columnLetter}{row}"].Value = language.Name;
        }

        // Add conditional formatting for regions
        if (country.Region == "Europe")
        {
            workSheet[$"C{row}"].Style.SetBackgroundColor("#E6F3FF");
        }
        else if (country.Region == "Asia")
        {
            workSheet[$"C{row}"].Style.SetBackgroundColor("#FFF2E6");
        }

        // Show progress every 50 countries
        if (i % 50 == 0)
        {
            Console.WriteLine($"Processed {i} of {countries.Count} countries");
        }
    }

    // Auto-size all columns
    for (int col = 0; col < 7; col++)
    {
        workSheet.AutoSizeColumn(col);
    }
}

Objektreferenz und Ressourcen

Erforschen Sie die umfassende IronXL API-Referenz für detaillierte Klassendokumentation und erweiterte Funktionen.

Zusätzliche Tutorials für Excel-Operationen:

• Erstellen Sie Excel-Dateien programmgesteuert
• Leitfaden für Excel-Formatierung und -Styling
• Arbeiten mit Excel-Formeln
• Excel-Diagramm-Erstellungstutorial

Zusammenfassung

IronXL.Excel ist eine umfassende .NET-Bibliothek zum Lesen und Bearbeiten von Excel-Dateien in verschiedenen Formaten. Es arbeitet unabhängig, ohne dass eine Microsoft Excel- oder Interop-Installation erforderlich ist.

Für cloudbasierte Tabellenmanipulationen können Sie auch die Google Sheets API Client Library für .NET erkunden, die IronXL's lokalen Dateifunktionen ergänzt.

Bereit, um eine Excel-Automatisierung in Ihren C#-Projekten zu implementieren? Laden Sie IronXL herunter oder erkunden Sie Lizenzierungsoptionen für die Produktion.