C# Net 使用 openxml 读取Excel到对象
C# Net 使用 openxml 读取 Excel
C# Net 使用 openxml 读取Excel到对象
C# Net Core 使用 openxml 读取Excel
C# Net Core 使用 openxml 读取Excel到对象
注：需要写入对象到Excel请参考另一篇博客（https://www.cnblogs.com/ping9719/p/12539737.html）
------------------------------------------------------------
------------------------------------------------------------
-------------------------文尾看效果---------------------
------------------------------------------------------------
------------------------------------------------------------
加入包：OpenXml
创建文件：ExcelRead.cs
复制下面全部代码到文件 ExcelRead.cs
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Office2010.ExcelAc;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using YGNT.Office.ExcelXml.Models;
namespace YGNT.Office.ExcelXml
{
/// <summary>
/// 读取Excel
/// </summary>
public class ExcelRead
{
/// <summary>
/// 读取、解析
/// </summary>
/// <param name="fileName">文件</param>
/// <param name="sheetName">工作表（默认第一个）</param>
/// <param name="type">1 不去空格 2 前后空格 3 所有空格 </param>
/// <returns></returns>
public static List<ExcelCellInfo> Read(string fileName, string sheetName = "", int type = 2)
{
List<ExcelCellInfo> excelCellInfos = new List<ExcelCellInfo>();
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
//查找工作薄
Sheet sheet = ExcelSeek.SeekSheet(workbookPart, sheetName);
//工作表
WorksheetPart worksheetPart = (WorksheetPart)spreadsheetDocument.WorkbookPart.GetPartById(sheet.Id);
//数据行
var rows = worksheetPart.Worksheet.Descendants<Row>();//获得Excel中得数据行
foreach (Row r in rows)
{
foreach (Cell c in r.Elements<Cell>())
{
ExcelCellInfo excelCellInfo = new ExcelCellInfo();
excelCellInfo.RowIndex = (int)r.RowIndex.Value;
excelCellInfo.CellReference = c.CellReference;
excelCellInfo.ColumnIndex = ExcelAlphabet.ABCToColumn(excelCellInfo.CellReference.Replace(excelCellInfo.RowIndex.ToString(), ""));
excelCellInfo.Value = GetCellValue(c, workbookPart, type);
excelCellInfos.Add(excelCellInfo);
}
}
}
return excelCellInfos;
}
/// <summary>
/// 读取、解析
/// </summary>
/// <param name="fileName">文件</param>
/// <param name="sheetName">工作表（默认第一个）</param>
/// <param name="type">1 不去空格 2 前后空格 3 所有空格 </param>
/// <returns></returns>
public static List<T> Read<T>(string fileName, string sheetName = "", int type = 2) where T : new()
{
List<ExcelCellInfo> excelCellInfos = Read(fileName, sheetName, type);
List<T> t = new List<T>();
//所有属性
var properties = new T().GetType().GetProperties();
//exc中第一行单元格
var oneRow = excelCellInfos.Where(o => o.RowIndex == 1 && !string.IsNullOrEmpty(o.Value));
//属性和单元格关系（key:属性，val：单元格）
var p_OneROw = new Dictionary<System.Reflection.PropertyInfo, ExcelCellInfo>();
//给【p_OneROw】赋值
foreach (var property in properties)
{
//取属性上的自定义特性
ExcelColumnAttribute att = null;
var atts = (IEnumerable<ExcelColumnAttribute>)property.GetCustomAttributes(typeof(ExcelColumnAttribute), false);
if (atts.Any())
att = atts.First();
if (att != null && att.IsShow)
{
string eName = att.ColumnName;
var lie = oneRow.FirstOrDefault(o => o.Value == eName);
if (lie != null)
{
p_OneROw.Add(property, lie);
}
}
else
{
string eName = property.Name;
var lie = oneRow.FirstOrDefault(o => o.Value == eName);
if (lie != null)
{
p_OneROw.Add(property, lie);
}
}
}
for (int i = 2; i <= excelCellInfos.Max(o => o.RowIndex); i++)
{
var model = new T();
foreach (var por in p_OneROw)
{
var clee = excelCellInfos.FirstOrDefault(o => o.RowIndex == i && o.ColumnIndex == por.Value.ColumnIndex);
if (clee != null)
{
string ty = por.Key.PropertyType.FullName;
if (ty.Contains("System.String"))
por.Key.SetValue(model, clee.Value);
else if (ty.Contains("System.DateTime"))
por.Key.SetValue(model, Convert.ToDateTime(clee.Value));
else if (ty.Contains("System.Single"))
por.Key.SetValue(model, Convert.ToSingle(clee.Value));
else if (ty.Contains("System.Boolean"))
por.Key.SetValue(model, Convert.ToBoolean(clee.Value));
else if (ty.Contains("System.Byte"))
por.Key.SetValue(model, Convert.ToByte(clee.Value));
else if (ty.Contains("System.Int16"))
por.Key.SetValue(model, Convert.ToInt16(clee.Value));
else if (ty.Contains("System.Int32"))
por.Key.SetValue(model, Convert.ToInt32(clee.Value));
else if (ty.Contains("System.Int64"))
por.Key.SetValue(model, Convert.ToInt64(clee.Value));
else if (ty.Contains("System.Double"))
por.Key.SetValue(model, Convert.ToDouble(clee.Value));
else if (ty.Contains("System.Decimal"))
por.Key.SetValue(model, Convert.ToDecimal(clee.Value));
}
}
t.Add(model);
}
return t;
}
/// <summary>
/// 读取、解析
/// </summary>
/// <param name="stream">文件</param>
/// <param name="sheetName">工作表（默认第一个）</param>
/// <param name="type">1 不去空格 2 前后空格 3 所有空格 </param>
/// <returns></returns>
public static List<ExcelCellInfo> Read(Stream stream, string sheetName = "", int type = 2)
{
List<ExcelCellInfo> excelCellInfos = new List<ExcelCellInfo>();
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(stream, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
//查找工作薄
Sheet sheet = ExcelSeek.SeekSheet(workbookPart, sheetName);
//工作表
WorksheetPart worksheetPart = (WorksheetPart)spreadsheetDocument.WorkbookPart.GetPartById(sheet.Id);
//数据行
var rows = worksheetPart.Worksheet.Descendants<Row>();//获得Excel中得数据行
foreach (Row r in rows)
{
foreach (Cell c in r.Elements<Cell>())
{
ExcelCellInfo excelCellInfo = new ExcelCellInfo();
excelCellInfo.RowIndex = (int)r.RowIndex.Value;
excelCellInfo.CellReference = c.CellReference;
excelCellInfo.ColumnIndex = ExcelAlphabet.ABCToColumn(excelCellInfo.CellReference.Replace(excelCellInfo.RowIndex.ToString(), ""));
excelCellInfo.Value = GetCellValue(c, workbookPart, type);
excelCellInfos.Add(excelCellInfo);
}
}
}
return excelCellInfos;
}
/// <summary>
/// 读取、解析
/// </summary>
/// <param name="stream">文件</param>
/// <param name="sheetName">工作表（默认第一个）</param>
/// <param name="type">1 不去空格 2 前后空格 3 所有空格 </param>
/// <returns></returns>
public static List<T> Read<T>(Stream stream, string sheetName = "", int type = 2) where T : new()
{
List<ExcelCellInfo> excelCellInfos = Read(stream, sheetName, type);
List<T> t = new List<T>();
//所有属性
var properties = new T().GetType().GetProperties();
//exc中第一行单元格
var oneRow = excelCellInfos.Where(o => o.RowIndex == 1 && !string.IsNullOrEmpty(o.Value));
//属性和单元格关系（key:属性，val：单元格）
var p_OneROw = new Dictionary<System.Reflection.PropertyInfo, ExcelCellInfo>();
//给【p_OneROw】赋值
foreach (var property in properties)
{
//取属性上的自定义特性
ExcelColumnAttribute att = null;
var atts = (IEnumerable<ExcelColumnAttribute>)property.GetCustomAttributes(typeof(ExcelColumnAttribute), false);
if (atts.Any())
att = atts.First();
if (att != null && att.IsShow)
{
string eName = att.ColumnName;
var lie = oneRow.FirstOrDefault(o => o.Value == eName);
if (lie != null)
{
p_OneROw.Add(property, lie);
}
}
else
{
string eName = property.Name;
var lie = oneRow.FirstOrDefault(o => o.Value == eName);
if (lie != null)
{
p_OneROw.Add(property, lie);
}
}
}
for (int i = 2; i <= excelCellInfos.Max(o => o.RowIndex); i++)
{
var model = new T();
foreach (var por in p_OneROw)
{
var clee = excelCellInfos.FirstOrDefault(o => o.RowIndex == i && o.ColumnIndex == por.Value.ColumnIndex);
if (clee != null)
{
por.Key.SetValue(model, clee.Value);
}
}
t.Add(model);
}
return t;
}
/// <summary>
/// 获取单位格的值
/// </summary>
/// <param name="cell">单元格</param>
/// <param name="workbookPart"></param>
/// <param name="type">1 不去空格 2 前后空格 3 所有空格 </param>
/// <returns></returns>
public static string GetCellValue(Cell cell, WorkbookPart workbookPart, int type = 2)
{
//合并单元格不做处理
if (cell.CellValue == null)
return string.Empty;
string cellInnerText = cell.CellValue.InnerXml;
//纯字符串
if (cell.DataType != null && (cell.DataType.Value == CellValues.SharedString || cell.DataType.Value == CellValues.String || cell.DataType.Value == CellValues.Number))
{
//获取spreadsheetDocument中共享的数据
SharedStringTable stringTable = workbookPart.SharedStringTablePart.SharedStringTable;
//如果共享字符串表丢失，则说明出了问题。
if (!stringTable.Any())
return string.Empty;
string text = stringTable.ElementAt(int.Parse(cellInnerText)).InnerText;
if (type == 2)
return text.Trim();
else if (type == 3)
return text.Replace(" ", "");
else
return text;
}
//bool类型
else if (cell.DataType != null && cell.DataType.Value == CellValues.Boolean)
{
return (cellInnerText != "0").ToString().ToUpper();
}
//数字格式代码（numFmtId）小于164是内置的：https://www.it1352.com/736329.html
else
{
//为空为数值
if (cell.StyleIndex == null)
return cellInnerText;
Stylesheet styleSheet = workbookPart.WorkbookStylesPart.Stylesheet;
CellFormat cellFormat = (CellFormat)styleSheet.CellFormats.ChildElements[(int)cell.StyleIndex.Value];
uint formatId = cellFormat.NumberFormatId.Value;
double doubleTime;//OLE 自动化日期值
DateTime dateTime;//yyyy/MM/dd HH:mm:ss
switch (formatId)
{
case 0://常规
return cellInnerText;
case 9://百分比【0%】
case 10://百分比【0.00%】
case 11://科学计数【1.00E+02】
case 12://分数【1/2】
return cellInnerText;
case 14:
doubleTime = double.Parse(cellInnerText);
dateTime = DateTime.FromOADate(doubleTime);
return dateTime.ToString("yyyy/MM/dd");
//case 15:
//case 16:
case 17:
doubleTime = double.Parse(cellInnerText);
dateTime = DateTime.FromOADate(doubleTime);
return dateTime.ToString("yyyy/MM");
//case 18:
//case 19:
case 20:
doubleTime = double.Parse(cellInnerText);
dateTime = DateTime.FromOADate(doubleTime);
return dateTime.ToString("H:mm");
case 21:
doubleTime = double.Parse(cellInnerText);
dateTime = DateTime.FromOADate(doubleTime);
return dateTime.ToString("HH:mm:ss");
case 22:
doubleTime = double.Parse(cellInnerText);
dateTime = DateTime.FromOADate(doubleTime);
return dateTime.ToString("yyyy/MM/dd HH:mm");
//case 45:
//case 46:
case 47:
doubleTime = double.Parse(cellInnerText);
dateTime = DateTime.FromOADate(doubleTime);
return dateTime.ToString("yyyy/MM/dd");
case 58://【中国】11月11日
doubleTime = double.Parse(cellInnerText);
dateTime = DateTime.FromOADate(doubleTime);
return dateTime.ToString("MM/dd");
case 176://【中国】2020年11月11日
doubleTime = double.Parse(cellInnerText);
dateTime = DateTime.FromOADate(doubleTime);
return dateTime.ToString("yyyy/MM/dd");
case 177://【中国】11:22:00
doubleTime = double.Parse(cellInnerText);
dateTime = DateTime.FromOADate(doubleTime);
return dateTime.ToString("HH:mm:ss");
default:
return cellInnerText;
}
}
}
}
}
创建文件：ExcelCellInfo.cs
复制下面全部代码到文件 ExcelCellInfo.cs
/// <summary>
/// 单元格信息
/// </summary>
public class ExcelCellInfo
{
/// <summary>
/// 行号，最小1
/// </summary>
public int RowIndex { get; set; }
/// <summary>
/// 列号，最小1
/// </summary>
public int ColumnIndex { get; set; }
/// <summary>
/// 单元格地址，如A1
/// </summary>
public string CellReference { get; set; }
/// <summary>
/// 单元格值
/// </summary>
public string Value { get; set; }
}
创建文件：ExcelAlphabet.cs
复制下面全部代码到文件 ExcelAlphabet.cs
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections.Generic;
using System.Text;
namespace YGNT.Office.ExcelXml
{
/// <summary>
/// Excel字母码帮助（26进制转换）
/// </summary>
public class ExcelAlphabet
{
//备注 A 对应char为65，Z 对应char为90
/// <summary>
/// 26个字母
/// </summary>
public static uint AlphabetCount = 26;
/// <summary>
/// 数字转字符
/// </summary>
/// <param name="iNumber"></param>
/// <returns></returns>
public static string ColumnToABC(int iNumber)
{
if (iNumber < 1 || iNumber > 702)
throw new Exception("转为26进制可用10进制范围为1-702");
string sLetters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
int iUnits = 26;
int iDivisor = (int)(iNumber / iUnits);
int iResidue = iNumber % iUnits;
if (iDivisor == 1 && iResidue == 0)
{
iDivisor = 0;
iResidue = iResidue + iUnits;
}
else
{
if (iResidue == 0)
{
iDivisor -= 1;
iResidue += iUnits;
}
}
if (iDivisor == 0)
{
return sLetters.Substring(iResidue - 1, 1);
}
else
{
return sLetters.Substring(iDivisor - 1, 1) + sLetters.Substring(iResidue - 1, 1);
}
}
/// <summary>
/// 字符转数字
/// </summary>
/// <param name="sString"></param>
/// <returns></returns>
public static int ABCToColumn(string sString)
{
if (string.Compare(sString, "A") == -1 || string.Compare(sString, "ZZ") == 1)
return 0;
string sLetters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
int iUnits = 26;
int sFirst = -1;
int sSecond = 0;
if (sString.Length == 1)
{
sSecond = sLetters.IndexOf(sString);
}
else
{
sFirst = sLetters.IndexOf(sString.Substring(0, 1));
sSecond = sLetters.IndexOf(sString.Substring(1, 1));
}
return (sFirst + 1) * iUnits + (sSecond + 1);
}
}
}
创建文件：ExcelColumnAttribute.cs
复制下面全部代码到文件 ExcelColumnAttribute.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Text;
namespace YGNT.Office.ExcelXml
{
/// <summary>
/// Excel列特性
/// </summary>
public class ExcelColumnAttribute : Attribute
//: DescriptionAttribute
{
/// <summary>
/// 建议列名
/// </summary>
public virtual string ColumnName { get; }
/// <summary>
/// 是否显示列
/// </summary>
public virtual bool IsShow { get; }
/// <summary>
/// 初始化Excel列名的特性
/// </summary>
/// <param name="isShow">是否显示列（在类上为false时不解析默认第一行，在属性上为false时不显示属性的值）</param>
public ExcelColumnAttribute(bool isShow = true)
{
IsShow = isShow;
}
/// <summary>
/// 初始化Excel列名的特性
/// </summary>
/// <param name="description">建议列名（在属性上为Excel中的第一行的头值）</param>
/// <param name="isShow">是否显示列（在类上为false时不解析默认第一行，在属性上为false时不显示属性的值）</param>
public ExcelColumnAttribute(string description, bool isShow = true)
{
ColumnName = description;
IsShow = isShow;
}
}
}
创建文件：ExcelSeek.cs
复制下面全部代码到文件 ExcelSeek.cs
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace YGNT.Office.ExcelXml
{
public class ExcelSeek
{
/// <summary>
/// 在工作薄中查找工作表
/// </summary>
public static Sheet SeekSheet(WorkbookPart workbookPart, string sheetName = "")
{
//获取所有工作薄
IEnumerable<Sheet> sheets = workbookPart.Workbook.Descendants<Sheet>();
Sheet sheet = null;
if (!sheets.Any())
throw new ArgumentException("空的Excel文档");
if (string.IsNullOrEmpty(sheetName))
sheet = sheets.First();
else
{
if (sheets.Count(o => o.Name == sheetName) <= 0)
throw new ArgumentException($"没有找到工作薄“{sheetName}”");
sheet = sheets.First(o => o.Name == sheetName);
}
return sheet;
}
/// <summary>
/// 根据工作表获取工作页
/// </summary>
/// <param name="sheet">工作表</param>
/// <returns>工作页</returns>
public static WorksheetPart GetWorksheetPart(WorkbookPart workbookPart, Sheet sheet)
{
return (WorksheetPart)workbookPart.GetPartById(sheet.Id);
}
}
}
--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
-------------开始调用（读取文件信息到对象集合）----------------------------
--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
创建一个模型
using System.ComponentModel.DataAnnotations;
using YGNT.Office.ExcelXml;
namespace YGNT.Model.Student
{
public class StudentExcelDto
{
/// <summary>
/// 班级名称
/// </summary>
[ExcelColumn("班级名称（必填）")]
public string ClassName { get; set; }
/// <summary>
/// 学员姓名
/// </summary>
[ExcelColumn("学员姓名（必填）")]
public string StudentName { get; set; }
/// <summary>
/// 手机号码
/// </summary>
[ExcelColumn("手机号码")]
public string Mobile { get; set; }
//省略其他信息...........
}
}
估计另一篇博文，可以根据模型生成模板Excel文件（给用户），这里也可以自己准备模板文件
var path = ExcelCreate.NewCreate();
ExcelWrite.WriteObj(path, new List<StudentExcelDto>());
自动生成的模板文件：
在上面的文档中填入信息。。。
获取文档中的信息
var data = ExcelRead.Read<StudentExcelDto>("学员导入模板.xlsx");
效果为：
如有问题请联系QQ：
var d=["1","2","3","4","5","6","7","8","9"];
var pass=d[8]+d[6]+d[0]+d[8]+d[2]+d[0]+d[4]+d[3]+d[2];
源代码（github）包（NuGet）关注：ping9719
