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