/// <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);
}
/// <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;
}
}
}
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());
}
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);
}
private void FindExcelRow(OpenXmlReader reader, int skip = 0)
{
while (reader.Read() && reader.ElementType != typeof(OpenXML.Row))
{
;
}
while (--skip > 0)
{
reader.ReadNextSibling();
}
}
private IList <string> GetExcelRowCells(WorkbookPart workbook, OpenXmlReader reader)
{
IList <string> cells = new List <string>();
do
{
if (reader.ElementType == typeof(OpenXML.Cell))
{
cells.Add(GetCellValue(workbook, reader));
}
} while (reader.ReadNextSibling());
return(cells);
}
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;
}
}
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();
}
}
}
}
/// <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);
}
/// <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;
}
}
}
public string Readexcel(string DocName)
{
//String xlDocName = @"C:/Users/Administrator/Desktop/Book1.xlsx";
//string dest=System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop),DocName);
string dest = "/home/imbatch1/Insight/Insight-API/Insight-Google-NLP/app/src/" + DocName;
string finaldoc = "";
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(dest, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
string cellValue = string.Empty;
foreach (WorksheetPart worksheetPart in workbookPart.WorksheetParts)
{
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();
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;
finaldoc = finaldoc + cellValue;
Console.WriteLine(cellValue);
}
}
}while (reader.ReadNextSibling());
}
}
}
Console.ReadLine();
}
File.Delete(dest);
return(finaldoc);
}
private bool NextRow(OpenXmlReader reader, int take = int.MaxValue)
{
while (reader.ReadNextSibling())
{
if (reader.ElementType == typeof(OpenXML.Row))
{
if (take-- > 0)
{
reader.ReadFirstChild();
return(true);
}
else
{
return(false);
}
}
}
return(false);
}
private Dictionary <string, string> ReadSingleRow(OpenXmlReader reader, WorkbookPart workbookPart)
{
var resultDictionary = new Dictionary <string, string>();
reader.ReadFirstChild();
do
{
if (reader.ElementType == typeof(Cell))
{
Cell c = (Cell)reader.LoadCurrentElement();
string cellValue;
#region Read cell value
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
{
if (c.CellValue != null)
{
cellValue = c.CellValue.InnerText;
}
else
{
cellValue = "";
}
}
#endregion
resultDictionary.Add(Regex.Replace(c.CellReference, @"[\d]", string.Empty), cellValue);
}
} while (reader.ReadNextSibling());
return(resultDictionary);
}
public void ReadRow(ExcelRowReaderHandler handler)
{
SharedStringTable sst = null;
SharedStringTablePart sstpart = this.document.WorkbookPart.GetPartsOfType <SharedStringTablePart>().FirstOrDefault();
if (sstpart != null)
{
sst = sstpart.SharedStringTable;
}
int rowNo = 0;
using (OpenXmlReader reader = OpenXmlReader.Create(this.part))
{
while (reader.Read())
{
if (reader.ElementType == typeof(Row))
{
reader.ReadFirstChild();
rowNo++;
ExcelRowReader row = new ExcelRowReader(reader, sst, rowNo);
if (handler(row) == false)
{
break;
}
do
{
if (reader.ElementType == typeof(Row))
{
break;
}
}while (reader.ReadNextSibling());
}
}
}
}
//gets the cells on a row as a List of objects
private static List <object> GetSAXRowArray(OpenXmlReader Reader, WorkbookPart WorkbookPart, ExcelRange Range, SharedStringItem[] SharedStringArray)
{
List <object> results = new List <object>();
Reader.ReadFirstChild();
int previousCellNo = Range.StartColumn - 1;
do
{
if (Reader.ElementType == typeof(Cell))
{
Cell c = (Cell)Reader.LoadCurrentElement();
//get current column
int currentCol = c.GetCellColumn();
if (currentCol > Range.EndColumn && Range.EndColumn != -1)
{
//we're out of the current range, we simply break
break;
}
else if (currentCol >= Range.StartColumn && (currentCol <= Range.EndColumn || Range.EndColumn == -1))
{
GetCellValue(SharedStringArray, results, previousCellNo, c, currentCol);
previousCellNo = currentCol;
}
}
} while (Reader.ReadNextSibling());
//fill in the last gaps
if (Range.EndRow != -1)
{
FillCellGaps(ref previousCellNo, Range.EndColumn, results);
}
//return resulted Rows
return(results);
}
///<summary>
///TestReaderOnPragraph.
///</summary>
private void TestReaderOnParagraph(OpenXmlReader targetReader)
{
bool moved = false;
Assert.False(targetReader.EOF);
Assert.NotNull(targetReader.Attributes);
Assert.Single(targetReader.Attributes);
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.Empty(targetReader.Attributes);
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.Empty(targetReader.Attributes);
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.Empty(targetReader.Attributes);
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.Empty(targetReader.Attributes);
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.Empty(targetReader.Attributes);
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.Empty(targetReader.Attributes);
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()));
}
public void PartReaderBasicTest()
{
string partText = "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>" +
"<w:document xmlns:w=\"http://schemas.openxmlformats.org/wordprocessingml/2006/main\">" +
"<w:body>" +
"<w:p w:rsidP=\"001\"><w:r><w:t>Run Text.</w:t><w:t>Run 2.</w:t></w:r></w:p>" +
"<w:p w:rsidP=\"001\"><w:r><w:t>Run Text.</w:t><w:t>Run 2.</w:t></w:r></w:p>" +
"</w:body>" +
"</w:document>";
UTF8Encoding utf8Encoding = new UTF8Encoding();
Stream stream = new MemoryStream(utf8Encoding.GetBytes(partText), false);
OpenXmlReader targetReader = OpenXmlReader.Create(stream);
targetReader.Read();
Assert.False(targetReader.EOF);
Assert.NotNull(targetReader.Attributes);
Assert.Empty(targetReader.Attributes);
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(Document), targetReader.ElementType);
targetReader.ReadFirstChild(); // to <w:body>
targetReader.Read(); // to <w:p>
TestReaderOnParagraph(targetReader);
Assert.False(targetReader.EOF);
Assert.NotNull(targetReader.Attributes);
Assert.Empty(targetReader.Attributes);
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()));
Assert.True(targetReader.GetLineInfo().HasLineInfo());
Assert.Equal(1, targetReader.GetLineInfo().LineNumber);
Assert.Equal(216, targetReader.GetLineInfo().LinePosition);
targetReader.ReadNextSibling(); // next <w:p>
OpenXmlElement element = targetReader.LoadCurrentElement();
// at </w:p>
Assert.False(targetReader.EOF);
Assert.NotNull(targetReader.Attributes);
Assert.Empty(targetReader.Attributes);
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()));
Assert.Equal(1, targetReader.GetLineInfo().LineNumber);
Assert.Equal(295, targetReader.GetLineInfo().LinePosition);
// loaded element is Run
Assert.NotNull(element);
Assert.IsType <Paragraph>(element);
Run run = (Run)element.FirstChild;
Assert.Equal("Run Text.", (run.FirstChild as Text).Text);
Assert.Equal("Run 2.", (run.LastChild as Text).Text);
targetReader.Close();
}
/// <summary>
///
/// </summary>
/// <remarks></remarks>
/// <seealso cref=""/>
/// <param name="worksheetPart"></param>
/// <param name="startRow"></param>
/// <param name="endRow"></param>
/// <returns></returns>
private List <VariableIdentifier> getVariableIdentifiers(WorksheetPart worksheetPart, int startRow, int endRow)
{
//NEW OPENXMLREADER
if (this.VariableIdentifiers == null || this.VariableIdentifiers.Count == 0)
{
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
int rowNum = 0;
// read variable rows to get name and id from area variable
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();
if (row.Hidden == null)
{
VariableIdentifierRows.Add(rowToList(row));
}
else if (row.Hidden != true)
{
VariableIdentifierRows.Add(rowToList(row));
}
}
} while (reader.ReadNextSibling() && rowNum < endRow); // Skip to the next row
break;
}
}
// convert variable rows to VariableIdentifiers
if (VariableIdentifierRows != null)
{
foreach (List <string> l in VariableIdentifierRows)
{
//create headerVariables
if (VariableIdentifiers.Count == 0)
{
foreach (string s in l)
{
VariableIdentifier hv = new VariableIdentifier();
hv.name = s;
VariableIdentifiers.Add(hv);
}
}
else
{
foreach (string s in l)
{
int id = Convert.ToInt32(s);
int index = l.IndexOf(s);
VariableIdentifiers.ElementAt(index).id = id;
}
}
}
}
}
if (this.VariableIdentifiers != null)
{
return(this.VariableIdentifiers);
}
else
{
return(null);
}
}
public List <List <object> > ParseSpreadSheet()
{
List <List <object> > result = new List <List <object> >();
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filePath, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
Dictionary <int, string> sharedStringCache = new Dictionary <int, string>();
int i = 0;
foreach (var el in workbookPart.SharedStringTablePart.SharedStringTable.ChildElements)
{
sharedStringCache.Add(i++, el.InnerText);
}
while (reader.Read())
{
if (reader.ElementType == typeof(Row))
{
reader.ReadFirstChild();
List <object> row = new List <object>();
do
{
if (reader.ElementType == typeof(Cell))
{
Cell c = (Cell)reader.LoadCurrentElement();
if (c == null || c.DataType == null || !c.DataType.HasValue)
{
continue;
}
object value;
switch (c.DataType.Value)
{
case CellValues.Boolean:
value = bool.Parse(c.CellValue.InnerText);
break;
case CellValues.Date:
value = DateTime.Parse(c.CellValue.InnerText);
break;
case CellValues.Number:
value = double.Parse(c.CellValue.InnerText);
break;
case CellValues.InlineString:
case CellValues.String:
value = c.CellValue.InnerText;
break;
case CellValues.SharedString:
value = sharedStringCache[int.Parse(c.CellValue.InnerText)];
break;
default:
continue;
}
if (value != null)
{
row.Add(value);
}
}
} while (reader.ReadNextSibling());
if (row.Any())
{
result.Add(row);
}
}
}
}
return(result);
}
public void OpenXMLReadValues_PortefeuilleModele2()
{
SQLCopy.MSDBIntegration s = new SQLCopy.MSDBIntegration(destinationConnection: "FGA_JMOINS1");
DataTable excelDataTable = null;
List <string> columns = new List <string>();
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(@"C:\DATA\Portefeuille modèle.xlsm", false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
WorksheetPart worksheetPart = GetWorksheetPartByName(spreadsheetDocument, "modele_€");
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
string text;
string rowNum;
while (reader.Read())
{
if (reader.ElementType == typeof(SheetData))
{
// SheetData sd = (SheetData)reader.LoadCurrentElement();
reader.ReadFirstChild();
do// while : Skip to the next row
{
if (reader.HasAttributes)
{
rowNum = reader.Attributes.First(a => a.LocalName == "r").Value;
Console.WriteLine("rowNum: " + rowNum);
}
if (reader.ElementType == typeof(Row))
{
reader.ReadFirstChild();
do// while: next Cell
{
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 if (c.CellValue != null)
{
cellValue = c.CellValue.InnerText;
}
else
{
cellValue = "Empty";
}
Console.WriteLine("{0}: {1} ", c.CellReference, cellValue);
}
} while (reader.ReadNextSibling());// while: next Cell
}
Console.WriteLine("END ROW");
} while (reader.ReadNextSibling());// while : Skip to the next row
Console.WriteLine("END ROW");
}
if (reader.ElementType != typeof(Worksheet))
{
reader.Skip();
}
}
reader.Close();
}
// s.bulkcopyData(dt: excelDataTable, nomTableDestination : "PTF_FGA");
}
public async Task <List <List <String> > > FromExcel(IFormFile files, string userID)
{
List <List <String> > lst = new List <List <String> >();
if (files == null || files.Length == 0)
{
throw new Exception("boom");
}
string filename = DateTime.Now.Year + "_" + DateTime.Now.Month + "_" + DateTime.Now.Day + "_" + DateTime.Now.Hour + "_" + DateTime.Now.Minute + "ExcelList_" + Guid.NewGuid() + ".xlsx";;
string path = Path.Combine(
Directory.GetCurrentDirectory(),
"files", "UploadedExcels", userID);
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
string filePath = Path.Combine(path, filename);
using (var stream = new FileStream(filePath, FileMode.Create))
{
await files.CopyToAsync(stream);
}
try
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filePath, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
string cellValue = string.Empty;
List <List <string> > lstExcel = new List <List <string> >();
List <string> exelRow;
foreach (WorksheetPart worksheetPart in workbookPart.WorksheetParts)
{
int rowNumber = 0;
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
while (reader.Read())
{
if (reader.ElementType == typeof(Row))
{
reader.ReadFirstChild();
// int cellNumber = 1;
exelRow = new List <string>();
do
{
if (reader.ElementType == typeof(Cell))
{
Cell c = (Cell)reader.LoadCurrentElement();
if (c.DataType != null && c.DataType == CellValues.SharedString)
{
SharedStringItem ssi = workbookPart.SharedStringTablePart
.SharedStringTable
.Elements <SharedStringItem>()
.ElementAt(Int32.Parse(c.CellValue.InnerText));
cellValue = ssi.Text.Text;
}
else
{
cellValue = c.CellValue.InnerText;
}
exelRow.Add(cellValue);
}
}while (reader.ReadNextSibling());
lstExcel.Add(exelRow);
rowNumber++;
}
}
}
//await _dbContext.Stores.AddRangeAsync(storesFromExcel);
//await _dbContext.SaveChangesAsync();
}
}
catch (Exception)
{
//file.Delete();
throw new Exception("booooom on import");
}
finally
{
//file.Delete();
}
return(lst);
}
protected override void Execute(CodeActivityContext context)
{
WorkflowDataContext dc = context.DataContext;
//get Excel context
XLExcelContextInfo customContext = Utils.GetXLExcelContextInfo(context);
//retrieve the parameters from the Context
string filePath = customContext.Path;
string sheetName = SheetName.Get(context);
string rowNum = "";
//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;
if (String.IsNullOrEmpty(relId))
{
throw new Exception("Could not indentify the Excel Sheet");
}
//open reader for Sheet
WorksheetPart worksheetPart = workbookPart.GetPartById(relId) as WorksheetPart;
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
//read the XML objects until we reach the rows
while (reader.Read())
{
//check if current elem is row
if (reader.ElementType == typeof(Row))
{
//loop through row siblings
do
{
if (reader.HasAttributes)
{
//at each step, read the current rowNum
rowNum = reader.Attributes.First(a => a.LocalName == "r").Value;
}
} while (reader.ReadNextSibling());
break;
}
}
}
int result;
//convert the result to Int and pass it as an output argument
if (Int32.TryParse(rowNum, out result))
{
NumberOfRows.Set(context, result);
}
else
{
throw new Exception("Unable to parse the line number");
}
}
/// <summary>
/// Call OpenXmlReader ReadNextSibling() method, and move the corresponding XmlReader to
/// the match point as the OpenXmlReader according to the Read algorithm.
/// </summary>
/// <param name="Oreader">Oreader to be called</param>
/// <param name="Treader">XmlReader that keeps synchronous with Oreader</param>
/// <returns>TRUE, if the ReadFirstChild success. FALSE, if not</returns>
private bool ReadNextSibling(OpenXmlReader Oreader, XmlReader Treader)
{
Log.Comment("ReadNextSibling()");
var OfoundNextSibling = Oreader.ReadNextSibling();
var foundNextSibling = false;
var oldDepth = Treader.Depth;
if (Treader.EOF)
{
foundNextSibling = false;
}
else if (Treader.Depth == 0)
{
while (!Treader.EOF && Read(Treader))
{
}
foundNextSibling = false;
}
else if (Oreader.ReadMiscNodes && IsMisc(Treader))
{
Read(Treader);
if (Treader.NodeType != XmlNodeType.EndElement)
{
foundNextSibling = true;
}
else
{
foundNextSibling = false;
}
}
else if (Treader.NodeType == XmlNodeType.EndElement)
{
if (Oreader.ReadMiscNodes)
{
Read(Treader);
}
else
{
while (Read(Treader) && (IsMisc(Treader) || Treader.Depth > oldDepth))
{
}
}
if (Treader.Depth == oldDepth && Treader.NodeType != XmlNodeType.EndElement)
{
foundNextSibling = true;
}
else
{
foundNextSibling = false;
}
}
else if (Treader.IsStartElement())
{
while (!(Treader.NodeType == XmlNodeType.EndElement && Treader.Depth == oldDepth))
{
Read(Treader);
}
Read(Treader);
if (!Oreader.ReadMiscNodes)
{
if (IsMisc(Treader))
{
while (IsMisc(Treader))
{
Read(Treader);
}
}
}
if (Treader.Depth == oldDepth && (Treader.NodeType != XmlNodeType.EndElement))
{
foundNextSibling = true;
}
else if ((Treader.Depth == oldDepth + 1 && Treader.NodeType == XmlNodeType.EndElement) || Treader.EOF)
{
foundNextSibling = false;
}
}
Log.Comment("check if the ReadNextSibling() results matches with XmlReader");
Log.VerifyTrue(OfoundNextSibling == foundNextSibling, "OpenXmlReader and XmlReader don't match");
return(OfoundNextSibling);
}
public void DomReaderMiscNodeTest()
{
Body body = new Body(new Paragraph(new ParagraphProperties(), new Run(new Text("test"))));
body.PrependChild(new OpenXmlMiscNode(System.Xml.XmlNodeType.Comment, "<!-- start body -->"));
//======== new test with a new reader ========
OpenXmlReader reader = OpenXmlReader.Create(body, true); // read misc nodes
Assert.False(reader.EOF);
bool moved = reader.Read();
Assert.True(moved);
Assert.False(reader.EOF);
Assert.Equal(0, reader.Depth);
Assert.True(reader.IsStartElement);
Assert.False(reader.IsEndElement);
Assert.False(reader.IsMiscNode);
Assert.Equal("body", reader.LocalName);
moved = reader.Read();
Assert.True(moved);
Assert.False(reader.EOF);
Assert.Equal(1, reader.Depth);
Assert.False(reader.IsStartElement);
Assert.False(reader.IsEndElement);
Assert.True(reader.IsMiscNode);
Assert.Equal("#comment", reader.LocalName);
Assert.Equal(string.Empty, reader.Prefix);
Assert.Equal(string.Empty, reader.NamespaceUri);
reader.Close();
// test case: for ReadFirstChild
reader = OpenXmlReader.Create(body, true); // read misc nodes
Assert.False(reader.EOF);
moved = reader.Read();
Assert.False(reader.EOF);
moved = reader.ReadFirstChild();
Assert.True(moved);
Assert.False(reader.EOF);
Assert.Equal(1, reader.Depth);
Assert.False(reader.IsStartElement);
Assert.False(reader.IsEndElement);
Assert.True(reader.IsMiscNode);
Assert.Equal("#comment", reader.LocalName);
Assert.Equal(string.Empty, reader.Prefix);
Assert.Equal(string.Empty, reader.NamespaceUri);
reader.Close();
OpenXmlElement miscNode = body.RemoveChild(body.FirstChild);
body.AppendChild(miscNode);
reader = OpenXmlReader.Create(body, true); // read misc nodes
Assert.False(reader.EOF);
moved = reader.Read();
Assert.False(reader.EOF);
moved = reader.ReadFirstChild();
reader.Skip();
Assert.True(moved);
Assert.False(reader.EOF);
Assert.Equal(1, reader.Depth);
Assert.False(reader.IsStartElement);
Assert.False(reader.IsEndElement);
Assert.True(reader.IsMiscNode);
Assert.Equal(string.Empty, reader.Prefix);
Assert.Equal(string.Empty, reader.NamespaceUri);
reader.Close();
// test case: root element is misc node
reader = OpenXmlReader.Create(new OpenXmlMiscNode(System.Xml.XmlNodeType.ProcessingInstruction, "<?pi test?>"), true);
moved = reader.Read();
Assert.True(moved);
Assert.False(reader.EOF);
Assert.Equal(0, reader.Depth);
Assert.False(reader.IsStartElement);
Assert.False(reader.IsEndElement);
Assert.True(reader.IsMiscNode);
Assert.Equal("pi", reader.LocalName);
Assert.Equal(string.Empty, reader.Prefix);
Assert.Equal(string.Empty, reader.NamespaceUri);
reader.Close();
// case bug #253890
body = new Body(new Paragraph(new ParagraphProperties(), new Run(new Text("test"))));
miscNode = body.AppendChild(new OpenXmlMiscNode(System.Xml.XmlNodeType.Comment, "<!-- start body -->"));
reader = OpenXmlReader.Create(body.FirstChild, true);
moved = reader.Read();
Assert.True(moved);
Assert.False(reader.EOF);
Assert.Equal(0, reader.Depth);
Assert.True(reader.IsStartElement);
Assert.False(reader.IsEndElement);
Assert.False(reader.IsMiscNode);
Assert.Equal("p", reader.LocalName);
reader.Skip();
Assert.True(reader.EOF);
reader.Close();
// case bug #253890
reader = OpenXmlReader.Create(body.FirstChild, true);
moved = reader.Read();
reader.ReadNextSibling();
Assert.True(reader.EOF);
reader.Close();
// case bug #253890
reader = OpenXmlReader.Create(body.FirstChild, true);
moved = reader.Read();
moved = reader.Read();
moved = reader.ReadNextSibling();
moved = reader.ReadNextSibling();
Assert.False(moved);
Assert.False(reader.EOF);
Assert.Equal(0, reader.Depth);
Assert.False(reader.IsStartElement);
Assert.True(reader.IsEndElement);
Assert.False(reader.IsMiscNode);
Assert.Equal("p", reader.LocalName);
// call ReadNextSibling() on EndElement
reader.ReadNextSibling();
Assert.True(reader.EOF);
}
public async Task <IActionResult> UploadExcel(ApplicantExcelUploadViewModel model)
{
if (ModelState.IsValid && model.LessonId > 0)
{
if (model.ExcelFile == null || model.ExcelFile.Length == 0)
{
return(Content("file not selected"));
}
string fileName = model.ExcelFile.FileName;
string filePath = Path.Combine(
Directory.GetCurrentDirectory(),
"wwwroot", "files", "UploadedExcels",
fileName);
using (var stream = new FileStream(filePath, FileMode.Create))
{
await model.ExcelFile.CopyToAsync(stream);
}
List <Applicant> applicantList = new List <Applicant>();
try
{
using (SpreadsheetDocument spreadsheetDocument =
SpreadsheetDocument.Open(filePath, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
string cellValue = string.Empty;
foreach (WorksheetPart worksheetPart in workbookPart.WorksheetParts)
{
int rowNumber = 0;
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
while (reader.Read())
{
int columnNumber = 0;
if (reader.ElementType == typeof(Row))
{
reader.ReadFirstChild();
Applicant applicant = new Applicant()
{
Lesson = _dbContext.Lessons.Find(model.LessonId),
Date = DateTime.Now
};
do
{
if (rowNumber == 0)
{
continue;
}
if (reader.ElementType == typeof(Cell))
{
Cell c = (Cell)reader.LoadCurrentElement();
if (c.DataType != null &&
c.DataType == CellValues.SharedString)
{
SharedStringItem ssi = workbookPart.SharedStringTablePart
.SharedStringTable
.Elements <SharedStringItem>()
.ElementAt(Int32.Parse(c.CellValue.InnerText));
var s = ssi.Text;
cellValue = ssi.Text.Text;
}
else
{
cellValue = c.CellValue.InnerText;
}
switch (columnNumber)
{
case 0:
applicant.FirstName = cellValue;
break;
case 1:
applicant.LastName = cellValue;
break;
case 2:
applicant.Email = cellValue;
break;
case 3:
applicant.Phone1 = cellValue.RemoveWhiteSpace();
break;
case 4:
if (cellValue != "x")
{
applicant.Phone2 = cellValue.RemoveWhiteSpace();
}
break;
case 7:
applicant.Date = cellValue.UNIXTimeToDateTime();
break;
default:
break;
}
}
columnNumber++;
} while (reader.ReadNextSibling());
if (rowNumber != 0)
{
applicantList.Add(applicant);
}
rowNumber++;
}
}
}
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
System.IO.File.Delete(filePath);
}
_dbContext.Applicants.AddRange(applicantList);
_dbContext.SaveChanges();
return(RedirectToAction("AllApplicants", "Applicant"));
}
return(RedirectToAction("UploadExcel"));
}
public void DomReaderRootCaseTest()
{
Body body = new Body(new Paragraph(new ParagraphProperties(), new Run(new Text("test"))));
//======== new test with a new reader ========
using (OpenXmlReader reader = OpenXmlReader.Create(body))
{
Assert.False(reader.EOF);
bool moved = reader.Read();
Assert.True(moved);
Assert.False(reader.EOF);
Assert.Equal(0, reader.Depth);
Assert.True(reader.IsStartElement);
Assert.False(reader.IsEndElement);
Assert.False(reader.IsMiscNode);
// ReadNextSibling on root start
moved = reader.ReadNextSibling();
Assert.False(moved);
Assert.True(reader.EOF);
}
//======== new test with a new reader ========
using (var reader = OpenXmlReader.Create(body))
{
Assert.False(reader.EOF);
var moved = reader.Read();
moved = reader.ReadFirstChild();
Assert.True(moved);
Assert.True(reader.IsStartElement);
Assert.False(reader.IsEndElement);
Assert.False(reader.IsMiscNode);
Assert.False(reader.EOF);
Assert.Equal(1, reader.Depth);
Assert.Equal("p", reader.LocalName);
moved = reader.ReadNextSibling();
Assert.False(moved); // no more sibling
Assert.True(reader.IsEndElement); // should on Body end now.
Assert.False(reader.IsStartElement);
Assert.False(reader.IsMiscNode);
Assert.False(reader.EOF);
Assert.Equal(0, reader.Depth);
Assert.Equal("body", reader.LocalName);
// ReadNextSibling on root end
moved = reader.ReadNextSibling();
Assert.False(moved);
Assert.True(reader.EOF); // should be EOF now.
}
//======== new test with a new reader ========
using (var reader = OpenXmlReader.Create(body))
{
Assert.False(reader.EOF);
var moved = reader.Read();
// bug #248145
// call Skip on root start
reader.Skip();
Assert.True(reader.EOF);
}
//======== new test with a new reader ========
using (var reader = OpenXmlReader.Create(body))
{
Assert.False(reader.EOF);
var moved = reader.Read();
moved = reader.ReadFirstChild();
reader.Skip();
Assert.True(reader.IsEndElement); // should on Body end now.
Assert.False(reader.IsStartElement);
Assert.False(reader.IsMiscNode);
Assert.False(reader.EOF);
Assert.Equal(0, reader.Depth);
Assert.Equal("body", reader.LocalName);
// call Skip on root end
reader.Skip();
Assert.True(reader.EOF);
}
//======== new test with a new reader ========
using (var reader = OpenXmlReader.Create(body))
{
Assert.False(reader.EOF);
var moved = reader.Read();
moved = reader.ReadFirstChild();
reader.Skip();
// call Read on root end
moved = reader.Read();
Assert.False(moved);
Assert.True(reader.EOF);
// additional operations should return false.
moved = reader.Read();
Assert.False(moved);
moved = reader.ReadFirstChild();
Assert.False(moved);
moved = reader.ReadNextSibling();
Assert.False(moved);
reader.Skip();
}
}
public bool ParsingIntoDb(string filePath)
{
try
{
using (Stream swtream = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.Read))
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filePath, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
int i = 0;
while (reader.Read())
{
if (reader.ElementType == typeof(Row))
{
if (i < 2)
{
i++;
continue;
}
reader.ReadFirstChild();
var testContact = new TestContact();
do
{
if (reader.ElementType == typeof(Cell))
{
Cell cell = (Cell)reader.LoadCurrentElement();
var cellIndex = GetColumnIndexFromName(GetColumnName(cell.CellReference));
FullingProperties(testContact, cell, workbookPart);
}
} while (reader.ReadNextSibling());
//SaveSinglDataToDb(testObject);
if (listContacts.Count == CountInChunk)
{
SaveItemsDataToDb();
}
listContacts.Add(testContact);
}
}
if (listContacts.Any())
{
SaveItemsDataToDb();
}
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(true);
}
public string GenerateJsonTable(SheetFormat sheetFormat, String worksheetUri)
{
// open excel file
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(this.fileStream, false))
{
// get workbookpart
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
_sharedStrings = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ToArray();
_stylesheet = workbookPart.WorkbookStylesPart.Stylesheet;
WorksheetPart worksheetPart = null;
foreach (Sheet worksheet in workbookPart.Workbook.Descendants <Sheet>())
{
//Get the current worksheetpart and see if it is the correct one
WorksheetPart tmp = (WorksheetPart)workbookPart.GetPartById(worksheet.Id);
if (tmp.Uri.ToString() == worksheetUri)
{
//Found the correct WorksheetPart
worksheetPart = tmp;
}
}
using (OpenXmlReader reader = OpenXmlReader.Create(worksheetPart))
{
int expectedRowIndex = 1;
while (reader.Read())
{
if (reader.ElementType == typeof(DocumentFormat.OpenXml.Spreadsheet.Row))
{
do
{
DocumentFormat.OpenXml.Spreadsheet.Row row = (DocumentFormat.OpenXml.Spreadsheet.Row)reader.LoadCurrentElement();
List <String> rowAsStringList = new List <string>();
//Since this library will ignore empty rows, check if we skipped some and add empty rows if necessary
//This will still ignore empty rows at the end of the file but those wouldn't have any influence on the indices of data & header anyway
while (row.RowIndex > expectedRowIndex)
{
List <String> dummyRow = new List <string>();
dummyRow.Add("");
table.Add(dummyRow);
expectedRowIndex++;
}
// create a new cell
Cell c = new Cell();
int expectedIndex = 0; //To check whether we skipped cells because they were empty
for (int i = 0; i < row.ChildElements.Count(); i++)
{
// get current cell at i
c = row.Elements <Cell>().ElementAt(i);
string value = "";
if (c != null)
{
//See if cells have been skipped (empty cells are not contained in the xml and therefore not contained in row.ChildElements)
//See: https://stackoverflow.com/a/3981249
// Gets the column index of the cell with data
int cellColumnIndex = (int)GetColumnIndexFromName(GetColumnName(c.CellReference));
if (expectedIndex < cellColumnIndex)
{
//We skipped one or more cells so add some blank data
do
{
rowAsStringList.Add(""); //Insert blank data
expectedIndex++;
}while (expectedIndex < cellColumnIndex);
}
//We now have the correct index and can grab the value of the cell
if (c.CellValue != null && !string.IsNullOrEmpty(c.CellValue.Text))
{
// if Value a text
if (c.DataType != null && c.DataType.HasValue && c.DataType.Value == CellValues.SharedString)
{
int sharedStringIndex = int.Parse(c.CellValue.Text, CultureInfo.InvariantCulture);
SharedStringItem sharedStringItem = _sharedStrings[sharedStringIndex];
value = sharedStringItem.InnerText;
}
//If cell contains boolean (doesn't always work for files saved with libre office)
else if (c.DataType != null && c.DataType.HasValue && c.DataType.Value == CellValues.Boolean)
{
if (c.InnerText == "1")
{
value = "true";
}
else
{
value = "false";
}
}
// not a text
else if (c.StyleIndex != null && c.StyleIndex.HasValue)
{
uint styleIndex = c.StyleIndex.Value;
CellFormat cellFormat = _stylesheet.CellFormats.ChildElements[(int)styleIndex] as CellFormat;
if (cellFormat != null && cellFormat.NumberFormatId != null && cellFormat.NumberFormatId.HasValue)
{
uint numberFormatId = cellFormat.NumberFormatId.Value;
NumberingFormat numberFormat = _stylesheet.NumberingFormats?.FirstOrDefault(numFormat => ((NumberingFormat)numFormat).NumberFormatId.Value == numberFormatId) as NumberingFormat;
//
if (numberFormat != null)
{
if (numberFormat != null && numberFormat.FormatCode != null && numberFormat.FormatCode.HasValue)
{
string formatCode = numberFormat.FormatCode.Value;
if ((formatCode.ToLower().Contains("d") && formatCode.ToLower().Contains("m")) ||
(formatCode.ToLower().Contains("m") && formatCode.ToLower().Contains("y")) ||
(formatCode.ToLower().Contains("m") && formatCode.ToLower().Contains("d")) ||
(formatCode.ToLower().Contains("h") && formatCode.ToLower().Contains("m")) ||
(formatCode.ToLower().Contains("m") && formatCode.ToLower().Contains("s"))
)
{
DateTime dateTime = DateTime.FromOADate(double.Parse(c.CellValue.Text, CultureInfo.InvariantCulture));
//value = dateTime.ToString(new CultureInfo("en-us"));
//get c# display pattern
DataTypeDisplayPattern dataTypeDisplayPattern = DataTypeDisplayPattern.GetByExcelPattern(formatCode);
value = dataTypeDisplayPattern != null?dateTime.ToString(dataTypeDisplayPattern.StringPattern) : dateTime.ToString(new CultureInfo("en-us"));
//Debug.WriteLine("----");
//Debug.WriteLine(formatCode);
}
}
} // numberformat not null end
} // (cellFormat != null && cellFormat.NumberFormatId != null && cellFormat.NumberFormatId.HasValue)
//It may happen that values are in a cell, but the associated information such as numberformat or style are missing.
// In this case, we decide to display the values, even if they are incorrect.
if (string.IsNullOrEmpty(value) && (!string.IsNullOrEmpty(c?.CellValue?.Text)))
{
value = c.CellValue.Text;
}
}
else
{
value = c.CellValue.Text;
}
rowAsStringList.Add(value);
}//end if cell value null
else
{
rowAsStringList.Add("");
}
}//end if cell null
expectedIndex++;
}//for children of row
//Check if there's a new max length for the length of a row
maxCellCount = Math.Max(maxCellCount, rowAsStringList.Count);
//Just read a row, so increase the expected index for the next one
expectedRowIndex++;
table.Add(rowAsStringList);
} while (reader.ReadNextSibling()); // Skip to the next row
break;
}
}
}
//Make sure each row has the same number of values in it
foreach (List <String> row in table)
{
while (row.Count < maxCellCount)
{
row.Add("");
}
}
//Convert the Lists to Arrays
List <String>[] rowArray = table.ToArray(); //The elements of the Array are the rows in form of String-lists
String[][] tableArray = new String[rowArray.Length][];
for (int i = 0; i < rowArray.Length; i++)
{
tableArray[i] = rowArray[i].ToArray();
}
return(JsonConvert.SerializeObject(tableArray));
}
}
/// <summary>
/// read the XMLX File and fill the DataTable in the DataSet
/// </summary>
/// <param name="dataSet"></param>
/// <param name="dataTableName"></param>
/// <returns></returns>
public new int Fill(DataSet dataSet, string dtTableName = "Table")
{
string dataTableName = dtTableName;
System.Data.DataTable dt = this.PrepareDataTable(dataSet, ref dataTableName);
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(this.XMLFilePath, false))
{
// Output
string[] headers = new string[(int)(this.EndCell_Column - this.StartCell_Column + 1)];
int nbRows = 0;
//Input: open the worksheet
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
WorksheetPart worksheetPart;
if (this.WorksheetName != null)
{
worksheetPart = GetWorksheetPartByName(spreadsheetDocument, WorksheetName);
}
else
{
worksheetPart = GetFirstWorksheetPart(spreadsheetDocument);
}
// Starting
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
string rowNum;
while (reader.Read())
{
if (reader.ElementType == typeof(SheetData))
{
// SheetData sd = (SheetData)reader.LoadCurrentElement();
reader.ReadFirstChild();
do// while : Skip to the next row
{
if (reader.ElementType == typeof(Row))
{
rowNum = null;
DataRow row = null;
if (reader.HasAttributes)
{
rowNum = reader.Attributes.First(a => a.LocalName == "r").Value;
}
if (rowNum != null)
{
reader.ReadFirstChild();
do// while: next Cell
{
if (reader.ElementType == typeof(Cell))
{
Cell c = (Cell)reader.LoadCurrentElement();
// Calculate the transformation between Cell reference inside the excel file and the output datatable
double[] coord; // coordinate in (int,int)
double[] outputColumnRow = this.GetRecordedCellXY(c.CellReference, out coord);
//1) if the cell is in the area defined by startCell/endCell
if (outputColumnRow != null)
{
string value = this.GetCellValue(c, workbookPart);
if (this.WithHeaderOnFirstRow && outputColumnRow[1] == 0)
{
KeyValuePair <string, Type> headerName = this.GetMappingColumnName(dtTableName, value);
headers[(int)outputColumnRow[0]] = headerName.Key;
dt.Columns.Add(headerName.Key, headerName.Value /*Type of column*/);
}
else
{
if (row == null)
{
row = dt.NewRow();
}
string columnName = headers[(int)outputColumnRow[0]];
if (value != null && value.Length > 0)
{
if (dt.Columns[columnName].DataType == System.Type.GetType("System.DateTime"))
{
double d;
if (double.TryParse(value, out d))
{
row.SetField <DateTime>(columnName, DateTime.FromOADate(d));
}
else
{
row.SetField <Object>(columnName, value);
}
}
else
{
row.SetField <Object>(columnName, value);
}
}
else
{
row.SetField <Object>(columnName, null);
}
}
}
KeyValuePair <string, Type> cn;
// 2) if there are Column Filling with value of the cell
if (this.columnsFilling.TryGetValue(c.CellReference, out cn))
{
string value = this.GetCellValue(c, workbookPart);
Object v;
if (cn.Value.Equals(typeof(System.DateTime)))
{
v = Convert.ToDateTime(value);
}
else
{
v = Convert.ChangeType(value, cn.Value);
}
columnsFillingConstant.Add(cn.Key, v);
}
}
} while (reader.ReadNextSibling());// while: next Cell
}
if (row != null)
{
dt.Rows.Add(row);
nbRows++;
}
}
//
} while (reader.ReadNextSibling());// while : Skip to the next row
}
if (reader.ElementType != typeof(Worksheet))
{
reader.Skip();
}
}
reader.Close();
dt.AddConstantInColumn(this.columnsFillingConstant);
return(nbRows);
}
}