浮雲雅築: [研究][ASP.NET]使用 ClosedXML 0.95.4 讀取匯入 .xlsx 到資料庫(暫存、不暫存檔)

[研究][ASP.NET]使用 ClosedXML 0.95.4 讀取匯入 .xlsx 到資料庫

2021-02-04
2021-03-16更新
2022-06-13補不寫暫存檔案的方法

環境：Visual Studio 2019 + ASP.NET + WebForm + C# + NuGet 安裝 ClosedXML 0.95.4

packages.conf

<?xml version="1.0" encoding="utf-8"?>

</packages>

Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="ImportXlsxByClosedXMLTest.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:FileUpload ID="FileUpload1" runat="server" /><br />
            <asp:Button ID="Button_Import_Xlsx_by_ClosedXML" runat="server" Text="匯入(.xlsx)" OnClick="Button_Import_Xlsx_by_ClosedXML_Click" /><br />
            <br />
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TestDBConnectionString %>" SelectCommand="SELECT * FROM [Table1]"></asp:SqlDataSource>
            <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="id" DataSourceID="SqlDataSource1">
                <Columns>
                    <asp:BoundField DataField="id" HeaderText="id" InsertVisible="False" ReadOnly="True" SortExpression="id" />
                    <asp:BoundField DataField="FieldText" HeaderText="FieldText" SortExpression="FieldText" />
                    <asp:BoundField DataField="FieldDateTime" HeaderText="FieldDateTime" SortExpression="FieldDateTime" />
                    <asp:BoundField DataField="FieldInt" HeaderText="FieldInt" SortExpression="FieldInt" />
                    <asp:CheckBoxField DataField="FieldBit" HeaderText="FieldBit" SortExpression="FieldBit" />
                </Columns>
            </asp:GridView>
            <asp:Label ID="Label_ExportImportMSG" runat="server"></asp:Label><br />
            <asp:Label ID="Label_MSG1" runat="server"></asp:Label>
        </div>
    </form>
</body>
</html>

Default.aspx.cs (寫暫存檔)

using ClosedXML.Excel;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Configuration;
using System.Web.UI;
using System.Web.UI.WebControls;

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

        }

        protected void Button_Import_Xlsx_by_ClosedXML_Click(object sender, EventArgs e)
        {
            // ClosedXML 官方網站 (有範例)
            // https://github.com/ClosedXML/ClosedXML/wiki

            Label_MSG1.Text = "";
            Label_ExportImportMSG.Text = "";
            int lineNo = 0;
            int lineCount = 0;
            int successCount = 0;
            string queryString = "";
            string errMsg = "";

            string fieldText = "";
            string fieldDateTime = "";
            string fieldInt = "";
            string fieldBit = "";

            int rowCount = 0;
            int columnCount = 0;

            if (!FileUpload1.HasFile)
            {
                Label_ExportImportMSG.Text = "<font color=red>請選擇.xlsx檔案</font>";
                Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript'>alert('請選擇.xlsx檔案。');</script>");
                return;
            }
            else
            {
                string fd = (string)ConfigurationManager.AppSettings["TempFolder"] + "\\";
                if (!System.IO.Directory.Exists(fd))
                {
                    System.IO.Directory.CreateDirectory(fd);
                }
                string ext = System.IO.Path.GetExtension(FileUpload1.FileName).ToLower();
                // ClosedXML 0.94.2 不支援 .csv
                // Extension 'csv' is not supported. Supported extensions are '.xlsx', '.xslm', '.xltx' and '.xltm'.
                //if (!((ext == ".xlsx") || (ext == ".csv")))
                if (!(ext == ".xlsx"))
                {
                    Label_ExportImportMSG.Text = "<font color=red>請上傳副檔名.xlsx檔案</font>";
                    //Label_MSG1.Text = "<font color=red>請上傳副檔名.xlsx檔案</font>";
                    ScriptManager.RegisterStartupScript(this, GetType(), "Popup1", "erroralert('" + "請上傳副檔名.xlsx檔案！" + "');", true);
                    return;
                }
                string fileName = fd + FileUpload1.FileName;
                FileUpload1.SaveAs(fileName);

                string mainFileName = Path.GetFileNameWithoutExtension(FileUpload1.FileName);
                //string openFilename = "D:\\Production\\TempImageFiles\\" + mainFileName + "-匯入結果.xlsx";
                string openFilename = fd + mainFileName + "-匯入結果" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff") + ".xlsx";
                FileUpload1.SaveAs(openFilename);

                //queryString = @"DELETE FROM [dbo].[Table1] WHERE id=@id; ";
                queryString = @"DELETE FROM [dbo].[Table1]; ";

                using (SqlConnection connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["TestDBConnectionString"].ConnectionString))
                {
                    SqlCommand command = new SqlCommand(queryString, connection);
                    connection.Open();
                    command.ExecuteNonQuery();
                }

                using (XLWorkbook workbook = new XLWorkbook(openFilename))
                {
                    // 讀取第一個 Sheet
                    IXLWorksheet worksheet = workbook.Worksheet(1);

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

                    // 定義資料起始/結束 Cell
                    var firstCell = worksheet.FirstCellUsed();
                    var lastCell = worksheet.LastCellUsed();

                    // 使用資料起始/結束 Cell，來定義出一個資料範圍
                    var data = worksheet.Range(firstCell.Address, lastCell.Address);

                    // 將資料範圍轉型
                    var table = data.AsTable();

                    //讀取資料
                    //string Excel = "";
                    //Excel = table.Cell(6, 1).Value.ToString();

                    //寫入資料
                    //table.Cell(2, 1).Value = "test";

                    rowCount = table.RowCount();
                    columnCount = table.ColumnCount();

                    // i=1，第1列標題，不讀取；第2列開始是資料
                    for (int i = 2; i <= rowCount; i++)
                    {
                        // 填入值, Cell 的 Column number must be between 1 and 16384
                        fieldText = table.Cell(i, 1).Value.ToString();  // 必填，無財產編號請寫「無」
                        if (fieldText == "")
                        {
                            errMsg = "#1匯入.xlsx共" + rowCount + "列" + columnCount + "行 (含標題列，若看不到，可能填寫了不可見字元)，成功匯入" +  successCount + "筆，第" + (i - 1) + "筆 (Excel 第" + i.ToString() + "列)第一欄位未填，匯入中斷！若資料已全部匯入，可不理會。";
                            Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript'>alert('" + errMsg + "');</script>");
                            return;
                        }
                        else
                        {
                            fieldDateTime = table.Cell(i, 2).Value.ToString();
                            DateTime.TryParse(fieldDateTime, out DateTime fieldDateTime2);

                            fieldInt = table.Cell(i, 3).Value.ToString();
                            fieldBit = table.Cell(i, 4).Value.ToString();
                            
                            if (errMsg != "")
                            {
                                errMsg = "#2匯入.xlsx共" + rowCount + "列" + columnCount + "行 (含標題列，若看不到，可能填寫了不可見字元)，第" + (i-1) + "筆 (Excel 第" + i.ToString() + "列)匯入失敗。\\n" + errMsg;
                                Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript'>alert('" + errMsg + "');</script>");
                                return;
                            }

                            queryString = @"
INSERT INTO [dbo].[Table1]
           ([FieldText]
           ,[FieldDateTime]
           ,[FieldInt]
           ,[FieldBit])
     VALUES
           (N'" + fieldText + "'" +
           ",'" + fieldDateTime2.ToString("yyyy/MM/dd HH:mm:ss.fff") + "'" +
           ",'" + fieldInt + "'" +
           ",'" + fieldBit + "'); ";

                            using (SqlConnection connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["TestDBConnectionString"].ConnectionString))
                            {
                                SqlCommand command = new SqlCommand(queryString, connection);
                                //command.Parameters.Clear();
                                //command.Parameters.AddWithValue("@value", value);
                                connection.Open();
                                try
                                {
                                    command.ExecuteNonQuery();
                                    successCount++;
                                }
                                catch (Exception ex)
                                {
                                    string exceptionMsg = "不明錯誤。";
                                    if (ex != null)
                                    {
                                        exceptionMsg = ex.Message.ToString();
                                    }
                                    // Exception 用 JavaScript 顯示，可能會因為內容關係，導致 alert 對話盒視窗無法顯示。
                                    //errMsg = "#3匯入.xlsx共" + rowCount + "列" + columnCount + "行 (含標題列，若看不到，可能填寫了不可見字元)，第" + (i-1) + "筆 (Excel 第" + i.ToString() + "列)匯入失敗。\\n" + exceptionMsg;
                                    errMsg = "#3匯入.xlsx共" + rowCount + "列" + columnCount + "行 (含標題列，若看不到，可能填寫了不可見字元)，第" + (i - 1) + "筆 (Excel 第" + i.ToString() + "列)匯入失敗。\\n";
                                    //ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert('" + errMsg + "');</script>");
                                    Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript'>alert('" + errMsg + "');</script>");
                                    return;
                                }
                            } // using (SqlConnection connection
                        } // if (fieldText=="")
                    } // for
                } // using

                //string url = "/List.aspx?Ref1=" + Request.QueryString["Ref1"].ToString() + "&Ref2=" + Request.QueryString["Ref2"].ToString() + "&Ref3=" + Request.QueryString["Ref3"].ToString();
                string url = "#";

                string msg = "#4匯入.xlsx共" + rowCount + "列" + columnCount + "行 (含標題列，若看不到，可能填寫了不可見字元)，成功匯入。\\n";
                ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert('" + msg + "按【確定】轉往列表畫面。');location.href='" + url + "';</script>");
            } //if (FileUpload1.HasFile)

            GridView1.DataBind();
        }
    }
}

********************************************************************************

Default.aspx.cs (不寫暫存檔)

using ClosedXML.Excel;
using System;
using System.Data.SqlClient;
using System.IO;
using System.Web.Configuration;
using System.Web.UI;

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

        }

        protected void Button_Import_Xlsx_by_ClosedXML_Click(object sender, EventArgs e)
        {
            // ClosedXML 官方網站 (有範例)
            // https://github.com/ClosedXML/ClosedXML/wiki

            Label_MSG1.Text = "";
            int lineNo = 0;
            int lineCount = 0;
            int successCount = 0;
            string queryString = "";
            string errMsg = "";

            string fieldText = "";
            string fieldDateTime = "";
            string fieldInt = "";
            string fieldBit = "";

            int rowCount = 0;
            int columnCount = 0;

            if (!FileUpload1.HasFile)
            {
                Label_MSG1.Text = "<font color=red>請選擇.xlsx檔案</font>";
                Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript'>alert('請選擇.xlsx檔案。');</script>");
                return;
            }
            else
            {
                //string fd = (string)ConfigurationManager.AppSettings["TempFolder"] + "\\";
                //if (!System.IO.Directory.Exists(fd))
                //{
                //    System.IO.Directory.CreateDirectory(fd);
                //}
                string ext = System.IO.Path.GetExtension(FileUpload1.FileName).ToLower();
                // ClosedXML 0.94.2 不支援 .csv
                // Extension 'csv' is not supported. Supported extensions are '.xlsx', '.xslm', '.xltx' and '.xltm'.
                //if (!((ext == ".xlsx") || (ext == ".csv")))
                if (!(ext == ".xlsx"))
                {
                    Label_MSG1.Text = "<font color=red>請上傳副檔名.xlsx檔案</font>";
                    //Label_MSG1.Text = "<font color=red>請上傳副檔名.xlsx檔案</font>";
                    ScriptManager.RegisterStartupScript(this, GetType(), "Popup1", "erroralert('" + "請上傳副檔名.xlsx檔案！" + "');", true);
                    return;
                }
                //string fileName = fd + FileUpload1.FileName;
                //FileUpload1.SaveAs(fileName);

                string mainFileName = Path.GetFileNameWithoutExtension(FileUpload1.FileName);
                //string openFilename = "D:\\Production\\TempImageFiles\\" + mainFileName + "-匯入結果.xlsx";
                //string openFilename = fd + mainFileName + "-匯入結果" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff") + ".xlsx";
                //FileUpload1.SaveAs(openFilename);

                string filePath = FileUpload1.PostedFile.FileName;
                string filename = Path.GetFileName(filePath);

                Stream fs = FileUpload1.PostedFile.InputStream;
                BinaryReader br = new BinaryReader(fs);
                Byte[] bytes = br.ReadBytes((Int32)fs.Length);

                MemoryStream destination = new MemoryStream(bytes);

                //queryString = @"DELETE FROM [dbo].[Table1] WHERE id=@id; ";
                queryString = @"DELETE FROM [dbo].[Table1]; ";

                using (SqlConnection connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["TestDBConnectionString"].ConnectionString))
                {
                    SqlCommand command = new SqlCommand(queryString, connection);
                    connection.Open();
                    command.ExecuteNonQuery();
                }

                //using (XLWorkbook workbook = new XLWorkbook(openFilename))
                using (XLWorkbook workbook = new XLWorkbook(destination))
                {
                    // 讀取第一個 Sheet
                    IXLWorksheet worksheet = workbook.Worksheet(1);

                    // 否則會出現類似 The range 工作表1!A1:U57 overlaps with the worksheet's autofilter. 錯誤訊息
                    worksheet.AutoFilter.IsEnabled = false;
                    // 定義資料起始/結束 Cell
                    var firstCell = worksheet.FirstCellUsed();
                    var lastCell = worksheet.LastCellUsed();

                    // 使用資料起始/結束 Cell，來定義出一個資料範圍
                    var data = worksheet.Range(firstCell.Address, lastCell.Address);

                    // 將資料範圍轉型
                    var table = data.AsTable();

                    //讀取資料
                    //string Excel = "";
                    //Excel = table.Cell(6, 1).Value.ToString();

                    //寫入資料
                    //table.Cell(2, 1).Value = "test";

                    rowCount = table.RowCount();
                    columnCount = table.ColumnCount();

                    // i=1，第1列標題，不讀取；第2列開始是資料
                    for (int i = 2; i <= rowCount; i++)
                    {
                        // 填入值, Cell 的 Column number must be between 1 and 16384
                        fieldText = table.Cell(i, 1).Value.ToString();  // 必填，無財產編號請寫「無」
                        if (fieldText == "")
                        {
                            errMsg = "#1匯入.xlsx共" + rowCount + "列" + columnCount + "行 (含標題列，若看不到，可能填寫了不可見字元)，成功匯入" + successCount + "筆，第" + (i - 1) + "筆 (Excel 第" + i.ToString() + "列)第一欄位未填，匯入中斷！若資料已全部匯入，可不理會。";
                            Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript'>alert('" + errMsg + "');</script>");
                            return;
                        }
                        else
                        {
                            fieldDateTime = table.Cell(i, 2).Value.ToString();
                            DateTime.TryParse(fieldDateTime, out DateTime fieldDateTime2);

                            fieldInt = table.Cell(i, 3).Value.ToString();
                            fieldBit = table.Cell(i, 4).Value.ToString();

                            if (errMsg != "")
                            {
                                errMsg = "#2匯入.xlsx共" + rowCount + "列" + columnCount + "行 (含標題列，若看不到，可能填寫了不可見字元)，第" + (i - 1) + "筆 (Excel 第" + i.ToString() + "列)匯入失敗。\\n" + errMsg;
                                Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript'>alert('" + errMsg + "');</script>");
                                return;
                            }

                            queryString = @"
INSERT INTO [dbo].[Table1]
           ([FieldText]
           ,[FieldDateTime]
           ,[FieldInt]
           ,[FieldBit])
     VALUES
           (N'" + fieldText + "'" +
           ",'" + fieldDateTime2.ToString("yyyy/MM/dd HH:mm:ss.fff") + "'" +
           ",'" + fieldInt + "'" +
           ",'" + fieldBit + "'); ";

                            using (SqlConnection connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["TestDBConnectionString"].ConnectionString))
                            {
                                SqlCommand command = new SqlCommand(queryString, connection);
                                //command.Parameters.Clear();
                                //command.Parameters.AddWithValue("@value", value);
                                connection.Open();
                                try
                                {
                                    command.ExecuteNonQuery();
                                    successCount++;
                                }
                                catch (Exception ex)
                                {
                                    string exceptionMsg = "不明錯誤。";
                                    if (ex != null)
                                    {
                                        exceptionMsg = ex.Message.ToString();
                                    }
                                    // Exception 用 JavaScript 顯示，可能會因為內容關係，導致 alert 對話盒視窗無法顯示。
                                    //errMsg = "#3匯入.xlsx共" + rowCount + "列" + columnCount + "行 (含標題列，若看不到，可能填寫了不可見字元)，第" + (i-1) + "筆 (Excel 第" + i.ToString() + "列)匯入失敗。\\n" + exceptionMsg;
                                    errMsg = "#3匯入.xlsx共" + rowCount + "列" + columnCount + "行 (含標題列，若看不到，可能填寫了不可見字元)，第" + (i - 1) + "筆 (Excel 第" + i.ToString() + "列)匯入失敗。\\n";
                                    //ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert('" + errMsg + "');</script>");
                                    Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript'>alert('" + errMsg + "');</script>");
                                    return;
                                }
                            } // using (SqlConnection connection
                        } // if (fieldText=="")
                    } // for
                } // using

                //string url = "/List.aspx?Ref1=" + Request.QueryString["Ref1"].ToString() + "&Ref2=" + Request.QueryString["Ref2"].ToString() + "&Ref3=" + Request.QueryString["Ref3"].ToString();
                string url = "#";

                string msg = "#4匯入.xlsx共" + rowCount + "列" + columnCount + "行 (含標題列，若看不到，可能填寫了不可見字元)，成功匯入。\\n";
                ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert('" + msg + "按【確定】轉往列表畫面。');location.href='" + url + "';</script>");
            } //if (FileUpload1.HasFile)

            GridView1.DataBind();
        }
    }
}

(完)

相關

[研究][ASP.NET]ClosedXML元件常用屬性或方法http://shaurong.blogspot.com/2022/04/aspnetclosedxml.html

[研究][ASP.NET]使用 ClosedXML 0.95.4 讀取匯入 .xlsx 到資料庫(暫存、不暫存檔)
http://shaurong.blogspot.com/2021/02/aspnet-closedxml-0954-xlsx.html

[研究][ASP.NET]使用 ClosedXML 0.95.4 匯出、寫入 .xlsx(暫存、不暫存檔)
http://shaurong.blogspot.com/2021/02/aspnet-closedxml-0954-xlsx_17.html

浮雲雅築

2021年2月4日星期四

[研究][ASP.NET]使用 ClosedXML 0.95.4 讀取匯入 .xlsx 到資料庫(暫存、不暫存檔)

沒有留言:

張貼留言

2021年2月4日 星期四

[研究][ASP.NET]使用 ClosedXML 0.95.4 讀取匯入 .xlsx 到資料庫(暫存、不暫存檔)

沒有留言:

張貼留言

2021年2月4日星期四