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