从excel文件中打开xml读取 [英] open xml reading from excel file
问题描述
我想在我的项目中实现 openXml sdk 2.5.我在这个链接
I want to implement openXml sdk 2.5 into my project. I do everything in this link
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.IO.Packaging;
static void Main(string[] args)
{
String fileName = @"C:OPENXMLBigData.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
But i am not getting any row. It hasn't entered loop. Note: I also set up openXml sdk 2.5 my computer
我发现下面的代码适用于数值.对于字符串值，它写入 0 1 2 ...
And I find below code this is work for numeric value.For string value it writes 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();
}
推荐答案
你的方法对我来说似乎没问题——因为它确实进入了循环".不过，您也可以尝试以下操作:
Your approach seemed to work ok for me - in that it did "enter the loop". Nevertheless you could also try something like the following:
void Main()
{
string fileName = @"c:path omyfile.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(... 方法将不起作用.
I used the filestream approach to open the workbook because this allows you to open it with shared access - so that you can have the workbook open in Excel at the same time. The Spreadsheet.Open(... method won't work if the workbook is open elsewhere.
也许这就是您的代码不起作用的原因.
Perhaps that is why your code didn't work.
还要注意，在适当的地方使用 SharedStringTable 来获取单元格文本.
Note, also, the use of the SharedStringTable to get the cell text where appropriate.
编辑 2018-07-11:
由于这篇文章仍在获得投票，我还应该指出，在许多情况下，使用 可能要容易得多ClosedXML 来操作/阅读/编辑您的工作簿.文档示例对用户非常友好，根据我有限的经验，编码更加直接.请注意，它(尚未)实现所有 Excel 函数(例如 INDEX 和 MATCH)，这可能是也可能不是问题.[无论如何我都不想尝试在 OpenXML 中处理 INDEX 和 MATCH.]
Since this post is still getting votes I should also point out that in many cases it may be a lot easier to use ClosedXML to manipulate/read/edit your workbooks. The documentation examples are pretty user friendly and the coding is, in my limited experience, much more straight forward. Just be aware that it does not (yet) implement all the Excel functions (for example INDEX and MATCH) which may or may not be an issue. [Not that I would want to be trying to deal with INDEX and MATCH in OpenXML anyway.]
这篇关于从excel文件中打开xml读取的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
