浮雲雅築: [研究][ASP.NET]使用 OpenXML SDK 2.12.1 讀取匯入 .xlsx 到資料庫

[研究][ASP.NET]使用 DocumentFormat.OpenXml 2.12.1 讀取匯入 .xlsx 到資料庫

2021-02-04

Microsoft OpenXML SDK 安裝後，packages.conf 中元件名稱是 DocumentFormat.OpenXml。

環境：Visual Studio 2019 + ASP.NET + WebForm + C# + NuGet 安裝 OpenXML SDK 2.12.1

Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="ImportXlsxByOpenXMLSDKTest.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_OpenXMLSDK_DocumentFormatOpenXml" runat="server" Text="匯入(.xlsx)" OnClick="Button_Import_xlsx_by_OpenXMLSDK_DocumentFormatOpenXml_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 DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
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 ImportXlsxByOpenXMLSDKTest
{
    public partial class Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void Button_Import_xlsx_by_OpenXMLSDK_DocumentFormatOpenXml_Click(object sender, EventArgs e)
        {
            // Microsoft OpenXML SDK 安裝後，packages.conf 中元件名稱是 DocumentFormat.OpenXml
            // https://docs.microsoft.com/en-us/office/open-xml/how-to-parse-and-read-a-large-spreadsheet

            Label_MSG1.Text = "";
            Label_ExportImportMSG.Text = "";
            int lineNo = 0;
            int lineCount = 0;
            int successCount = 0;
            string queryString = "";
            string errMsg = "";
            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' defer>alert('請選擇.xlsx檔案。');</script>");
                return;
            }
            else
            {
                //string fd = (string)ConfigurationManager.AppSettings["TempFolder"] + "\\";
                string fd = @"C:\Temp";
                if (!System.IO.Directory.Exists(fd))
                {
                    System.IO.Directory.CreateDirectory(fd);
                }
                string ext = System.IO.Path.GetExtension(FileUpload1.FileName).ToLower();
                if (!(ext == ".xlsx"))
                {
                    Label_ExportImportMSG.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 = fd + mainFileName + "-匯入結果" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff") + ".xlsx";
                FileUpload1.SaveAs(openFilename);

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

                // 清空
                //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 (DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheetDocument =
                    DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(openFilename, false))
                {
                    WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
                    WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
                    SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();
                    //string text;

                    lineCount = sheetData.Elements<Row>().Count();
                    rowCount = sheetData.Elements<Row>().Count();
                    // 錯誤
                    //int columnCount2 = sheetData.Elements<Columns>().Count();
                    //int columnCount3 = sheetData.Elements<ColumnFields>().Count();
                    //int columnCount4 = sheetData.Elements<ColumnItems>().Count();
                    //int columnCount5 = sheetData.Elements<Cell>().Count();
                    foreach (Row r in sheetData.Elements<Row>())
                    {
                        columnCount = r.Descendants<Cell>().Count();

                        //foreach (Cell c in r.Elements<Cell>())
                        //{
                        //    text = c.CellValue.Text;
                        //    //Console.Write(text + " ");
                        //}

                        // lineNo 初始值0，lineNo=1，第1列標題，不讀取
                        lineNo++;
                        if (lineNo != 1)
                        {
                            // 讀文字
                            // CellValues.SharedString
                            // Cell cell = (Cell)r.ChildElements.GetItem(0);
                            // cell.DataType = CellValues.SharedString
                            
                            // 似乎 OpenXML 會跳過空橫列不理會，讀取下一橫列內容
                            fieldText = getOpenXMLSDKCellValue(workbookPart, (Cell)r.ChildElements.GetItem(0));
                            
                            if (fieldText != "")
                            {
                                // CellValues.Date
                                fieldDateTime = getOpenXMLSDKCellValue(workbookPart, (Cell)r.ChildElements.GetItem(1));
                                DateTime.TryParse(fieldDateTime, out DateTime fieldDateTime2);

                                // 數值讀法和文字不同，就算「儲存格格式」設定為「文字」，輸入數字，仍可正常讀取
                                // CellValues.Number
                                fieldInt = ((Cell)r.ChildElements.GetItem(2)).InnerText;

                                // Excel 填 1 或 0 表示 True 或 False
                                fieldBit = ((Cell)r.ChildElements.GetItem(3)).InnerText;

                                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("@unit_oid", DropDownList_Jiguan.SelectedValue);
                                    connection.Open();
                                    try
                                    {
                                        command.ExecuteNonQuery();
                                        successCount++;
                                    }
                                    catch (Exception ex)
                                    {
                                        errMsg = "不明錯誤。";
                                        if (ex != null)
                                            errMsg = ex.Message.ToString();

                                        errMsg = "#1匯入.xlsx 共 " + rowCount + " 列 " + columnCount + " 行 (若沒看到，可能填寫了不可見字元)，" +
                                            "第" + lineNo + "筆(Excel 第" + (lineNo + 1) + "列) 匯入失敗。\\n" + errMsg;
                                        Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('" + errMsg + "'');</script>");
                                        return;
                                    }
                                } // using (SqlConnection connection
                            } // if
                            else
                            {
                                // fieldText == ""
                                errMsg = "#2匯入.xlsx 共 " + rowCount + " 列 " + columnCount + " 行 (若沒看到，可能填寫了不可見字元)，" +
                                    "第" + lineNo + "筆(Excel 第" + (lineNo + 1) + "列) 第1欄為空的，中斷匯入。\\n若資料已全部匯入，可忽略此問題。";
                                Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('" + errMsg + "');</script>");
                                return;
                            } // if
                        } // if (lineNo != 1)
                    } // foreach (Row r in sheetData.Elements<Row>())
                } // using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(openFilename, false))

                //string url = "/List.aspx?Ref1=" + Request.QueryString["Ref1"].ToString() + "&Ref2=" + Request.QueryString["Ref2"].ToString() + "&Ref3=" + Request.QueryString["Ref3"].ToString();
                string url = "#";
                errMsg = "#3匯入.xlsx 共 " + rowCount + " 列 " + columnCount + " 行 (若沒看到，可能填寫了不可見字元)，" +
                                    "成功匯入" + successCount + "筆資料。\\n" +
                                    "按【確定】轉往列表畫面。";
                
                ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert('" + errMsg + "');location.href='" + url + "';</script>");
            } //if (FileUpload1.HasFile)

            GridView1.DataBind();
        }
        protected string getOpenXMLSDKCellValue(WorkbookPart workbookPart, Cell currentcell)
        {
            string currentcellvalue = "";
            if (currentcell.DataType != null)
            {
                // Excel「儲存格格式」為「通用格式」，輸入非數字、非日期文字，DataType == CellValues.SharedString
                // Excel「儲存格格式」為「通用格式」，輸入日期，DataType == CellValues.SharedString
                if (currentcell.DataType == CellValues.SharedString)
                {
                    int id = -1;

                    if (Int32.TryParse(currentcell.InnerText, out id))
                    {
                        SharedStringItem item = workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(id);

                        if (item.Text != null)
                        {
                            //code to take the string value  
                            currentcellvalue = item.Text.Text;
                        }
                        else if (item.InnerText != null)
                        {
                            currentcellvalue = item.InnerText;
                        }
                        else if (item.InnerXml != null)
                        {
                            currentcellvalue = item.InnerXml;
                        }
                    }
                }
                //if (currentcell.DataType == CellValues.Number)
                //{
                //    currentcellvalue = currentcell.InnerText;
                //}
                //if (currentcell.DataType == CellValues.Date)
                //{
                //    //if (DateTime.TryParse(currentcell.InnerText, out DateTime currentcellDateTime))
                //    if (double.TryParse(currentcell.InnerText, out double currentcellDateTime))
                //    {
                //        //return System.DateTime.FromOADate(currentcellDateTime);
                //        return System.DateTime.FromOADate(currentcellDateTime).ToString();
                //    }
                //}
            }
            else
            {
                //「儲存格格式」為「通用格式」，輸入數字，DataType 會為 null
                currentcellvalue = currentcell.InnerText;
                //currentcellvalue = currentcell.CellValue.ToString();
            }

            return currentcellvalue;
        }

    }
}

(完)

浮雲雅築

2021年2月4日星期四

[研究][ASP.NET]使用 OpenXML SDK 2.12.1 讀取匯入 .xlsx 到資料庫

沒有留言:

張貼留言

2021年2月4日 星期四

[研究][ASP.NET]使用 OpenXML SDK 2.12.1 讀取匯入 .xlsx 到資料庫

沒有留言:

張貼留言

2021年2月4日星期四