我讨厌采取StackOverflow这样的(看似)基本的东西,但是我在过去几个小时里一直在和微软打交道,似乎是一个死胡同.我试图阅读(大型)Excel 2007电子表格,Google已经通知我,使用OpenXml SDK是一个非常受欢迎的选择.所以我给了一个镜头,阅读了一些教程,检查了微软自己的图书馆页面,并从中得到的很少.
我正在使用一个小的测试电子表格,只有一列数字和一个字符串 – 大规模测试将会稍后.我已经尝试了几个类似于我即将发布的实现,并没有人读取数据.下面的代码主要是从另一个StackOverflow线程中获取的,它似乎在工作 – 对我来说不是这样.我想我会让你们检查/调试/帮助这个版本,因为它可能比我今天写的任何东西都要坏.
static void ReadExcelFileSAX(string fileName)
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName,true))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
OpenXmlPartReader reader = new OpenXmlPartReader(worksheetPart);
string text;
string rowNum;
while (reader.Read())
{
if (reader.ElementType == typeof(Row))
{
do
{
if (reader.HasAttributes)
{
rowNum = reader.Attributes.First(a => a.LocalName == "r").Value;
Console.Write("rowNum: " + rowNum); //we never even get here,I tested it with a breakpoint
}
} while (reader.ReadNextSibling()); // Skip to the next row
Console.ReadKey();
break; // We just looped through all the rows so no need to continue reading the worksheet
}
if (reader.ElementType == typeof(Cell))
{
}
if (reader.ElementType != typeof(Worksheet)) // Dont' want to skip the contents of the worksheet
reader.Skip(); // Skip contents of any node before finding the first row.
}
reader.Close();
Console.WriteLine();
Console.ReadKey();
}
}
而且,在附注中,有没有什么好的选择使用OpenXml SDK我以某种方式错过了？
解决方法
我认为你错误的WorksheetPart读取行.
线
workbookPart.WorksheetParts.First();
获取不能的集合的第一个WorksheetPart
必须是您在Microsoft Excel中看到的第一个工作表.
所以,遍历所有WorksheetParts,你应该看到一些输出
控制台窗口.
static void ReadExcelFileSAX(string fileName)
{
using (SpreadsheetDocument spreadsheetDocument =
SpreadsheetDocument.Open(fileName,true))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
// Iterate through all WorksheetParts
foreach (WorksheetPart worksheetPart in workbookPart.WorksheetParts)
{
OpenXmlPartReader reader = new OpenXmlPartReader(worksheetPart);
string text;
string rowNum;
while (reader.Read())
{
if (reader.ElementType == typeof(Row))
{
do
{
if (reader.HasAttributes)
{
rowNum = reader.Attributes.First(a => a.LocalName == "r").Value;
Console.Write("rowNum: " + rowNum);
}
} while (reader.ReadNextSibling()); // Skip to the next row
break; // We just looped through all the rows so no
// need to continue reading the worksheet
}
if (reader.ElementType != typeof(Worksheet))
reader.Skip();
}
reader.Close();
}
}
}
要读取所有单元格值,请使用以下功能(省略所有错误处理内容)：
static void ReadAllCellValues(string fileName)
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName,false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
foreach(WorksheetPart worksheetPart in workbookPart.WorksheetParts)
{
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
while (reader.Read())
{
if (reader.ElementType == typeof(Row))
{
reader.ReadFirstChild();
do
{
if (reader.ElementType == typeof(Cell))
{
Cell c = (Cell)reader.LoadCurrentElement();
string cellValue;
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;
}
Console.Out.Write("{0}: {1} ",c.CellReference,cellValue);
}
} while (reader.ReadNextSibling());
Console.Out.WriteLine();
}
}
}
}
}
在上面的代码中,您可以看到使用数据类型为SharedString的单元格SharedStringTablePart.
