// The SAX approach.
static void ReadExcelFileSAX(string fileName)
{
using (DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheetDocument = DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(fileName, false))
{
DocumentFormat.OpenXml.Packaging.WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
DocumentFormat.OpenXml.Packaging.WorksheetPart worksheetPart = System.Linq.Enumerable.First(workbookPart.WorksheetParts);
DocumentFormat.OpenXml.OpenXmlReader reader = DocumentFormat.OpenXml.OpenXmlReader.Create(worksheetPart);
string text;
while (reader.Read())
{
if (reader.ElementType == typeof(DocumentFormat.OpenXml.Spreadsheet.CellValue))
{
text = reader.GetText();
System.Console.Write(text + " ");
}
}
System.Console.WriteLine();
System.Console.ReadKey();
}
}
/// <summary>
/// Moves to the next row of current worksheet.
/// </summary>
/// <returns>True if success.</returns>
public bool MoveToNextRow(bool allowEmptyRow = false)
{
currentCellPosition = 1;
if (currentRowPosition < currentRowIndex)
{
// This row does not exist in worksheet (the row is empty)
currentRowPosition++;
return(true);
}
Type rowType = typeof(DocumentFormat.OpenXml.Spreadsheet.Row);
Type cellType = typeof(DocumentFormat.OpenXml.Spreadsheet.Cell);
while (reader.Read())
{
if (reader.IsStartElement && (reader.ElementType == rowType))
{
// Read next available row
DocumentFormat.OpenXml.OpenXmlAttribute attribute = reader.Attributes
.First(a => a.LocalName == "r");
currentRowIndex = Convert.ToInt32(attribute.Value);
// Fix: for first row with zero index (invalid)
if (currentRowIndex == 0)
{
currentRowIndex = 1;
}
currentRowPosition++;
currentCell = null;
currentCellIndex = 0;
currentCellPosition = 0;
if (reader.ReadFirstChild())
{
// Row contains cells
if (reader.IsStartElement && (reader.ElementType == cellType))
{
// Read first available cell
currentCell = (DocumentFormat.OpenXml.Spreadsheet.Cell)reader.LoadCurrentElement();
currentCellIndex = ColumnNameToIndex(GetColumnNameFromCellReference(currentCell.CellReference));
currentCellPosition = 1;
// Move to the next cell
reader.ReadNextSibling();
return(true);
}
}
else
{
// Row is empty
return(allowEmptyRow);
}
}
}
return(false);
}