- 最後登錄
- 2022-11-24
- 在線時間
- 1 小時
- 註冊時間
- 2008-9-2
- 閱讀權限
- 20
- 精華
- 0
- UID
- 4896884
- 帖子
- 541
- 積分
- 212 點
- 潛水值
- 13707 米
| 本帖最後由 rr09192084 於 2016-3-27 09:38 PM 編輯
也可以用EPPlus這個Nuget套件,請到"工具"->"Nuget套件管理員"->"管理方案的Nuget套件",到線上去搜尋"EPPlus",記得大小寫要注意,找到"標記"是"excel OOXML"這個然後安裝它。
這整個程式要正常執行,首先要加入這四個參考:- using System.IO;
- using System.Reflection;
- using OfficeOpenXml;
- using OfficeOpenXml.Style;
複製代碼 完整的程式碼如下
- //定義資料
- class ClassData : IEquatable<ClassData>
- {
- public string 姓名 { get; set; }
- public string 身高 { get; set; }
- public string 體重 { get; set; }
- public string 年齡 { get; set; }
- public bool Equals(ClassData other)
- {
- return this.姓名.Equals(other.姓名);
- }
- }
- //新增存放資料的LIST欄位來備用
- private List<ClassData> classData = new List<ClassData>();
- //載入程式時就將EXCEL檔案讀取進來,並將它放入準備好的LIST中
- private void Form1_Load(object sender, EventArgs e)
- {
- string xlsxpath = @"D:TMPClass_0715.xlsx";
- using (FileStream fs = new FileStream(xlsxpath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
- {
- using (ExcelPackage package = new ExcelPackage(fs))
- {
- //難怪有人說C#程式只要一直點下去就對了。
- classData = package.ToDataTable().ToList<ClassData>();
- }
- }
- }
- //在textBox1中輸入姓名,按下button1查詢的程式碼
- private void button1_Click(object sender, EventArgs e)
- {
- textBox2.Clear();
- textBox3.Clear();
- textBox4.Clear();
- if (textBox1.Text.Length > 0)
- {
- foreach (var p in classData)
- {
- if (p.姓名.Equals(textBox1.Text))
- {
- textBox2.Text = p.身高;
- textBox3.Text = p.體重;
- textBox4.Text = p.年齡;
- }
- }
- }
- }
- //擴充方法要放在靜態類別中
- public static class ExtensionUtility
- {
- //擴充方法:將EXCEL檔案中的第sheetindex個Worksheet讀到DataTable中
- public static DataTable ToDataTable(this ExcelPackage package, int sheetindex = 1)
- {
- ExcelWorksheet workSheet = package.Workbook.Worksheets[sheetindex];
- DataTable table = new DataTable();
- foreach (var firstRowCell in workSheet.Cells[1, 1, 1, workSheet.Dimension.End.Column])
- {
- table.Columns.Add(firstRowCell.Text);
- }
- for (var rowNumber = 2; rowNumber <= workSheet.Dimension.End.Row; rowNumber++)
- {
- var row = workSheet.Cells[rowNumber, 1, rowNumber, workSheet.Dimension.End.Column];
- var newRow = table.NewRow();
- foreach (var cell in row)
- {
- newRow[cell.Start.Column - 1] = cell.Text;
- }
- table.Rows.Add(newRow);
- }
- return table;
- }
- //擴充方法:將DataTable轉換成指定結構型態的LIST
- public static List<TSource> ToList<TSource>(this DataTable dataTable) where TSource : new()
- {
- var dataList = new List<TSource>();
- const BindingFlags flags = BindingFlags.Public | BindingFlags.Instance | BindingFlags.NonPublic;
- var objFieldNames = (from PropertyInfo aProp in typeof(TSource).GetProperties(flags)
- select new { Name = aProp.Name, Type = Nullable.GetUnderlyingType(aProp.PropertyType) ?? aProp.PropertyType }).ToList();
- var dataTblFieldNames = (from DataColumn aHeader in dataTable.Columns
- select new { Name = aHeader.ColumnName, Type = aHeader.DataType }).ToList();
- var commonFields = objFieldNames.Intersect(dataTblFieldNames).ToList();
- foreach (DataRow dataRow in dataTable.AsEnumerable().ToList())
- {
- var aTSource = new TSource();
- foreach (var aField in commonFields)
- {
- PropertyInfo propertyInfos = aTSource.GetType().GetProperty(aField.Name);
- propertyInfos.SetValue(aTSource, dataRow[aField.Name], null);
- }
- dataList.Add(aTSource);
- }
- return dataList;
- }
- }
複製代碼
補充內容 (2016-3-27 09:51 PM):
ExtensionUtility這個類別要放在namespace這一區內,不可以放在Form裡。... |
|