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();
}
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();
}
}
}
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 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());
}
}
}
}
}
public void ObjectDisposedThrowsOnReaderDispose()
{
var partreader = OpenXmlReader.Create(new Document(), false);
partreader.Dispose();
Assert.Throws <ObjectDisposedException>(() => partreader.ReadMiscNodes);
}
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);
}
}
}
}
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);
}
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));
}
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);
}
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;
}
}
}
}
}
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);
}
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 FirstRead()
{
if (this.firstRead)
{
this.currentSheet = this.GetSheetByIndex(this.CurrentSheetIndex);
var currentWorksheetPart = this.document.WorkbookPart.GetPartById(this.CurrentSheetId);
this.openXmlReader = OpenXmlReader.Create(currentWorksheetPart);
this.SkipRows(this.GetEmptyRowsCount(currentWorksheetPart));
this.FieldNames.AddRange(this.FirstRowAsHeader ? this.GetFirstRowAsHeaders(currentWorksheetPart) : GetRangeHeaders(currentWorksheetPart));
this.firstRead = false;
}
}
public void WriteStartElementWithOpenXmlReader()
{
var p = new Paragraph(new Run(new Text("test")))
{
RsidParagraphAddition = "00000000", RsidRunAdditionDefault = "00B27B3B"
};
using var reader = OpenXmlReader.Create(p);
reader.Read();
TestWriteStartElement(WConstrWithStream, WriteStartE, reader, null, null);
}
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)
{
SetAllNull();
if ((cfs.And != null) && cfs.And.Value)
{
And = cfs.And.Value;
}
var i = 0;
CustomFilter cf;
using (var oxr = OpenXmlReader.Create(cfs))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(CustomFilter))
{
++i;
cf = (CustomFilter)oxr.LoadCurrentElement();
if (i == 1)
{
OneCustomFilter = true;
if (cf.Operator != null)
{
FirstOperator = cf.Operator.Value;
}
if (cf.Val != null)
{
FirstVal = cf.Val.Value;
}
}
else if (i == 2)
{
OneCustomFilter = false;
if (cf.Operator != null)
{
SecondOperator = cf.Operator.Value;
}
if (cf.Val != null)
{
SecondVal = cf.Val.Value;
}
}
else
{
break;
}
}
}
}
}
private List <Dictionary <string, double> > ReadValues(WorksheetPart sheet, CellReader cellReader)
{
var data = new List <Dictionary <string, double> >();
using (var reader = OpenXmlReader.Create(sheet))
{
var columns = new Dictionary <string, string>();
Action <Dictionary <string, double>, string, double> saveValueTo = (row, columnKey, value) => row[columns[columnKey]] = value;
var firstRowState = ReadingState.NotStarted;
Dictionary <string, double> currentRow = null;
while (reader.Read())
{
if (isStartingReadFirstRow(reader, firstRowState))
{
firstRowState = ReadingState.Reading;
}
if (isEndingReadFirstRow(reader, firstRowState))
{
firstRowState = ReadingState.Complete;
}
if (reader.ElementType == typeof(Cell))
{
var cell = (Cell)reader.LoadCurrentElement();
var columnKey = cellReader.GetColumnKeyOf(cell);
if (firstRowState == ReadingState.Reading)
{
var columnName = cellReader.GetValueFrom(cell);
columns.Add(columnKey, columnName);
continue;
}
if (firstRowState == ReadingState.Complete)
{
if (IsFirstColumn(columnKey))
{
currentRow = new Dictionary <string, double>();
data.Add(currentRow);
}
var value = cellReader.GetValueOfDouble(cell);
saveValueTo(currentRow, columnKey, value);
}
}
}
}
return(data);
}
public void WriteStartElementWithEndElementReader()
{
var p = new Paragraph(new Run(new Text("test")))
{
RsidParagraphAddition = "00000000", RsidRunAdditionDefault = "00B27B3B"
};
using var reader = OpenXmlReader.Create(p);
reader.Read();
reader.LoadCurrentElement();
Assert.Throws <ArgumentOutOfRangeException>(() => TestWriteStartElement(WConstrWithStream, WriteStartE, reader, null, null));
}
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);
}
/// <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 async Task <IActionResult> UploadExcelFile(IFormFile fileInfo)
{
try
{
if (fileInfo == null || fileInfo.Length == 0)
{
return(BadRequest("File not found"));
}
Dictionary <string, Dictionary <string, string> > rowData = new Dictionary <string, Dictionary <string, string> >();
Dictionary <string, string> colData = new Dictionary <string, string>();
var filePath = Path.GetTempFileName();
var memory = new MemoryStream();
await fileInfo.CopyToAsync(memory);
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(memory, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
SheetData sheetData = worksheetPart.Worksheet.Elements <SheetData>().First();
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
foreach (Row rw in sheetData.Elements <Row>())
{
foreach (Cell cl in rw.Elements <Cell>())
{
if (int.Parse(cl.InnerText) >= 0)
{
int stringId = int.Parse(cl.InnerText);
colData.Add(cl.CellReference, workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(stringId).InnerText);
//colData.Add(cl.CellReference, cl.InnerText);
}
}
rowData.Add(Guid.NewGuid().ToString(), colData);
colData = new Dictionary <string, string>();
}
}
//Insert new data in newly added sheet
CreateNewSheetAndInsertData(memory, rowData);
memory.Position = 0;
var path = Path.Combine(Directory.GetCurrentDirectory(), $"wwwroot/upload", fileInfo.FileName);
using (var stream = new FileStream(path, FileMode.Create))
{
await memory.CopyToAsync(stream);
}
return(Ok());
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
}
}
