// Adds child parts and generates content of the specified part. public static void CreateTable(WorkbookPart workbookPart, WorksheetPart worksheetPart, string[] columns, int topLeftColumn, int topLeftRow, int width, int height) { List<WorksheetPart> worksheets = workbookPart.GetPartsOfType<WorksheetPart>().ToList(); uint maxTableId = worksheets.Select(ws => ws.TableDefinitionParts.ToList()).SelectMany(tableDefinitions => tableDefinitions).Aggregate<TableDefinitionPart, uint>(0, (current, tableDef) => Math.Max(tableDef.Table.Id, current)); uint tableId = maxTableId + 1; var tables = new TableParts { Count = 1U }; worksheetPart.Worksheet.Append((IEnumerable<OpenXmlElement>)tables); var newTableDefnPart = worksheetPart.AddNewPart<TableDefinitionPart>(); string relationshipId = worksheetPart.GetIdOfPart(newTableDefnPart); string cellReference = string.Format("{0}{1}:{2}{3}", GetColumnIdentifier(topLeftColumn), topLeftRow, GetColumnIdentifier(topLeftColumn + width - 1), topLeftRow + height); var table1 = new Table { Id = tableId, Name = "Table" + relationshipId, DisplayName = "Table" + relationshipId, Reference = cellReference, TotalsRowShown = false }; var autoFilter1 = new AutoFilter { Reference = cellReference }; var tableColumns1 = new TableColumns { Count = (uint)columns.Length }; for (int iColumn = 0; iColumn < columns.Length; iColumn++) { var tableColumn = new TableColumn { Id = (UInt32Value)(uint)iColumn + 1, Name = columns[iColumn] }; tableColumns1.Append((IEnumerable<OpenXmlElement>)tableColumn); } var tableStyleInfo1 = new TableStyleInfo { Name = "TableStyleMedium2", ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false }; table1.Append((IEnumerable<OpenXmlElement>)autoFilter1); table1.Append((IEnumerable<OpenXmlElement>)tableColumns1); table1.Append((IEnumerable<OpenXmlElement>)tableStyleInfo1); newTableDefnPart.Table = table1; var table = new TablePart { Id = relationshipId }; tables.Append((IEnumerable<OpenXmlElement>)table); //TableStyles tableStyles1 = new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleMedium9" }; //worksheetPart.Worksheet.Append(tableStyles1); }
public ExcelHelper Open(string ExcelFile,bool IsEditable=false) { _expectSheets = new List<string>(); OpenSettings os = new OpenSettings(); this._excelFilePath = ExcelFile; _doc = SpreadsheetDocument.Open(_excelFilePath, IsEditable); _wbPart = _doc.WorkbookPart; _shareStringPart = _wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault(); shareStringDic = new Dictionary<int, string>(); int j = 0; foreach (var item in _shareStringPart.SharedStringTable.Elements<SharedStringItem>()) { shareStringDic.Add(j, item.InnerText); j++; } return _instance; }
public static string GetCellValue(WorkbookPart wbPart, Cell theCell) { string value = theCell.InnerText; if (theCell.DataType != null) { switch (theCell.DataType.Value) { case CellValues.SharedString: var stringTable = wbPart. GetPartsOfType<SharedStringTablePart>().FirstOrDefault(); if (stringTable != null) { value = stringTable.SharedStringTable. ElementAt(int.Parse(value)).InnerText; } break; case CellValues.Boolean: switch (value) { case "0": value = "FALSE"; break; default: value = "TRUE"; break; } break; } } return value; }
protected string GetCellValue(WorkbookPart wbPart, Cell theCell) { if (theCell == null) { return null; } var value = theCell.InnerText; if (theCell.DataType == null) { return value; } switch (theCell.DataType.Value) { case (CellValues.SharedString): { var stringTable = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault(); if (stringTable != null) { value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText; } break; } case (CellValues.Boolean): { switch (value) { case "0": { value = "FALSE"; break; } default: { value = "TRUE"; break; } } break; } case (CellValues.Date): { if (!string.IsNullOrEmpty(value)) { value = DateTime.FromOADate(Convert.ToDouble(value)).ToString(CultureInfo.CurrentCulture); } break; } } return value; }
public static string GetCellValue(WorkbookPart wbPart, Cell theCell) { string value = theCell.InnerText; if (theCell.DataType != null) { switch (theCell.DataType.Value) { case CellValues.SharedString: var stringTable = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault(); if (stringTable != null) { value = stringTable.SharedStringTable. ElementAt(int.Parse(value)).InnerText; } break; case CellValues.Boolean: switch (value) { case "0": value = "FALSE"; break; default: value = "TRUE"; break; } break; case CellValues.Date: var date = new DateTime(); var success = DateTime.TryParse(value, out date); if (success) { value = date.ToString("MM-yyyy"); } break; } } return value; }
// Given the main workbook part, and a text value, insert the text into // the shared string table. Create the table if necessary. If the value // already exists, return its index. If it doesn't exist, insert it and // return its new index. internal static int InsertSharedStringItem(WorkbookPart wbPart, string value) { int index = 0; bool found = false; var stringTablePart = wbPart .GetPartsOfType<SharedStringTablePart>().FirstOrDefault(); // If the shared string table is missing, something's wrong. // Just return the index that you found in the cell. // Otherwise, look up the correct text in the table. if (stringTablePart == null) { // Create it. stringTablePart = wbPart.AddNewPart<SharedStringTablePart>(); } var stringTable = stringTablePart.SharedStringTable; if (stringTable == null) { stringTable = new SharedStringTable(); stringTablePart.SharedStringTable = stringTable; } // Iterate through all the items in the SharedStringTable. // If the text already exists, return its index. foreach (SharedStringItem item in stringTable.Elements<SharedStringItem>()) { if (item.InnerText == value) { found = true; break; } index += 1; } if (!found) { stringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(value))); stringTable.Save(); } return index; }
// // Insert the string into the shared string table of the WorkBookPart // private int insertSharedStringItem(WorkbookPart wbPart, string Value) { // // Look if the string already exists into the SharedStringTable, if it does return it // else return a new one // int index = 0; bool found = false; // // If the shared string table is missing, something's wrong. // Just return the index that you found in the cell. // Otherwise, look up the correct text in the table. // var stringTablePart = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault(); if (stringTablePart == null) { // Create a new SharedString stringTablePart = wbPart.AddNewPart<SharedStringTablePart>(); } var stringTable = stringTablePart.SharedStringTable; if (stringTable == null) { stringTable = new SharedStringTable(); } // // Iterate through all the items in the SharedStringTable. // If the text already exists, return its index. // foreach (SharedStringItem item in stringTable.Elements<SharedStringItem>()) { if (item.InnerText == Value) { found = true; break; } index++; } // // if not found append a new SharedString to the table // if (!found) { stringTable.AppendChild(new SharedStringItem(new Text(Value))); try { stringTable.Save(); } catch (Exception) { } } return index; }
private string GetCellValue(Worksheet worksheet, WorkbookPart wbPart, string targetCell) { uint targetRow = GetRowIndex(targetCell); string result = null; SharedStringTablePart sstPart = wbPart.GetPartsOfType<SharedStringTablePart>().First(); SharedStringTable ssTable = sstPart.SharedStringTable; try { Row row = worksheet.Descendants<Row>().SingleOrDefault(r => r.RowIndex == targetRow); //non ho la riga if (row == null) return result; Cell cell = row.Descendants<Cell>().SingleOrDefault(c => targetCell.Equals(c.CellReference)); if (cell == null) return result; if (cell.DataType != null && cell.DataType == CellValues.SharedString) { result = ssTable.ChildElements[Convert.ToInt32(cell.CellValue.Text)].InnerText; } else { if (cell.CellValue != null) { result = cell.CellValue.Text; } } } catch (Exception) { } return result; }
/// <summary> /// restituisce il contenuto di una cella Excel /// </summary> /// <param name="wsPart">parametro del foglio excel</param> /// <param name="wbPart">parametro del foglio excel</param> /// <param name="i">riga</param> /// <param name="j">colonna</param> /// <returns></returns> public string GetCellValueNew(WorksheetPart wsPart, WorkbookPart wbPart, int i, int j) { string value = null; String addressName = Convert.ToString((char)('A' + j)); addressName = (addressName + (i + 1)); Cell theCell = wsPart.Worksheet.Descendants<Cell>().Where(c => c.CellReference == addressName).FirstOrDefault(); if (theCell != null) { string test = null; value = theCell.InnerText; if (theCell.DataType != null) { test = theCell.DataType.Value.ToString(); switch (theCell.DataType.Value) { case CellValues.String: value = theCell.GetFirstChild<CellValue>()?.Text; break; case CellValues.SharedString: var stringTable = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault(); if (stringTable != null) { value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText; } break; } } else { value = theCell.GetFirstChild<CellValue>()?.Text; } } return value; }
/// <summary> /// Заполнение ячейки данными /// </summary> /// <param name="wbPart"></param> /// <param name="value"></param> /// <returns></returns> private int InsertSharedStringItem(WorkbookPart wbPart, string value) { var index = 0; var found = false; var stringTablePart = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault() ?? wbPart.AddNewPart<SharedStringTablePart>(); var stringTable = stringTablePart.SharedStringTable ?? new SharedStringTable(); foreach (var item in stringTable.Elements<SharedStringItem>()) { if (item.InnerText == value) { found = true; break; } index += 1; } if (found) return index; stringTable.AppendChild(new SharedStringItem(new Text(value))); stringTable.Save(); return index; }
/// <summary> /// Function is used to read row values for the provided excel sheet /// </summary> /// <param name="row">Row from the excel sheet</param> /// <param name="workbookPart">WorkbookPart object</param> /// <returns>List of values in row</returns> private static Collection<string> ReadRow(Row row, WorkbookPart workbookPart) { string value = string.Empty; int rowSize = row.Elements().Count(), emptyValue = 0; Collection<string> rowValue = new Collection<string>(); try { foreach (Cell cell in row.Elements<Cell>()) { if (null != cell) { CellValue cellValue = cell.Descendants<CellValue>().FirstOrDefault(); if (null != cellValue) { value = cellValue.Text; if (null != cell.DataType) { switch (cell.DataType.Value) { case CellValues.SharedString: var stringTable = workbookPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault(); if (null != stringTable) { value = stringTable.SharedStringTable. ElementAt(int.Parse(value, CultureInfo.InvariantCulture)).InnerText; } break; case CellValues.Boolean: switch (value) { case "0": value = "FALSE"; break; default: value = "TRUE"; break; } break; } } } else { value = string.Empty; } value = value.Trim(); if (string.IsNullOrWhiteSpace(value)) { emptyValue++; } if (string.Equals("NA", value, StringComparison.OrdinalIgnoreCase)) { value = string.Empty; } rowValue.Add(value); } } } catch (Exception exception) { ErrorLogger.LogErrorToTextFile(errorFilePath, "Exception occurred while reading row value: " + exception.Message); } if (emptyValue == rowSize) { return null; } return rowValue; }
private string FindCellInnerValue(WorkbookPart wbPart, Cell theCell,bool reference=true) { if (theCell != null) { var value = theCell.InnerText; if (theCell.DataType != null) { switch (theCell.DataType.Value) { case CellValues.SharedString: var stringTable = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault(); if (stringTable != null) { value = stringTable.SharedStringTable. ElementAt(int.Parse(value)).InnerText; if (reference) return theCell.CellReference; else return value; } break; } } if(theCell.CellValue!=null) return theCell.CellValue.InnerText; } return null; }
static string GetCellValue(WorkbookPart wbPart, Cell theCell) { string value = theCell.InnerText; if (theCell.DataType != null) { switch (theCell.DataType.Value) { case CellValues.SharedString: var stringTable = wbPart. GetPartsOfType<SharedStringTablePart>().FirstOrDefault(); if (stringTable != null) { //var allNoAttributesValue = stringTable.SharedStringTable. // ElementAt(int.Parse(value)).Where(m => m.HasAttributes == false).Select(m => m.InnerText).Aggregate((m1, m2) => m1 + m2); //value = allNoAttributesValue; //var a = stringTable.SharedStringTable. // ElementAt(int.Parse(value)); //var node = stringTable.SharedStringTable. // ElementAt(int.Parse(value)).First(); //var nodeA = stringTable.SharedStringTable. // ElementAt(int.Parse(value)); var allAllLocalNameNode = stringTable.SharedStringTable. ElementAt(int.Parse(value)).Where(m => allowLocalName.Contains(m.LocalName)).ToList(); if (allAllLocalNameNode.Any()) { value = allAllLocalNameNode.Select(m => m.InnerText).Aggregate((m1, m2) => m1 + m2); } else { value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText; } //var a = System.Text.RegularExpressions.Regex.Replace(fieldFirstChild.OuterXml, "<[^>]*>", ""); //!!Old first field //var fieldFirstChild = stringTable.SharedStringTable. // ElementAt(int.Parse(value)).FirstChild; //if (fieldFirstChild != null) //{ // value = fieldFirstChild.InnerText; //} //else //{ // value = stringTable.SharedStringTable. // ElementAt(int.Parse(value)).InnerText; //} } break; case CellValues.Boolean: switch (value) { case "0": value = "FALSE"; break; default: value = "TRUE"; break; } break; case CellValues.String: if (theCell.CellValue != null) value = theCell.CellValue.InnerText; break; } } return value; }
private string GetCellValue(WorkbookPart wbPart, Cell cell) { if (cell.DataType != null && cell.DataType == CellValues.SharedString && !string.IsNullOrWhiteSpace(cell.CellValue.InnerText)) { var stringTable = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault(); if (stringTable != null) { return stringTable.SharedStringTable.ElementAt(int.Parse(cell.CellValue.InnerText)).InnerText; } else { return cell.CellValue.InnerText; } } else { return cell?.CellValue?.InnerText; } }
//Temporal hide /* public List<SCNNiveldeGestion> GetValuesIndexNiveldeGestion(Stream ExcelStream, string sheetName, string columnName, List<string> GestionListValues, List<string> EstatalValues) { List<SCNNiveldeGestion> ListNiveldeGestion = new List<SCNNiveldeGestion>(); using (SpreadsheetDocument document = SpreadsheetDocument.Open(ExcelStream, false)) { WorkbookPart wbPart = document.WorkbookPart; Sheet theSheet = wbPart.Workbook.Descendants<Sheet>(). Where(s => s.Name == sheetName).FirstOrDefault(); if (theSheet == null) { throw new ArgumentException("sheetName"); } WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id)); IEnumerable<Cell> cells = wsPart.Worksheet.Descendants<Cell>().Where(c => string.Compare(GetColumnName(c.CellReference.Value), columnName, true) == 0).OrderBy(R => R.CellReference.Value); foreach (Cell currentcell in cells) { string cValue = GetCleanValueCell(currentcell, ref wbPart); if (!string.IsNullOrEmpty(cValue)) { if (GestionListValues.IndexOf(cValue) != -1) { ListNiveldeGestion.Add(new SCNNiveldeGestion { NiveldeGestionIndex = cValue, Position = GetRowIndex(currentcell.CellReference.Value) }); } else { if (EstatalValues.IndexOf(cValue) != -1) { ListNiveldeGestion.Add(new SCNNiveldeGestion { NiveldeGestionIndex = "Estatal", Position = GetRowIndex(currentcell.CellReference.Value) }); } else { if (!string.Equals(cValue, "Programa de Cumplimiento Grupo Modelo") && !string.Equals(cValue, "Indicar el órgano que emite el ordenamiento (sin abreviaciones)")) { ListNiveldeGestion.Add(new SCNNiveldeGestion { NiveldeGestionIndex = cValue, Position = "-1" }); } } } //ValuesColumn.Append(" Value: " + GetValueCell(ref wbPart, ref wsPart, currentcell.CellReference.Value)); } } } return ListNiveldeGestion; } */ //Final Temporal Hide private string GetCleanValueCell(Cell theCell, ref WorkbookPart wbPart) { string value = null; if (theCell != null) { value = theCell.InnerText; if (theCell.DataType != null) { switch (theCell.DataType.Value) { case CellValues.SharedString: // For shared strings, look up the value in the shared strings table. var stringTable = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault(); // If the shared string table is missing, something's wrong. // Just return the index that you found in the cell. // Otherwise, look up the correct text in the table. if (stringTable != null) { value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText; } break; case CellValues.Boolean: switch (value) { case "0": value = "FALSE"; break; default: value = "TRUE"; break; } break; } } } return value; }
static SharedStringTable GetSharedStringTable(WorkbookPart wbPart) { var tablePart = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault(); return tablePart != null ? tablePart.SharedStringTable : null; }
public static string GetCellValue(WorkbookPart wbPart, Sheet theSheet, string address) { string value = null; if (theSheet != null) { WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id)); Cell theCell = wsPart.Worksheet.Descendants<Cell>(). Where(c => c.CellReference == address).FirstOrDefault(); if (theCell != null) { value = theCell.InnerText; // If the cell represents an integer number, you are done. // For dates, this code returns the serialized value that // represents the date. The code handles strings and // Booleans individually. For shared strings, the code // looks up the corresponding value in the shared string // table. For Booleans, the code converts the value into // the words TRUE or FALSE. if (theCell.DataType != null) { switch (theCell.DataType.Value) { case CellValues.SharedString: // For shared strings, look up the value in the // shared strings table. var stringTable = wbPart.GetPartsOfType<SharedStringTablePart>() .FirstOrDefault(); // If the shared string table is missing, something // is wrong. Return the index that is in // the cell. Otherwise, look up the correct text in // the table. if (stringTable != null) { value = stringTable.SharedStringTable .ElementAt(int.Parse(value)).InnerText; } break; case CellValues.Boolean: switch (value) { case "0": value = "FALSE"; break; default: value = "TRUE"; break; } break; } } } } return value; }
// Get the value of a cell, given a file name, sheet name, and address name. string XLGetCellValue(Excel.Cell c, WorkbookPart wbPart) { string value = null; // If the cell does not exist, return an empty string. if (c != null) { value = c.InnerText; // If the cell represents an integer number, you are finished. // For dates, this code returns the serialized value that // represents the date. The code handles strings and Boolean values // individually. For shared strings, the code looks up the corresponding // value in the shared string table. For Boolean values, the code converts // the value into the words TRUE or FALSE. if (c.DataType != null) { switch (c.DataType.Value) { case Excel.CellValues.SharedString: // For shared strings, look up the value in the shared strings table. var stringTable = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault(); // If the shared string table is missing, something is wrong. // Just return the index that you found in the cell. // Otherwise, look up the correct text in the table. if (stringTable != null) { value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText; } break; case Excel.CellValues.Boolean: switch (value) { case "0": value = "FALSE"; break; default: value = "TRUE"; break; } break; } } } return value; }
/// <summary> /// 創建一個SharedStringTablePart(相當於各Sheet共用的存放字符串的容器) /// </summary> /// <param name="workbookPart"></param> /// <returns></returns> private static SharedStringTablePart CreateSharedStringTablePart(WorkbookPart workbookPart) { SharedStringTablePart shareStringPart = null; if (workbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0) { shareStringPart = workbookPart.GetPartsOfType<SharedStringTablePart>().First(); } else { shareStringPart = workbookPart.AddNewPart<SharedStringTablePart>(); } return shareStringPart; }
private static SharedStringTablePart GetSharedStringPart(WorkbookPart workbookPart) { return workbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0 ? workbookPart.GetPartsOfType<SharedStringTablePart>().First() : workbookPart.AddNewPart<SharedStringTablePart>(); }