public IEnumerable <HelperCell> GetCells() { foreach (var cell in _row.Elements <Doc.Cell>()) { yield return(new HelperCell(_parent, _sheetName, cell)); } }
static void ReadRow(Sheet sheet, Excel.Row importRow, Dictionary <string, string> sharedStringTable, Dictionary <int, CellFormat> cellFormats) { Row row = sheet.Row((int)(uint)importRow.RowIndex - 1); foreach (var importCell in importRow.Elements <Excel.Cell>()) { ReadCell(row, importCell, sharedStringTable, cellFormats); } }
private static OpenXmlSpread.Cell GetCell(DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet, string columnName, uint rowIndex) { OpenXmlSpread.Row row = GetRow(worksheet, rowIndex); if (row == null) { return(null); } return(row.Elements <OpenXmlSpread.Cell>() .Where(c => string.Compare(c.CellReference.Value, columnName + rowIndex, true) == 0).First()); }
private MSOpenXML.Cell CreateCellIfNotExist(MSOpenXML.Worksheet worksheet, string cellName) { string columnName = GetColumnName(cellName); uint rowIndex = GetRowIndex(cellName); IEnumerable <MSOpenXML.Row> rows = worksheet.Descendants <MSOpenXML.Row>().Where(r => r.RowIndex.Value == rowIndex); // If the Worksheet does not contain the specified row, create the specified row. // Create the specified cell in that row, and insert the row into the Worksheet. if (rows.Count() == 0) { MSOpenXML.Row row = new MSOpenXML.Row() { RowIndex = rowIndex }; MSOpenXML.Cell cell = new MSOpenXML.Cell() { CellReference = cellName }; row.Append(cell); worksheet.Descendants <MSOpenXML.SheetData>().First().Append(row); return(cell); } else { MSOpenXML.Row row = rows.First(); IEnumerable <MSOpenXML.Cell> cells = row.Elements <MSOpenXML.Cell>().Where(c => c.CellReference.Value == cellName); // If the row does not contain the specified cell, create the specified cell. if (cells.Count() == 0) { MSOpenXML.Cell cell = new MSOpenXML.Cell() { CellReference = cellName }; row.Append(cell); return(cell); } else { return(cells.First()); } } }
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)); } }