从excel文件中打开xml读取
本文关键字：xml 读取 excel 文件 | 更新日期: 2023-09-27 17:58:28
我想在我的项目中实现openXml sdk 2.5。我在这个链接中做所有事情
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.IO.Packaging;
static void Main(string[] args)
{
String fileName = @"C:'OPENXML'BigData.xlsx";
// Comment one of the following lines to test the method separately.
ReadExcelFileDOM(fileName); // DOM
//ReadExcelFileSAX(fileName); // SAX
}
// The DOM approach.
// Note that the code below works only for cells that contain numeric values.
//
static void ReadExcelFileDOM(string fileName)
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();
string text;
int rowCount= sheetData.Elements<Row>().Count();
foreach (Row r in sheetData.Elements<Row>())
{
foreach (Cell c in r.Elements<Cell>())
{
text = c.CellValue.Text;
Console.Write(text + " ");
}
}
Console.WriteLine();
Console.ReadKey();
}
}
但我没有受到任何争吵。它还没有进入循环。注意：我还设置了openXml sdk 2.5我的电脑
我在下面的代码中发现，这是数值的工作。对于字符串值，它写入0 1 2。。。
private static void Main(string[] args)
{
var filePath = @"C:/OPENXML/BigData.xlsx";
using (var document = SpreadsheetDocument.Open(filePath, false))
{
var workbookPart = document.WorkbookPart;
var workbook = workbookPart.Workbook;
var sheets = workbook.Descendants<Sheet>();
foreach (var sheet in sheets)
{
var worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);
var sharedStringPart = workbookPart.SharedStringTablePart;
//var values = sharedStringPart.SharedStringTable.Elements<SharedStringItem>().ToArray();
string text;
var rows = worksheetPart.Worksheet.Descendants<Row>();
foreach (var row in rows)
{
Console.WriteLine();
int count = row.Elements<Cell>().Count();
foreach (Cell c in row.Elements<Cell>())
{
text = c.CellValue.InnerText;
Console.Write(text + " ");
}
}
}
}
Console.ReadLine();
}
你的方法似乎对我来说还不错，因为它确实"；进入循环"；。尽管如此，你也可以尝试以下方法：
void Main()
{
string fileName = @"c:'path'to'my'file.xlsx";
using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fs, false))
{
WorkbookPart workbookPart = doc.WorkbookPart;
SharedStringTablePart sstpart = workbookPart.GetPartsOfType<SharedStringTablePart>().First();
SharedStringTable sst = sstpart.SharedStringTable;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
Worksheet sheet = worksheetPart.Worksheet;
var cells = sheet.Descendants<Cell>();
var rows = sheet.Descendants<Row>();
Console.WriteLine("Row count = {0}", rows.LongCount());
Console.WriteLine("Cell count = {0}", cells.LongCount());
// One way: go through each cell in the sheet
foreach (Cell cell in cells)
{
if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
{
int ssid = int.Parse(cell.CellValue.Text);
string str = sst.ChildElements[ssid].InnerText;
Console.WriteLine("Shared string {0}: {1}", ssid, str);
}
else if (cell.CellValue != null)
{
Console.WriteLine("Cell contents: {0}", cell.CellValue.Text);
}
}
// Or... via each row
foreach (Row row in rows)
{
foreach (Cell c in row.Elements<Cell>())
{
if ((c.DataType != null) && (c.DataType == CellValues.SharedString))
{
int ssid = int.Parse(c.CellValue.Text);
string str = sst.ChildElements[ssid].InnerText;
Console.WriteLine("Shared string {0}: {1}", ssid, str);
}
else if (c.CellValue != null)
{
Console.WriteLine("Cell contents: {0}", c.CellValue.Text);
}
}
}
}
}
}
我使用文件流方法打开工作簿，因为这允许您使用共享访问权限打开它，这样您就可以同时在Excel中打开工作簿。如果工作簿在其他地方打开，则Spreadsheet.Open（…）方法将不起作用。
也许这就是您的代码不起作用的原因。
还要注意，在适当的地方使用SharedStringTable来获取单元格文本。
编辑2018-07-11：
由于这篇文章仍在获得投票，我还应该指出，在许多情况下，使用ClosedXML来操作/读取/编辑工作簿可能会容易得多。文档示例对用户非常友好，而且在我有限的经验中，编码要直接得多。请注意，它（还）没有实现所有Excel函数（例如INDEX和MATCH），这可能是问题，也可能不是问题。[这并不是说我无论如何都想尝试在OpenXML中处理INDEX和MATCH。]
我遇到了与OP相同的问题，上面的答案对我不起作用。
我认为这就是问题所在：当您在Excel中创建文档（而不是以编程方式）时，默认情况下有3张工作表，而具有Sheet1行数据的WorksheetParts是最后一个WorksheetPart元素，而不是第一个。
我通过观察文件发现了这一点。WorkbookPart.WorksheetParts在Visual Studio中，展开Results，然后查看所有子元素，直到我找到一个HasChildren=true的SheetData对象。
试试这个：
// open the document read-only
SpreadSheetDocument document = SpreadsheetDocument.Open(filePath, false);
SharedStringTable sharedStringTable = document.WorkbookPart.SharedStringTablePart.SharedStringTable;
string cellValue = null;
foreach (WorksheetPart worksheetPart in document.WorkbookPart.WorksheetParts)
{
foreach (SheetData sheetData in worksheetPart.Worksheet.Elements<SheetData>())
{
if (sheetData.HasChildren)
{
foreach (Row row in sheetData.Elements<Row>())
{
foreach (Cell cell in row.Elements<Cell>())
{
cellValue = cell.InnerText;
if (cell.DataType == CellValues.SharedString)
{
Console.WriteLine("cell val: " + sharedStringTable.ElementAt(Int32.Parse(cellValue)).InnerText);
}
else
{
Console.WriteLine("cell val: " + cellValue);
}
}
}
}
}
}
document.Close();
读取大型Excel：openxml有两种读取excel的方法：DOM和SAX。DOM占用了更多的RAM资源，因为它在内存中加载了整个xml内容（Excel文件），但它的强类型方法。另一方面，SAX是基于事件的解析。点击此处了解更多
因此，如果您面对的是大型excel文件，最好使用SAX。
下面的代码示例使用SAX方法，还处理了excel文件读取中的两个重要场景。
- openxml跳过空单元格，因此数据集将面临移位和错误索引
- 您还需要跳过空行
此函数返回单元格当时的确切实际索引，并处理第一种情况。从这里
private static int CellReferenceToIndex(Cell cell)
{
int index = 0;
string reference = cell.CellReference.ToString().ToUpper();
foreach (char ch in reference)
{
if (Char.IsLetter(ch))
{
int value = (int)ch - (int)'A';
index = (index == 0) ? value : ((index + 1) * 26) + value;
}
else
return index;
}
return index;
}
阅读excel-sax方法的代码。
//i want to import excel to data table
dt = new DataTable();
using (SpreadsheetDocument document = SpreadsheetDocument.Open(path, false))
{
WorkbookPart workbookPart = document.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
//row counter
int rcnt = 0;
while (reader.Read())
{
//find xml row element type
//to understand the element type you can change your excel file eg : test.xlsx to test.zip
//and inside that you may observe the elements in xl/worksheets/sheet.xml
//that helps to understand openxml better
if (reader.ElementType == typeof(Row))
{
//create data table row type to be populated by cells of this row
DataRow tempRow = dt.NewRow();
//***** HANDLE THE SECOND SENARIO*****
//if row has attribute means it is not a empty row
if (reader.HasAttributes)
{
//read the child of row element which is cells
//here first element
reader.ReadFirstChild();
do
{
//find xml cell element type
if (reader.ElementType == typeof(Cell))
{
Cell c = (Cell)reader.LoadCurrentElement();
string cellValue;
int actualCellIndex = CellReferenceToIndex(c);
if (c.DataType != null && c.DataType == CellValues.SharedString)
{
SharedStringItem ssi = workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(int.Parse(c.CellValue.InnerText));
cellValue = ssi.Text.Text;
}
else
{
cellValue = c.CellValue.InnerText;
}
//if row index is 0 its header so columns headers are added & also can do some headers check incase
if (rcnt == 0)
{
dt.Columns.Add(cellValue);
}
else
{
// instead of tempRow[c.CellReference] = cellValue;
tempRow[actualCellIndex] = cellValue;
}
}
}
while (reader.ReadNextSibling());
//if its not the header row so append rowdata to the datatable
if (rcnt != 0)
{
dt.Rows.Add(tempRow);
}
rcnt++;
}
}
}
}
所有内容都在接受的答案中进行了解释
这里只是一个解决问题的扩展方法
public static string GetCellText(this Cell cell, in SharedStringTable sst)
{
if (cell.CellValue is null)
return string.Empty;
if ((cell.DataType is not null) &&
(cell.DataType == CellValues.SharedString))
{
int ssid = int.Parse(cell.CellValue.Text);
return sst.ChildElements[ssid].InnerText;
}
return cell.CellValue.Text;
}