[研究]ASP.NET,WebForm,用 MiniExcel 1.34.2匯出、匯入 .xlsx (不支援 .ods)
2024-10-28
環境：Visual Studio 2022 + ASP.NET + WebForm + Web Application + C# + SQL Server 2019 + SQL Server Management Studio (SSMS) 19
********************************************************************************
Fast, Low-Memory, Easy Excel .NET helper to import/export/template spreadsheet
用於匯入/匯出/範本電子表格的快速、低記憶體、簡單的 Excel .NET 助手
Github : https://github.com/mini-software/MiniExcel
Gitee : https://gitee.com/dotnetchina/MiniExcel
Issues : https://github.com/mini-software/MiniExcel/issues
Todo : https://github.com/mini-software/MiniExcel/projects/1?fullscreen=true
授權 Apache-2.0
匯入、匯出範例：https://github.com/mini-software/MiniExcel?tab=readme-ov-file#getstart1
********************************************************************************
【匯出.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 MiniExcelLibs;
using System;
using System.Collections.Generic;
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)
{
var data = new List<Dictionary<string, object>>
{
new Dictionary<string, object> { { "Name", "Alice" }, { "Age", 25 }, { "City", "Taipei" } },
new Dictionary<string, object> { { "Name", "Bob" }, { "Age", 30 }, { "City", "Kaohsiung" } }
};
// 設定 Response 內容
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("Content-Disposition", "attachment;filename=ExportData.xlsx");
// 匯出 Excel
using (var stream = new MemoryStream())
{
MiniExcel.SaveAs(stream, data);
stream.WriteTo(Response.OutputStream);
Response.Flush();
}
Response.End();
}
}
}
|
********************************************************************************
【匯入.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="FileUpload1" runat="server" /><br />
<asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" /><br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="true" />
</form>
</body>
</html>
|
Default2aspx.cs
using MiniExcelLibs;
using System;
using System.Data;
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 (FileUpload1.HasFile)
{
try
{
// 獲取上傳的檔案流
using (var stream = FileUpload1.PostedFile.InputStream)
{
// 使用 MiniExcel 讀取 Excel 檔案
//var data = stream.ReadExcel();
//var table = MiniExcel.QueryAsDataTable(stream, useHeaderRow: true);
//var table = MiniExcel.QueryAsDataTable(stream, useHeaderRow: false);
var table = MiniExcel.QueryAsDataTable(stream);
// 創建一個新的 DataTable 用於存放處理過的資料
DataTable processedTable = new DataTable();
// 添加列到 processedTable
foreach (DataColumn column in table.Columns)
{
processedTable.Columns.Add(column.ColumnName, typeof(string));
//processedTable.Columns.Add(column.ColumnName, typeof(System.String)); //也可
}
// 逐行逐列處理資料
foreach (DataRow row in table.Rows)
{
DataRow newRow = processedTable.NewRow();
for (int i = 0; i < table.Columns.Count; i++)
{
newRow[i] = row[i]; // 這裡可以進行自定義處理
}
processedTable.Rows.Add(newRow);
}
GridView1.DataSource = processedTable;
GridView1.DataBind();
}
}
catch (Exception ex)
{
// 處理例外情況
Response.Write($"錯誤: {ex.Message}");
}
}
else
{
Response.Write("請選擇一個檔案進行匯入。");
}
}
//---
}
}
|
結果
相關
沒有留言:
張貼留言