如何使用 IronXL 在 C# 中將資料匯出到現有的 Excel 模板

Install-Package IronXL

dotnet add package IronXL

using IronXL;

Imports IronXL

using IronXL;

// Load the existing Excel template
WorkBook workbook = WorkBook.Load("ReportTemplate.xlsx");
WorkSheet sheet = workbook.DefaultWorkSheet;

// Write header values to named cells
sheet["B2"].Value = "Q4 2025 Sales Report";
sheet["C4"].StringValue = DateTime.Now.ToString("MMMM dd, yyyy");
sheet["C6"].DecimalValue = 125000.50m;
sheet["C7"].DecimalValue = 98500.75m;

// Add a profit formula -- Excel recalculates automatically
sheet["C8"].Formula = "=C6-C7";

// Populate a column range with monthly 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 file
workbook.SaveAs("Q4_Sales_Report.xlsx");

Imports IronXL

' Load the existing Excel template
Dim workbook As WorkBook = WorkBook.Load("ReportTemplate.xlsx")
Dim sheet As WorkSheet = workbook.DefaultWorkSheet

' Write header values to named cells
sheet("B2").Value = "Q4 2025 Sales Report"
sheet("C4").StringValue = DateTime.Now.ToString("MMMM dd, yyyy")
sheet("C6").DecimalValue = 125000.50D
sheet("C7").DecimalValue = 98500.75D

' Add a profit formula -- Excel recalculates automatically
sheet("C8").Formula = "=C6-C7"

' Populate a column range with monthly data
Dim monthlyData As Decimal() = {10500D, 12300D, 15600D, 11200D}
For i As Integer = 0 To monthlyData.Length - 1
    sheet($"E{10 + i}").DecimalValue = monthlyData(i)
Next

' Save the populated file
workbook.SaveAs("Q4_Sales_Report.xlsx")

using IronXL;

// Load an invoice template containing placeholder markers
WorkBook workbook = WorkBook.Load("InvoiceTemplate.xlsx");
WorkSheet sheet = workbook.DefaultWorkSheet;

// Iterate over a range and replace placeholder text
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-2025-001");
}

// Append line items starting at row 15
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");

Imports IronXL

' Load an invoice template containing placeholder markers
Dim workbook As WorkBook = WorkBook.Load("InvoiceTemplate.xlsx")
Dim sheet As WorkSheet = workbook.DefaultWorkSheet

' Iterate over a range and replace placeholder text
For Each cell In sheet("A1:H50")
    If cell.Text.Contains("{{CustomerName}}") Then
        cell.Value = cell.Text.Replace("{{CustomerName}}", "Acme Corporation")
    End If

    If cell.Text.Contains("{{InvoiceDate}}") Then
        cell.Value = cell.Text.Replace("{{InvoiceDate}}", DateTime.Now.ToShortDateString())
    End If

    If cell.Text.Contains("{{InvoiceNumber}}") Then
        cell.Value = cell.Text.Replace("{{InvoiceNumber}}", "INV-2025-001")
    End If
Next

' Append line items starting at row 15
Dim items = {
    New With {.Description = "Software License", .Qty = 5, .Price = 299.99},
    New With {.Description = "Support Package", .Qty = 1, .Price = 999.99}
}

Dim startRow As Integer = 15
For Each 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 += 1
Next

workbook.SaveAs("GeneratedInvoice.xlsx")

using IronXL;

// Load the monthly report template
WorkBook workbook = WorkBook.Load("MonthlyReportTemplate.xlsx");
WorkSheet sheet = workbook.GetWorkSheet("Monthly Report");

// Build sample sales data
var salesData = new Dictionary<string, decimal>
{
    { "Product A", 42500.00m },
    { "Product B", 31750.50m },
    { "Product C", 18300.25m }
};

// Write report header
sheet["B2"].Value = $"Sales Report - {DateTime.Now:MMMM yyyy}";
sheet["B3"].Value = $"Generated: {DateTime.Now:g}";

// Write each product row starting at row 6
int currentRow = 6;
decimal totalSales = salesData.Values.Sum();

foreach (var sale in salesData)
{
    sheet[$"B{currentRow}"].Value = sale.Key;
    sheet[$"C{currentRow}"].DecimalValue = sale.Value;
    // Percentage of total formula
    sheet[$"D{currentRow}"].Formula = $"=C{currentRow}/C{currentRow + salesData.Count}*100";
    currentRow++;
}

// Write the total row and apply bold style
sheet[$"C{currentRow}"].DecimalValue = totalSales;
sheet[$"C{currentRow}"].Style.Font.Bold = true;

// Save with a date-stamped filename
string outputPath = $"Reports/Monthly_Report_{DateTime.Now:yyyyMMdd}.xlsx";
workbook.SaveAs(outputPath);

Imports IronXL

' Load the monthly report template
Dim workbook As WorkBook = WorkBook.Load("MonthlyReportTemplate.xlsx")
Dim sheet As WorkSheet = workbook.GetWorkSheet("Monthly Report")

' Build sample sales data
Dim salesData As New Dictionary(Of String, Decimal) From {
    {"Product A", 42500.0D},
    {"Product B", 31750.5D},
    {"Product C", 18300.25D}
}

' Write report header
sheet("B2").Value = $"Sales Report - {DateTime.Now:MMMM yyyy}"
sheet("B3").Value = $"Generated: {DateTime.Now:g}"

' Write each product row starting at row 6
Dim currentRow As Integer = 6
Dim totalSales As Decimal = salesData.Values.Sum()

For Each sale In salesData
    sheet($"B{currentRow}").Value = sale.Key
    sheet($"C{currentRow}").DecimalValue = sale.Value
    ' Percentage of total formula
    sheet($"D{currentRow}").Formula = $"=C{currentRow}/C{currentRow + salesData.Count}*100"
    currentRow += 1
Next

' Write the total row and apply bold style
sheet($"C{currentRow}").DecimalValue = totalSales
sheet($"C{currentRow}").Style.Font.Bold = True

' Save with a date-stamped filename
Dim outputPath As String = $"Reports/Monthly_Report_{DateTime.Now:yyyyMMdd}.xlsx"
workbook.SaveAs(outputPath)