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 + ASP.NET + WebForm + C#
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
}
}
}
}
|
(完)
沒有留言:
張貼留言