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>
/// 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>
/// Call OpenXmlReader ReadFirstChild() method, and move the corresponding XmlReader to
/// the match point as the OpenXmlReader according to the Read algorithm. If ReadFirstChild
/// return false, the next element will be read instead.
/// </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 ReadFirstChildOrMoveToNext(OpenXmlReader Oreader, XmlReader Treader)
{
Log.Comment("ReadFirstChild()");
if (Treader.EOF || IsMisc(Treader) || Treader.NodeType == XmlNodeType.EndElement || Oreader.IsEndElement)
{
Log.VerifyFalse(Oreader.ReadFirstChild(), "ReadFirstChild on EOF, expect: False Actual: True");
Read(Oreader, Treader);
return(false);
}
else if (Treader.IsStartElement())
{
var IsStart = Oreader.IsStartElement;
var IsOreadSuccessful = Oreader.ReadFirstChild();
var skip = (IsStart == true) && (Oreader.IsStartElement == false);
if (IsOreadSuccessful)
{
if (Oreader.ReadMiscNodes)
{
Read(Treader);
}
else
{
while (Read(Treader) && IsMisc(Treader))
{
}
}
return(true);
}
else
{
if (!(Treader.IsEmptyElement && Oreader.IsEndElement && skip))
{
while (Read(Treader) && Treader.NodeType != XmlNodeType.EndElement)
{
}
}
return(false);
}
}
return(false);
}
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);
}
/// <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();
}
}
}
}
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);
}
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());
}
}
}
}
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);
}
internal void fastImport(ImportResults import)
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(import.ImportFileName, false)) {
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
var cellFormats = workbookPart.WorkbookStylesPart.Stylesheet.CellFormats;
var worksheetPart = GetWorksheetPart(workbookPart, import);
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
var needHeader = true;
List <Model.Field> fields = new List <Model.Field>();
int rowIndex = 1;
while (reader.Read())
{
if (reader.ElementType == typeof(Row))
{
reader.ReadFirstChild();
fields.ForEach(f => f.Value = DBNull.Value);
ReadCells(import, workbookPart, cellFormats, reader, needHeader, fields, rowIndex);
if (needHeader)
{
needHeader = false;
if (!import.Template.HasGoodColumnMatch(fields))
{
import.Message = $"File formatting issue: file,{import.ImportFileName}, columns do not match the reference file,{import.Template.ReferenceFile}";
this.ShouldAttemptInsert = false;
return;
}
if (import.Template.HasHeaders)
{
continue;
}
}
this.Insert(fields, import, rowIndex);
rowIndex++;
}
}
}
}
//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);
}
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();
}
// The SAX approach.
public static bool FileToDatabase(string fileName, string sqlTblGPE)
{
MakeDataTable();
try
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
string rowNum;
while (reader.Read())
{
//skip three rows
if (reader.ElementType == typeof(Row))
{
reader.ReadFirstChild();
if (reader.HasAttributes)
{
rowNum = reader.Attributes.First(a => a.LocalName == "r").Value;
//Avoid the first three columns, and another that has headers
if ("A1-A2-A3-A4".LastIndexOf(rowNum) != -1)
{
do
{
} while (reader.ReadNextSibling()); //read at the end of the row
continue; //read next from the main while
}
}
//Load in datatable, preparing datarow
DataRow row = dt.NewRow();
do
{
if (reader.ElementType == typeof(Cell))
{
Cell c = (Cell)reader.LoadCurrentElement();
//Only cols [R,V,Z,AC,AF,AJ,BJ], avoid any other columns
var check = onlyCols.Any(colStart => c.CellReference.Value.StartsWith(colStart));
if (check)
{
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 == null ? null : c.CellValue.InnerText;
}
//Console.Out.Write("{0}: {1} ", c.CellReference, cellValue);
row[ExcelColNameFromReferenc(c.CellReference)] = cellValue;
}
}
} while (reader.ReadNextSibling());
//Add datarow to datatable
dt.Rows.Add(row);
//Console.Out.WriteLine();
}
}
DataTableToServer(dt, sqlTblGPE);
return(true);
}
}
catch (Exception ex)
{
return(false);
}
}
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");
}
/// <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 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);
}
public void ReadSheet <T>(Stream stream, ReadSheetOptions <T> option) where T : class, new()
{
Inspector.NotNull(stream, "Excel文件流不能为空");
Inspector.NotNull(option, $"{nameof(ReadSheetOptions<T>)} can not be null");
//匹配SheetName
var sheetName = "";
{
var sheetNames = this.GetSheetNames(stream, false);
Inspector.Validation(option.ReadWay == ReadWay.SheetIndex && option.SheetIndex > sheetNames.Count(), $"指定的SheetIndex {option.SheetIndex} 无效，实际只存在{sheetNames.Count()}个Sheet");
Inspector.Validation(option.ReadWay == ReadWay.SheetName && !sheetNames.Contains(option.SheetName), $"指定的SheetName {option.SheetName} 不存在");
sheetName = option.ReadWay switch { ReadWay.SheetIndex => sheetNames.ElementAt(option.SheetIndex - 1), ReadWay.SheetName => option.SheetName };
}
//Excel中的表头列与实体类中字段映射(index：集合中元素的位置，cellRef：单元格的A1 B1中的A B这种)
var fieldLoc = new List <(int index, string excelField, string cellRef, string classField, bool allowNull, PropertyInfo prop)>();
{
var props = ReflectionHelper.NewInstance.GetSortedReadProps <T>().Select(t => t.prop).ToList();
for (int index = 0; index < props.Count(); index++)
{
var attribute = props[index].GetCustomAttribute <ExcelKitAttribute>();
fieldLoc.Add((index, attribute.Desc, "", attribute.Code ?? props[index].Name, attribute.AllowNull, props[index]));
}
}
using (var sheetDoc = SpreadsheetDocument.Open(stream, false))
{
WorkbookPart workbookPart = sheetDoc.WorkbookPart;
//1.目标Sheet的Rid是否存在
string rId = workbookPart.Workbook.Sheets?.Cast <Sheet>()?.FirstOrDefault(t => t.Name.Value == sheetName)?.Id?.Value;
Inspector.NotNullOrWhiteSpace(rId, $"不存在名为：{sheetName} 的Sheet");
SharedStringTablePart shareStringPart;
if (workbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0)
{
shareStringPart = workbookPart.GetPartsOfType <SharedStringTablePart>().First();
}
else
{
shareStringPart = workbookPart.AddNewPart <SharedStringTablePart>();
}
string[] shareStringItemValues = shareStringPart.GetItemValues().ToArray();
//2.反转Sheet顺序
foreach (var workSheetPart in workbookPart.WorksheetParts?.Reverse())
{
//是否是指定Sheet的Rid
string partRelationshipId = workbookPart.GetIdOfPart(workSheetPart);
if (partRelationshipId != rId)
{
continue;
}
//读取失败的原始数据信息
(Dictionary <string, object> odata, List <(string rowIndex, string columnName, string cellValue, string errorMsg)> failInfos)failRowData =
(new Dictionary <string, object>(), new List <(string rowIndex, string columnName, string cellValue, string errorMsg)>());
//创建Reader
OpenXmlReader reader = OpenXmlReader.Create(workSheetPart);
//工具类实例
var reflection = ReflectionHelper.NewInstance;
while (reader.Read())
{
if (reader.ElementType == typeof(Worksheet))
{
reader.ReadFirstChild();
}
if (reader.ElementType == typeof(Row))
{
var row = (Row)reader.LoadCurrentElement();
//3.读取表头列，匹配字段信息
if (row.RowIndex == option.HeadRow)
{
foreach (Cell cell in row.Elements <Cell>())
{
if (cell.CellReference != null && cell.CellReference.HasValue)
{
//excel中的表头列字段
string outerCode = cell.GetValue(shareStringItemValues);
if (fieldLoc.Exists(t => t.excelField == outerCode))
{
var fieldInfo = fieldLoc.FirstOrDefault(t => t.excelField == outerCode);
fieldInfo.cellRef = StringHelper.RemoveNumber(cell.CellReference);
fieldLoc[fieldInfo.index] = fieldInfo;
}
}
}
//实体上定义了ExcelKit特性的字段未在Excel中匹配到
var unMatchedField = fieldLoc.Where(t => string.IsNullOrWhiteSpace(t.cellRef));
if (unMatchedField.Count() > 0)
{
var obj = unMatchedField.FirstOrDefault();
var msg = $"{typeof(T).Name}中的字段{obj.classField}特性上指定的Desc：{obj.excelField} 未在Excel列头中匹配到";
throw new ExcelKitException(msg);
}
continue;
}
if (row.RowIndex < option.DataStartRow)
{
continue;
}
if (option.DataEndRow.HasValue && row.RowIndex > option.DataEndRow)
{
break;
}
//读取到的每行数据
T model = new T();
//excel原始数据
failRowData.odata.Clear();
//失败信息
failRowData.failInfos.Clear();
//是否读取成功
var readSuc = true;
//4. row.Elements<Cell>()获取出来的会自动跳过为空的单元格
foreach (Cell cell in row.Elements <Cell>())
{
//4.1 跳过cell引用为空的
if (cell.CellReference == null || !cell.CellReference.HasValue)
{
continue;
}
//4.2 当前循环的cell列位置(不含数字)
var loopCellRef = StringHelper.RemoveNumber(cell.CellReference);
//不存在或匹配列信息不一致的跳过
var fieldInfo = fieldLoc.FirstOrDefault(t => t.cellRef.Equals(loopCellRef, StringComparison.OrdinalIgnoreCase));
if (fieldInfo == (0, null, null, null, false, null) || !loopCellRef.Equals(fieldInfo.cellRef, StringComparison.OrdinalIgnoreCase))
{
continue;
}
//Excel中读取到的值
string value = cell.GetValue(shareStringItemValues);
Inspector.Validation(!fieldInfo.allowNull && string.IsNullOrWhiteSpace(value), $"Excel中列 {fieldInfo.excelField} 为必填项");
try
{
failRowData.odata.Add(fieldInfo.excelField, value);
reflection.SetValue(fieldInfo.prop, ref model, value, fieldInfo.allowNull);
}
catch (Exception ex)
{
readSuc = false;
failRowData.failInfos.Add((row.RowIndex, fieldInfo.excelField, value?.ToString(), ex.Message));
}
}
//读取成功执行
if (readSuc)
{
option.SucData?.Invoke(model, row.RowIndex.Value);
}
else
{
option.FailData?.Invoke(failRowData.odata, failRowData.failInfos);
}
}
}
}
sheetDoc.Close();
}
}
}
public void ReadSheet(Stream stream, ReadSheetDicOptions option)
{
Inspector.NotNull(stream, "Excel文件流不能为空");
Inspector.NotNull(option, $"{nameof(ReadSheetDicOptions)} can not be null");
Inspector.Validation(option.ExcelFields == null || option.ExcelFields.Length == 0 || option.ExcelFields.Count(t => string.IsNullOrWhiteSpace(t.field)) > 0, "Excel中的列头信息不能为空或存在为空的列名");
//匹配SheetName
var sheetName = "";
{
var sheetNames = this.GetSheetNames(stream, false);
Inspector.Validation(option.ReadWay == ReadWay.SheetIndex && option.SheetIndex > sheetNames.Count(), $"指定的SheetIndex {option.SheetIndex} 无效，实际只存在{sheetNames.Count()}个Sheet");
Inspector.Validation(option.ReadWay == ReadWay.SheetName && !sheetNames.Contains(option.SheetName), $"指定的SheetName {option.SheetName} 不存在");
sheetName = option.ReadWay switch { ReadWay.SheetIndex => sheetNames.ElementAt(option.SheetIndex - 1), ReadWay.SheetName => option.SheetName };
}
//Excel中的表头列信息(index：集合中元素的位置，cellRef：单元格的A1 B1中的A B这种)
var fieldLoc = new List <(int index, string excelField, ColumnType columnType, bool allowNull, string cellRef)>();
{
for (int index = 0; index < option.ExcelFields.Count(); index++)
{
Inspector.Validation(fieldLoc.Exists(t => t.excelField == option.ExcelFields[index].field?.Trim()), "指定读取的 ExcelFields 中存在相同的列名");
fieldLoc.Add((index, option.ExcelFields[index].field, option.ExcelFields[index].type, option.ExcelFields[index].allowNull, ""));
}
}
using (var sheetDoc = SpreadsheetDocument.Open(stream, false))
{
WorkbookPart workbookPart = sheetDoc.WorkbookPart;
//1.目标Sheet的Rid是否存在
string rId = workbookPart.Workbook.Sheets?.Cast <Sheet>()?.FirstOrDefault(t => t.Name.Value == sheetName)?.Id?.Value;
Inspector.NotNullOrWhiteSpace(rId, $"不存在名为 {sheetName} 的Sheet");
SharedStringTablePart shareStringPart;
if (workbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0)
{
shareStringPart = workbookPart.GetPartsOfType <SharedStringTablePart>().First();
}
else
{
shareStringPart = workbookPart.AddNewPart <SharedStringTablePart>();
}
string[] shareStringItemValues = shareStringPart.GetItemValues().ToArray();
//2.反转Sheet顺序
foreach (var workSheetPart in workbookPart.WorksheetParts?.Reverse())
{
//是否是指定Sheet的Rid，不是则忽略
string partRelationshipId = workbookPart.GetIdOfPart(workSheetPart);
if (partRelationshipId != rId)
{
continue;
}
//读取失败的原始数据信息
(Dictionary <string, object> odata, List <(string rowIndex, string columnName, string cellValue, string errorMsg)> failInfos)failRowData =
(new Dictionary <string, object>(), new List <(string rowIndex, string columnName, string cellValue, string errorMsg)>());
//创建Reader
OpenXmlReader reader = OpenXmlReader.Create(workSheetPart);
//工具类实例
var reflection = ReflectionHelper.NewInstance;
while (reader.Read())
{
if (reader.ElementType == typeof(Worksheet))
{
reader.ReadFirstChild();
}
if (reader.ElementType == typeof(Row))
{
var row = (Row)reader.LoadCurrentElement();
//3.读取表头列，匹配字段信息
if (row.RowIndex == option.HeadRow)
{
foreach (Cell cell in row.Elements <Cell>())
{
if (cell.CellReference != null && cell.CellReference.HasValue)
{
//excel中的表头列字段
string excelField = cell.GetValue(shareStringItemValues);
if (fieldLoc.Exists(t => t.excelField == excelField))
{
var fieldInfo = fieldLoc.FirstOrDefault(t => t.excelField == excelField);
fieldInfo.cellRef = StringHelper.RemoveNumber(cell.CellReference);
fieldLoc[fieldInfo.index] = fieldInfo;
}
}
}
//实体上定义了ExcelKit特性的字段未在Excel中匹配到
var unMatchedField = fieldLoc.Where(t => string.IsNullOrWhiteSpace(t.cellRef));
if (unMatchedField.Count() > 0)
{
var unmatchFields = string.Join("、", unMatchedField.Select(t => t.excelField));
var msg = $"指定的ExcelFields中的字段【{unmatchFields}】不存在于Excel中";
throw new ExcelKitException(msg);
}
continue;
}
if (row.RowIndex < option.DataStartRow)
{
continue;
}
if (option.DataEndRow.HasValue && row.RowIndex > option.DataEndRow)
{
break;
}
//读取到的每行数据
var rowData = new Dictionary <string, object>();
//excel原始数据
failRowData.odata.Clear();
//失败信息
failRowData.failInfos.Clear();
//是否读取成功
var readSuc = true;
//4. row.Elements<Cell>()获取出来的会自动跳过为空的单元格
foreach (Cell cell in row.Elements <Cell>())
{
//4.1 跳过cell引用为空的
if (cell.CellReference == null || !cell.CellReference.HasValue)
{
continue;
}
//4.2 当前循环的cell列位置(不含数字)
var loopCellRef = StringHelper.RemoveNumber(cell.CellReference);
//不存在或匹配列信息不一致的跳过
var fieldInfo = fieldLoc.FirstOrDefault(t => t.cellRef.Equals(loopCellRef, StringComparison.OrdinalIgnoreCase));
if (fieldInfo == (0, null, 0, false, null) || !loopCellRef.Equals(fieldInfo.cellRef, StringComparison.OrdinalIgnoreCase))
{
continue;
}
//Excel中读取到的值
string readVal = null;
try
{
readVal = cell.GetValue(shareStringItemValues);
Inspector.Validation(!fieldInfo.allowNull && string.IsNullOrWhiteSpace(readVal), $"Excel中列 {fieldInfo.excelField} 为必填项");
object value = ColumnTypeMapping.Convert(readVal, fieldInfo.columnType, fieldInfo.allowNull);
rowData.Add(fieldInfo.excelField, value);
}
catch (Exception ex)
{
readSuc = false;
failRowData.failInfos.Add((row.RowIndex, fieldInfo.excelField, readVal?.ToString(), ex.Message));
}
}
//5.单元格为空缺失的key补齐（这样做key的顺序和原始的不一致了，有需求时可以使用header上面的cellRef排序解决，为了读取速度此处暂不做）
var lackKeys = fieldLoc.Select(t => t.excelField).Except(rowData.Keys);
foreach (var lackKey in lackKeys)
{
rowData.TryAdd(lackKey, null);
}
//读取成功执行
if (readSuc)
{
option.SucData?.Invoke(rowData, row.RowIndex.Value);
}
else
{
option.FailData?.Invoke(failRowData.odata, failRowData.failInfos);
}
}
}
}
sheetDoc.Close();
}
}
public void ReadRows(Stream stream, ReadRowsOptions option)
{
Inspector.NotNull(stream, "Excel文件流不能为空");
Inspector.NotNull(option, $"{nameof(ReadRowsOptions)}不能为null");
if (option.ReadWay == ReadWay.SheetIndex)
{
Inspector.MoreThanOrEqual(option.SheetIndex, 1, "Sheet索引至少从1开始");
}
if (option.ReadWay == ReadWay.SheetName)
{
Inspector.NotNullOrWhiteSpace(option.SheetName, "Sheet名称不能为空");
}
//匹配SheetName
var sheetName = "";
{
var sheetNames = this.GetSheetNames(stream, false);
Inspector.Validation(option.ReadWay == ReadWay.SheetIndex && option.SheetIndex > sheetNames.Count(), $"指定的Sheet索引 {option.SheetIndex} 无效，实际只存在{sheetNames.Count()}个Sheet");
Inspector.Validation(option.ReadWay == ReadWay.SheetName && !sheetNames.Contains(option.SheetName), $"指定的Sheet名称 {option.SheetName} 不存在");
sheetName = option.ReadWay switch { ReadWay.SheetIndex => sheetNames.ElementAt(option.SheetIndex - 1), ReadWay.SheetName => option.SheetName };
}
//包含返回空单元格校验
Inspector.Validation(option.ReadEmptyCell && (option.ColumnHeaders == null || option.ColumnHeaders.Count() == 0), $"读取要包含空格列时，必须指定读取的列头信息，{nameof(option.ColumnHeaders)}不能为空");
if (option.ReadEmptyCell)
{
foreach (var cellRef in option.ColumnHeaders)
{
Inspector.Validation(!_excelColumn.Contains(cellRef), $"{nameof(option.ColumnHeaders)}中 {cellRef} Excel列头无效");
}
}
using (var sheetDoc = SpreadsheetDocument.Open(stream, false))
{
WorkbookPart workbookPart = sheetDoc.WorkbookPart;
SharedStringTablePart shareStringPart;
if (workbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0)
{
shareStringPart = workbookPart.GetPartsOfType <SharedStringTablePart>().First();
}
else
{
shareStringPart = workbookPart.AddNewPart <SharedStringTablePart>();
}
string[] shareStringItemValues = shareStringPart.GetItemValues().ToArray();
//目标Sheet的Rid
string rId = workbookPart.Workbook.Sheets?.Cast <Sheet>()?.FirstOrDefault(t => t.Name.Value == sheetName)?.Id?.Value;
Inspector.NotNullOrWhiteSpace(rId, $"不存在名为：{sheetName} 的Sheet");
//读取多个Sheet
foreach (var worksheetPart in workbookPart.WorksheetParts?.Reverse())
{
//是否是指定Sheet的Rid
string partRelationshipId = workbookPart.GetIdOfPart(worksheetPart);
if (partRelationshipId != rId)
{
continue;
}
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
//数据行
var list = new List <(string cellRef, string value)>();
while (reader.Read())
{
if (reader.ElementType == typeof(Worksheet))
{
reader.ReadFirstChild();
}
if (reader.ElementType == typeof(Row))
{
var row = (Row)reader.LoadCurrentElement();
if (row.RowIndex < option.DataStartRow)
{
continue;
}
if (option.DataEndRow.HasValue && row.RowIndex > option.DataEndRow)
{
break;
}
list.Clear();
foreach (Cell cell in row.Elements <Cell>())
{
if (cell.CellReference == null || !cell.CellReference.HasValue)
{
continue;
}
//无数字的列头名（A1、B2、C1中的A、B、C）
var loopCellRef = StringHelper.RemoveNumber(cell.CellReference);
if (option.ReadEmptyCell && !option.ColumnHeaders.Contains(loopCellRef))
{
continue;
}
string value = cell.GetValue(shareStringItemValues);
list.Add((loopCellRef, value));
}
//包含空单元格，实际却没有则补齐数据
if (option.ReadEmptyCell)
{
var willAdd = option.ColumnHeaders.Except(list.Select(t => t.cellRef));
list.AddRange(willAdd.Select(item => (item, "")));
list = list.OrderBy(t => t.cellRef).ToList();
}
option.RowData?.Invoke(list.Select(t => t.value).ToList());
}
}
}
sheetDoc.Close();
}
if (option.IsDisposeStream)
{
stream.Dispose();
}
}
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 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 static void ReadAllCellValues(string fileName, bool storeFile,
ref SortedDictionary <string, List <StoredElement> > storedElements,
ref SortedDictionary <string, List <CommissionElement> > commissionElements)
{
using (
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false))
{
bool firstRow = true;
LinkedList <StoredElement> store = new LinkedList <StoredElement>();
LinkedList <CommissionElement> commission = new LinkedList <CommissionElement>();
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
while (reader.Read())
{
if (reader.ElementType == typeof(Row))
{
reader.ReadFirstChild();
string profile = "";
string type = "";
string delivery = "";
int len = 0;
int count = 0;
int listing = 0;
do
{
if (reader.ElementType == typeof(Cell))
{
Cell c = (Cell)reader.LoadCurrentElement();
if (c != null)
{
string cellValue = "";
string address = c.CellReference.ToString();
string headerAddress = address[0] + "1";
string value = null;
value = GetCellValue(worksheetPart, workbookPart, headerAddress);
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
{
try
{
cellValue = c.CellValue.InnerText;
}
catch (NullReferenceException exception)
{
}
}
if (!firstRow)
{
switch (value)
{
case "Profil":
profile = cellValue;
break;
case "Gatunek":
type = cellValue;
break;
case "Długość":
Int32.TryParse(cellValue.Split('.')[0], out len);
break;
case "Sztuk":
Int32.TryParse(cellValue.Split('.')[0], out count);
break;
case "Nr Dostawy":
delivery = cellValue;
break;
case "Pozycja":
Int32.TryParse(cellValue.Split('.')[0], out listing);
break;
}
}
}
}
else
{
}
} while (reader.ReadNextSibling());
if (!firstRow)
{
for (int i = 0; i != count; ++i)
{
if (storeFile)
{
store.AddLast(new StoredElement(profile, type, len, delivery, 0));
}
else
{
commission.AddLast(new CommissionElement(profile, type, len, listing));
}
}
}
firstRow = false;
}
}
if (storeFile)
{
foreach (StoredElement e in store)
{
if (!storedElements.ContainsKey(e.GetProfile()))
{
storedElements.Add(e.GetProfile(), new List <StoredElement>());
}
e.SetStoreId(storedElements[e.GetProfile()].Count);
storedElements[e.GetProfile()].Add(e);
}
}
else
{
foreach (CommissionElement e in commission)
{
if (!commissionElements.ContainsKey(e.GetProfile()))
{
commissionElements.Add(e.GetProfile(), new List <CommissionElement>());
}
commissionElements[e.GetProfile()].Add(e);
}
}
}
}
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();
}
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 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"));
}
///<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 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);
}
}