private void GenerateSharedStringTablePartContent(SharedStringTablePart sharedStringTablePart,
SaveContext context)
{
// Call all table headers to make sure their names are filled
var x = 0;
Worksheets.ForEach(w => w.Tables.ForEach(t => x = (t as XLTable).FieldNames.Count));
sharedStringTablePart.SharedStringTable = new SharedStringTable {Count = 0, UniqueCount = 0};
var stringId = 0;
var newStrings = new Dictionary<String, Int32>();
var newRichStrings = new Dictionary<IXLRichText, Int32>();
foreach (
var c in
Worksheets.Cast<XLWorksheet>().SelectMany(
w =>
w.Internals.CellsCollection.GetCells(
c => ((c.DataType == XLCellValues.Text && c.ShareString) || c.HasRichText)
&& (c as XLCell).InnerText.Length > 0
&& XLHelper.IsNullOrWhiteSpace(c.FormulaA1)
)))
{
c.DataType = XLCellValues.Text;
if (c.HasRichText)
{
if (newRichStrings.ContainsKey(c.RichText))
c.SharedStringId = newRichStrings[c.RichText];
else
{
var sharedStringItem = new SharedStringItem();
foreach (var rt in c.RichText.Where(r => !String.IsNullOrEmpty(r.Text)))
{
sharedStringItem.Append(GetRun(rt));
}
if (c.RichText.HasPhonetics)
{
foreach (var p in c.RichText.Phonetics)
{
var phoneticRun = new PhoneticRun
{
BaseTextStartIndex = (UInt32)p.Start,
EndingBaseIndex = (UInt32)p.End
};
var text = new Text {Text = p.Text};
if (p.Text.PreserveSpaces())
text.Space = SpaceProcessingModeValues.Preserve;
phoneticRun.Append(text);
sharedStringItem.Append(phoneticRun);
}
var f = new XLFont(null, c.RichText.Phonetics);
if (!context.SharedFonts.ContainsKey(f))
context.SharedFonts.Add(f, new FontInfo {Font = f});
var phoneticProperties = new PhoneticProperties
{
FontId =
context.SharedFonts[
new XLFont(null, c.RichText.Phonetics)].
FontId
};
if (c.RichText.Phonetics.Alignment != XLPhoneticAlignment.Left)
phoneticProperties.Alignment = c.RichText.Phonetics.Alignment.ToOpenXml();
if (c.RichText.Phonetics.Type != XLPhoneticType.FullWidthKatakana)
phoneticProperties.Type = c.RichText.Phonetics.Type.ToOpenXml();
sharedStringItem.Append(phoneticProperties);
}
sharedStringTablePart.SharedStringTable.Append(sharedStringItem);
sharedStringTablePart.SharedStringTable.Count += 1;
sharedStringTablePart.SharedStringTable.UniqueCount += 1;
newRichStrings.Add(c.RichText, stringId);
c.SharedStringId = stringId;
stringId++;
}
}
else
{
if (newStrings.ContainsKey(c.Value.ToString()))
c.SharedStringId = newStrings[c.Value.ToString()];
else
{
var s = c.Value.ToString();
var sharedStringItem = new SharedStringItem();
var text = new Text {Text = s};
if (!s.Trim().Equals(s))
text.Space = SpaceProcessingModeValues.Preserve;
sharedStringItem.Append(text);
sharedStringTablePart.SharedStringTable.Append(sharedStringItem);
sharedStringTablePart.SharedStringTable.Count += 1;
sharedStringTablePart.SharedStringTable.UniqueCount += 1;
newStrings.Add(c.Value.ToString(), stringId);
c.SharedStringId = stringId;
stringId++;
}
}
}
}
public ExcelHelper Open(string ExcelFile,bool IsEditable=false)
{
_expectSheets = new List<string>();
OpenSettings os = new OpenSettings();
this._excelFilePath = ExcelFile;
_doc = SpreadsheetDocument.Open(_excelFilePath, IsEditable);
_wbPart = _doc.WorkbookPart;
_shareStringPart = _wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
shareStringDic = new Dictionary<int, string>();
int j = 0;
foreach (var item in _shareStringPart.SharedStringTable.Elements<SharedStringItem>()) {
shareStringDic.Add(j, item.InnerText);
j++;
}
return _instance;
}
private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
{
// If the part does not contain a SharedStringTable, create one.
if (shareStringPart.SharedStringTable == null)
{
shareStringPart.SharedStringTable = new SharedStringTable();
}
int i = 0;
// Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
{
if (item.InnerText == text)
{
return i;
}
i++;
}
// The text does not exist in the part. Create the SharedStringItem and return its index.
shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
shareStringPart.SharedStringTable.Save();
return i;
}
public void Close()
{
if (_doc != null)
{
_doc.Close();
_expectSheets = null;
_wbPart = null;
_shareStringPart = null;
}
}
public static String GetValue(Cell cell, SharedStringTablePart stringTablePart)
{
if (cell.ChildElements.Count == 0)
return null;
//get cell value
String value = cell.CellValue.InnerText;
//Look up real value from shared string table
if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
value = stringTablePart.SharedStringTable
.ChildElements[Int32.Parse(value)]
.InnerText;
return value;
}
//We use sharedstring becasue is more efficient. With same strings it use a ref and does not copy the value everytime
internal static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
{
if (shareStringPart.SharedStringTable == null)
shareStringPart.SharedStringTable = new SharedStringTable();
var i = 0;
foreach (var item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
{
if (item.InnerText == text)
return i;
i++;
}
shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text(text)));
shareStringPart.SharedStringTable.Save();
return i;
}
public List<object[]> LerPlanilhaDados(Stream stream)
{
List<object[]> dadosPlanilha = new List<object[]>();
using (_document = SpreadsheetDocument.Open(stream, true))
{
try
{
_wbPart = _document.WorkbookPart;
_sharedStringPart = GetSharedStringPart();
if (SelectFirstSheet())
{
int indice = 0;
foreach (Row row in _activeWorksheet.Descendants<Row>())
{
indice++;
if (indice == 1) continue; // Pula o cabeçalho.
List<object> dadosLinha = new List<object>();
foreach (Cell celula in row.Descendants<Cell>())
dadosLinha.Add(GetCellValue(celula));
dadosPlanilha.Add(dadosLinha.ToArray());
}
}
}
catch
{
throw;
}
finally
{
_document.Close();
}
return dadosPlanilha;
}
}
// 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;
}
public SharedStringTableIndexer(SharedStringTablePart toIndex)
{
// Handle parameter checking in ctor.
if (toIndex == null)
{
throw new ArgumentNullException("stringTablePart");
}
// In this case, we'll initialize the shared string table object for the caller.
if (toIndex.SharedStringTable == null)
{
// Side-effect!
toIndex.SharedStringTable = new SharedStringTable()
{
Count = 0U,
UniqueCount = 0U
};
}
this.Initialize(toIndex.SharedStringTable);
}
public OpenXmlExcel(Stream stream)
{
spreadSheet = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook);
WorkbookPart workbookPart = spreadSheet.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = InsertWorksheet(spreadSheet.WorkbookPart);
if (spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
{
shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
}
else
{
shareStringPart = spreadSheet.WorkbookPart.AddNewPart<SharedStringTablePart>();
}
shareStringPart.SharedStringTable = new SharedStringTable();
shareStringPart.SharedStringTable.Count = 1;
shareStringPart.SharedStringTable.UniqueCount = 1;
CurrentWorksheetPart = worksheetPart;
}
private static void SetCellValue(SharedStringTablePart shareStringTablePart, Cell cell, string value)
{
if (cell == null || cell.CellValue.Text.Equals(string.Empty))
{
return;
}
else
{
if (cell.DataType.Value == CellValues.SharedString)
{
int shareStringId = int.Parse(cell.CellValue.Text);
SharedStringItem item = shareStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(shareStringId);
item.Elements<Text>().First().Text = value;
}
else
{
cell.CellValue.Text = value;
}
}
}
// 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;
}
private bool MatchSeparatedField(IEnumerable<Cell> tcs, Field fld, FieldCellMap fm, SharedStringTablePart stringTable, CellFormats formats)
{
if (fm.Title != null && fld.titles.Select(t => t.ToLower()).Contains(fm.Title))
{
fm.field = fld;
if (fld.DataFormat == DataFormatType.Date || fld.DataFormat == DataFormatType.DateTime || fld.DataFormat == DataFormatType.DateMixed)
{
var cell = tcs.Where(c => c.CellReference == fm.cellLoc.ValueRef).FirstOrDefault();
fm.Value = Spreadsheet.GetCellValue(cell, stringTable.SharedStringTable, formats, fld);
}
return true;
}
return false;
}
// 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;
}
public MatchData MatchCellLayouts(MatchData md, IEnumerable<Cell> tcs, SheetLayout sheetLayout, SharedStringTablePart stringTable, CellFormats formats, FileInfo file)
{
// Obtain titles for all field cell layouts
md.fldCellVersMaps = new List<FieldCellVersionMap>();
foreach (var fldLayout in sheetLayout.wsLayout.cellLayouts)
{
var fldLayoutVals = new List<FieldCellMap>();
foreach (var cellLoc in fldLayout.cellLocations)
{
try
{
var cl = tcs.FirstOrDefault(cll => cll.CellReference.InnerText == cellLoc.TitleRef);
var clVal = tcs.FirstOrDefault(clv => clv.CellReference.InnerText == cellLoc.ValueRef);
var title = Spreadsheet.GetCellValue(cl, stringTable.SharedStringTable, formats, null);
if (title != null) title = System.Text.RegularExpressions.Regex.Replace(title, @"\s+", " ").Trim().ToLower();
var val = Spreadsheet.GetCellValue(clVal, stringTable.SharedStringTable, formats, null);
fldLayoutVals.Add(new FieldCellMap
{
cellLoc = cellLoc,
Title = string.IsNullOrWhiteSpace(title) ? null : title.Trim().ToLower(),
Value = string.IsNullOrWhiteSpace(val) ? null : val.Trim()
});
}
catch (Exception ex)
{
Log.New.Msg(ex);
}
}
md.fldCellVersMaps.Add(new FieldCellVersionMap { fldmaps = fldLayoutVals, fldLayout = fldLayout });
}
md.fldCellVersMaps.ForEach(vm => vm.fldmaps.ForEach(vfm => vfm.versMap = vm));
var reqFlds = sheetLayout.wsLayout.fields.Where(sf => sf.fldType == FieldType.cell && sf.isRequired);
// Match Titles to layout fields
foreach (var flvv in md.fldCellVersMaps)
{
var agMaps = new List<FieldCellMap>();
foreach (var fm in flvv.fldmaps.Where(m => m.Title != null))
{
MatchField(tcs, fm, sheetLayout, stringTable, formats, agMaps);
}
flvv.fldmaps.AddRange(agMaps);
// Only keep data containing CellDataLayouts
var fmts = new List<CellDataLayout> { CellDataLayout.combined, CellDataLayout.separate };
flvv.fldmaps = flvv.fldmaps.Where(fm => fm.cellLoc != null && fmts.Contains(fm.cellLoc.dataLayout)).ToList();
// Add a filename layout if the field exists.
var fileName = sheetLayout.wsLayout.fields.FirstOrDefault(fld => fld.fldType == FieldType.fileName);
if (fileName != null)
{
flvv.fldmaps.Add(new FieldCellMap { Title = FieldType.fileName.ToString(), field = fileName, Value = file.Name });
}
// Add a filePath layout if the field exists.
var filePath = sheetLayout.wsLayout.fields.FirstOrDefault(fld => fld.fldType == FieldType.filePath);
if (filePath != null)
{
flvv.fldmaps.Add(new FieldCellMap { Title = FieldType.filePath.ToString(), field = filePath, Value = file.FullName });
}
// Compute how well the matching went.
flvv.noneNullTitleCnt = flvv.fldmaps.Where(fm => !string.IsNullOrWhiteSpace(fm.Title)).Count();
flvv.noMatchCnt = flvv.fldmaps.Where(fm => fm.field == null).Count();
flvv.missingReqFldCnt = reqFlds.Where(rf => !flvv.fldmaps.Select(fm => fm.field).Contains(rf)).Count();
flvv.noReqValCnt = flvv.fldmaps.Where(fm => fm.field != null && fm.field.isRequired && string.IsNullOrWhiteSpace(fm.Value)).Count();
flvv.noValCnt = flvv.fldmaps.Where(fm => fm.field != null && string.IsNullOrWhiteSpace(fm.Value)).Count();
}
// Find the best acceptable layout match.
var fldLayout_v = md.fldCellVersMaps
.Where(fl => fl.noMatchCnt == 0 && fl.noReqValCnt == 0 && fl.missingReqFldCnt == 0)
.OrderByDescending(fl => fl.fldmaps.Count())
.OrderBy(fl => fl.noValCnt)
.FirstOrDefault();
md.fldCellMap = fldLayout_v;
md.matchCnt += md.fldCellMap != null ? 1 : 0;
return md;
}
private bool MatchField(IEnumerable<Cell> tcs, FieldCellMap fm, SheetLayout sheetLayout, SharedStringTablePart stringTable, CellFormats formats, List<FieldCellMap> agMaps)
{
bool foundFld = false;
foreach (var fld in sheetLayout.wsLayout.fields.Where(f => f.fldType == FieldType.cell))
{
if (foundFld)
break;
try
{
switch (fm.cellLoc.dataLayout)
{
case CellDataLayout.combined:
foundFld = MatchCombinedField(fld, fm);
break;
case CellDataLayout.separate:
foundFld = MatchSeparatedField(tcs, fld, fm, stringTable, formats);
break;
case CellDataLayout.aggregate:
// Cell contains an aggregate of fields. Based on
{
var aggCell = tcs.Where(c => c.CellReference == fm.cellLoc.TitleRef).FirstOrDefault();
var val = Spreadsheet.GetCellValue(aggCell, stringTable.SharedStringTable, formats, null);
if (val != null && fm.cellLoc.aggregateCellCnt > 0 && fm.cellLoc.aggregateCellSeparator != null && fm.cellLoc.aggregateCellSeparator.Count() > 0)
{
var cells = Regex.Split(val.Trim(), fm.cellLoc.aggregateCellSeparator).ToList();
if (cells.Count() == fm.cellLoc.aggregateCellCnt)
{
var nfms = new List<FieldCellMap>();
foreach(var agv in fm.cellLoc.cellMaps)
{
FieldCellMap nfm = null;
switch (agv.dataLayout)
{
case CellDataLayout.combined:
nfm = new FieldCellMap {
Title = cells[agv.aggregateIdx].ToLower(),
Value = cells[agv.aggregateIdx],
versMap = fm.versMap,
cellLoc = new CellLocation
{
dataLayout = CellDataLayout.combined,
TitleRef = fm.cellLoc.TitleRef,
ValueRef = fm.cellLoc.ValueRef
}
};
MatchField(tcs, nfm, sheetLayout, stringTable, formats, agMaps);
if (nfm.field != null)
nfms.Add(nfm);
break;
case CellDataLayout.lookup:
var nfld1 = sheetLayout.wsLayout.fields.Where(f => f.fldType == FieldType.cell);
var nfld2 = nfld1.Where(f => f.titles != null);
var nfld = nfld2.FirstOrDefault(f => f.titles.Select(t => t.ToLower()).Contains(agv.lookupString.ToLower()));
if (nfld != null)
{
nfm = new FieldCellMap
{
Title = agv.lookupString,
Value = cells[agv.aggregateIdx],
field = nfld,
versMap = fm.versMap,
cellLoc = new CellLocation
{
dataLayout = CellDataLayout.separate,
TitleRef = fm.cellLoc.TitleRef,
ValueRef = fm.cellLoc.ValueRef
}
};
nfms.Add(nfm);
}
break;
}
}
if(nfms.Count == fm.cellLoc.cellMaps.Count())
{
agMaps.AddRange(nfms);
foundFld = true;
break;
}
}
}
}
break;
}
}
catch (Exception ex)
{
Log.New.Msg(ex);
}
}
return foundFld;
}
/// <remarks>
/// An Excel SpreadSheet contains one or more Worksheets, each may or maynot contain data of interest.
/// - Excel is a terrible way to collect data from a large number of different sources in a consistent and reliable way.
/// - Be that as it may, Excel is favored by organizations that prefer manpower over automation when performing data processing tasks.
/// - Most state agencies are typical of this kind of organization.
/// - To top it off most of these agencies give little thought to gathering data in a consistent way. So we are likely to
/// recieve a dump of spreadsheets with a variety of inconsistencies.
///
/// As varied as these spreadsheets may be, a spreadsheet is expected to contain only a single type of related data set which is called a
/// DataSourceType in this application.
///
/// A DataSourceType describes how to process the worksheets in a spreadsheet. It indicates:
/// - the name of the file to save extracted data.
/// - a list of DataWorkSheets
/// - an indicator as to how to process the spreadsheet against the list of DataWorkSheets:
///
/// • MatchAllDataWorkSheets
/// - There must be a one to one correspondence between each DataWorkSheet and each SpreadSheet WorkSheet in order.
/// - The DataWorkSheet name must match the SpreadSheet WorkSheet name.
///
/// • MatchByClosestWorkSheetLayout
/// - Each SpreadSheet Worksheet will be matched against the closest DataWorkSheet/WorkSheetLayout
///
/// A DataWorkSheet has
/// - a Name to be used when processing the spreadsheet by MatchAllDataWorkSheets.
/// - a WorkSheetLayout
///
/// A WorkSheetLayout is
/// - a collection of field layout versions with additional information about how to determine where to look
/// for field cells on the spreadsheet.
/// - a collection of data columns with addition information about how to determine where to look
/// for the data column on the WorkSheet.
///
/// - Each data column in the collection has associated with it a list of column titles that should
/// map from the WorkSheet to the data column.
///
/// - The WorkSheetLayout also includes a collection of col layout versions. Each of these is a list of cells
/// that should be scraped for strings that are concatinated into a column title and the column to associate
/// the title with.
///
/// - For a given WorkSheet all layouts are processed
///
/// There is a layout of column title cells that will be scaped for column titles. Those titles
/// are then matched to a list of titles for a given data column. The assumption being that all titles to match
/// are unique across all data columns for a given WorkSheetLayout
/// </remarks>
public MatchData MatchLayouts(Worksheet ws, SheetLayout sheetLayout, SharedStringTablePart stringTable, CellFormats formats, FileInfo file)
{
MatchData md = new MatchData();
MatchData colMD = null;
MatchData cellMD = null;
// All cells in worksheet.
var tcs = ws.Descendants<Cell>();
switch (sheetLayout.wsLayout.layoutType)
{
case LayoutType.Both:
colMD = MatchColLayouts(md, tcs, sheetLayout, stringTable, formats, file);
cellMD = MatchCellLayouts(md, tcs, sheetLayout, stringTable, formats, file);
md.fldCellMap = cellMD.fldCellMap;
md.fldColMap = colMD.fldColMap;
md.isPass = md.fldColMap != null && md.fldCellMap != null;
break;
case LayoutType.CellOnly:
cellMD = MatchCellLayouts(md, tcs, sheetLayout, stringTable, formats, file);
md.fldCellMap = cellMD.fldCellMap;
md.isPass = md.fldCellMap != null;
break;
case LayoutType.ColumnOnly:
colMD = MatchColLayouts(md, tcs, sheetLayout, stringTable, formats, file);
md.fldColMap = colMD.fldColMap;
md.isPass = md.fldColMap != null;
break;
}
if (md.isPass)
{
if (sheetLayout.srcWorksheets == null)
sheetLayout.srcWorksheets = new List<Worksheet>();
sheetLayout.srcWorksheets.Add(ws);
}
return md;
}
// 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;
}
private void FillCellText(SharedStringTablePart shareStringPart, DocumentFormat.OpenXml.Spreadsheet.Cell cell, string text)
{
if (cell == null)
throw new ArgumentException("cell");
if (text == null)
text = String.Empty;
int index = InsertSharedStringItem(text, shareStringPart);
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(index.ToString());
cell.DataType = new EnumValue<DocumentFormat.OpenXml.Spreadsheet.CellValues>(DocumentFormat.OpenXml.Spreadsheet.CellValues.SharedString);
}
//public byte[] Populate(QueriesDataModel queriesDataModel, string templateFilePath)
//{
// // Read a template file.
// byte[] templateBytes;
// try
// {
// templateBytes = File.ReadAllBytes(templateFilePath);
// }
// catch (Exception ex)
// {
// Trace.TraceError("Unable to read a template file. Exception: {0}.", ex);
// throw;
// }
// using (var fileStream = new MemoryStream())
// {
// fileStream.Write(templateBytes, 0, templateBytes.Length);
// var spreadsheetDocument = SpreadsheetDocument.Open(fileStream, true);
// var workbookPart = spreadsheetDocument.WorkbookPart;
// // Get the SharedStringTablePart. If it does not exist, create a new one.
// //var sharedStringTablePart = workbookPart.GetPartsOfType<SharedStringTablePart>().Any()
// // ? workbookPart.GetPartsOfType<SharedStringTablePart>().First()
// // : workbookPart.AddNewPart<SharedStringTablePart>();
// foreach (var worksheetPart in workbookPart.WorksheetParts)
// {
// var sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
// var variableCells = getVariableCells(worksheetPart);
// foreach (var variableCell in variableCells)
// {
// var refCell = new Cell
// {
// CellReference = changeRowIndex(variableCell.Cell.CellReference, -1),
// StyleIndex = variableCell.Cell.StyleIndex
// };
// var dataCollection = getDataForVariable(queriesDataModel, variableCell);
// foreach (var data in dataCollection)
// {
// // Insert the text into the SharedStringTablePart.
// //var index = insertSharedStringTableItem(data.ToString(), sharedStringTablePart);
// var newCell = new Cell
// {
// CellReference = changeRowIndex(refCell.CellReference),
// CellValue = new CellValue(data.ToString()),
// DataType = new EnumValue<CellValues>(CellValues.String),
// //CellValue = new CellValue(index.ToString()),
// //DataType = new EnumValue<CellValues>(CellValues.SharedString),
// StyleIndex = refCell.StyleIndex
// };
// // Find a row for the new cell.
// var refRowIndex = getRowIndex(newCell.CellReference);
// var refRow = sheetData.Elements<Row>().SingleOrDefault(x => x.RowIndex == refRowIndex);
// // If a row does not exists, create a new one.
// if (refRow == null)
// {
// var newRow = new Row { RowIndex = refRowIndex };
// newRow.AppendChild(newCell);
// var rowAfter = sheetData.Elements<Row>().FirstOrDefault(row => row.RowIndex > newRow.RowIndex);
// sheetData.InsertBefore(newRow, rowAfter);
// }
// // Otherwise, add a new cell to an existing row.
// else
// {
// var existingCell = refRow.Elements<Cell>().SingleOrDefault(x => x.CellReference.Value == newCell.CellReference.Value);
// if (existingCell == null)
// {
// var cellAfter =
// refRow.Elements<Cell>()
// .FirstOrDefault(
// cell =>
// string.Compare(cell.CellReference.Value, newCell.CellReference.Value,
// StringComparison.InvariantCultureIgnoreCase) > 0);
// refRow.InsertBefore(newCell, cellAfter);
// }
// else
// refRow.ReplaceChild(newCell, existingCell);
// }
// refCell = newCell;
// }
// }
// }
// workbookPart.Workbook.Save();
// spreadsheetDocument.Close();
// return fileStream.ToArray();
// }
//}
private static int insertSharedStringTableItem(SharedStringTablePart sharedStringTablePart, string text)
{
// If the part does not contain a SharedStringTable, create one.
if (sharedStringTablePart.SharedStringTable == null)
sharedStringTablePart.SharedStringTable = new SharedStringTable();
// Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
var i = 0;
foreach (var item in sharedStringTablePart.SharedStringTable.Elements<SharedStringItem>())
{
if (item.InnerText == text)
return i;
i++;
}
// The text does not exist in the part. Create the SharedStringItem and return its index.
sharedStringTablePart.SharedStringTable.AppendChild(new SharedStringItem(new Text(text)));
sharedStringTablePart.SharedStringTable.Save();
return i;
}
// 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;
}
/// 向SharedStringTablePart添加字符串
/// </summary>
/// <param name="text">字符串內容</param>
/// <param name="shareStringPart">sharedStringTablePart內容</param>
/// <returns>如果要添加的字符串已經存在，則直接返回該字符串的索引</returns>
private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
{
//檢測SharedStringTable是否存在，如果不存在，則創建一個
if (shareStringPart.SharedStringTable == null)
{
shareStringPart.SharedStringTable = new SharedStringTable();
}
int i = 0;
//遍歷SharedStringTable中所有的Elements，查看目標字符串是否存在
foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
{
if (item.InnerText == text)
{
return i;
}
i++;
}
//如果目標字符串不存在，則創建一個，同時把SharedStringTable的最後一個Elements的"索引+1"返回
shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
shareStringPart.SharedStringTable.Save();
return i;
}
/// <summary>
/// Get cell's value depend on its style: share string or text
/// </summary>
/// <param name="shareStringTablePart"></param>
/// <param name="cell"></param>
/// <returns></returns>
private static string GetCellValue(SharedStringTablePart shareStringTablePart, Cell cell)
{
if (cell == null || cell.CellValue == null || cell.CellValue.Text.Equals(string.Empty))
{
return null;
}
else
{
if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
{
int shareStringId = int.Parse(cell.CellValue.Text);
SharedStringItem item = shareStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(shareStringId);
return item.InnerText;
}
else
{
return cell.CellValue.Text;
}
}
}
protected abstract void GenerateSharedStringTablePartContent(SharedStringTablePart sharedStringTablePart);
public MatchData MatchColLayouts(MatchData md, IEnumerable<Cell> tcs, SheetLayout sheetLayout, SharedStringTablePart stringTable, CellFormats formats, FileInfo file)
{
// Find and map columns to sheet layouts
var field_ord = sheetLayout.wsLayout.fields
.Where(c => c.fldType == FieldType.column)
.OrderBy(c => c.OutputOrder)
.Select(c => c.OutputOrder)
.ToList();
// Obtain column titles for all signature versions.
md.fldColVersMaps = new List<FieldColumnVersionMap>();
foreach (var sig in sheetLayout.wsLayout.colLayouts.OrderByDescending(scl => scl.titleLocations.Count()))
// for each column layout version scape the worksheet for column title values
{
var fldColMaps = new List<FieldColumnMap>();
var col_ord = sig.titleLocations.OrderBy(so => so.col).Select(so => so.col).ToList();
foreach (var colLayout in sig.titleLocations)
{
string title = "";
foreach (var c in colLayout.cellRefs)
// A column may have a number of title cells that must be scraped and concatinated to product the title used for matching to data columns.
{
var cl = tcs.FirstOrDefault(cll => cll.CellReference.InnerText == c);
var tlt = Spreadsheet.GetCellValue(cl, stringTable.SharedStringTable, formats, null);
title += tlt;
}
title = System.Text.RegularExpressions.Regex.Replace(title.Replace('\n', ' '), @"\s+", " ").Trim().ToLower();
fldColMaps.Add(new FieldColumnMap { column = colLayout.col, title = title, col_order = col_ord.IndexOf(colLayout.col) });
}
md.fldColVersMaps.Add(new FieldColumnVersionMap { colLayout = sig, colmaps = fldColMaps });
}
// Match the titles to the DataColumns
foreach (var fcvm in md.fldColVersMaps)
{
foreach (var cm in fcvm.colmaps)
{
try
{
// TODO: Performance improvement if config strings are pre-processed for whitespace and case.
cm.field = sheetLayout.wsLayout.fields
.Where(cc => cc.fldType == FieldType.column && cc.titles != null)
.FirstOrDefault(cc =>
{
var lct = cc.titles.Select(t =>
{
var tt = System.Text.RegularExpressions.Regex.Replace(t, @"\s+", " ");
return tt.ToLower();
});
var hasTitle = lct.Contains(cm.title);
return hasTitle;
});
// if required field and should be verified then check that first row has value
if (sheetLayout.wsLayout.verifyFirstRowData && cm.field != null && cm.field.isRequired)
{
int col = fcvm.colLayout.colLayoutType == ColLayoutType.Row_Col ? cm.column : fcvm.colLayout.FirstRow;
int row = fcvm.colLayout.colLayoutType == ColLayoutType.Row_Col ? fcvm.colLayout.FirstRow : cm.column;
var valRef = Spreadsheet.GetCellRef(row, col);
var clVal = tcs.FirstOrDefault(clv => clv.CellReference.InnerText == valRef);
var val = Spreadsheet.GetCellValue(clVal, stringTable.SharedStringTable, formats, cm.field);
if (!string.IsNullOrWhiteSpace(val))
{
cm.hasValue = true;
cm.firstRowVal = val;
}
}
cm.field_order = cm.field != null ? field_ord.IndexOf(cm.field.OutputOrder) : -9999;
}
catch (Exception ex)
{
Log.New.Msg(ex);
}
}
// match by neighbor
// - Locate flds located by related field
var flds_byRelated = sheetLayout.wsLayout.fields.Where(f => f.locType == LocateType.byRelated);
// - Locate column map for related field parent
var related_pairs = flds_byRelated.Select(fr => new { fr = fr, cm = fcvm.colmaps.FirstOrDefault(rcm => rcm.field != null && rcm.field.OutputOrder == fr.RelatedCol) });
// - Locate column map for related field
var rf_cm = related_pairs.Where(rp => rp.cm != null).ToList().Select(rp => new { rp = rp, rc = fcvm.colmaps.FirstOrDefault(fcm => fcm.col_order == rp.cm.col_order + 1) });
// - Update the field for located column map
rf_cm.ToList().ForEach(rfcm =>
{
rfcm.rc.field = rfcm.rp.fr;
rfcm.rc.field_order = rfcm.rc.field.OutputOrder;
});
fcvm.notNullTitleCnt = fcvm.colmaps.Where(cm => !string.IsNullOrWhiteSpace(cm.title)).Count();
fcvm.noMatchCnt = fcvm.colmaps.Where(cm => cm.field == null).Count();
fcvm.ReqNoValCnt = fcvm.colmaps.Where(cm => cm.field != null && sheetLayout.wsLayout.verifyFirstRowData && cm.field.isRequired && !cm.hasValue).Count();
fcvm.disOrder = (int)fcvm.colmaps.Where(dm => dm.field != null).Select(dm => Math.Pow((dm.field_order - dm.col_order), 2)).Sum();
var dupCols = fcvm.colmaps.Where(dm => dm.field != null).GroupBy(cd => cd.field).Where(d => d.Count() > 1);
fcvm.colDups = dupCols.Count();
}
// Only match col layout versions with zero mismatch, favoring the version with the lowest disorder.
var colLayout_v = md.fldColVersMaps.Where(sv => sv.noMatchCnt == 0 && sv.colDups == 0 && (!sheetLayout.wsLayout.verifyFirstRowData || sv.ReqNoValCnt == 0)).OrderByDescending(sv => sv.notNullTitleCnt).ThenByDescending(sv => sv.disOrder).FirstOrDefault();
md.fldColMap = colLayout_v;
md.matchCnt += md.fldColMap != null ? 1 : 0;
return md;
}
public ExcelSheetReader Load()
{
WorkbookPart wbp = ExcelLoader.SSDocument.WorkbookPart;
WorksheetPart wsp = (WorksheetPart)wbp.GetPartById(_sheet.Id.Value);
DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = wsp.Worksheet.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.SheetData>();
_iter = sheetData.Elements<Row>().GetEnumerator();
_sstp = wbp.GetPartsOfType<SharedStringTablePart>().First();
RowCount = sheetData.Elements<Row>().Count();
MaxRowIndex = sheetData.Elements<Row>().Max(v => v.RowIndex.Value);
// Field data
if (ExcelLoader.RowIndex_FieldName >= 0)
{
Row row = sheetData.Elements<Row>().Where(v => v.RowIndex == ExcelLoader.RowIndex_FieldName).FirstOrDefault();
Int32 idx = 0;
// field name
if (row == null)
throw new AegisException("FieldName index is {0}, but {1} has no {0} row index.", ExcelLoader.RowIndex_FieldName, _sheet.Name);
Fields = new FieldInfo[row.LongCount()];
foreach (Cell cell in row.Elements<Cell>())
{
Fields[idx] = new FieldInfo();
Fields[idx].Name = GetTextInCell(cell);
++idx;
}
// data type
idx = 0;
row = sheetData.Elements<Row>().Where(v => v.RowIndex == ExcelLoader.RowIndex_DataType).First();
foreach (Cell cell in row.Elements<Cell>())
{
String text = GetTextInCell(cell).ToLower();
if (text == "int" || text == "integer")
Fields[idx++].DataType = DataType.Int;
else if (text == "double")
Fields[idx++].DataType = DataType.Double;
else if (text == "datetime")
Fields[idx++].DataType = DataType.DateTime;
else if (text == "string")
Fields[idx++].DataType = DataType.String;
else
throw new AegisException("Invalid field type at {0}.{1}", _sheet.Name, cell.CellReference);
}
}
return this;
}
// 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>
/// Create an instance of OpenXmlPart according to the given relationship type.
/// </summary>
/// <param name="openXmlPackage">The container OpenXmlPackage.</param>
/// <param name="relationshipType">The relationship type of the target part.</param>
/// <param name="openXmlPart">The created instance of OpenXmlPart.</param>
/// <remarks>This partial method will be generated by code generaotr.</remarks>
static partial void CreatePartCore(OpenXmlPackage openXmlPackage, string relationshipType, ref OpenXmlPart openXmlPart)
{
if (openXmlPackage == null)
{
throw new ArgumentNullException("openXmlPackage");
}
if (relationshipType == null)
{
throw new ArgumentNullException("relationshipType");
}
if (openXmlPackage is WordprocessingDocument)
{
switch (relationshipType)
{
case MainDocumentPart.RelationshipTypeConstant:
openXmlPart = new MainDocumentPart();
return;
case CustomXmlPart.RelationshipTypeConstant:
openXmlPart = new CustomXmlPart();
return;
case CustomXmlPropertiesPart.RelationshipTypeConstant:
openXmlPart = new CustomXmlPropertiesPart();
return;
case GlossaryDocumentPart.RelationshipTypeConstant:
openXmlPart = new GlossaryDocumentPart();
return;
case WordprocessingCommentsPart.RelationshipTypeConstant:
openXmlPart = new WordprocessingCommentsPart();
return;
case AlternativeFormatImportPart.RelationshipTypeConstant:
openXmlPart = new AlternativeFormatImportPart();
return;
case ChartPart.RelationshipTypeConstant:
openXmlPart = new ChartPart();
return;
case ChartDrawingPart.RelationshipTypeConstant:
openXmlPart = new ChartDrawingPart();
return;
case ImagePart.RelationshipTypeConstant:
openXmlPart = new ImagePart();
return;
case EmbeddedPackagePart.RelationshipTypeConstant:
openXmlPart = new EmbeddedPackagePart();
return;
case ThemeOverridePart.RelationshipTypeConstant:
openXmlPart = new ThemeOverridePart();
return;
case ChartStylePart.RelationshipTypeConstant:
openXmlPart = new ChartStylePart();
return;
case ChartColorStylePart.RelationshipTypeConstant:
openXmlPart = new ChartColorStylePart();
return;
case DiagramColorsPart.RelationshipTypeConstant:
openXmlPart = new DiagramColorsPart();
return;
case DiagramDataPart.RelationshipTypeConstant:
openXmlPart = new DiagramDataPart();
return;
case SlidePart.RelationshipTypeConstant:
openXmlPart = new SlidePart();
return;
case DiagramPersistLayoutPart.RelationshipTypeConstant:
openXmlPart = new DiagramPersistLayoutPart();
return;
case DiagramLayoutDefinitionPart.RelationshipTypeConstant:
openXmlPart = new DiagramLayoutDefinitionPart();
return;
case DiagramStylePart.RelationshipTypeConstant:
openXmlPart = new DiagramStylePart();
return;
case EmbeddedObjectPart.RelationshipTypeConstant:
openXmlPart = new EmbeddedObjectPart();
return;
case VmlDrawingPart.RelationshipTypeConstant:
openXmlPart = new VmlDrawingPart();
return;
case LegacyDiagramTextPart.RelationshipTypeConstant:
openXmlPart = new LegacyDiagramTextPart();
return;
case EmbeddedControlPersistenceBinaryDataPart.RelationshipTypeConstant:
openXmlPart = new EmbeddedControlPersistenceBinaryDataPart();
return;
case NotesSlidePart.RelationshipTypeConstant:
openXmlPart = new NotesSlidePart();
return;
case NotesMasterPart.RelationshipTypeConstant:
openXmlPart = new NotesMasterPart();
return;
case ThemePart.RelationshipTypeConstant:
openXmlPart = new ThemePart();
return;
case UserDefinedTagsPart.RelationshipTypeConstant:
openXmlPart = new UserDefinedTagsPart();
return;
case SlideLayoutPart.RelationshipTypeConstant:
openXmlPart = new SlideLayoutPart();
return;
case SlideMasterPart.RelationshipTypeConstant:
openXmlPart = new SlideMasterPart();
return;
case EmbeddedControlPersistencePart.RelationshipTypeConstant:
openXmlPart = new EmbeddedControlPersistencePart();
return;
case SlideSyncDataPart.RelationshipTypeConstant:
openXmlPart = new SlideSyncDataPart();
return;
case WorksheetPart.RelationshipTypeConstant:
openXmlPart = new WorksheetPart();
return;
case DrawingsPart.RelationshipTypeConstant:
openXmlPart = new DrawingsPart();
return;
case WebExtensionPart.RelationshipTypeConstant:
openXmlPart = new WebExtensionPart();
return;
case PivotTablePart.RelationshipTypeConstant:
openXmlPart = new PivotTablePart();
return;
case PivotTableCacheDefinitionPart.RelationshipTypeConstant:
openXmlPart = new PivotTableCacheDefinitionPart();
return;
case PivotTableCacheRecordsPart.RelationshipTypeConstant:
openXmlPart = new PivotTableCacheRecordsPart();
return;
case SingleCellTablePart.RelationshipTypeConstant:
openXmlPart = new SingleCellTablePart();
return;
case TableDefinitionPart.RelationshipTypeConstant:
openXmlPart = new TableDefinitionPart();
return;
case QueryTablePart.RelationshipTypeConstant:
openXmlPart = new QueryTablePart();
return;
case ControlPropertiesPart.RelationshipTypeConstant:
openXmlPart = new ControlPropertiesPart();
return;
case CustomPropertyPart.RelationshipTypeConstant:
openXmlPart = new CustomPropertyPart();
return;
case WorksheetSortMapPart.RelationshipTypeConstant:
openXmlPart = new WorksheetSortMapPart();
return;
case SlicersPart.RelationshipTypeConstant:
openXmlPart = new SlicersPart();
return;
case TimeLinePart.RelationshipTypeConstant:
openXmlPart = new TimeLinePart();
return;
case DocumentSettingsPart.RelationshipTypeConstant:
openXmlPart = new DocumentSettingsPart();
return;
case MailMergeRecipientDataPart.RelationshipTypeConstant:
openXmlPart = new MailMergeRecipientDataPart();
return;
case EndnotesPart.RelationshipTypeConstant:
openXmlPart = new EndnotesPart();
return;
case FontTablePart.RelationshipTypeConstant:
openXmlPart = new FontTablePart();
return;
case FontPart.RelationshipTypeConstant:
openXmlPart = new FontPart();
return;
case FootnotesPart.RelationshipTypeConstant:
openXmlPart = new FootnotesPart();
return;
case NumberingDefinitionsPart.RelationshipTypeConstant:
openXmlPart = new NumberingDefinitionsPart();
return;
case StyleDefinitionsPart.RelationshipTypeConstant:
openXmlPart = new StyleDefinitionsPart();
return;
case StylesWithEffectsPart.RelationshipTypeConstant:
openXmlPart = new StylesWithEffectsPart();
return;
case WebSettingsPart.RelationshipTypeConstant:
openXmlPart = new WebSettingsPart();
return;
case FooterPart.RelationshipTypeConstant:
openXmlPart = new FooterPart();
return;
case HeaderPart.RelationshipTypeConstant:
openXmlPart = new HeaderPart();
return;
case WordprocessingPrinterSettingsPart.RelationshipTypeConstant:
openXmlPart = new WordprocessingPrinterSettingsPart();
return;
case CustomizationPart.RelationshipTypeConstant:
openXmlPart = new CustomizationPart();
return;
case WordAttachedToolbarsPart.RelationshipTypeConstant:
openXmlPart = new WordAttachedToolbarsPart();
return;
case VbaProjectPart.RelationshipTypeConstant:
openXmlPart = new VbaProjectPart();
return;
case VbaDataPart.RelationshipTypeConstant:
openXmlPart = new VbaDataPart();
return;
case WordprocessingCommentsExPart.RelationshipTypeConstant:
openXmlPart = new WordprocessingCommentsExPart();
return;
case WordprocessingPeoplePart.RelationshipTypeConstant:
openXmlPart = new WordprocessingPeoplePart();
return;
case ThumbnailPart.RelationshipTypeConstant:
openXmlPart = new ThumbnailPart();
return;
case CoreFilePropertiesPart.RelationshipTypeConstant:
openXmlPart = new CoreFilePropertiesPart();
return;
case ExtendedFilePropertiesPart.RelationshipTypeConstant:
openXmlPart = new ExtendedFilePropertiesPart();
return;
case CustomFilePropertiesPart.RelationshipTypeConstant:
openXmlPart = new CustomFilePropertiesPart();
return;
case DigitalSignatureOriginPart.RelationshipTypeConstant:
openXmlPart = new DigitalSignatureOriginPart();
return;
case XmlSignaturePart.RelationshipTypeConstant:
openXmlPart = new XmlSignaturePart();
return;
case QuickAccessToolbarCustomizationsPart.RelationshipTypeConstant:
openXmlPart = new QuickAccessToolbarCustomizationsPart();
return;
case RibbonExtensibilityPart.RelationshipTypeConstant:
openXmlPart = new RibbonExtensibilityPart();
return;
case RibbonAndBackstageCustomizationsPart.RelationshipTypeConstant:
openXmlPart = new RibbonAndBackstageCustomizationsPart();
return;
case WebExTaskpanesPart.RelationshipTypeConstant:
openXmlPart = new WebExTaskpanesPart();
return;
}
}
else if (openXmlPackage is SpreadsheetDocument)
{
switch (relationshipType)
{
case WorkbookPart.RelationshipTypeConstant:
openXmlPart = new WorkbookPart();
return;
case CustomXmlPart.RelationshipTypeConstant:
openXmlPart = new CustomXmlPart();
return;
case CustomXmlPropertiesPart.RelationshipTypeConstant:
openXmlPart = new CustomXmlPropertiesPart();
return;
case CalculationChainPart.RelationshipTypeConstant:
openXmlPart = new CalculationChainPart();
return;
case CellMetadataPart.RelationshipTypeConstant:
openXmlPart = new CellMetadataPart();
return;
case ConnectionsPart.RelationshipTypeConstant:
openXmlPart = new ConnectionsPart();
return;
case CustomXmlMappingsPart.RelationshipTypeConstant:
openXmlPart = new CustomXmlMappingsPart();
return;
case SharedStringTablePart.RelationshipTypeConstant:
openXmlPart = new SharedStringTablePart();
return;
case WorkbookRevisionHeaderPart.RelationshipTypeConstant:
openXmlPart = new WorkbookRevisionHeaderPart();
return;
case WorkbookRevisionLogPart.RelationshipTypeConstant:
openXmlPart = new WorkbookRevisionLogPart();
return;
case WorkbookUserDataPart.RelationshipTypeConstant:
openXmlPart = new WorkbookUserDataPart();
return;
case WorkbookStylesPart.RelationshipTypeConstant:
openXmlPart = new WorkbookStylesPart();
return;
case ThemePart.RelationshipTypeConstant:
openXmlPart = new ThemePart();
return;
case ImagePart.RelationshipTypeConstant:
openXmlPart = new ImagePart();
return;
case ThumbnailPart.RelationshipTypeConstant:
openXmlPart = new ThumbnailPart();
return;
case VolatileDependenciesPart.RelationshipTypeConstant:
openXmlPart = new VolatileDependenciesPart();
return;
case ChartsheetPart.RelationshipTypeConstant:
openXmlPart = new ChartsheetPart();
return;
case SpreadsheetPrinterSettingsPart.RelationshipTypeConstant:
openXmlPart = new SpreadsheetPrinterSettingsPart();
return;
case DrawingsPart.RelationshipTypeConstant:
openXmlPart = new DrawingsPart();
return;
case ChartPart.RelationshipTypeConstant:
openXmlPart = new ChartPart();
return;
case ChartDrawingPart.RelationshipTypeConstant:
openXmlPart = new ChartDrawingPart();
return;
case EmbeddedPackagePart.RelationshipTypeConstant:
openXmlPart = new EmbeddedPackagePart();
return;
case ThemeOverridePart.RelationshipTypeConstant:
openXmlPart = new ThemeOverridePart();
return;
case ChartStylePart.RelationshipTypeConstant:
openXmlPart = new ChartStylePart();
return;
case ChartColorStylePart.RelationshipTypeConstant:
openXmlPart = new ChartColorStylePart();
return;
case DiagramColorsPart.RelationshipTypeConstant:
openXmlPart = new DiagramColorsPart();
return;
case DiagramDataPart.RelationshipTypeConstant:
openXmlPart = new DiagramDataPart();
return;
case SlidePart.RelationshipTypeConstant:
openXmlPart = new SlidePart();
return;
case DiagramPersistLayoutPart.RelationshipTypeConstant:
openXmlPart = new DiagramPersistLayoutPart();
return;
case DiagramLayoutDefinitionPart.RelationshipTypeConstant:
openXmlPart = new DiagramLayoutDefinitionPart();
return;
case DiagramStylePart.RelationshipTypeConstant:
openXmlPart = new DiagramStylePart();
return;
case EmbeddedObjectPart.RelationshipTypeConstant:
openXmlPart = new EmbeddedObjectPart();
return;
case VmlDrawingPart.RelationshipTypeConstant:
openXmlPart = new VmlDrawingPart();
return;
case LegacyDiagramTextPart.RelationshipTypeConstant:
openXmlPart = new LegacyDiagramTextPart();
return;
case EmbeddedControlPersistenceBinaryDataPart.RelationshipTypeConstant:
openXmlPart = new EmbeddedControlPersistenceBinaryDataPart();
return;
case NotesSlidePart.RelationshipTypeConstant:
openXmlPart = new NotesSlidePart();
return;
case NotesMasterPart.RelationshipTypeConstant:
openXmlPart = new NotesMasterPart();
return;
case UserDefinedTagsPart.RelationshipTypeConstant:
openXmlPart = new UserDefinedTagsPart();
return;
case SlideLayoutPart.RelationshipTypeConstant:
openXmlPart = new SlideLayoutPart();
return;
case SlideMasterPart.RelationshipTypeConstant:
openXmlPart = new SlideMasterPart();
return;
case EmbeddedControlPersistencePart.RelationshipTypeConstant:
openXmlPart = new EmbeddedControlPersistencePart();
return;
case SlideSyncDataPart.RelationshipTypeConstant:
openXmlPart = new SlideSyncDataPart();
return;
case WorksheetPart.RelationshipTypeConstant:
openXmlPart = new WorksheetPart();
return;
case WorksheetCommentsPart.RelationshipTypeConstant:
openXmlPart = new WorksheetCommentsPart();
return;
case PivotTablePart.RelationshipTypeConstant:
openXmlPart = new PivotTablePart();
return;
case PivotTableCacheDefinitionPart.RelationshipTypeConstant:
openXmlPart = new PivotTableCacheDefinitionPart();
return;
case PivotTableCacheRecordsPart.RelationshipTypeConstant:
openXmlPart = new PivotTableCacheRecordsPart();
return;
case SingleCellTablePart.RelationshipTypeConstant:
openXmlPart = new SingleCellTablePart();
return;
case TableDefinitionPart.RelationshipTypeConstant:
openXmlPart = new TableDefinitionPart();
return;
case QueryTablePart.RelationshipTypeConstant:
openXmlPart = new QueryTablePart();
return;
case ControlPropertiesPart.RelationshipTypeConstant:
openXmlPart = new ControlPropertiesPart();
return;
case CustomPropertyPart.RelationshipTypeConstant:
openXmlPart = new CustomPropertyPart();
return;
case WorksheetSortMapPart.RelationshipTypeConstant:
openXmlPart = new WorksheetSortMapPart();
return;
case SlicersPart.RelationshipTypeConstant:
openXmlPart = new SlicersPart();
return;
case TimeLinePart.RelationshipTypeConstant:
openXmlPart = new TimeLinePart();
return;
case WebExtensionPart.RelationshipTypeConstant:
openXmlPart = new WebExtensionPart();
return;
case DialogsheetPart.RelationshipTypeConstant:
openXmlPart = new DialogsheetPart();
return;
case ExternalWorkbookPart.RelationshipTypeConstant:
openXmlPart = new ExternalWorkbookPart();
return;
case ExcelAttachedToolbarsPart.RelationshipTypeConstant:
openXmlPart = new ExcelAttachedToolbarsPart();
return;
case VbaProjectPart.RelationshipTypeConstant:
openXmlPart = new VbaProjectPart();
return;
case VbaDataPart.RelationshipTypeConstant:
openXmlPart = new VbaDataPart();
return;
case MacroSheetPart.RelationshipTypeConstant:
openXmlPart = new MacroSheetPart();
return;
case InternationalMacroSheetPart.RelationshipTypeConstant:
openXmlPart = new InternationalMacroSheetPart();
return;
case CustomDataPropertiesPart.RelationshipTypeConstant:
openXmlPart = new CustomDataPropertiesPart();
return;
case CustomDataPart.RelationshipTypeConstant:
openXmlPart = new CustomDataPart();
return;
case SlicerCachePart.RelationshipTypeConstant:
openXmlPart = new SlicerCachePart();
return;
case TimeLineCachePart.RelationshipTypeConstant:
openXmlPart = new TimeLineCachePart();
return;
case CoreFilePropertiesPart.RelationshipTypeConstant:
openXmlPart = new CoreFilePropertiesPart();
return;
case ExtendedFilePropertiesPart.RelationshipTypeConstant:
openXmlPart = new ExtendedFilePropertiesPart();
return;
case CustomFilePropertiesPart.RelationshipTypeConstant:
openXmlPart = new CustomFilePropertiesPart();
return;
case DigitalSignatureOriginPart.RelationshipTypeConstant:
openXmlPart = new DigitalSignatureOriginPart();
return;
case XmlSignaturePart.RelationshipTypeConstant:
openXmlPart = new XmlSignaturePart();
return;
case QuickAccessToolbarCustomizationsPart.RelationshipTypeConstant:
openXmlPart = new QuickAccessToolbarCustomizationsPart();
return;
case RibbonExtensibilityPart.RelationshipTypeConstant:
openXmlPart = new RibbonExtensibilityPart();
return;
case RibbonAndBackstageCustomizationsPart.RelationshipTypeConstant:
openXmlPart = new RibbonAndBackstageCustomizationsPart();
return;
case WebExTaskpanesPart.RelationshipTypeConstant:
openXmlPart = new WebExTaskpanesPart();
return;
}
}
else if (openXmlPackage is PresentationDocument)
{
switch (relationshipType)
{
case PresentationPart.RelationshipTypeConstant:
openXmlPart = new PresentationPart();
return;
case CustomXmlPart.RelationshipTypeConstant:
openXmlPart = new CustomXmlPart();
return;
case CustomXmlPropertiesPart.RelationshipTypeConstant:
openXmlPart = new CustomXmlPropertiesPart();
return;
case FontPart.RelationshipTypeConstant:
openXmlPart = new FontPart();
return;
case PresentationPropertiesPart.RelationshipTypeConstant:
openXmlPart = new PresentationPropertiesPart();
return;
case TableStylesPart.RelationshipTypeConstant:
openXmlPart = new TableStylesPart();
return;
case ThemePart.RelationshipTypeConstant:
openXmlPart = new ThemePart();
return;
case ImagePart.RelationshipTypeConstant:
openXmlPart = new ImagePart();
return;
case ViewPropertiesPart.RelationshipTypeConstant:
openXmlPart = new ViewPropertiesPart();
return;
case SlidePart.RelationshipTypeConstant:
openXmlPart = new SlidePart();
return;
case ChartPart.RelationshipTypeConstant:
openXmlPart = new ChartPart();
return;
case ChartDrawingPart.RelationshipTypeConstant:
openXmlPart = new ChartDrawingPart();
return;
case EmbeddedPackagePart.RelationshipTypeConstant:
openXmlPart = new EmbeddedPackagePart();
return;
case ThemeOverridePart.RelationshipTypeConstant:
openXmlPart = new ThemeOverridePart();
return;
case ChartStylePart.RelationshipTypeConstant:
openXmlPart = new ChartStylePart();
return;
case ChartColorStylePart.RelationshipTypeConstant:
openXmlPart = new ChartColorStylePart();
return;
case DiagramColorsPart.RelationshipTypeConstant:
openXmlPart = new DiagramColorsPart();
return;
case DiagramDataPart.RelationshipTypeConstant:
openXmlPart = new DiagramDataPart();
return;
case WorksheetPart.RelationshipTypeConstant:
openXmlPart = new WorksheetPart();
return;
case DrawingsPart.RelationshipTypeConstant:
openXmlPart = new DrawingsPart();
return;
case DiagramPersistLayoutPart.RelationshipTypeConstant:
openXmlPart = new DiagramPersistLayoutPart();
return;
case DiagramLayoutDefinitionPart.RelationshipTypeConstant:
openXmlPart = new DiagramLayoutDefinitionPart();
return;
case DiagramStylePart.RelationshipTypeConstant:
openXmlPart = new DiagramStylePart();
return;
case WebExtensionPart.RelationshipTypeConstant:
openXmlPart = new WebExtensionPart();
return;
case VmlDrawingPart.RelationshipTypeConstant:
openXmlPart = new VmlDrawingPart();
return;
case LegacyDiagramTextPart.RelationshipTypeConstant:
openXmlPart = new LegacyDiagramTextPart();
return;
case PivotTablePart.RelationshipTypeConstant:
openXmlPart = new PivotTablePart();
return;
case PivotTableCacheDefinitionPart.RelationshipTypeConstant:
openXmlPart = new PivotTableCacheDefinitionPart();
return;
case PivotTableCacheRecordsPart.RelationshipTypeConstant:
openXmlPart = new PivotTableCacheRecordsPart();
return;
case SingleCellTablePart.RelationshipTypeConstant:
openXmlPart = new SingleCellTablePart();
return;
case TableDefinitionPart.RelationshipTypeConstant:
openXmlPart = new TableDefinitionPart();
return;
case QueryTablePart.RelationshipTypeConstant:
openXmlPart = new QueryTablePart();
return;
case EmbeddedControlPersistencePart.RelationshipTypeConstant:
openXmlPart = new EmbeddedControlPersistencePart();
return;
case EmbeddedControlPersistenceBinaryDataPart.RelationshipTypeConstant:
openXmlPart = new EmbeddedControlPersistenceBinaryDataPart();
return;
case ControlPropertiesPart.RelationshipTypeConstant:
openXmlPart = new ControlPropertiesPart();
return;
case EmbeddedObjectPart.RelationshipTypeConstant:
openXmlPart = new EmbeddedObjectPart();
return;
case CustomPropertyPart.RelationshipTypeConstant:
openXmlPart = new CustomPropertyPart();
return;
case WorksheetSortMapPart.RelationshipTypeConstant:
openXmlPart = new WorksheetSortMapPart();
return;
case SlicersPart.RelationshipTypeConstant:
openXmlPart = new SlicersPart();
return;
case TimeLinePart.RelationshipTypeConstant:
openXmlPart = new TimeLinePart();
return;
case SlideCommentsPart.RelationshipTypeConstant:
openXmlPart = new SlideCommentsPart();
return;
case NotesSlidePart.RelationshipTypeConstant:
openXmlPart = new NotesSlidePart();
return;
case NotesMasterPart.RelationshipTypeConstant:
openXmlPart = new NotesMasterPart();
return;
case UserDefinedTagsPart.RelationshipTypeConstant:
openXmlPart = new UserDefinedTagsPart();
return;
case SlideLayoutPart.RelationshipTypeConstant:
openXmlPart = new SlideLayoutPart();
return;
case SlideMasterPart.RelationshipTypeConstant:
openXmlPart = new SlideMasterPart();
return;
case SlideSyncDataPart.RelationshipTypeConstant:
openXmlPart = new SlideSyncDataPart();
return;
case CommentAuthorsPart.RelationshipTypeConstant:
openXmlPart = new CommentAuthorsPart();
return;
case HandoutMasterPart.RelationshipTypeConstant:
openXmlPart = new HandoutMasterPart();
return;
case LegacyDiagramTextInfoPart.RelationshipTypeConstant:
openXmlPart = new LegacyDiagramTextInfoPart();
return;
case VbaProjectPart.RelationshipTypeConstant:
openXmlPart = new VbaProjectPart();
return;
case VbaDataPart.RelationshipTypeConstant:
openXmlPart = new VbaDataPart();
return;
case CoreFilePropertiesPart.RelationshipTypeConstant:
openXmlPart = new CoreFilePropertiesPart();
return;
case ExtendedFilePropertiesPart.RelationshipTypeConstant:
openXmlPart = new ExtendedFilePropertiesPart();
return;
case CustomFilePropertiesPart.RelationshipTypeConstant:
openXmlPart = new CustomFilePropertiesPart();
return;
case ThumbnailPart.RelationshipTypeConstant:
openXmlPart = new ThumbnailPart();
return;
case DigitalSignatureOriginPart.RelationshipTypeConstant:
openXmlPart = new DigitalSignatureOriginPart();
return;
case XmlSignaturePart.RelationshipTypeConstant:
openXmlPart = new XmlSignaturePart();
return;
case QuickAccessToolbarCustomizationsPart.RelationshipTypeConstant:
openXmlPart = new QuickAccessToolbarCustomizationsPart();
return;
case RibbonExtensibilityPart.RelationshipTypeConstant:
openXmlPart = new RibbonExtensibilityPart();
return;
case RibbonAndBackstageCustomizationsPart.RelationshipTypeConstant:
openXmlPart = new RibbonAndBackstageCustomizationsPart();
return;
case WebExTaskpanesPart.RelationshipTypeConstant:
openXmlPart = new WebExTaskpanesPart();
return;
}
}
else
{
System.Diagnostics.Debug.Assert(false);
}
return;
}
static string GetCellValue(WorksheetPart worksheetPart, SharedStringTablePart stringTablePart, string startCol, string startRow)
{
string reference = startCol + startRow;
//get exact cell based on reference
IEnumerable<Cell> cell = worksheetPart.Worksheet.Descendants<Cell>()
.Where(c => reference.Equals(c.CellReference));
if (cell.Count() != 0)
return GetValue(cell.First(), stringTablePart);
else
return null;
}