使用 IronXL 將 C# 中的 DataGridView 匯出到 Excel

Install-Package IronXL
dotnet add package IronXL

using IronXL;
using System.Data;
using System.Windows.Forms;

// Assume dataGridView1 is already populated with data
var dataGridView1 = new DataGridView();

// (populate dataGridView1 with data as shown in the previous example)

var workbook = WorkBook.Create(ExcelFileFormat.XLSX);
var worksheet = workbook.DefaultWorkSheet;

// Set column headers with formatting
for (int colIndex = 0; colIndex < dataGridView1.Columns.Count; colIndex++)
{
    var headerCell = worksheet.GetCellAt(0, colIndex);
    headerCell.Value = dataGridView1.Columns[colIndex].HeaderText;
    headerCell.Style.Font.Bold = true;
    headerCell.Style.BackgroundColor = "#4472C4";
    headerCell.Style.Font.Color = "#FFFFFF";
    headerCell.Style.HorizontalAlignment = IronXL.Styles.HorizontalAlignment.Center;
}

// Export data with alternating row colors
for (int rowIndex = 0; rowIndex < dataGridView1.Rows.Count; rowIndex++)
{
    if (!dataGridView1.Rows[rowIndex].IsNewRow)
    {
        for (int colIndex = 0; colIndex < dataGridView1.Columns.Count; colIndex++)
        {
            var cellValue = dataGridView1.Rows[rowIndex].Cells[colIndex].Value;
            var excelCell = worksheet.GetCellAt(rowIndex + 1, colIndex);

            if (cellValue != null)
                excelCell.Value = cellValue.ToString();

            // Apply alternating row background
            if (rowIndex % 2 == 0)
                excelCell.Style.BackgroundColor = "#F2F2F2";
        }
    }
}

// Auto-fit columns
for (int colIndex = 0; colIndex < dataGridView1.Columns.Count; colIndex++)
{
    worksheet.AutoSizeColumn(colIndex);
}

workbook.SaveAs("FormattedExport.xlsx");
Console.WriteLine("Formatted export completed successfully!");

Imports IronXL
Imports System.Data
Imports System.Windows.Forms

' Assume dataGridView1 is already populated with data
Dim dataGridView1 As New DataGridView()

' (populate dataGridView1 with data as shown in the previous example)

Dim workbook = WorkBook.Create(ExcelFileFormat.XLSX)
Dim worksheet = workbook.DefaultWorkSheet

' Set column headers with formatting
For colIndex As Integer = 0 To dataGridView1.Columns.Count - 1
    Dim headerCell = worksheet.GetCellAt(0, colIndex)
    headerCell.Value = dataGridView1.Columns(colIndex).HeaderText
    headerCell.Style.Font.Bold = True
    headerCell.Style.BackgroundColor = "#4472C4"
    headerCell.Style.Font.Color = "#FFFFFF"
    headerCell.Style.HorizontalAlignment = IronXL.Styles.HorizontalAlignment.Center
Next

' Export data with alternating row colors
For rowIndex As Integer = 0 To dataGridView1.Rows.Count - 1
    If Not dataGridView1.Rows(rowIndex).IsNewRow Then
        For colIndex As Integer = 0 To dataGridView1.Columns.Count - 1
            Dim cellValue = dataGridView1.Rows(rowIndex).Cells(colIndex).Value
            Dim excelCell = worksheet.GetCellAt(rowIndex + 1, colIndex)

            If cellValue IsNot Nothing Then
                excelCell.Value = cellValue.ToString()
            End If

            ' Apply alternating row background
            If rowIndex Mod 2 = 0 Then
                excelCell.Style.BackgroundColor = "#F2F2F2"
            End If
        Next
    End If
Next

' Auto-fit columns
For colIndex As Integer = 0 To dataGridView1.Columns.Count - 1
    worksheet.AutoSizeColumn(colIndex)
Next

workbook.SaveAs("FormattedExport.xlsx")
Console.WriteLine("Formatted export completed successfully!")

using IronXL;

var workbook = WorkBook.Create(ExcelFileFormat.XLSX);
var worksheet = workbook.DefaultWorkSheet;

// (populate worksheet with DataGridView data as shown above)
int dataRowCount = 5; // Replace with actual dataGridView1.Rows.Count

// Add a SUM formula to calculate the total price column
worksheet.SetCellValue(dataRowCount + 2, 3, $"=SUM(D2:D{dataRowCount + 1})");

// Create a summary worksheet
var summarySheet = workbook.CreateWorkSheet("Summary");
summarySheet.SetCellValue(0, 0, "Total Products");
summarySheet.SetCellValue(0, 1, dataRowCount);

// Save in multiple formats
workbook.SaveAs("export.xlsx");
workbook.SaveAsCsv("export.csv");
workbook.SaveAsJson("export.json");
workbook.SaveAsXml("export.xml");

Console.WriteLine("Multi-format export completed!");

Imports IronXL

Dim workbook = WorkBook.Create(ExcelFileFormat.XLSX)
Dim worksheet = workbook.DefaultWorkSheet

' (populate worksheet with DataGridView data as shown above)
Dim dataRowCount As Integer = 5 ' Replace with actual dataGridView1.Rows.Count

' Add a SUM formula to calculate the total price column
worksheet.SetCellValue(dataRowCount + 2, 3, $"=SUM(D2:D{dataRowCount + 1})")

' Create a summary worksheet
Dim summarySheet = workbook.CreateWorkSheet("Summary")
summarySheet.SetCellValue(0, 0, "Total Products")
summarySheet.SetCellValue(0, 1, dataRowCount)

' Save in multiple formats
workbook.SaveAs("export.xlsx")
workbook.SaveAsCsv("export.csv")
workbook.SaveAsJson("export.json")
workbook.SaveAsXml("export.xml")

Console.WriteLine("Multi-format export completed!")