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); } }