public ExcelDataReader(string path, int sheetIndex = 0, bool firstRowAsHeader = true)
{
_document = SpreadsheetDocument.Open(path, false);
_sharedStrings = GetSharedStrings(_document);
var worksheetPart = _document.WorkbookPart.GetPartById(GetSheetByIndex(sheetIndex).Id.Value);
_reader = OpenXmlReader.Create(worksheetPart);
_headers = firstRowAsHeader ? GetFirstRowAsHeaders() : GetRangeHeaders(worksheetPart);
}
public ExcelDataReader(Stream stream, string sheetName, bool firstRowAsHeader = true)
{
_document = SpreadsheetDocument.Open(stream, false);
_sharedStrings = GetSharedStrings(_document);
var worksheetPart = _document.WorkbookPart.GetPartById(GetSheetByName(sheetName).Id.Value);
_reader = OpenXmlReader.Create(worksheetPart);
_headers = firstRowAsHeader ? GetFirstRowAsHeaders() : GetRangeHeaders(worksheetPart);
}
/// <summary>
/// Writes out a start element tag of the current element of the OpenXmlReader. And write the attributes in attributes.
/// </summary>
/// <param name="elementReader">The OpenXmlReader to read from. </param>
/// <param name="attributes">The attributes to be written, can be null if no attributes.</param>
public override void WriteStartElement(OpenXmlReader elementReader, IEnumerable <OpenXmlAttribute> attributes)
{
if (elementReader is null)
{
throw new ArgumentNullException(nameof(elementReader));
}
WriteStartElement(elementReader, attributes, elementReader.NamespaceDeclarations);
}
/// <summary>
/// Writes out a start element tag of the current element of the OpenXmlReader. And write the attributes in attributes.
/// </summary>
/// <param name="elementReader">The OpenXmlReader to read from. </param>
/// <param name="attributes">The attributes to be writtern, can be null if no attrbutes.</param>
/// <param name="namespaceDeclarations">The namespace declarations to be written, can be null if no namespace declarations.</param>
public override void WriteStartElement(OpenXmlReader elementReader, IEnumerable <OpenXmlAttribute> attributes, IEnumerable <KeyValuePair <string, string> > namespaceDeclarations)
{
if (elementReader == null)
{
throw new ArgumentNullException("elementReader");
}
if (elementReader.IsEndElement)
{
throw new ArgumentOutOfRangeException("elementReader");
}
if (elementReader.IsMiscNode)
{
// OpenXmlMiscNode should be written by WriteElement( );
throw new ArgumentOutOfRangeException("elementReader");
}
this.ThrowIfObjectDisposed();
this._xmlWriter.WriteStartElement(elementReader.Prefix, elementReader.LocalName, elementReader.NamespaceUri);
if (namespaceDeclarations != null)
{
foreach (var item in namespaceDeclarations)
{
this._xmlWriter.WriteAttributeString(OpenXmlElementContext.xmlnsPrefix, item.Key, OpenXmlElementContext.xmlnsUri, item.Value);
}
}
if (attributes != null)
{
// write attributes
foreach (OpenXmlAttribute attribute in attributes)
{
this._xmlWriter.WriteAttributeString(attribute.Prefix, attribute.LocalName, attribute.NamespaceUri, attribute.Value);
}
}
if (IsOpenXmlLeafTextElement(elementReader.ElementType))
{
this._isLeafTextElementStart = true;
}
else
{
this._isLeafTextElementStart = false;
}
}
/// <summary> /// Writes out a start element tag of the current element of the OpenXmlReader. And write the attributes in attributes. /// </summary> /// <param name="elementReader">The OpenXmlReader to read from. </param> /// <param name="attributes">The attributes to be writtern, can be null if no attrbutes.</param> /// <param name="namespaceDeclarations">The namespace declarations to be written, can be null if no namespace declarations.</param> public abstract void WriteStartElement(OpenXmlReader elementReader, IEnumerable <OpenXmlAttribute> attributes, IEnumerable <KeyValuePair <string, string> > namespaceDeclarations);
/// <summary> /// Writes out a start element tag of the current element of the OpenXmlReader. And write the attributes in attributes. /// </summary> /// <param name="elementReader">The OpenXmlReader to read from. </param> /// <param name="attributes">The attributes to be writtern, can be null if no attrbutes.</param> public abstract void WriteStartElement(OpenXmlReader elementReader, IEnumerable <OpenXmlAttribute> attributes);
/// <summary> /// Writes out a start element tag of the current element of the OpenXmlReader. And write all the attributes of the element. /// </summary> /// <param name="elementReader">The OpenXmlReader to read from. </param> public abstract void WriteStartElement(OpenXmlReader elementReader);
/// <summary>
/// Writes out a start element tag of the current element of the OpenXmlReader. And write the attributes in attributes.
/// </summary>
/// <param name="elementReader">The OpenXmlReader to read from. </param>
/// <param name="attributes">The attributes to be writtern, can be null if no attrbutes.</param>
public override void WriteStartElement(OpenXmlReader elementReader, IEnumerable <OpenXmlAttribute> attributes)
{
WriteStartElement(elementReader, attributes, elementReader.NamespaceDeclarations);
}
private void HandleSheetDimensionStartElement(OpenXmlReader reader)
{
var sheetRefAttr = reader.Attributes.SingleOrDefault(attr => attr.LocalName == "ref");
string sheetRefValue = sheetRefAttr.Value;
string sheetStartCellRef = sheetRefValue.Split(':')[0];
string sheetEndCellRef = sheetRefValue.Split(':')[1];
string sheetStartColumnName = GetColumnNameFromCellAddress(sheetStartCellRef);
string sheetEndColumnName = GetColumnNameFromCellAddress(sheetEndCellRef);
sheetEndColumnIndex = ColumnIndexFromName(sheetEndColumnName);
}
/// <summary> /// Writes out a start element tag of the current element of the OpenXmlReader. And write the attributes in attributes. /// </summary> /// <param name="elementReader">The OpenXmlReader to read from. </param> /// <param name="attributes">The attributes to be writtern, can be null if no attrbutes.</param> public abstract void WriteStartElement(OpenXmlReader elementReader, IEnumerable<OpenXmlAttribute> attributes);
/// <summary>
/// Writes out a start element tag of the current element of the OpenXmlReader. And write the attributes in attributes.
/// </summary>
/// <param name="elementReader">The OpenXmlReader to read from. </param>
/// <param name="attributes">The attributes to be writtern, can be null if no attrbutes.</param>
/// <param name="namespaceDeclarations">The namespace declarations to be written, can be null if no namespace declarations.</param>
public override void WriteStartElement(OpenXmlReader elementReader, IEnumerable<OpenXmlAttribute> attributes, IEnumerable<KeyValuePair<string, string>> namespaceDeclarations)
{
if (elementReader == null)
{
throw new ArgumentNullException("elementReader");
}
if (elementReader.IsEndElement)
{
throw new ArgumentOutOfRangeException("elementReader");
}
if (elementReader.IsMiscNode)
{
// OpenXmlMiscNode should be written by WriteElement( );
throw new ArgumentOutOfRangeException("elementReader");
}
this.ThrowIfObjectDisposed();
this._xmlWriter.WriteStartElement(elementReader.Prefix, elementReader.LocalName, elementReader.NamespaceUri);
if (namespaceDeclarations != null)
{
foreach (var item in namespaceDeclarations)
{
this._xmlWriter.WriteAttributeString(OpenXmlElementContext.xmlnsPrefix, item.Key, OpenXmlElementContext.xmlnsUri, item.Value);
}
}
if (attributes != null)
{
// write attributes
foreach (OpenXmlAttribute attribute in attributes)
{
this._xmlWriter.WriteAttributeString(attribute.Prefix, attribute.LocalName, attribute.NamespaceUri, attribute.Value);
}
}
if (IsOpenXmlLeafTextElement(elementReader.ElementType))
{
this._isLeafTextElementStart = true;
}
else
{
this._isLeafTextElementStart = false;
}
}
/// <summary>
/// Writes out a start element tag of the current element of the OpenXmlReader. And write all the attributes of the element.
/// </summary>
/// <param name="elementReader">The OpenXmlReader to read from. </param>
public override void WriteStartElement(OpenXmlReader elementReader)
{
WriteStartElement(elementReader, elementReader.Attributes, elementReader.NamespaceDeclarations);
}
public void Dispose()
{
if (_reader != null)
{
_reader.Close();
_reader.Dispose();
_reader = null;
}
if (_document != null)
{
_document.Dispose();
_document = null;
}
}
private void HandleInlineStringStartElement(OpenXmlReader reader)
{
hasCellValue = true;
InlineString inlineString = reader.LoadCurrentElement() as InlineString;
string text = inlineString.InnerText;
text = text.Replace("\n", " ");
text = text.Replace("\"", "'");
if (rowCount == 0)
{
// if header text is empty, do not write this column
if (text == "")
columnsToSkip.Add(currColumnIndex);
if (!headers.ContainsKey(text))
headers.Add(text, 1);
else
{
headers[text] = headers[text] + 1;
text = text + headers[text];
}
}
if (!columnsToSkip.Contains(currColumnIndex))
{
lineBuilder.Append(string.Format("{0}{1}{0}", textDelimiter, text));
// check end of row
if (currColumnIndex != sheetEndColumnIndex)
lineBuilder.Append(columnDelimiter);
}
}
private void HandleCellEndElement(OpenXmlReader reader)
{
// Sometimes blank cells have cell elements but do not create cell value elements
// Fill missing cell value element at end of cell
if (!hasCellValue)
{
if (!columnsToSkip.Contains(currColumnIndex))
{
lineBuilder.Append(string.Format("{0}{0}", textDelimiter));
// check end of row
if (currColumnIndex != sheetEndColumnIndex)
lineBuilder.Append(columnDelimiter);
}
}
// reset cell flags at end of cell
hasCellValue = false;
isSharedString = false;
numberFormatToApply = -1;
}
private void HandleCellStartElement(OpenXmlReader reader)
{
currColumnIndex++;
var cellRefAttr = reader.Attributes.SingleOrDefault(attr => attr.LocalName == "r");
string cellRefValue = cellRefAttr.Value;
string cellColumnName = GetColumnNameFromCellAddress(cellRefValue);
cellColumnIndex = ColumnIndexFromName(cellColumnName);
// Sometimes blank cells do not create cell elements
// Fill missing cell elements at beginning of next cell
if (currColumnIndex != cellColumnIndex)
{
while (currColumnIndex < cellColumnIndex)
{
if (!columnsToSkip.Contains(currColumnIndex))
{
lineBuilder.Append(string.Format("{0}{0}", textDelimiter));
// check end of row
if (currColumnIndex != sheetEndColumnIndex)
lineBuilder.Append(columnDelimiter);
}
currColumnIndex++;
}
}
var typeAttr = reader.Attributes.SingleOrDefault(attr => attr.LocalName == "t");
string typeValue = typeAttr.Value;
if (typeValue == "s") isSharedString = true;
if (!isSharedString)
{
var styleAttr = reader.Attributes.SingleOrDefault(attr => attr.LocalName == "s");
string styleValue = styleAttr.Value;
if (styleValue != null && styleValue != "")
numberFormatToApply =
Convert.ToInt32(cellFormats[Convert.ToInt16(styleValue)].NumberFormatId.Value);
}
}
private void HandleCellValueStartElement(OpenXmlReader reader)
{
string text = "";
hasCellValue = true;
if (isSharedString)
text = sharedStringItems[Convert.ToInt32(reader.GetText())].InnerText;
else
text = reader.GetText();
text = text.Replace("\n", " ");
text = text.Replace("\"", "'");
if (numberFormatToApply > -1)
{
DateTime testDateTime;
// TODO Add new number format handlers when faced
switch (numberFormatToApply)
{
case 14:
testDateTime = DateTime.FromOADate(Convert.ToDouble(text));
text = testDateTime.ToString(CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern);
break;
case 15:
testDateTime = DateTime.FromOADate(Convert.ToDouble(text));
text = testDateTime.ToString("d-MMM-yy");
break;
case 16:
testDateTime = DateTime.FromOADate(Convert.ToDouble(text));
text = testDateTime.ToString("d-MMM");
break;
case 17:
testDateTime = DateTime.FromOADate(Convert.ToDouble(text));
text = testDateTime.ToString("MMM-yy");
break;
case 18:
testDateTime = DateTime.FromOADate(Convert.ToDouble(text));
text = testDateTime.ToString("h:mm tt");
break;
case 19:
testDateTime = DateTime.FromOADate(Convert.ToDouble(text));
text = testDateTime.ToString("h:mm:ss tt");
break;
case 20:
testDateTime = DateTime.FromOADate(Convert.ToDouble(text));
text = testDateTime.ToString("h:mm");
break;
case 21:
testDateTime = DateTime.FromOADate(Convert.ToDouble(text));
text = testDateTime.ToString("h:mm:ss");
break;
case 22:
testDateTime = DateTime.FromOADate(Convert.ToDouble(text));
text = testDateTime.ToString(string.Format("{0} h:mm", CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern));
break;
default:
break;
}
}
// For header row, sometimes it is a requirement to have unique names
// If it is not, this part and the headers dictionary can be removed
if (rowCount == 0)
{
// if header text is empty, do not write this column
if (text == "")
columnsToSkip.Add(currColumnIndex);
if (!headers.ContainsKey(text))
headers.Add(text, 1);
else
{
headers[text] = headers[text] + 1;
text = text + headers[text];
}
}
if (!columnsToSkip.Contains(currColumnIndex))
{
lineBuilder.Append(string.Format("{0}{1}{0}", textDelimiter, text));
// check end of row
if (currColumnIndex != sheetEndColumnIndex)
lineBuilder.Append(columnDelimiter);
}
}
private void HandleRowEndElement(OpenXmlReader reader, StreamWriter writer)
{
// Assume accessing next cell
currColumnIndex++;
// Sometimes blank cells do not create cell elements
// Fill missing cell elements at end of row
if (currColumnIndex != sheetEndColumnIndex)
{
while (currColumnIndex <= sheetEndColumnIndex)
{
if (rowCount == 0)
{
columnsToSkip.Add(currColumnIndex);
}
if (!columnsToSkip.Contains(currColumnIndex))
{
lineBuilder.Append(string.Format("{0}{0}", textDelimiter));
// check end of row
if (currColumnIndex != sheetEndColumnIndex - 1)
lineBuilder.Append(columnDelimiter);
}
currColumnIndex++;
}
}
else
{
if (rowCount == 0)
{
columnsToSkip.Add(currColumnIndex);
}
if (!columnsToSkip.Contains(currColumnIndex))
{
lineBuilder.Append(string.Format("{0}{0}", textDelimiter));
// check end of row
if (currColumnIndex != sheetEndColumnIndex - 1)
lineBuilder.Append(columnDelimiter);
}
}
// if we exclude columns from behind, there will be trailing commas
if (lineBuilder.ToString().EndsWith(columnDelimiter))
lineBuilder.Length--;
lineBuilder.Append("\n");
string line = lineBuilder.ToString();
writer.Write(line);
rowCount++;
// reset counters at end of row
currColumnIndex = 0;
lineBuilder.Length = 0;
}
/// <summary>
/// Writes out a start element tag of the current element of the OpenXmlReader. And write the attributes in attributes.
/// </summary>
/// <param name="elementReader">The OpenXmlReader to read from. </param>
/// <param name="attributes">The attributes to be writtern, can be null if no attrbutes.</param>
public override void WriteStartElement(OpenXmlReader elementReader, IEnumerable<OpenXmlAttribute> attributes)
{
WriteStartElement(elementReader,attributes, elementReader.NamespaceDeclarations);
}
///<summary>
///TestReaderOnPragraph.
///</summary>
public void TestReaderOnParagraph(OpenXmlReader targetReader)
{
bool moved = false;
Assert.False(targetReader.EOF);
Assert.NotNull(targetReader.Attributes);
Assert.Equal(1, targetReader.Attributes.Count);
Assert.True(targetReader.HasAttributes);
Assert.True(targetReader.IsStartElement);
Assert.False(targetReader.IsEndElement);
Assert.False(targetReader.IsMiscNode);
Assert.True(string.IsNullOrEmpty(targetReader.GetText()));
Assert.Equal(typeof(Paragraph), targetReader.ElementType);
moved = targetReader.Read(); // read to <w:r>
Assert.True(moved);
Assert.False(targetReader.EOF);
Assert.NotNull(targetReader.Attributes);
Assert.Equal(0, targetReader.Attributes.Count);
Assert.False(targetReader.HasAttributes);
Assert.True(targetReader.IsStartElement);
Assert.False(targetReader.IsEndElement);
Assert.False(targetReader.IsMiscNode);
Assert.True(string.IsNullOrEmpty(targetReader.GetText()));
Assert.Equal(typeof(Run), targetReader.ElementType);
moved = targetReader.ReadFirstChild(); // read to <w:t>
Assert.True(moved);
Assert.False(targetReader.EOF);
Assert.NotNull(targetReader.Attributes);
Assert.Equal(0, targetReader.Attributes.Count);
Assert.False(targetReader.HasAttributes);
Assert.True(targetReader.IsStartElement);
Assert.False(targetReader.IsEndElement);
Assert.False(targetReader.IsMiscNode);
Assert.Equal(typeof(Text), targetReader.ElementType);
Assert.False(string.IsNullOrEmpty(targetReader.GetText()));
Assert.Equal("Run Text.", targetReader.GetText());
moved = targetReader.Read(); // read to </w:t>
Assert.True(moved);
Assert.False(targetReader.EOF);
Assert.NotNull(targetReader.Attributes);
Assert.Equal(0, targetReader.Attributes.Count);
Assert.False(targetReader.HasAttributes);
Assert.False(targetReader.IsStartElement);
Assert.True(targetReader.IsEndElement);
Assert.False(targetReader.IsMiscNode);
Assert.Equal(typeof(Text), targetReader.ElementType);
Assert.True(string.IsNullOrEmpty(targetReader.GetText()));
moved = targetReader.ReadNextSibling(); // read to <w:t>
Assert.True(moved);
Assert.False(targetReader.EOF);
Assert.NotNull(targetReader.Attributes);
Assert.Equal(0, targetReader.Attributes.Count);
Assert.False(targetReader.HasAttributes);
Assert.True(targetReader.IsStartElement);
Assert.False(targetReader.IsEndElement);
Assert.False(targetReader.IsMiscNode);
Assert.Equal(typeof(Text), targetReader.ElementType);
Assert.False(string.IsNullOrEmpty(targetReader.GetText()));
Assert.Equal("Run 2.", targetReader.GetText());
moved = targetReader.ReadNextSibling(); // read to </w:r>, skip the </w:t>
Assert.False(moved);
Assert.False(targetReader.EOF);
Assert.NotNull(targetReader.Attributes);
Assert.Equal(0, targetReader.Attributes.Count);
Assert.False(targetReader.HasAttributes);
Assert.False(targetReader.IsStartElement);
Assert.True(targetReader.IsEndElement);
Assert.False(targetReader.IsMiscNode);
Assert.Equal(typeof(Run), targetReader.ElementType);
Assert.True(string.IsNullOrEmpty(targetReader.GetText()));
moved = targetReader.Read(); // read to </w:p>
Assert.True(moved);
Assert.False(targetReader.EOF);
Assert.NotNull(targetReader.Attributes);
Assert.Equal(0, targetReader.Attributes.Count);
Assert.False(targetReader.HasAttributes);
Assert.False(targetReader.IsStartElement);
Assert.True(targetReader.IsEndElement);
Assert.False(targetReader.IsMiscNode);
Assert.Equal(typeof(Paragraph), targetReader.ElementType);
Assert.True(string.IsNullOrEmpty(targetReader.GetText()));
}
/// <summary> /// Writes out a start element tag of the current element of the OpenXmlReader. And write all the attributes of the element. /// </summary> /// <param name="elementReader">The OpenXmlReader to read from. </param> public abstract void WriteStartElement(OpenXmlReader elementReader);
/// <summary>
/// Writes out a start element tag of the current element of the OpenXmlReader. And write all the attributes of the element.
/// </summary>
/// <param name="elementReader">The OpenXmlReader to read from. </param>
public override void WriteStartElement(OpenXmlReader elementReader)
{
WriteStartElement(elementReader, elementReader.Attributes, elementReader.NamespaceDeclarations);
}
/// <summary> /// Writes out a start element tag of the current element of the OpenXmlReader. And write the attributes in attributes. /// </summary> /// <param name="elementReader">The OpenXmlReader to read from. </param> /// <param name="attributes">The attributes to be writtern, can be null if no attrbutes.</param> /// <param name="namespaceDeclarations">The namespace declarations to be written, can be null if no namespace declarations.</param> public abstract void WriteStartElement(OpenXmlReader elementReader, IEnumerable<OpenXmlAttribute> attributes, IEnumerable<KeyValuePair<string, string>> namespaceDeclarations);
public abstract DataSet GetDataSetFromExcelTab(OpenXmlReader reader, WorkbookPart workbookPart);