[研究]ASP.NET,WebForm,用 EPPlus 7.4.2 匯入、匯出 .xlsx (不支援 .ods)
2024-10-28
環境：Visual Studio 2022 + ASP.NET + WebForm + Web Application + C# + SQL Server 2019 + SQL Server Management Studio (SSMS) 19
********************************************************************************
官方：https://www.epplussoftware.com/
Commerical License and Pricing：https://www.epplussoftware.com/en/LicenseOverview/
License FAQ：https://www.epplussoftware.com/en/LicenseOverview/LicenseFAQ
Noncommercial/free use
- Can I use EPPlus without a commercial license internally within a commercial company?
- No, all usage within an commercial company requires a commercial license.
- Can I use EPPlus for my personal project?
- Yes, the polyform NC license permits personal use. See Polyform Noncommercial.
- Can I use EPPlus for free in my project?
- You can use EPPlus for free if you qualify as a non-commercial organization, See Polyform Noncommercial.
********************************************************************************
2025-07-29 於 2025年3月釋出 EPPlus 8.0.1版，EPPlus 8.x 開始支援 .NET 8（.NET 7 也支援），相較於 7.x 最多只支援到 .NET 6。
********************************************************************************
相依
- EPPlus.Interfaces.6.1.1
- EPPlus.System.Drawing.6.1.1
- System.Buffers.4.5.1
- System.ComponentModel.Annotations.5.0.0
- System.Numerics.Vectors.4.5.0
- System.Runtime.CompilerServices.Unsafe.4.5.3
- System.Memory.4.5.5
- Microsoft.IO.RecyclableMemoryStream.3.0.1
- EPPlus.7.4.2
********************************************************************************
【匯出成為.xlsx檔案】
Default.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs"
Inherits="WebApplication1.Default" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<title></title>
</head>
<body>
<form id="form1" runat="server">
<asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />
</form>
</body>
</html>
|
Default.aspx.cs
using OfficeOpenXml; // EPPlus 命名空間
using System;
using System.Data; // 假設資料來自 DataTable
using System.IO;
namespace WebApplication1
{
public partial class Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
// 設置 EPPlus 授權為非商業用途
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
// 假設資料來自於 DataTable
DataTable dt = GetData();
using (ExcelPackage package = new ExcelPackage())
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Sheet1");
// 加入資料到 Excel 工作表
worksheet.Cells["A1"].LoadFromDataTable(dt, true);
// 設定標題行的樣式
using (var range = worksheet.Cells["A1:XFD1"])
{
range.Style.Font.Bold = true;
range.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray);
}
// 設定回應內容
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment; filename=Export.xlsx");
// 將 Excel 資料寫入回應
using (MemoryStream stream = new MemoryStream())
{
package.SaveAs(stream);
stream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
}
}
private DataTable GetData()
{
// 模擬資料
DataTable dt = new DataTable();
dt.Columns.Add("Column1");
dt.Columns.Add("Column2");
dt.Rows.Add("資料1", "資料2");
dt.Rows.Add("資料3", "資料4");
return dt;
}
}
}
|
Default.aspx.cs 要加上一行
// 設置 EPPlus 授權為非商業用途 ExcelPackage.LicenseContext = LicenseContext.NonCommercial; |
再測試
********************************************************************************
【匯入.xlsx檔案】
Default2.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default2.aspx.cs"
Inherits="WebApplication1.Default2" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<title></title>
</head>
<body>
<form id="form1" runat="server">
<asp:FileUpload ID="fileUpload" runat="server" /><br />
<asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" /><br />
<asp:GridView ID="gridView" runat="server"></asp:GridView>
</form>
</body>
</html>
|
Default2.aspx.cs
using OfficeOpenXml; // EPPlus 命名空間
using System;
using System.Data;
using System.IO;
namespace WebApplication1
{
public partial class Default2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
// 確認是否有上傳檔案
if (fileUpload.HasFile && Path.GetExtension(fileUpload.FileName).Equals(".xlsx"))
{
// 設置 EPPlus 授權為非商業用途
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (var package = new ExcelPackage(fileUpload.FileContent))
{
// 取得第一個工作表
ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
DataTable dt = new DataTable();
// 建立 DataTable 欄位名稱
foreach (var headerCell in worksheet.Cells[1, 1, 1, worksheet.Dimension.End.Column])
{
dt.Columns.Add(headerCell.Text);
}
// 讀取資料列並加入 DataTable
for (int row = 2; row <= worksheet.Dimension.End.Row; row++)
{
DataRow dr = dt.NewRow();
for (int col = 1; col <= worksheet.Dimension.End.Column; col++)
{
dr[col - 1] = worksheet.Cells[row, col].Text;
}
dt.Rows.Add(dr);
}
// 將資料綁定至 GridView 控制項
gridView.DataSource = dt;
gridView.DataBind();
}
}
else
{
// 顯示錯誤訊息（可選）
Response.Write("<script>alert('請上傳 .xlsx 格式的 Excel 檔案');</script>");
}
}
//---
}
}
|
(完)
相關
沒有留言:
張貼留言