[研究][ASP.NET]使用 NPOI 2.5.2 讀取匯入 .xlsx 到資料庫
2021-02-08
在 Server 端存取 Excel 檔案的利器：NPOI Library | Microsoft Learn
2014/06/12
https://learn.microsoft.com/zh-tw/previous-versions/ee818993(v=msdn.10)?redirectedfrom=MSDN
GitHub - nissl-lab/npoi-examples: To get started with NPOI, here is all the official examples.
https://github.com/nissl-lab/npoi-examples
- Folder Name Description
- POIFS OLE2/ActiveX document examples
- HSSF examples for Microsoft Excel BIFF(Excel 97-2003, xls)
- SS Excel Common examples for both Excel 2003(xls) and Excel 2007+(xlsx)
- XSSF Excel 2007(xlsx) examples
- XWPF Word 2007(docx) examples
- OOXML OpenXml format low-level examples
- ScratchPad/HWPF Word 2003(doc) examples
NPOI 不支援 .ods。
環境：Visual Studio 2019 + ASP.NET + WebForm + C# + NuGet 安裝 NPOI 2.5.2
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="ImportXlsxByNPOITest.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_NPOI" runat="server" Text="匯入(.xlsx)"
OnClick="Button_Import_Xlsx_by_NPOI_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 NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
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 ImportXlsxByNPOITest
{
public partial class Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button_Import_Xlsx_by_NPOI_Click(object sender, EventArgs e)
{
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;
try
{
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);
IWorkbook workBook;
using (var fs = new FileStream(openFilename, FileMode.Open, FileAccess.ReadWrite))
{
workBook = new XSSFWorkbook(fs); // .xlsx
//var sheet = workBook.GetSheet("調查表");
ISheet sheet = workBook.GetSheetAt(0); // 抓第一個sheet
//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();
}
while (sheet.GetRow(rowCount) != null)
{
rowCount++;
}
i = 1;
while (sheet.GetRow(i) != null)
{
fieldText = "";
fieldDateTime = "";
fieldInt = "";
fieldBit = "";
if (i == 0)
{
// 標題列不讀取
}
else
{
// 非標題列
if (sheet.GetRow(i).GetCell(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
{
// 讀取所有欄位
if (sheet.GetRow(i).GetCell(0) != null)
fieldText = sheet.GetRow(i).GetCell(0).ToString();
if (sheet.GetRow(i).GetCell(0) != null)
{
fieldDateTime = sheet.GetRow(i).GetCell(1).ToString();
//DateTime fieldDateTime2 = DateTime.Now;
}
if (sheet.GetRow(i).GetCell(0) != null)
fieldInt = sheet.GetRow(i).GetCell(2).ToString();
if (sheet.GetRow(i).GetCell(0) != null)
fieldBit = sheet.GetRow(i).GetCell(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
} // using
//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>");
} // if (!FileUpload1.HasFile)
}
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>");
}
GridView1.DataBind(); // 更新畫面
}
}
} |
(完)
相關
[研究][ASP.NET]Npoi.Mapper 6.0.0 與 .NET Framework 相容安裝測試https://shaurong.blogspot.com/2023/07/aspnetnpoimapper-600-net-framework.html
[研究][ASP.NET]NPOI 與 Npoi.Mapper 升級：無法安裝封裝 'Npoi.Mapper 6.0.0'
https://shaurong.blogspot.com/2023/07/aspnetnpoi-npoimapper-npoimapper-600.html
[研究][ASP.NET]無法解析相依性 'SixLabors.Fonts'。
https://shaurong.blogspot.com/2022/11/aspnet-sixlaborsfonts-nugetorg.html
[研究][ASP.NET]無法解析相依性。'SharpZipLib 1.3.2' 與 'NPOI 2.5.3 條件約束: SharpZipLib (= 1.2.0)' 不相容。
https://shaurong.blogspot.com/2021/06/aspnetsharpziplib-132-npoi-253.html
[研究][ASP.NET]使用 NPOI 2.5.2 讀取匯入 .xlsx 到資料庫
https://shaurong.blogspot.com/2021/02/aspnet-npoi-252-xlsx.html
[研究][C#][ASP.NET] 用 NPOI 替換 Word (.docx) 中的文字https://shaurong.blogspot.com/2020/06/caspnet-npoi-word-docx.html
[研究] 匯出、匯入 Excel 檔案 (.NET)
https://shaurong.blogspot.com/2017/11/excel-net.html
[研究][C#][ASP.NET] 用 NPOI v2.2.1 在 Server 端存取 Excel 檔案 (從官方下載安裝)
https://shaurong.blogspot.com/2017/11/caspnet-npoi-v221-server-excel.html
[研究][C#][ASP.NET] 用 NPOI v2.3.0 在 Server 端存取 Excel 檔案 (NuGet安裝)
https://shaurong.blogspot.com/2017/11/caspnet-npoi-server-excel-nuget.html
沒有留言:
張貼留言