直接上代码:
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
/// 获取单元格信息 这也是官方获取值的方法
/// 参数一为单元格对象本身,参数二为WorkbookPart.SharedStringTablePart,它存储了所有以SharedStringTable方式存储数据的子元素。
static string GetValue(Cell cell, SharedStringTablePart stringTablePart)
{
if (cell.ChildElements.Count == 0)
return "";
//get cell value
String value = cell.CellValue!.InnerText;
//Look up real value from shared string table
if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
value = stringTablePart.SharedStringTable
.ChildElements[Int32.Parse(value)]
.InnerText;
return value;
}
/// 获取Excel指定工作表数据
/// 参数一为Excel文件路径,参数二为工作表名称
void GetExcelVlaue(string filePath)
{
//打开文件
SpreadsheetDocument document = SpreadsheetDocument.Open(filePath, false);
WorkbookPart? workbook = document.WorkbookPart;
//返回第一个工作表
IEnumerable<Sheet> sheets = document.WorkbookPart!.Workbook.Descendants<Sheet>();
if (sheets.Count() != 0)
{
WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id!);
Worksheet worksheet = worksheetPart.Worksheet;
IEnumerable<Row> rows = worksheet.Descendants<Row>();
foreach (Row row in rows)//获取行的值
{
foreach (Cell cell in row)
{
string columnValue = GetValue(cell, workbook!.SharedStringTablePart!);
Console.Write(columnValue+" ");
}
Console.WriteLine();
}
}
}
GetExcelVlaue("字段替换表格.xlsx");