浮雲雅築: [研究][C#][ASP.NET][WebForm] 使用 Microsoft OpenXML SDK 2.10.1 ( DocumentFormat.OpenXml ) 把 Excel .xlsx 匯入

[研究][C#][ASP.NET][WebForm] 使用 Microsoft OpenXML SDK 2.10.1 ( DocumentFormat.OpenXml ) 把 Excel .xlsx 匯入

2020-04-25
2020-04-27 更新

Open XML SDK 2.5 for Microsoft Office
https://www.microsoft.com/en-us/download/details.aspx?id=30425

微軟官方下載只提供到 2.5 版，NuGet 才能下載到更新版。

**********

另外 OpenXML 在不同版本時，API function method 用法不完全相同。

Welcome to the Open XML SDK 2.5 for Office
2017/11/01
https://docs.microsoft.com/zh-tw/office/open-xml/open-xml-sdk

DocumentFormat.OpenXml 2.7.2
https://docs.microsoft.com/zh-tw/dotnet/api/documentformat.openxml.spreadsheet?view=openxml-2.7.2

DocumentFormat.OpenXml 2.8.1
https://docs.microsoft.com/zh-tw/dotnet/api/documentformat.openxml.spreadsheet?view=openxml-2.8.1

本文使用下面工具

Visual Studio Enterprise 2019 v16.5.4
NuGet 安裝 OpenXML 2.10. 1 (DocumentFormat.OpenXml)

Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="OpenXMLExportTest.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 />
&nbsp;<asp:Button ID="Button_Import_by_OpenXML" runat="server" Text="Import" OnClick="Button_Import_by_OpenXML_Click" />
            <br />
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
                ConnectionString="<%$ ConnectionStrings:TestDBConnectionString %>" 
                SelectCommand="SELECT * FROM [MyTable]"></asp:SqlDataSource>
            <br />
            <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"

                AutoGenerateColumns="False" DataKeyNames="SN" DataSourceID="SqlDataSource1">
                <Columns>
                    <asp:BoundField DataField="SN" HeaderText="SN" InsertVisible="False" ReadOnly="True" SortExpression="SN" />
                    <asp:BoundField DataField="cname" HeaderText="cname" SortExpression="cname" Visible="true" />
                    <%--<asp:BoundField DataField="addr" HeaderText="addr" SortExpression="addr" />--%>
                    <asp:TemplateField HeaderText="addr" SortExpression="addr">
                        <EditItemTemplate>
                            <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("addr") %>'></asp:TextBox>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label ID="Label1" runat="server" Text='<%# Bind("addr") %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                </Columns>
            </asp:GridView>
            <asp:Label ID="Label_MSG1" runat="server"></asp:Label>
        </div>
    </form>
</body>
</html>

Default.axpx.cs

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Configuration;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Web.Configuration;
using System.Web.UI.WebControls;

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

        }
        protected void Button_Import_by_OpenXML_Click(object sender, EventArgs e)

        {
            Label_MSG1.Text = "";
            string queryString = "";
            bool importSuccess = true;
            if (FileUpload1.HasFile)
            {
                //string fd = (string)ConfigurationManager.AppSettings["InformationSystemFiles"] + "\\";
                string fd = @"D:\Production\TempImageFiles";
                if (!System.IO.Directory.Exists(fd))
                {
                    System.IO.Directory.CreateDirectory(fd);
                }
                string extFilename = System.IO.Path.GetExtension(FileUpload1.FileName).ToLower();
                if (extFilename != ".xlsx")
                {
                    Label_MSG1.Text = "請上傳副檔名.xlsx檔案";
                    //ScriptManager.RegisterStartupScript(this, GetType(), "Popup1", "erroralert('" + "請上傳副檔名.xlsx檔案！" + "');", true);
                    return;
                }
                //string FileName = fd + ID + System.IO.Path.GetExtension(FileUpload1.FileName).ToLower();
                //string fileName = "D:\\Production\\TempImageFiles\\" + System.IO.Path.GetExtension(FileUpload1.FileName).ToLower();
                string fileName = "D:\\Production\\TempImageFiles\\" + FileUpload1.FileName;
                FileUpload1.SaveAs(fileName);

                //using (SpreadsheetDocument doc = SpreadsheetDocument.Open(@"F:\OpenXml.xlsx", false))
                try
                {
                    using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fileName, false))
                    {
                        WorkbookPart wbPart = doc.WorkbookPart;
                        Sheet mysheet = (Sheet)doc.WorkbookPart.Workbook.Sheets.ChildElements.FirstOrDefault();
                        Worksheet worksheet = ((WorksheetPart)wbPart.GetPartById(mysheet.Id)).Worksheet;
                        SheetData sheetData = (SheetData)worksheet.ChildElements.FirstOrDefault();
                        int i = 0;
                        foreach (var row in sheetData.ChildElements)
                        {
                            //foreach (var cell in (row as Row).ChildElements)
                            //{
                            //    var cellValue = (cell as Cell).CellValue;
                            //    if (cellValue != null)
                            //    {
                            //        Console.WriteLine(cellValue.Text);
                            //    }
                            //}
                            // 跳過標題列
                            if (i == 0)
                            {
                                i++;
                                continue;
                            }
                            string SN = ((Cell)row.ChildElements[0]).CellValue.Text;
                            string cname = ((Cell)row.ChildElements[1]).CellValue.Text;
                            string addr = ((Cell)row.ChildElements[2]).CellValue.Text;
                            // SN 不匯入，不管了
                            //　檢查 cname
                            if (string.IsNullOrEmpty(cname))
                            {
                                Label_MSG1.Text = "cname不可空";
                                return;
                            }
                            //　檢查「年份」
                            if (string.IsNullOrEmpty(addr))
                            {
                                Label_MSG1.Text = "addr不可空";
                                return;
                            }
                            //----
                            queryString = queryString + @"INSERT INTO [dbo].[MyTable] ([cname],[addr])
                                VALUES (N'" + cname + "',N'" + addr + "'); ";
                        } // foreach

                        using (SqlConnection connection = new SqlConnection(

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

                            GridView1.DataSourceID = "";
                            GridView1.DataSourceID = "SqlDataSource1";
                            GridView1.DataBind();
                            Label_MSG1.ForeColor = System.Drawing.Color.Green;
                            Label_MSG1.Text = "匯入成功！";
                            Page.ClientScript.RegisterStartupScript(Page.GetType(), "message",

                                "<script language='javascript' defer>alert('匯入成功！');</script>");

                        } // using
                    } // using
                }
                catch (Exception ex)
                {
                    //throw;
                    if (ex != null)
                    {
                        Label_MSG1.Text = ex.Message.ToString();
                    }
                    else
                    {
                        Label_MSG1.Text = "匯入失敗！";
                        Page.ClientScript.RegisterStartupScript(Page.GetType(), "message",

                            "<script language='javascript' defer>alert('匯入失敗！');</script>");
                    }
                    Label_MSG1.ForeColor = System.Drawing.Color.Red;
                    importSuccess = false;
                }
            } //if (FileUpload1.HasFile)
            else
            {
                Label_MSG1.Text = "請選擇.xlsx檔案";
                Page.ClientScript.RegisterStartupScript(Page.GetType(), "message",

                    "<script language='javascript' defer>alert('請選擇.xlsx檔案！');</script>");
                return;
            }
        }
    }
}

日期欄位檢查方式

//----
// 檢查「購置日期」 myDate

// 檢查「日期」格式 YYYY/MM/DD，但 YYYY/M/D、YYYY/MM/D、YYYY/M/DD 不行
//string regularExpressions = @"^([2][0]\d{2}\/([0]\d|[1][0-2])\/([0-2]\d|[3][0-1]))$|^([2][0]\d{2}\/([0]\d|[1][0-2])\/([0-2]\d|[3][0-1]))$";
//(19 | 20)[0 - 9]{ 2}[- /.] (0[1-9]|1[012])[- /.] (0[1-9]|[12] [0-9]|3[01])

// YYYY/MM/DD、YYYY/MM/D、YYYY/M/DD、YYYY/M/D 都可以，但 9999/9/99 會過
// 「2020/3/1 上午 12:00:00」實際測試也會過 ( Why ?) DateTime.TryParse 得到相同值；
string regularExpressions = @"^(\d{4}(?:/\d{1,2}){2})";
Match m = Regex.Match(myDate, regularExpressions);

if (m.Success)
{
 DateTime temp;
 if (DateTime.TryParse(myDate, out temp))
 {
  //myDate=temp.ToString("yyyy/MM/dd HH:mm:ss");
  // Excel 儲存格格式若為【日期】，ClosedXML 對「2020/3/1」回傳「2020/3/1 上午 00:00:00」
  // Excel 儲存格格式若為【文字】，ClosedXML 對「2020/3/1」回傳「2020/3/1」
  // 插入 SQL Server 會出現【從字元字串轉換成日期及/或時間時，轉換失敗。】錯誤，要想辦拿拿掉【上午】兩字
  myDate = temp.ToString("yyyy/MM/dd");
 }
 else
 {
  Label_ExportImportMSG.Text = "<font color=red>第 " + lineNo.ToString() + "行，「購置日期」您輸入為「" + myDate + "」，日期不合理。</font>";
  return;
 }
 //return true;
}
else
{
 Label_ExportImportMSG.Text = "<font color=red>第 " + lineNo.ToString() + "行，「購置日期」您輸入為「" + myDate +

     "」，格式不符 YYYY/MM/DD 或 YYYY/MM/D 或 YYYY/M/DD 或 YYYY/MM/DD。</font>";
 return;
}
//----

(完)

相關文章

[研究][C#][ASP.NET][WebForm] 使用 Microsoft OpenXML SDK 2.10.1 ( DocumentFormat.OpenXml ) 把 Excel .xlsx 匯入DocumentFormat.OpenXml, OpenXML
https://shaurong.blogspot.com/2020/04/caspnetwebform-microsoft-openxml-sdk.html

[研究][C#][ASP.NET][WebForm] 使用 OpenXML 2.10.1 (DocumentFormat.OpenXml) 把 GridView 匯出成 .xlsxDocumentFormat.OpenXml, OpenXML
https://shaurong.blogspot.com/2020/04/caspnetwebform-openxml-2101.html

浮雲雅築

2020年4月25日星期六

[研究][C#][ASP.NET][WebForm] 使用 Microsoft OpenXML SDK 2.10.1 ( DocumentFormat.OpenXml ) 把 Excel .xlsx 匯入

沒有留言:

張貼留言

2020年4月25日 星期六

[研究][C#][ASP.NET][WebForm] 使用 Microsoft OpenXML SDK 2.10.1 ( DocumentFormat.OpenXml ) 把 Excel .xlsx 匯入

沒有留言:

張貼留言

2020年4月25日星期六