/// <summary>
/// Opens worksheet by name.
/// </summary>
/// <param name="index">Worksheet name.</param>
/// <returns>True if success.</returns>
public bool OpenWorksheet(string name, bool isIgnorWorksheet = false)
{
if (string.IsNullOrEmpty(name))
{
throw new ArgumentOutOfRangeException("name", string.Format("Worksheet name should not be empty in {0}", fileName));
}
if (spreadsheetDocument == null)
{
return(false);
}
columnCount = 0;
rowCount = 0;
currentCellIndex = 0;
currentCellPosition = 0;
currentRowIndex = 0;
currentRowPosition = 0;
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = spreadsheetDocument.WorkbookPart.Workbook.Descendants <DocumentFormat.OpenXml.Spreadsheet.Sheet>().FirstOrDefault(s => string.Compare(s.Name, name, true) == 0);
if (isIgnorWorksheet)
{
if (sheet == null)
{
return(false);
}
}
else
{
if (sheet == null)
{
throw new ArgumentOutOfRangeException("name", string.Format("Worksheet {0} name was not found in {1}", name, fileName));
}
}
foreach (var worksheetPart in spreadsheetDocument.WorkbookPart.WorksheetParts)
{
if (spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart) == sheet.Id)
{
reader = DocumentFormat.OpenXml.OpenXmlReader.Create(worksheetPart);
if (reader == null)
{
return(false);
}
if (!ReadSheetDimensions())
{
return(false);
}
CacheSharedStrings();
return(true);
}
}
return(false);
}
private void CleanupResources()
{
fileName = null;
currentCell = null;
if (reader != null)
{
reader.Close();
reader.Dispose();
reader = null;
}
if (spreadsheetDocument != null)
{
spreadsheetDocument.Close();
spreadsheetDocument.Dispose();
spreadsheetDocument = null;
}
if (fileStream != null)
{
fileStream.Close();
fileStream.Dispose();
fileStream = null;
}
sharedStrings = null;
}
/// <summary>
/// Opens worksheet by index.
/// </summary>
/// <param name="index">Worksheet index (one-based).</param>
/// <returns>True if success.</returns>
public bool OpenWorksheet(int index)
{
if (spreadsheetDocument == null)
{
return(false);
}
if (index < 1 || index > spreadsheetDocument.WorkbookPart.Workbook.Descendants <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count())
{
throw new ArgumentOutOfRangeException("index", "Worksheet index is out of range");
}
columnCount = 0;
rowCount = 0;
currentCellIndex = 0;
currentCellPosition = 0;
currentRowIndex = 0;
currentRowPosition = 0;
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = spreadsheetDocument.WorkbookPart.Workbook.Descendants <DocumentFormat.OpenXml.Spreadsheet.Sheet>().ElementAt(index - 1);
foreach (var worksheetPart in spreadsheetDocument.WorkbookPart.WorksheetParts)
{
if (spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart) == sheet.Id)
{
reader = DocumentFormat.OpenXml.OpenXmlReader.Create(worksheetPart);
if (reader == null)
{
return(false);
}
if (!ReadSheetDimensions())
{
return(false);
}
CacheSharedStrings();
return(true);
}
}
return(false);
}
// 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();
}
}