如何在 C# 中匯出模板

Why Use Excel Templates for Data Export?

Excel templates offer significant advantages over creating spreadsheets from scratch. Templates maintain professional formatting, complex formulas, conditional formatting rules, and validated data structures. Organizations often have standardized templates for invoices, reports, and dashboards that must retain their design while incorporating dynamic data from databases, APIs, or collection objects such as a data table. When applying conditional formatting and cell formatting to your output file, templates ensure consistency across all generated documents in xlsx format.

By populating existing templates programmatically, developers save countless hours of formatting work and ensure consistency across all generated documents. IronXL makes this process seamless, supporting various Excel formats, including XLSX, XLS file, XLSM, and XLTX templates without requiring Office installation. The source code for these operations is straightforward and easy to implement in any project folder.

Setting Up IronXL for Template Operations

Start by installing IronXL through NuGet Package Manager. Open your Package Manager Console and run the following command:

Install-Package IronXL.Excel

After installation, add the necessary namespace to your C# file:

using IronXL;

using IronXL;

Imports IronXL

IronXL operates independently without requiring Microsoft Office installation, making it ideal for server environments and cross-platform applications, including Docker containers and cloud platforms. For detailed setup instructions and additional information, visit the IronXL getting started guide. The library supports .NET Framework, .NET Core, and .NET 5+ across Windows, Linux, and macOS environments, making it perfect for .NET applications.

Loading and Populating Excel Templates

Loading an existing template is straightforward with IronXL's WorkBook.Load() method. The following example shows how to open a template and populate it with data, handling the first row as headers and managing column names effectively:

// Load the existing Excel template for data import
WorkBook workbook = WorkBook.Load("ReportTemplate.xlsx");
WorkSheet sheet = workbook.DefaultWorkSheet;
// Populate specific worksheet cells with data
sheet["B2"].Value = "Q4 2024 Sales Report";
sheet["C4"].StringValue = DateTime.Now.ToString("MMMM dd, yyyy");
sheet["C6"].DecimalValue = 125000.50m;
sheet["C7"].DecimalValue = 98500.75m;
sheet["C8"].Formula = "=C6-C7"; // Profit calculation
// Populate a range with array data
decimal[] monthlyData = { 10500, 12300, 15600, 11200 };
for (int i = 0; i < monthlyData.Length; i++)
{
    sheet[$"E{10 + i}"].DecimalValue = monthlyData[i];
}
// Save the populated template
workbook.SaveAs("Q4_Sales_Report.xlsx");

// Load the existing Excel template for data import
WorkBook workbook = WorkBook.Load("ReportTemplate.xlsx");
WorkSheet sheet = workbook.DefaultWorkSheet;
// Populate specific worksheet cells with data
sheet["B2"].Value = "Q4 2024 Sales Report";
sheet["C4"].StringValue = DateTime.Now.ToString("MMMM dd, yyyy");
sheet["C6"].DecimalValue = 125000.50m;
sheet["C7"].DecimalValue = 98500.75m;
sheet["C8"].Formula = "=C6-C7"; // Profit calculation
// Populate a range with array data
decimal[] monthlyData = { 10500, 12300, 15600, 11200 };
for (int i = 0; i < monthlyData.Length; i++)
{
    sheet[$"E{10 + i}"].DecimalValue = monthlyData[i];
}
// Save the populated template
workbook.SaveAs("Q4_Sales_Report.xlsx");

IRON VB CONVERTER ERROR developers@ironsoftware.com

This code loads a pre-designed template, maintains all existing formatting, and populates specific cells with new data. The DecimalValue property ensures numerical data retains proper formatting. Formula cells automatically recalculate when adjacent data changes, preserving the template's computational logic. Learn more about working with Excel formulas in IronXL.

Working with Template Placeholders

Many templates use placeholder text markers that need replacement with actual data. IronXL handles this scenario efficiently through cell iteration and text replacement. When you need to write data to excel templates and insert dynamic content, this approach provides maximum flexibility:

// Load template with placeholders
WorkBook workbook = WorkBook.Load("InvoiceTemplate.xlsx");
WorkSheet sheet = workbook.DefaultWorkSheet;
// Find and replace placeholder text in cells
foreach (var cell in sheet["A1:H50"])
{
    if (cell.Text.Contains("{{CustomerName}}"))
        cell.Value = cell.Text.Replace("{{CustomerName}}", "Acme Corporation");
    if (cell.Text.Contains("{{InvoiceDate}}"))
        cell.Value = cell.Text.Replace("{{InvoiceDate}}", DateTime.Now.ToShortDateString());
    if (cell.Text.Contains("{{InvoiceNumber}}"))
        cell.Value = cell.Text.Replace("{{InvoiceNumber}}", "INV-2024-001");
}
// Populate line items dynamically
var items = new[] {
    new { Description = "Software License", Qty = 5, Price = 299.99 },
    new { Description = "Support Package", Qty = 1, Price = 999.99 }
};
int startRow = 15;
foreach (var item in items)
{
    sheet[$"B{startRow}"].Value = item.Description;
    sheet[$"E{startRow}"].IntValue = item.Qty;
    sheet[$"F{startRow}"].DoubleValue = item.Price;
    sheet[$"G{startRow}"].Formula = $"=E{startRow}*F{startRow}";
    startRow++;
}
workbook.SaveAs("GeneratedInvoice.xlsx");

// Load template with placeholders
WorkBook workbook = WorkBook.Load("InvoiceTemplate.xlsx");
WorkSheet sheet = workbook.DefaultWorkSheet;
// Find and replace placeholder text in cells
foreach (var cell in sheet["A1:H50"])
{
    if (cell.Text.Contains("{{CustomerName}}"))
        cell.Value = cell.Text.Replace("{{CustomerName}}", "Acme Corporation");
    if (cell.Text.Contains("{{InvoiceDate}}"))
        cell.Value = cell.Text.Replace("{{InvoiceDate}}", DateTime.Now.ToShortDateString());
    if (cell.Text.Contains("{{InvoiceNumber}}"))
        cell.Value = cell.Text.Replace("{{InvoiceNumber}}", "INV-2024-001");
}
// Populate line items dynamically
var items = new[] {
    new { Description = "Software License", Qty = 5, Price = 299.99 },
    new { Description = "Support Package", Qty = 1, Price = 999.99 }
};
int startRow = 15;
foreach (var item in items)
{
    sheet[$"B{startRow}"].Value = item.Description;
    sheet[$"E{startRow}"].IntValue = item.Qty;
    sheet[$"F{startRow}"].DoubleValue = item.Price;
    sheet[$"G{startRow}"].Formula = $"=E{startRow}*F{startRow}";
    startRow++;
}
workbook.SaveAs("GeneratedInvoice.xlsx");

IRON VB CONVERTER ERROR developers@ironsoftware.com

This approach searches for placeholder markers within a specified range and replaces them with actual values. The template's formatting, including fonts, colors, and borders, remains intact throughout the process. For more advanced scenarios, explore IronXL's cell styling options to dynamically modify formatting when needed.

Real-World Implementation Example

Here's a complete example generating a monthly sales report from an existing Excel template with pre-formatted cells. This code demonstrates how to handle object sender events and write comprehensive reports. When working with data from a system database or in-memory collections, you can efficiently export data to excel using a new datatable or existing dataset to populate templates:

public void GenerateMonthlyReport(string templatePath, Dictionary<string, decimal> salesData)
{
    // Load the existing template file
    WorkBook workbook = WorkBook.Load(templatePath);
    WorkSheet sheet = workbook.GetWorkSheet("Monthly Report");
    // Set report header information
    sheet["B2"].Value = $"Sales Report - {DateTime.Now:MMMM yyyy}";
    sheet["B3"].Value = $"Generated: {DateTime.Now:g}";
    // Populate sales data starting from row 6
    int currentRow = 6;
    decimal totalSales = 0;
    foreach (var sale in salesData)
    {
        sheet[$"B{currentRow}"].Value = sale.Key;  // Product name
        sheet[$"C{currentRow}"].DecimalValue = sale.Value;  // Sales amount
        sheet[$"D{currentRow}"].Formula = $"=C{currentRow}/C${salesData.Count + 6}*100"; // Percentage formula
        totalSales += sale.Value;
        currentRow++;
    }
    // Update total row with sum
    sheet[$"C{currentRow}"].DecimalValue = totalSales;
    sheet[$"C{currentRow}"].Style.Font.Bold = true;
    // Save with timestamp
    string outputPath = $"Reports/Monthly_Report_{DateTime.Now:yyyyMMdd}.xlsx";
    workbook.SaveAs(outputPath);
}

public void GenerateMonthlyReport(string templatePath, Dictionary<string, decimal> salesData)
{
    // Load the existing template file
    WorkBook workbook = WorkBook.Load(templatePath);
    WorkSheet sheet = workbook.GetWorkSheet("Monthly Report");
    // Set report header information
    sheet["B2"].Value = $"Sales Report - {DateTime.Now:MMMM yyyy}";
    sheet["B3"].Value = $"Generated: {DateTime.Now:g}";
    // Populate sales data starting from row 6
    int currentRow = 6;
    decimal totalSales = 0;
    foreach (var sale in salesData)
    {
        sheet[$"B{currentRow}"].Value = sale.Key;  // Product name
        sheet[$"C{currentRow}"].DecimalValue = sale.Value;  // Sales amount
        sheet[$"D{currentRow}"].Formula = $"=C{currentRow}/C${salesData.Count + 6}*100"; // Percentage formula
        totalSales += sale.Value;
        currentRow++;
    }
    // Update total row with sum
    sheet[$"C{currentRow}"].DecimalValue = totalSales;
    sheet[$"C{currentRow}"].Style.Font.Bold = true;
    // Save with timestamp
    string outputPath = $"Reports/Monthly_Report_{DateTime.Now:yyyyMMdd}.xlsx";
    workbook.SaveAs(outputPath);
}

IRON VB CONVERTER ERROR developers@ironsoftware.com

This method accepts sales data and populates a standardized template, automatically calculating percentages and totals while preserving the template's professional appearance. The existing charts and conditional formatting in the template automatically update based on the new data. Note: When transferring data to excel from DataTable objects or dataset collections, preserve column names and handle the first row as headers.

The following example approach works seamlessly whether you need to write data from dictionaries, insert values from database queries, or export data to Excel from various system sources. Simply save the output file to your designated folder for easy access. For additional information on working with DataTables, see the DataTable import documentation and source code examples.

Troubleshooting Common Issues

When working with templates, ensure file paths are correct and templates aren't locked by other processes. For password-protected templates, use WorkBook.Load("template.xlsx", "password"). If formulas aren't updating, call sheet.Calculate() after populating data. For large datasets, consider using workbook.SaveAs() with streaming options to optimize memory usage. Check the troubleshooting documentation for additional information and solutions when working with xlsx format files across different system environments.

結論

IronXL simplifies Excel template population in C#, preserving complex formatting while efficiently injecting dynamic data from various sources including dataset objects and database connections. This approach significantly reduces development time and maintains document consistency across your organization's reporting workflows. Whether you need to write data to excel, insert new rows, or apply cell formatting to your output file, IronXL provides the tools necessary for professional excel automation in .NET applications.

Ready to streamline your Excel reporting? Start your free IronXL trial to test template population in your project, or explore more Excel automation tutorials to enhance your workflow. For production deployment, view licensing options that fit your needs.