2020年4月27日 星期一

[研究][C#][ASP.NET][WebForm] GridView1.Columns.Count 和 GridView1.HeaderRow.Cells.Count 測試

[研究][C#][ASP.NET][WebForm] GridView1.Columns.Count 和 GridView1.HeaderRow.Cells.Count 測試

2020-04-27
2020-04-28 更新

GridView1.Columns.Count 是「非自動生成列數」。不管是否為 Visible。
GridView1.HeaderRow.Cells.Count 是「自動生成列數」和「非自動生成列數」的總和。不管是否為 Visible。
GridView1 會先顯示「非自動生成」的欄位,再顯示「自動生成」的欄位。

GridView1.Visible = false; 時,GridView1.HeaderRow.Cells[i].Visible 總是會 false。
GridView1.Visible = true; 時,GridView1.HeaderRow.Cells[i].Visible 總是會 true。
GridView1.Columns[i].Visible 才能判斷該欄位是否為 true 或 false。

Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="WebApplication3.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: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" 
                Caption="GridView1:非自動生成列"
                AutoGenerateColumns="False" DataKeyNames="SN" DataSourceID="SqlDataSource1">
                <Columns>
                    <asp:BoundField DataField="SN" HeaderText="SN" InsertVisible="False" ReadOnly="True" SortExpression="SN" />
                    <asp:BoundField DataField="myID" HeaderText="myID" SortExpression="myID" />
                    <asp:BoundField DataField="myName" HeaderText="myName" SortExpression="myName" />
                    <asp:BoundField DataField="purchaseDate" HeaderText="purchaseDate" SortExpression="purchaseDate" />
                </Columns>
            </asp:GridView>
            <br />
            <asp:GridView ID="GridView2" runat="server" AllowPaging="True" AllowSorting="True" 
                Caption="GridView2:自動生成列"
                AutoGenerateColumns="true" DataKeyNames="SN" DataSourceID="SqlDataSource1">
            </asp:GridView>
            <br />
            <asp:GridView ID="GridView3" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" 
                Caption="GridView3:非自動生成列"
                DataKeyNames="SN" DataSourceID="SqlDataSource1" Visible="false">
                <Columns>
                    <asp:BoundField DataField="SN" HeaderText="SN" InsertVisible="False" ReadOnly="True" SortExpression="SN" />
                    <asp:BoundField DataField="myID" HeaderText="myID" SortExpression="myID" />
                    <asp:BoundField DataField="myName" HeaderText="myName" SortExpression="myName" />
                    <asp:BoundField DataField="purchaseDate" HeaderText="purchaseDate" SortExpression="purchaseDate" />
                </Columns>
            </asp:GridView>
            <br />
            <asp:GridView ID="GridView4" runat="server" AllowPaging="True" AllowSorting="True" 
                Caption="GridView4:非自動生成列"
                AutoGenerateColumns="true" DataKeyNames="SN" DataSourceID="SqlDataSource1" Visible="false">
            </asp:GridView>
            <br />
            <asp:GridView ID="GridView5" runat="server" AllowPaging="True" AllowSorting="True" 
                Caption="GridView5:混合自動生成列"
                AutoGenerateColumns="true" DataKeyNames="SN" DataSourceID="SqlDataSource1" Visible="false">
                <Columns>
                    <asp:BoundField DataField="SN" HeaderText="SN" InsertVisible="False" ReadOnly="True" SortExpression="SN" />
                    <asp:BoundField DataField="SN" HeaderText="SN" InsertVisible="False" ReadOnly="True" SortExpression="SN" Visible="false" />
                    <%--<asp:BoundField DataField="SN" HeaderText="SN" InsertVisible="False" ReadOnly="True" SortExpression="SN" />--%>
                </Columns>
            </asp:GridView>
            <br />
            <asp:GridView ID="GridView6" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" 
                Caption="GridView6:非自動生成列"
                DataKeyNames="SN" DataSourceID="SqlDataSource1" Visible="false">
                <Columns>
                    <asp:BoundField DataField="SN" HeaderText="SN" InsertVisible="False" ReadOnly="True" SortExpression="SN" />
                    <asp:BoundField DataField="myID" HeaderText="myID" SortExpression="myID" Visible="false" />
                    <asp:BoundField DataField="myName" HeaderText="myName" SortExpression="myName" />
                    <asp:BoundField DataField="purchaseDate" HeaderText="purchaseDate" SortExpression="purchaseDate" />
                </Columns>
            </asp:GridView>
            <br />
            <asp:GridView ID="GridView7" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" 
                Caption="GridView7:非自動生成列"
                DataKeyNames="SN" DataSourceID="SqlDataSource1" Visible="true">
                <Columns>
                    <asp:BoundField DataField="SN" HeaderText="SN" InsertVisible="False" ReadOnly="True" SortExpression="SN" />
                    <asp:BoundField DataField="myID" HeaderText="myID" SortExpression="myID" Visible="false" />
                    <%--<asp:BoundField DataField="myName" HeaderText="myName" SortExpression="myName" />--%>
                    <asp:BoundField DataField="purchaseDate" HeaderText="purchaseDate" SortExpression="purchaseDate" />
                </Columns>
            </asp:GridView>
            <br />
            <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Button" />
            <br />
            <br />
            GridView1.Columns.Count 是「非自動生成列數」。不管是否為 Visible。<br />
            GridView1.HeaderRow.Cells.Count 是「自動生成列數」和「非自動生成列數」的總和。不管是否為 Visible。<br />
            <br />
            GridView1.Visible = false; 時,GridView1.HeaderRow.Cells[i].Visible 總是會 false。<br />
            <br />
            <asp:Label ID="Label1" runat="server" ></asp:Label><br />
            <br />
        </div>
    </form>
</body>
</html>


Default.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

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

        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            Label1.Text = "";
            Label1.Text = Label1.Text + "GridView1.HeaderRow.Cells.Count : " + GridView1.HeaderRow.Cells.Count + "<br />";
            Label1.Text = Label1.Text + "GridView2.HeaderRow.Cells.Count : " + GridView2.HeaderRow.Cells.Count + "<br />";
            Label1.Text = Label1.Text + "GridView3.HeaderRow.Cells.Count : " + GridView3.HeaderRow.Cells.Count + "<br />";
            Label1.Text = Label1.Text + "GridView4.HeaderRow.Cells.Count : " + GridView4.HeaderRow.Cells.Count + "<br />";
            Label1.Text = Label1.Text + "GridView5.HeaderRow.Cells.Count : " + GridView5.HeaderRow.Cells.Count + "<br />";
            Label1.Text = Label1.Text + "GridView6.HeaderRow.Cells.Count : " + GridView6.HeaderRow.Cells.Count + "<br />";
            Label1.Text = Label1.Text + "GridView7.HeaderRow.Cells.Count : " + GridView7.HeaderRow.Cells.Count + "<br />";

            Label1.Text = Label1.Text + "GridView1.Columns.Count : " + GridView1.Columns.Count + "<br />";
            Label1.Text = Label1.Text + "GridView2.Columns.Count : " + GridView2.Columns.Count + "<br />";
            Label1.Text = Label1.Text + "GridView3.Columns.Count : " + GridView3.Columns.Count + "<br />";
            Label1.Text = Label1.Text + "GridView4.Columns.Count : " + GridView4.Columns.Count + "<br />";
            Label1.Text = Label1.Text + "GridView5.Columns.Count : " + GridView5.Columns.Count + "<br />";
            Label1.Text = Label1.Text + "GridView6.Columns.Count : " + GridView6.Columns.Count + "<br />";
            Label1.Text = Label1.Text + "GridView7.Columns.Count : " + GridView7.Columns.Count + "<br />";
        }
    }
}

(完)

[研究][C#][ASP.NET][WebForm] ClosedXML 匯入錯誤:參數名稱: The range 工作表1!A1:L2 is already part of table 'Table1'

[研究][C#][ASP.NET][WebForm] ClosedXML 匯入錯誤:參數名稱: The range 工作表1!A1:L2 is already part of table 'Table1' 

2020-04-27


range
參數名稱: The range 工作表1!A1:L2 is already part of table 'Table1'
 描述: 在執行目前 Web 要求的過程中發生未處理的例外狀況。請檢閱堆疊追蹤以取得錯誤的詳細資訊,以及在程式碼中產生的位置。

 例外狀況詳細資訊: System.ArgumentException: range
參數名稱: The range 工作表1!A1:L2 is already part of table 'Table1'

原始程式錯誤:



行 858:                    var firstCell = workSheet.FirstCellUsed();
行 859:                    var lastCell = workSheet.LastCellUsed();
行 860:                    dt = workSheet.Range(firstCell.Address, lastCell.Address).AsTable().AsNativeDataTable();
行 861:                }
行 862:

經查,如果是 ClosedXML 0.94.2 或 0.95.2 匯出的 .xlsx
匯入時,在 AsTable() 這邊會出錯;

但若為  OpenXML ( DocumentFormat.OpenXml ) 2.10.1 匯出的,匯入不會有問題。

要改寫成下面方式,ClosedXML 才能讀取。

using (XLWorkbook workBook = new XLWorkbook(fileName))
{
 IXLWorksheet workSheet = workBook.Worksheet("工作表1");
 // 讀取第一個 Sheet
 //IXLWorksheet worksheet = workbook.Worksheet(1);
 //var worksheet = workbook.Worksheets.First();

 var firstCell = workSheet.FirstCellUsed();
 var lastCell = workSheet.LastCellUsed();

 var range = workSheet.Range(firstCell.Address, lastCell.Address);

 for (int i=2; i <= range.RowCount(); i++)
 {
  // row =1 是標題列;row=2 才開始是資料列
  lineNo = i;
  string sn = range.Cell(i, 1).Value.ToString();
  string cName = range.Cell(i, 2).Value.ToString();
  string addr = range.Cell(i, 3).Value.ToString();
 }
} 

(完)

2020年4月25日 星期六

[研究][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


[研究][C#][ASP.NET][WebForm] 使用 OpenXML 2.10.1 (DocumentFormat.OpenXml) 把 GridView 匯出成 .xlsx

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

2020-04-25
2020-04-27 整個 Code 大改 (因為 「非自動生成列」會比「自動生成列」、以及欄位是否 visible 問題)
2020-04-28 匯出檔名強化

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="WebApplication4.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:SqlDataSource ID="SqlDataSource1" runat="server" 
                ConnectionString="<%$ ConnectionStrings:TestDBConnectionString %>" 
                SelectCommand="SELECT * FROM [MyTable]"></asp:SqlDataSource>
            <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" 
                AutoGenerateColumns="True" DataKeyNames="SN" DataSourceID="SqlDataSource1">
                <Columns>
                    <asp:BoundField DataField="SN" HeaderText="SN" InsertVisible="False" ReadOnly="True" SortExpression="SN" />
                    <asp:BoundField DataField="myID" HeaderText="myID" SortExpression="myID" Visible="false" />
                    <%--<asp:BoundField DataField="myName" HeaderText="myName" SortExpression="myName" />--%>
                    <asp:BoundField DataField="purchaseDate" HeaderText="purchaseDate" SortExpression="purchaseDate" />
                </Columns>
            </asp:GridView>
            <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" /><br />
            <br />
            <asp:GridView ID="GridView2" runat="server" AllowPaging="True" AllowSorting="True" 
                Visible="false"
                AutoGenerateColumns="True" DataKeyNames="SN" DataSourceID="SqlDataSource1">
                <Columns>
                    <asp:BoundField DataField="SN" HeaderText="SN" InsertVisible="False" ReadOnly="True" SortExpression="SN" />
                    <asp:BoundField DataField="myID" HeaderText="myID" SortExpression="myID" Visible="false" />
                    <%--<asp:BoundField DataField="myName" HeaderText="myName" SortExpression="myName" />--%>
                    <asp:BoundField DataField="purchaseDate" HeaderText="purchaseDate" SortExpression="purchaseDate" />
                </Columns>
            </asp:GridView>
            <asp:Button ID="Button2" runat="server" Text="Button" OnClick="Button2_Click" /><br />
            <asp:Label ID="Label1" runat="server"></asp:Label>
        </div>
    </form>
</body>
</html>







Default.aspx.cs


using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

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

        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            //不想匯出的列,可以隱藏掉
            //GridView1.Columns[0].Visible = false;
            GridViewExportToXlsxByOpenXML(GridView1);
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            GridViewExportToXlsxByOpenXML(GridView2);
        }
        private string GridViewExportToXlsxByOpenXML(GridView gridview)
        {
            // Import
            // https://docs.microsoft.com/en-us/office/open-xml/how-to-parse-and-read-a-large-spreadsheet

            // Export
            // https://docs.microsoft.com/en-us/office/open-xml/how-to-create-a-spreadsheet-document-by-providing-a-file-name

            //Insert text into a cell in a spreadsheet document(Open XML SDK)
            //https://docs.microsoft.com/zh-tw/office/open-xml/how-to-insert-text-into-a-cell-in-a-spreadsheet
            string year = DateTime.Now.Year.ToString();
            string mainFileName = Session["jiguanname"].ToString() + "(" + Session["oid"].ToString() + ")" + year + "年設備清冊";

            //DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xlsx";
            // 使用者若很多,最好連 fff 也用上
            //DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff") + ".xlsx";

            // 若檔案名稱有中文字或特殊符號,Response.AddHeader() 輸出檔案名稱要編碼
            string outFileName = HttpUtility.UrlEncode(mainFileName, System.Text.Encoding.UTF8) + "-" +
                DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff") + ".xlsx";

            // XLWorkbook.SaveAs() 輸出檔案名稱不要用 HttpUtility.UrlEncode 編碼
            // Response.TransmitFile() 輸出檔案名稱不要用 HttpUtility.UrlEncode 編碼
            string dirFileName = @"D:\Production\TempImageFiles\" + mainFileName + "-" +
                DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff") + ".xlsx";
            try
            {
                GridView gv = gridview;

                bool oldAllowPaging = gv.AllowPaging;
                bool oldAllowSorting = gv.AllowSorting;
                gv.AllowSorting = false; // 支援排序時,標題不是文字,要改抓 LinkButton 的文字,麻煩,不如暫時取消
                gv.AllowPaging = false; // 分頁狀態下,只能抓到當頁的10筆,必須取消分頁
                gv.DataBind();

                SpreadsheetDocument spreadsheetDocument = null;
                Response.Clear();
                Response.Buffer = true;
                Response.Charset = "";
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                //Response.AddHeader("content-disposition", "attachment;filename=Export.xlsx");
                Response.AddHeader("content-disposition", "attachment;filename=" + outFileName );
                using (MemoryStream MyMemoryStream = new MemoryStream())
                {
                    spreadsheetDocument = SpreadsheetDocument.Create(MyMemoryStream, SpreadsheetDocumentType.Workbook, true);
                    WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
                    workbookpart.Workbook = new Workbook();
                    // Add a WorksheetPart to the WorkbookPart.
                    WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                    worksheetPart.Worksheet = new Worksheet(new SheetData());

                    // Add Sheets to the Workbook.
                    Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild(new Sheets());
                    // Append a new worksheet and associate it with the workbook.
                    Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "工作表1" };
                    sheets.Append(sheet);
                    // Get the sheetData cell table.
                    SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
                    // Add a column row to the cell table.
                    Row colrow;
                    colrow = new Row() { RowIndex = 1 };
                    sheetData.Append(colrow);

                    int visibleIndex = 0;
                    // 標題純文字這樣抓
                    //GridView1.Columns.Count 是「非自動生成列數」。不管是否為 Visible。
                    //GridView1.HeaderRow.Cells.Count 是「自動生成列數」和「非自動生成列數」的總和。不管是否為 Visible。
                    //GridView1.Visible = false; 時,GridView1.HeaderRow.Cells[i].Visible 總是會 false。

                    // 「非自動生成列」會比「自動生成列」先顯示
                    // HeaderRow.Cells[i].Visible 不管是 true 或 false,回傳都 true
                    // gv.Columns[i].Visible 才回真正回傳 true 或 false
                    // 但是 gv.Columns[i] 只能抓「非自動生成列數」

                    for (int i = 0; i < gv.HeaderRow.Cells.Count; i++)
                    {
                        if (i < gv.Columns.Count)
                        {
                            if (gv.Columns[i].Visible == true)
                            {
                                string columnName = gv.Columns[i].HeaderText;
                                Cell CReceiptID = new Cell();
                                CReceiptID.CellValue = new CellValue(columnName);
                                CReceiptID.DataType = new EnumValue<CellValues>(CellValues.String);
                                colrow.InsertAt(CReceiptID, visibleIndex);
                                visibleIndex++;
                            }
                        }
                        else
                        {
                            // GridView1.Visible = true  時,gv.HeaderRow.Cells[i].Visible 也一定 true
                            // GridView1.Visible = false 時,gv.HeaderRow.Cells[i].Visible 也一定 false
                            // 若想 GridView1.Visible 為 false 時也一定匯出,下面 if 要註解掉
                            //if (gv.HeaderRow.Cells[i].Visible == true)
                            //{
                                string columnName = gv.HeaderRow.Cells[i].Text;

                                Cell CReceiptID = new Cell();
                                CReceiptID.CellValue = new CellValue(columnName);
                                CReceiptID.DataType = new EnumValue<CellValues>(CellValues.String);
                                colrow.InsertAt(CReceiptID, visibleIndex);
                                visibleIndex++;
                            //}
                        }
                    }

                    // 資料列這樣抓
                    // Add rows to the cell table
                    UInt32Value DataIndex = new UInt32Value();
                    DataIndex.Value = 2;

                    for (int i = 0; i < gv.Rows.Count; i++)
                    {
                        Row row;
                        row = new Row() { RowIndex = DataIndex.Value };
                        sheetData.Append(row);
                        visibleIndex = 0;

                        for (int j = 0; j < gv.HeaderRow.Cells.Count; j++)
                        {
                            if (j < gv.Columns.Count)
                            {
                                if (gv.Columns[j].Visible == true)
                                {
                                    // 未執行【將這個欄位轉為 TemplateField】的欄位這樣就可抓到
                                    string dataText = gv.Rows[i].Cells[j].Text;

                                    // 執行【將這個欄位轉為 TemplateField】的欄位要根據情況改成下面
                                    if (gv.Rows[i].Cells[j].HasControls())
                                    {
                                        for (int k = 0; k < gv.Rows[i].Cells[j].Controls.Count; k++)
                                        {
                                            if (gv.Rows[i].Cells[j].Controls[k] is Label)
                                            {
                                                Label labelControl = gv.Rows[i].Cells[j].Controls[k] as Label;
                                                //Label labelControl = (Label)gv.Rows[i].Cells[j].FindControl("Label1");
                                                if (labelControl != null)
                                                {
                                                    string labelText = labelControl.Text;
                                                    dataText = labelText;
                                                }
                                            }
                                        }
                                    }
                                    dataText = dataText.Replace("&nbsp;", "");

                                    Cell CellReceiptID = new Cell();
                                    CellReceiptID.CellValue = new CellValue(dataText);
                                    //CellReceiptID.DataType = new EnumValue<CellValues>(CellValues.Number);
                                    CellReceiptID.DataType = new EnumValue<CellValues>(CellValues.String);
                                    row.InsertAt(CellReceiptID, visibleIndex);
                                    visibleIndex++;
                                }
                            }
                            else
                            {
                                // GridView1.Visible = true  時,gv.HeaderRow.Cells[i].Visible 也一定 true
                                // GridView1.Visible = false 時,gv.HeaderRow.Cells[i].Visible 也一定 false
                                // 若想 GridView1.Visible 為 false 時也一定匯出,下面 if 要註解掉
                                //if (gv.HeaderRow.Cells[j].Visible == true)
                                //{
                                    // 未執行【將這個欄位轉為 TemplateField】的欄位這樣就可抓到
                                    string dataText = gv.Rows[i].Cells[j].Text;

                                    // 執行【將這個欄位轉為 TemplateField】的欄位要根據情況改成下面
                                    if (gv.Rows[i].Cells[j].HasControls())
                                    {
                                        for (int k = 0; k < gv.Rows[i].Cells[j].Controls.Count; k++)
                                        {
                                            if (gv.Rows[i].Cells[j].Controls[k] is Label)
                                            {
                                                Label labelControl = gv.Rows[i].Cells[j].Controls[k] as Label;
                                                //Label labelControl = (Label)gv.Rows[i].Cells[j].FindControl("Label1");
                                                if (labelControl != null)
                                                {
                                                    string labelText = labelControl.Text;
                                                    dataText = labelText;
                                                }
                                            }
                                        }
                                    }
                                    dataText = dataText.Replace("&nbsp;", "");
Cell CellReceiptID = new Cell(); CellReceiptID.CellValue = new CellValue(dataText); //CellReceiptID.DataType = new EnumValue<CellValues>(CellValues.Number); CellReceiptID.DataType = new EnumValue<CellValues>(CellValues.String); row.InsertAt(CellReceiptID, visibleIndex); visibleIndex++; //} // if } // else } // for (int j = 0; j < gv.HeaderRow.Cells.Count; j++) DataIndex.Value++; } // for (int i = 0; i < gv.Rows.Count; i++) worksheetPart.Worksheet.Save(); workbookpart.Workbook.Save(); spreadsheetDocument.Close(); MyMemoryStream.WriteTo(Response.OutputStream); Response.Flush(); Response.End(); } gv.AllowPaging = oldAllowPaging; gv.AllowSorting = oldAllowSorting; gv.DataBind(); } catch (Exception ex) { Label1.ForeColor = System.Drawing.Color.Red; string exMsg = ""; if (ex != null) { exMsg = ex.ToString(); } else { exMsg = "不明錯誤。"; } Label1.Text = exMsg;
                Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('" + exMsg + "');</script>");
                return exMsg;
            }
            Label_MSG1.ForeColor = System.Drawing.Color.Green;
            Label_MSG1.Text = "匯出成功。";
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('匯出成功。');</script>"); return "OK"; } } }


匯出後,用 Microsoft Excel 2016 開啟正常,沒有警告或錯誤。

(完)

相關文章

[研究][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月21日 星期二

[研究] [C#] [ASP.NET] [JavaScript] 彈出視窗 (對話盒)

[研究] [C#] [ASP.NET] [JavaScript] 彈出視窗 (對話盒)

2017-06-13
2020-04-21 更新


  // 網頁不會空白
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('「檢核表」第 " + (i + 1).ToString() + " 行「辦理結果」沒填寫,送出失敗。');</script>");
               
// 網頁會空白
Page.ClientScript.RegisterClientScriptBlock(Page.GetType(), "message", "<script language='javascript' defer>alert('「檢核表」第 " + (i + 1).ToString() + " 行「辦理結果」沒填寫,送出失敗。');</script>"); 

//轉向
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('送出成功!');window.location='Default.aspx'</script>");


********************************************************************************
alert 警報對話盒視窗 

C#  + ASP.NET + JavaScript

alert 是 JavaScript 的指令

        protected void Button1_Click(object sender, EventArgs e)
        {
            Response.Write(@"<script language='javascript'>alert('Update is successful.')</script>");
        }

HTML + JavaScript

<form>
<input type="button" value="Pop-up an alert box" onClick="alert('Hi, I am alert box.')">
</form>




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

confirm 確認對話盒視窗

C#  + ASP.NET + JavaScript



<asp:LinkButton ID="LinkButton1" runat="server" CommandName="Delete" CssClass="glyphicon glyphicon-trash btn btn-danger btn-xs" OnClientClick='return confirm("確定刪除?")'>刪除</asp:LinkButton>


        protected void Button2_Click(object sender, EventArgs e)
        {
            Response.Write(@"<script language='javascript'>confirm('Are you sure?'); alert(reply)</script>");
        }

HTML + JavaScript

<form>
<input type="button" value="Pop-up an confirm box"
onClick="var reply = confirm('Are you sure?') ; alert(reply)">
</form>


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

prompt 提示輸入對話盒視窗

C#  + ASP.NET + JavaScript


        protected void Button3_Click(object sender, EventArgs e)
        {
            Response.Write(@"<script language='javascript'>prompt('What is your name?.', 'You name here')</script>");
        }


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

Window.open 開新網頁

C#  + ASP.NET + JavaScript


protected void Button4_Click(object sender, EventArgs e)
        {
            Response.Write(@"<script language='javascript'>window.open('http://www.yahoo.com', 'winname','location,menubar=1 status,toolbar,scrollbars=0')</script>");
            //window.open("http://www.yahoo.com", "winname","location,menubar=1 status,toolbar,scrollbars=0");
        }






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

Sweet Alert With asp.net C#
https://forums.asp.net/t/2079785.aspx?Sweet+Alert+With+asp+net+C+

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

【JS套件】將傳統window.alert 取代override成sweetalert 教學


<script type="text/javascript">
(function() {
  window.alert = function() {
    return swal.apply(this, arguments);
  };
})(window.alert);
</script>

http://loveplay6688.pixnet.net/blog/post/104687290-%E3%80%90js%E5%A5%97%E4%BB%B6%E3%80%91%E5%B0%87%E5%82%B3%E7%B5%B1window.alert-%E5%8F%96%E4%BB%A3override%E6%88%90sweetal

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

(待續)

相關

[研究] [C#] [ASP.NET] [JavaScript] 彈出視窗 (對話盒)
http://shaurong.blogspot.com/2020/04/c-aspnet-javascript.html

[研究] ASP.NET + SweetAlert 安裝 (NuGet)與試用
http://shaurong.blogspot.com/2017/06/aspnet-sweetalert-nuget.html

[研究] [C#] [ASP.NET] 用 SweetAlert + Button 作 送出確認 提示對話盒
http://shaurong.blogspot.com/2017/06/c-aspnet-sweetalert_23.html

[研究] [C#] [ASP.NET] 用 SweetAlert + LinkButton 作 刪除確認 提示對話盒
http://shaurong.blogspot.tw/2017/06/c-aspnet-sweetalert.html

JavaScruot - 視窗 (Window)
http://taiwantc.com/js/js_tut_b_window0.htm

ASP.NET 開新視窗四部曲
https://dotblogs.com.tw/hatelove/archive/2009/10/28/11325.aspx

http://lanfar.pixnet.net/blog/post/40706881

LinkButton 另開新視窗的方法
http://jimmy0222.pixnet.net/blog/post/36045311-linkbutton-%E5%8F%A6%E9%96%8B%E6%96%B0%E8%A6%96%E7%AA%97%E7%9A%84%E6%96%B9%E6%B3%95

showModalDialog與IE快顯封鎖
http://blog.darkthread.net/post-2011-07-12-showmodaldialog-and-popup-blocker.aspx

About the Pop-up Blocker
https://msdn.microsoft.com/en-us/library/ms537632(v=vs.85).aspx

<asp:LinkButton ID="LinkButton_View" runat="server" CausesValidation="False" Text="檢視" CssClass="btn btn-primary btn-xs" OnClientClick="<script>window.open('<%# String.Format(&quot;~/ManageJA/JAView.aspx?id={0}&quot;, Eval(&quot;Id&quot;)) %>','_blank','height=700,width=1000,status=yes,toolbar=no,menubar=no,location=no');</script>"></asp:LinkButton>

解決開視窗會變開在頁籤
https://dotblogs.com.tw/kim/2011/07/11/_blank

[C#]在 .Net 上實現 Win Form 中 MessageBox 的確認視窗
2009-10-09
https://dotblogs.com.tw/willy0080/2009/10/09/10984

ASP.NET中彈出訊息框的幾種常見方法
https://codertw.com/%E5%89%8D%E7%AB%AF%E9%96%8B%E7%99%BC/221190/

ASP.NET中提交按鈕彈出訊息框,頁面不空白
https://www.itread01.com/p/627079.html


2020年4月20日 星期一

[研究][C#][ASP.NET][WebForm] 用 ClosedXML 0.95.0 匯入 Excel (.xlsx) 檔案

[研究][C#][ASP.NET][WebForm] 用 ClosedXML 0.95.0 匯入 Excel (.xlsx) 檔案

2020-04-20
2020-04-24 修訂

Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="GridViewImportXlsxByClosedXML.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:FileUpload ID="FileUpload1" runat="server" /><br />
        <asp:Button ID="Button_Import_by_ClosedXML" runat="server" Text="匯入" OnClick="Button_Import_by_ClosedXML_Click" /><br />
            <asp:Label ID="Label_ExportImportMSG" runat="server"></asp:Label><br />
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TestDBConnectionString %>" 
            SelectCommand="SELECT * FROM [MyTable]"></asp:SqlDataSource>
        <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="myID" HeaderText="myID" SortExpression="myID" />
                <asp:BoundField DataField="myName" HeaderText="myName" SortExpression="myName" />
                <asp:BoundField DataField="purchaseDate" HeaderText="purchaseDate" SortExpression="purchaseDate" />
            </Columns>
        </asp:GridView>
    </form>
</body>
</html>



注意,下面連線字串名稱 TestDBConnectionString 要換成自己的。否則會出現【並未將物件參考設定為物件的執行個體。】錯誤。資料表 MyTable 和欄位名稱也要換自己的。

如果出現錯誤【字串或二進位資料會被截斷。】,可以一次1000筆太長,減少些。
如果改成1筆也這樣,表示欄位長度不夠放入資料,請改 DB Schema。

.xlsx 檔案第一橫列的【排序與篩選】功能必須關閉,存檔,再匯入,否則匯入會失敗。
否則/ using (XLWorkbook workBook = new XLWorkbook(fileName)) 開檔案會失敗。

Default.aspx.cs


using ClosedXML.Excel;
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text.RegularExpressions;
using System.Web.Configuration;

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

        }

        protected void Button_Import_by_ClosedXML_Click(object sender, EventArgs e)
        {
            Label_ExportImportMSG.Text = "";
            int lineNo = 2;
            string queryString = @"truncate  Table MyTable; 
                DBCC CHECKIDENT('MyTable', RESEED, 1); ";
            bool importSuccess = true;
            string myID = "";
            string myName = "";
            string purchaseDate = "";

            try
            {
                if (FileUpload1.HasFile)
                {
                    string fd = (string)ConfigurationManager.AppSettings["InformationSystemFiles"] + "\\";
                    if (!System.IO.Directory.Exists(fd))
                    {
                        System.IO.Directory.CreateDirectory(fd);
                    }
                    string extFileName = System.IO.Path.GetExtension(FileUpload1.FileName).ToLower();
                    // ClosedXML 0.94.2
                    // Extension 'csv' is not supported. Supported extensions are '.xlsx', '.xslm', '.xltx' and '.xltm'.
                    if (!(extFileName == ".xlsx"))
                    {
                        Label_ExportImportMSG.Text = "<font color=red>請上傳副檔名.xlsx檔案</font>";
                        //ScriptManager.RegisterStartupScript(this, GetType(), "Popup1", "erroralert('" + "請上傳副檔名.xlsx檔案!" + "');", true);
                        return;
                    }
                    string mainFileName = Path.GetFileNameWithoutExtension(FileUpload1.FileName);
                    string fileName = @"D:\\Production\\TempImageFiles\\" + mainFileName + "-" + DateTime.Now.ToString("-yyyy-MM-dd HH-mm-ss") + extFileName;
                    FileUpload1.SaveAs(fileName);

                    var dt = new DataTable();
                    using (XLWorkbook workBook = new XLWorkbook(fileName))
                    {
                        var workSheet = workBook.Worksheet("工作表1");
                        var firstCell = workSheet.FirstCellUsed();
                        var lastCell = workSheet.LastCellUsed();
                        dt = workSheet.Range(firstCell.Address, lastCell.Address).AsTable().AsNativeDataTable();
                    }

                    int i = 0;
                    foreach (DataRow item in dt.Rows)
                    {
                        myID = item[0].ToString().Trim();
                        myName = item[1].ToString().Trim();

                        // Excel 儲存格格式若為【日期】,「2020/3/1」回傳「2020/3/1 上午 00:00:00」
                        // Excel 儲存格格式若為【文字】,「2020/3/1」回傳「2020/3/1」
                        // 插入 SQL Server 會出現【從字元字串轉換成日期及/或時間時,轉換失敗。】錯誤,要想辦拿拿掉【上午】兩字
                        purchaseDate = item[2].ToString().Trim();

                        // 檢查「名稱」
                        if (string.IsNullOrEmpty(myName))
                        {
                            Label_ExportImportMSG.Text = "<font color=red>第 " + lineNo.ToString() + "行,「名稱」不可空</font>";
                            return;
                        }
                        if (myName.Length > 50)
                        {
                            Label_ExportImportMSG.Text = "<font color=red>第 " + lineNo.ToString() + "行,「名稱」您輸入為「" + myName + "」,長度超過50個字(全半形都算1個)。</font>";
                            return;
                        }
                        //----
                        // 檢查「購置日期」

                        // 檢查「日期」格式 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(purchaseDate, regularExpressions);

                        if (m.Success)
                        {
                            DateTime temp;
                            if (DateTime.TryParse(purchaseDate, out temp))
                            {
                                //PurchaseDate=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 會出現【從字元字串轉換成日期及/或時間時,轉換失敗。】錯誤,要想辦拿拿掉【上午】兩字
                                purchaseDate = temp.ToString("yyyy/MM/dd");
                            }
                            else
                            {
                                Label_ExportImportMSG.Text = "<font color=red>第 " + lineNo.ToString() + "行,「購置日期」您輸入為「" + purchaseDate + "」,日期不合理。</font>";
                                return;
                            }
                            //return true;
                        }
                        else
                        {
                            Label_ExportImportMSG.Text = "<font color=red>第 " + lineNo.ToString() + "行,「購置日期」您輸入為「" + purchaseDate + "」,格式不符 YYYY/MM/DD 或 YYYY/MM/D 或 YYYY/M/DD 或 YYYY/MM/DD。</font>";
                            return;
                        }
                        //----
                        queryString = queryString + @"
INSERT INTO [dbo].[MyTable]
           ([MyID]
           ,[MyName]
           ,[PurchaseDate]
            )
     VALUES
           ('" + myID + "',N'" +
           myName + "',N'" +
           purchaseDate + "'); ";

                        lineNo = lineNo + 1;
                    } // 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_ExportImportMSG.ForeColor = System.Drawing.Color.Green;
                        Label_ExportImportMSG.Text = "匯入成功!";
                    } // using
                }

                else
                {
                    Label_ExportImportMSG.Text = "<font color=red>請選擇.xlsx檔案</font>";
                    return;
                }//if (FileUpload1.HasFile)
            }
            catch (Exception ex)
            {
                if (ex != null)
                {
                    Label_ExportImportMSG.Text = ex.Message.ToString();
                    if (Label_ExportImportMSG.Text.Contains("找不到資料行"))
                    {
                        Label_ExportImportMSG.Text = Label_ExportImportMSG.Text + "請不要擅自刪除欄位,或自創.xlsx內容格式。";
                    }
                }
                else
                {
                    Label_ExportImportMSG.Text = "匯入失敗!";
                    Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('匯入失敗!');</script>");
                }
                Label_ExportImportMSG.ForeColor = System.Drawing.Color.Red;
                importSuccess = false;
            }
        }
    }
}

(完)

[研究][ASP.NET][WebForm] GridView 欄位內容文字中強迫自動換行、斷行

[研究][ASP.NET][WebForm] GridView 欄位內容文字中強迫自動換行、斷行

2020-04-20

protected void Page_Load(object sender, EventArgs e)
{
    //正常換行
    //GridView1.Attributes.Add("style", "word-break:keep-all;word-wrap:normal");

    //強迫字中自動換行
    //GridView1.Attributes.Add("style", "word-break:break-all;word-wrap:break-word");
}

強迫字中換行,但有最低寬度限制

<td style="word-break: break-all; min-width: 45px;">12345678901234567890123456789
1234567890123456789012345678900123456789012345678901234567890</td>

(完)

相關文章

[研究][ASP.NET][WebForm] GridView 欄位內容文字自動換行、斷行
https://shaurong.blogspot.com/2020/04/aspnetwebform-gridview.html

[研究] CSS li 不自動換行、斷行
https://shaurong.blogspot.com/2020/04/css-li.html

[研究][ASP.NET][WebForm] GridView 欄位標題列文字自動換行、斷行
https://shaurong.blogspot.com/2018/11/aspnet-webform-gridview.html

[研究] 用 CSS 的 word-break: break-word; 強迫自動換行、斷行
https://shaurong.blogspot.com/2017/07/css-word-break-break-word-td.html

[研究] CSS li 不自動換行、斷行

[研究] CSS  li 不自動換行、斷行

2019-04-18


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<style type="text/css">
    /* li 不換行,寬度根據內容自適應,通過 float 實現 */
    .ul_one {list-style: none;margin: 0px;padding: 0px;}
    .ul_one li {float: left;background-color: #ccd;margin: 0px 20px 10px 0px;}
   
    /* li 不換行,寬度固定 */
    .ul_two {list-style: none;margin: 0px;padding: 0px;}
    .ul_two li {float: left;width: 100px;margin: 0px 20px 10px 0px;background-color: #ccd;overflow: hidden; line-height:1.6em;}
   
    /* li 不換行,寬度根據內容自適應,通過 display:inline 實現 */
    .ul_three {list-style: none;margin: 0px;padding: 0px;}
    .ul_three li {display:inline;background-color: #ccd;margin: 0px 20px 10px 0px;}
   
    .clear {clear: both;}
</style>
</head>
<body>
    <ul class="ul_one">
        <li>1ssssssssssssssssssssssss</li>
        <li>1</li>
        <li>1</li>
        <li>1</li>
        <li>1</li>
        <li>1</li>
        <li>寬度根據內容自適應1</li>
        <li>1</li>
        <li>1</li>
        <li>1</li>
        <li>1</li>
    </ul>
    <div class="clear"></div>
    <hr />
    <ul class="ul_two">
        <li>1ssssssssssssssssssssssss</li>
        <li>1</li>
        <li>1</li>
        <li>1</li>
        <li>1</li>
        <li>1寬度根據內容自適應</li>
        <li>1</li>
        <li>1</li>
        <li>1</li>
        <li>1</li>
        <li>1</li>
    </ul>
    <div class="clear"></div>
    <hr />
    <ul class="ul_three">
        <li>1ssssssssssssssssssssssss</li>
        <li>1</li>
        <li>1</li>
        <li>1</li>
        <li>1</li>
        <li>1寬度根據內容自適應</li>
        <li>1</li>
        <li>1</li>
        <li>1</li>
        <li>1</li>
        <li>1</li>
    </ul>
</body>
</html>  

(完)

[研究] 用 WinX YouTube Downloader 5.16 試用版下載 YouTube「現場直播(及時串流, Live Streaming, Realtime Streaming)」

[研究] 用 WinX YouTube Downloader 5.16 試用版下載 YouTube「現場直播(及時串流, Live Streaming, Realtime Streaming)」

2020-04-19









(完)

2020年4月19日 星期日

[研究] 用 YouTube Downloader HD v2.9.9.72下載 YouTube「現場直播(及時串流, Live Streaming, Realtime Streaming)」

[研究] 用 YouTube Downloader HD v2.9.9.72下載 YouTube「現場直播(及時串流, Live Streaming, Realtime Streaming)」

2020-04-19






(完)

[研究] 用 YouTube By Click 2.2.126下載 YouTube「現場直播(及時串流, Live Streaming, Realtime Streaming)」

[研究] 用 YouTube By Click 2.2.126下載 YouTube 「現場直播(及時串流, Live Streaming, Realtime Streaming)」

2020-04-19

live streaming , online streaming 直播

YouTube By Click 免費版,可以下載 480p
https://www.youtubebyclick.com/zh/YouTube-Live-Download.php

付費版,可以下載 HD 畫質
https://www.youtubebyclick.com/zh/PremiumVersion.php



**********

封存直播影片- YouTube說明 - Google Support

有鑑於傳統版直播工具即將停用,我們歡迎您改用全新的現場控制室,這個頁面結合了多項傳統版電腦直播工具,並加入全新功能和改良版功能。瞭解詳情
如果您的直播影片長度少於 12 小時,YouTube 可以自動為您封存影片。這項功能適用於所有類型的直播,包括透過「立即直播」、「活動」、網路攝影機或行動裝置進行的直播。YouTube 還會自動封存畫質為 1440p 和 2160p (4K) 的直播影片。不過,我們仍建議您自行在本機上將直播內容錄下來存檔備份,以備不時之需。此外,您也可以在直播的過程中同時製作焦點剪輯片段。
請注意,如果直播影片長度超過 12 小時,系統可能完全不會擷取任何內容。
https://support.google.com/youtube/answer/6247592?hl=zh-Hant

(完)

2020年4月17日 星期五

[研究][C#][ASP.NET][WebForm] 用 ClosedXML 0.95.0 把 GridView1 匯出成 Excel (.xlsx)

[研究][C#][ASP.NET][WebForm] 用 ClosedXML 0.95.0 把 GridView1 匯出成 Excel (.xlsx) 下載

2020-04-17
2020-04-20更新

Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="GridViewtoDataTableTest.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:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TestDBConnectionString %>" 
                SelectCommand="SELECT * FROM [TestTable]"></asp:SqlDataSource><br />
            <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" 
                AutoGenerateColumns="False" DataSourceID="SqlDataSource1">
                <Columns>
                    <asp:BoundField DataField="no" HeaderText="no" InsertVisible="False" ReadOnly="True" SortExpression="no" />
                    <asp:BoundField DataField="myname" HeaderText="myname" SortExpression="myname" />
                    <asp:BoundField DataField="addr" HeaderText="addr" SortExpression="addr" />
                    <asp:BoundField DataField="myname2" HeaderText="myname2" SortExpression="myname2" Visible="false" />
                    <asp:TemplateField HeaderText="addr2" SortExpression="addr2">
                        <EditItemTemplate>
                            <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("addr2") %>'></asp:TextBox>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label ID="Label1" runat="server" Text='<%# Bind("addr2") %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                </Columns>
            </asp:GridView><br />
            <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" /><br />
            <asp:Label ID="Label_MSG1" runat="server"></asp:Label>
        </div>
    </form>
</body>
</html>


上面 myname2 欄位因為 Visible="false",GridViewtoDataTable() 產出結果會只剩下標題,沒有內容。
上面 addr2 欄位因為執行【將這個欄位轉為 TemplateField】,GridViewtoDataTable() 產出結果會只剩下標題。
GridViewtoDataTable() 可能要再研究改良。

Default.aspx.cs

using ClosedXML.Excel;
using System;
using System.Data;
using System.Text;
using System.Web;
using System.Web.UI.WebControls;

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

        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            Label_MSG1.Text = "";
            if (GridView1.Rows.Count == 0)
            {
                Label_MSG1.Text = "無可匯出資料。";
                Label_MSG1.ForeColor = System.Drawing.Color.Red;
                return;
            }

            var wb = new XLWorkbook();
            DataTable dataTable = GridViewtoDataTable(GridView1);
            //Worksheet names cannot be empty
            dataTable.TableName = "工作表1";

            wb.Worksheets.Add(dataTable);

            string mainFileName = "匯出檔案";

            //DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xlsx";
            // 使用者若很多,最好連 fff 也用上
            //DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff") + ".xlsx";

            // 若檔案名稱有中文字或特殊符號,Response.AddHeader() 輸出檔案名稱要編碼
            string outFileName = HttpUtility.UrlEncode(mainFileName, System.Text.Encoding.UTF8) + "-" +
                DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff") + ".xlsx";

            // XLWorkbook.SaveAs() 輸出檔案名稱不要用 HttpUtility.UrlEncode 編碼
            // Response.TransmitFile() 輸出檔案名稱不要用 HttpUtility.UrlEncode 編碼
            string dirFileName = @"D:\Production\TempImageFiles\" + mainFileName + "-" +
                DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff") + ".xlsx";

            //wb.SaveAs("Export.xlsx");
            // Visual Studio 下 Debug 會存到 C:\Program Files (x86)\IIS Express\
            wb.SaveAs(dirFileName);
            // 跳出下載視窗
            Response.Clear();
            Response.Buffer = true;
            Response.ContentEncoding = Encoding.GetEncoding(950);   //950就是所謂的BIG5
            //Response.AddHeader("Content-Disposition", "attachment;filename=\"" +
            //    HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + "-" +
            //    DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx");
            Response.AddHeader("Content-Disposition", "attachment;filename=\"" + outFileName);

            //'Excel 2003 : "application/vnd.ms-excel"
            //'Excel 2007 : "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"

            //Extension       MIME Type
            //.xlsx               application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
            //.xltx                application/vnd.openxmlformats-officedocument.spreadsheetml.template
            //.potx               application/vnd.openxmlformats-officedocument.presentationml.template
            //.ppsx              application/vnd.openxmlformats-officedocument.presentationml.slideshow
            //.pptx               application/vnd.openxmlformats-officedocument.presentationml.presentation
            //.sldx               application/vnd.openxmlformats-officedocument.presentationml.slide
            //.docx              application/vnd.openxmlformats-officedocument.wordprocessingml.document
            //.dotx               application/vnd.openxmlformats-officedocument.wordprocessingml.template
            //.xlam              application/vnd.ms-excel.addin.macroEnabled.12
            //.xlsb               application/vnd.ms-excel.sheet.binary.macroEnabled.12

            //Response.ContentType = "application/ms-excel";
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

            //Response.TransmitFile(@"C:\Program Files (x86)\IIS Express\Export.xlsx");
            //dirFileName = dirFileName.Replace('\\', '/');   //轉換 Windows, Linux 不同路徑斜線
            //dirFileName = dirFileName.Replace("/", "\\");  // 轉換斜線 / 成 \  ,因 \ 是特殊符號,要以 \\ 表示
            Response.TransmitFile(dirFileName);
            Response.End();
        }

        public static DataTable GridViewtoDataTable(GridView gv)
        {
            //GridView2DataTable
            // 只會抓 <asp:BoundField DataField="myname" HeaderText="myname" SortExpression="myname" /> 這類的值,
            // 執行【將這個欄位轉為 TemplateField】的欄位不會被抓到。 
            gv.AllowSorting = false;    // 支援排序時,標題不是文字,要改抓 LinkButton 的文字,麻煩,不如暫時取消
            gv.AllowPaging = false; // 分頁狀態下,只能抓到當頁的10筆,必須取消分頁
            gv.DataBind();

            DataTable dt = new DataTable();
            DataColumn dc;
            DataRow dr;
            for (int i = 0; i < gv.Columns.Count; i++)
            {
                dc = new DataColumn();
                dc.ColumnName = gv.Columns[i].HeaderText;
                dt.Columns.Add(dc);
            }
            for (int i = 0; i < gv.Rows.Count; i++)
            {
                dr = dt.NewRow();
                for (int j = 0; j < gv.Columns.Count; j++)
                {
                    dr[j] = gv.Rows[i].Cells[j].Text;
                }
                dt.Rows.Add(dr);
            }
            gv.AllowSorting = true;
            gv.AllowPaging = true;
            gv.DataBind();

            return dt;
        }
    }
}


解決欄位 Visible="false" 問題,標題和資料內容都不會產出了。


public static DataTable GridViewtoDataTable(GridView gv)
{
    //GridView2DataTable
    // 只會抓 <asp:BoundField DataField="myname" HeaderText="myname" SortExpression="myname" /> 這類的值,
    // 執行【將這個欄位轉為 TemplateField】的欄位不會被抓到。 
    gv.AllowSorting = false;    // 支援排序時,標題不是文字,要改抓 LinkButton 的文字,麻煩,不如暫時取消
    gv.AllowPaging = false; // 分頁狀態下,只能抓到當頁的10筆,必須取消分頁
    gv.DataBind();

    DataTable dt = new DataTable();
    DataColumn dc;
    DataRow dr;
    for (int i = 0; i < gv.Columns.Count; i++)
    {
        dc = new DataColumn();
        dc.ColumnName = gv.Columns[i].HeaderText;
        if (gv.Columns[i].Visible != false)
        {
            dt.Columns.Add(dc);
        }
    }
    int index = 0;
    for (int i = 0; i < gv.Rows.Count; i++)
    {
        dr = dt.NewRow();
        index = 0;
for (int j = 0; j < gv.Columns.Count; j++) { if (gv.Columns[j].Visible != false) { //dr[j] = gv.Rows[i].Cells[j].Text; dr[index] = gv.Rows[i].Cells[j].Text; index++; } } dt.Rows.Add(dr); } gv.AllowSorting = true; gv.AllowPaging = true; gv.DataBind(); return dt; }

執行【將這個欄位轉為 TemplateField】的欄位不會被抓到或可參考下面
http://shaurong.blogspot.com/2016/03/caspnet-gridview-excel-xlsx-openxml-sdk.html



public static DataTable GridViewtoDataTable(GridView gv)
        {
            //GridView2DataTable
            // 只會抓 <asp:BoundField DataField="myname" HeaderText="myname" SortExpression="myname" /> 這類的值,
            // 執行【將這個欄位轉為 TemplateField】的欄位不會被抓到。 
            gv.AllowSorting = false;    // 支援排序時,標題不是文字,要改抓 LinkButton 的文字,麻煩,不如暫時取消
            gv.AllowPaging = false; // 分頁狀態下,只能抓到當頁的10筆,必須取消分頁
            gv.DataBind();

            DataTable dt = new DataTable();
            DataColumn dc;
            DataRow dr;
            for (int i = 0; i < gv.Columns.Count; i++)
            {
                dc = new DataColumn();
                dc.ColumnName = gv.Columns[i].HeaderText;
                if (gv.Columns[i].Visible != false)
                {
                    dt.Columns.Add(dc);
                }
            }
            int index = 0;
            for (int i = 0; i < gv.Rows.Count; i++)
            {
                dr = dt.NewRow();
                index = 0;
                for (int j = 0; j < gv.Columns.Count; j++)
                {
                    
                    if (gv.Columns[j].Visible != false)
                    {
                        //dr[j] = gv.Rows[i].Cells[j].Text;
                        dr[index] = gv.Rows[i].Cells[j].Text;
                        if (gv.Rows[i].Cells[j].HasControls())
                        {
                            for (int k=0;k< gv.Rows[i].Cells[j].Controls.Count; k++)
                            {
                                if (gv.Rows[i].Cells[j].Controls[k] is Label)
                                {
                                    Label labelControl = gv.Rows[i].Cells[j].Controls[k] as Label;
                                    //Label labelControl = (Label)gv.Rows[i].Cells[j].FindControl("Label1");
                                    if (labelControl != null)
                                    {
                                        string labelText = labelControl.Text;
                                        dr[index] = labelText;
                                    }
                                }
                            }
                        }
                        index++;
                    }
                }
                dt.Rows.Add(dr);
            }
            gv.AllowSorting = true;
            gv.AllowPaging = true;
            gv.DataBind();

            return dt;
        }

補充:如果不做下面設定


gv.AllowSorting = false;    // 支援排序時,標題不是文字,要改抓 LinkButton 的文字,麻煩,不如暫時取消

標題純文字這樣抓


// 標題純文字這樣抓
for (int i = 0; i < girdViewtest.HeaderRow.Cells.Count; i++)
{
    dt.Columns.Add(girdViewtest.HeaderRow.Cells[i].Text.Replace("&nbsp;", ""));
}

// 如果 GridView 設定為可排序,header 就要改抓 LinkButton 的文字
for (int i = 0; i < gridView.HeaderRow.Cells.Count; i++)
{
    if (gridView.HeaderRow.Cells[i].HasControls())
    {
        // 如果 GridView 設定為可排序,header 就要改抓 LinkButton 的文字
        // Linkbutton is in index 0 of the control
        if (gridView.HeaderRow.Cells[i].Controls[0] is LinkButton)
        {
   LinkButton headerControl = gridView.HeaderRow.Cells[i].Controls[0] as LinkButton;
            string headerName = headerControl.Text;
  }     
 }     
}
                           

2020-04-27 改為下面




public static DataTable GridViewtoDataTable(GridView gv)
{
 //GridView2DataTable
 // 只會抓 <asp:BoundField DataField="myname" HeaderText="myname" SortExpression="myname" /> 這類的值,
 // 執行【將這個欄位轉為 TemplateField】的欄位不會被抓到。 
 gv.AllowSorting = false;    // 支援排序時,標題不是文字,要改抓 LinkButton 的文字,麻煩,不如暫時取消
 gv.AllowPaging = false; // 分頁狀態下,只能抓到當頁的10筆,必須取消分頁
 gv.DataBind();

 DataTable dt = new DataTable();
 DataColumn dc;
 DataRow dr;

 // 標題純文字這樣抓
 //for (int i = 0; i < gv.Columns.Count; i++)
 //{
 //    dc = new DataColumn();
 //    dc.ColumnName = gv.Columns[i].HeaderText;
 //    dt.Columns.Add(dc);
 //}

 // 標題純文字這樣抓
 for (int i = 0; i < gv.HeaderRow.Cells.Count; i++)
 {
  dt.Columns.Add(gv.HeaderRow.Cells[i].Text.Replace("&nbsp;", ""));
 }

 // 抓資料
 //for (int i = 0; i < gv.Rows.Count; i++)
 //{
 //    dr = dt.NewRow();
 //    for (int j = 0; j < gv.Columns.Count; j++)
 //    {
 //        dr[j] = gv.Rows[i].Cells[j].Text;
 //    }
 //    dt.Rows.Add(dr);
 //}

 foreach (GridViewRow row in gv.Rows)
 {
  DataRow datarow = dt.NewRow();
  // 用  gv.Columns.Count 可能抓到 0,用 row.Cells.Count 
  for (int i = 0; i < row.Cells.Count; i++)
  {
   datarow[i] = row.Cells[i].Text.Replace("&nbsp;", "");
  }
  dt.Rows.Add(datarow);
 }
 gv.AllowSorting = true;
 gv.AllowPaging = true;
 gv.DataBind();

 return dt;
}

(完)


[研究][C#][ASP.NET][WebForm] GridView 轉 DataTable

[研究][C#][ASP.NET][WebForm] GridView 轉 DataTable

2020-04-17

public static DataTable GridViewtoDataTable(GridView gv)
        {
            // GridView2DataTable
            // 只會抓 <asp:BoundField DataField="myname" HeaderText="myname" SortExpression="myname" /> 這類的值,
            // 執行【將這個欄位轉為 TemplateField】的欄位不會被抓到。 
            gv.AllowSorting = false;    // 支援排序時,標題不是文字,要改抓 LinkButton 的文字,麻煩,不如暫時取消
            gv.AllowPaging = false; // 分頁狀態下,只能抓到當頁的10筆,必須取消分頁
            gv.DataBind();
            DataTable dt = new DataTable();
            DataColumn dc;
            DataRow dr;   
            for (int i = 0; i < gv.Columns.Count; i++)
            {
                dc = new DataColumn();
                dc.ColumnName = gv.Columns[i].HeaderText;
                dt.Columns.Add(dc);
            }
            for (int i = 0; i < gv.Rows.Count; i++)
            {
                dr = dt.NewRow();
                for (int j = 0; j < gv.Columns.Count; j++)
                {
                    dr[j] = gv.Rows[i].Cells[j].Text;
                }
                dt.Rows.Add(dr);
            }
            gv.AllowSorting = true;
            gv.AllowPaging = true;
            gv.DataBind();
            return dt;
        }

(完)


[研究][C#][ASP.NET][WebForm] 用 ExcelDataReader 讀取匯入 .xlsx , .xls , csv 檔案

[研究][C#][ASP.NET][WebForm] 用 ExcelDataReader 讀取匯入 .xlsx , .xls , csv 檔案

2020-04-17
2020-04-24 修 Bug

https://exceldatareader.codeplex.com/
https://www.nuget.org/packages?q=ExcelDataReader
https://github.com/ExcelDataReader/ExcelDataReader

建議用 Microsoft Excel 建立檔案,
敝人用 ClosedXML 匯出的 .xlsx 用 ExcelDataReader 讀取會失敗,網頁畫面一片空白,也沒有錯誤訊息。(待研究)

工具:Visual Studio 2019

Default.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" 
Inherits="ExcelDataReaderDemo.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" accept=".csv,.xls,.xlsx"  /><br />
            <asp:Button ID="Button1" runat="server" Text="匯入" OnClick="Button1_Click" /><br />
            <asp:Label ID="Label_MSG1" runat="server"></asp:Label><br />
            <asp:GridView ID="GridView1" runat="server"></asp:GridView>
        </div>
    </form>
</body>
</html>


Default.aspx.cs

using ExcelDataReader;
using System;
using System.Data;
using System.IO;

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

        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            // ref : https://exceldatareader.codeplex.com/
            // ref : https://github.com/ExcelDataReader/ExcelDataReader
            // ref : https://ithelp.ithome.com.tw/articles/10048933

            if (FileUpload1.HasFile)
            {
                //string fd = (string)ConfigurationManager.AppSettings["SaveTempDir"] + "\\";
                //if (!System.IO.Directory.Exists(fd))
                //{
                //    System.IO.Directory.CreateDirectory(fd);
                //}
                string extFileName = System.IO.Path.GetExtension(FileUpload1.FileName).ToLower();
                if ((extFileName != ".xls") && (extFileName != ".xlsx") && (extFileName != ".csv") )
                {
                    Label_MSG1.Text = "請上傳副檔名.xls或.xlsx檔案";
                    //ScriptManager.RegisterStartupScript(this, GetType(), "Popup1", "erroralert('" + "請上傳副檔名.xlsx檔案!" + "');", true);
                    return;
                }
                //string FileName = fd + System.IO.Path.GetExtension(FileUpload1.FileName).ToLower();
                string mainFileName = Path.GetFileNameWithoutExtension(FileUpload1.FileName);
                string fileName = @"D:\\Production\\TempImageFiles\\" + mainFileName + "-" + DateTime.Now.ToString("-yyyy-MM-dd HH-mm-ss") + extFileName;
                FileUpload1.SaveAs(fileName);

                using (var stream = File.Open(fileName, FileMode.Open, FileAccess.Read))
                {
                    // Auto-detect format, supports:
                    //  - Binary Excel files (2.0-2003 format; *.xls)
                    //  - OpenXml Excel files (2007 format; *.xlsx)
                    //using (IExcelDataReader reader = ExcelReaderFactory.CreateCsvReader(stream))  // 支援 .csv,下面還有地方要改
                    //using (IExcelDataReader reader = ExcelReaderFactory.CreateOpenXmlReader(stream))    // 僅支援 .xlsx,不支援 .xls
                    using (var reader = ExcelReaderFactory.CreateReader(stream))    // 僅支援 .xls 和 .xlsx,不支援 .csv
                    {
                        // 3種方法選一種就好

                        // 1. Use the reader methods
                        do
                        {
                            // 逐列讀取
                            while (reader.Read())
                            {
                                // reader.GetDouble(0);
                                // 會包含第一列 (可能是標題,而非資料)
                                // 或設定 excelReader.IsFirstRowAsColumnNames = true; 去避免

                                //string columnData0 = reader[0].ToString();   // 讀取該列第0欄位
                                //string columnData1 = reader[1].ToString();   // 讀取該列第1欄位
                            }
                        } while (reader.NextResult());
                        // ----------------------------------------
                        // 2. Use the AsDataSet extension method
                        // The result of each spreadsheet is in result.Tables
                        // NuGet 要安裝 ExcelDataReader.DataSet
                        var result = reader.AsDataSet();
                        DataRowCollection dataRow = result.Tables["工作表1"].Rows;
                        DataColumnCollection dataColumn = result.Tables["工作表1"].Columns;
                        // Print the ColumnName and DataType for each column.
                        //foreach (DataColumn column in dataColumn)
                        //{
                        //    string columnName = column.ColumnName;
                        //    string columnDataType = column.DataType.ToString();
                        //}
                        //foreach (DataRow row in dataRow)
                        //{
                        //    // 會包含第一列 (可能是標題,而非資料)
                        //    string columnData0 = reader[0].ToString();   // 讀取該列第0欄位
                        //    string columnData1 = reader[1].ToString();   // 讀取該列第1欄位
                        //}

                        foreach (DataTable _table in result.Tables)
                        {
                            for (int i = 0; i < _table.Columns.Count; i++)
                            {
                                string columnName = _table.Columns[i].ColumnName.PadRight(_table.Columns[i].ColumnName.Length + 2, Convert.ToChar(" "));
                            }
                            foreach (DataRow _row in _table.Rows)
                            {
                                for (int i = 0; i < _table.Columns.Count; i++)
                                {
                                    string columnData = _row[i].ToString().Trim().PadRight(_table.Columns[i].ColumnName.Length + 2, Convert.ToChar(" "));
                                }
                            }
                        }
                        // ----------------------------------------
                        // 3. 讀取放入 GridView1
                        GridView1.DataSource = reader.AsDataSet();
                        GridView1.DataBind();
                    }
                } // using
            }
        }
    }
}



(完)