浮雲雅築: [研究][ASP.NET][WebForm]使用 ClosedXML 0.95.3 讀取、寫入 Excel .xlsx 的 Cell 值

2020年9月9日星期三

[研究][ASP.NET][WebForm]使用 ClosedXML 0.95.3 讀取、寫入 Excel .xlsx 的 Cell 值

[研究][ASP.NET][WebForm]使用 ClosedXML 0.95.3修改 Excel .xlsx 的 Cell 值

2020-09-09

2021-03-16 更新

工具 Visual Studio 2019 + Web Application ( WebForm ) 架構

先用 NuGet 安裝 ClosedXML 元件。

Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="ClosedXMLUpdateCellTest.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">
        <div>
            <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Button" />
            <br />
            <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
        </div>
    </form>
</body>
</html>

Default.aspx.cs

using ClosedXML.Excel;
using System;
using System.Web;

namespace ClosedXMLUpdateCellTest
{
    public partial class Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            string openFilename = HttpContext.Current.Server.MapPath("Test.xlsx");
            using (XLWorkbook workbook = new XLWorkbook(openFilename))
            {
                // 讀取第一個 Sheet
                IXLWorksheet workSheet = workbook.Worksheet(1);
                //var workSheet = workBook.Worksheets.First();

                //var row = worksheet.FirstRow();

                // 否則會出現類似 The range 工作表1!A1:U57 overlaps with the worksheet's autofilter. 錯誤訊息
                worksheet.AutoFilter.IsEnabled = false;

                //var firstCell = workSheet.FirstCellUsed();
                IXLCell firstCell = workSheet.FirstCellUsed();
                var lastCell = workSheet.LastCellUsed();
                var range = workSheet.Range(firstCell.Address, lastCell.Address);

                int excelRowCount = range.RowCount();
                int excelColumnCount = range.ColumnCount();
                for (int i = 1; i <= excelRowCount; i++)
                {
                    if (i==1)
                    {
                        Label1.Text = "";
                    }
                    else
                    {
                        Label1.Text = Label1.Text + "<br />";
                    }
                    for (int j = 1; j <= excelColumnCount; j++)
                    {
                        string cellText = workSheet.Cell(i, j).GetString();
                        if (j == 1)
                        {
                            Label1.Text = Label1.Text + cellText;
                        }
                        else
                        {
                            Label1.Text = Label1.Text + " ; " +  cellText;
                        }
                    }
                    workSheet.Cell(i, excelColumnCount + 1).SetValue("test"); 
                }
                workbook.SaveAs(openFilename);
            } // using
        }
    }
}

(下圖)執行前