private static bool Skip(OpenXmlReader reader)
{
var result = true;
if (reader != null && reader.EOF == false)
{
reader.Skip();
if (IGNORE_WHITESPACE_SETTING == false)
{
result = SkipWhitespace(reader);
}
}
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 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 void DomReaderRootCaseTest()
{
Body body = new Body(new Paragraph(new ParagraphProperties(), new Run(new Text("test"))));
//======== new test with a new reader ========
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);
reader.Close();
//======== new test with a new reader ========
reader = OpenXmlReader.Create(body);
Assert.False(reader.EOF);
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.
reader.Close();
//======== new test with a new reader ========
reader = OpenXmlReader.Create(body);
Assert.False(reader.EOF);
moved = reader.Read();
// bug #248145
// call Skip on root start
reader.Skip();
Assert.True(reader.EOF);
//======== new test with a new reader ========
reader = OpenXmlReader.Create(body);
Assert.False(reader.EOF);
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);
reader.Close();
//======== new test with a new reader ========
reader = OpenXmlReader.Create(body);
Assert.False(reader.EOF);
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();
reader.Close();
}
/// <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);
}
}
public static List <T> GetSheetData <T>(this WorksheetPart wks, WorkbookPart wkb, string shtName, int firstDataRow, Dictionary <string, string> colsToRead, bool useHeaders = false)
{
string cellValue = "";
int rowNo = -1;
string col = "";
try
{
List <T> data = new List <T>();
using (OpenXmlReader xmlReader = OpenXmlReader.Create(wks))
{
while (xmlReader.Read())
{
rowNo = -1;
//loop through the rows
if (xmlReader.ElementType == typeof(Row))
{
Dictionary <string, string> columnHeadings = new Dictionary <string, string>();
do
{
OpenXmlAttribute attri = xmlReader.Attributes.FirstOrDefault(r => r.LocalName == "r");
bool isRow;
isRow = xmlReader.HasAttributes && Int32.TryParse(attri.Value, out rowNo);
if (isRow)
{
if (useHeaders)
{
if (rowNo == 1)
{
//Read the data
xmlReader.ReadFirstChild();
do
{
if (xmlReader.ElementType == typeof(Cell))
{
Cell c = (Cell)xmlReader.LoadCurrentElement();
cellValue = ReadCell(wkb, c);
col = c.ColumnName().ToUpper();
if (colsToRead.ContainsKey(cellValue))
{
columnHeadings.Add(col, cellValue);
}
}
}while (xmlReader.ReadNextSibling());
}
}
if (rowNo >= firstDataRow)
{
//Read the data
xmlReader.ReadFirstChild();
T rowData = (T)Activator.CreateInstance(typeof(T));
PropertyInfo prop;
do
{
if (xmlReader.ElementType == typeof(Cell))
{
Cell c = (Cell)xmlReader.LoadCurrentElement();
cellValue = ReadCell(wkb, c);
col = c.ColumnName().ToUpper();
if (useHeaders)
{
if (columnHeadings.ContainsKey(col))
{
col = columnHeadings[col];
}
}
if (colsToRead.ContainsKey(col))
{
prop = typeof(T).GetProperty(colsToRead[col]);
Type t = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType;
object propVal;
if (t == typeof(DateTime))
{
if (cellValue == null)
{
propVal = null;
}
else
{
double dt;
bool parsed = double.TryParse(cellValue, out dt);
if (parsed)
{
propVal = DateTime.FromOADate(dt);
}
else
{
propVal = DateTime.Parse(cellValue);
}
}
}
else if (cellValue != null && cellValue.Contains("E") && t == typeof(decimal))
{
//Scientific notation
//Convert to decimal
propVal = decimal.Parse(cellValue, NumberStyles.Float, CultureInfo.InvariantCulture);
propVal = Convert.ChangeType(propVal, t, CultureInfo.InvariantCulture);
}
else
{
propVal = (cellValue == null) ? null : Convert.ChangeType(cellValue, t, CultureInfo.InvariantCulture);
}
prop.SetValue(rowData, propVal, null);
}
}
}while (xmlReader.ReadNextSibling());
data.Add(rowData);
}
}
}while (xmlReader.ReadNextSibling());
break;
}
}
if (xmlReader.ElementType != typeof(DocumentFormat.OpenXml.Spreadsheet.Worksheet))
{
xmlReader.Skip();
}
}
return(data);
}
catch (Exception ex)
{
throw new Exception(string.Format("Error getting sheet data [{0}, COLUMN:{1}, ROW:{2}, Value:{3}]", shtName, col, rowNo, cellValue), ex);
}
}