// Given text and a SharedStringTablePart, creates a SharedStringItem with the specified text // and inserts it into the SharedStringTablePart. If the item already exists, returns its index. private static int InsertSharedStringItem(SharedStringTablePart stringTablePart, string text) { var stringTable = stringTablePart.SharedStringTable; // If the part does not contain a SharedStringTable, create one. if (stringTable == null) { stringTable = new SharedStringTable(); stringTablePart.SharedStringTable = stringTable; } int index = 0; // Iterate through all the items in the SharedStringTable. If the text already exists, return its index. foreach (var item in stringTable.Elements <SharedStringItem>()) { if (item.InnerText == text) { return(index); } index++; } // The text does not exist in the part. Create the SharedStringItem and return its index. stringTable.AppendChild(new SharedStringItem(new Text(text))); return(index); }
/// <summary> /// Получить структуры данных из файла Excel. /// </summary> /// <returns>Матрица полей.</returns> public IEnumerable <List <string> > GetDataFromExcel() { var result = new List <List <string> >(); try { using (SpreadsheetDocument doc = SpreadsheetDocument.Open(DocPath, false)) { WorkbookPart workbookPart = doc.WorkbookPart; SharedStringTablePart sstpart = workbookPart.GetPartsOfType <SharedStringTablePart>().First(); SharedStringTable sst = sstpart.SharedStringTable; WorksheetPart worksheetPart = null; try { worksheetPart = GetWorksheetPartByName(doc, WorksheetName); } catch (Exception ex) { throw new Exception(string.Format("Не удалось обработать файл xlsx. Не найдена страница с наименованием \"{0}\". Подробности: {1}", WorksheetName, ex.Message), ex); } Worksheet sheet = worksheetPart.Worksheet; var cells = sheet.Descendants <Cell>(); var rows = sheet.Descendants <Row>(); var maxElements = rows.First().Elements <Cell>().Count(); var rowNum = 0; foreach (Row row in rows) { rowNum++; // HACK OpenText пропускает пустые ячейки. Будем импровизировать. С помощью функции CellReferenceToIndex будем определять "правильный" порядковый номер ячейки и заполнять массив. var rowData = new string[maxElements]; for (var i = 0; i < maxElements; i++) { rowData[i] = string.Empty; } foreach (Cell c in row.Elements <Cell>()) { if (c.CellValue != null) { var innerText = c.DataType != null && c.DataType == CellValues.SharedString ? sst.ChildElements[int.Parse(c.CellValue.Text)].InnerText : c.CellValue.InnerText; rowData[CellReferenceToIndex(c)] = innerText; } } // Если максимальное значение в наборе данных - пустое значение, значит строка не содержит данных. if (!string.IsNullOrWhiteSpace(rowData.Max())) { result.Add(rowData.ToList()); } } } } catch (Exception ex) { throw new Exception(string.Format("Не удалось обработать файл xlsx. Обратитесь к администратору системы. Подробности: {0}", ex.Message), ex); } return(result); }
/// <summary> /// 根据给定的Excel流组织成Datatable /// </summary> /// <param name="stream">Excel文件流</param> /// <param name="sheetName">需要读取的Sheet</param> /// <returns>组织好的DataTable</returns> public static DataTable ReadExcelBySheetName(string sheetName, Stream stream) { using (SpreadsheetDocument document = SpreadsheetDocument.Open(stream, false)) {//打开Stream IEnumerable <Sheet> sheets = document.WorkbookPart.Workbook.Descendants <Sheet>().Where(s => s.Name == sheetName); if (sheets.Count() == 0) {//找出符合条件的sheet,没有则返回 return(null); } WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id); //获取Excel中共享数据 SharedStringTable stringTable = document.WorkbookPart.SharedStringTablePart.SharedStringTable; IEnumerable <Row> rows = worksheetPart.Worksheet.Descendants <Row>();//得到Excel中得数据行 DataTable dt = new DataTable("Excel"); //因为需要将数据导入到DataTable中,所以我们假定Excel的第一行是列名,从第二行开始是行数据 foreach (Row row in rows) { if (row.RowIndex == 1) { //Excel第一行为列名 GetDataColumn(row, stringTable, ref dt); } GetDataRow(row, stringTable, ref dt);//Excel第二行同时为DataTable的第一行数据 } return(dt); } }
/// <summary> /// 获取单元格的值 /// </summary> /// <param name="cell"></param> /// <param name="stringTablePart"></param> /// <returns></returns> private static string GetValue(Cell cell, SharedStringTable stringTable) { //由于Excel的数据存储在SharedStringTable中,需要获取数据在SharedStringTable 中的索引 string value = string.Empty; try { if (cell.ChildElements.Count == 0) { return(value); } value = double.Parse(cell.CellValue.InnerText).ToString(); if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString)) { value = stringTable.ChildElements[Int32.Parse(value)].InnerText; } } catch (Exception) { value = "N/A"; } return(value); }
private void UpdateWorksheetContent(WorksheetPart workSheetPart, List<ExcelSheet> templateRows, SharedStringTable sharedStrings, List<ProjectFile> projectFiles, Customer customer, User user) { IEnumerable<SheetData> sheets = workSheetPart.Worksheet.ChildElements.OfType<SheetData>(); foreach (var sheetData in sheets) { sheetData.RemoveAllChildren<Row>(); List<String> strings = sharedStrings.ChildElements.OfType<SharedStringItem>().Where(shItem => shItem.ChildElements.OfType<Text>().Any()).Select(shItem => { var firstOrDefault = shItem.ChildElements.OfType<Text>().FirstOrDefault(); return firstOrDefault != null ? firstOrDefault.Text : String.Empty; }).ToList(); //from this point is the content int linesToAdd = 0; var templateSheet = templateRows.FirstOrDefault(row => row.SheetData.Equals(sheetData)); if (templateSheet != null) { foreach (var projectFile in projectFiles) { GenerateWorksheetDataContent(sheetData, templateSheet.Rows, linesToAdd, new List<TokensProvider> {projectFile, customer, user}, strings); linesToAdd += (templateSheet.Rows.Count + 5); } } } }
/// <summary> /// Запись строк в ячейки. /// </summary> /// <param name="listImportData">Список импортируемых записей.</param> public void InsertText(List <ArrayList> listImportData) { using (SpreadsheetDocument doc = SpreadsheetDocument.Open(DocPath, true)) { SharedStringTablePart shareStringPart; if (doc.WorkbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0) { shareStringPart = doc.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First(); } else { shareStringPart = doc.WorkbookPart.AddNewPart <SharedStringTablePart>(); } WorkbookPart workbookPart = doc.WorkbookPart; SharedStringTablePart sstpart = workbookPart.GetPartsOfType <SharedStringTablePart>().First(); SharedStringTable sst = sstpart.SharedStringTable; WorksheetPart worksheetPart = GetWorksheetPartByName(doc, WorksheetName); Worksheet sheet = worksheetPart.Worksheet; foreach (var importItems in listImportData) { int index = InsertSharedStringItem(Convert.ToString(importItems[0]), shareStringPart); Cell cell = InsertCellInWorksheet(Convert.ToString(importItems[1]), Convert.ToUInt32(importItems[2]), worksheetPart); cell.CellValue = new CellValue(index.ToString()); cell.DataType = new EnumValue <CellValues>(CellValues.SharedString); } worksheetPart.Worksheet.Save(); } }
/// <summary> /// Добавить текст в таблицу <see cref="SharedStringTable"/> /// </summary> /// <param name="sst">Таблица с тектами</param> /// <param name="text">Добавляемый текст</param> /// <param name="rPr">Стиль добавляемого текста</param> /// <returns>Добавленыый элемент в <see cref="SharedStringTable"/> содержащий указанный текст</returns> public static SharedStringItem Add(this SharedStringTable sst, string text, RunProperties rPr = null) { var item = new SharedStringItem(); if (rPr == null) { item.Text = new Text(text); } else { var run = new Run(); run.Text = new Text(text); run.Append(rPr.CloneNode(true)); item.Append(run); } sst.Append(item); if (sst.Count != null) { sst.Count.Value++; } if (sst.UniqueCount != null) { sst.UniqueCount.Value++; } return(item); }
public ISet Read(FileInfo file) { if (file is null) { _logger.LogError("File {0} is null", file.Name); throw new ArgumentNullException(nameof(file), "File cannot be null"); } if (!file.Exists) { _logger.LogError("File {0} does not exist", file.Name); throw new ArgumentException($"File {file.FullName} does not exist", nameof(file)); } _extractionDirectory = Utils.GetUniqueTempDirectory(); try { _logger.LogDebug("Extracting {0} to {1} directory...", file.Name, _extractionDirectory.FullName); Unzipper.Unzip(file, _extractionDirectory); _logger.LogDebug("Extraction complete"); Workbook workbook = GetWorkbook(); SharedStringTable sharedStringTable = GetSharedStringTable(); IEnumerable <Sheet> sheets = GetSheets(); Styles styles = GetStyles(); throw new NotImplementedException(); } finally { _extractionDirectory.Delete(recursive: true); _logger.LogDebug("Extraction directory {0} has been deleted.", _extractionDirectory.FullName); } }
/* ----- Fonction de test ----- */ #region TestToPlaceAStringInCellValue /// <summary> /// Résultat du test /// Tentative de remplacement d'une CellValue par une autre. /// Comme attendu cela fonctionne sur le moment mais peut poser des problèmes par la suite. /// En effet si la cellule que l'on modifie référencait une string alors lors des lectures /// qui peuvent suivre (avec les algorithmes ci-dessus en tout cas) provoqueront des erreurs. /// L'explication est que les algorithmes de lectures détectent la présence de string via le /// champs DataType uniquement. Si c'est le cas alors il récupère l'index contenu dans CellValue /// pour le converti. Sauf que CellValue ne contient pas un Int32 mais une string. /// Cette erreur est évitable cependant cela ne serait pas une bonne chose étant donné que /// CellValue ne contient pas de string dans les cas nominaux. /// /// La bonne pratique pour ce genre de fonction est de placer la string voulu dans la liste des /// string partagée (dans SharedString.xml) comme le fait la fonction ChangeStringByAnother et si /// besoin de procéder à un changement de DataType comme le fait la fonction ChangeIntByString. /// </summary> /// /// <param name="pathToFile"> /// Le chemin (nom et extension compris) du fichier à lire et modifier /// </param> private static void TestToPlaceAStringInValue(string pathToFile) { Console.WriteLine("Fichier : " + pathToFile); // Ouverture du document using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(pathToFile, true)) { // Parcours de la hiérarchie du document jusqu'à récupération du tableau WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart; // Récupération du tableau nommé "Feuil1" Sheet sheet = workbookPart.Workbook.Descendants <Sheet>().Where(x => x.Name == "Feuil1").FirstOrDefault <Sheet>(); WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id); // Récupération de la liste des string liées SharedStringTable listeStringElement = workbookPart.SharedStringTablePart.SharedStringTable; // Récupération de la liste des cellules IEnumerable <Cell> listCell = worksheetPart.Worksheet.Descendants <Cell>().Where(x => x.CellReference != null); int i = 0; foreach (Cell c in listCell) { if (c.CellFormula == null) { // Pour ne pas toucher aux formules c.CellValue = new CellValue("Test " + i); } } worksheetPart.Worksheet.Save(); spreadsheetDocument.Close(); } }
string SpreadsheetProcess(Stream memStream) { ///открываем xlsx документ из потока using (SpreadsheetDocument doc = SpreadsheetDocument.Open(memStream, false)) { memStream.Position = 0; StringBuilder sb = new StringBuilder(1000); sb.Append("<?xml version=\"1.0\"?><documents><document>"); ///shared ячейки (в которых хранятся повторяемые значения). Так вот, из объекта класса SpreadsheetDocument их можно получить так: SharedStringTable sharedStringTable = doc.WorkbookPart.SharedStringTablePart.SharedStringTable; int sheetIndex = 0; // перебираем все листы foreach (WorksheetPart worksheetPart in doc.WorkbookPart.WorksheetParts) { WorkSheetProcess(sb, sharedStringTable, worksheetPart, doc, sheetIndex); sheetIndex++; } sb.Append(@"</document></documents>"); return(sb.ToString()); } }
private List <DataCell> ExtractCells(SharedStringTable sst, Row row, IEnumerable <CellMap> cellMaps) { var result = new List <DataCell>(); var cellsByRow = GetCells(row); var cells = cellsByRow.Select(cell => new { value = GetCellValue(sst, cell), column = GetColumnIndex(cell.CellReference) }).ToList(); // если все ячейки пустые, таблица либо пустая, либо закончилась if (cells.All(cell => string.IsNullOrEmpty(cell.value))) { return(result); } foreach (var cellMap in cellMaps) { var cellByColumn = cells.FirstOrDefault(cell => cell.column == cellMap.ColumnIndex); result.Add(new DataCell { Value = cellByColumn?.value ?? string.Empty, PropertyInfo = cellMap.FilterValue.PropertyInfo }); } return(result); }
private async Task <IEnumerable <DataRow> > ExtractDataFromDocumentAsync(SpreadsheetDocument document, IEnumerable <FilterValue> filterValues, string sheetName = "") { WorkbookPart workbookPart = document.WorkbookPart; SharedStringTablePart sstpart = workbookPart.GetPartsOfType <SharedStringTablePart>().First(); SharedStringTable sst = sstpart.SharedStringTable; var worksheetPart = GetWorksheetPartByName(document, sheetName); if (worksheetPart == null) { worksheetPart = workbookPart.WorksheetParts.Last(); } Worksheet sheet = worksheetPart.Worksheet; var rows = sheet .Descendants <Row>() .ToList(); // найти заголовок согласно фильтру var rowMap = GetHeaderMap(sst, rows, filterValues); if (!rowMap.CellMaps.Any()) { return(new List <DataRow>()); } var bodyRows = await ExtractBodyRowsAsync(sst, rowMap, rows); return(bodyRows); }
static void ReadXLSX(string fileName) { using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false)) { WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart; WorksheetPart worksheetPart = workbookPart.WorksheetParts.First(); SheetData sheetData = worksheetPart.Worksheet.Elements <SheetData>().First(); SharedStringTable sharedStringTable = workbookPart.SharedStringTablePart.SharedStringTable; foreach (Row row in sheetData.Elements <Row>()) { foreach (Cell cell in row.Elements <Cell>()) { if (cell.DataType != null && cell.DataType == CellValues.SharedString) { Console.Write(sharedStringTable.ElementAt(int.Parse(cell.InnerText)).InnerText); } else if (cell.CellValue != null) { Console.Write(cell.CellValue.Text); } Console.Write("\t"); } Console.WriteLine(); } } }
public RowMapper(Type type, Row headerRow, SharedStringTable stringTable) { _type = type; _stringTable = stringTable; foreach (Cell cell in headerRow.OfType <Cell>()) { string header = GetCellValue(cell).ToUpper().Trim(); string columnRef = ParseColName(cell.CellReference.Value); _colRefsToHeaders.Add(columnRef, header); _colHeadersToRefs.Add(header, columnRef); } PropertyInfo[] props = type.GetProperties(); foreach (string colRef in _colRefsToHeaders.Keys) { PropertyInfo prop = props.SingleOrDefault(p => string.Compare( p.Name, _colRefsToHeaders[colRef], CultureInfo.CurrentCulture, CompareOptions.IgnoreSymbols | CompareOptions.IgnoreCase ) == 0); if (prop != null) { _colRefsToProp.Add(colRef, prop); } } }
public CellData(Cell cell, SharedStringTable sharedStringTable) { this.CellReference = cell.CellReference; this.CellName = this.GetColumnName(this.CellReference); this.CellIndex = this.ConvertColumnNameToNumber(this.CellName); this.Value = this.GetValue(cell, sharedStringTable); }
public string GetStringFromSharedStringTable(int stringId) { SharedStringTable stringTable = Document.WorkbookPart.SharedStringTablePart.SharedStringTable; string sharedString = Document.WorkbookPart.SharedStringTablePart.SharedStringTable.ChildElements[stringId].InnerText; return(sharedString); }
static public WorkbookAdaptor Open(string workbookPath, bool readOnly = true) { if (workbookPath == null) { throw new ArgumentNullException("Path to workbook cannot be null."); } if (!File.Exists(workbookPath)) { throw new ArgumentException("Workbook does not exist"); } try { SpreadsheetDocument doc = SpreadsheetDocument.Open(workbookPath, !readOnly); var wb = new WorkbookAdaptor(); wb.Document = doc; SharedStringTable sst = doc.WorkbookPart.SharedStringTablePart.SharedStringTable; wb.SharedStrings = new SharedStrings(sst); wb.Sheets = doc.WorkbookPart.Workbook.GetFirstChild <Sheets>(); return(wb); } catch (Exception ex) { throw new ApplicationException("Error opening workbook", ex); } }
public SharedStringTableWithIndex(SharedStringTable sharedStringTable, WorkbookPart workbookPart) { SharedStringTable = sharedStringTable; WorkbookPart = workbookPart; MakeIndex(); }
public static void CheckCellValue(string filePath, string cellReference, bool isNumeric, string cellValue) { using (var document = SpreadsheetDocument.Open(filePath, true)) { WorkbookPart workbookPart = document.WorkbookPart; SharedStringTable sharedStringTable = workbookPart.SharedStringTablePart.SharedStringTable; WorksheetPart worksheetPart = workbookPart.WorksheetParts.First(); Worksheet worksheet = worksheetPart.Worksheet; SheetData sheetData = worksheet.GetFirstChild <SheetData>(); var cell = sheetData.Descendants <Cell>().Where(x => x.CellReference.Value == cellReference).FirstOrDefault(); Assert.IsNotNull(cell); if (isNumeric) { Assert.AreEqual(cellValue, cell.CellValue.InnerText); } else { Assert.AreEqual(CellValues.SharedString, cell.DataType.Value); Assert.AreEqual(cellValue, GetSharedStringItem(sharedStringTable, int.Parse(cell.CellValue.InnerText))); } } }
public void UpdateFromTemplateRow(Row row, SharedStringTable sharedStrings) { if (row == null) { Action = "Delete"; return; } Action = string.Empty; var columns = GetColumns(); var properties = typeof(AttributeMetadataRow).GetProperties(); for (var i = 0; i < columns.Length; i++) { var column = columns[i]; var property = properties.First(x => (x.GetCustomAttribute(typeof(ColumnAttribute)) as ColumnAttribute)?.Header == column.Header); var oldValue = property.GetValue(this); var newValue = TemplateHelper.GetCellValue(row, i, sharedStrings); if (!Equal(oldValue, newValue)) { property.SetValue(this, newValue); Action = "Update"; } } }
private bool IsChanged(ListViewItem lvItems, Row row, SharedStringTable stringTable) { var changed = false; var displayName = TemplateHelper.GetCellValue(row, 1, stringTable); if (lvItems.SubItems[1].Text != displayName && !string.IsNullOrEmpty(displayName)) { lvItems.SubItems[1].Text = displayName; lvItems.SubItems[1].ForeColor = ColorUpdate; changed = true; } for (var i = 4; i < lvItems.SubItems.Count; i++) { var item = lvItems.SubItems[i]; var value = TemplateHelper.GetCellValue(row, 4, stringTable); if (item.Text != value && !string.IsNullOrEmpty(value)) { item.Text = value; item.ForeColor = ColorUpdate; changed = true; } } return(changed); }
// Given the main workbook part, and a text value, insert the text into // the shared string table. private static int InsertSharedStringItem(SharedStringTable stringTable, WorkbookPart wbPart, string value) { int _index = 0; bool _found = false; // 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 (!_found) { stringTable.AppendChild(new SharedStringItem(new Text(value))); } return(_index); }
/// <summary> /// Creates the shared string table part. /// </summary> /// <param name="sharedStringTablePart">The shared string table part.</param> /// <param name="sheetData">The sheet data.</param> private void CreateSharedStringTablePart(SharedStringTablePart sharedStringTablePart, DataTable sheetData) { UInt32Value stringCount = Convert.ToUInt32(sheetData.Rows.Count) + Convert.ToUInt32(sheetData.Columns.Count); SharedStringTable sharedStringTable = new SharedStringTable() { Count = stringCount, UniqueCount = stringCount }; for (int columnIndex = 0; columnIndex < sheetData.Columns.Count; columnIndex++) { SharedStringItem sharedStringItem = new SharedStringItem(); Text text = new Text(); text.Text = sheetData.Columns[columnIndex].ColumnName; sharedStringItem.Append(text); sharedStringTable.Append(sharedStringItem); } for (int rowIndex = 0; rowIndex < sheetData.Rows.Count; rowIndex++) { SharedStringItem sharedStringItem = new SharedStringItem(); Text text = new Text(); text.Text = sheetData.Rows[rowIndex][0].ToString(); sharedStringItem.Append(text); sharedStringTable.Append(sharedStringItem); } sharedStringTablePart.SharedStringTable = sharedStringTable; }
// 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. public static int InsertSharedStringItem(this WorkbookPart wbPart, string value) { // Insert a value into the shared string table, creating the table if necessary. // Insert the string if it's not already there. // Return the index of the string. 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>(); } SharedStringTable stringTable; if (stringTablePart != null && stringTablePart.SharedStringTable != null) { stringTable = stringTablePart.SharedStringTable; } else { stringTable = new SharedStringTable(); stringTablePart.SharedStringTable = 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 += 1; } if (!found) { if (stringTablePart.SharedStringTable.Count == null) { stringTablePart.SharedStringTable.AppendChild(new SharedStringItem(new Text(value))); stringTablePart.SharedStringTable.Save(); } else { stringTable.AppendChild(new SharedStringItem(new Text(value))); // stringTablePart.SharedStringTable.AppendChild(new SharedStringItem(new Text(value))); stringTable.Save(); } } return index; }
private void RowProcess(Row row, StringBuilder sb, SharedStringTable sharedStringTable) { sb.Append("<row>"); foreach (Cell cell in row.Elements <Cell>()) { string cellValue = string.Empty; sb.Append("<cell>"); if (cell.CellFormula != null) { cellValue = cell.CellValue.InnerText; sb.Append(cellValue); sb.Append("</cell>"); continue; } cellValue = cell.InnerText; if (cell.DataType != null && cell.DataType == CellValues.SharedString) { sb.Append(sharedStringTable.ElementAt(Int32.Parse(cellValue)).InnerText); } else { sb.Append(cellValue); } sb.Append("</cell>"); } sb.Append("</row>"); }
public static string GetOrAddSharedString(this DocumentContext documentContext, string text) { if (text == null) { return(null); } if (!documentContext.SharedStringTable.TryGetValue(text, out string stringIndex)) { SharedStringTable sharedStringTable = documentContext.GetSharedStringTable(); SharedStringItem sharedStringItem = new SharedStringItem(); Text text1 = new Text { Text = text }; sharedStringItem.Append(text1); sharedStringTable.AppendChild(sharedStringItem); uint itemCount = (uint)sharedStringTable.ChildElements.Count; sharedStringTable.Count = itemCount; sharedStringTable.UniqueCount = itemCount; stringIndex = (itemCount - 1).ToString(); documentContext.SharedStringTable.Add(text, stringIndex); } return(stringIndex); }
private async Task <IEnumerable <DataRow> > ExtractBodyRowsAsync(SharedStringTable sst, RowMap rowMap, IEnumerable <Row> rows) { var firstBodyRowIndex = rowMap.RowIndex + 1; var rowList = rows.Where(r => r.RowIndex >= firstBodyRowIndex).ToList(); var extractRowsTasks = rowList.Select(row => Task.Run(() => { var cells = ExtractCells(sst, row, rowMap.CellMaps); return(new DataRow { RowIndex = row.RowIndex, DataCells = cells }); })); var dataRows = await Task.WhenAll(extractRowsTasks); dataRows = dataRows .Where(r => r.DataCells.Any()) .OrderBy(r => r.RowIndex) .ToArray(); return(dataRows); }
public SharedStrings(SharedStringTable sst) { foreach (SharedStringItem item in sst.ChildElements.OfType <SharedStringItem>()) { Add(new SharedString(item)); } }
/// <summary> /// Generates content of sharedStringTablePart /// </summary> /// <param name="sharedStringTablePart">SharedStringTablePart Object</param> /// <param name="table">DataTable Object</param> private void CreateSharedStringTablePart(SharedStringTablePart sharedStringTablePart, DataTable table) { UInt32Value stringCount = Convert.ToUInt32(table.Rows.Count) + Convert.ToUInt32(table.Columns.Count); // Initialize an instance of SharedString Table SharedStringTable sharedStringTable = new SharedStringTable() { Count = stringCount, UniqueCount = stringCount }; // Add columns of DataTable to sharedString iteam for (int columnIndex = 0; columnIndex < table.Columns.Count; columnIndex++) { SharedStringItem sharedStringItem = new SharedStringItem(); Text text = new Text(); text.Text = table.Columns[columnIndex].ColumnName; sharedStringItem.Append(text); // Add sharedstring item to sharedstring Table sharedStringTable.Append(sharedStringItem); } // Add rows of DataTable to sharedString iteam for (int rowIndex = 0; rowIndex < table.Rows.Count; rowIndex++) { SharedStringItem sharedStringItem = new SharedStringItem(); Text text = new Text(); text.Text = table.Rows[rowIndex][0].ToString(); sharedStringItem.Append(text); sharedStringTable.Append(sharedStringItem); } sharedStringTablePart.SharedStringTable = sharedStringTable; }
private void FindHeadRow(SpreadsheetDocument doc, IEnumerable <Row> rows, SharedStringTable sharedStringTable) // Считываем заголовки столбцов и их буквенные инедксы { Row head_row = rows.Where(r => r.RowIndex == 1).FirstOrDefault(); if (columnName_columnIndex == null) { columnName_columnIndex = new List <Tuple <string, string> >(); } if (columnName_columnIndex != null) { foreach (Cell cell in head_row) { if (cell.CellValue != null && !String.IsNullOrWhiteSpace(cell.CellValue.InnerText) && cell.DataType?.Value != CellValues.Error) { if (cell.DataType != null) { if (cell.DataType == CellValues.SharedString) { columnName_columnIndex.Add(new Tuple <string, string>(sharedStringTable.ElementAt(int.Parse(cell.CellValue.InnerText)).InnerText, Regex.Match(cell.CellReference, "[A-Z]{1,}").Value)); } } else { columnName_columnIndex.Add(new Tuple <string, string>(cell.CellValue.InnerText, Regex.Match(cell.CellReference, "[A-Z]{1,}").Value)); } } } RemoveNeedless(); } }
/// <summary> /// 获取单元格值 /// </summary> /// <param name="cell"></param> /// <param name="sharedStringTable"></param> /// <returns></returns> static string GetCellVal(Cell cell, SharedStringTable sharedStringTable) { var val = cell.InnerText; if (cell.DataType != null) { switch (cell.DataType.Value) { //从共享表中获取值 case CellValues.SharedString: if (sharedStringTable != null) { val = sharedStringTable .ElementAt(int.Parse(val)) .InnerText; } break; default: val = string.Empty; break; } } return(val); }
private void ResetImportConfiguraion() { WorkSheetktExaminedGroup.Instance = null; _ktExaminedGroupSheet = null; _ktExaminedID = ""; _workSheetktExaminedGroup = WorkSheetktExaminedGroup.Instance; WorkSheetktUIGroupOrder.Instance = null; _ktUiGroupOrderSheet = null; _ktUiGroupOrderID = ""; _workSheetktUIGroupOrder = WorkSheetktUIGroupOrder.Instance; WorkSheetktUIOrder.Instance = null; _ktUiOrderSheet = null; _ktUiOrderID = ""; _workSheetktUIOrder = WorkSheetktUIOrder.Instance; WorkSheetktResources.Instance = null; _ktResourcesSheet = null; _ktResourcesID = ""; _workSheetktResources = WorkSheetktResources.Instance; WorkSheetktResourceTranslation.Instance = null; _ktResourceTranslationSheet = null; _ktResourceTranslationID = ""; _workSheetktResourceTranslation = WorkSheetktResourceTranslation.Instance; _workBook = null; _workSheets = null; _sharedStrings = null; }
private static string GetCellValue(SharedStringTable sharedStrings, DocumentFormat.OpenXml.Spreadsheet.Cell cell) { return cell.DataType != null && cell.DataType.HasValue && cell.DataType == CellValues.SharedString ? sharedStrings.ChildElements[ int.Parse(cell.CellValue.InnerText)].InnerText : cell.CellValue.InnerText; }
public SharedStringTableIndexer(SharedStringTable toIndex) { // Handle parameter checking in ctor. if (toIndex == null) { throw new ArgumentNullException("toIndex"); } this.Initialize(toIndex); }
// Generates content of sharedStringTablePart1. private void GenerateSharedStringTablePart1Content(SharedStringTablePart sharedStringTablePart1) { SharedStringTable sharedStringTable1 = new SharedStringTable() { Count = (UInt32Value)5U, UniqueCount = (UInt32Value)5U }; SharedStringItem sharedStringItem1 = new SharedStringItem(); Text text1 = new Text(); text1.Text = "HeaderVal"; sharedStringItem1.Append(text1); SharedStringItem sharedStringItem2 = new SharedStringItem(); Text text2 = new Text(); text2.Text = "HeaderNumber"; sharedStringItem2.Append(text2); SharedStringItem sharedStringItem3 = new SharedStringItem(); Text text3 = new Text(); text3.Text = "HeaderLink"; sharedStringItem3.Append(text3); SharedStringItem sharedStringItem4 = new SharedStringItem(); Text text4 = new Text(); text4.Text = "test"; sharedStringItem4.Append(text4); SharedStringItem sharedStringItem5 = new SharedStringItem(); Text text5 = new Text(); text5.Text = "http://google.com"; sharedStringItem5.Append(text5); sharedStringTable1.Append(sharedStringItem1); sharedStringTable1.Append(sharedStringItem2); sharedStringTable1.Append(sharedStringItem3); sharedStringTable1.Append(sharedStringItem4); sharedStringTable1.Append(sharedStringItem5); sharedStringTablePart1.SharedStringTable = sharedStringTable1; }
private static void ExtractRowsData(List<Object> data, Worksheet worksheet, SharedStringTable ssTable, CellFormats cellFormats) { var columnHeaders = worksheet.FirstRow().Descendants<Cell>().Select(c => Convert.ToString(ProcessCellValue(c, ssTable, cellFormats))).ToArray(); var columnHeadersCellReference = worksheet.FirstRow().Descendants<Cell>().Select(c => c.CellReference.InnerText.Replace("1", string.Empty)).ToArray(); var spreadsheetData = worksheet.SkipFirstRow(); int dataRowIndex = 2; foreach (var dataRow in spreadsheetData) { dynamic row = new ExpandoObject(); Cell[] rowCells = dataRow.Descendants<Cell>().ToArray(); for (int i = 0; i < columnHeaders.Length; i++) { // Selecciona y agrega la celda correcta al archivo de la fila. Cell cell = dataRow.Descendants<Cell>().Where(c => c.CellReference == columnHeadersCellReference[i] + dataRow.RowIndex).FirstOrDefault(); if (cell != null) ((IDictionary<String, Object>)row).Add(new KeyValuePair<String, Object>(dataRowIndex + "," + i, ProcessCellValue(cell, ssTable, cellFormats))); } data.Add(row); ++dataRowIndex; } }
// Generates content of sharedStringTablePart1. private void GenerateSharedStringTablePart1Content(SharedStringTablePart sharedStringTablePart1) { SharedStringTable sharedStringTable1 = new SharedStringTable() { Count = (UInt32Value)54U, UniqueCount = (UInt32Value)22U }; SharedStringItem sharedStringItem1 = new SharedStringItem(); Text text1 = new Text() { Space = SpaceProcessingModeValues.Preserve }; text1.Text = "Av. Corrientes 6277 ( 1427) Buenos Aires "; sharedStringItem1.Append(text1); SharedStringItem sharedStringItem2 = new SharedStringItem(); Text text2 = new Text(); text2.Text = "Argentina - Tel.: 4323-9931"; sharedStringItem2.Append(text2); SharedStringItem sharedStringItem3 = new SharedStringItem(); Text text3 = new Text(); text3.Text = "ORDEN DE PUBLICIDAD"; sharedStringItem3.Append(text3); SharedStringItem sharedStringItem4 = new SharedStringItem(); Text text4 = new Text(); text4.Text = "ORDEN DE PUBLICIDAD Nº"; sharedStringItem4.Append(text4); SharedStringItem sharedStringItem5 = new SharedStringItem(); Text text5 = new Text(); text5.Text = "MEDIO:"; sharedStringItem5.Append(text5); SharedStringItem sharedStringItem6 = new SharedStringItem(); Text text6 = new Text(); text6.Text = "CLIENTE:"; sharedStringItem6.Append(text6); SharedStringItem sharedStringItem7 = new SharedStringItem(); Text text7 = new Text(); text7.Text = "FECHA DE EMISION:"; sharedStringItem7.Append(text7); SharedStringItem sharedStringItem8 = new SharedStringItem(); Text text8 = new Text(); text8.Text = "CONTACTO:"; sharedStringItem8.Append(text8); SharedStringItem sharedStringItem9 = new SharedStringItem(); Text text9 = new Text() { Space = SpaceProcessingModeValues.Preserve }; text9.Text = "EMAIL: "; sharedStringItem9.Append(text9); SharedStringItem sharedStringItem10 = new SharedStringItem(); Text text10 = new Text(); text10.Text = "HORARIO:"; sharedStringItem10.Append(text10); SharedStringItem sharedStringItem11 = new SharedStringItem(); Text text11 = new Text(); text11.Text = "HORARIO"; sharedStringItem11.Append(text11); SharedStringItem sharedStringItem12 = new SharedStringItem(); Text text12 = new Text(); text12.Text = "LUNES"; sharedStringItem12.Append(text12); SharedStringItem sharedStringItem13 = new SharedStringItem(); Text text13 = new Text(); text13.Text = "MARTES"; sharedStringItem13.Append(text13); SharedStringItem sharedStringItem14 = new SharedStringItem(); Text text14 = new Text(); text14.Text = "MIERCOLES"; sharedStringItem14.Append(text14); SharedStringItem sharedStringItem15 = new SharedStringItem(); Text text15 = new Text(); text15.Text = "JUEVES"; sharedStringItem15.Append(text15); SharedStringItem sharedStringItem16 = new SharedStringItem(); Text text16 = new Text(); text16.Text = "VIERNES"; sharedStringItem16.Append(text16); SharedStringItem sharedStringItem17 = new SharedStringItem(); Text text17 = new Text(); text17.Text = "SABADO"; sharedStringItem17.Append(text17); SharedStringItem sharedStringItem18 = new SharedStringItem(); Text text18 = new Text(); text18.Text = "DOMINGO"; sharedStringItem18.Append(text18); SharedStringItem sharedStringItem19 = new SharedStringItem(); Text text19 = new Text(); text19.Text = "TOTAL SALIDAS"; sharedStringItem19.Append(text19); SharedStringItem sharedStringItem20 = new SharedStringItem(); Text text20 = new Text(); text20.Text = "COSTO X INFOMERCIAL"; sharedStringItem20.Append(text20); SharedStringItem sharedStringItem21 = new SharedStringItem(); Text text21 = new Text(); text21.Text = "SUBTOTAL"; sharedStringItem21.Append(text21); SharedStringItem sharedStringItem22 = new SharedStringItem(); Text text22 = new Text(); text22.Text = "TOTAL"; sharedStringItem22.Append(text22); sharedStringTable1.Append(sharedStringItem1); sharedStringTable1.Append(sharedStringItem2); sharedStringTable1.Append(sharedStringItem3); sharedStringTable1.Append(sharedStringItem4); sharedStringTable1.Append(sharedStringItem5); sharedStringTable1.Append(sharedStringItem6); sharedStringTable1.Append(sharedStringItem7); sharedStringTable1.Append(sharedStringItem8); sharedStringTable1.Append(sharedStringItem9); sharedStringTable1.Append(sharedStringItem10); sharedStringTable1.Append(sharedStringItem11); sharedStringTable1.Append(sharedStringItem12); sharedStringTable1.Append(sharedStringItem13); sharedStringTable1.Append(sharedStringItem14); sharedStringTable1.Append(sharedStringItem15); sharedStringTable1.Append(sharedStringItem16); sharedStringTable1.Append(sharedStringItem17); sharedStringTable1.Append(sharedStringItem18); sharedStringTable1.Append(sharedStringItem19); sharedStringTable1.Append(sharedStringItem20); sharedStringTable1.Append(sharedStringItem21); sharedStringTable1.Append(sharedStringItem22); sharedStringTablePart1.SharedStringTable = sharedStringTable1; }
/// <summary> /// Helper method for creating a list of ktExaminedGroup /// from an Excel worksheet. /// </summary> public bool LoadktResources(Worksheet worksheet, SharedStringTable sharedString) { Result = new List<ktResources>(); //Linq query to get the column headers on the sheet Row columnRow = (from row in worksheet.Descendants<Row>() where row.RowIndex == 1 select row).First(); IEnumerable<String> headerValues = from cell in columnRow.Descendants<Cell>() where cell.CellValue != null select (cell.DataType != null && cell.DataType.HasValue && cell.DataType == CellValues.SharedString ? sharedString.ChildElements[ int.Parse(cell.CellValue.InnerText)].InnerText : cell.CellValue.InnerText); foreach (string header in headerValues) { ColumnNames.Add(header); } if (CheckColumnNames(ColumnNames)) { //LINQ query to skip first row with column names. IEnumerable<Row> dataRows = from row in worksheet.Descendants<Row>() where row.RowIndex > 1 select row; if (CheckDataInSheets(dataRows)) { foreach (Row row in dataRows) { //LINQ query to return the row's cell values. //Where clause filters out any cells that do not contain a value. //Select returns the value of a cell unless the cell contains // a Shared String. //If the cell contains a Shared String, its value will be a // reference id which will be used to look up the value in the // Shared String table. IEnumerable<String> textValues = from cell in row.Descendants<Cell>() where cell.CellValue != null select (cell.DataType != null && cell.DataType.HasValue && cell.DataType == CellValues.SharedString ? sharedString.ChildElements[ int.Parse(cell.CellValue.InnerText)].InnerText : cell.CellValue.InnerText); //Check to verify the row contained data. if (textValues.Count() > 0) { var textArray = textValues.ToArray(); ktResources resource = new ktResources(); resource.ResourceID = textArray[0]; resource.ResourceTypeID = textArray[1]; resource.ResourceResxID = textArray[2]; Result.Add(resource); } else { //If no cells, then you have reached the end of the table. break; } //Return populated list of ktExaminedGroup. } } else { return false; } return true; } return false; }
/// <summary> /// Create a new <see cref="ExcelFile"/> from the specified stream. /// </summary> /// <param name="file">The excel file as a binary stream.</param> ExcelFile(Stream file) { this.spreadsheet = SpreadsheetDocument.Open(file, false); this.sharedStrings = spreadsheet.WorkbookPart.SharedStringTablePart.SharedStringTable; this.workbook = spreadsheet.WorkbookPart.Workbook; }
// Generates content of sharedStringTablePart1. private void GenerateSharedStringTablePart1Content(SharedStringTablePart sharedStringTablePart1) { SharedStringTable sharedStringTable1 = new SharedStringTable() { Count = (UInt32Value)77U, UniqueCount = (UInt32Value)35U }; SharedStringItem sharedStringItem1 = new SharedStringItem(); Text text1 = new Text() { Space = SpaceProcessingModeValues.Preserve }; text1.Text = "Av. Corrientes 6277 ( 1427) Buenos Aires "; sharedStringItem1.Append(text1); SharedStringItem sharedStringItem2 = new SharedStringItem(); Text text2 = new Text(); text2.Text = "Argentina - Tel.: 4323-9931"; sharedStringItem2.Append(text2); SharedStringItem sharedStringItem3 = new SharedStringItem(); Text text3 = new Text(); text3.Text = "ORDEN DE PUBLICIDAD"; sharedStringItem3.Append(text3); SharedStringItem sharedStringItem4 = new SharedStringItem(); Text text4 = new Text(); text4.Text = "MEDIO"; sharedStringItem4.Append(text4); SharedStringItem sharedStringItem5 = new SharedStringItem(); Text text5 = new Text(); text5.Text = "PROGRAMA:"; sharedStringItem5.Append(text5); SharedStringItem sharedStringItem6 = new SharedStringItem(); Text text6 = new Text(); text6.Text = "FECHA DE EMISION:"; sharedStringItem6.Append(text6); SharedStringItem sharedStringItem7 = new SharedStringItem(); Text text7 = new Text(); text7.Text = "CONTACTO:"; sharedStringItem7.Append(text7); SharedStringItem sharedStringItem8 = new SharedStringItem(); Text text8 = new Text(); text8.Text = "TELEFONO/ Fax:"; sharedStringItem8.Append(text8); SharedStringItem sharedStringItem9 = new SharedStringItem(); Text text9 = new Text(); text9.Text = "DIRECCION:"; sharedStringItem9.Append(text9); SharedStringItem sharedStringItem10 = new SharedStringItem(); Text text10 = new Text(); text10.Text = "E-MAIL:"; sharedStringItem10.Append(text10); SharedStringItem sharedStringItem11 = new SharedStringItem(); Text text11 = new Text(); text11.Text = "SALIDA"; sharedStringItem11.Append(text11); SharedStringItem sharedStringItem12 = new SharedStringItem(); Text text12 = new Text(); text12.Text = "DURACION"; sharedStringItem12.Append(text12); SharedStringItem sharedStringItem13 = new SharedStringItem(); Text text13 = new Text(); text13.Text = "LUNES"; sharedStringItem13.Append(text13); SharedStringItem sharedStringItem14 = new SharedStringItem(); Text text14 = new Text(); text14.Text = "MARTES"; sharedStringItem14.Append(text14); SharedStringItem sharedStringItem15 = new SharedStringItem(); Text text15 = new Text(); text15.Text = "MIÉRCOLES"; sharedStringItem15.Append(text15); SharedStringItem sharedStringItem16 = new SharedStringItem(); Text text16 = new Text(); text16.Text = "JUEVES"; sharedStringItem16.Append(text16); SharedStringItem sharedStringItem17 = new SharedStringItem(); Text text17 = new Text(); text17.Text = "VIERNES"; sharedStringItem17.Append(text17); SharedStringItem sharedStringItem18 = new SharedStringItem(); Text text18 = new Text(); text18.Text = "SÁBADO"; sharedStringItem18.Append(text18); SharedStringItem sharedStringItem19 = new SharedStringItem(); Text text19 = new Text(); text19.Text = "DOMINGO"; sharedStringItem19.Append(text19); SharedStringItem sharedStringItem20 = new SharedStringItem(); Text text20 = new Text(); text20.Text = "SALIDA 1"; sharedStringItem20.Append(text20); SharedStringItem sharedStringItem21 = new SharedStringItem(); Text text21 = new Text(); text21.Text = "SUBTOTAL"; sharedStringItem21.Append(text21); SharedStringItem sharedStringItem22 = new SharedStringItem(); Text text22 = new Text(); text22.Text = "HORARIO"; sharedStringItem22.Append(text22); SharedStringItem sharedStringItem23 = new SharedStringItem(); Text text23 = new Text(); text23.Text = "PRODUCTO"; sharedStringItem23.Append(text23); SharedStringItem sharedStringItem24 = new SharedStringItem(); Text text24 = new Text(); text24.Text = "EMPRESA"; sharedStringItem24.Append(text24); SharedStringItem sharedStringItem25 = new SharedStringItem(); Text text25 = new Text(); text25.Text = "ZOCALO"; sharedStringItem25.Append(text25); SharedStringItem sharedStringItem26 = new SharedStringItem(); Text text26 = new Text(); text26.Text = "COD. INGESTA"; sharedStringItem26.Append(text26); SharedStringItem sharedStringItem27 = new SharedStringItem(); Text text27 = new Text(); text27.Text = "SALIDAS"; sharedStringItem27.Append(text27); SharedStringItem sharedStringItem28 = new SharedStringItem(); Text text28 = new Text(); text28.Text = "PNTS TOTALES:"; sharedStringItem28.Append(text28); SharedStringItem sharedStringItem29 = new SharedStringItem(); Text text29 = new Text(); text29.Text = "COSTO POR SALIDA"; sharedStringItem29.Append(text29); SharedStringItem sharedStringItem30 = new SharedStringItem(); Text text30 = new Text(); text30.Text = "IVA 21%:"; sharedStringItem30.Append(text30); SharedStringItem sharedStringItem31 = new SharedStringItem(); Text text31 = new Text(); text31.Text = "TOTAL"; sharedStringItem31.Append(text31); SharedStringItem sharedStringItem32 = new SharedStringItem(); Text text32 = new Text(); text32.Text = "NUMERO"; sharedStringItem32.Append(text32); SharedStringItem sharedStringItem33 = new SharedStringItem(); Text text33 = new Text(); text33.Text = "SPRAYETTE"; sharedStringItem33.Append(text33); SharedStringItem sharedStringItem34 = new SharedStringItem(); Text text34 = new Text(); text34.Text = "EXCLUSIVE"; sharedStringItem34.Append(text34); SharedStringItem sharedStringItem35 = new SharedStringItem(); Text text35 = new Text(); text35.Text = "POLISHOP"; sharedStringItem35.Append(text35); sharedStringTable1.Append(sharedStringItem1); sharedStringTable1.Append(sharedStringItem2); sharedStringTable1.Append(sharedStringItem3); sharedStringTable1.Append(sharedStringItem4); sharedStringTable1.Append(sharedStringItem5); sharedStringTable1.Append(sharedStringItem6); sharedStringTable1.Append(sharedStringItem7); sharedStringTable1.Append(sharedStringItem8); sharedStringTable1.Append(sharedStringItem9); sharedStringTable1.Append(sharedStringItem10); sharedStringTable1.Append(sharedStringItem11); sharedStringTable1.Append(sharedStringItem12); sharedStringTable1.Append(sharedStringItem13); sharedStringTable1.Append(sharedStringItem14); sharedStringTable1.Append(sharedStringItem15); sharedStringTable1.Append(sharedStringItem16); sharedStringTable1.Append(sharedStringItem17); sharedStringTable1.Append(sharedStringItem18); sharedStringTable1.Append(sharedStringItem19); sharedStringTable1.Append(sharedStringItem20); sharedStringTable1.Append(sharedStringItem21); sharedStringTable1.Append(sharedStringItem22); sharedStringTable1.Append(sharedStringItem23); sharedStringTable1.Append(sharedStringItem24); sharedStringTable1.Append(sharedStringItem25); sharedStringTable1.Append(sharedStringItem26); sharedStringTable1.Append(sharedStringItem27); sharedStringTable1.Append(sharedStringItem28); sharedStringTable1.Append(sharedStringItem29); sharedStringTable1.Append(sharedStringItem30); sharedStringTable1.Append(sharedStringItem31); sharedStringTable1.Append(sharedStringItem32); sharedStringTable1.Append(sharedStringItem33); sharedStringTable1.Append(sharedStringItem34); sharedStringTable1.Append(sharedStringItem35); sharedStringTablePart1.SharedStringTable = sharedStringTable1; }
// Generates content of sharedStringTablePart1. protected override void GenerateSharedStringTablePartContent(SharedStringTablePart sharedStringTablePart) { SharedStringTable sharedStringTable1 = new SharedStringTable() { Count = (UInt32Value)66U, UniqueCount = (UInt32Value)65U }; SharedStringItem sharedStringItem1 = new SharedStringItem(); Text text1 = new Text(); text1.Text = sampleSelectionRequest.Executor.Name; sharedStringItem1.Append(text1); SharedStringItem sharedStringItem2 = new SharedStringItem(); Text text2 = new Text(); text2.Text = "(наименование государственного учреждения)"; sharedStringItem2.Append(text2); SharedStringItem sharedStringItem3 = new SharedStringItem(); Text text3 = new Text(); text3.Text = "Регистрационный №"; sharedStringItem3.Append(text3); SharedStringItem sharedStringItem4 = new SharedStringItem(); Text text4 = new Text(); text4.Text = "Дата регистрации"; sharedStringItem4.Append(text4); SharedStringItem sharedStringItem5 = new SharedStringItem(); Text text5 = new Text(); text5.Text = "на проведение апробации сельскохозяйственного растения"; sharedStringItem5.Append(text5); SharedStringItem sharedStringItem6 = new SharedStringItem(); Text text6 = new Text(); text6.Text = "1."; sharedStringItem6.Append(text6); SharedStringItem sharedStringItem7 = new SharedStringItem(); Text text7 = new Text(); text7.Text = "Сведения о заявителе"; sharedStringItem7.Append(text7); SharedStringItem sharedStringItem8 = new SharedStringItem(); Text text8 = new Text(); text8.Text = "1.1."; sharedStringItem8.Append(text8); SharedStringItem sharedStringItem9 = new SharedStringItem(); Text text9 = new Text(); text9.Text = "Наименование юридического лица; фамилия, собственное имя и отчество (если таковое имеется)"; sharedStringItem9.Append(text9); SharedStringItem sharedStringItem10 = new SharedStringItem(); Text text10 = new Text(); text10.Text = "физического лица, в том числе индивидуального предпринимателя"; sharedStringItem10.Append(text10); SharedStringItem sharedStringItem11 = new SharedStringItem(); Text text11 = new Text(); text11.Text = sampleSelectionRequest.Client.Name; sharedStringItem11.Append(text11); SharedStringItem sharedStringItem12 = new SharedStringItem(); Text text12 = new Text(); text12.Text = "1.2."; sharedStringItem12.Append(text12); SharedStringItem sharedStringItem13 = new SharedStringItem(); Text text13 = new Text(); text13.Text = "Место нахождения юридического лица; адрес регистрации по месту жительства (месту пребывания)"; sharedStringItem13.Append(text13); SharedStringItem sharedStringItem14 = new SharedStringItem(); Text text14 = new Text(); text14.Text = sampleSelectionRequest.Client.Address; sharedStringItem14.Append(text14); SharedStringItem sharedStringItem15 = new SharedStringItem(); Text text15 = new Text(); text15.Text = "1.3."; sharedStringItem15.Append(text15); SharedStringItem sharedStringItem16 = new SharedStringItem(); Text text16 = new Text(); text16.Text = "Номер телефона, факс, адрес электронной почты"; sharedStringItem16.Append(text16); SharedStringItem sharedStringItem17 = new SharedStringItem(); Text text17 = new Text(); text17.Text = sampleSelectionRequest.Client.Telephone; sharedStringItem17.Append(text17); SharedStringItem sharedStringItem18 = new SharedStringItem(); Text text18 = new Text(); text18.Text = "Прошу провести апробацию посева (посадки) сельскохозяйственного растения"; sharedStringItem18.Append(text18); SharedStringItem sharedStringItem19 = new SharedStringItem(); Text text19 = new Text(); text19.Text = "2."; sharedStringItem19.Append(text19); SharedStringItem sharedStringItem20 = new SharedStringItem(); Text text20 = new Text(); text20.Text = "Сведения о семенах сельскохозяйственного растения и его посеве (посадке)"; sharedStringItem20.Append(text20); SharedStringItem sharedStringItem21 = new SharedStringItem(); Text text21 = new Text(); text21.Text = "2.1."; sharedStringItem21.Append(text21); SharedStringItem sharedStringItem22 = new SharedStringItem(); Text text22 = new Text(); text22.Text = "Наименование сельскохозяйственного растения"; sharedStringItem22.Append(text22); SharedStringItem sharedStringItem23 = new SharedStringItem(); Text text23 = new Text(); text23.Text = "2.2."; sharedStringItem23.Append(text23); SharedStringItem sharedStringItem24 = new SharedStringItem(); Text text24 = new Text(); text24.Text = "Наименование сорта сельскохозяйственного растения"; sharedStringItem24.Append(text24); SharedStringItem sharedStringItem25 = new SharedStringItem(); Text text25 = new Text(); text25.Text = sampleSelectionRequest.PlantVariety.Plant.Name; sharedStringItem25.Append(text25); SharedStringItem sharedStringItem26 = new SharedStringItem(); Text text26 = new Text(); text26.Text = sampleSelectionRequest.PlantVariety.Name; sharedStringItem26.Append(text26); SharedStringItem sharedStringItem27 = new SharedStringItem(); Text text27 = new Text(); text27.Text = "2.3."; sharedStringItem27.Append(text27); SharedStringItem sharedStringItem28 = new SharedStringItem(); Text text28 = new Text(); text28.Text = "Категория высеянных семян сельскохозяйственного растения"; sharedStringItem28.Append(text28); SharedStringItem sharedStringItem29 = new SharedStringItem(); Text text29 = new Text(); text29.Text = "2.4."; sharedStringItem29.Append(text29); SharedStringItem sharedStringItem30 = new SharedStringItem(); Text text30 = new Text(); text30.Text = "Этап размножения высеянных семян сельскохозяйственного растения"; sharedStringItem30.Append(text30); SharedStringItem sharedStringItem31 = new SharedStringItem(); Text text31 = new Text(); text31.Text = sampleSelectionRequest.PlantVariety.Plant.Category; sharedStringItem31.Append(text31); SharedStringItem sharedStringItem32 = new SharedStringItem(); Text text32 = new Text(); text32.Text = sampleSelectionRequest.ReproductionStep.Name; sharedStringItem32.Append(text32); SharedStringItem sharedStringItem33 = new SharedStringItem(); Text text33 = new Text(); text33.Text = "2.5."; sharedStringItem33.Append(text33); SharedStringItem sharedStringItem34 = new SharedStringItem(); Text text34 = new Text(); text34.Text = "Номер партии высеянных семян сельскохозяйственного растения"; sharedStringItem34.Append(text34); SharedStringItem sharedStringItem35 = new SharedStringItem(); Text text35 = new Text(); text35.Text = "2.6."; sharedStringItem35.Append(text35); SharedStringItem sharedStringItem36 = new SharedStringItem(); Text text36 = new Text(); text36.Text = "Номер и дача выдачи документа, подтверждающего посевные и сортовые качества семян сельскохозяйственного растения"; sharedStringItem36.Append(text36); SharedStringItem sharedStringItem37 = new SharedStringItem(); Text text37 = new Text(); text37.Text = sampleSelectionRequest.SeedsBatchNumber; sharedStringItem37.Append(text37); SharedStringItem sharedStringItem38 = new SharedStringItem(); Text text38 = new Text(); text38.Text = "2.7."; sharedStringItem38.Append(text38); SharedStringItem sharedStringItem39 = new SharedStringItem(); Text text39 = new Text(); text39.Text = "Количество высеянных семян (единиц)"; sharedStringItem39.Append(text39); SharedStringItem sharedStringItem40 = new SharedStringItem(); Text text40 = new Text(); text40.Text = "2.8."; sharedStringItem40.Append(text40); SharedStringItem sharedStringItem41 = new SharedStringItem(); Text text41 = new Text(); text41.Text = "Площадь поля, участка, посева (посадки)"; sharedStringItem41.Append(text41); SharedStringItem sharedStringItem42 = new SharedStringItem(); Text text42 = new Text(); text42.Text = "2.9."; sharedStringItem42.Append(text42); SharedStringItem sharedStringItem43 = new SharedStringItem(); Text text43 = new Text(); text43.Text = "Номер поля, участка, посева (посадки)"; sharedStringItem43.Append(text43); SharedStringItem sharedStringItem44 = new SharedStringItem(); Text text44 = new Text(); text44.Text = "2.10."; sharedStringItem44.Append(text44); SharedStringItem sharedStringItem45 = new SharedStringItem(); Text text45 = new Text(); text45.Text = "Место расположения поля, участка, посева (посадки)"; sharedStringItem45.Append(text45); SharedStringItem sharedStringItem46 = new SharedStringItem(); Text text46 = new Text(); text46.Text = "2.11."; sharedStringItem46.Append(text46); SharedStringItem sharedStringItem47 = new SharedStringItem(); Text text47 = new Text(); text47.Text = "Происхождение семян сельскохозяйственных растений"; sharedStringItem47.Append(text47); SharedStringItem sharedStringItem48 = new SharedStringItem(); Text text48 = new Text(); text48.Text = "2.12."; sharedStringItem48.Append(text48); SharedStringItem sharedStringItem49 = new SharedStringItem(); Text text49 = new Text(); text49.Text = "Дата посева (посадки)"; sharedStringItem49.Append(text49); SharedStringItem sharedStringItem50 = new SharedStringItem(); Text text50 = new Text(); text50.Text = "2.13."; sharedStringItem50.Append(text50); SharedStringItem sharedStringItem51 = new SharedStringItem(); Text text51 = new Text(); text51.Text = "Сельскохозяйственные растения, предшествующие посеву (посадке) (указать год)"; sharedStringItem51.Append(text51); SharedStringItem sharedStringItem52 = new SharedStringItem(); Text text52 = new Text(); text52.Text = "2.14."; sharedStringItem52.Append(text52); SharedStringItem sharedStringItem53 = new SharedStringItem(); Text text53 = new Text(); text53.Text = "Метод определения сортовой чистоты или сортовой типичности сельскохозяйственных растений, семена которых в случае реализации подлежат обязательному грунтовому контролю и (или) лабораторному сортовому контролю:"; sharedStringItem53.Append(text53); SharedStringItem sharedStringItem54 = new SharedStringItem(); Text text54 = new Text(); text54.Text = "[ ] грунтовой контроль"; sharedStringItem54.Append(text54); SharedStringItem sharedStringItem55 = new SharedStringItem(); Text text55 = new Text(); text55.Text = "[ ] лабораторный сортовой контроль"; sharedStringItem55.Append(text55); SharedStringItem sharedStringItem56 = new SharedStringItem(); Text text56 = new Text(); text56.Text = "3. [ ] Вся информация, изложенная в заявлении, является окончательной и достоверной."; sharedStringItem56.Append(text56); SharedStringItem sharedStringItem57 = new SharedStringItem(); Text text57 = new Text(); text57.Text = "(должность представителя заявителя)"; sharedStringItem57.Append(text57); SharedStringItem sharedStringItem58 = new SharedStringItem(); Text text58 = new Text(); text58.Text = "(подпись)"; sharedStringItem58.Append(text58); SharedStringItem sharedStringItem59 = new SharedStringItem(); Text text59 = new Text(); text59.Text = "инициалы, фамилия"; sharedStringItem59.Append(text59); SharedStringItem sharedStringItem60 = new SharedStringItem(); Text text60 = new Text(); text60.Text = "5 июня 2016 г."; sharedStringItem60.Append(text60); SharedStringItem sharedStringItem61 = new SharedStringItem(); Text text61 = new Text(); text61.Text = "* Для зерновых, зернобобовых, кукурузы - в течение минимум 2 лет; для крестоцветных кормовых и масличных, злаковых и бобовых трав - в течение минимум 5 лет; для прочих растений - в течение минимум 1 года."; sharedStringItem61.Append(text61); SharedStringItem sharedStringItem62 = new SharedStringItem(); Text text62 = new Text(); text62.Text = "ЗАЯВЛЕНИЕ"; sharedStringItem62.Append(text62); SharedStringItem sharedStringItem63 = new SharedStringItem(); Text text63 = new Text(); text63.Text = "М.П. (при наличии)"; sharedStringItem63.Append(text63); SharedStringItem sharedStringItem64 = new SharedStringItem(); Text text64 = new Text(); text64.Text = "4. К заявлению прилагаются"; sharedStringItem64.Append(text64); SharedStringItem sharedStringItem65 = new SharedStringItem(); Text text65 = new Text(); text65.Text = "5. Дополнительные сведения"; sharedStringItem65.Append(text65); sharedStringTable1.Append(sharedStringItem1); sharedStringTable1.Append(sharedStringItem2); sharedStringTable1.Append(sharedStringItem3); sharedStringTable1.Append(sharedStringItem4); sharedStringTable1.Append(sharedStringItem5); sharedStringTable1.Append(sharedStringItem6); sharedStringTable1.Append(sharedStringItem7); sharedStringTable1.Append(sharedStringItem8); sharedStringTable1.Append(sharedStringItem9); sharedStringTable1.Append(sharedStringItem10); sharedStringTable1.Append(sharedStringItem11); sharedStringTable1.Append(sharedStringItem12); sharedStringTable1.Append(sharedStringItem13); sharedStringTable1.Append(sharedStringItem14); sharedStringTable1.Append(sharedStringItem15); sharedStringTable1.Append(sharedStringItem16); sharedStringTable1.Append(sharedStringItem17); sharedStringTable1.Append(sharedStringItem18); sharedStringTable1.Append(sharedStringItem19); sharedStringTable1.Append(sharedStringItem20); sharedStringTable1.Append(sharedStringItem21); sharedStringTable1.Append(sharedStringItem22); sharedStringTable1.Append(sharedStringItem23); sharedStringTable1.Append(sharedStringItem24); sharedStringTable1.Append(sharedStringItem25); sharedStringTable1.Append(sharedStringItem26); sharedStringTable1.Append(sharedStringItem27); sharedStringTable1.Append(sharedStringItem28); sharedStringTable1.Append(sharedStringItem29); sharedStringTable1.Append(sharedStringItem30); sharedStringTable1.Append(sharedStringItem31); sharedStringTable1.Append(sharedStringItem32); sharedStringTable1.Append(sharedStringItem33); sharedStringTable1.Append(sharedStringItem34); sharedStringTable1.Append(sharedStringItem35); sharedStringTable1.Append(sharedStringItem36); sharedStringTable1.Append(sharedStringItem37); sharedStringTable1.Append(sharedStringItem38); sharedStringTable1.Append(sharedStringItem39); sharedStringTable1.Append(sharedStringItem40); sharedStringTable1.Append(sharedStringItem41); sharedStringTable1.Append(sharedStringItem42); sharedStringTable1.Append(sharedStringItem43); sharedStringTable1.Append(sharedStringItem44); sharedStringTable1.Append(sharedStringItem45); sharedStringTable1.Append(sharedStringItem46); sharedStringTable1.Append(sharedStringItem47); sharedStringTable1.Append(sharedStringItem48); sharedStringTable1.Append(sharedStringItem49); sharedStringTable1.Append(sharedStringItem50); sharedStringTable1.Append(sharedStringItem51); sharedStringTable1.Append(sharedStringItem52); sharedStringTable1.Append(sharedStringItem53); sharedStringTable1.Append(sharedStringItem54); sharedStringTable1.Append(sharedStringItem55); sharedStringTable1.Append(sharedStringItem56); sharedStringTable1.Append(sharedStringItem57); sharedStringTable1.Append(sharedStringItem58); sharedStringTable1.Append(sharedStringItem59); sharedStringTable1.Append(sharedStringItem60); sharedStringTable1.Append(sharedStringItem61); sharedStringTable1.Append(sharedStringItem62); sharedStringTable1.Append(sharedStringItem63); sharedStringTable1.Append(sharedStringItem64); sharedStringTable1.Append(sharedStringItem65); sharedStringTablePart.SharedStringTable = sharedStringTable1; }
public static List<Client> LoadClients(Worksheet worksheet, SharedStringTable sharedString) { List<Client> result = new List<Client>(); IEnumerable<Row> dataRows = from row in worksheet.Descendants<Row>() where row.RowIndex > 1 select row; foreach (Row row in dataRows) { IEnumerable<String> textValues = from cell in row.Descendants<Cell>() where cell.CellValue != null select (cell.DataType != null && cell.DataType.HasValue && cell.DataType == CellValues.SharedString ? sharedString.ChildElements[ int.Parse(cell.CellValue.InnerText)].InnerText : cell.CellValue.InnerText) ; if (textValues.Count() > 0) { var textArray = textValues.ToArray(); if (textArray.Length > 0) { Client client = new Client(); client.Name = textArray[0]; client.SortedName = client.Name.ToLower(); if (client.Name.ToLower().StartsWith("the ")) client.SortedName = client.Name.Substring(4).ToLower(); if (textArray.Length > 1) client.Location = textArray[1]; result.Add(client); } } else { break; } } return result; }
// Generates content of sharedStringTablePart1. protected override void GenerateSharedStringTablePartContent(SharedStringTablePart sharedStringTablePart) { SharedStringTable sharedStringTable1 = new SharedStringTable() { Count = (UInt32Value)65U, UniqueCount = (UInt32Value)42U }; SharedStringItem sharedStringItem1 = new SharedStringItem(); Text text1 = new Text(); text1.Text = "Поставщик:"; sharedStringItem1.Append(text1); SharedStringItem sharedStringItem2 = new SharedStringItem(); Text text2 = new Text(); text2.Text = sampleSelectionRequest.Executor.ToString(); sharedStringItem2.Append(text2); SharedStringItem sharedStringItem3 = new SharedStringItem(); Text text3 = new Text(); text3.Text = "СЧЕТ-ФАКТУРА"; sharedStringItem3.Append(text3); SharedStringItem sharedStringItem4 = new SharedStringItem(); Text text4 = new Text(); text4.Text = "№" + sampleSelectionRequest.InvoiceNumber; sharedStringItem4.Append(text4); SharedStringItem sharedStringItem5 = new SharedStringItem(); Text text5 = new Text(); text5.Text = "от " + sampleSelectionRequest.CreationDate.ToLongDateString(); sharedStringItem5.Append(text5); SharedStringItem sharedStringItem6 = new SharedStringItem(); Text text6 = new Text(); text6.Text = "предоплата за клубневой анализ"; sharedStringItem6.Append(text6); SharedStringItem sharedStringItem7 = new SharedStringItem(); Text text7 = new Text(); text7.Text = "Плательщик:"; sharedStringItem7.Append(text7); SharedStringItem sharedStringItem8 = new SharedStringItem(); Text text8 = new Text(); text8.Text = sampleSelectionRequest.Client.ToString(); sharedStringItem8.Append(text8); SharedStringItem sharedStringItem9 = new SharedStringItem(); Text text9 = new Text(); text9.Text = "Р/сч №"; sharedStringItem9.Append(text9); var bankRequisitesSubject = sampleSelectionRequest.Executor; SharedStringItem sharedStringItem10 = new SharedStringItem(); Text text10 = new Text(); text10.Text = bankRequisitesSubject.AccountNumber; sharedStringItem10.Append(text10); SharedStringItem sharedStringItem11 = new SharedStringItem(); Text text11 = new Text(); text11.Text = "Банк"; sharedStringItem11.Append(text11); SharedStringItem sharedStringItem12 = new SharedStringItem(); Text text12 = new Text(); text12.Text = bankRequisitesSubject.AccountBank.Name; sharedStringItem12.Append(text12); SharedStringItem sharedStringItem13 = new SharedStringItem(); Text text13 = new Text(); text13.Text = "Код банка"; sharedStringItem13.Append(text13); SharedStringItem sharedStringItem14 = new SharedStringItem(); Text text14 = new Text(); text14.Text = bankRequisitesSubject.AccountBank.Code.ToString(); sharedStringItem14.Append(text14); SharedStringItem sharedStringItem15 = new SharedStringItem(); Text text15 = new Text(); text15.Text = "УНН"; sharedStringItem15.Append(text15); SharedStringItem sharedStringItem16 = new SharedStringItem(); Text text16 = new Text(); text16.Text = string.Format( "Дополнение. Основание: прейскурант стоимости услуг, выполняемых испекцией по семеноводству № {0} от {1:D}", sampleSelectionRequest.PriceList.Number, sampleSelectionRequest.PriceList.BeginDate); sharedStringItem16.Append(text16); SharedStringItem sharedStringItem17 = new SharedStringItem(); Text text17 = new Text(); text17.Text = "наименование"; sharedStringItem17.Append(text17); SharedStringItem sharedStringItem18 = new SharedStringItem(); Text text18 = new Text(); text18.Text = "к-во"; sharedStringItem18.Append(text18); SharedStringItem sharedStringItem19 = new SharedStringItem(); Text text19 = new Text(); text19.Text = "цена"; sharedStringItem19.Append(text19); SharedStringItem sharedStringItem20 = new SharedStringItem(); Text text20 = new Text(); text20.Text = "стоимость"; sharedStringItem20.Append(text20); SharedStringItem sharedStringItem21 = new SharedStringItem(); Text text21 = new Text(); text21.Text = "%ндс"; sharedStringItem21.Append(text21); SharedStringItem sharedStringItem22 = new SharedStringItem(); Text text22 = new Text(); text22.Text = "Сумма"; sharedStringItem22.Append(text22); SharedStringItem sharedStringItem23 = new SharedStringItem(); Text text23 = new Text(); text23.Text = "всего с НДС"; sharedStringItem23.Append(text23); SharedStringItem sharedStringItem24 = new SharedStringItem(); Text text24 = new Text(); text24.Text = "Полный анализ семян"; sharedStringItem24.Append(text24); SharedStringItem sharedStringItem25 = new SharedStringItem(); Text text25 = new Text(); text25.Text = "54321"; sharedStringItem25.Append(text25); SharedStringItem sharedStringItem26 = new SharedStringItem(); Text text26 = new Text(); text26.Text = "Анализ семян на чистоту"; sharedStringItem26.Append(text26); SharedStringItem sharedStringItem27 = new SharedStringItem(); Text text27 = new Text(); text27.Text = "Анализ семян на влажность"; sharedStringItem27.Append(text27); SharedStringItem sharedStringItem28 = new SharedStringItem(); Text text28 = new Text(); text28.Text = "Анализ семян на заселенность вредителями"; sharedStringItem28.Append(text28); SharedStringItem sharedStringItem29 = new SharedStringItem(); Text text29 = new Text(); text29.Text = "Анализ семян на всхожесть"; sharedStringItem29.Append(text29); SharedStringItem sharedStringItem30 = new SharedStringItem(); Text text30 = new Text(); text30.Text = "Анализ семян на определение массы 1000 семян"; sharedStringItem30.Append(text30); SharedStringItem sharedStringItem31 = new SharedStringItem(); Text text31 = new Text(); text31.Text = "Анализ семян на жизнеспособность"; sharedStringItem31.Append(text31); SharedStringItem sharedStringItem32 = new SharedStringItem(); Text text32 = new Text(); text32.Text = "Анализ (для картофеля, лука и т.д.)"; sharedStringItem32.Append(text32); SharedStringItem sharedStringItem33 = new SharedStringItem(); Text text33 = new Text(); text33.Text = "Фитоанализ"; sharedStringItem33.Append(text33); SharedStringItem sharedStringItem34 = new SharedStringItem(); Text text34 = new Text(); text34.Text = "Определение алкалоидности семян люпина"; sharedStringItem34.Append(text34); SharedStringItem sharedStringItem35 = new SharedStringItem(); Text text35 = new Text(); text35.Text = "Выдача удостоверения (уведомления)"; sharedStringItem35.Append(text35); SharedStringItem sharedStringItem36 = new SharedStringItem(); Text text36 = new Text(); text36.Text = "Продление срока действия удостоверения"; sharedStringItem36.Append(text36); SharedStringItem sharedStringItem37 = new SharedStringItem(); Text text37 = new Text(); text37.Text = "Итого:"; sharedStringItem37.Append(text37); SharedStringItem sharedStringItem38 = new SharedStringItem(); Text text38 = new Text(); text38.Text = "Всего сумма НДС:"; sharedStringItem38.Append(text38); SharedStringItem sharedStringItem39 = new SharedStringItem(); Text text39 = new Text(); text39.Text = "Всего с учётом НДС:"; sharedStringItem39.Append(text39); SharedStringItem sharedStringItem40 = new SharedStringItem(); Text text40 = new Text(); text40.Text = "* Счет-фактура является протоколом согласование цен"; sharedStringItem40.Append(text40); SharedStringItem sharedStringItem41 = new SharedStringItem(); Text text41 = new Text(); text41.Text = "проба"; sharedStringItem41.Append(text41); SharedStringItem sharedStringItem42 = new SharedStringItem(); Text text42 = new Text(); text42.Text = "ед. изм."; sharedStringItem42.Append(text42); SharedStringItem sharedStringItem43 = new SharedStringItem(); Text text43 = new Text(); text43.Text = sumVat.ToString(SeedsConfiguration.Current.Ui.CurrencyFormat); sharedStringItem43.Append(text43); SharedStringItem sharedStringItem44 = new SharedStringItem(); Text text44 = new Text(); text44.Text = sumFullCost.ToString(SeedsConfiguration.Current.Ui.CurrencyFormat); sharedStringItem44.Append(text44); sharedStringTable1.Append(sharedStringItem1); sharedStringTable1.Append(sharedStringItem2); sharedStringTable1.Append(sharedStringItem3); sharedStringTable1.Append(sharedStringItem4); sharedStringTable1.Append(sharedStringItem5); sharedStringTable1.Append(sharedStringItem6); sharedStringTable1.Append(sharedStringItem7); sharedStringTable1.Append(sharedStringItem8); sharedStringTable1.Append(sharedStringItem9); sharedStringTable1.Append(sharedStringItem10); sharedStringTable1.Append(sharedStringItem11); sharedStringTable1.Append(sharedStringItem12); sharedStringTable1.Append(sharedStringItem13); sharedStringTable1.Append(sharedStringItem14); sharedStringTable1.Append(sharedStringItem15); sharedStringTable1.Append(sharedStringItem16); sharedStringTable1.Append(sharedStringItem17); sharedStringTable1.Append(sharedStringItem18); sharedStringTable1.Append(sharedStringItem19); sharedStringTable1.Append(sharedStringItem20); sharedStringTable1.Append(sharedStringItem21); sharedStringTable1.Append(sharedStringItem22); sharedStringTable1.Append(sharedStringItem23); sharedStringTable1.Append(sharedStringItem24); sharedStringTable1.Append(sharedStringItem25); sharedStringTable1.Append(sharedStringItem26); sharedStringTable1.Append(sharedStringItem27); sharedStringTable1.Append(sharedStringItem28); sharedStringTable1.Append(sharedStringItem29); sharedStringTable1.Append(sharedStringItem30); sharedStringTable1.Append(sharedStringItem31); sharedStringTable1.Append(sharedStringItem32); sharedStringTable1.Append(sharedStringItem33); sharedStringTable1.Append(sharedStringItem34); sharedStringTable1.Append(sharedStringItem35); sharedStringTable1.Append(sharedStringItem36); sharedStringTable1.Append(sharedStringItem37); sharedStringTable1.Append(sharedStringItem38); sharedStringTable1.Append(sharedStringItem39); sharedStringTable1.Append(sharedStringItem40); sharedStringTable1.Append(sharedStringItem41); sharedStringTable1.Append(sharedStringItem42); sharedStringTable1.Append(sharedStringItem43); sharedStringTable1.Append(sharedStringItem44); sharedStringTablePart.SharedStringTable = sharedStringTable1; }
/// <summary> /// Helper method for creating a list of ktExaminedGroup /// from an Excel worksheet. /// </summary> public void LoadQAGroups(Worksheet worksheet, SharedStringTable sharedString) { //Initialize the ktExaminedGroup list. List<QAGroup> result = new List<QAGroup>(); //LINQ query to skip first row with column names. IEnumerable<Row> dataRows = from row in worksheet.Descendants<Row>() where row.RowIndex > 1 select row; foreach (Row row in dataRows) { //LINQ query to return the row's cell values. //Where clause filters out any cells that do not contain a value. //Select returns the value of a cell unless the cell contains // a Shared String. //If the cell contains a Shared String, its value will be a // reference id which will be used to look up the value in the // Shared String table. //IEnumerable<String> textValues = // from cell in row.Descendants<Cell>() // where cell.CellValue != null // select // (cell.DataType != null // && cell.DataType.HasValue // && cell.DataType == CellValues.SharedString // ? sharedString.ChildElements[ // int.Parse(cell.CellValue.InnerText)].InnerText // : cell.CellValue.InnerText) // ; IEnumerable<String> textValues = from cell in row.Descendants<Cell>() where cell.CellValue != null select (cell.DataType != null && cell.DataType.HasValue && cell.DataType == CellValues.SharedString ? sharedString.ChildElements[ int.Parse(cell.CellValue.InnerText)].InnerText : cell.CellValue.InnerText) ; //Check to verify the row contained data. if (textValues.Count() > 0) { //Create a ktExaminedGroup and add it to the list. var textArray = textValues.ToArray(); QAGroup qaGroup = new QAGroup(); qaGroup.TypeID = textArray[0]; qaGroup.Type = textArray[1]; result.Add(qaGroup); } else { //If no cells, then you have reached the end of the table. break; } //Return populated list of ktExaminedGroup. QAGroupsList = result; } }
/// <summary> /// Helper method for creating a list of ktExaminedGroup /// from an Excel worksheet. /// </summary> public bool LoadExaminedGroup(Worksheet worksheet, SharedStringTable sharedString) { Result = new List<ktExaminedGroup>(); //Linq query to get the column headers on the sheet Row columnRow = (from row in worksheet.Descendants<Row>() where row.RowIndex == 1 select row).First(); IEnumerable<String> headerValues = from cell in columnRow.Descendants<Cell>() where cell.CellValue != null select (cell.DataType != null && cell.DataType.HasValue && cell.DataType == CellValues.SharedString ? sharedString.ChildElements[ int.Parse(cell.CellValue.InnerText)].InnerText : cell.CellValue.InnerText); foreach (string header in headerValues) { ColumnNames.Add(header); } if (CheckColumnNames(ColumnNames)) { //LINQ query to skip first row with column names. IEnumerable<Row> dataRows = from row in worksheet.Descendants<Row>() where row.RowIndex > 1 select row; if (CheckDataInSheets(dataRows)) { foreach (Row row in dataRows) { //LINQ query to return the row's cell values. //Where clause filters out any cells that do not contain a value. //Select returns the value of a cell unless the cell contains // a Shared String. //If the cell contains a Shared String, its value will be a // reference id which will be used to look up the value in the // Shared String table. IEnumerable<String> textValues = from cell in row.Descendants<Cell>() where cell.CellValue != null select (cell.DataType != null && cell.DataType.HasValue && cell.DataType == CellValues.SharedString ? sharedString.ChildElements[ int.Parse(cell.CellValue.InnerText)].InnerText : cell.CellValue.InnerText); //Check to verify the row contained data. if (textValues.Count() > 0) { if (textValues.Count() == 8) { //Create a ktExaminedGroup and add it to the list. var textArray = textValues.ToArray(); ktExaminedGroup examined = new ktExaminedGroup(); examined.ID = textArray[0]; examined.GroupIdentifier = textArray[1]; examined.GroupType = textArray[2]; examined.GroupExpendable = textArray[3]; examined.Name = textArray[4]; examined.Expanded = textArray[5]; examined.DataQualityScore = textArray[6]; examined.RequiredScore = textArray[7]; Result.Add(examined); } else { var textArray = textValues.ToArray(); ktExaminedGroup examined = new ktExaminedGroup(); examined.ID = textArray[0]; examined.GroupIdentifier = textArray[1]; examined.GroupType = textArray[2]; examined.GroupExpendable = textArray[3]; examined.Name = ""; examined.Expanded = textArray[4]; examined.DataQualityScore = textArray[5]; examined.RequiredScore = textArray[6]; Result.Add(examined); } } else { //If no cells, then you have reached the end of the table. break; } } } else { return false; } return true; } return false; }
private static Dictionary<int, string> CreateSharedStringList(SharedStringTable table) { Dictionary<int, string> sharedStringList = new Dictionary<int, string>(); int itemIndex = 0; foreach (SharedStringItem item in table) { sharedStringList.Add(itemIndex++, item.InnerText); } return sharedStringList; }
// 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; }
/// <summary> /// Helper method for creating a list of ktExaminedGroup /// from an Excel worksheet. /// </summary> public bool LoadUIDesign(Worksheet worksheet, SharedStringTable sharedString) { Result = new List<ktUIDesign>(); //Linq query to get the column headers on the sheet Row columnRow = (from row in worksheet.Descendants<Row>() where row.RowIndex == 1 select row).First(); IEnumerable<String> headerValues = from cell in columnRow.Descendants<Cell>() where cell.CellValue != null select (cell.DataType != null && cell.DataType.HasValue && cell.DataType == CellValues.SharedString ? sharedString.ChildElements[ int.Parse(cell.CellValue.InnerText)].InnerText : cell.CellValue.InnerText); foreach (string header in headerValues) { ColumnNames.Add(header); } if (CheckColumnNames(ColumnNames)) { //LINQ query to skip first row with column names. IEnumerable<Row> dataRows = from row in worksheet.Descendants<Row>() where row.RowIndex > 1 select row; if (CheckDataInSheets(dataRows)) { foreach (Row row in dataRows) { //LINQ query to return the row's cell values. //Where clause filters out any cells that do not contain a value. //Select returns the value of a cell unless the cell contains // a Shared String. //If the cell contains a Shared String, its value will be a // reference id which will be used to look up the value in the // Shared String table. //IEnumerable<String> textValues = // from cell in row.Descendants<Cell>() // where cell.CellValue != null // select // (cell.DataType != null // && cell.DataType.HasValue // && cell.DataType == CellValues.SharedString // ? sharedString.ChildElements[ // int.Parse(cell.CellValue.InnerText)].InnerText // : cell.CellValue.InnerText) // ; IEnumerable<String> textValues = from cell in row.Descendants<Cell>() where cell.CellValue != null select (cell.DataType != null && cell.DataType.HasValue && cell.DataType == CellValues.SharedString ? sharedString.ChildElements[ int.Parse(cell.CellValue.InnerText)].InnerText : cell.CellValue.InnerText) ; //Check to verify the row contained data. if (textValues.Count() > 0) { //Create a ktExaminedGroup and add it to the list. //var textArray = textValues.ToArray(); //ktUIDesign design = new ktUIDesign(); //design.DesignID = Int32.Parse(textArray[0]); //design.DatabaseTableName = textArray[1]; //design.DatabaseFieldName = textArray[2]; //design.CodeTableName = textArray[3]; //design.ResxID = textArray[4]; //design.ReadOnlyPolicy = Int32.Parse(textArray[5]); //design.InputDataType = Int32.Parse(textArray[6]); //design.MortyParameter = Int32.Parse(textArray[7]); //design.RequiredID = Int32.Parse(textArray[8]); //design.GUIUnitShortName = textArray[9]; //design.DatabaseUnitName = textArray[10]; //design.LabkaUnitName = textArray[11]; //design.DatabaseToUIConversion = textArray[12]; //design.DefaultValue = textArray[13]; //design.NormalRangeMinimum = Int32.Parse(textArray[14]); //design.NormalRangeMaximum = Int32.Parse(textArray[15]); //design.CopyEncounter = Int32.Parse(textArray[16]); //design.CopyEpisode = Int32.Parse(textArray[17]); //design.DataQualityScore = Int32.Parse(textArray[18]); //design.CopyFinalEncounter = Int32.Parse(textArray[19]); //result.Add(design); var textArray = textValues.ToArray(); ktUIDesign design = new ktUIDesign(); design.DesignID = textArray[0]; design.DatabaseTableName = textArray[1]; design.DatabaseFieldName = textArray[2]; design.CodeTableName = textArray[3]; design.ResxID = textArray[4]; design.ReadOnlyPolicy = textArray[5]; design.InputDataType = textArray[6]; design.MortyParameter = textArray[7]; design.RequiredID = textArray[8]; design.GUIUnitShortName = textArray[9]; design.DatabaseUnitName = textArray[10]; design.LabkaUnitName = textArray[11]; design.DatabaseToUIConversion = textArray[12]; design.DefaultValue = textArray[13]; design.NormalRangeMinimum = textArray[14]; design.NormalRangeMaximum = textArray[15]; design.RangeMinimum = textArray[16]; design.RangeMaximum = textArray[17]; design.CopyEncounter = textArray[18]; design.CopyEpisode = textArray[19]; design.DataQualityScore = textArray[20]; design.CopyFinalEncounter = textArray[21]; Result.Add(design); } else { //If no cells, then you have reached the end of the table. break; } } } else { return false; } return true; } return false; }
// Generates content of sharedStringTablePart1. private void GenerateSharedStringTablePart1Content(SharedStringTablePart sharedStringTablePart1) { SharedStringTable sharedStringTable1 = new SharedStringTable() { Count = (UInt32Value)5U, UniqueCount = (UInt32Value)5U }; SharedStringItem sharedStringItem1 = new SharedStringItem(); Text text1 = new Text(); text1.Text = "id"; SharedStringItem sharedStringItem2 = new SharedStringItem(); Text text2 = new Text(); text2.Text = "score"; SharedStringItem sharedStringItem3 = new SharedStringItem(); Text text3 = new Text(); text3.Text = "Sum of score"; SharedStringItem sharedStringItem4 = new SharedStringItem(); Text text4 = new Text(); text4.Text = "Row Labels"; SharedStringItem sharedStringItem5 = new SharedStringItem(); Text text5 = new Text(); text5.Text = "Grand Total"; sharedStringItem1.Append(text1); sharedStringItem2.Append(text2); sharedStringItem3.Append(text3); sharedStringItem4.Append(text4); sharedStringItem5.Append(text5); sharedStringTable1.Append(sharedStringItem1); sharedStringTable1.Append(sharedStringItem2); sharedStringTable1.Append(sharedStringItem3); sharedStringTable1.Append(sharedStringItem4); sharedStringTable1.Append(sharedStringItem5); sharedStringTablePart1.SharedStringTable = sharedStringTable1; }
/// <summary> /// Imports all data from the original Excel file /// </summary> public void ImportExcelFromFile(string path) { _workSheetktExaminedGroup = WorkSheetktExaminedGroup.Instance; _workSheetUIDesign = WorkSheetktUIDesign.Instance; _workSheetktUIFieldIncludedType = WorkSheetktUIFieldIncludedType.Instance; _workSheetktUIGroupOrder = WorkSheetktUIGroupOrder.Instance; _workSheetktUIOrder = WorkSheetktUIOrder.Instance; _workSheetktResources = WorkSheetktResources.Instance; _workSheetktResourceTranslation = WorkSheetktResourceTranslation.Instance; _workSheetktResourceType = WorkSheetktResourceType.Instance; _workSheetktUIPageType = WorkSheetUIPageType.Instance; _workSheetQAGroups = WorkSheetQAGroup.Instance; _workSheetQAktUIDesign = WorkSheetQAktUIDesign.Instance; //Check that the file is an excel file if (CheckFileType(path)) { if (CheckSheetNames(path)) { //Open the Excel workbook. using (SpreadsheetDocument document = SpreadsheetDocument.Open(path, true)) { //References to the workbook and Shared String Table. _workBook = document.WorkbookPart.Workbook; _workSheets = _workBook.Descendants<Sheet>(); _sharedStrings = document.WorkbookPart.SharedStringTablePart.SharedStringTable; //Reference to Excel Worksheet with ktExaminedGroup data. _ktExaminedID = _workSheets.First(s => s.Name == this._workSheetktExaminedGroup.SheetName).Id; _ktExaminedGroupSheet = (WorksheetPart)document.WorkbookPart.GetPartById(_ktExaminedID); //Reference to Excel Worksheet with ktUIDesign data. _ktUiDesignID = _workSheets.First(s => s.Name == this._workSheetUIDesign.SheetName).Id; _ktUiDesignSheet = (WorksheetPart)document.WorkbookPart.GetPartById(_ktUiDesignID); //Reference to Excel Worksheet with ktUIFieldIncludedTypeSheet data. _ktUiFieldID = _workSheets.First(s => s.Name == this._workSheetktUIFieldIncludedType.SheetName).Id; _ktUiFieldIncludedTypeSheet = (WorksheetPart)document.WorkbookPart.GetPartById(_ktUiFieldID); //Load ktUIFieldIncludedType data to business object. this._workSheetktUIFieldIncludedType.LoadUIFieldIncludedType(_ktUiFieldIncludedTypeSheet.Worksheet, _sharedStrings); //Reference to Excel Worksheet with ktUIFieldIncludedTypeSheet data. _ktUiGroupOrderID = _workSheets.First(s => s.Name == this._workSheetktUIGroupOrder.SheetName).Id; _ktUiGroupOrderSheet = (WorksheetPart)document.WorkbookPart.GetPartById(_ktUiGroupOrderID); //Reference to Excel Worksheet with ktUIFieldIncludedTypeSheet data. _ktUiOrderID = _workSheets.First(s => s.Name == this._workSheetktUIOrder.SheetName).Id; _ktUiOrderSheet = (WorksheetPart)document.WorkbookPart.GetPartById(_ktUiOrderID); //Load ktUIFieldIncludedType data to business object. //this._workSheetktUIOrder.LoadUIOrder(_ktUiOrderSheet.Worksheet, _sharedStrings); //Reference to Excel Worksheet with ktResource data. _ktResourcesID = _workSheets.First(s => s.Name == this._workSheetktResources.SheetName).Id; _ktResourcesSheet = (WorksheetPart)document.WorkbookPart.GetPartById(_ktResourcesID); ////Load ktResource data to business object. this._workSheetktResources.LoadktResources(_ktResourcesSheet.Worksheet, _sharedStrings); ////Reference to Excel Worksheet with ktResourceTranslation data. _ktResourceTranslationID = _workSheets.First(s => s.Name == this._workSheetktResourceTranslation.SheetName).Id; _ktResourceTranslationSheet = (WorksheetPart)document.WorkbookPart.GetPartById(_ktResourceTranslationID); ////Load ktResouceTranslation data to business object. this._workSheetktResourceTranslation.LoadktResourceTranslation(_ktResourceTranslationSheet.Worksheet, _sharedStrings); ////Reference to Excel Worksheet with ktResource data. _ktResourceTypeID = _workSheets.First(s => s.Name == this._workSheetktResourceType.SheetName).Id; _ktResourceTypeSheet = (WorksheetPart)document.WorkbookPart.GetPartById(_ktResourceTypeID); ////Load ktResource data to business object. this._workSheetktResourceType.LoadktResourceType(_ktResourceTypeSheet.Worksheet, _sharedStrings); ////Reference to Excel Worksheet with ktUIPageType data. _ktUiPageTypeID = _workSheets.First(s => s.Name == this._workSheetktUIPageType.SheetName).Id; _ktUiPageTypeSheet = (WorksheetPart)document.WorkbookPart.GetPartById(_ktUiPageTypeID); ////Load ktResource data to business object. this._workSheetktUIPageType.LoadUIPageType(_ktUiPageTypeSheet.Worksheet, _sharedStrings); ////Reference to Excel Worksheet with QAGroups data. _qaGroupsID = _workSheets.First(s => s.Name == this._workSheetQAGroups.SheetName).Id; _qaGroupsSheet = (WorksheetPart)document.WorkbookPart.GetPartById(_qaGroupsID); ////Load QAGroups data to business object. this._workSheetQAGroups.LoadQAGroups(_qaGroupsSheet.Worksheet, _sharedStrings); ////Reference to Excel Worksheet with QAGroups data. _qAktUiDesignID = _workSheets.First(s => s.Name == this._workSheetQAktUIDesign.SheetName).Id; _qAktUiDesignSheet = (WorksheetPart)document.WorkbookPart.GetPartById(_qAktUiDesignID); ////Load QAGroups data to business object. this._workSheetQAktUIDesign.LoadQAktUIDesign(_qAktUiDesignSheet.Worksheet, _sharedStrings); if (this._workSheetktExaminedGroup.LoadExaminedGroup(_ktExaminedGroupSheet.Worksheet, _sharedStrings) && this._workSheetktUIGroupOrder.LoadUIGroupOrder(_ktUiGroupOrderSheet.Worksheet, _sharedStrings) && this._workSheetUIDesign.LoadUIDesign(_ktUiDesignSheet.Worksheet, _sharedStrings) && this._workSheetktResources.LoadktResources(_ktResourcesSheet.Worksheet, _sharedStrings) && this._workSheetktResourceTranslation.LoadktResourceTranslation(_ktResourceTranslationSheet.Worksheet, _sharedStrings) && this._workSheetktUIOrder.LoadUIOrder(_ktUiOrderSheet.Worksheet, _sharedStrings) // +++ ) { _workSheetktExaminedGroup.AcceptChanges(); _workSheetUIDesign.AcceptChanges(); _workSheetktUIGroupOrder.AcceptChanges(); _workSheetktResources.AcceptChanges(); _workSheetktResourceTranslation.AcceptChanges(); _workSheetktUIOrder.AcceptChanges(); } } } } if (!FileTypeOk) { ImportFileOK = false; MessageBox.Show("This file is not an excel file with the correct (.xlsx) file extension", "Wrong file type", MessageBoxButton.OK, MessageBoxImage.Error); } if (!SheetNameOk) { ImportFileOK = false; MessageBox.Show("The configuration excel file does not contain the correct excel sheets", "Wrong excel file", MessageBoxButton.OK, MessageBoxImage.Error); } if (!_workSheetktExaminedGroup.ColumnHeadersOk || !_workSheetktUIGroupOrder.ColumnHeadersOk || !_workSheetUIDesign.ColumnHeadersOk || !_workSheetktResources.ColumnHeadersOk || !_workSheetktResourceTranslation.ColumnHeadersOk || !_workSheetktUIOrder.ColumnHeadersOk // +++ ) { ImportFileOK = false; MessageBox.Show("One or all of the excel sheet does not have the right column headers", "Wrong column names", MessageBoxButton.OK, MessageBoxImage.Error); } if (!_workSheetktExaminedGroup.DataOnSheetOk || !_workSheetktUIGroupOrder.DataOnSheetOk || !_workSheetUIDesign.DataOnSheetOk || !_workSheetktResources.ColumnHeadersOk || !_workSheetktResourceTranslation.ColumnHeadersOk || !_workSheetktUIOrder.ColumnHeadersOk // +++ ) { ImportFileOK = false; MessageBox.Show("One or all of the excel sheets does not have any data", "No data on sheet", MessageBoxButton.OK, MessageBoxImage.Error); } }
// Generates content of sharedStringTablePart1. private void GenerateSharedStringTablePart1Content(SharedStringTablePart sharedStringTablePart1) { SharedStringTable sharedStringTable1 = new SharedStringTable() { Count = (UInt32Value)78U, UniqueCount = (UInt32Value)44U }; SharedStringItem sharedStringItem1 = new SharedStringItem(); Text text1 = new Text(); text1.Text = "CLIENTE:"; sharedStringItem1.Append(text1); SharedStringItem sharedStringItem2 = new SharedStringItem(); Text text2 = new Text(); text2.Text = "FECHA DE EMISION:"; sharedStringItem2.Append(text2); SharedStringItem sharedStringItem3 = new SharedStringItem(); Text text3 = new Text(); text3.Text = "Av. Corrientes 6277 ( 1427) Buenos Aires"; sharedStringItem3.Append(text3); SharedStringItem sharedStringItem4 = new SharedStringItem(); Text text4 = new Text(); text4.Text = "ORDEN:"; sharedStringItem4.Append(text4); SharedStringItem sharedStringItem5 = new SharedStringItem(); Text text5 = new Text() { Space = SpaceProcessingModeValues.Preserve }; text5.Text = " Argentina - Tel.: 4323-9931"; sharedStringItem5.Append(text5); SharedStringItem sharedStringItem6 = new SharedStringItem(); Text text6 = new Text(); text6.Text = "MEDIO"; sharedStringItem6.Append(text6); SharedStringItem sharedStringItem7 = new SharedStringItem(); Text text7 = new Text(); text7.Text = "PROGRAMA:"; sharedStringItem7.Append(text7); SharedStringItem sharedStringItem8 = new SharedStringItem(); Text text8 = new Text(); text8.Text = "COORDINADOR GRAL. DE PRODUCCION:"; sharedStringItem8.Append(text8); SharedStringItem sharedStringItem9 = new SharedStringItem(); Text text9 = new Text(); text9.Text = "CONTACTO:"; sharedStringItem9.Append(text9); SharedStringItem sharedStringItem10 = new SharedStringItem(); Text text10 = new Text(); text10.Text = "TELEFONO/ Fax:"; sharedStringItem10.Append(text10); SharedStringItem sharedStringItem11 = new SharedStringItem(); Text text11 = new Text(); text11.Text = "DIRECCION:"; sharedStringItem11.Append(text11); SharedStringItem sharedStringItem12 = new SharedStringItem(); Text text12 = new Text(); text12.Text = "E-MAIL:"; sharedStringItem12.Append(text12); SharedStringItem sharedStringItem13 = new SharedStringItem(); Text text13 = new Text(); text13.Text = "PROGRAMAS"; sharedStringItem13.Append(text13); SharedStringItem sharedStringItem14 = new SharedStringItem(); Text text14 = new Text(); text14.Text = "PRODUCTOS"; sharedStringItem14.Append(text14); SharedStringItem sharedStringItem15 = new SharedStringItem(); Text text15 = new Text(); text15.Text = "EMPRESA"; sharedStringItem15.Append(text15); SharedStringItem sharedStringItem16 = new SharedStringItem(); Text text16 = new Text() { Space = SpaceProcessingModeValues.Preserve }; text16.Text = "Tipo de "; sharedStringItem16.Append(text16); SharedStringItem sharedStringItem17 = new SharedStringItem(); Text text17 = new Text(); text17.Text = "SEG."; sharedStringItem17.Append(text17); SharedStringItem sharedStringItem18 = new SharedStringItem(); Text text18 = new Text(); text18.Text = "S"; sharedStringItem18.Append(text18); SharedStringItem sharedStringItem19 = new SharedStringItem(); Text text19 = new Text(); text19.Text = "D"; sharedStringItem19.Append(text19); SharedStringItem sharedStringItem20 = new SharedStringItem(); Text text20 = new Text(); text20.Text = "L"; sharedStringItem20.Append(text20); SharedStringItem sharedStringItem21 = new SharedStringItem(); Text text21 = new Text(); text21.Text = "M"; sharedStringItem21.Append(text21); SharedStringItem sharedStringItem22 = new SharedStringItem(); Text text22 = new Text(); text22.Text = "J"; sharedStringItem22.Append(text22); SharedStringItem sharedStringItem23 = new SharedStringItem(); Text text23 = new Text(); text23.Text = "V"; sharedStringItem23.Append(text23); SharedStringItem sharedStringItem24 = new SharedStringItem(); Text text24 = new Text(); text24.Text = "Cantidad"; sharedStringItem24.Append(text24); SharedStringItem sharedStringItem25 = new SharedStringItem(); Text text25 = new Text() { Space = SpaceProcessingModeValues.Preserve }; text25.Text = "Costo Neto "; sharedStringItem25.Append(text25); SharedStringItem sharedStringItem26 = new SharedStringItem(); Text text26 = new Text(); text26.Text = "Total"; sharedStringItem26.Append(text26); SharedStringItem sharedStringItem27 = new SharedStringItem(); Text text27 = new Text(); text27.Text = "Salida"; sharedStringItem27.Append(text27); SharedStringItem sharedStringItem28 = new SharedStringItem(); Text text28 = new Text(); text28.Text = "Salidas"; sharedStringItem28.Append(text28); SharedStringItem sharedStringItem29 = new SharedStringItem(); Text text29 = new Text(); text29.Text = "x salida PNT"; sharedStringItem29.Append(text29); SharedStringItem sharedStringItem30 = new SharedStringItem(); Text text30 = new Text(); text30.Text = "Neto"; sharedStringItem30.Append(text30); SharedStringItem sharedStringItem31 = new SharedStringItem(); Text text31 = new Text(); text31.Text = "SPRAYETTE"; sharedStringItem31.Append(text31); SharedStringItem sharedStringItem32 = new SharedStringItem(); Text text32 = new Text(); text32.Text = "PNT"; sharedStringItem32.Append(text32); SharedStringItem sharedStringItem33 = new SharedStringItem(); Text text33 = new Text(); text33.Text = "NUMERO"; sharedStringItem33.Append(text33); SharedStringItem sharedStringItem34 = new SharedStringItem(); Text text34 = new Text(); text34.Text = "PNT\'S TOTALES"; sharedStringItem34.Append(text34); SharedStringItem sharedStringItem35 = new SharedStringItem(); Text text35 = new Text(); text35.Text = "COSTO POR SALIDA"; sharedStringItem35.Append(text35); SharedStringItem sharedStringItem36 = new SharedStringItem(); Text text36 = new Text(); text36.Text = "EXCLUSIVE"; sharedStringItem36.Append(text36); SharedStringItem sharedStringItem37 = new SharedStringItem(); Text text37 = new Text(); text37.Text = "SUBTOTAL"; sharedStringItem37.Append(text37); SharedStringItem sharedStringItem38 = new SharedStringItem(); Text text38 = new Text(); text38.Text = "POLISHOP"; sharedStringItem38.Append(text38); SharedStringItem sharedStringItem39 = new SharedStringItem(); Text text39 = new Text(); text39.Text = "IVA 21%:"; sharedStringItem39.Append(text39); SharedStringItem sharedStringItem40 = new SharedStringItem(); Text text40 = new Text(); text40.Text = "TOTAL"; sharedStringItem40.Append(text40); SharedStringItem sharedStringItem41 = new SharedStringItem(); Text text41 = new Text(); text41.Text = "PRODUCTO"; sharedStringItem41.Append(text41); SharedStringItem sharedStringItem42 = new SharedStringItem(); Text text42 = new Text(); text42.Text = "ZOCALO"; sharedStringItem42.Append(text42); SharedStringItem sharedStringItem43 = new SharedStringItem(); Text text43 = new Text(); text43.Text = "COD. INGESTA"; sharedStringItem43.Append(text43); SharedStringItem sharedStringItem44 = new SharedStringItem(); Text text44 = new Text(); text44.Text = "SALIDAS"; sharedStringItem44.Append(text44); sharedStringTable1.Append(sharedStringItem1); sharedStringTable1.Append(sharedStringItem2); sharedStringTable1.Append(sharedStringItem3); sharedStringTable1.Append(sharedStringItem4); sharedStringTable1.Append(sharedStringItem5); sharedStringTable1.Append(sharedStringItem6); sharedStringTable1.Append(sharedStringItem7); sharedStringTable1.Append(sharedStringItem8); sharedStringTable1.Append(sharedStringItem9); sharedStringTable1.Append(sharedStringItem10); sharedStringTable1.Append(sharedStringItem11); sharedStringTable1.Append(sharedStringItem12); sharedStringTable1.Append(sharedStringItem13); sharedStringTable1.Append(sharedStringItem14); sharedStringTable1.Append(sharedStringItem15); sharedStringTable1.Append(sharedStringItem16); sharedStringTable1.Append(sharedStringItem17); sharedStringTable1.Append(sharedStringItem18); sharedStringTable1.Append(sharedStringItem19); sharedStringTable1.Append(sharedStringItem20); sharedStringTable1.Append(sharedStringItem21); sharedStringTable1.Append(sharedStringItem22); sharedStringTable1.Append(sharedStringItem23); sharedStringTable1.Append(sharedStringItem24); sharedStringTable1.Append(sharedStringItem25); sharedStringTable1.Append(sharedStringItem26); sharedStringTable1.Append(sharedStringItem27); sharedStringTable1.Append(sharedStringItem28); sharedStringTable1.Append(sharedStringItem29); sharedStringTable1.Append(sharedStringItem30); sharedStringTable1.Append(sharedStringItem31); sharedStringTable1.Append(sharedStringItem32); sharedStringTable1.Append(sharedStringItem33); sharedStringTable1.Append(sharedStringItem34); sharedStringTable1.Append(sharedStringItem35); sharedStringTable1.Append(sharedStringItem36); sharedStringTable1.Append(sharedStringItem37); sharedStringTable1.Append(sharedStringItem38); sharedStringTable1.Append(sharedStringItem39); sharedStringTable1.Append(sharedStringItem40); sharedStringTable1.Append(sharedStringItem41); sharedStringTable1.Append(sharedStringItem42); sharedStringTable1.Append(sharedStringItem43); sharedStringTable1.Append(sharedStringItem44); sharedStringTablePart1.SharedStringTable = sharedStringTable1; }
// Generates content of sharedStringTablePart1. private void GenerateSharedStringTablePart1Content(SharedStringTablePart sharedStringTablePart1) { SharedStringTable sharedStringTable1 = new SharedStringTable() { Count = (UInt32Value)20U, UniqueCount = (UInt32Value)19U }; SharedStringItem sharedStringItem1 = new SharedStringItem(); Text text1 = new Text(); text1.Text = "Totales"; sharedStringItem1.Append(text1); SharedStringItem sharedStringItem2 = new SharedStringItem(); Text text2 = new Text(); text2.Text = "Av. Corrientes 6277 ( 1427) Buenos Aires"; sharedStringItem2.Append(text2); SharedStringItem sharedStringItem3 = new SharedStringItem(); Text text3 = new Text() { Space = SpaceProcessingModeValues.Preserve }; text3.Text = " Argentina - Tel.: 4323-9931"; sharedStringItem3.Append(text3); SharedStringItem sharedStringItem4 = new SharedStringItem(); Text text4 = new Text() { Space = SpaceProcessingModeValues.Preserve }; text4.Text = "Medio: "; sharedStringItem4.Append(text4); SharedStringItem sharedStringItem5 = new SharedStringItem(); Text text5 = new Text(); text5.Text = "Costo total"; sharedStringItem5.Append(text5); SharedStringItem sharedStringItem6 = new SharedStringItem(); Text text6 = new Text(); text6.Text = "PRODUCTO:"; sharedStringItem6.Append(text6); SharedStringItem sharedStringItem7 = new SharedStringItem(); Text text7 = new Text(); text7.Text = "HORARIO"; sharedStringItem7.Append(text7); SharedStringItem sharedStringItem8 = new SharedStringItem(); Text text8 = new Text(); text8.Text = "Cantidad"; sharedStringItem8.Append(text8); SharedStringItem sharedStringItem9 = new SharedStringItem(); Text text9 = new Text(); text9.Text = "Duración"; sharedStringItem9.Append(text9); SharedStringItem sharedStringItem10 = new SharedStringItem(); Text text10 = new Text(); text10.Text = "Costo"; sharedStringItem10.Append(text10); SharedStringItem sharedStringItem11 = new SharedStringItem(); Text text11 = new Text(); text11.Text = "Salidas"; sharedStringItem11.Append(text11); SharedStringItem sharedStringItem12 = new SharedStringItem(); Text text12 = new Text(); text12.Text = "Total"; sharedStringItem12.Append(text12); SharedStringItem sharedStringItem13 = new SharedStringItem(); Text text13 = new Text(); text13.Text = "Orden de Publicidad:"; sharedStringItem13.Append(text13); SharedStringItem sharedStringItem14 = new SharedStringItem(); Text text14 = new Text(); text14.Text = "Programa:"; sharedStringItem14.Append(text14); SharedStringItem sharedStringItem15 = new SharedStringItem(); Text text15 = new Text(); text15.Text = "Fecha de Emisión:"; sharedStringItem15.Append(text15); SharedStringItem sharedStringItem16 = new SharedStringItem(); Text text16 = new Text(); text16.Text = "Contacto:"; sharedStringItem16.Append(text16); SharedStringItem sharedStringItem17 = new SharedStringItem(); Text text17 = new Text(); text17.Text = "Tel/Fax:"; sharedStringItem17.Append(text17); SharedStringItem sharedStringItem18 = new SharedStringItem(); Text text18 = new Text(); text18.Text = "Dirección:"; sharedStringItem18.Append(text18); SharedStringItem sharedStringItem19 = new SharedStringItem(); Text text19 = new Text(); text19.Text = "Email:"; sharedStringItem19.Append(text19); sharedStringTable1.Append(sharedStringItem1); sharedStringTable1.Append(sharedStringItem2); sharedStringTable1.Append(sharedStringItem3); sharedStringTable1.Append(sharedStringItem4); sharedStringTable1.Append(sharedStringItem5); sharedStringTable1.Append(sharedStringItem6); sharedStringTable1.Append(sharedStringItem7); sharedStringTable1.Append(sharedStringItem8); sharedStringTable1.Append(sharedStringItem9); sharedStringTable1.Append(sharedStringItem10); sharedStringTable1.Append(sharedStringItem11); sharedStringTable1.Append(sharedStringItem12); sharedStringTable1.Append(sharedStringItem13); sharedStringTable1.Append(sharedStringItem14); sharedStringTable1.Append(sharedStringItem15); sharedStringTable1.Append(sharedStringItem16); sharedStringTable1.Append(sharedStringItem17); sharedStringTable1.Append(sharedStringItem18); sharedStringTable1.Append(sharedStringItem19); sharedStringTablePart1.SharedStringTable = sharedStringTable1; }
// Generates content of sharedStringTablePart1. protected override void GenerateSharedStringTablePartContent(SharedStringTablePart sharedStringTablePart) { SharedStringTable sharedStringTable1 = new SharedStringTable() { Count = (UInt32Value)29U, UniqueCount = (UInt32Value)28U }; SharedStringItem sharedStringItem1 = new SharedStringItem(); Text text1 = new Text(); text1.Text = "АКТ №" + sampleSelectionRequest.ActNumber; sharedStringItem1.Append(text1); SharedStringItem sharedStringItem2 = new SharedStringItem(); Text text2 = new Text(); text2.Text = string.Format("Выполненных работ между {0} и {1}", sampleSelectionRequest.Executor.Name, sampleSelectionRequest.Client.Name); sharedStringItem2.Append(text2); SharedStringItem sharedStringItem3 = new SharedStringItem(); Text text3 = new Text(); text3.Text = string.Format("Срок выполнения работ: _______{0}г", DateTime.Now.Year); sharedStringItem3.Append(text3); SharedStringItem sharedStringItem4 = new SharedStringItem(); Text text4 = new Text(); text4.Text = "Наименование выполненных работ (оказанных услуг)"; sharedStringItem4.Append(text4); SharedStringItem sharedStringItem5 = new SharedStringItem(); Text text5 = new Text(); text5.Text = "Стоимость работ (услуг) всего без НДС, руб"; sharedStringItem5.Append(text5); SharedStringItem sharedStringItem6 = new SharedStringItem(); Text text6 = new Text(); text6.Text = "Ставка НДС, %"; sharedStringItem6.Append(text6); SharedStringItem sharedStringItem7 = new SharedStringItem(); Text text7 = new Text(); text7.Text = "Сумма НДС, руб"; sharedStringItem7.Append(text7); SharedStringItem sharedStringItem8 = new SharedStringItem(); Text text8 = new Text(); text8.Text = "Стоимость работ всего с учётом НДС, руб"; sharedStringItem8.Append(text8); SharedStringItem sharedStringItem9 = new SharedStringItem(); Text text9 = new Text(); text9.Text = "Полный анализ семян"; sharedStringItem9.Append(text9); SharedStringItem sharedStringItem10 = new SharedStringItem(); Text text10 = new Text(); text10.Text = "Анализ семян на чистоту"; sharedStringItem10.Append(text10); SharedStringItem sharedStringItem11 = new SharedStringItem(); Text text11 = new Text(); text11.Text = "Анализ семян на влажность"; sharedStringItem11.Append(text11); SharedStringItem sharedStringItem12 = new SharedStringItem(); Text text12 = new Text(); text12.Text = "Анализ семян на заселенность вредителями"; sharedStringItem12.Append(text12); SharedStringItem sharedStringItem13 = new SharedStringItem(); Text text13 = new Text(); text13.Text = "Анализ семян на всхожесть"; sharedStringItem13.Append(text13); SharedStringItem sharedStringItem14 = new SharedStringItem(); Text text14 = new Text(); text14.Text = "Анализ семян на определение массы 1000 семян"; sharedStringItem14.Append(text14); SharedStringItem sharedStringItem15 = new SharedStringItem(); Text text15 = new Text(); text15.Text = "Анализ семян на жизнеспособность"; sharedStringItem15.Append(text15); SharedStringItem sharedStringItem16 = new SharedStringItem(); Text text16 = new Text(); text16.Text = "Анализ (для картофеля, лука и т.д.)"; sharedStringItem16.Append(text16); SharedStringItem sharedStringItem17 = new SharedStringItem(); Text text17 = new Text(); text17.Text = "Фитоанализ"; sharedStringItem17.Append(text17); SharedStringItem sharedStringItem18 = new SharedStringItem(); Text text18 = new Text(); text18.Text = "Определение алкалоидности семян люпина"; sharedStringItem18.Append(text18); SharedStringItem sharedStringItem19 = new SharedStringItem(); Text text19 = new Text(); text19.Text = "Итого:"; sharedStringItem19.Append(text19); SharedStringItem sharedStringItem20 = new SharedStringItem(); Text text20 = new Text(); text20.Text = "Сумма НДС:"; sharedStringItem20.Append(text20); SharedStringItem sharedStringItem21 = new SharedStringItem(); Text text21 = new Text(); text21.Text = "Всего с учётом НДС:"; sharedStringItem21.Append(text21); SharedStringItem sharedStringItem22 = new SharedStringItem(); Text text22 = new Text(); text22.Text = "Исполнитель:"; sharedStringItem22.Append(text22); SharedStringItem sharedStringItem23 = new SharedStringItem(); Text text23 = new Text(); text23.Text = "Заказчик:"; sharedStringItem23.Append(text23); SharedStringItem sharedStringItem24 = new SharedStringItem(); Text text24 = new Text(); text24.Text = sampleSelectionRequest.Executor.Name; sharedStringItem24.Append(text24); SharedStringItem sharedStringItem25 = new SharedStringItem(); Text text25 = new Text(); text25.Text = sampleSelectionRequest.Client.Name; sharedStringItem25.Append(text25); SharedStringItem sharedStringItem26 = new SharedStringItem(); Text text26 = new Text(); text26.Text = sampleSelectionRequest.Executor.AccountNumber; sharedStringItem26.Append(text26); SharedStringItem sharedStringItem27 = new SharedStringItem(); Text text27 = new Text(); text27.Text = sampleSelectionRequest.Client.AccountNumber; sharedStringItem27.Append(text27); SharedStringItem sharedStringItem28 = new SharedStringItem(); Text text28 = new Text(); text28.Text = "подпись"; sharedStringItem28.Append(text28); sharedStringTable1.Append(sharedStringItem1); sharedStringTable1.Append(sharedStringItem2); sharedStringTable1.Append(sharedStringItem3); sharedStringTable1.Append(sharedStringItem4); sharedStringTable1.Append(sharedStringItem5); sharedStringTable1.Append(sharedStringItem6); sharedStringTable1.Append(sharedStringItem7); sharedStringTable1.Append(sharedStringItem8); sharedStringTable1.Append(sharedStringItem9); sharedStringTable1.Append(sharedStringItem10); sharedStringTable1.Append(sharedStringItem11); sharedStringTable1.Append(sharedStringItem12); sharedStringTable1.Append(sharedStringItem13); sharedStringTable1.Append(sharedStringItem14); sharedStringTable1.Append(sharedStringItem15); sharedStringTable1.Append(sharedStringItem16); sharedStringTable1.Append(sharedStringItem17); sharedStringTable1.Append(sharedStringItem18); sharedStringTable1.Append(sharedStringItem19); sharedStringTable1.Append(sharedStringItem20); sharedStringTable1.Append(sharedStringItem21); sharedStringTable1.Append(sharedStringItem22); sharedStringTable1.Append(sharedStringItem23); sharedStringTable1.Append(sharedStringItem24); sharedStringTable1.Append(sharedStringItem25); sharedStringTable1.Append(sharedStringItem26); sharedStringTable1.Append(sharedStringItem27); sharedStringTable1.Append(sharedStringItem28); sharedStringTablePart.SharedStringTable = sharedStringTable1; }
/// <summary> /// Helper method for creating a list of ktExaminedGroup /// from an Excel worksheet. /// </summary> public bool LoadUIOrder(Worksheet worksheet, SharedStringTable sharedString) { Result = new List<ktUIOrder>(); //Linq query to get the column headers on the sheet Row columnRow = (from row in worksheet.Descendants<Row>() where row.RowIndex == 1 select row).First(); IEnumerable<String> headerValues = from cell in columnRow.Descendants<Cell>() where cell.CellValue != null select (cell.DataType != null && cell.DataType.HasValue && cell.DataType == CellValues.SharedString ? sharedString.ChildElements[ int.Parse(cell.CellValue.InnerText)].InnerText : cell.CellValue.InnerText); foreach (string header in headerValues) { ColumnNames.Add(header); } if (CheckColumnNames(ColumnNames)) { //LINQ query to skip first row with column names. IEnumerable<Row> dataRows = from row in worksheet.Descendants<Row>() where row.RowIndex > 1 select row; if (CheckDataInSheets(dataRows)) { foreach (Row row in dataRows) { //LINQ query to return the row's cell values. //Where clause filters out any cells that do not contain a value. //Select returns the value of a cell unless the cell contains // a Shared String. //If the cell contains a Shared String, its value will be a // reference id which will be used to look up the value in the // Shared String table. //IEnumerable<String> textValues = // from cell in row.Descendants<Cell>() // where cell.CellValue != null // select // (cell.DataType != null // && cell.DataType.HasValue // && cell.DataType == CellValues.SharedString // ? sharedString.ChildElements[ // int.Parse(cell.CellValue.InnerText)].InnerText // : cell.CellValue.InnerText) // ; IEnumerable<String> textValues = from cell in row.Descendants<Cell>() where cell.CellValue != null select (cell.DataType != null && cell.DataType.HasValue && cell.DataType == CellValues.SharedString ? sharedString.ChildElements[ int.Parse(cell.CellValue.InnerText)].InnerText : cell.CellValue.InnerText) ; //Check to verify the row contained data. if (textValues.Count() > 0) { if (textValues.Count() == 5) { //Create a ktUIOrder and add it to the list. var textArray = textValues.ToArray(); ktUIOrder order = new ktUIOrder(); order.DesignID = textArray[0]; order.GroupOrder = double.Parse(textArray[1], CultureInfo.InvariantCulture); // Convert.ToDouble(textArray[1]); order.GroupTypeID = textArray[2]; order.PageTypeID = textArray[3]; order.IncludedTypeID = textArray[4]; Result.Add(order); } else { var textArray = textValues.ToArray(); ktUIOrder order = new ktUIOrder(); order.DesignID = textArray[0]; order.GroupOrder = double.Parse(textArray[1], CultureInfo.InvariantCulture); // Convert.ToDouble(textArray[1]); order.GroupTypeID = textArray[2]; order.PageTypeID = ""; order.IncludedTypeID = textArray[4]; Result.Add(order); } } else { //If no cells, then you have reached the end of the table. break; } } } else { return false; } return true; } return false; }
private static string GetStringValue(SharedStringTable stringTable, Cell c, int id) { return stringTable.ElementAt(Convert.ToInt32(c.CellValue.Text)).FirstOrDefault().InnerText; }
// Generates content of sharedStringTablePart1. private void GenerateSharedStringTablePart1Content(SharedStringTablePart sharedStringTablePart1) { SharedStringTable sharedStringTable1 = new SharedStringTable() { Count = (UInt32Value)18U, UniqueCount = (UInt32Value)17U }; SharedStringItem sharedStringItem1 = new SharedStringItem(); Text text1 = new Text(); text1.Text = "Tel. / Fax.:"; sharedStringItem1.Append(text1); SharedStringItem sharedStringItem2 = new SharedStringItem(); Text text2 = new Text() { Space = SpaceProcessingModeValues.Preserve }; text2.Text = "Att.: "; sharedStringItem2.Append(text2); SharedStringItem sharedStringItem3 = new SharedStringItem(); Text text3 = new Text(); text3.Text = "Fecha:"; sharedStringItem3.Append(text3); SharedStringItem sharedStringItem4 = new SharedStringItem(); Text text4 = new Text(); text4.Text = "Medio:"; sharedStringItem4.Append(text4); SharedStringItem sharedStringItem5 = new SharedStringItem(); Text text5 = new Text(); text5.Text = "EDITORIAL"; sharedStringItem5.Append(text5); SharedStringItem sharedStringItem6 = new SharedStringItem(); Text text6 = new Text(); text6.Text = "REVISTA"; sharedStringItem6.Append(text6); SharedStringItem sharedStringItem7 = new SharedStringItem(); Text text7 = new Text(); text7.Text = "MEDIDA"; sharedStringItem7.Append(text7); SharedStringItem sharedStringItem8 = new SharedStringItem(); Text text8 = new Text(); text8.Text = "PRODUCTO"; sharedStringItem8.Append(text8); SharedStringItem sharedStringItem9 = new SharedStringItem(); Text text9 = new Text(); text9.Text = "Argentina - Tel.: 4323-9931"; sharedStringItem9.Append(text9); SharedStringItem sharedStringItem10 = new SharedStringItem(); Text text10 = new Text(); text10.Text = "Av. Corrientes 6277"; sharedStringItem10.Append(text10); SharedStringItem sharedStringItem11 = new SharedStringItem(); Text text11 = new Text() { Space = SpaceProcessingModeValues.Preserve }; text11.Text = "(C1427BPA) Buenos Aires "; sharedStringItem11.Append(text11); SharedStringItem sharedStringItem12 = new SharedStringItem(); Text text12 = new Text(); text12.Text = "ORDEN DE PUBLICIDAD"; sharedStringItem12.Append(text12); SharedStringItem sharedStringItem13 = new SharedStringItem(); Text text13 = new Text(); text13.Text = "Subtotal:"; sharedStringItem13.Append(text13); SharedStringItem sharedStringItem14 = new SharedStringItem(); Text text14 = new Text(); text14.Text = "iva 21%:"; sharedStringItem14.Append(text14); SharedStringItem sharedStringItem15 = new SharedStringItem(); Text text15 = new Text(); text15.Text = "Total"; sharedStringItem15.Append(text15); SharedStringItem sharedStringItem16 = new SharedStringItem(); Text text16 = new Text(); text16.Text = "FECHA"; sharedStringItem16.Append(text16); SharedStringItem sharedStringItem17 = new SharedStringItem(); Text text17 = new Text(); text17.Text = "COSTO"; sharedStringItem17.Append(text17); sharedStringTable1.Append(sharedStringItem1); sharedStringTable1.Append(sharedStringItem2); sharedStringTable1.Append(sharedStringItem3); sharedStringTable1.Append(sharedStringItem4); sharedStringTable1.Append(sharedStringItem5); sharedStringTable1.Append(sharedStringItem6); sharedStringTable1.Append(sharedStringItem7); sharedStringTable1.Append(sharedStringItem8); sharedStringTable1.Append(sharedStringItem9); sharedStringTable1.Append(sharedStringItem10); sharedStringTable1.Append(sharedStringItem11); sharedStringTable1.Append(sharedStringItem12); sharedStringTable1.Append(sharedStringItem13); sharedStringTable1.Append(sharedStringItem14); sharedStringTable1.Append(sharedStringItem15); sharedStringTable1.Append(sharedStringItem16); sharedStringTable1.Append(sharedStringItem17); sharedStringTablePart1.SharedStringTable = sharedStringTable1; }
// Generates content of sharedStringTablePart1. private void GenerateSharedStringTablePart1Content(SharedStringTablePart sharedStringTablePart1) { SharedStringTable sharedStringTable1 = new SharedStringTable(){ Count = (UInt32Value)1U, UniqueCount = (UInt32Value)1U }; SharedStringItem sharedStringItem1 = new SharedStringItem(); Text text1 = new Text(); text1.Text = "este es un texto en A1"; sharedStringItem1.Append(text1); sharedStringTable1.Append(sharedStringItem1); sharedStringTablePart1.SharedStringTable = sharedStringTable1; }
public static IEnumerable<DocumentFormat.OpenXml.Spreadsheet.Cell> Cells(this DocumentFormat.OpenXml.Spreadsheet.Row row, SharedStringTable sharedStringTable) { foreach (var celll in row.Descendants<DocumentFormat.OpenXml.Spreadsheet.Cell>()) { var c = (DocumentFormat.OpenXml.Spreadsheet.Cell)celll.Clone(); if (celll.CellValue != null) { var text = (celll.DataType != null && celll.DataType.HasValue && celll.CellValue.InnerText != null && celll.DataType == CellValues.SharedString) ? sharedStringTable.ChildElements[ int.Parse(celll.CellValue.InnerText)].InnerText : celll.CellValue.InnerText; c.CellValue.Text = text; } yield return c; } }
// // 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; }