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;
}
}
}
}
|
(完)
沒有留言:
張貼留言