如何在 ASP.NET C# 中將 Excel 匯入 GridView

using IronXL;
using System.Data;
using System.IO;

Imports IronXL
Imports System.Data
Imports System.IO

using IronXL;
using System.Data;

WorkBook workBook = WorkBook.Load("data.xlsx");
WorkSheet sheet = workBook.DefaultWorkSheet;

// true = treat first row as column headers
DataTable dataTable = sheet.ToDataTable(true);

GridView1.DataSource = dataTable;
GridView1.DataBind();

Imports IronXL
Imports System.Data

Dim workBook As WorkBook = WorkBook.Load("data.xlsx")
Dim sheet As WorkSheet = workBook.DefaultWorkSheet

' True = treat first row as column headers
Dim dataTable As DataTable = sheet.ToDataTable(True)

GridView1.DataSource = dataTable
GridView1.DataBind()

using IronXL;
using System.Data;

WorkBook workBook = WorkBook.Load("sales-report.xlsx");

// Access a named sheet
WorkSheet salesSheet = workBook.GetWorkSheet("Q4 Sales");

// Read a specific cell range
var topTen = salesSheet["A1:E11"];

// Convert the entire workbook to a DataSet (one DataTable per sheet)
DataSet allSheets = workBook.ToDataSet();

// Bind the first sheet's DataTable to the grid
GridView1.DataSource = allSheets.Tables[0];
GridView1.DataBind();

Imports IronXL
Imports System.Data

Dim workBook As WorkBook = WorkBook.Load("sales-report.xlsx")

' Access a named sheet
Dim salesSheet As WorkSheet = workBook.GetWorkSheet("Q4 Sales")

' Read a specific cell range
Dim topTen = salesSheet("A1:E11")

' Convert the entire workbook to a DataSet (one DataTable per sheet)
Dim allSheets As DataSet = workBook.ToDataSet()

' Bind the first sheet's DataTable to the grid
GridView1.DataSource = allSheets.Tables(0)
GridView1.DataBind()

void btnExport_Click(object sender, EventArgs e)
{
    DataTable dt = Session["CurrentData"] as DataTable;
    if (dt == null) return;

    WorkBook workBook = WorkBook.Create(ExcelFileFormat.XLSX);
    WorkSheet ws = workBook.CreateWorkSheet("Export");

    // Write header row
    for (int col = 0; col < dt.Columns.Count; col++)
        ws.SetCellValue(0, col, dt.Columns[col].ColumnName);

    // Write data rows
    for (int row = 0; row < dt.Rows.Count; row++)
        for (int col = 0; col < dt.Columns.Count; col++)
            ws.SetCellValue(row + 1, col, dt.Rows[row][col]?.ToString() ?? string.Empty);

    string exportDir = Server.MapPath("~/Exports/");
    Directory.CreateDirectory(exportDir);
    string exportPath = Path.Combine(exportDir, "export.xlsx");
    workBook.SaveAs(exportPath);

    Response.Clear();
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    Response.AddHeader("Content-Disposition", "attachment; filename=export.xlsx");
    Response.TransmitFile(exportPath);
    Response.End();
}

Imports System
Imports System.Data
Imports System.IO

Sub btnExport_Click(sender As Object, e As EventArgs)
    Dim dt As DataTable = TryCast(Session("CurrentData"), DataTable)
    If dt Is Nothing Then Return

    Dim workBook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
    Dim ws As WorkSheet = workBook.CreateWorkSheet("Export")

    ' Write header row
    For col As Integer = 0 To dt.Columns.Count - 1
        ws.SetCellValue(0, col, dt.Columns(col).ColumnName)
    Next

    ' Write data rows
    For row As Integer = 0 To dt.Rows.Count - 1
        For col As Integer = 0 To dt.Columns.Count - 1
            ws.SetCellValue(row + 1, col, If(dt.Rows(row)(col)?.ToString(), String.Empty))
        Next
    Next

    Dim exportDir As String = Server.MapPath("~/Exports/")
    Directory.CreateDirectory(exportDir)
    Dim exportPath As String = Path.Combine(exportDir, "export.xlsx")
    workBook.SaveAs(exportPath)

    Response.Clear()
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    Response.AddHeader("Content-Disposition", "attachment; filename=export.xlsx")
    Response.TransmitFile(exportPath)
    Response.End()
End Sub