internal void FromAutoFilter(AutoFilter af)
{
this.SetAllNull();
int iStartRowIndex = 1;
int iStartColumnIndex = 1;
int iEndRowIndex = 1;
int iEndColumnIndex = 1;
string sRef = af.Reference.Value;
if (sRef.IndexOf(":") > 0)
{
if (SLTool.FormatCellReferenceRangeToRowColumnIndex(sRef, out iStartRowIndex, out iStartColumnIndex, out iEndRowIndex, out iEndColumnIndex))
{
this.StartRowIndex = iStartRowIndex;
this.StartColumnIndex = iStartColumnIndex;
this.EndRowIndex = iEndRowIndex;
this.EndColumnIndex = iEndColumnIndex;
}
}
else
{
if (SLTool.FormatCellReferenceToRowColumnIndex(sRef, out iStartRowIndex, out iStartColumnIndex))
{
this.StartRowIndex = iStartRowIndex;
this.StartColumnIndex = iStartColumnIndex;
this.EndRowIndex = iStartRowIndex;
this.EndColumnIndex = iStartColumnIndex;
}
}
if (af.HasChildren)
{
SLFilterColumn fc;
using (OpenXmlReader oxr = OpenXmlReader.Create(af))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(FilterColumn))
{
fc = new SLFilterColumn();
fc.FromFilterColumn((FilterColumn)oxr.LoadCurrentElement());
this.FilterColumns.Add(fc);
}
else if (oxr.ElementType == typeof(SortState))
{
this.SortState = new SLSortState();
this.SortState.FromSortState((SortState)oxr.LoadCurrentElement());
this.HasSortState = true;
}
}
}
}
}
/// <summary>
/// Read rows from worksheetPart starts from a startrow and ends on the endrow
/// </summary>
/// <remarks></remarks>
/// <seealso cref=""/>
/// <param name="worksheetPart">part of a excel worksheet</param>
/// <param name="startRow">start row</param>
/// <param name="endRow">end row</param>
protected void ReadRows(WorksheetPart worksheetPart, int startRow, int endRow)
{
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 (endRow == 0)
{
if (rowNum >= startRow)
{
Row row = (Row)reader.LoadCurrentElement();
//this.errorMessages = this.errorMessages.Union(Validate(RowToList(row), Convert.ToInt32(row.RowIndex.ToString()))).ToList();
this.DataTuples.Add(ReadRow(RowToList(row), Convert.ToInt32(row.RowIndex.ToString())));
count++;
}
}
else
{
if (rowNum >= startRow && rowNum <= endRow)
{
Row row = (Row)reader.LoadCurrentElement();
if (!IsEmpty(row))
{
this.DataTuples.Add(ReadRow(RowToList(row), Convert.ToInt32(row.RowIndex.ToString())));
}
//this.errorMessages = this.errorMessages.Union(Validate(RowToList(row), Convert.ToInt32(row.RowIndex.ToString()))).ToList();
count++;
}
}
} while (reader.ReadNextSibling()); // Skip to the next row
break;
}
}
}
/// <summary>
///
/// </summary>
/// <remarks></remarks>
/// <seealso cref=""/>
/// <param name="worksheetPart"></param>
/// <param name="variableList"></param>
/// <param name="startRow"></param>
/// <param name="endRow"></param>
/// <returns></returns>
private List <List <string> > GetValuesFromRows(WorksheetPart worksheetPart, List <long> variableList, int startRow, int endRow)
{
List <List <string> > temp = new List <List <string> >();
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 (endRow == 0)
{
if (rowNum >= startRow)
{
Row row = (Row)reader.LoadCurrentElement();
temp.Add(RowToList(row, variableList));
count++;
}
}
else
{
if (rowNum >= startRow && rowNum <= endRow)
{
Row row = (Row)reader.LoadCurrentElement();
temp.Add(RowToList(row, variableList));
count++;
}
}
} while (reader.ReadNextSibling()); // Skip to the next row
break;
}
}
return(temp);
}
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);
}
}
}
}
/// <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;
}
}
}
/// <summary>
/// Reads the next row in the spreadsheet and performs some action on said row
/// </summary>
/// <param name="func">The action to perform on the row of Cells</param>
/// <returns>True if a row was found, false if not</returns>
private bool ReadNextRow(Action <Cell> func)
{
// Read until a Row object is found.
while (reader.Read())
{
if (reader.ElementType == typeof(Row))
{
if (reader.ReadFirstChild())
{
do
{
if (reader.ElementType == typeof(Cell))
{
Cell c = (Cell)reader.LoadCurrentElement();
func(c);
}
} while (reader.ReadNextSibling());
return(true);
}
// If logic reaches here, then this row had no cells.
// Skip to the next row and check.
Logger.Warn("Found a row with no cells.");
}
}
return(false);
}
internal void FromConditionalFormat(ConditionalFormat cf)
{
this.SetAllNull();
if (cf.Scope != null)
{
this.Scope = cf.Scope.Value;
}
if (cf.Type != null)
{
this.Type = cf.Type.Value;
}
if (cf.Priority != null)
{
this.Priority = cf.Priority.Value;
}
SLPivotArea pa;
using (OpenXmlReader oxr = OpenXmlReader.Create(cf))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(PivotArea))
{
pa = new SLPivotArea();
pa.FromPivotArea((PivotArea)oxr.LoadCurrentElement());
this.PivotAreas.Add(pa);
}
}
}
}
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());
}
internal void FromColorScale(X14.ColorScale cs)
{
this.SetAllNull();
SLConditionalFormattingValueObject2010 cfvo;
SLColor clr;
using (OpenXmlReader oxr = OpenXmlReader.Create(cs))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(X14.ConditionalFormattingValueObject))
{
cfvo = new SLConditionalFormattingValueObject2010();
cfvo.FromConditionalFormattingValueObject((X14.ConditionalFormattingValueObject)oxr.LoadCurrentElement());
this.Cfvos.Add(cfvo);
}
else if (oxr.ElementType == typeof(X14.Color))
{
clr = new SLColor(new List <System.Drawing.Color>(), new List <System.Drawing.Color>());
clr.FromExcel2010Color((X14.Color)oxr.LoadCurrentElement());
this.Colors.Add(clr);
}
}
}
}
internal void FromFieldGroup(FieldGroup fg)
{
this.SetAllNull();
if (fg.ParentId != null)
{
this.ParentId = fg.ParentId.Value;
}
if (fg.Base != null)
{
this.Base = fg.Base.Value;
}
using (OpenXmlReader oxr = OpenXmlReader.Create(fg))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(RangeProperties))
{
this.RangeProperties.FromRangeProperties((RangeProperties)oxr.LoadCurrentElement());
this.HasRangeProperties = true;
}
else if (oxr.ElementType == typeof(DiscreteProperties))
{
DiscreteProperties dp = (DiscreteProperties)oxr.LoadCurrentElement();
FieldItem fi;
using (OpenXmlReader oxrDiscrete = OpenXmlReader.Create(dp))
{
while (oxrDiscrete.Read())
{
if (oxrDiscrete.ElementType == typeof(FieldItem))
{
fi = (FieldItem)oxrDiscrete.LoadCurrentElement();
this.DiscreteProperties.Add(fi.Val);
}
}
}
}
else if (oxr.ElementType == typeof(GroupItems))
{
this.GroupItems.FromGroupItems((GroupItems)oxr.LoadCurrentElement());
this.HasGroupItems = true;
}
}
}
}
internal void FromEntries(Entries es)
{
this.SetAllNull();
SLMissingItem mi;
SLNumberItem ni;
SLErrorItem ei;
SLStringItem si;
using (OpenXmlReader oxr = OpenXmlReader.Create(es))
{
while (oxr.Read())
{
// make sure to add to Items first, because of the Count thing.
if (oxr.ElementType == typeof(MissingItem))
{
mi = new SLMissingItem();
mi.FromMissingItem((MissingItem)oxr.LoadCurrentElement());
this.Items.Add(new SLEntriesItemsTypeIndexPair(SLEntriesItemsType.Missing, this.MissingItems.Count));
this.MissingItems.Add(mi);
}
else if (oxr.ElementType == typeof(NumberItem))
{
ni = new SLNumberItem();
ni.FromNumberItem((NumberItem)oxr.LoadCurrentElement());
this.Items.Add(new SLEntriesItemsTypeIndexPair(SLEntriesItemsType.Number, this.NumberItems.Count));
this.NumberItems.Add(ni);
}
else if (oxr.ElementType == typeof(ErrorItem))
{
ei = new SLErrorItem();
ei.FromErrorItem((ErrorItem)oxr.LoadCurrentElement());
this.Items.Add(new SLEntriesItemsTypeIndexPair(SLEntriesItemsType.Error, this.ErrorItems.Count));
this.ErrorItems.Add(ei);
}
else if (oxr.ElementType == typeof(StringItem))
{
si = new SLStringItem();
si.FromStringItem((StringItem)oxr.LoadCurrentElement());
this.Items.Add(new SLEntriesItemsTypeIndexPair(SLEntriesItemsType.String, this.StringItems.Count));
this.StringItems.Add(si);
}
}
}
}
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;
}
internal void FromConsolidation(Consolidation c)
{
this.SetAllNull();
if (c.AutoPage != null)
{
this.AutoPage = c.AutoPage.Value;
}
Page pg;
PageItem pgi;
List <string> listPage;
SLRangeSet rs;
using (OpenXmlReader oxr = OpenXmlReader.Create(c))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(Page))
{
listPage = new List <string>();
pg = (Page)oxr.LoadCurrentElement();
using (OpenXmlReader oxrPage = OpenXmlReader.Create(pg))
{
while (oxrPage.Read())
{
if (oxrPage.ElementType == typeof(PageItem))
{
pgi = (PageItem)oxrPage.LoadCurrentElement();
listPage.Add(pgi.Name.Value);
}
}
}
this.Pages.Add(listPage);
}
else if (oxr.ElementType == typeof(RangeSet))
{
rs = new SLRangeSet();
rs.FromRangeSet((RangeSet)oxr.LoadCurrentElement());
this.RangeSets.Add(rs);
}
}
}
}
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();
}
private DataRow ProcessOneRow()
{
var dataRow = _schema.NewRow();
var colCount = 0;
_reader.ReadFirstChild();
do
{
if (_reader.ElementType == typeof(Cell))
{
Cell c = (Cell)_reader.LoadCurrentElement();
string cellValue = GetCellValue(c, "");
dataRow[colCount++] = cellValue;
}
} while (_reader.ReadNextSibling());
return(dataRow);
}
internal void FromTupleSet(TupleSet ts)
{
this.SetAllNull();
if (ts.MaxRank != null)
{
this.MaxRank = ts.MaxRank.Value;
}
if (ts.SetDefinition != null)
{
this.SetDefinition = ts.SetDefinition.Value;
}
if (ts.SortType != null)
{
this.SortType = ts.SortType.Value;
}
if (ts.QueryFailed != null)
{
this.QueryFailed = ts.QueryFailed.Value;
}
SLTuplesType tt;
using (OpenXmlReader oxr = OpenXmlReader.Create(ts))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(Tuples))
{
tt = new SLTuplesType();
tt.FromTuples((Tuples)oxr.LoadCurrentElement());
this.Tuples.Add(tt);
}
else if (oxr.ElementType == typeof(SortByTuple))
{
this.SortByTuple.FromSortByTuple((SortByTuple)oxr.LoadCurrentElement());
this.HasSortByTuple = true;
}
}
}
}
public static OpenXmlElement ToOpenXmlElement(this XElement element)
{
// Write XElement to MemoryStream.
using var stream = new MemoryStream();
element.Save(stream);
stream.Seek(0, SeekOrigin.Begin);
// Read OpenXmlElement from MemoryStream.
using OpenXmlReader reader = OpenXmlReader.Create(stream);
reader.Read();
return(reader.LoadCurrentElement());
}
internal void FromCustomFilters(CustomFilters cfs)
{
this.SetAllNull();
if (cfs.And != null && cfs.And.Value)
{
this.And = cfs.And.Value;
}
int i = 0;
CustomFilter cf;
using (OpenXmlReader oxr = OpenXmlReader.Create(cfs))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(CustomFilter))
{
++i;
cf = (CustomFilter)oxr.LoadCurrentElement();
if (i == 1)
{
this.OneCustomFilter = true;
if (cf.Operator != null)
{
this.FirstOperator = cf.Operator.Value;
}
if (cf.Val != null)
{
this.FirstVal = cf.Val.Value;
}
}
else if (i == 2)
{
this.OneCustomFilter = false;
if (cf.Operator != null)
{
this.SecondOperator = cf.Operator.Value;
}
if (cf.Val != null)
{
this.SecondVal = cf.Val.Value;
}
}
else
{
break;
}
}
}
}
}
internal void FromStringItem(StringItem si)
{
this.SetAllNull();
if (si.Val != null) this.Val = si.Val.Value;
if (si.Unused != null) this.Unused = si.Unused.Value;
if (si.Calculated != null) this.Calculated = si.Calculated.Value;
if (si.Caption != null) this.Caption = si.Caption.Value;
if (si.PropertyCount != null) this.PropertyCount = si.PropertyCount.Value;
if (si.FormatIndex != null) this.FormatIndex = si.FormatIndex.Value;
if (si.BackgroundColor != null) this.BackgroundColor = si.BackgroundColor.Value;
if (si.ForegroundColor != null) this.ForegroundColor = si.ForegroundColor.Value;
if (si.Italic != null) this.Italic = si.Italic.Value;
if (si.Underline != null) this.Underline = si.Underline.Value;
if (si.Strikethrough != null) this.Strikethrough = si.Strikethrough.Value;
if (si.Bold != null) this.Bold = si.Bold.Value;
SLTuplesType tt;
MemberPropertyIndex mpi;
using (OpenXmlReader oxr = OpenXmlReader.Create(si))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(Tuples))
{
tt = new SLTuplesType();
tt.FromTuples((Tuples)oxr.LoadCurrentElement());
this.Tuples.Add(tt);
}
else if (oxr.ElementType == typeof(MemberPropertyIndex))
{
// 0 is the default value.
mpi = (MemberPropertyIndex)oxr.LoadCurrentElement();
if (mpi.Val != null) this.MemberPropertyIndexes.Add(mpi.Val.Value);
else this.MemberPropertyIndexes.Add(0);
}
}
}
}
/// <summary>
/// Gets the header (first row) of the specified sheet
/// </summary>
/// <param name="worksheet">The sheet to use</param>
/// <returns>A list of cell values for the first row of the sheet</returns>
public IList <string> GetHeader(Worksheet worksheet)
{
try
{
List <string> result = new List <string>();
SharedStringTable sharedStrings = document.WorkbookPart.SharedStringTablePart.SharedStringTable;
WorksheetPart worksheetData = (WorksheetPart)document.WorkbookPart.GetPartById(worksheet.ID);
using (OpenXmlReader reader = OpenXmlReader.Create(worksheetData))
{
// Read until a Row object is found.
while (reader.Read())
{
if (reader.ElementType == typeof(Row))
{
if (reader.ReadFirstChild())
{
do
{
if (reader.ElementType == typeof(Cell))
{
Cell c = (Cell)reader.LoadCurrentElement();
if (c.DataType != null && c.DataType == CellValues.SharedString)
{
result.Add(sharedStrings.ElementAt(int.Parse(c.CellValue.Text)).InnerText);
}
else
{
result.Add(c.CellValue.Text);
}
}
} while (reader.ReadNextSibling());
return(result);
}
// If logic reaches here, then this row had no cells.
// Skip to the next row and check.
Logger.Warn("Found a row with no cells.");
}
}
}
throw new SpreadsheetReaderException("No header found for worksheet.");
}
catch (Exception ex)
{
Logger.Fatal(ex, "An error occurred while getting header.");
throw ex;
}
}
public override List <Model.Field> FieldDefinitions(string fileName, Model.Template template)
{
var fields = new List <Model.Field>();
int row = 0;
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false)) {
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
var cellFormats = workbookPart.WorkbookStylesPart.Stylesheet.CellFormats;
foreach (WorksheetPart worksheetPart in workbookPart.WorksheetParts)
{
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
var needHeader = true;
while (reader.Read())
{
if (reader.ElementType == typeof(Row))
{
reader.ReadFirstChild();
int columnIndex = 0;
do
{
if (reader.ElementType == typeof(Cell))
{
Cell c = (Cell)reader.LoadCurrentElement();
var cellValue = GetCellValue(workbookPart, cellFormats, c);
if (needHeader)
{
SetHeader(template.HasHeaders, fields, c, cellValue);
if (template.HasHeaders)
{
continue;
}
}
string excelColumnReference = Regex.Replace(c.CellReference, "\\d", "");
var field = fields.FirstOrDefault(f => f.ExcelColumnReference == excelColumnReference);
if (field != null && field.ColumnIndex != columnIndex)
{
fields[columnIndex].IsNullable = true;
columnIndex = field.ColumnIndex;
}
SetFieldType(fields, cellValue, c);
columnIndex++;
}
} while (reader.ReadNextSibling());
needHeader = false;
row++;
}
}
}
}
return(fields);
}
static public void TestReadRange()
{
string filePath = "C:\\Work\\AMS_SC_Inputs_201801.xlsx";
string sheetName = "US";
//open file
using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filePath, true))
{
WorkbookPart workbookPart = myDoc.WorkbookPart;
//determine ID of the Sheet
string relId = workbookPart.Workbook.Descendants <Sheet>().First(s => sheetName.Equals(s.Name)).Id;
//open reader for Sheet
WorksheetPart worksheetPart = workbookPart.GetPartById(relId) as WorksheetPart;
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();
}
}
}
}
internal void FromFilters(Filters fs)
{
this.SetAllNull();
if (fs.Blank != null && fs.Blank.Value)
{
this.Blank = fs.Blank.Value;
}
if (fs.CalendarType != null)
{
this.CalendarType = fs.CalendarType.Value;
}
if (fs.HasChildren)
{
SLFilter f;
SLDateGroupItem dgi;
using (OpenXmlReader oxr = OpenXmlReader.Create(fs))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(Filter))
{
f = new SLFilter();
f.FromFilter((Filter)oxr.LoadCurrentElement());
this.Filters.Add(f);
}
else if (oxr.ElementType == typeof(DateGroupItem))
{
dgi = new SLDateGroupItem();
dgi.FromDateGroupItem((DateGroupItem)oxr.LoadCurrentElement());
this.DateGroupItems.Add(dgi);
}
}
}
}
}
internal void FromDataBar(DataBar db)
{
this.SetAllNull();
using (OpenXmlReader oxr = OpenXmlReader.Create(db))
{
int i = 0;
SLConditionalFormatValueObject cfvo;
while (oxr.Read())
{
if (oxr.ElementType == typeof(ConditionalFormatValueObject))
{
if (i == 0)
{
cfvo = new SLConditionalFormatValueObject();
cfvo.FromConditionalFormatValueObject((ConditionalFormatValueObject)oxr.LoadCurrentElement());
this.MinimumType = this.TranslateToAutoMinMaxValues(cfvo.Type);
this.MinimumValue = cfvo.Val;
++i;
}
else if (i == 1)
{
cfvo = new SLConditionalFormatValueObject();
cfvo.FromConditionalFormatValueObject((ConditionalFormatValueObject)oxr.LoadCurrentElement());
this.MaximumType = this.TranslateToAutoMinMaxValues(cfvo.Type);
this.MaximumValue = cfvo.Val;
++i;
}
}
else if (oxr.ElementType == typeof(Color))
{
this.Color.FromSpreadsheetColor((Color)oxr.LoadCurrentElement());
}
}
}
if (db.MinLength != null)
{
this.MinLength = db.MinLength.Value;
}
if (db.MaxLength != null)
{
this.MaxLength = db.MaxLength.Value;
}
if (db.ShowValue != null)
{
this.ShowValue = db.ShowValue.Value;
}
}
public bool Read()
{
while (_reader.Read())
{
if (_reader.ElementType == typeof(Row))
{
_currentRow = _reader.LoadCurrentElement();
// skip empty rows
if (string.IsNullOrEmpty(_currentRow.InnerText))
continue;
break;
}
}
return _currentRow != null && !_reader.EOF;
}
private string GetCellValue(WorkbookPart workbook, OpenXmlReader reader)
{
OpenXML.Cell c = (OpenXML.Cell)reader.LoadCurrentElement();
if (c.DataType != null && c.DataType == OpenXML.CellValues.SharedString)
{
OpenXML.SharedStringItem ssi = workbook.SharedStringTablePart.
SharedStringTable.Elements <OpenXML.SharedStringItem>().ElementAt
(int.Parse(c.CellValue.InnerText));
return(ssi.Text.Text);
}
else
{
return(c.CellValue.InnerText);
}
}
public void LoadCurrentElementTest()
{
using (var stream = GetStream(TestDataStorage.V2FxTestFiles.Bvt.Complex2005_12rtm, true))
using (var package = WordprocessingDocument.Open(stream, true))
{
var part = package.DescendantParts().PickFirst(p => p.IsReflectable());
using (OpenXmlReader reader = OpenXmlReader.Create(part as OpenXmlPart))
{
if (reader.Read())
{
Assert.NotNull(reader.LoadCurrentElement());
}
}
}
}
internal void FromBooleanItem(BooleanItem bi)
{
this.SetAllNull();
if (bi.Val != null)
{
this.Val = bi.Val.Value;
}
if (bi.Unused != null)
{
this.Unused = bi.Unused.Value;
}
if (bi.Calculated != null)
{
this.Calculated = bi.Calculated.Value;
}
if (bi.Caption != null)
{
this.Caption = bi.Caption.Value;
}
if (bi.PropertyCount != null)
{
this.PropertyCount = bi.PropertyCount.Value;
}
MemberPropertyIndex mpi;
using (OpenXmlReader oxr = OpenXmlReader.Create(bi))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(MemberPropertyIndex))
{
// 0 is the default value.
mpi = (MemberPropertyIndex)oxr.LoadCurrentElement();
if (mpi.Val != null)
{
this.MemberPropertyIndexes.Add(mpi.Val.Value);
}
else
{
this.MemberPropertyIndexes.Add(0);
}
}
}
}
}
// [Description("O14:537826")]
public void TestRootElementOfVmlDrawingPartIsLoadedAsUnknown()
{
var file = CopyTestFiles(@"bugregression", true, "537826.vmlpart.xlsx", f => f.IsSpreadsheetFile())
.FirstOrDefault();
using (var vmldoc = SpreadsheetDocument.Open(file.FullName, false))
{
var vmlDrawingPart = vmldoc.WorkbookPart.GetPartById("rId1").GetPartById("rId1");
Assert.True(vmlDrawingPart is VmlDrawingPart);
Assert.True(((VmlDrawingPart)vmlDrawingPart).RootElement == null);
using (OpenXmlReader reader = OpenXmlReader.Create(vmlDrawingPart))
{
reader.Read();
var elem = reader.LoadCurrentElement();
Log.VerifyTrue(elem is OpenXmlUnknownElement, "elem is OpenXmlUnknownElement");
}
}
}
public void LoadCurrentElementTest()
{
var testfiles = CopyTestFiles(@"bvt");
var testfile = testfiles.FirstOrDefault().GetCopy();
using (var package = testfile.OpenPackage(true, true))
{
var part = package.DescendantParts().PickFirst(p => p.IsReflectable());
using (OpenXmlReader reader = OpenXmlReader.Create(part as OpenXmlPart))
{
if (reader.Read())
{
var rootElement = reader.LoadCurrentElement(); // This line made the exception thrown.
}
}
}
Log.Pass("No exception thrown out for OpenXmlReader.LoadCurrentElement().");
}
