[研究][ASP.NET]使用 Aspose.Cells 21.1.0 讀取匯入 .xlsx 或 .ods 到資料庫
2021-02-08
環境：Visual Studio 2019 + ASP.NET + WebForm + C# + NuGet 安裝 Aspose.Cells 21.1.0
packages.conf
<?xml version="1.0" encoding="utf-8"?>
<packages>
<package id="Aspose.Cells" version="21.1.0" targetFramework="net472" />
<package id="Microsoft.CodeDom.Providers.DotNetCompilerPlatform" version="2.0.1" targetFramework="net472" />
</packages> |
Web.Config
<?xml version="1.0" encoding="utf-8"?>
<!--
如需如何設定 ASP.NET 應用程式的詳細資訊，請前往
https://go.microsoft.com/fwlink/?LinkId=169433
-->
<configuration>
<connectionStrings>
<add name="TestDBConnectionString" connectionString="Data Source=.;Initial Catalog=TestDB;User ID=sa;Password=P@ssw0rd"
providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<compilation debug="true" targetFramework="4.7.2"/>
<httpRuntime targetFramework="4.7.2"/>
</system.web>
<system.codedom>
<compilers>
<compiler language="c#;cs;csharp" extension=".cs"
type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.CSharpCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=2.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
warningLevel="4" compilerOptions="/langversion:default /nowarn:1659;1699;1701"/>
<compiler language="vb;vbs;visualbasic;vbscript" extension=".vb"
type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.VBCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=2.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
warningLevel="4" compilerOptions="/langversion:default /nowarn:41008 /define:_MYTYPE=\"Web\" /optionInfer+"/>
</compilers>
</system.codedom>
<appSettings>
<add key="AsposelicFolder" value="D:\WWW\Asposelic" />
</appSettings>
</configuration>
|
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="ImportXlsxByAsposeCellsTest.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_Ods_by_AsposeCells" runat="server" Text="匯入(.xlsx)"
OnClick="Button_Import_Xlsx_Ods_by_AsposeCells_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 Aspose.Cells;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Configuration;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace ImportXlsxByAsposeCellsTest
{
public partial class Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button_Import_Xlsx_Ods_by_AsposeCells_Click(object sender, EventArgs e)
{
// https://products.aspose.com/cells/net
/* Input/Output
Microsoft Excel: XLS, XLSX, XLSB, XLT, XLTX, XLTM, XLSM, XML
OpenOffice: ODS
Text: CSV, TSV
Web: HTML, MHTML
Numbers: Apple's iWork office suite Numbers app documents
*/
// 設定和使用 Aspose.Cells 授權 (Begin)
string asposelicFolder = ConfigurationManager.AppSettings["AsposelicFolder"];
Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense(asposelicFolder + "/Aspose.Cells.lic");
// 設定和使用 Aspose.Cells 授權 (End)
Label_MSG1.Text = "";
Label_ExportImportMSG.Text = "";
int lineNo = 1;
string queryString = "";
string errMsg = "";
string url = "";
string msg = "";
string fieldText = "";
string fieldDateTime = "";
string fieldInt = "";
string fieldBit = "";
int rowCount = 0;
int columnCount = 0;
if (!FileUpload1.HasFile)
{
Label_ExportImportMSG.Text = "<font color=red>請選擇.xlsx檔案</font>";
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message",
"<script language='javascript' defer>alert('請選擇.xlsx檔案。');</script>");
return;
}
else
{
string fd = (string)ConfigurationManager.AppSettings["TempFolder"] + "\\";
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 == ".ods") || (ext == ".csv")))
//if (!(ext == ".xlsx"))
if (!((ext == ".xlsx") || (ext == ".ods")))
{
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);
//DataSet odsDataSet = ReadOdsFile(openFilename);
//using (FileStream fs = File.Open(@"C:\\Temp\\活頁簿1.xlsx", FileMode.Open))
using (FileStream fs = File.Open(openFilename, FileMode.Open))
{
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(fs);
//Workbook workbook = null;
//if (ext == ".csv")
//{
// TxtLoadOptions loadOptions = new TxtLoadOptions(LoadFormat.CSV);
// loadOptions.ConvertNumericData = false;
// //Workbook workbook = new Workbook(openFilename, loadOptions);
// workbook = new Workbook(openFilename, loadOptions);
//}
//else
//{
// //Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(fs);
// workbook = new Aspose.Cells.Workbook(fs);
//}
rowCount = workbook.Worksheets[0].Cells.Rows.Count;
//columnCount = workbook.Worksheets[0].Cells.Columns.Count; // 只有1
columnCount = workbook.Worksheets[0].Cells.MaxColumn;
//columnCount = workbook.Worksheets[0].Cells.MaxDataColumn; // 好像也可
try
{
//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();
}
int i = 1;
while (workbook.Worksheets[0].Cells.Rows[i][0].Value != null)
{
// 第0列(i=0)是標題，忽略不讀取，故 i=1 開始
// 第1欄位的索引值是 0
// 有些人上傳檔案，下方有幾百列空的，所以某列第一欄若空的，中斷，以下不匯入了，避免浪費時間。
fieldText = workbook.Worksheets[0].Cells.Rows[i][0].ToString().Trim();
if (fieldText != "")
{
// .xlsx 欄位若為空的，回傳回 null，要先檢查
// 要先清除一次，避免這次沒填，回傳 null，值沒被更新
fieldText = "";
fieldDateTime = "";
fieldInt = "";
fieldBit = "";
// null 若 ToString() 會出錯
if (workbook.Worksheets[0].Cells.Rows[i][0].Value != null)
fieldText = workbook.Worksheets[0].Cells.Rows[i][0].Value.ToString().Trim();
if (workbook.Worksheets[0].Cells.Rows[i][1].Value != null)
fieldDateTime = workbook.Worksheets[0].Cells.Rows[i][1].Value.ToString().Trim();
DateTime.TryParse(fieldDateTime, out DateTime fieldDateTime2);
if (workbook.Worksheets[0].Cells.Rows[i][2].Value != null)
fieldInt = workbook.Worksheets[0].Cells.Rows[i][2].Value.ToString().Trim();
if (workbook.Worksheets[0].Cells.Rows[i][3].Value != null)
fieldBit = workbook.Worksheets[0].Cells.Rows[i][3].Value.ToString().Trim();
// --------------------------------------------------------------------------------
// 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();
lineNo++;
}
}// if
i++;
} // while loop
//string url = "/List.aspx?Ref1=" + Request.QueryString["Ref1"].ToString() + "&Ref2=" +
// Request.QueryString["Ref2"].ToString() + "&Ref3=" + Request.QueryString["Ref3"].ToString();
url = "#";
msg = "#1匯入.xlsx共" + rowCount + "橫列" + columnCount +
"行(欄位) (含標題列，若看不到那麼多橫列，，可能填寫了不可見字元)，成功匯入" + (lineNo - 1) + "筆。\\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 = "#2匯入.xlsx共" + rowCount + "橫列" + columnCount
+ "行(欄位) (含標題列，若看不到那麼多橫列，，可能填寫了不可見字元)，第"
+ (lineNo - 1) + "筆 (Excel 第" + lineNo.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(); // 更新畫面
} // if (FileUpload1.HasFile)
}
} |
(完)
沒有留言:
張貼留言