如何使用 IronXL.Excel 在 C# 中將 Excel 資料匯出至現有的 Excel 檔案範本

using IronXL;

Imports IronXL

// 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");

Imports System

' Load the existing Excel template for data import
Dim workbook As WorkBook = WorkBook.Load("ReportTemplate.xlsx")
Dim sheet As WorkSheet = 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.50D
sheet("C7").DecimalValue = 98500.75D
sheet("C8").Formula = "=C6-C7" ' Profit calculation
' Populate a range with array 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 template
workbook.SaveAs("Q4_Sales_Report.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");

Imports System

' Load template with placeholders
Dim workbook As WorkBook = WorkBook.Load("InvoiceTemplate.xlsx")
Dim sheet As WorkSheet = workbook.DefaultWorkSheet
' Find and replace placeholder text in cells
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-2024-001")
    End If
Next
' Populate line items dynamically
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")

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 Sub GenerateMonthlyReport(templatePath As String, salesData As Dictionary(Of String, Decimal))
    ' Load the existing template file
    Dim workbook As WorkBook = WorkBook.Load(templatePath)
    Dim sheet As WorkSheet = 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
    Dim currentRow As Integer = 6
    Dim totalSales As Decimal = 0
    For Each 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 += 1
    Next
    ' Update total row with sum
    sheet($"C{currentRow}").DecimalValue = totalSales
    sheet($"C{currentRow}").Style.Font.Bold = True
    ' Save with timestamp
    Dim outputPath As String = $"Reports/Monthly_Report_{DateTime.Now:yyyyMMdd}.xlsx"
    workbook.SaveAs(outputPath)
End Sub