[研究][ASP.NET]使用 ExcelDataReader 3.6.0 讀取匯入 .xlsx 到資料庫
2021-02-08
環境：Visual Studio 2019 + ASP.NET + WebForm + C# + NuGet 安裝 ExcelDataReader 3.6.0
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="ImportXlsxByExcelDataReaderTest.Default" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:FileUpload ID="FileUpload1" runat="server" /><br />
<asp:Button ID="Button_Import_Xlsx_by_ExcelDataReader" runat="server" Text="匯入(.xlsx)"
OnClick="Button_Import_Xlsx_by_ExcelDataReader_Click" /><br />
<br />
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:TestDBConnectionString %>"
SelectCommand="SELECT * FROM [Table1]" ></asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" DataKeyNames="id" DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="id" HeaderText="id" InsertVisible="False" ReadOnly="True" SortExpression="id" />
<asp:BoundField DataField="FieldText" HeaderText="FieldText" SortExpression="FieldText" />
<asp:BoundField DataField="FieldDateTime" HeaderText="FieldDateTime" SortExpression="FieldDateTime" />
<asp:BoundField DataField="FieldInt" HeaderText="FieldInt" SortExpression="FieldInt" />
<asp:CheckBoxField DataField="FieldBit" HeaderText="FieldBit" SortExpression="FieldBit" />
</Columns>
</asp:GridView>
<asp:Label ID="Label_ExportImportMSG" runat="server"></asp:Label><br />
<asp:Label ID="Label_MSG1" runat="server"></asp:Label>
</div>
</form>
</body>
</html> |
Default.aspx.cs
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Configuration;
using System.Web.UI;
using System.Web.UI.WebControls;
using ExcelDataReader;
namespace ImportXlsxByExcelDataReaderTest
{
public partial class Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button_Import_Xlsx_by_ExcelDataReader_Click(object sender, EventArgs e)
{
// https://exceldatareader.codeplex.com/
// https://github.com/ExcelDataReader/ExcelDataReader
// https://ithelp.ithome.com.tw/articles/10048933
Label_MSG1.Text = "";
Label_ExportImportMSG.Text = "";
//
string queryString = "";
string errMsg = "";
string url = "";
string msg = "";
string fieldText = "";
string fieldDateTime = "";
DateTime fieldDateTime2 = DateTime.Now;
string fieldInt = "";
string fieldBit = "";
int rowCount = 0;
int columnCount = 0;
int successCount = 0;
int i = 1;
if (!FileUpload1.HasFile)
{
Label_ExportImportMSG.Text = "<font color=red>請選擇.xlsx檔案</font>";
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message",
"<script language='javascript' defer>alert('請選擇.xlsx檔案。');</script>");
return;
}
else
{
string fd = (string)ConfigurationManager.AppSettings["TempFolder"] + "\\";
if (!System.IO.Directory.Exists(fd))
{
System.IO.Directory.CreateDirectory(fd);
}
string ext = System.IO.Path.GetExtension(FileUpload1.FileName).ToLower();
// ClosedXML 0.94.2 不支援 .csv
// Extension 'csv' is not supported. Supported extensions are '.xlsx', '.xslm', '.xltx' and '.xltm'.
//if (!((ext == ".xlsx") || (ext == ".csv")))
if (!(ext == ".xlsx"))
{
Label_ExportImportMSG.Text = "<font color=red>請上傳副檔名.xlsx檔案</font>";
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript'>alert('"
+ "請上傳副檔名.xlsx檔案！" + "');</script>");
return;
}
string fileName = fd + FileUpload1.FileName;
FileUpload1.SaveAs(fileName);
string mainFileName = Path.GetFileNameWithoutExtension(FileUpload1.FileName);
//string openFilename = "D:\\WWW\\TempImageFiles\\" + mainFileName + "-匯入結果.xlsx";
string openFilename = fd + mainFileName + "-匯入結果" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff") + ".xlsx";
FileUpload1.SaveAs(openFilename);
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
//queryString = @"DELETE FROM [dbo].[Table1] WHERE id=@id; ";
queryString = @"DELETE FROM [dbo].[Table1]; ";
using (SqlConnection connection = new SqlConnection(
WebConfigurationManager.ConnectionStrings["TestDBConnectionString"].ConnectionString))
{
SqlCommand command = new SqlCommand(queryString, connection);
connection.Open();
command.ExecuteNonQuery();
}
try
{
using (var reader = ExcelReaderFactory.CreateReader(stream)) // 僅支援 .xls 和 .xlsx，不支援 .csv
{
rowCount = reader.RowCount;
columnCount = reader.FieldCount;
do
{
// 逐列讀取
while (reader.Read())
{
if (i == 1)
{
// 標題不管
}
else
{
// reader.GetDouble(0);
// 會包含第一列 (可能是標題，而非資料)
// 或設定 excelReader.IsFirstRowAsColumnNames = true; 去避免
fieldText = "";
fieldDateTime = "";
fieldInt = "";
fieldBit = "";
// reader[0] 可能是 null
if (reader[0] == null)
{
// 第一欄位必填，null 或 "" 就假設後面都沒有資料
errMsg = "#1匯入.xlsx共" + rowCount + "橫列" + columnCount
+ "行(欄位) (含標題列，若看不到那麼多橫列，可能填寫了不可見字元)，第"
+ (i - 1) + "筆 (Excel 第" + i.ToString() + "列) 必填沒有資料，匯入中斷，若資料已經全部匯入，可不用理會訊息。\\n";
//ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert('" + errMsg + "');</script>");
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript'>alert('"
+ errMsg + "');</script>");
return;
}
else
{
fieldText = reader[0].ToString();
if (reader[1] != null)
{
fieldDateTime = reader[1].ToString();
DateTime.TryParse(fieldDateTime, out fieldDateTime2);
}
if (reader[2] != null)
fieldInt = reader[2].ToString();
if (reader[3] != null)
fieldBit = reader[3].ToString();
// --------------------------------------------------------------------------------
// e.Values 中欄位名稱的大小寫要符合前端 Bind 的欄位名稱
// 檢察欄位長度、必填、關聯要求
// errMsg = CheckRecord(fieldText,fieldDateTime,fieldInt,fieldBit);
// if (errMsg != "")
// {
// Page.ClientScript.RegisterStartupScript(Page.GetType(), "message",
// "<script language='javascript' defer>alert('第" + lineNo +
// "筆匯入失敗，匯入中斷！" + errMsg + "');</script>");
// return;
// }
// --------------------------------------------------------------------------------
queryString = @"
INSERT INTO [dbo].[Table1]
([FieldText]
,[FieldDateTime]
,[FieldInt]
,[FieldBit])
VALUES
(N'" + fieldText + "'" +
",'" + fieldDateTime2.ToString("yyyy/MM/dd HH:mm:ss.fff") + "'" +
",'" + fieldInt + "'" +
",'" + fieldBit + "'); ";
using (SqlConnection connection = new SqlConnection(
WebConfigurationManager.ConnectionStrings["TestDBConnectionString"].ConnectionString))
{
SqlCommand command = new SqlCommand(queryString, connection);
//command.Parameters.Clear();
//command.Parameters.AddWithValue("@Value", value);
connection.Open();
command.ExecuteNonQuery();
successCount++;
}
}
} // if
i++;
} // while (reader.Read())
} while (reader.NextResult());
//string url = "/List.aspx?Ref1=" + Request.QueryString["Ref1"].ToString() + "&Ref2=" +
// Request.QueryString["Ref2"].ToString() + "&Ref3=" + Request.QueryString["Ref3"].ToString();
url = "#";
msg = "#2匯入.xlsx共" + rowCount + "橫列" + columnCount +
"行(欄位) (含標題列，若看不到那麼多橫列，可能填寫了不可見字元)，成功匯入" + successCount + "筆。\\n";
ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert('"
+ msg + "');location.href='" + url + "';</script>");
}
}
catch (Exception ex)
{
string exceptionMsg = "不明錯誤。";
if (ex != null)
{
exceptionMsg = ex.Message.ToString();
}
Label_MSG1.ForeColor = System.Drawing.Color.Red;
Label_MSG1.Text = errMsg + "<br />" + exceptionMsg;
errMsg = "#3匯入.xlsx共" + rowCount + "橫列" + columnCount
+ "行(欄位) (含標題列，若看不到那麼多橫列，可能填寫了不可見字元)，第"
+ (i - 1) + "筆 (Excel 第" + i.ToString() + "列)匯入失敗。\\n";
//ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert('" + errMsg + "');</script>");
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript'>alert('"
+ errMsg + "');</script>");
// 會因為 Exception 內容關係，JavaScript alert 對話盒視窗可能不會跳出顯示
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript'>alert('"
+ exceptionMsg + "');</script>");
}
} //using (FileStream fs = File.Open(openFilename, FileMode.Open))
} //if (FileUpload1.HasFile)
GridView1.DataBind(); // 更新畫面
}
}
} |
(完)
沒有留言:
張貼留言