private void LoadSharedStringTable()
{
var sharedStringTablePart = this.spreadsheetDocument.WorkbookPart.SharedStringTablePart;
if (sharedStringTablePart == null)
{
return;
}
using (var reader = OpenXmlReader.Create(sharedStringTablePart.SharedStringTable))
{
int i = 0;
while (reader.Read())
{
if (reader.ElementType == typeof(SharedStringItem))
{
var sharedStringItem = (SharedStringItem)reader.LoadCurrentElement();
this.sharedStrings.Add(sharedStringItem.Text != null ? sharedStringItem.Text.Text : string.Empty, i.ToString());
i++;
}
}
this.originalSharedStringCount = i;
}
}
/// <summary>
/// Validate rows from file
/// </summary>
/// <remarks></remarks>
/// <seealso cref=""/>
/// <param name="worksheetPart">Part of a excel worksheet where the datastructure is located</param>
/// <param name="startRow">Rows starting</param>
/// <param name="endRow">Rows ending</param>
protected void ValidateRows(WorksheetPart worksheetPart, int startRow, int endRow)
{
//NEW OPENXMLREADER
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
int count = 0;
int rowNum = 0;
while (reader.Read())
{
if (reader.ElementType == typeof(Row))
{
do
{
if (reader.HasAttributes)
{
rowNum = Convert.ToInt32(reader.Attributes.First(a => a.LocalName == "r").Value);
}
if (rowNum >= startRow && rowNum <= endRow)
{
Row row = (Row)reader.LoadCurrentElement();
this.ErrorMessages = this.ErrorMessages.Union(ValidateRow(RowToList(row), rowNum)).ToList();
count++;
}
} while (reader.ReadNextSibling()); // Skip to the next row
break;
}
}
}
internal void FromConditionalFormatting(X14.ConditionalFormatting cf)
{
this.SetAllNull();
if (cf.Pivot != null)
{
this.Pivot = cf.Pivot.Value;
}
using (OpenXmlReader oxr = OpenXmlReader.Create(cf))
{
while (oxr.Read())
{
SLConditionalFormattingRule2010 cfr;
if (oxr.ElementType == typeof(X14.ConditionalFormattingRule))
{
cfr = new SLConditionalFormattingRule2010();
cfr.FromConditionalFormattingRule((X14.ConditionalFormattingRule)oxr.LoadCurrentElement());
this.Rules.Add(cfr);
}
else if (oxr.ElementType == typeof(Excel.ReferenceSequence))
{
Excel.ReferenceSequence refseq = (Excel.ReferenceSequence)oxr.LoadCurrentElement();
this.ReferenceSequence = SLTool.TranslateRefSeqToCellPointRange(refseq);
}
}
}
}
internal void FromColorScale(ColorScale cs)
{
SetAllNull();
SLConditionalFormatValueObject cfvo;
SLColor clr;
using (var oxr = OpenXmlReader.Create(cs))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(ConditionalFormatValueObject))
{
cfvo = new SLConditionalFormatValueObject();
cfvo.FromConditionalFormatValueObject((ConditionalFormatValueObject)oxr.LoadCurrentElement());
Cfvos.Add(cfvo);
}
else if (oxr.ElementType == typeof(Color))
{
clr = new SLColor(new List <System.Drawing.Color>(), new List <System.Drawing.Color>());
clr.FromSpreadsheetColor((Color)oxr.LoadCurrentElement());
Colors.Add(clr);
}
}
}
}
private static void ParseViaSAX(string physicalPath)
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(physicalPath, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
var sheetCount = workbookPart.Workbook.Sheets.Count();
foreach (WorksheetPart wsp in workbookPart.WorksheetParts)
{
var columnCount = wsp.Worksheet.Descendants <Column>().Count();
var rowCount = wsp.Worksheet.Descendants <Row>().Count();
var cellCount = wsp.Worksheet.Descendants <Cell>().Count();
OpenXmlReader reader = OpenXmlReader.Create(wsp);
string text;
while (reader.Read())
{
text = reader.GetText();
System.Diagnostics.Debug.WriteLine(text + "");
//if (reader.ElementType == typeof(CellValue))
//{
// text = reader.GetText();
// Console.Write(text + " ");
// System.Diagnostics.Debug.WriteLine(text + "");
//}
}
}
}
}
public void PartReaderIgnoreWhitespaceTest(bool ignoreWhitespace)
{
const string PartText = "<w:document xmlns:w=\"http://schemas.openxmlformats.org/wordprocessingml/2006/main\">" +
"<w:body>" +
"<w:p w:rsidP=\"001\"><w:r><w:t> </w:t></w:r></w:p>" +
"</w:body>" +
"</w:document>";
using var stream = new MemoryStream(Encoding.UTF8.GetBytes(PartText), false);
using var reader = OpenXmlReader.Create(stream, false, ignoreWhitespace);
Assert.False(reader.EOF);
reader.Read();
Assert.False(reader.EOF);
reader.ReadFirstChild();
Assert.True(reader.IsStartElement);
Assert.Equal(typeof(Body), reader.ElementType);
reader.Read();
Assert.True(reader.IsStartElement);
Assert.Equal(typeof(Paragraph), reader.ElementType);
reader.Read();
Assert.True(reader.IsStartElement);
Assert.Equal(typeof(Run), reader.ElementType);
reader.Read();
Assert.True(reader.IsStartElement);
Assert.Equal(typeof(Text), reader.ElementType);
Assert.Equal(ignoreWhitespace, reader.GetText() == string.Empty);
reader.Close();
}
internal void FromColorScale(X14.ColorScale cs)
{
SetAllNull();
SLConditionalFormattingValueObject2010 cfvo;
SLColor clr;
using (var oxr = OpenXmlReader.Create(cs))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(X14.ConditionalFormattingValueObject))
{
cfvo = new SLConditionalFormattingValueObject2010();
cfvo.FromConditionalFormattingValueObject(
(X14.ConditionalFormattingValueObject)oxr.LoadCurrentElement());
Cfvos.Add(cfvo);
}
else if (oxr.ElementType == typeof(X14.Color))
{
clr = new SLColor(new List <Color>(), new List <Color>());
clr.FromExcel2010Color((X14.Color)oxr.LoadCurrentElement());
Colors.Add(clr);
}
}
}
}
internal void FromIconSet(IconSet ics)
{
this.SetAllNull();
if (ics.IconSetValue != null)
{
this.IconSetType = SLIconSet.TranslateIconSetToInternalSet(ics.IconSetValue.Value);
}
if (ics.ShowValue != null)
{
this.ShowValue = ics.ShowValue.Value;
}
if (ics.Percent != null)
{
this.Percent = ics.Percent.Value;
}
if (ics.Reverse != null)
{
this.Reverse = ics.Reverse.Value;
}
using (OpenXmlReader oxr = OpenXmlReader.Create(ics))
{
SLConditionalFormatValueObject cfvo;
while (oxr.Read())
{
if (oxr.ElementType == typeof(ConditionalFormatValueObject))
{
cfvo = new SLConditionalFormatValueObject();
cfvo.FromConditionalFormatValueObject((ConditionalFormatValueObject)oxr.LoadCurrentElement());
this.Cfvos.Add(cfvo);
}
}
}
}
internal void FromConditionalFormat(ConditionalFormat cf)
{
SetAllNull();
if (cf.Scope != null)
{
Scope = cf.Scope.Value;
}
if (cf.Type != null)
{
Type = cf.Type.Value;
}
if (cf.Priority != null)
{
Priority = cf.Priority.Value;
}
SLPivotArea pa;
using (var oxr = OpenXmlReader.Create(cf))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(PivotArea))
{
pa = new SLPivotArea();
pa.FromPivotArea((PivotArea)oxr.LoadCurrentElement());
PivotAreas.Add(pa);
}
}
}
}
private IEnumerable <string> GetFirstRow()
{
ValidateImportSettings();
IList <string> cells = new List <string>();
using (FileStream fileStream = new FileStream(settings.FileName, FileMode.Open, FileAccess.Read))
{
using (SpreadsheetDocument excel = SpreadsheetDocument.Open(fileStream, false))
{
var workbook = excel.WorkbookPart;
var worksheet = workbook.WorksheetParts.First();
using (OpenXmlReader reader = OpenXmlReader.Create(worksheet))
{
try
{
FindExcelRow(reader);
if (reader.ReadFirstChild())
{
cells = GetExcelRowCells(workbook, reader);
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
}
}
return(cells);
}
/// <summary>
/// Read rows in excel file.
/// </summary>
/// <param name="importParametersId">Session Id.</param>
/// <returns>Row</returns>
private IEnumerable <Row> ReadAllRows(Guid importParametersId)
{
using (Stream stream = ImportParametersRepository.GetFileStream(importParametersId)) {
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(stream, false)) {
using (var reader = OpenXmlReader.Create(ProcessSpreadsheetDocument(spreadsheetDocument))) {
while (reader.Read())
{
if (reader.ElementType != typeof(Row))
{
continue;
}
do
{
var row = (Row)reader.LoadCurrentElement();
var celValues = ProcessRow(row);
if (celValues.Any())
{
yield return(row);
}
} while (reader.ReadNextSibling());
}
}
}
}
}
/// <summary>
/// test NameSpaceURI of the OpenXmlReader
/// </summary>
/// <param name="reader">the OpenXmlReader to be tested</param>
/// <param name="XTreader">the corresponding XmlReader</param>
private void TestNameSpaceURI(OpenXmlReader reader, XmlReader XTreader)
{
Log.Comment("Test NameSpaceURI");
Log.VerifyTrue(
XTreader.NamespaceURI.Equals(reader.NamespaceUri, StringComparison.OrdinalIgnoreCase),
"Expect: {0} <> actual: {1}", XTreader.NamespaceURI, reader.NamespaceUri);
}
public void WriteStringExceptionTest4()
{
using (var memStream = new MemoryStream())
using (OpenXmlPartWriter target = new OpenXmlPartWriter(memStream))
{
string text = "abc";
target.WriteStartDocument();
target.WriteStartElement(new Run());
var run = new Run(new Text("111"), new Text("222"));
using (var reader = OpenXmlReader.Create(run))
{
reader.Read();
reader.Read();
target.WriteStartElement(reader);
target.WriteString(reader.GetText());
reader.Read();
target.WriteEndElement();
Assert.Throws <System.InvalidOperationException>(() =>
{
target.WriteString(text); // exception
});
reader.Close();
}
}
}
public SheetOperator(WorkbookPart workbookPart, OpenXmlReader reader, Sheet currentSheet, SharedStringCache sharedStringCache)
{
this._workbookPart = workbookPart;
this._reader = reader;
this._currentSheet = currentSheet;
this._sharedStringCache = sharedStringCache;
}
public int open(string nameParam)
{
int retvalueTemp = 0;
try
{
_doc = SpreadsheetDocument.Open(nameParam, true);
}
catch (Exception ee)
{
Console.WriteLine("<ExcelWriter>" + ee.Message);
return(-1);
}
if (_doc != null)
{
_workBook = _doc.WorkbookPart;
_workSheet = _workBook.WorksheetParts.First();
_reader = OpenXmlReader.Create(_workSheet);
retvalueTemp = 1;
}
else
{
retvalueTemp = -1;
}
return(retvalueTemp);
}
/// <summary>
/// test Prefix of the OpenXmlReader
/// </summary>
/// <param name="reader">the OpenXmlReader to be tested</param>
/// <param name="XTreader">the corresponding XmlReader</param>
private void TestPrefix(OpenXmlReader reader, XmlReader XTreader)
{
Log.Comment("Test Prefix");
Log.VerifyTrue(
reader.Prefix.Equals(XTreader.Prefix, StringComparison.OrdinalIgnoreCase),
"Expect: {0} <> actual: {1}", XTreader.Prefix, reader.Prefix);
}
public static void WriteStartElement(XmlWriter writer, OpenXmlReader reader)
{
if (string.IsNullOrEmpty(reader.Prefix) || reader.Prefix.Equals("x", StringComparison.Ordinal))
{
var prolog = writer.WriteState == WriteState.Prolog;
writer.WriteStartElement(reader.LocalName, reader.NamespaceUri);
if (prolog)
{
writer.WriteAttributeString("xmlns", null, @"http://www.w3.org/2000/xmlns/", reader.NamespaceUri);
}
}
else
{
writer.WriteStartElement(reader.Prefix, reader.LocalName, reader.NamespaceUri);
}
WriteNamespace(writer, reader.NamespaceDeclarations);
WriteAttributes(writer, reader.Attributes);
var text = reader.GetText();
if (!string.IsNullOrEmpty(text))
{
writer.WriteString(text);
}
}
private void ReadCells(ImportResults import, WorkbookPart workbookPart, CellFormats cellFormats,
OpenXmlReader reader, bool needHeader, List <Model.Field> fields, int rowIndex)
{
do
{
if (reader.ElementType == typeof(Cell))
{
Cell c = (Cell)reader.LoadCurrentElement();
var cellValue = GetCellValue(workbookPart, cellFormats, c);
if (needHeader)
{
SetHeader(import.Template.HasHeaders, fields, c, cellValue);
if (import.Template.HasHeaders)
{
continue;
}
}
string excelColumnReference = Regex.Replace(c.CellReference, "\\d", "");
var field = fields.FirstOrDefault(f => f.ExcelColumnReference == excelColumnReference);
SetFieldType(fields, cellValue, c);
if (field != null)
{
field.SetValue(cellValue);
}
}
} while (reader.ReadNextSibling());
}
public void ObjectDisposedThrowsOnReaderDispose()
{
var partreader = OpenXmlReader.Create(new Document(), false);
partreader.Dispose();
Assert.Throws <ObjectDisposedException>(() => partreader.ReadMiscNodes);
}
public void WriteStartElementWithMisc()
{
var node = new OpenXmlMiscNode(XmlNodeType.Comment);
using var miscReader = OpenXmlReader.Create(node, true);
Assert.True(miscReader.Read());
Assert.Throws <ArgumentOutOfRangeException>(() => TestWriteStartElement(WConstrWithStream, WriteStartE, miscReader, null, null));
}
public ExcelRowReader(OpenXmlReader reader, SharedStringTable sst, int rowIndex)
{
this.reader = reader;
this.sst = sst;
this.RowIndex = rowIndex;
this.first = true;
}
void LeerArchivoExcel(out List <string> Datos, string nombreArchivo)
{
SpreadsheetDocument SSD = null;
if (File.Exists(Directory.GetCurrentDirectory() + @"\Assets\Informacion en Excel\" + nombreArchivo))
{
SSD = SpreadsheetDocument.Open(Directory.GetCurrentDirectory() + @"\Assets\Informacion en Excel\" + nombreArchivo, true);
}
else if (File.Exists(Directory.GetCurrentDirectory() + @"\" + nombreArchivo))
{
SSD = SpreadsheetDocument.Open(Directory.GetCurrentDirectory() + @"\" + nombreArchivo, true);
}
else
{
Debug.LogError("No se ha encontrado el archivo '" + nombreArchivo + "'. Verifique la direccion o el nombre");
Datos = null;
return;
}
WorkbookPart WBP = SSD.WorkbookPart;
WorksheetPart WSP = WBP.WorksheetParts.First();
var stringTable = WBP.GetPartsOfType <SharedStringTablePart>().FirstOrDefault();
OpenXmlReader lector = OpenXmlReader.Create(WSP);
List <string> DatosListos = new List <string>();
int contador = 0;
while (lector.Read())
{
if (lector.ElementType == typeof(Row))
{
contador++;
}
if (lector.ElementType == typeof(Cell) && contador > 1)
{
Cell Celda = (Cell)lector.LoadCurrentElement();
string valor = null;
if (Celda != null)
{
valor = Celda.InnerText;
if (Celda.DataType != null && stringTable != null)
{
string Texto = stringTable.SharedStringTable.ElementAt(int.Parse(valor)).InnerText;
if (!Texto.Contains("//"))
{
DatosListos.Add(Texto);
}
}
else if (Celda.DataType == null && valor != "")
{
DatosListos.Add(valor);
}
}
}
}
Datos = DatosListos;
}
public void DomReaderBasicTest()
{
bool moved = false;
string paragraphOuterXml = "<w:p w:rsidP=\"001\" xmlns:w=\"http://schemas.openxmlformats.org/wordprocessingml/2006/main\"><w:r><w:t>Run Text.</w:t><w:t>Run 2.</w:t></w:r></w:p>";
Paragraph para = new Paragraph(paragraphOuterXml);
OpenXmlReader targetReader = OpenXmlReader.Create(para);
targetReader.Read();
TestReaderOnParagraph(targetReader);
moved = targetReader.Read(); // should be false
Assert.False(moved);
Assert.True(targetReader.EOF);
// Assert.NotNull(targetReader.Attributes);
// Assert.Equal(0, targetReader.Attributes.Count);
// Assert.False(targetReader.HasAttributes);
// Assert.False(targetReader.IsStartElement);
// Assert.False(targetReader.IsEndElement);
// Assert.False(targetReader.IsMiscNode);
// Assert.Equal(typeof(Paragraph), targetReader.ElementType);
Assert.True(string.IsNullOrEmpty(targetReader.GetText()));
targetReader.Close();
targetReader = OpenXmlReader.Create(para);
targetReader.Read();
moved = targetReader.ReadFirstChild(); // read to <w:r>
OpenXmlElement element = targetReader.LoadCurrentElement();
// at the end of the run
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()));
// loaded element is Run
Assert.NotNull(element);
Assert.IsType(typeof(Run), element);
Run run = (Run)element;
Assert.Equal("Run Text.", (run.FirstChild as Text).Text);
Assert.Equal("Run 2.", (run.LastChild as Text).Text);
targetReader.Close();
}
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);
SkipRows(GetEmptyRowsCount(worksheetPart));
_headers = firstRowAsHeader ? GetFirstRowAsHeaders() : GetRangeHeaders(worksheetPart);
}
/// <summary>
/// test IsMiscNode of the OpenXmlReader
/// </summary>
/// <param name="reader">the OpenXmlReader to be tested</param>
/// <param name="XTreader">the corresponding XmlReader</param>
private void TestIsMiscNode(OpenXmlReader reader, XmlReader XTreader)
{
//TODO: What is MiscNode? What is the definition?
Log.Comment("Test IsMiscNode");
Log.VerifyTrue(
reader.IsMiscNode == IsMisc(XTreader),
"Expect:{0} ({2}) <> actual: {1} ({3})",
IsMisc(XTreader), reader.IsMiscNode,
XTreader.LocalName, reader.LocalName);
}
internal void FromAutoFilter(AutoFilter af)
{
SetAllNull();
var iStartRowIndex = 1;
var iStartColumnIndex = 1;
var iEndRowIndex = 1;
var iEndColumnIndex = 1;
var sRef = af.Reference.Value;
if (sRef.IndexOf(":") > 0)
{
if (SLTool.FormatCellReferenceRangeToRowColumnIndex(sRef, out iStartRowIndex, out iStartColumnIndex,
out iEndRowIndex, out iEndColumnIndex))
{
StartRowIndex = iStartRowIndex;
StartColumnIndex = iStartColumnIndex;
EndRowIndex = iEndRowIndex;
EndColumnIndex = iEndColumnIndex;
}
}
else
{
if (SLTool.FormatCellReferenceToRowColumnIndex(sRef, out iStartRowIndex, out iStartColumnIndex))
{
StartRowIndex = iStartRowIndex;
StartColumnIndex = iStartColumnIndex;
EndRowIndex = iStartRowIndex;
EndColumnIndex = iStartColumnIndex;
}
}
if (af.HasChildren)
{
SLFilterColumn fc;
using (var oxr = OpenXmlReader.Create(af))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(FilterColumn))
{
fc = new SLFilterColumn();
fc.FromFilterColumn((FilterColumn)oxr.LoadCurrentElement());
FilterColumns.Add(fc);
}
else if (oxr.ElementType == typeof(SortState))
{
SortState = new SLSortState();
SortState.FromSortState((SortState)oxr.LoadCurrentElement());
HasSortState = true;
}
}
}
}
}
private void ParseTableDefinition(ExecuteArgs param, Dictionary <string, DefinedName> cacheNames, Stream tableDefinitionPart, TableDefinitionPart newTableDefinitionPart)
{
using (var reader = OpenXmlReader.Create(tableDefinitionPart))
using (var writer = XmlWriter.Create(newTableDefinitionPart.GetStream(),
new XmlWriterSettings {
Encoding = Encoding.UTF8, CloseOutput = true
}))
{
writer.WriteStartDocument(true);
while (reader.Read())
{
if (reader.ElementType == typeof(Excel.Table))
{
var table = (Excel.Table)reader.LoadCurrentElement();
var code = param.ParseCode(table.Name);
if (code != null)
{
var reference = CellRange.Parse(table.Reference.Value);
var defName = new DefinedName
{
Name = table.Name,
Range = reference,
Code = code,
CacheValue = param.GetValue(code)
};
if (defName.CacheValue is QResult result && result.Values.Count > 0)
{
var index = reference.Start.Row + result.Values.Count;
if (index > reference.End.Row)
{
defName.NewRange = new CellRange(reference.Start, new CellReference(reference.End.Col, index));
table.Reference = defName.NewRange.ToString();
//table.TotalsRowCount = (uint)newrange.Rows;
}
defName.Table = table;
cacheNames[defName.Range.Start.ToString()] = defName;
}
}
WriteElement(writer, table);
}
else if (reader.IsStartElement)
{
WriteStartElement(writer, reader);
}
else if (reader.IsEndElement)
{
writer.WriteEndElement();
}
}
writer.WriteEndDocument();
writer.Flush();
}
}
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);
}
private void FindExcelRow(OpenXmlReader reader, int skip = 0)
{
while (reader.Read() && reader.ElementType != typeof(OpenXML.Row))
{
;
}
while (--skip > 0)
{
reader.ReadNextSibling();
}
}
public void ReadMiscNode()
{
var body = new Body(new Paragraph(new ParagraphProperties(), new Run(new Text("test"))));
body.PrependChild(new OpenXmlMiscNode(XmlNodeType.Comment, "<!-- start body -->"));
using var reader = OpenXmlReader.Create(body, true);
Assert.True(reader.Read());
Assert.True(reader.Read());
Assert.True(reader.IsMiscNode);
}
